SQL for Sta s cians

Lecture 3

A rela on has a:

 Structural defini on
Rela on name
A set of a ribute names and their domains

 Data Part
Each row represents a data item

A rela onal database is a collec on of rela ons

Primary Key set – would include data that is unique in that table, eg id number, employee id.
It also cannot be null or else it will give you an error.
A rela onship is named associa on between a number of en es that is governed by certain
rules and some mes qualified with its own a ributes
Rela ng 2 tables together that rela onship can have its’ own par cular a ributes.
2 Important proper es of a rela onship design are:

 Cardinality
 Par cipa on
For a binary rela onship, the number of these cardinality possibili es is four namely:
1. One to one eg. Person to Passport (1 – 1)
2. One to many eg. Class to Student (1 – N)
3. Many to one (N – 1)
4. Many to many eg. Employee to Project (M – N)

The op ons are total, all instances must par cipate in at least one rela onship, par al does
not have to have a rela onship
Total Par cipa on – student MUST be enrolled to a course
Par al Par cipa on – Course CAN have one or more students or none
Foreign Keys are used to join tables or navigate between tables together. Specifically, foreign
keys build rela onships.
The a ributes in FK have the same domain(s) as the primary key a ributes PK
Value of FK in a table t1 of the current state r1(R1) either occurs as a value of PK for some tuple
t2 in the current state r2(R2) or is NULL
If the rela onship is total, then NULL values in the foreign key are prohibited. Therefore the
null constraint must be resent to implement a total rela onship

Each a ribute is associated with domain of values – datatypes in a Rela onal DBMS
Each datatype take space on physical storage
Eg. SELECT 1+2 AS;
SELECT abs(-2)AS;
We also need datatype conversion func ons for example from a number to string and vice
versa
SELECT cast(1+1 AS varchar);
SELECT cast(‘1’ AS integer) + cast(‘1’ AS integer);
Whenever a number is enclosed ‘’ it means that it’s actually a string type not integer
From date to an integer:
SELECT CURRENT_DATE ”Today is”;
SELECT cast(to_char(CURRENT_DATE,”J”)AS integer) ”ThatJulianDate”;
Some latent problems exists when comparing or conver ng datatypes:
Comparing with == and <> on FLOATs
Trunca on (i.e rounding dot zero)

Generic Types
Numeric: INTEGER, DECIMAL, REAL, SERIAL
Money: MONEY
Character: VARCHAR(n), TEXT, CHAR(n)
Date Time: TIMESTAMP, TIME DATE, INTERVAL
Booleans: BOOLEAN
We can also create our datatypes by crea ng new domains.
For example, the following domain describes our grade table:
CREATE DOMAIN uni_grade
CHAR(1) NOT NULL DEFAULT ’I’
CHECK (VALUE(‘A’,’B’,’C’,’D’,’F’,’I’));
Constraints so far:
NOT NULL – specifies that we cannot input a record and leave that a par cular a ribute
empty
UNIQUE – cannot have 2 records with the same value
CHECK - we define a par cular set of values and that field has to abide with that par cular
condi on
Referen al Constraints – introduce a foreign key between tables student and faculty
ALTER TABLE student
ADD CONSTRAINT stud_faculty_
FOREIGN KEY (sfaculty)
REFERENCES faculty(fid);

Lecture 4

Code:

CREATE TABLE university.course

(
course_id serial NOT NULL PRIMARY KEY,
course_name character(20),
credits integer
);

INSERT INTO university.course(course_name, credits)

VALUES ('Maths',4),('English',4)
CREATE TABLE university.grade
(
course_id integer NOT NULL references university.course(course_id),
student_id character(20) NOT NULL references university.student(student_id),
mark integer,
grade character(1),
Primary key(course_id, student_id)
);

insert into university.grade

values (1,1,90,'A')

Lecture 5
SELECT*FROM university.student

SELECT student_name, gender FROM university.student;

SELECT * FROM university.student

WHERE gender='Female' AND locality='Sliema'

SELECT * from university.student WHERE locality IN ('Sliema', 'A ard')

SELECT * FROM university.student WHERE locality='Sliema' OR locality='A ard'

SELECT * FROM university.student WHERE locality NOT IN ('Sliema')

SELECT DISTINCT locality FROM university.student

SELECT * FROM university.student WHERE date_of_birth >= '2002-03-06'

SELECT * FROM university.student WHERE date_of_birth BETWEEN '2002-03-06' and '2005-05-17'
AND locality='A ard'

SELECT * FROM university.student ORDER BY date_of_birth ASC

SELECT * FROM university.student ORDER BY date_of_birth DESC

SELECT student_name as "Name", student_id as "ID" FROM university.student

Lecture 6
Rela onal Joins
Are you used to join tables together when we’re selec ng our data

SELECT [DISTINCT] list of a ributes

FROM list of tables

WHERE row level selec on condi on expression

ORDER BY list of a ributes;

CJ Date database is well known with simple structure and easy to recall its data content that is it’s easy
to verify yourself that all is well with your query a empt (most likely schema for exam)

SPJ – work schedule

Inner Joins based on the Equality Operator

When we want to combine two tables together

Eg. Give work schedule details with product’s colour and weight for RED coloured parts. Note wrk.p is a
FK in table spj and prd.p is a PK in table p

SELECT wrk.s, wrk.j, prd.p, prd.colour, prd.weight (you need to state exactly the table of each field)

FROM date.spj wrk, date.p prd (you can give it a variable name)

WHERE wrk.p = prd.p

AND prd.colour = ‘RED’;

OR
Comparing Foreign Key to Primary Key to retrieve data when crea ng the join

SELECT wrk.s, wrk.j, prd.p, prd.colout, prd.weight

FROM date.spj wrk INNER JOIN Joining the spj and product table

date.p prd ON(wrk.p = prd.p)

AND prd.colour = ‘RED’;

eg. Give work schedule details with product’s colour and weight for non RED coloured parts, and
supplier’s city.

In the first join we have wrk.p is a FK in table spj and prd.p is a PK in table p, and in the second join
spls.s is a PK in table s and wrk.s is a FK in table spj

SELECT wrk.s, wrk.j, prd.p, prd.colour,

prd.weight, spl.city

FROM date.spj wrk INNER JOIN

date.p prd ON (wrk.p = prd.p)

INNER JOIN date.s spl ON(wrk.s = spl.s)

WHERE prd.colour <> ‘RED’);

We can add any amount of joins needed

SELECT wrk.s, wrk.j, prd.p, prd.colour, prd.weight, spl.city, job.city

FROM date.spj wrk INNER JOIN

date.p prd ON (wrk.p = prd.p) INNER JOIN

date.s spl ON(wrk.s = spl.s) INNER JOIN

date.j job ON (wrk.j = job.j)

WHERE prd.colour <> ‘RED’);

Code from Class

INSERT INTO university.student

VALUES

('4','Rachel Cutajar','Female', '23/04/2000', 'Pieta'),

('5','Monica Agius','Female', '10/03/2000', 'Zejtun'),

('6','Brian Bugeja','Male', '07/07/2000', 'Pieta');

INSERT INTO university.course (course_name, credits)

VALUES ('Maltese',4),('Religion',2),('Geography',2), ('History',2);

insert into university.grade values

(1,2,97,'A'),(1,3,85,'A'),

(1,4,70,'B'),(1,5, 62,'C'),

(2,1, 75,'B'),(2,2,73,'B'),

(2,3,65,'C'),(2,4,61,'C'),

(2,6, 45,'D'),(3,2,88,'A'),(3,3,76,'A'),

(3,4,70,'B'),(3,6, 61,'C'),(4,2,88,'A'),(4,3,76,'A'),

(4,4,70,'B'),(5,6, 61,'C'),(5,1, 60,'C'),(5,2, 64,'C'),(6,1, 70,'B'),

(6,2, 61,'C');

SELECT *

FROM university.grade

SELECT s.student_name, g.grade

FROM university.grade g , university.student s

WHERE g.student_id = s.student_id

SELECT s.student_name, g.grade

FROM university.grade g

INNER JOIN university.student s ON (g.student_id = s.student_id)

SELECT s.student_name, g.grade, c.course_name

FROM university.grade g

INNER JOIN university.student s ON (g.student_id = s.student_id)

INNER JOIN university.course c ON (g.course_id = c.course_id)

Lecture 7

Eg. Give details of work schedule for RED coloured parts and where jobs and parts are collocated. In
the first join we have wrk.p is a FK in table spj and prd.p is a PK in table p, in the second join spl.s is a
PK in table s and wrk.s is a FK int able spj and in the third join condi on we require coloca on – have
the same city value- between project and supplier

SELECT wrk.s, wrk.j, prd.p

Prd.colour, prd.weight, spl.city

FROM date.spj wrk INNER JOIN

Date.p prd ON (wrk.p=prd.p) INNER JOIN

Date.s spl ON (wrk.s = spl.s AND prd.city = spl.city)

WHERE prd.colour <>’RED’;

Outer Joins

What if we want to see all co-located parts and jobs and ensure that all the job ci es are men oned;
even if not co-located with any part

SELECT prd.p, job.j, job.city

FROM date.p prd RIGHT OUTER JOIN

date.j job ON prd.city=job.city

ORDER BY job.city;

RIGHT OUTER JOIN means keep all of the RIGHT table’s – i.e job tuples – a ribute values

Note: the ‘null’ values for columns that come from the non right table(s) where no match is sa sfied

IF you do not want to generate any nulls in our output THEN use the COALESCE() func on

SELECT COALESCE(prd.p, ‘NO PART!’) AS “prd.p”,

job.j, job.city

FROM date.p prd RIGHT OUTER JOIN

date.j job ON prd.city=job.city

ORDER BY job.city;
SELECT prd.p, job.j, prd.city

FROM date.p prd LEFT OUTER JOIN

date.j job ON prd.city=job.city

ORDER BY prd.city;

All matching records together with the le record, vice versa for the Right join and for the full
join is the matching records and everything else

SELECT prd.p, job.j

prd.city AS “PartCity”,

job.city AS “JobCity”

FROM date.p prd FULL OUTER JOIN

date.j job ON prd.city=job.city

ORDER BY prd.city;

Other Joins – Self Joins and Non Equi-Joins

Self Join

When we have a table and we are crea ng two instances of the same table

Self joins are self joins characterised by using a table more than once in the from list of tables list an
example will be pairs of projects that are co-located

First A empt

SELECT j1.j,j2.j,j1.city

FROM date.j j1 INNER JOIN

date.j j2 ON(j1.city = j2.city);

Second A empt i.e solve spurious data output

SELECT j1.j, j2.j, j1.city

FROM date.j j1 INNER JOIN

date.j j2 ON (j1.city = j2.city AND j1.j > j2.j);

Crea ng copies of the same table and comparing the a ributes – ge ng combina ons – to get rid of
the duplicates we use the greater than or the less than in the second a empt

The join comparison expression between a ributes uses a non equality operator; for example >.

Consider the query to name pairs of parts with one part ‘ having more weight’ than the second

SELECT ‘Part’,

p1.p

‘has more weight than’,

p2.p

‘by’,

p1.weight – p2.weight

FROM date.p p1 INNER JOIN

date.p2 ON (p1.weight > p2.weight)

ORDER BY p1.p, p2.p;

How can one build all possible pairs? This is an example of Cross Product

Output a list of all job’s ci es and product’s colours pairs

SELECT DISTINCT job.city, prd.colour

FROM date.j job, date.p prd % date. j is a schema

ORDER BY job.city, prd.colour;

SELECT DISTINCT job.city, prd.colour

FROM date.j job CROSS JOIN

date.p prd

ORDER BY job.city prd.colour;

Lecture 9

Nested Queries

Defined universal and existen al quan fica on queries

Existen al type – which parts are actually used

Universal type – which jobs supplied by all suppliers

Tend to provide data driven capability to a query language.

Nested SELECT statement:

SELECT [DISTINCT] list of a ributes

FROM list of tables

WHERE [row level selec on condi on expressions]

[Row level comparison (

SELECT [DISTINCT] list of a ributes

FROM list of tables

WHERE row level selec on condi on expressions

)]

[Row to aggregate level (

SELECT [DISTINCT] list of a ributes

FROM list of tables

WHERE row level selec on condi on expressions

)]

(The one with 3 nested queries is not going to be for the exam)

Views – are a convenient way to organize and rehash database data

Views are ideal for retrieving data from reports, forms, genera ng structure and content of datasets.

CREATE [ OR REPLACE ] [TEMP TEMPORARY] [RECURSIVE] VIEW name [(column_name [,…])]

[WITH (view_op on_name [=view_op on_value][,…])]

AS query

[WITH [CASCADED LOCAL CHECK] OPTION]