daily pastebin goal
5%
SHARE
TWEET

Untitled

a guest Feb 16th, 2019 74 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top