1 / 19

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.

hmahoney
Download Presentation

Introduction to SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Introduction to SQL Lecture 5

  2. 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

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

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

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

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

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

  8. 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 ;

  9. 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;

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

  11. 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;

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

  13. DELETE DELETE FROM Depts WHERE Budget < 1000000;

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

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

  16. 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)

  17. 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

  18. ER for the suppliers-parts problem Supplier Parts Contracted

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

More Related