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
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

Seeking a programmer to design a legal document with pre-existing fields that could allow the auto-population of client(s) information (i.e. Name, Account Number, Address etc.) int

Parameter and Keyword Description: select_item: This select_item is a value returned by the SELECT statement, and then assigned to the equivalent variable or field in the

How Transactions Guard Your Database The transaction is a sequence of SQL data manipulation statements which does a logical unit of work. The Oracle treats the sequence of SQL

Controlling Cursor Variables You use 3 statements to control the cursor variable: OPEN-FOR, FETCH, & CLOSE. At First, you OPEN a cursor variable FOR a multi-row query. Then, y

MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example

Constants and Variables:   You can declare the constants and variables in the declarative section of any PL/SQL subprogram, block, or package. The Declarations allot the stor

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL

Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL