X


Nested Subqueries: Subqueries in the From Clause







The with Clause




Q. Find all departments where the total salary is greater than the average of the total salary at all departments [Hint: instructor (ID, name, dept_name, salary)]




Scalar Subqueries


SQL allows subqueries to occur wherever an expression returning a value is permitted, provided the subquery returns only one tuple containing a single attribute; such subqueries are called scalar subqueries.


Q. list all departments along with the number of instructors in each department [Hint: instructor (ID, name, dept_name, salary)]






Modification of the Database




Q. Query to Delete all tuples from the instructor relation [Hint: instructor (ID, name, dept_name, salary)]





Q. Query to Delete all tuples in the instructor relation pertaining to instructors in the Finance department. [Hint: instructor (ID, name, dept_name, salary)]



Q. Query to Delete all instructors with a salary between $13,000 and $15,000. [Hint: instructor (ID, name, dept_name, salary)]



Q. Delete all tuples in the instructor relation for those instructors associated with a department located in the Churchgate building. [Hint: instructor (ID, name, dept_name, salary)]



Q. To delete the records of all instructors with salary below the average at the university [Hint: instructor (ID, name, dept_name, salary)]



Q. Insert the fact that there is a course CS-437 in the Computer Science department with title “Database Systems”, and 4 credit hours. [Hint: course( course_id, title, dept_name, credits )]





Q. For the benefit of users who may not remember the order of the attributes, SQL allows the attributes to be specified as part of the insert statement[Hint: course( course_id, title, dept_name, credits )]



Q. To make each student in the Music department who has earned more than 144 credit hours, an instructor in the Music department, with a salary of $18,000 [Hint : student(ID, name, dept_name, tot_cred) and instructor(ID, name, dept_name, salary) ]




Updates






Q. Write a query for annual salary increases are being made, and salaries of all instructors are to be increased by 5 percent. [Hint : student(ID, name, dept_name, tot_cred) and instructor(ID, name, dept_name, salary) ]



Q. If a salary increase is to be paid only to instructors with salary of less than $70,000 [Hint : student(ID, name, dept_name, tot_cred) and instructor(ID, name, dept_name, salary) ]



Q. Give a 5 percent salary raise to instructors whose salary is less than average using a nested select within an update statement. [Hint : student(ID, name, dept_name, tot_cred) and instructor(ID, name, dept_name, salary) ]



Q. Write a query for all instructors with salary over $100,000 receive a 3 percent raise, whereas all others receive a 5 percent raise. Different queries for each case. [Hint : student(ID, name, dept_name, tot_cred) and instructor(ID, name, dept_name, salary) ]






SQL: case construct




Q.Write a query for all instructors with salary over $100,000 receive a 3 percent raise, whereas all others receive a 5 percent raise. Use SQL case construct. [Hint : student(ID, name, dept_name, tot_cred) and instructor(ID, name, dept_name, salary) ]


Previous Next