Databases Design. Introduction to SQL

Last lecture
AS
String Concatenation ||
DISTINCT
IS NULL & IS NOT NULL
Range condition:
<,<=,>, >=
BETWEEN & NOT BETWEEN
LIKE with % and _ characters
CAST & ::

Aggregate Functions
SQL provides the following aggregate functions that appear in SELECT statement:
Min() selects the minimum value
Max() selects the maximum value
Avg() selects the average value
Sum() selects the sum of occurrences
Count() selects the number of occurrences
SQL aggregate functions return a single value,
calculated from values in a column.

Aggregate Functions
Example: Select the minimum, maximum and average gpa from the Students table.
SELECT min(gpa), max(gpa), avg(gpa)
FROM Students;

Aggregate Functions
Selecting count(*) or count(expression) returns the number of tuples that satisfy a selection condition.
Example: Get number of students.
SELECT count(*)
FROM Students;

Aggregate Functions
Example: Get number of students with group_id = 1. The column should be named NumOfStud.
SELECT count(*) AS NumOfStud
FROM Students
WHERE group_id=1;

Students table
Count (*) Count (group_id)
Count example

GROUP BY
The aggregate functions can also be applied to subsets of tables.
In SQL, rows can be grouped together based on the value of some attribute(s) called grouping attribute.
The GROUP BY clause is used to specify these groupings.
The effect is to combine each set of rows having common values into one group row that represents all rows in the group. This is done to compute aggregates that apply to these groups.

GROUP BY: example
Example: Select the group_id’s that students study in and the number of students that study in those groups.
SELECT group_id, count(*)
FROM Students
GROUP BY group_id;
Note: The group by attribute (group_id) should be part of the selected columns.

GROUP BY: example
Students table
SELECT count(*)
FROM Students;

GROUP BY: example
Students table
SELECT group_id, count(*)
FROM Students
GROUP BY group_id;

HAVING
The HAVING clause is used to filtering groups
This is similar to a selection condition WHERE only the required rows are filtered out

HAVING: example
Query the group_id’s and number of students of each group.
SELECT group_id, count(*)
FROM Students
GROUP BY group_id;
Now, query group_id’s with more than 20 students.
SELECT group_id, count(*)
FROM Students
GROUP BY group_id
HAVING count(*) > 20;

Example with join
SELECT g.name as group_name,
count(*) as number_of_students
FROM Students s, Groups g
WHERE s.group_id=g.group_id
GROUP BY g.name
HAVING count(*) > 20;

ORDER BY
The ORDER BY clause is used to set the ordering of the resultant table.
Columns may be sorted in ASCending or DESCending order.
Example: Query the first and last name of each student in ascending order of their last names
SELECT fname, lname
FROM Students
ORDER BY lname ASC;

Ordering Results in SQL: example
• The ordering of results may be mixed in query: one column may be sorted in ascending order while another column may be sorted in descending order.
For the previous query, sort the results in ascending order of last names and descending order of first names:
SELECT fname, lname
FROM Students
ORDER BY lname ASC, fname DESC;

Example with join
SELECT g.name as group_name, count(*) as number_of_students
FROM Students s, Groups g
WHERE s.group_id=g.group_id
GROUP BY g.name
HAVING count(*) > 20
ORDER BY g.name ASC;

SELECT Statement
SQL allows us to query data using select statements. Syntax:
SELECT attribute(s)
FROM table(s)
WHERE selection condition(s);
16

Complete SELECT Statement
SELECT attribute(s)
FROM table(s)
[WHERE selection condition(s)]
[GROUP BY condition(s)]
[HAVING condition(s)]
[ORDER BY condition(s)]

Select Statement Summary

String Functions and Operators

String Functions and Operators

Date Functions
EXTRACT (field FROM source)
EXTRACT function retrieves subfield such as year or hour from date/time values.
Source must be a value expression of date type.
Field is an identifier or string that selects what field to extract from the source value.

Date Functions
date_part (‘field’, source)
Source must be a value expression of date type.
Field is an identifier or string that selects what field to extract from the source value.

Date Functions
Fields:
century
year
month
week
day
decade
quarter
dow (the day of the week) / isodow
doy (day of the the year)
hour
minute
second
etc.

EXTRACT / date_part examples
SELECT EXTRACT(year FROM bdate)
FROM Students;
SELECT date_part('year', bdate)
FROM Students;

Date Functions
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
Example:
SELECT CURRENT_DATE;