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
Parameter and Keyword Description: cursor_name: This identifies an explicit cursor formerly declared within the present scope. cursor_variable_name: These identif

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Example of Check Constraints Example: Workaround for when subqueries not permitted in CHECK constraints CREATE FUNCTION NO_MORE_THAN_20000_ENROLMENTS ( ) RETURNS BOOLEAN

Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla

Example of UNWRAP Operator - SQL Example here shows how unwrapping can be done in longhand in SQL. Example: Unwrapping in SQL Letting CONTACT_INFO_WRAPPED denote the res

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

I need to write one function and one procedure to query a Oracle 10.1 DB using PL SQL. I have the schema and exact queries...along with work Ive started and a template to put the a

Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl