Guest User

Untitled

a guest
Feb 16th, 2019
108
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.62 KB | None | 0 0
  1. /*
  2. How many instructors for each department?
  3. Department (department_name, building)
  4. Department_name number_inst
  5. CS 17
  6. EE 25
  7. Physics 30
  8. Math 20
  9. Music 15
  10. Instructor(instructorId,department_name, salary)
  11. */
  12.  
  13. SELECT department_name,
  14. (SELECT count(*)
  15. FROM instructor
  16. WHERE Department.department_name = Instructor.department_name)
  17. AS num_instructors
  18. FROM Department;
  19.  
  20.  
  21. /*Deletion*/
  22. delete from instructor
  23. where dept_name = 'Finance';
  24. /*Deletes all tuples in instructor associated with department*/
  25.  
  26. delete from instructor
  27. where dept_name in (select dept_name
  28. from department
  29. where building = 'Watson');
  30.  
  31.  
  32. /*Problem because salary deletes cause avg to change over time due to diff avg*/
  33. delete from instructor
  34. where salary <(select avg(salary)
  35. from instructor);
  36.  
  37.  
  38. /*Insert*/
  39. insert into course values('CS-437', 'Database systems', 'Comp. Sci', 4);
  40. insert into course(course_id,title,dept_name,credits) values('CS-437','Database Systems', 'comp sci', 4)
  41.  
  42. /*Updates: must be in the order to avoid bumping lower people up into the higher category and get a salary twice*/
  43. update instructor set salary = salary * 1.03where salary > 100000;
  44. update instructor set salary = salary * 1.05where salary <= 100000;
  45.  
  46. /*Same update but if else*/
  47. update instructor set salary = case
  48. when salary <= 100000
  49. then salary * 1.05
  50. else salary * 1.03
  51. end
Add Comment
Please, Sign In to add comment