💡Recall

🖋 Notes

Managing Relational Databases with SQL

`CREATE TABLE`

Syntax

defines a new table

CREATE TABLE Students(
	sid INT PRIMARY KEY,
	cnp CHAR(13),
	lastname VARCHAR(50),
	firstname VARCHAR(50) DEFAULT 'TBA',
	age INT 
	CHECK (age >= 18)
)

restrictions associated with a column:
- NOT NULL ⇒ can't have undefined values
- PRIMARY KEY ⇒ the column is a primary key
- UNIQUE ⇒ the values in the column are unique
- CHECK(condition) ⇒ the condition that must be satisfied by the column's values (simple conditions that evaluate to true or false)
- FOREIGN KEY REFERENCES parent_table[(column_name)] [ON UPDATE action] [ON DELETE action]
restrictions associated with a table:
- PRIMARY KEY(column_list) ⇒ defining the primary key for the table
- UNIQUE(column_list) ⇒ the values in the column list are unique
- CHECK(condition) ⇒ the condition that must be satisfied by a row
- FOREIGN KEY(column_list) REFERENCES parent_table[(column_list)][ON UPDATE action][ON DELETE action]
possible actions for a foreign key:
- NO ACTION ⇒ the operation is not allowed if it violates integrity constraints
- SET NULL ⇒ the foreign key value is set to null
- SET DEFAULT ⇒ the foreign key value is set to the default value
- CASCADE ⇒ the delete / update is performed on the parent table, but it generates corresponding deletes / updates in the child table

Example

CREATE TABLE Programs
(id SMALLINT PRIMARY KEY,
name VARCHAR(70))

CREATE TABLE Students
(program SMALLINT REFERENCES Programs(id),
reg_number CHAR(10),
last_name CHAR(30),
first_name CHAR(30),
cnp CHAR(13) UNIQUE,
PRIMARY KEY(program, reg_number))

CREATE TABLE Groups
(acad_year SMALLINT,
program SMALLINT REFERENCES Programs(id),
year_of_study SMALLINT,
id CHAR(10),
PRIMARY KEY (acad_year, program, id))

CREATE TABLE Courses
(id CHAR(10) PRIMARY KEY,
name VARCHAR(70))

CREATE TABLE Trajectory
(id INT PRIMARY KEY IDENTITY(1,1),
acad_year SMALLINT,
program SMALLINT,
reg_number CHAR(10),
sgroup CHAR(10),
FOREIGN KEY(program, reg_number) REFERENCES
Students(program, reg_number),
FOREIGN KEY(acad_year, program, sgroup)
REFERENCES Groups(acad_year, program, id))

CREATE TABLE Results
(id INT PRIMARY KEY IDENTITY(1,1),
program SMALLINT,
reg_number CHAR(10),
acad_year SMALLINT,
semester SMALLINT,
course CHAR(10) REFERENCES Courses(id),
no_credits DECIMAL(3,1),
grade SMALLINT,
FOREIGN KEY(program, reg_number) REFERENCES
Students(program, reg_number))

CREATE TABLE Universities
(UnivID SMALLINT PRIMARY KEY IDENTITY(1,1),
UnivName NVARCHAR(100) NOT NULL,
Country NVARCHAR(40))

CREATE TABLE ResearchTeams
(RTID SMALLINT IDENTITY(1,1),
RTName NVARCHAR(100) UNIQUE,
CONSTRAINT PK_ResearchTeams PRIMARY KEY(RTID))

CREATE TABLE ResearchTeams_Universities
(RTID SMALLINT FOREIGN KEY REFERENCES
ResearchTeams(RTID) ,
UnivID SMALLINT REFERENCES Universities(UnivID),
PRIMARY KEY(RTID, UnivID))

CREATE TABLE SensorTypes
(STID TINYINT PRIMARY KEY IDENTITY(1,1),
STName NVARCHAR(50) UNIQUE,
STBriefDescription NVARCHAR(300) DEFAULT 'TBW')

CREATE TABLE Birds
(BID INT PRIMARY KEY IDENTITY(1,1),
BNickName NVARCHAR(50),
Species VARCHAR(100),
RTID SMALLINT,
CONSTRAINT FK_Birds_ResearchTeams FOREIGN KEY(RTID)
REFERENCES ResearchTeams(RTID))

CREATE TABLE Sensors
(SensorID INT PRIMARY KEY IDENTITY(1,1),
STID TINYINT REFERENCES SensorTypes ON DELETE NO ACTION
ON UPDATE CASCADE,
BirdID INT REFERENCES Birds(BID))

CREATE TABLE SensorData
(SDID INT PRIMARY KEY IDENTITY(1,1),
SensorID INT REFERENCES Sensors(SensorID),
SensorDataValue REAL,
SensorDataTime DATETIME2)

CREATE TABLE Researchers
(RID SMALLINT PRIMARY KEY IDENTITY(1,1),
RFName NVARCHAR(90) NOT NULL,
RLName NVARCHAR(90) NOT NULL,
RTID SMALLINT REFERENCES ResearchTeams(RTID))

CREATE TABLE PublishedPapers
(PID INT PRIMARY KEY IDENTITY(1,1),
Title NVARCHAR(200),
Conference NVARCHAR(200))

CREATE TABLE Researchers_PublishedPapers
(RID SMALLINT REFERENCES Researchers(RID) ,
PID INT REFERENCES PublishedPapers(PID),
PRIMARY KEY(RID, PID))

`ALTER TABLE`

Syntax

changes the structure of a defined table

ALTER TABLE Students
ADD FavSymphony VARCHAR(50)

possible operations (differences among DBMSs)
- add / change / remove a column
  - ADD column_definition
  - {ALTER COLUMN | MODIFY} column_definition
  - DROP COLUMN column_name
- add / remove a constraint
  - ADD [CONSTRAINT constraint_name] PRIMARY KEY(column_list)
  - ADD [CONSTRAINT constraint_name] UNIQUE(column_list)
  - ADD [CONSTRAINT constraint_name] FOREIGN KEY (column_list) REFERENCES table_name[(column_list)] [ON UPDATE action] [ON DELETE action]
  - DROP [CONSTRAINT] constraint_name

`DROP TABLE`

Syntax

destroys a tablE

DROP TABLE table_name

DROP TABLE Students

`INSERT`

Syntax

adding records

INSERT INTO table_name[(column_list)] VALUES (value_list)
INSERT INTO table_name[(column_list)] subquery, -- where subquery refers to a set of records (generated with the SELECT statement)

INSERT INTO Students (sid, cnp, lastname, firstname, age)
VALUES (1, '123456789012', 'Popescu', 'Maria', 20)

`UPDATE`

Syntax

changing records
the command changes the records in the table that satisfy the condition in the WHERE clause
if the WHERE clause is omitted, all the records in the table are changed; the values of the columns specified in SET are changed to the associated expressions' values
```
UPDATE table_name
SET column_name = expression [, column_name=expression] ...
[WHERE condition]

UPDATE Students
SET age = age + 1
WHERE cnp = '123456789012'
```

`DELETE`

Syntax

removing records
the command deletes the records in the table that satisfy the condition in the WHERE clause

if the WHERE clause is omitted, all the table's records are deleted

DELETE FROM table_name
[WHERE condition]

DELETE
FROM Students
WHERE lastname = 'Popescu'

Filter Conditions

expression comparison_operator expression
expression [NOT] BETWEEN valmin AND valmax
expression [NOT] LIKE pattern ("%" - any substring, "_" - one character)
expression IS [NOT] NULL
expression [NOT] IN (value [, value] ...)
expression [NOT] IN (subquery)
expression comparison_operator {ALL | ANY} (subquery)
[NOT] EXISTS (subquery)
(condition)
NOT condition
condition1 AND condition2
condition1 OR condition2

3-Valued Logic

truth values: true, false, unknown

Querying Relational Databases Using SQL

`SELECT` Statement

Students[sid, sname, email, age, sgroup]
Courses[cid, cname, credits]
Exams[sid, cid, grade]

-- Q1. Find the 21-year-old students in the Students table.
SELECT * 
FROM Students S
WHERE S.age = 21

-- Q2. Find the names and email addresses of all the 21-year-old students in the Students table. Eliminate duplicates.
SELECT DISTINCT S.sname, S.email
FROM Students S
WHERE S.age = 21

Range variable

alias used for a table in a SQL query
needed when a relation appears more than once in the FROM clause (to solve the ambiguity)
it is good style to always use range variables

Pirates [PID, Name, Reputation, … ]
Treasures [TID, Name, Description, Category, … ]
Shares [PID, TID, Value]

SELECT *
FROM Pirates P, Shares AS S
WHERE P.PID == S.PID AND S.Value > 10

SELECT * -- Name, Reputation
FROM Pirates P
WHERE P.reputation > 1 AND P.reputation < 4

-- Q3. Find the "10" grades (student name, course id).
SELECT S.sname, E.cid
FROM Students S, Exams E
WHERE S.sid = E.sid AND E.grade = 10

SELECT sname, cid
FROM Students, Exams
WHERE Students.sid = Exams.sid AND grade = 10

Arithmetic expressions and the `LIKE` operator

AS and = can be used to name fields in the result set
LIKE operator is used for string pattern matching:

-- Q4. For all students whose name starts and ends with B and has at least 3 characters, retrieve the following data: student age, student age – 18, student age * 2.
SELECT S.age, age1 = S.age-18, 2*S.age AS age2
FROM Students S
WHERE S.sname LIKE 'B_%B'

SELECT *
FROM Pirates AS P
WHERE P.Name LIKE '%_ _ _ escu' 

SELECT P.PID, Pension = P.Reputation * 10 + 5 -- must be scaler
FROM Pirates AS P
WHERE P.Name LIKE '%_ _ _ escu'

Set operations

`UNION`(or)

compute the union / intersection / difference of any 2 union-compatible sets of tuples (results of SQL queries)
duplicate rows are eliminated
UNION ALL doesn't eliminate duplicates

-- Q5. Find the ids of students who are older than 20 or have a grade in the Alg1 course.
SELECT S.sid
FROM Students S
WHERE S.age > 20
UNION
SELECT E.sid
FROM Exams E
WHERE E.cid = 'Alg1'
--UNION ALL doesn't eliminate duplicates

-- Names of the pirates that have shares in treasure 1 or treasure 2
SELECT P.Name
FROM Pirates P, Shares S, Treasures T
WHERE P.PID == S.PID AND T.TID == S.TID AND T.Category = 1
UNION
SELECT P2.Name
FROM Pirates P2, Shares S2, Treasures T2
WHERE P2.PID == S2.PID AND T2.TID == S2.TID AND T2.Category = 2

`INTERSECT`(and)

compute the intersection of any 2 union-compatible sets of tuples (results of SQL queries)
duplicate rows are eliminated

-- Q6. Find the ids of students who received a grade in both a 4 credits course and a 5 credits course.
SELECT E.sid
FROM Exams E, Courses C
WHERE E.cid = C.cid AND C.credits = 4
INTERSECT
SELECT E2.sid
FROM Exams E2, Courses C2
WHERE E2.cid = C2.cid AND C2.credits = 5

-- Names of the pirates that have shares in treasure 1 or treasure 2
SELECT P.Name
FROM Pirates P, Shares S, Treasures T
WHERE P.PID == S.PID AND T.TID == S.TID AND T.Category = 1
INTERSECT
SELECT P2.Name
FROM Pirates P2, Shares S2, Treasures T2
WHERE P2.PID == S2.PID AND T2.TID == S2.TID AND T2.Category = 2

`EXCEPT`(but)

compute the difference of any 2 union-compatible sets of tuples (results of SQL queries)
duplicate rows are eliminated

-- Q7. Find the ids of students who received a grade in a 4 credits course, but have no grades in 5 credits courses.
SELECT E.sid
FROM Exams E, Courses C
WHERE E.cid = C.cid AND C.credits = 4
EXCEPT
SELECT E2.sid
FROM Exams E2, Courses C2
WHERE E2.cid = C2.cid AND C2.credits = 5

Nested queries

Lecture 4 - Querying Relational Databases Using SQL

💡Recall

🖋 Notes

Managing Relational Databases with SQL

CREATE TABLE

Syntax

Example

ALTER TABLE

Syntax

DROP TABLE

Syntax

INSERT

Syntax

UPDATE

Syntax

DELETE

Syntax

Filter Conditions

3-Valued Logic

Querying Relational Databases Using SQL

SELECT Statement

Range variable

Arithmetic expressions and the LIKE operator

Set operations

UNION(or)

INTERSECT(and)

EXCEPT(but)

Nested queries

`CREATE TABLE`

`ALTER TABLE`

`DROP TABLE`

`INSERT`

`UPDATE`

`DELETE`

`SELECT` Statement

Arithmetic expressions and the `LIKE` operator

`UNION`(or)

`INTERSECT`(and)

`EXCEPT`(but)