SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

SQL Subquery Examples for Analyzing Student and Employee Data

hidi avatar
hidi
15 diapositives · 275 Views · Jul 13, 2014

Discover practical SQL subquery examples to enhance your database querying skills. This guide covers a range of topics including finding students with GPA below average, retrieving employees with above-average salaries, and identifying faculty members who advise students. Learn to use subqueries efficiently for various scenarios like displaying students who owe more than $2000, filtering students based on course counts, and more. Each example is crafted to illustrate the power of SQL in data analysis and management.

#sql subquery examples for analyzing student and employee data #sql subquery examples for analyzing student and employee data ppt #powerpoint #presentation
SQL Subquery Examples for Analyzing Student and Employee Data - slide 1
01 / 15SQL Subquery Examples for Analyzing Student and Employee Data
1 / 17

Télécharger la présentation

SQL Subquery Examples for Analyzing Student and Employee Data
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. SQL Sub (or Nested ) Query

  2. Examples • Q: Find students whose GPA is below the average. • The criteria itself requires a SQL statement. • SELECT * FROM student • WHERE gpa < (SELECT AVG(gpa) FROM student); • Q: Find employees with higher than average salary. • Q: Find employees who are hired before E2.

  3. Sub Query with IN • SELECT sid, sname FROM student • WHERE sid in (‘S1’, ‘S2’); • Use a subquery to retrieve keys that meet criteria. • Example: Display students’ ID, name who owe university more than $2000. (Note: not showing balance) • SELECT sid,sname • FROM student • WHERE SID IN (SELECTSIDFROM account WHERE balance > 2000); • Example: Display students’ ID, name and Balance who owe university more than $2000.

  4. Sub Query with IN, NOT IN • Use a subquery to retrieve keys that meet criteria. • Q: Display faculty’s ID and name if the faculty advises at least one student. • SELECT fid, fname FROM faculty • WHERE fid IN (SELECT DISTINCT fid FROM student); • Q: Display faculty’s name and name if the faculty does not advise any student. • SELECT fid, fname FROM faculty • WHERE fid NOT IN (SELECT DISTINCT fid FROM student);

  5. Q: Display students’ ID and name who are taking at least one course. • Q: Display students’ ID and name who do not take any course.

  6. Sub Query with IN • Q: Find students who take more than 5 courses and display their ID and name. • SELECT sid, sname FROM • Student NATURAL JOIN Registration • GROUP BY sid,sname • HAVING COUNT(cid)>5; • SELECT sid, sname FROM student • WHERE sid IN (SELECT sid FROM Registration GROUP BY sid • HAVING COUNT(cid) > 5);

  7. Sub Query with Join Display students’ ID, name and Balance who owe university more than $2000. SELECT sid,sname, Balance FROM student NATURAL JOIN (SELECT * FROM account WHERE balance > 2000); Display students’ ID and name who owe university more than $2000. SELECT sid,sname FROM student NATURAL JOIN (SELECT * FROM account WHERE balance > 2000); SELECT sid,sname FROM student WHERE SID IN (SELECTSIDFROM account WHERE balance > 2000);

  8. Sub Query with ALL/SOME/ANY • Q: Find students whose gpa is greater than all/some bus majors’ gpa: • SELECT sid, sname FROM student • WHERE gpa > ALL(SELECT gpa FROM student WHERE major=‘bus’); • SELECT sid, sname FROM student • WHERE gpa > SOME (SELECT gpa FROM student WHERE major=‘bus’); • SELECT sid, sname FROM student • WHERE gpa > ANY (SELECT gpa FROM student WHERE major=‘bus’);

  9. Comparing SQL Statements Compute the number of courses taken by each student: (1) SELECT sid, sname, courses FROM student NATURAL JOIN (SELECT sid, COUNT(cid) AS courses FROM registration GROUP BY sid); (2) SELECT sid, sname, COUNT(cid) AS courses FROM student NATURAL JOIN registration GROUP BY sid, sname;

  10. InLine View • When a multiple-column subquery is used in the FROM clause of an outer query, it basically creates a temporary table that can be referenced by other clauses of the outer query. The temporary table is called InLine view.

  11. Select from InLine View SELECT GPAGroup, Count(SID), avg(gpa) FROM (SELECT SID,GPA, CASE WHEN gpa < 2.0 THEN 'Poor' WHEN gpa < 3.0 THEN 'Good' ELSE 'Excellent' END AS GPAGroup, FROM student) GROUPBY GPAGroup; Note: Calculated field cannot be used in the Where or Group By clause. Select eid,ename, salary*.1 As Tax From Employee Where Tax > 1000; ---- This will cause error.

  12. Top (Last) n Analysis • Find students with the top 3 GPA. • Can we do: • SELECT * FROM student ORDER BY GPA desc WHERE ROWNUM<=3; ? --- No! • SELECT * FROM • (SELECT * FROM student ORDER BY GPA DESC) • WHERE ROWNUM<=3; • Note: Use the ROWNUM of the InLineView.

  13. Example: Find Business students who owe university more than $2000 and display their ID, name and balance. • Select sid, sname, Balance From • (select * from Student where major='bus') natural join (select * from account where balance >2000);

  14. Subquery in the SELECT List • SELECT eid,ename,salary,(select AVG(salary) from employee)as AvgSal FROM employee; • SELECT eid,ename,salary-(select AVG(salary) from employee)as DeviateFromMeanFROM employee;

  15. Multiple Levels Subquery • Find students who are taking one-unit courses and display their ID and name. • Join: • Select sid, sname, cid from student natural join registration natural join course where units=1; • Without Join: • Select sid, sname From Student • Where sid in (select sid from registration where cid in • (select cid from course where units=1));