SQL allows a subquery expression to be used in the from clause.
Example: Find the average instructors’ salaries of those departments where the average salary is greater than $42,000. [Hint: instructor (ID, name, dept_name, salary)]
select deptname, avg_salary
from (select deptname, avg (salary) as avg_salary
from instructor
group by deptname)
where avg_salary > 42000;
The subquery generates a relation consisting of the names of all departments and their corresponding average instructors’ salaries.
Example: To find the maximum across all departments of the total salary at each department. [Hint: instructor (ID, name, dept_name, salary)]
select max (totsalary)
from (select dept_name, sum(salary)
from instructor
group by deptname) as dept total (dept_name, tot_salary);
The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.
Example : Find those departments with the maximum budget [Hint : department(dept_name, building, budget) ]
with max_budget (value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department.budget = max_budget.value;
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)]
with dept_total (dept name, value) as
(select deptname, sum(salary)
from instructor
group by deptname),
dept_total_avg(value) as
(select avg(value)
from dept_total)
select deptname
from dept_total, dept_total_avg
where dept_total.value ≥ dept total avg.value;
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)]
select deptname,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department;
Deletion :
A delete request is expressed in much the same way as a query. We can delete only whole tuples; we cannot delete values on only particular attributes.
delete from r
where P;
The delete statement first finds all tuples 't' in table 'r' for which P(t) is true, and then deletes them from 'r'. The where clause can be omitted, in which case all tuples in 'r' are deleted.
Insertion :
To insert data into a relation, we either specify a tuple to be inserted or write a query whose result is a set of tuples to be inserted
The attribute values for inserted tuples must be members of the corresponding attribute’s domain. Similarly, tuples inserted must have the correct number of attributes.
It is possible for inserted tuples to be given values on only some attributes of the schema. The remaining attributes are assigned a null value denoted by null.
Q. Query to Delete all tuples from the instructor relation [Hint: instructor (ID, name, dept_name, salary)]
delete from instructor;
Q. Query to Delete all tuples in the instructor relation pertaining to instructors in the Finance department. [Hint: instructor (ID, name, dept_name, salary)]
delete from instructor
where deptname = ’Finance’;
Q. Query to Delete all instructors with a salary between $13,000 and $15,000. [Hint: instructor (ID, name, dept_name, salary)]
delete from instructor
where salary between 13000 and 15000;
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)]
delete from instructor
where deptname in (select deptname
from department
where building = ’Churchgate’);
Q. To delete the records of all instructors with salary below the average at the university [Hint: instructor (ID, name, dept_name, salary)]
delete from instructor
where salary < (select avg (salary)
from instructor);
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 )]
insert into course
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);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 )]
insert into course (courseid, title, dept name, credits) values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);
insert into course (title, courseid, credits, dept name) values (’Database Systems’, ’CS-437’, 4, ’Comp. Sci.’);
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) ]
insert into instructor
select ID, name, dept name, 18000
from student
where deptname = ’Music’ and tot_cred > 144;We use a select to specify a set of tuples. SQL evaluates the select statement first, giving a set of tuples that is then inserted into the instructor relation. Each tuple has an ID, a name, a deptname (Music), and an salary of $18,000.
In certain situations, we may wish to change a value in a tuple without changing all values in the tuple.
As we could for insert and delete, we can choose the tuples to be updated by using a query.
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) ]
update instructor set salary = salary * 1.05;
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) ]
update instructor set salary = salary * 1.05 where salary < 70000;
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) ]
update instructor
set salary = salary *1.05
where salary < (select avg (salary)
from instructor)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) ]
update instructor set salary = salary *1.03 where salary > 100000;
update instructor set salary = salary *1.05 where salary ≤ 100000;
The general form of the case statement is as follows.
case when pred1 then result1 when pred2 then result2 .... when predn then resultn else result0
The operation returns resulti where 'i' is the first of the predicates that is satisfied. Otherwise result0 is returned.
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) ]
update instructor
set salary = case
when salary <= 100000 then salary *1.05
else salary *1.03
end