TTV_Jabit

MySQL Queries for sample_school DB

Mar 25th, 2021
515
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* Gets the departments with an average salary over 50,000. QUERY: 15 */
  2. SELECT Dept, AVG(Salary)
  3. FROM Faculty, MemberOf
  4. WHERE MemberOf.Id = Faculty.Id
  5. GROUP BY Dept
  6. HAVING AVG(Salary) > 50000;
  7.  
  8. /* Number of grad level courses for departments that offer more than 10 courses. QUERY: 14*/
  9. SELECT Dept, COUNT(*)
  10. FROM Course
  11. WHERE Course_Level > 4 AND Dept IN (
  12.     SELECT Dept
  13.     FROM Course
  14.     GROUP BY Dept
  15.     HAVING COUNT(*) > 10
  16. )
  17. GROUP BY Dept;
  18.  
  19. /* QUERY: 16. Course numbers of CIS courses that are larger than at least 1 EE course */
  20. SELECT Course_Number
  21. FROM Course
  22. WHERE Dept = 'CIS' AND MaxSize > SOME (
  23.     SELECT MaxSize
  24.     FROM Course
  25.     WHERE Dept = 'EE'
  26. );
  27.    
  28. /* QUERY: 17. Course numbers of CIS courses that are larger than all EE courses. */
  29. SELECT Course_Number
  30. FROM Course
  31. WHERE Dept = 'CIS' AND MaxSize > SOME (
  32.     SELECT MaxSize
  33.     FROM Course
  34.     WHERE Dept = 'EE'
  35. );
  36.  
  37. /* QUERY: 18. Names of faculty who are not teaching. */
  38. SELECT Lname
  39. FROM Faculty
  40. WHERE NOT EXISTS (
  41.     SELECT ID
  42.     FROM Teach
  43.     WHERE Faculty.ID = Teach.ID
  44. );
  45.  
  46. /* QUERY: 20. Average salaries by department from greatest to least. */
  47. SELECT Dept, AVG(Salary)
  48. FROM Faculty, MemberOf
  49. WHERE Faculty.ID = MemberOf.ID
  50. GROUP BY Dept
  51. ORDER BY AVG(Salary) DESC;
  52.  
  53. /*
  54.     LIKE WILDCARDS:
  55.         % (any number of matches)
  56.         _ (single match)
  57. */
  58.  
  59. /* QUERY: 21. Names of faculty whose names contain the string 'mit' (Modified to check it starts with S also) */
  60. SELECT Lname
  61. FROM Faculty
  62. WHERE Lname LIKE ('S%mit%');
  63.  
  64. /* UPDATE EXAMPLE 1: Giving everyone a 3% raise */
  65. UPDATE Faculty
  66. SET Salary = 1.03 * Salary
  67. WHERE Faculty.ID > 0;
  68.  
  69. /* UPDATE EXAMPLE 2: Reassign the office value of faculty member with id 20000 to office occupied by 1235 */
  70. UPDATE Faculty
  71. SET Office = (
  72.     SELECT Office
  73.     FROM Faculty
  74.     WHERE ID = 1235
  75. ), Bldg = (
  76.     SELECT Bldg
  77.     FROM Faculty
  78.     WHERE ID = 1235
  79. )
  80. WHERE ID = 20000;
  81.  
  82. /* DELETE EXAMPLE 1: Remove all rows in a table */
  83. DELETE FROM Faculty; -- Deletes all rows in faculty
  84.  
  85. /* DELETE EXAMPLE 2: Delete faculty member 1235 */
  86. DELETE FROM Faculty
  87. WHERE ID = 1235;
  88.  
  89. /* VIEW EXAMPLE 1: Virtual table of office information */
  90. CREATE VIEW Offices
  91. AS (
  92.     SELECT DISTINCT Office, Bldg, Phone
  93.     FROM Faculty
  94. );
  95.  
  96. /* VIEW EXAMPLE 2: Virtual table of CIS Faculty */
  97. CREATE VIEW CISFaculty
  98. AS (
  99.     SELECT ID, LName, Office, Bldg, Phone, Salary
  100.     FROM Faculty, MemberOf
  101.     WHERE Faculty.ID = MemberOf.ID AND
  102.           Dept = 'CIS'
  103. );
  104.  
  105. /* VIEW USAGE EXAMPLE 1: Gets the phone number from bldg smith and office 102 */
  106. SELECT Phone
  107. FROM Offices
  108. WHERE Bldg = 'Smith' AND Office = 102;
  109.  
  110. /* WON'T WORK SINCE IT VIOLATES CONSTRAINTS IN Faculty */
  111. INSERT INTO Offices
  112. VALUES (321, 'Smith', 555-555-5555);
  113.  
  114. /* VIEW EXAMPLE 1: Creates a virtual table of CIS Courses */
  115. CREATE VIEW CISCourse
  116. AS (
  117.     SELECT *
  118.     FROM Course
  119.     WHERE Dept = 'CIS'
  120. );
  121.  
  122. /* Valid statement but shouldn't do it */
  123. INSERT INTO CISCourse
  124. VALUES (4, 'EE', 451, 25);
  125.  
  126.  
  127.  
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×