Transcript Template file - Goldsmiths, University of London

Introduction to SQL
Introduction to SQL
Lecture 5
1
Introduction to SQL
Note
 in different implementations
 the syntax might slightly differ
 different features might be available
 in certain implementations
 non-relational operators might be supported
 certain relational operations might not be
possible to be performed
2
Introduction to SQL
Operations
 create table
 restrict, project and join (via SELECT)
 insert, update, delete
 create view, query view
3
Introduction to SQL
CREATE TABLE
CREATE TABLE Depts (
Dept_id
CHAR(2),
Dept_name CHAR(20),
Budget
DECIMAL(8),
PRIMARY KEY (Dept_id));
4
Introduction to SQL
CREATE TABLE
CREATE TABLE Emps (
E_id
CHAR(2),
E_name
CHAR(20),
Dept_id
CHAR(2),
Salary
DECIMAL(5),
PRIMARY KEY (E_id),
FOREIGN KEY (Dept_id) REFERENCES Depts);
5
Introduction to SQL
restrict (via SELECT)
SELECT ( E_id, E_name, Dept_id, Salary )
FROM Emps
WHERE Salary > 33000;
6
Introduction to SQL
project (via SELECT)
SELECT ( E_name, Salary )
FROM Emps ;
7
Introduction to SQL
join (via SELECT)
SELECT ( Depts.Dept_id, Dept_name, Budget, E_id,
E_name, Salary )
FROM Emps, Depts
WHERE Depts.Dept_id = Emps.Dept_id ;
8
Introduction to SQL
restrict, project and join (via SELECT)
SELECT ( Dept_name, E_name, Salary )
FROM Emps, Depts
WHERE Depts.Dept_id = Emps.Dept_id AND
Salary > 33000;
9
Introduction to SQL
INSERT single row
INSERT
INTO Emps ( E_id, E_name, Dept_id, Salary )
VALUES ( ‘E1’, ‘Smith’, ‘D1’, 40000 ) ;
10
Introduction to SQL
INSERT multiple rows
CREATE TABLE Temp (
Id
CHAR(2),
Name
CHAR(20),
Salary
DECIMAL(5),
PRIMARY KEY (Id) );
INSERT
INTO Temp ( Id, Name, Salary )
SELECT ( E_id, E_name, Salary)
FROM Emps
WHERE Salary > 33000;
11
Introduction to SQL
UPDATE
UPDATE Emps
SET Salary = Salary + 900
WHERE Salary < 40000;
12
Introduction to SQL
DELETE
DELETE
FROM Depts
WHERE Budget < 1000000;
13
Introduction to SQL
CREATE VIEW
CREATE VIEW All_emps AS
SELECT (Dept_name, E_name, Salary)
FROM Depts, Emps
WHERE Depts.Dept_id = Emps.Dept_id
14
Introduction to SQL
query a view
SELECT ( E_name, Salary )
FROM All_emps
WHERE Salary < 40000
15
Introduction to SQL
Intermediate conclusion
 you now know a small set of operators
by means of which you can create,
query and modify a database; you can
now implement your own database
(even though, it probably will be far from
a good design)
16
Introduction to SQL
Activity A4
You are the database administrator of your company.
Your company is supplied with different parts by a set
of supplying companies. You need to create and
maintain a database with information about the
supplied parts and the supplying companies.
1. given the ER (entity relationship) diagram, identify
the attributes of each entity (NOTE: treat the
relationship as an entity, i.e. find its attribute(s))
2. design the corresponding tables
3. write the data definition statements in SQL
17
Introduction to SQL
ER for the suppliers-parts problem
Supplier
Contracted
Parts
18
Introduction to SQL
Conclusion
 summary
 introduction to database systems
 introduction to the relational model
 introduction to SQL
 from next lecture
 the relational model
19