Practical Database Concepts
Lecture 7: SQL Continued Santha Sumanasekara April 2020
Overview of the lecture
Grouped Aggregation
GROUP BY and HAVING
Set Operations
UNION, INTERSECT, EXCEPT (or MINUS in Oracle)
Views
How Dates and Times handled in SQL
Dates and Times in SQLite, Oracle, and SQL Server
Grouped Aggregation
Grouped Aggregation
Consider the following query:
SELECT COUNT(*) FROM country;
What if some one asked about the number of countries in each continent?
Easily we can do that for one continent (and, repeat it for each and every continents.)
SELECT COUNT(*)
FROM country
WHERE continent = ‘Oceania’;
Grouped Aggregation
There is a better way of doing this, using GROUP BY clause.
SELECT continent, COUNT(*) AS “No. Countries” FROM country
GROUP BY continent;
The resultant countries are grouped by Aggregation is done per each this attribute. group (of rows).
SELECT
FROM country GROUP BY continent;
continent, COUNT(*) AS “No. Countries”
When using GROUP BY clause, we can only have GROUP BY attribute(s) and aggregate functions within SELECT clause.
No other attributes or expressions are permitted.
Grouped Aggregation
Show me the list of continents with the number of countries. List the country names within each continent.
SELECT continent, name, COUNT(*) AS “No. Countries” FROM country
GROUP BY continent;
SQLite does not comply with SQL
Does this query work? What is the problem?
standard here. Oracle does.
‘name’ is not a group-by attribute.
HAVING clause
In a normal query, a WHERE clause is used to add a condition that each row in the result is checked against.
If you require to check if a whole group (generated by GROUP BY) meets a particular condition, we use HAVING clause.
It must always accompany with a GROUP BY clause.
E.g. Display the countries with the number of languages spoken where there
are more than one languages.
Join Country and CountryLanguage tables
Group result on Name of the country.
Count No of rows in each group.
Display the result only if this count is greater than 1.
HAVING clause
SELECT c.name, COUNT(*)
FROM country c, countryLanguage cl WHERE c.code = cl.countrycode GROUP BY c.name
HAVING COUNT(*) > 1;
First, group rows by country name.
Then, check each group on how many rows in the group. Show only groups that have more than one row.
HAVING clause
First, join Country & CountryLanguage
Group rows by country name.
Show only the groups having count greater than 1. Note that Anguilla has disappeared from the result
SET Operators in SQL
Set Operations in SQL
The result of an SQL query can be considered as a “Set of Rows”.
So, we can apply set operations, such as UNION or INTERSECTION on
SQL queries.
This can be considered as another way of joining two or more result sets together.
Set Operations in SQL — Examples
List countries that speak either English or Spanish. List countries that speak both English and Spanish. List countries that speak English, but not Spanish.
Set Operations in SQL — Examples
We can visualise these queries and their intended results in a Venn diagram. A represents English-speaking countries, B represents Spanish-speaking
countries.
UNION
INTERSECTION
EXCEPT / MINUS
Countries that speak either English or Spanish.
Countries that
speak both
English and
Countries that
speak English,
but not
Spanish.
Spanish.
https://lucidchart.zendesk.com/hc/en-us/community/posts/115000376483-Venn-Diagrams-
with-Union-Intersection-in-different-color
Set Operations in SQL
Use UNION operator to select rows that are in result 1 OR result 2.
Use INTERSECT operator to select rows that are in result 1 AND result 2.
Use EXCEPT operator to select rows that are in result 1 BUT NOT IN result 2.
Very Important: Oracle has a different keyword for this: It is MINUS in Oracle.
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘English’;
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘Spanish’;
UNION
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘English’
UNION
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘Spanish’;
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘English’;
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘Spanish’;
INTERSECT
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘English’
INTERSECT
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘Spanish’;
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘English’;
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘Spanish’;
EXCEPT
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘English’
EXCEPT
SELECT c.name
FROM country c, countrylanguage cl WHERE c.code = cl.CountryCode AND cl.Language = ‘Spanish’;
Replace EXCEPT with MINUS in Oracle.
Views
Views
Views are kind of virtual tables, allow users to do the following:
Structure data in a way that users find natural or intuitive.
Restrict access to the data such that a user can only see limited data instead of complete table.
Summarise data from various tables which can be used to generate reports.
Views are defined using an SQL statement. So, you may consider a view as a stored query, assigned with a name (and also a virtual schema derived from the underlying SQL statement)
Views — Examples
In the world database, we have information about continents hidden in the Country table. We can derive these data and store as a view, called Continent
Country table has a number of columns that are of least importance to a sales manager. We can define a view, called CountryLite, for the sole use of sales managers.
We have frequently joined Country and City tables to retrieve information required. We can define a view by joining these two tables, then, users are not required to do the join every time they run a query.
Views: Create a view
In the world database, we have information about continents hidden in the Country table. We can derive these data and store as a view, called Continent.
Consider the following SQL statement:
SELECT c.continent, COUNT(*), SUM(c.population) FROM country c
GROUP BY c.continent;
These two aggregate functions generate summarised data for each continent.
Views: Create a view
Define a view using the above query:
CREATE VIEW continent (name, NumCountries, population) AS
SELECT c.continent, COUNT(*), SUM(c.population) FROM country c
GROUP BY c.continent;
This is the defining SQL query for the view.
Views: Using a view
A view can be in the same way as a table!
SELECT * FROM continent;
Views: Create a view
Consider another example: Let’s suppose that we require a smaller version of the Country table, along with capital name embedded in.
Consider the following SQL statement:
SELECT c.code, c.name, c.population, cc.name FROM country c, city cc
WHERE c.capital = cc.id;
Use this query to define a view – countryLite (code, name, population, capital).
Views: Create a view
Define a view using the above query:
CREATE VIEW countryLite( code, name, population, capital) AS
SELECT c.code, c.name, c.population, cc.name FROM country c, city cc
WHERE c.capital = cc.id;
SELECT *
FROM countryLite;
Views: A note on efficiency
While views could be used to return results similar in a way to a subselect, they are also inefficient in the same ways:
SELECT *
FROM countryLite
WHERE population < 100,000;
y
, then
CREATE VIEW countryLite( code, name, population, capital) AS
SELECT c.code, c.name, c.population, cc.name FROM country c, city cc
WHERE c.capital = cc.id;
T
T
h
a
i
qu
r
’s
uery on the result.
ng it’s
g
o
u
hi
b
e
e
n
w
is
e
o
tt
s
s
q
r
v
n
n
in
ul
q
y
e
f
in
q
g
t.
u
ue
g
e
n
ir
th
r
ry
t
s
it
yi
h
t,
e
r
e
t
s
o
r
g
o
v
e
e
w
t
u
h
ie
tt
su
n
n
e
w
n
ing
lt
q
,
u
le
in
ab
th
f
g
ti
th
e
o
e
t
s
e
ry
h
ve
n
o
r
e
re
ru
u
f
n
q
nn
s
ir
n
ue
u
t
s
n
h
l
t
i
in
t
,
e
r
g
the
r
Dates and Times in SQL
- How Dates and Times handled in SQL? Dates and Times in SQLite
Dates and Times in Oracle
Dates and Times in SQL Server
What is SQL standard in Dates and Times?
- Handling of dates and times in SQL varies greatly between database platforms.
- Here we demonstrate how Oracle and SQLite handle them.
- Refer to Programmers Manual of the database you use.
The date/time precision in Microsoft SQL Server is 100 nanoseconds. Oracle has the data type TIMESTAMP which has a precision of 1/100,000,000th of a second. Oracle also has a DATE data type that stores date and time values accurate to one second.
Dates and Times in SQLite
SQLite
SQLite does not have types for dates and times. You can use the built-in Date And Time Functions to store dates and times as TEXT, REAL, or INTEGER values:
- TEXT as strings in format “YYYY-MM-DD HH:MM:SS.SSS”
- REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar (in plain English: the Gregorian calendar extended backwards)
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
No Date/ Time data types in SQLite!
- SQLite has 5 primitive data types, referred to as storage classes.
- NULL, INTEGER, REAL, TEXT, BLOB.
- Other more complex data types can be derived using these primitive types.
SQLite leverages some built-in date and time functions to use other storage classes such as TEXT, REAL, or INTEGER for storing the date and time values.
No Date/ Time data types in SQLite!
- SQLite does not have a storage class set aside for storing dates and/or times.
- Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
- TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
- REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Creating tables with dates and
times
CREATE TABLE borrow (
txNumb DECIMAL(10), mvNumb DECIMAL(4), mmbNumb DECIMAL (4), duedate text, returnTimestamp real, PRIMARY KEY (txNumb)
)
Stores dates and times in ISO 8601 format.
Stores date/time as a real number, elapsed from November 24, 4714 B.C.
Inserting dates and times
- Use “datetime()” and “julianday()” functions to convert simple strings into correct date/ time format.
INSERT INTO borrow VALUES
(1, 9999, 1234, DATETIME('2017-04-03'),
JULIANDAY('2017-03-29 13:35:17.123'));
Displaying dates and times
- Use DATE, TIME, DATETIME functions to extract components of a date, as follows.
-
SELECT DATE(duedate) FROM borrow;
SELECT DATE(returnTimestamp), TIME(returnTimestamp)
FROM borrow;
SELECT returnTimestamp FROM borrow;
does not produce any human readable date. It will only display the date as a number.
Formatting dates and times
- Use strftime() function to format dates and times.
- Syntax: strftime(format-string, date-time, [modifier ..])
- Format string allows you to specify the required date/time format.
- E.g. to display “duedate” in Australian Standard Date Format (DD/MM/YYYY), you may use:
- Strftime('%d/%m/%Y’, duedate)
- Display returnTimestamp’s hour and minute, you may use:
- Strftime(‘%H:%M, returnTimestamp)
- For more information on format strings, check out: https://www.sqlite.org/lang_datefunc.html
Formatting dates and times
- You can use ‘modifier’ attribute in the STRFTIME() function to change the
way the input is processed.
- For example, use ‘localtime’ modifier if you wish to display the time in local time zone. (if not used, the default is UTC)
- E.g. Strftime('%H:%M:%S', 'now', 'localtime')
Formatting dates and times
-- Available format strings:
Format Explanation
%d Day of the month (1-31)
%f Seconds with fractional seconds (SS.sss) %H Hour on 24-hour clock (00-23)
%j Day of the year (001-366)
%J Julian day number (DDDDDDD.ddddddd) %m Month (01-12)
%M Minute (00-59)
%s Seconds since 1970-01-01
%S Seconds (00-59)
%w Weekday (0-6)
(0=Sun, 1=Mon, ..., 6=Sat)
%W Week number in the year (00-53)
The first Monday is the beginning of week 1. %Y Year with century (yyyy)
Current date/ time
- Use the system variables NOW, as follows. -
Note that SQLite allowed you to run the SELECT statement without FROM clause.
SELECT date('now');
Dates and Times in Oracle
Oracle – Data Types
- date – contains date and time in second precision, no time zone
information
- timestamp – date and time with fractional seconds precision, no time zone information
- timestamp with time zone – the same as timestamp, but with time zone information
- timestamp with local time zone – the same as timestamp. The data is converted to database’s time zone but upon retrieval it is converted to the local session’s time zone
Creating tables with dates and
-
times
Create a sample table to demo the use of date types!
CREATE TABLE borrow (
txNumb DECIMAL(6),
mvNumb DECIMAL(4), mmbNumb DECIMAL(4), DueDate DATE, ReturnTimeStamp TIMESTAMP, PRIMARY KEY (txNumb)
);
Stores dates and times with low precision.
Very high precision.
Inserting dates and times
- Use “to_date()” and “to_timestamp()” functions to convert simple strings into correct date and timestamp format.
INSERT INTO borrow VALUES
(1, 9999, 1234, TO_DATE('3-APR-2017', 'DD-MON-YYYY'),
TO_TIMESTAMP('29-MAR-2017 13:35:17.12345', 'DD-MON-YYYY HH24:MI:SS.FF'))
Displaying dates and times
- You can define how Oracle displays dates and times. If you do not change the display settings, it displays them as follows:
03/APR/17 29/MAR/17 01:35:17.123450000 PM
- However, you can change this default format to another format, using NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT system variables.
Displaying dates and times
Displays Dates in Australian Date Format
- Use these variables for changing the current session, as follows.
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD/MM/YYYY hh24:mi:ss';
ALTER SESSION
SET NLS_TIMESTAMP_FORMAT =
'DD/ MM/ YYYY HH24:MI:SS.FF';
-
03/ 04/ 2017 00:00:00 29/ 03/ 2017 13:35:17.123450000
Displaying dates and times minute, second, etc
- Use EXTRACT function to extract components of a date, as follows.
SELECT EXTRACT (YEAR FROM duedate) FROM borrow;
-
Can have year, month, day, hour,
timezone_abbr,
SELECT EXTRACT (HOUR FROM returnTimeStamp), EXTRACT (MINUTE FROM returnTimeStamp)
FROM borrow;
Current date/ time
- Use the system variables SYSDATE, as follows. -
What is “dual” table? DUAL is a table automatically created by Oracle. It has one column – DUMMY VARCHAR2(1)
SELECT SYSDATE FROM dual;
SELECT TRUNC(duedate - SYSDATE) AS "Due in days" FROM borrow;
Dates and Times in SQL Server
SQL Server -- Data Types
SQL Server has four types for storing date and time:
- date – stores just the date
- datetime2 – date and time of day, with optional fractional seconds, no time zone offset
- datetimeoffset – a date and time of day with time zone awareness
- smalldatetime – a date and time of day, with seconds always zero (:00), without fractional seconds; no time zone offset
Creating tables with dates and
-
times
Create a sample table to demo the use of date types!
CREATE TABLE borrow (
txNumb DECIMAL(6),
mvNumb DECIMAL(4), mmbNumb DECIMAL(4), DueDate DATE, ReturnTimeStamp DATETIME2, PRIMARY KEY (txNumb)
);
Stores dates and times with low precision.
Very high precision.
Inserting dates and times
- Use “convert()” function to convert simple strings into correct date and timestamp format.
- Cast() function can also be used as long as input string is in default format.
Style Code
INSERT INTO borrow VALUES
(2, 9999, 1234, CONVERT(Date, '03/04/2014' ,103),
CONVERT(datetime2, '29 MAR 2017 13:35:17.12345', 113))
Style codes
Displaying dates and times
- You can define how SQL Server displays dates and times. If you do not change the display settings, it displays them in default format as follows:
Displaying dates and times
Displays Dates in Australian Date Format
- Use these variables for changing the current session, as follows.
-
SELECT txnumb, mvnumb, mmbnumb,
CONVERT (VARCHAR, duedate, 103), CONVERT (VARCHAR, returntimestamp, 113)
FROM borrow
Displaying dates and times minute, second, etc
- Use DATEPART function to extract the components of a date or datetime2, as follows.
SELECT DATEPART(yy, duedate) AS "Due Year" FROM borrow;
Can have year, month, day, hour,
timezone_abbr,
-
SELECT DATEPART(hh, returnTimeStamp) AS "Return Hour", DATEPART(mi, returnTimeStamp) AS "Return Min"
FROM borrow;
Current date/ time
Unlike Oracle, in SQL Server, we can run commands without referring to a table! No need of ‘dual’ table.
- Use the system variables GETDATE() and SYSDATETIME(), as follows. -
SELECT GETDATE(), SYSDATETIME();
SELECT DATEDIFF(dd, GETDATE(), duedate) AS "Due in days" FROM borrow;
This gives difference in days. You can use years, months, hours, minutes, etc.
Next Week ....
Triggers in SQL
Let’s build an application with database backend!