X


Additional Basic Operations of SQL




The Rename Operation





'as' clause - Renaming relations



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



String Operations of SQL






Q: “Find the names of all departments whose building name includes the substring ‘Computer’.”


  1. select deptname
    from department
    where building like ’%Watson%’;



Additional Notes:


  1. For patterns to include the special pattern characters (that is, % and _ ), SQL allows the specification of an escape character '\'.

  2. The escape character is used immediately before a special pattern character to indicate that the special pattern character is to be treated like a normal character.

  3. We define the escape character for a like comparison using the escape keyword.


Q . Write a query that matches all strings beginning with “ab%cd”.


  1. like ’ab\%cd%’ escape ’\’ 

Q . Write a query that matches all strings beginning with “ab\cd”.


  1. like ’ab\\cd%’ escape ’\’ 

Q . Write a query to select all attributes of relation instructor.


  1. select *
    from instructor;




Ordering the Display of Tuples




Q . To list in alphabetic order all instructors in the Physics department


  1. select name
    from instructor
    where deptname = ’Physics’
    order by name;



Q . To list the entire instructor relation in descending order of salary, if several instructors have the same salary, we order them in ascending order by name.


  1. select *
    from instructor
    order by salary desc, name asc;


Where Clause Predicates




Q . To find the names of instructors with salary amounts between $90,000 and $100,000


  1. select name
    from instructor
    where salary between 90000 and 100000;



Q . Find the instructor names and the courses they taught for all instructors in the Biology department who have taught some course.



  1. select name, courseid
    from instructor, teaches
    where instructor.ID = teaches.ID and dept name = ’Biology’;

  1. Second way of writing the query:

  2. select name, course id
    from instructor, teaches
    where (instructor.ID, dept name) = (teaches.ID, ’Biology’);

Note : In the above query: if we write (a1, a2) ≤ (b1, b2) then it is the same as writing a1 ≤ b1 and a2 ≤ b2. The comparison operators can be used on tuples, and the ordering is defined lexicographically.



Set Operations






Q . Illustrate use of 'union' -> To find the set of all courses taught either in Fall 2009 or in Spring 2010, or both


    1. (select courseid
      from section
      where semester = ’Fall’ and year = 2009)
      union
      (select courseid
      from section
      where semester = ’Spring’ and year = 2010);

  1. The union operation automatically eliminates duplicates, unlike the select clause.


  2. If we want to retain all duplicates, we must write union all in place of union:


    1. (select courseid
      from section
      where semester = ’Fall’ and year = 2009)
      union all
      (select courseid
      from section
      where semester = ’Spring’ and year = 2010);



Q . Illustrate use of 'intersect' -> To find the set of all courses taught in Fall 2009 as well as in Spring 2010


    1. (select courseid
      from section
      where semester = ’Fall’ and year = 2009)
      intersect
      (select courseid
      from section
      where semester = ’Spring’ and year = 2010);

  1. The intersect operation automatically eliminates duplicates, unlike the select clause.


  2. If we want to retain all duplicates, we must write intersect all in place of union:


    1. (select courseid
      from section
      where semester = ’Fall’ and year = 2009)
      intersect all
      (select courseid
      from section
      where semester = ’Spring’ and year = 2010);



Q. Illustrate use of The Except Operation -> To find all courses taught in the Fall 2009 semester but not in the Spring 2010 semester



Previous Next