1 / 17

SQL: Structured Query Language – Part 2

SQL: Structured Query Language – Part 2. Queries : SELECT Data Manipulation: INSERT, UPDATE, DELETE. VW: new database. Look at this database: ..lektion03 (SQL2)vwDatabase.pdf Create the database. These scripts may be helpful. SQL: Data Manipulation Language. SELECT UPDATE INSERT

talisa
Download Presentation

SQL: Structured Query Language – Part 2

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. SQL: Structured Query Language – Part 2 Queries: SELECT Data Manipulation: INSERT, UPDATE, DELETE FEN 2014-02-07

  2. VW: new database • Look at this database:..\lektion03 (SQL2)\vwDatabase.pdf • Create the database. These scripts may be helpful. FEN 2014-02-07

  3. SQL: Data Manipulation Language • SELECT • UPDATE • INSERT • DELETE • All work on tables FEN 2014-02-07

  4. Queries: SELECT • Syntax: SELECT <attribute-list> FROM <tables> [WHERE <condition>] [GROUP BY <attribute-list>] [HAVING <condition>] [ORDER BY <attribute-list>] [...]: WHERE, GROUP BY, HAVING and ORDER BY may be omitted. FEN 2014-02-07

  5. Examples: Company (Q0): Row and column selection: SELECT Bdate, Address FROM Employee WHERE Fname= ’John’ AND Minit = ’B’ AND Lname = ’Smith’ All attributes: SELECT * --- FEN 2014-02-07

  6. Examples: Company (Q1): JOIN: SELECT Fname, Lname, Address FROM Employee, Department WHERE Dname= ’Research’ AND Dno= Dnumber • Last term in the WHERE-clause is the join-condition. If omitted the result will be the Cartesian product. • Alternative syntax is possible. FEN 2014-02-07

  7. Examples: Company (Q2): JOIN several tables: SELECTPnumber, Dnum, Lname, Address FROMProject, Employee, Department WHEREPlocation= ’Stafford’ AND Dnum= Dnumber AND Ssn= Mgrssn Note: Two join-conditions in the WHERE-clause. FEN 2014-02-07

  8. Examples: Company (Q8): Ambiguous attribute names and aliases: SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM Employee E, Employee S WHERE E.Superssn= S.Ssn Employee is joined with itself using the aliases E and S. ’.’ (”dot”)-notation may also be used to resolve ambiguous attribute Names (remember Minibank?). FEN 2014-02-07

  9. Examples: Company SQL-tables are NOT sets (in the mathematical sense of the word set): (Q11):SELECT Salary FROM Employee (Q11A): SELECT DISTINCT Salary FROM Employee FEN 2014-02-07

  10. Examples: Company SQL-tables are NOT sets in general, but in set operations (UNION, INTERSECT and EXCEPT) they are: (SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE LNAME = ’Smith’ AND DNUM = DNUMBER AND MGRSSN = SSN) UNION (SELECT PNUMBER FROM PROJECT, WORKS_ON, EMPLOYEE WHERE LNAME = ’Smith’ AND PNO = PNUMBER AND ESSN = SSN) FEN 2014-02-07

  11. Updates i SQL: Updates: • Inserting rows: INSERT • Deleting rows: DELETE • Updating row values: UPDATE As SELECT they work on tables. FEN 2014-02-07

  12. Examples: Company Inserting a single row: INSERT INTO EMPLOYEE VALUES (’Richard’,’K’,’Marini’,’653298653’, ’30-DEC-52’,’98 Oak Forest, Katy, ’TX’,’M’,37000,’987654321’,4) Inserting a single row, selected attributes: INSERT INTO EMPLOYEE(FNAME,LNAME,SSN) VALUES (’Richard’,’Marini’,’653298653’) Is rejected if any of the other attributes is defined NOT NULL and doesn’t have defined a default value. FEN 2014-02-07

  13. Examples: Company Deleting rows: DELETE FROM EMPLOYEE WHERE LNAME =’Brown’ DELETE FROM EMPLOYEE WHERE SSN = ’123456789’ DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME = ’Research’) DELETE FROM EMPLOYEE (Not equivalent to: ´DROP TABLE EMPLOYEE’. Why not?) FEN 2014-02-07

  14. Examples: Company Updating rows: UPDATE PROJECT SET PLOCATION = ’Bellaire’, DNUM = 5 WHERE PNUMBER = 10 UPDATE EMPLOYEE SET SALARY = SALARY*1.1 WHERE DNO IN (SELECT DNUMBER FROMDEPARTMENT WHERE DNAME = ’Research’) Note, that it is only possible to affect one table in one UPDATE statement. FEN 2014-02-07

  15. Exercises • Try out some of the SQL queries on the previous slides. FEN 2014-02-07

  16. Company: Exercise • Do Exercise 2, phase 4 onCompanyExercise.pdf FEN 2014-02-07

  17. Exercise: VW-database • Look at this database:..\lektion03 (SQL2)\vwDatabase.pdf • Do some of the queries in SQL FEN 2014-02-07

More Related