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
Due to an increase in overhead costs, the buying price of all items needs to be increased. Management wants to see a report before deciding how much each product will go up. Add to

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

I need SQL , WP SQL Expert Project Description: Expert required to modify WP SQL query. Skills required are MySQL, SQL, PHP, Wordpress

SQL Functions The PL/SQL uses all the SQL functions involving the following aggregate functions that summarize the whole columns of the Oracle data: GROUPING, AVG, COUNT, STDDE

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

If two relations R and S are joined, then the non matching tuples of both R and S are ignored in __________________.

Use of COUNT in SQL It describes and discusses various general methods of expressing constraints, eventually noting that support for "=" with relation operands is sufficient f

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

Expressions   An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl

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