Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- How many instructors for each department?
- Department (department_name, building)
- Department_name number_inst
- CS 17
- EE 25
- Physics 30
- Math 20
- Music 15
- Instructor(instructorId,department_name, salary)
- */
- SELECT department_name,
- (SELECT count(*)
- FROM instructor
- WHERE Department.department_name = Instructor.department_name)
- AS num_instructors
- FROM Department;
- /*Deletion*/
- delete from instructor
- where dept_name = 'Finance';
- /*Deletes all tuples in instructor associated with department*/
- delete from instructor
- where dept_name in (select dept_name
- from department
- where building = 'Watson');
- /*Problem because salary deletes cause avg to change over time due to diff avg*/
- delete from instructor
- where salary <(select avg(salary)
- from instructor);
- /*Insert*/
- insert into course values('CS-437', 'Database systems', 'Comp. Sci', 4);
- insert into course(course_id,title,dept_name,credits) values('CS-437','Database Systems', 'comp sci', 4)
- /*Updates: must be in the order to avoid bumping lower people up into the higher category and get a salary twice*/
- update instructor set salary = salary * 1.03where salary > 100000;
- update instructor set salary = salary * 1.05where salary <= 100000;
- /*Same update but if else*/
- update instructor set salary = case
- when salary <= 100000
- then salary * 1.05
- else salary * 1.03
- end
Add Comment
Please, Sign In to add comment