1 / 17

Chapter Eight Data Manipulation Language (DML) Nested Queries Dr. Chitsaz

This chapter offers a detailed exploration of Nested Queries within the Data Manipulation Language (DML) framework. Learn the objectives, rules, and conditions that govern nested queries, including the execution sequence and placement in SQL statements. Gain practical experience with examples such as retrieving customer names based on conditional balances and extracting the highest salary in a faculty context. Understand the distinctions between single-row and multiple-row subqueries, as well as aggregate functions in subqueries, enhancing your SQL proficiency.

nelly
Télécharger la présentation

Chapter Eight Data Manipulation Language (DML) Nested Queries Dr. Chitsaz

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. Chapter EightData Manipulation Language (DML)Nested QueriesDr. Chitsaz Objectives Nested queries Conditions on nested queries

  2. Nested Queries: SELECT …… FROM ….. WHERE operator (SELECT …. FROM …. );

  3. Rules: • Inner query is executed first • Enclosed sub-queries in parentheses. • Do not add an ORDER BY to a subquery • Result is used by outer query • Sub-query can be placed in WHERE HAVING FROM

  4. Rules: Operator: • Single row = <> > < >= <= • Multiple rows ANY IN ALL

  5. Nested Queries: • Example: SELECT Name FROM Student WHERE GPA > (SELECT GPA FROM Student WHERE ID=1111);

  6. Practice: List of customer’s name that have balance greater than balance of Mrs. Johnson with id=12345

  7. Using group function in a sub-query: • SELECT Name FROM Faculty WHERE Salary = (SELECT MIN(Salary ) FROM Faculty );

  8. Practice: List the name of sales person who has the highest salary.

  9. Using HAVING with sub-queries: SELECT Name, MIN(salary) FROM Faculty GROUP BY Name HAVING MIN(Salary) > (SELECT MIN(Salary ) FROM Faculty WHERE dept='COSC');

  10. Incorrect Statements: SELECT Name FROM Faculty WHERE Salary = (SELECT MIN(Salary ) FROM Faculty GROUP BY dept);

  11. Incorrect Statements: SELECT Name FROM Student WHERE GPA = (SELECT GPA FROM Student WHERE Name='NNHN');

  12. MULTIPLE ROW SUBQUERY: • Name of students having Grade A SELECT Name FROM Student WHERE ID IN (SELECT ID FROM Student_Course WHERE Grade='A' ); WHERE EXISTS WHERE ID NOT IN …. WHERE NOT EXISTS

  13. MULTIPLE ROW SUBQUERY: SELECT Name FROM Faculty WHERE Salary < (SELECT Salary FROM Faculty WHERE Faculty.ID =1234) AND Dept = (SELECT Dept FROM Faculty WHERE Area='DATABASE');

  14. Multiple Column Subqueries: SELECT col1,col2, col3,… FROM table WHERE (col1,col2, ….) IN (SELECT (col1,col2, ….) FROM table WHERE condition );

  15. (Pair-wise Comparison) Name of students who have the same major and minor as MARY: SELECT name, ID, GPA FROM student WHERE (major, minor) IN (SELECT major, minor FROM student WHERE name='MARY');

  16. Practice: List first name, last name, zip code of customers who have sales rep with minimum salary.

  17. (Non-pair-wise Comparison) SELECT Name, ID, salary FROM Faculty WHERE Salary IN (SELECT Salary FROM Faculty WHERE Dept='COSC') OR Area IN (SELECT Area FROM Faculty WHERE name='MARK');

More Related