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
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:
Percent (%): The % character matches any substring
Underscore (_): The character matches any character
To illustrate pattern matching, we consider the following examples:
’Intro%’ matches any string beginning with “Intro”.
’%Comp%’ matches any string containing “Comp” as a substring, for example, ’Intro. to Computer Science’, and ’Computational Biology’.
’_ _ _ ’ matches any string of exactly three characters.
’_ _ _%’ 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’.”
select deptname
from department
where building like ’%Watson%’;
Additional Notes:
For patterns to include the special pattern characters (that is, % and _ ), SQL allows the specification of an escape character '\'.
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.
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”.
like ’ab\%cd%’ escape ’\’
Q . Write a query that matches all strings beginning with “ab\cd”.
like ’ab\\cd%’ escape ’\’
Q . Write a query to select all attributes of relation instructor.
select *
from instructor;
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
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.
select *
from instructor
order by salary desc, name asc;
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
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)
select name, courseid
from instructor, teaches
where instructor.ID = teaches.ID and dept name = ’Biology’;
Second way of writing the query:
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.
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
(select courseid
from section
where semester = ’Fall’ and year = 2009)
union
(select courseid
from section
where semester = ’Spring’ and year = 2010);
The union operation automatically eliminates duplicates, unlike the select clause.
If we want to retain all duplicates, we must write union all in place of union:
(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
(select courseid
from section
where semester = ’Fall’ and year = 2009)
intersect
(select courseid
from section
where semester = ’Spring’ and year = 2010);
The intersect operation automatically eliminates duplicates, unlike the select clause.
If we want to retain all duplicates, we must write intersect all in place of union:
(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)