1 / 17

SQL EX 2

SQL EX 2. Complex SQL. Sub query Rules. The ORDER BY clause may not be used in a sub query. The sub query SELECT list must consist of a single column name or expression, except for sub queries that use the keyword EXISTS.

oren-fitzgerald
Download Presentation

SQL EX 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 EX 2 Complex SQL

  2. Sub query Rules • The ORDER BY clause may not be used in a sub query. • The sub query SELECT list must consist of a single column name or expression, except for sub queries that use the keyword EXISTS. • BY default, column names in a sub query refer to the table name in the FROM clause of the sub query. It is possible to refer to a table in a FROM clause of an outer query by qualifying the column name. • When a sub query is one of the two operands involved in a comparison, the sub query must appear on the right-hand side of the comparison. 

  3. List the staffs who work in the branch at ‘163 Main St’ Staff SELECT staffNO, fName, lName, position FROM Staff WHERE branchNO = (SELECT branchNO FROM Branch WHERE street = ‘163 Main St’);

  4. List the properties that are handled by staff who work in the branch at ‘163 Main St’. SELECT propertyNo, street, city, postcode, type, rooms, rent FROM PropertyForRent WHERE staffNo IN (SELECT staffNO FROM Staff WHERE branchNO = (SELECT branchNO FROM Branch WHERE street = ‘163 Main St’));

  5. Using ERD as a Map • ERD serves as a map when we try to solve a query.

  6. Company List the employee names who work in the branch at ‘163 Main St’

  7. Using Relational Schema • Foreign keys and primary keys can be used for join operation.

  8. Company Database Schema List the employee names who work in the branch at ‘163 Main St’

  9. Sub Quires (Non Equi-join) • Retrieve the names of employee whose salary is greater than the salary of all employees in department 5.

  10. Ans: • SELECT LNAME, FNAME • FROM EMPLOYEE • WHERE SALARY > ALL (SELECT SALARY • FROM EMPLOYEE • WHERE DNO = 5);

  11. Sub Quire • Retrieve the names of employee who have no dependent.

  12. Ans: • SELECT FNAME, LNAME • FROM EMPLOYEE • WHERE NOT EXISTS (SELECT * • FROM DEPENDENT • WHERE SSN =ESSN);

  13. Sub Groups • For each department, retrieve the department number, the number of employee in the department, and their average salary.

  14. Ans: • SELECT DNO, COUNT(*), AVG(Salary) • FROM EMPLOYEE • GROUP BY DNO;

  15. INSERT • INSERT INTO EMPLOYEE • VALUES (‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ’98 Oak Forest, Katy, TX’, 37000, ‘987654321’, 4); • INSERT INTO EMPLOYEE (FNAME, LNAME, DNO, SSN) • VALUES ((‘Richard’, ‘Marini’, 4, ‘653298653’);

  16. DELETE • DELETE FROM EMPLOYEE • WHERE LNAME =‘Brown’; • DELETE FROM EMPLOYEE • WHERE DNO IN (SELECT DNUMBER • FROM DEPARTMENT • WHERE DNAME=‘Research’);

  17. UPDATE • UPDATE EMPLOYEE • SET SALARY = SALARY * 1.1 • WHERE DNO IN (SELECT DNUMBER • FROM DEPARTMENT • WHERE DNAME = ‘Research’);

More Related