Example of groupby operator - sql, PL-SQL Programming

Assignment Help:

Example of GROUPBY Operator

Example: How many students sat each exam,

using GROUP BY, NATURAL LEFT JOIN, and COALESCE

SELECT CourseId, COALESCE (n, 0) AS n

FROM COURSE NATURAL LEFT JOIN

(SELECT CourseId, COUNT (*) AS n

FROM EXAM_MARK

GROUP BY CourseId) AS T

Explanation

NATURAL JOIN: Note, however, that the use of LEFT makes this an outer join, whereas Codd's term natural join referred to the "inner" variety only.
LEFT specifies that each unmatched row in the first join operand, COURSE, is to be extended with NULL for the column n.
COALESCE (n, 0) AS n effectively replaces those appearances of NULL by the correct value, 0.

Related Discussions:- Example of groupby operator - sql

Cursor for loops, Cursor FOR Loops In most cases that need an explicit ...

Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

Assigning and comparing collections, Assigning and Comparing Collections ...

Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

Like operator-comparison operators, LIKE Operator You use the LIKE opera...

LIKE Operator You use the LIKE operator to compare the character value to a pattern. The Case is significant. LIKE returns the Boolean value TRUE when the character patterns mat

Enforce security in the database system, Question: (a) In the context o...

Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

Update command- sql, UPDATE Command- SQL Loosely speaking, UPDATE chan...

UPDATE Command- SQL Loosely speaking, UPDATE changes some of the column values of some existing rows of its target table. Thus, although some rows disappear from the target an

Goto statement - syntax, GOTO Statement The GOTO statement branches ...

GOTO Statement The GOTO statement branches categorically to a block label or statement label. The label should be exclusive within its scope and should precede a PL/SQL bloc

Triggers, At times, customers make mistakes in submitting their orders and ...

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Pl/sql conditional control: if statements, Pl/sql Conditional Control: IF s...

Pl/sql Conditional Control: IF statements Frequently, it is necessary to take the alternative actions depending on the circumstances. The IF statement execute a series of statem

Cursors, What is Cursors how to use it in Real time application ?

What is Cursors how to use it in Real time application ?

Anatomy of a table, Anatomy of a Table: Figure shows the terminology u...

Anatomy of a Table: Figure shows the terminology used in SQL to refer to parts of the structure of a table. As you can see, SQL has no official terms for its counterpa

Write Your Message!

Name

Email id

Message

Verfication Code

User Account

All Pages