Example of groupby operator - sql, PL-SQL Programming

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.
Posted Date: 1/18/2013 6:39:54 AM | Location : United States







Related Discussions:- Example of groupby operator - sql, Assignment Help, Ask Question on Example of groupby operator - sql, Get Answer, Expert's Help, Example of groupby operator - sql Discussions

Write discussion on Example of groupby operator - sql
Your posts are moderated
Related Questions
i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Effects of NULL for UNIQUE Specification When a UNIQUE specification u for base table t includes a column c that is not subject to a NOT NULL constraint, the appearance of sev

Dynamic SQL: The Most PL/SQL programs do a predictable, specific job. For illustration, a stored procedure may accept an employee number and salary increase, and then update t

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Need Azure CRM Web Application with two-factor authentication We presently have a CRM-like database stored on MS Azure that we presently access over an MS Access application. It

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d

Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign