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
Define basic operators of relational algebra with an example each

Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER

UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

COMMIT Statement The COMMIT statement explicitly makes everlasting changes to the database during the present transaction. The Changes made to the database are not considered e

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

Many of the reports generated from the system calculate the total dollars in purchases for a shopper. Complete the following steps to create a function named TOT_PURCH_SF that acce

Using a join on 3 tables, select 5 columns and 10 rows from the 3 tables without the use of a Cartesian product Query: SELECT E.LAST_NAME, E.FIRST_NAME, S.BUILDING, S.BRAN

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th

Components of an Object Type: An object type encapsulates the operations and data. Therefore, you can declare the methods and attributes in an object type specification, but no