SQL SELECT: ORDER BY, Constants, Arithmetic, Aggregate Functions, and GROUP BY, Slides of Database Management Systems (DBMS)

Download SQL SELECT: ORDER BY, Constants, Arithmetic, Aggregate Functions, and GROUP BY and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Yet More SQL SELECT

ORDER BY

• The ORDER BY clause

sorts the results of a

query

• You can sort in ascending (default) or descending order
• Multiple columns can be given
• You cannot order by a column which isn’t in the result

SELECT

FROM

WHERE

ORDER BY

[ASCENDING |

DESCENDING|

ASC | DESC ]

ORDER BY Example

Grades

Name Code Mark

John DBS 56 John IAI 72 Mary DBS 60 Mark PR1 43 Mark PR2 35 Jane IAI 54

Name Code Mark Mary DBS 60 John DBS 56 John IAI 72 Jane IAI 54 Mark PR1 43 Mark PR2 35

SELECT * FROM Grades

ORDER BY Code ASC,

Mark DESC

Constants and Arithmetic

• As well as column

names, you can

select constants,

compute arithmetic

expressions and

evaluate functions in

a SELECT statement

SELECT Mark/ FROM Grades

SELECT

Salary + Bonus FROM Employee

SELECT 1.175Price FROM Products*

Aggregate Functions

Grades

Name Code Mark

John DBS 56 John IAI 72 Mary DBS 60 Mark PR1 43 Mark PR2 35 Jane IAI 54

SELECT

COUNT() AS Count FROM Grades*

SELECT

SUM(Mark) AS Total FROM Grades

SELECT

MAX(Mark) AS Best FROM Grades

Count 6

Total 320

Best 72

Aggregate Functions

• You can combine

aggregate functions

using arithmetic

SELECT

MAX(Mark)-MIN(Mark) AS Range FROM Grades Grades Name Code Mark John DBS 56 John IAI 72 Mary DBS 60 Mark PR1 43 Mark PR2 35 Jane IAI 54

Range 37

MAX(Mark) = 72

MIN(Mark) = 35

GROUP BY

• Sometimes we want

to apply aggregate

functions to groups

of rows

• Example, find the

average mark of

each student

• The GROUP BY clause

does this

SELECT

FROM

GROUP BY

GROUP BY

SELECT

FROM

GROUP BY

• Every entry in

must be in

, be a

constant, or be an

aggregate function

• You can have WHERE

or ORDER BY clauses

as well as a GROUP

BY clause

GROUP BY

• Find the total value

of the sales for each

department in each

month

• Can group by Month then Department or Department then Month
• Same results, but in a different order

Month Department Value

March Fiction 20 March Travel 30 March Technical 40 April Fiction 10 April Fiction 30 April Travel 25 April Fiction 20 May Fiction 20 May Technical 50

Sales

GROUP BY

Month Department Total

April Fiction 60 April Travel 25 March Fiction 20 March Technical 40 March Travel 30 May Fiction 20 May Technical 50

SELECT Month, Department, SUM(Value) AS Total FROM Sales GROUP BY Month, Department Month Department Total April Fiction 60 March Fiction 20 May Fiction 20 March Technical 40 May Technical 50 April Travel 25 March Travel 30

SELECT Month, Department, SUM(Value) AS Total FROM Sales GROUP BY Department, Month

WHERE and HAVING

• WHERE refers to the

rows of tables, and

so cannot use

aggregate functions

• HAVING refers to the

groups of rows, and

so cannot use

columns which are

not in the GROUP BY

• Think of a query

being processed as

follows:

• Tables are combined
• WHERE clauses
• GROUP BY and Aggregates
• Column selection
• HAVING clauses
• ORDER BY

UNION, etc.

• UNION , INTERSECT ,

and EXCEPT

• These treat the tables as sets and are the usual set operators of union, intersection, and difference
• We’ll concentrate on UNION
• Oracle has MINUS instead of EXCEPT - They all combine the

results from two

select statements

• The results of the

two selects must

have the same

columns and data

types

UNION

• The average for each

student:

SELECT Name,

AVG(Mark) AS Average FROM Grades GROUP BY Name

• The average overall

SELECT ‘Total’ AS Name, AVG(Mark) AS Average FROM Grades

• Note - this has the

same columns as the

average by student

UNION

SELECT Name

AVG(Mark) AS Average

FROM Grades

GROUP BY Name

UNION

SELECT

'Total' as Name,

AVG(Mark) AS Average

FROM Grades

Name Average Jane 52 John 64 Mark 39 Mary 60 Total 53