Introduction to SQL

Introduction to SQL Lecture 5

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

Operations • create table • restrict, project and join (via SELECT) • insert, update, delete • create view, query view

CREATE TABLE CREATE TABLE Depts ( Dept_id CHAR(2), Dept_name CHAR(20), Budget DECIMAL(8), PRIMARY KEY (Dept_id));

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);

restrict (via SELECT) SELECT ( E_id, E_name, Dept_id, Salary ) FROM Emps WHERE Salary > 33000;

project (via SELECT) SELECT ( E_name, Salary ) FROM Emps ;

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 ;

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;

INSERT single row INSERT INTO Emps ( E_id, E_name, Dept_id, Salary ) VALUES ( ‘E1’, ‘Smith’, ‘D1’, 40000 ) ;

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;

UPDATE UPDATE Emps SET Salary = Salary + 900 WHERE Salary < 40000;

DELETE DELETE FROM Depts WHERE Budget < 1000000;

CREATE VIEW CREATE VIEW All_emps AS SELECT (Dept_name, E_name, Salary) FROM Depts, Emps WHERE Depts.Dept_id = Emps.Dept_id

query a view SELECT ( E_name, Salary ) FROM All_emps WHERE Salary < 40000

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)

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

ER for the suppliers-parts problem Supplier Parts Contracted

Conclusion • summary • introduction to database systems • introduction to the relational model • introduction to SQL • from next lecture • the relational model

Introduction to SQL