Additional Basic Operations of SQL




The Rename Operation


  • Consider the query below:


    • select name, courseid
      from instructor, teaches
      where instructor.ID = teaches.ID;

  • The result of this query is a relation with the following attributes: name, courseid


  • SQL provides a way of renaming the attributes of a result relation. It uses the as clause, taking the form:


    • select name as instructor_name, course id
      from instructor, teaches
      where instructor.ID = teaches.ID;



'as' clause - Renaming relations


  • The as clause is particularly useful to replace a long relation name with a shortened version that is more convenient to use elsewhere in the query.


    • select T.name, S.courseid
      from instructor as T, teaches as S
      where T.ID = S.ID;


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

  • instructor (ID, name, dept_name, salary)

    • select distinct T.name
      from instructor as T, instructor as S
      where T.salary > S.salary and S.deptname = ’Biology’;

  • In the above query, T and S can be thought as aliases, that is as alternative names, for the relation instructor




String Operations of SQL




  • SQL specifies strings by enclosing them in single quotes, for example, ’Computer’.


  • A single quote character that is part of a string can be specified by using two single quote characters; Example, the string "It’s right" can be specified by "It''s right".


  • The SQL standard specifies that the equality operation on strings is case sensitive; as a result the expression "'comp. sci.' = 'Comp. Sci.'" evaluates to false.


  • Pattern matching can be performed on strings, using the operator like. We describe patterns by using two special characters:


    1. Percent (%): The % character matches any substring


    2. Underscore (_): The character matches any character


  • To illustrate pattern matching, we consider the following examples:


    1. ’Intro%’ matches any string beginning with “Intro”.


    2. ’%Comp%’ matches any string containing “Comp” as a substring, for example, ’Intro. to Computer Science’, and ’Computational Biology’.


    3. ’_ _ _ ’ matches any string of exactly three characters.


    4. ’_ _ _%’ matches any string of at least three characters.


  • Patterns are case sensitive; that is, uppercase characters do not match lowercase characters, or vice versa.


  • SQL allows us to search for mismatches instead of matches by using the not like comparison operator.



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



  • The order by clause causes the tuples in the result of a query to appear in sorted order.


  • By default, the order by clause lists items in ascending order. To specify the sort order, we may specify desc for descending order or asc for ascending order.


  • Ordering can be performed on multiple attributes.



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



  • SQL includes a between comparison operator to simplify where clauses that specify that a value be less than or equal to some value and greater than or equal to some other value.



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.


  • teaches(ID, course_id, sec_id, semester, year)


  • instructor(ID, name, dept_name, salary)



  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



  • The SQL operations union, intersect, and except operate on relations and correspond to the mathematical set-theory operations ∪, ∩, and −.




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


  • (select courseid
    from section
    where semester = ’Fall’ and year= 2009)
    except
    (select courseid
    from section
    where semester = ’Spring’ and year= 2010)

  • The except operation outputs all tuples from its first input that do not occur in the second input; that is, it performs set difference. The operation automatically eliminates duplicates in the inputs before performing set difference.


  • If we want to retain duplicates, we must write except all in place of except:


  • (select courseid
    from section
    where semester = ’Fall’ and year= 2009)
    except all
    (select courseid
    from section
    where semester = ’Spring’ and year= 2010)