www.cardiff.ac.uk/medic/irg-clinicalepidemiology
SQL queries
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
in the last lecture, we studied relational algebra, which is the basis for query formation
from this lecture, we will start to consider how the data stored in your database may be queried using SQL
Oracle SQL documentation
https://docs.cs.cf.ac.uk/notes/oracle-in-the-school/
https://www.cs.cf.ac.uk/systems/applications/oracle/
Basic SQL query structure
the basic structure for querying relational databases using SQL is the following SELECT–FROM–WHERE block
SELECT attribute-list
FROM relations
WHERE conditions;
SELECT * FROM student;
SELECT student_name, degree, year_enrolled
FROM student;
SELECT student_name, degree, year_enrolled;
FROM student
WHERE degree = ‘BSc Computer Science’;
Sample tables
we will use the following tables to explain SQL
Module Enrollment
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
Querying single tables
we may select columns (equivalent to in algebra):
SELECT M#, title
FROM Module;
we may select rows (equivalent to in algebra):
FROM Enrollment
WHERE M# = ‘M101’;
* (asterisk) refers to
all columns
M100 Databases
M101 Accounting
M102 Electronics
S# M# date marks
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
Querying single tables
column and row selections may be combined
SELECT S#, marks
FROM Enrollment
WHERE M# = ‘M101’;
more complex conditions can be used in WHERE
SELECT S#, marks
FROM Enrollment
WHERE M# = ‘M101’ AND marks > 40;
the result may be ordered
SELECT S#, Marks
FROM Enrollment
ORDER BY Marks;
More on search conditions
use pattern matching commands:
SELECT title
FROM Module
WHERE title LIKE ‘D_t%’;
_ matches any one character
% matches zero or more characters
use range conditions:
SELECT S#, marks
FROM Enrollment
WHERE (marks BETWEEN 40 AND 70)
OR (S# IN (‘S1′,’S2′,’S5’));
Handling NULL value
the following query will return an empty result
FROM Enrollment
WHERE date = NULL;
IS should be used instead of =
FROM Enrollment
WHERE date IS NULL;
S# M# date marks
S6 M101 60
Aggregate functions
operate on a collection of values, but return a single, summarising value
data may be aggregated by these functions:
AVG, COUNT, MAX, MIN, SUM
SELECT AVG(marks)
FROM Enrollment;
or have the attribute in the result table renamed:
SELECT AVG(Marks) AS AverageMarks
FROM Enrollment;
AVG(marks)
AverageMarks
Grouping retrievals
use GROUP BY to aggregate over a subset of a table
SELECT M#, AVG(marks)
FROM Enrollment
GROUP BY M#;
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
M# AVG(marks)
Grouping retrievals
it is possible to use more than one aggregate function in the SELECT clause
SELECT M#, COUNT(*), AVG(Marks)
FROM Enrollment
GROUP BY M#;
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
M# COUNT(*) AVG(marks)
M100 2 75
M101 4 62.5
Selecting groups
it is possible to select which groups to aggregate
SELECT M#, COUNT(*), AVG(Marks)
FROM Enrollment
GROUP BY M#
HAVING COUNT(*) >= 3;
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
M# COUNT(*) AVG(marks)
M100 2 75
M101 4 62.5
Join tables
there is an explicit JOIN command in SQL, but more commonly the following is used
SELECT title, department
FROM Enrollment, Module
WHERE S# = ‘S2’
AND Enrollment.M# = Module.M#;
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
title department
Databases Computing
note that M# is ambiguous, so it needs to be prefixed with table names
Table aliases
using alias to rename tables can be very useful or necessary
SELECT A.M#, B.M#
FROM Module A, Module B
WHERE A.credit > B.credit;
this query cannot be performed without table aliases, as we effectively need two copies of the table Module
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
retrieve a list of module codes, together
with students (if any) who take them
SELECT title, S#
FROM Enrollment, Module
WHERE Enrollment.M# = Module.M#;
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
Databases S1
Databases S2
Accounting S3
Accounting S4
Accounting S5
Accounting S6
Electronics is not listed!
Outer join
to output every row in a join operation, we use outer join
SELECT title, S#
FROM Enrollment, Module
WHERE Enrollment.M# (+) = Module.M#;
(+) should be used to “add” missing tuples
note: (+) notation is only used in Oracle,
which is vendor specific
Databases S1
Databases S2
Accounting S3
Accounting S4
Accounting S5
Accounting S6
Electronics
Table union
union of two tables is the
set of tuples in either, but
tuples common to both
will be listed once only
FROM Enrollment
WHERE marks > 80
FROM Module
WHERE Title = ‘Database’;
note that the two tables
must be union compatible
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
Table intersection
the set of all tuples that
are in both tables
FROM Enrollment
WHERE marks > 80
FROM Module
WHERE title = ‘Accounting’;
again, the two tables must
be union compatible
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
Table difference
the set of all tuples that
are in the first table, but
not in the second
FROM Enrollment
WHERE marks > 80
FROM Module
WHERE title = ‘Databases’;
again, the two tables must
be union compatible
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
Subqueries
the WHERE clause may contain another query
SELECT S#, M#, marks
FROM Enrollment
WHERE M# IN (
FROM Module
WHERE credits = 10
note that subqueries
can be nested to any depth
can be used in HAVING
(some) can be replaced
S# M# marks
S1 M100 50
S2 M100 100
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
Correlated subqueries
if we want to execute the subquery
once for each tuple fetched by the
parent query, we use a correlated
SELECT S#, M#, marks
FROM Enrollment E1
WHERE marks >=
(SELECT AVG(marks)
FROM Enrollment E2
WHERE E1.M# = E2.M#);
the subquery is executed for each
row in the Enrollment table
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
AVG(marks)
S# M# marks
S2 M100 100
S4 M101 80
S5 M101 90
AVG(marks)
we use EXISTS to test for
the existence of a tuple
in a subquery
SELECT M#, title
FROM Module M
WHERE EXISTS
FROM Enrollment E
WHERE E.M# = M.M#);
the EXISTS command in SQL
corresponds to the existential
quantifier in mathematical
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
S# M# date marks
S1 M100 02-Jan-1999 50
S2 M100 03-Feb-1999 100
S3 M101 04-Mar-1999 20
S4 M101 05-Apr-1999 80
S5 M101 06-May-1999 90
S6 M101 60
M100 Databases
M101 Accounting
NOT EXISTS
we use NOT EXISTS for the opposite effect
SELECT M#, title
FROM Module M
WHERE NOT EXISTS
FROM Enrollment E
WHERE E.M# = M.M#);
M102 Electronics
Scalar functions
operate on a single value and return a single value
SELECT SUBSTR(title, 1, 4)
FROM module
WHERE department = ‘Computing’;
numeric string others
ROUND LOWER TO_CHAR
TRUNC UPPER TO_NUMBER
LOG10 SUBSTR DECODE
… … …
M# title department credits
M100 Databases Computing 10
M101 Accounting Business 20
M102 Electronics Engineering 30
SUBSTR(title, 1, 4)
DELETE, UPDATE and INSERT
DELETE FROM Module
WHERE M# = ‘M104’;
UPDATE Module
SET M# = ‘C’ || M#
WHERE department = ‘Computing’;
INSERT INTO Student(name, address)
SELECT name, address
FROM LifeLongLearning
WHERE address LIKE ‘%Cardiff%’;
deletes an entire row!
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com