X


Null Values




Q . To find all instructors who appear in the instructor relation with null values for salary


  1. select name
    from instructor
    where salary is null;



Aggregate Functions




Q . Find the average salary of instructors in the Computer Science department


  1. select avg (salary)
    from instructor
    where deptname = 'Comp. Sci.';

  2. We can give a meaningful name to the result relation attribute that is generated by aggregation.


  3. select avg (salary) as avg_salary
    from instructor
    where deptname = 'Comp. Sci.';


Q . Find the total number of instructors who teach a course in the Spring 2010 semester.


  1. select count (distinct ID)
    from teaches
    where semester = ’Spring’ and year = 2010;

Q . To find the number of tuples in the course relation


  1. select count (*)
    from course;

SQL does not allow the use of distinct with count (*). It is legal to use distinct with max and min, even though the result does not change. We can use the keyword all in place of distinct to specify duplicate retention, but, since all is the default, there is no need to do so.






Aggregation with Grouping



  1. To apply the aggregate function not only to a single set of tuples, but also to a group of sets of tuples; we use the group by clause.


  2. The attribute or attributes given in the group by clause are used to form groups.


  3. Tuples with the same value on all attributes in the group by clause are placed in one group.


  4. When an SQL query uses grouping, it is important to ensure that the only attributes that appear in the select statement without being aggregated are those that are present in the group by clause.


  5. Thus, any attribute that is not present in the group by clause must appear only inside an aggregate function if it appears in the select clause, otherwise the query is treated as erroneous.



Q . Find the average salary in each department.



  1. select deptname, avg (salary) as avg salary
    from instructor
    group by deptname;

Q . Find the average salary of all instructors



  1. select avg (salary)
    from instructor;



Q . Find the number of instructors in each department who teach a course in the Spring 2010 semester.



  1. select deptname, count (distinct ID) as instr_count
    from instructor natural join teaches
    where semester = ’Spring’ and year = 2010
    group by deptname;


The Having Clause




Q . Find only those departments where the average salary of the instructors is more than $42,000



  1. select deptname, avg (salary) as avg salary
    from instructor
    group by deptname
    having avg (salary) > 42000;



Sequence of operations: Group By and Having Clauses



  1. The from clause is first evaluated to get a relation (This is true even for non aggregation based queries).


  2. If a where clause is present, the predicate in the where clause is applied on the result relation of the from clause.


  3. Tuples satisfying the where predicate are then placed into groups by the group by clause if it is present. If the group by clause is absent, the entire set of tuples satisfying the where predicate is treated as being in one group


  4. The having clause, if it is present, is applied to each group; the groups that do not satisfy the having clause predicate are removed


  5. The select clause uses the remaining groups to generate tuples of the result of the query, applying the aggregate functions to get a single result tuple for each group.


Q . For each course section offered in 2009, find the average total credits (tot_cred) of all students enrolled in the section, if the section had at least 2 students.


  1. student (ID, name, dept_name, tot_cred)

  2. takes (ID, course_id, sec_id, semester, year, grade)


  1. select course_id, semester, year, sec_id, avg (tot cred)
    from takes natural join student
    where year = 2009
    group by course_id, semester, year, sec_id
    having count (ID) > 2;


Aggregation with Null and Boolean Values










Set Membership



Q. Find all the courses taught in the both the Fall 2009 and Spring 2010 semesters.






Q. To find all the courses taught in the Fall 2009 semester but not in the Spring 2010 semester.




Q. find the total number of (distinct) students who have taken course sections taught by the instructor with ID 110011.




Q. Find the names of all instructors whose salary is greater than at least one instructor in the Biology department






Q. find the names of all instructors that have a salary value greater than that of each instructor in the Biology department.




Q. Find the departments that have the highest average salary.






Q. The 'exists' construct returns the value true if the argument subquery is nonempty. Using the exists construct, write the query “Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester”




Q. Find all students who have taken all courses offered in the Biology department.





Q. SQL includes a boolean function for testing whether a subquery has duplicate tuples in its result. The unique construct returns the value true if the argument subquery contains no duplicate tuples. Using the unique construct, write the query “Find all courses that were offered at most once in 2009”




Note : The unique test on a relation is defined to fail if and only if the relation contains two tuples t1 and t2 such that t1 = t2 .Since the test fails if any of the fields of t1 or t2 are null, it is possible for unique to be true even if there are multiple copies of a tuple, as long as at least one of the attributes of the tuple is null.


Previous Next