1 / 41

CSE 480: Database Systems

CSE 480: Database Systems. Lecture 10: SQL - DML. Reference: Read Chapter 4 of the textbook. Review. SQL. DML. DDL. UPDATE. RETRIEVAL. SELECT FROM WHERE GROUP BY HAVING ORDER BY. CREATE. DROP. ALTER. INSERT. DELETE. UPDATE. SQL DML (Updates).

ronda
Download Presentation

CSE 480: Database Systems

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. CSE 480: Database Systems • Lecture 10: SQL - DML • Reference: • Read Chapter 4 of the textbook

  2. Review SQL DML DDL UPDATE RETRIEVAL SELECTFROMWHEREGROUP BYHAVINGORDER BY CREATE DROP ALTER INSERT DELETE UPDATE

  3. SQL DML (Updates) There are three SQL commands to update the database state INSERT DELETE UPDATE

  4. COMPANY Database Schema

  5. INSERT Add one or more tuples to a relation Attribute values must be listed in the same order as the attributes specified in the CREATE TABLE command INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini','653298653','30-DEC-52', '98 Oak Forest,Katy,TX','M',37000,'987654321',4)

  6. INSERT An alternate form of INSERT specifies explicitly the attribute names that correspond to values in the new tuple Attributes with NULL values can be left out INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES ('Richard', 'Marini', '653298653')

  7. Disable Foreign Key in MySQL • Set foreign_key_checks = 0; • Useful to insert a tuple for subordinate before inserting the tuple for supervisor INSERT INTO EMPLOYEE (FNAME, LNAME, SSN, SUPER_SSN) VALUES ('Rob', ‘Stanley', '153298653', '431231123')

  8. INSERT Insertion of multiple tuples resulting from a query into a relation Example: Suppose we want to create a temporary table that has the name, number of employees, and total salaries for each department. CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(10), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER); INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME ;

  9. DELETE Removes tuples from a relation Includes a WHERE-clause to select the tuples to be deleted Examples: 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;

  10. UPDATE Used to modify attribute values of one or more selected tuples UPDATEtable_name SETset-clause WHEREwhere-clause WHERE-clause selects the tuples to be modified SET-clause specifies the attributes to be modified and their new values Each command modifies tuples in the same relation

  11. UPDATE Example: Change the location and controlling department number of project number 10 to 'Bellaire' and 5, respectively UPDATE PROJECT SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER = 10

  12. UPDATE Example: Give all employees in the 'Research' department a 10% raise in salary UPDATE EMPLOYEE SET SALARY = SALARY *1.1 WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research')

  13. Retrieval Queries in SQL • Basic form of the SQL retrieval queries: SELECT <attribute list> FROM <table list> WHERE <condition> • <attribute list> is a list of attribute names whose values are to be retrieved by the query • <table list> is a list of the relation names required to process the query • <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query

  14. Simple SQL Queries (from 1 Table) • Query: Retrieve the birthdate and address of the employee 'John B. Smith‘ • SELECT • FROM EMPLOYEE • WHERE

  15. Simple SQL Queries (from 1 Table) Query: Retrieve the birthdate and address of the employee 'John B. Smith‘ • SELECT Bdate, Address • FROM EMPLOYEE • WHERE

  16. Simple SQL Queries (from 1 Table) Query: Retrieve the birthdate and address of the employee 'John B. Smith‘ • SELECT Bdate, Address • FROM EMPLOYEE • WHERE Fname='John' AND Minit='B'AND Lname='Smith';

  17. Simple SQL Queries (from 1 Table) • SELECT Bdate, Address • FROM EMPLOYEE • WHERE Fname='John' AND Minit='B'AND Lname='Smith'; Another way to interpret this: • For each row in Employee table • If row.Fname='John' AND row.Minit='B' AND row.Lname='Smith' then • print row.Bdate, row.Address

  18. Simple SQL Queries (from 1 Table) Query: Retrieve the name and address of employees who work for department number 5 • SELECT Fname, Lname, AddressFROM EmployeeWHERE Dno = 5;

  19. Simple SQL Queries (from 1 Table) Query: Retrieve all the rows and columns in the Employee table • SELECT *FROM Employee; Wildcard (*) in the SELECT clause means retrieve all columns No WHERE clause means all the rows will be retrieved

  20. Simple SQL Queries (from 2 Tables) • Query: Retrieve the first name, last name and address of all employees who work for the 'Research' department

  21. Join Operation • SQL uses JOIN operation to combine information from two or more tables S R Join on R. ID = S.ID

  22. Join Operation S R Join on R. ID = S.ID SELECT * FROM R, S WHERE R.ID = S.ID In this case, a row in R is “merged” with a row in S if their IDs are the same

  23. Simple SQL Queries (from 2 Tables) Query: Retrieve the first name, last name and address of all employees who work for the 'Research' department • SELECT FROM Employee, DepartmentWHERE

  24. Simple SQL Queries (from 2 Tables) Query: Retrieve the first name, last name and address of all employees who work for the 'Research' department • SELECT Fname, Lname, AddressFROM Employee, DepartmentWHERE

  25. Simple SQL Queries (from 2 Tables) Query: Retrieve the first name, last name and address of all employees who work for the 'Research' department • SELECT Fname, Lname, AddressFROM Employee, DepartmentWHERE Dname='Research'

  26. Simple SQL Queries (from 2 Tables) Query: Retrieve the first name, last name and address of all employees who work for the 'Research' department • SELECT Fname, Lname, AddressFROM Employee, DepartmentWHERE Dname='Research' AND Dnumber=Dno;

  27. Simple SQL Queries (from 2 Tables) Query: Retrieve the name of each project and the name of the department that controls it • SELECT • FROM Department, Project • WHERE

  28. Simple SQL Queries (from 2 Tables) Query: Retrieve the name of each project and the name of the department that controls it • SELECT Pname, Dname • FROM Department, Project • WHERE

  29. Simple SQL Queries (from 2 Tables) Query: Retrieve the name of each project and the name of the department that controls it • SELECT Pname, Dname • FROM Department, Project • WHERE Dnum=Dnumber;

  30. Exercise List the names of all employees and their corresponding department names

  31. Exercise List the names of managers for each department

  32. Simple SQL Queries (from 3 Tables) • Query: For every project located in 'Stafford', list the project number, controlling department number, and the department manager's last name, address, and birthdate • SELECT • FROM PROJECT, DEPARTMENT, EMPLOYEE • WHERE

  33. Simple SQL Queries (from 3 Tables) Query: For every project located in 'Stafford', list the project number, controlling department number, and the department manager's last name, address, and birthdate • SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS • FROM PROJECT, DEPARTMENT, EMPLOYEE • WHERE

  34. Simple SQL Queries (from 3 Tables) Query: For every project located in 'Stafford', list the project number, controlling department number, and the department manager's last name, address, and birthdate • SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS • FROM PROJECT, DEPARTMENT, EMPLOYEE • WHERE PLOCATION='Stafford'

  35. Simple SQL Queries (from 3 Tables) Query: For every project located in 'Stafford', list the project number, controlling department number, and the department manager's last name, address, and birthdate • SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS • FROM PROJECT, DEPARTMENT, EMPLOYEE • WHERE PLOCATION='Stafford‘ AND DNUM=DNUMBER AND MGRSSN=SSN;

  36. Aliases • If a query refers to two or more attributes with the same name but in different relations, we must qualify the attribute name with the relation name by prefixing the relation name to the attribute name S R SELECT * FROM R, S WHERE R.ID = S.ID What if we want to join the same table? Use tuple variables

  37. Tuple Variables • Query: For each employee, retrieve the employee's name and the name of his or her immediate supervisor • SELECT FROM EMPLOYEE E, EMPLOYEE SWHERE • E and S are tuple variables

  38. Tuple Variables Query: For each employee, retrieve the employee's name and the name of his or her immediate supervisor • SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAMEFROM EMPLOYEE E, EMPLOYEE SWHERE • E and S are tuple variables

  39. Tuple Variables Query: For each employee, retrieve the employee's name and the name of his or her immediate supervisor • SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAMEFROM EMPLOYEE E, EMPLOYEE SWHERE E.SuperSSN = S.SSN • E and S are tuple variables

  40. Exercise Query: Find the names of employees who earn more than their supervisors • SELECT E.FNAME, E.LNAMEFROM EMPLOYEE E, Employee SWHERE E.SUPERSSN=S.SSN AND E.SALARY > S.SALARY;

  41. Find the names of the department where John Smith is currently working. SELECT DNAME FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER and FNAME=‘John’ and LNAME=‘Smith’ Find names of the managers earning more than 100K SELECT FNAME, LNAME FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER and MGRSSN=SSN and SALARY> 10000 Find names of employees worked on multiple (at least 2) projects. SELECT name FROM EMPLOYEE A, EMPLOEE B, DEPARTMENT C, DEPARTMENT D WHERE A.SSN = B.SSN AND C.DNUM not= D.DNUM OR SELECT FNAME, LNAME FROM EMPLOYEE WHERE (SELECT COUNT(*) FROM PROJECT WHERE DNO=DNUM) >= 2 A MORE GENERAL METHOD LATER

More Related