Other SQL options

Creating tables

• When you are creating the tables for the company database, I recommend you do NOT just type in exactly Figure 8.1 on page 211

• It is an example of SQL DDL statements, but not exactly the way you will want to create your tables.

• Use some discretion, and give it a little thought.

Joining tables

• It is possible to specify the join operation in the from clause of a query, instead of

– naming the tables in the from clause, the putting the join condition in the where clause.

Example query: Retrieve the name and address of all employees who work for the ‘Research’ department

select fname, lname, address
from employee, department
where dnumber=dno and dname=‘Research’;

select fname, lname, address
from (employee join department on number=dno)
where dname=‘Research’;

Joining in the from clause

• select fname, lname, address
from (employee join department on dnumber=dno)
where dname=‘Research’;

• In the above query, the from clause contains a single joined table

– Its attributes are all the attributes of the first table followed by all the attributes of the second.

• This syntax also allows the user to specify different types of join.

– if the join is a natural join, no join condition need be specified

• But the pair of attributes in the join must have the same name

• Renaming is often used here

Natural joins in the from clause

• select fname, lname, address
from (employee natural join
department as dept(dname, dno,mssn, msdate)
where dname=‘Research’;

• The implied join condition for the natural join is employee.dno = dept.dno,

– because this is the only pair of attributes with the same name (after renaming)

• The default type of join in a joined table is an inner join

– A tuple is included in the result only if a value in the join attribute exist in the both relations

– If the value is NULL, the tuple is not included

Inner and Outer joins

• If you want to include tuples with NULL values in the join attribute, you must use an outer join

• Suppose we have the two tables shown on the overhead and we execute this select statement

• select rentals.* customers.id, customers.customer_name
from rentals, customers
where customers.id=rentals.customer-id;

• What will the resultant table look like?

• This is an inner join.

• Suppose we want to know who the customers are, and who is currently renting movies.

– This requires an outer join

Give all customers names, and who is currently renting movies

• We need to put the tables together in such a way that we get all the customers names, and the names of movies they are renting, if any.

• An outer join preserves all the rows in one table regardless of whether a matching value can be found in the second table.

– there are three forms: one for preserving the left or right table, and one for preserving all the tuples in both tables

select C.customer_name R.tape_id
from (customers as C left outer join rentals as R
on C.id = R.customer_id) ;

– This will preserve all the tuples in the left (customer) table

– The keyword outer may be omitted

– If the join attributes have the same name, you may use a natural join; (for example, natural left outer join)

Bulletin board

• You can post information to the bulletin board

Comparisons involving nulls

• A null may have one of three different meanings

– Unknown value; the data exists but is not known

– Unavailable or withheld value; perhaps an unlisted phone number

– Not applicable;

• Since SQL cannot determine which meaning is intended, it does not distinguish between them

• In general, a null is considered to be different from every other null in the database

• When a null is involved in a comparison which should return true or false, the result is unknown

• So SQL uses a three value logic system: true, false, unknown

Boolean logic and unknowns

• You all know the truth tables for true and false true or false

• The only new facts are these:

– True AND unknown is unknown

– False AND unknown is false

– True OR unknown is true

– False OR unknown is unknown

• For inner joins, only those tuples whose join conditions evaluate to true are selected

• For outer joins, tuples may be selected whose join condition evaluates to unknown

Checking for nulls

• SQL considers each null value to be distinct from every other null value, so equality is not used

• Use IS or IS NOT to check for nulls, not = or ≠

• Retrieve the names of all employees who do not have supervisors

select fname, lname from employees
where superssn IS null;

Nested queries

• Some queries require that values be fetched from relation(s), and then used in comparisons

• This can be done by placing entire select-from-where block within the where clause of another (outer) query.

• Example: Make a list of all project numbers for projects that involve an employee whose last name is ‘Wong’ either as a worker or as a manager of the department that controls the project

Example nested query

• select distinct pnumber from project
where pnumber IN ( select pno
from works_on, employee
where essn=ssn and lname=‘Wong’)

• OR pnumber IN
(select pnumber
from project, department, employee
where dnum=dnumber and mgrssn = ssn
and lname=‘Wong’);

• The comparison operator IN compares a value v with a set of values V and returns true if the value is in the set

– In this query, it compares every pnumber with the set of pnumbers created by the select statement

Query: Retrieve the SSN of all employees who work the same project and hours on some project that employee whose ssn=‘123456789’ works on.

• select distinct essn from works_on
where (pno, hours) IN
( select pno, hours
from works_on
where essn =‘123456789’);

• The IN operator can also compare multiple values v with a set V of union compatible values

Explicit sets

• It is possible to use an explicit set of values in the where-clause.

• The explicit set is enclosed in parentheses

• Retrieve the social security numbers of all employees who work on project number 1,2, or 3

• Select distinct essn from works_on
where pno IN (1,2,3);

Other comparison operators

• In addition to in other operators can compare a single value to a set or multiset of values

• The = ANY operator returns true if the value v is equal to any of the values in the set V specified by the nested query

– Other relational operators can be used instead of =

>, < <=, >=, <>

• ALL can also be combined with the relational operators

– For example (v >V) returns true if the value v is greater than all of the values in the set V

Query: Retrieve the names of all employees whose salary is greater than the salary of all the employees in department 5

• select lname, fname from employee
where salary > ALL
( select salary
from employee
where dno=5);

Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee

• select E.fname, E.lname
from employee as E
where E.ssn IN
( select essn from dependent
where E.fname = dep_name
and E.sex = sex);

• In the inner query, notice that sex is not qualified. It is not necessary, since only the dependent table is mentioned in the inner query, but it is better if it is qualified

Correlated nested queries

• If the condition in the where-clause of a nested query references an attribute of a relation declared in the outer query, the two queries are correlated.

– The previous query was correlated.

• In general, a query written with nested select-from-where blocks and using the = or in comparison operators can always be express as a single block query if they are correlated.

• Use whatever seems the most logical to you. The DBMS almost always has a query optimizer so efficiency is not a factor.

The EXISTS function

• The exists function is used to check whether the result of a correlated nested query is empty

• Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

• select E.fname, E.lname from employee as E
where EXISTS
( select * from dependent
where E.ssn=essn
E.fname = dep_name and
and E.sex = sex);

• For each employee tuple evaluate the nested query; if at least one tuple exists in the result, then select it (them)

Retrieve the names of employees who have no dependents

• select fname, lname from employee
where NOT EXISTS
( select * from dependent
where ssn= essn);

– The nested query will retrieve all dependent tuples related to an employee.

– Each employee tuple is compared to the set retrieved by the nested query.

– If nothing in the nested query returns true to that employee tuple, that employee tuple is selected.

Modifying the database

• Three commands

– Insert

• adds a single tuple to a relation

– Delete

• Removes tuples from a relation

– Update

• Used to modify attribute values in one or more tuples

Delete

• It includes a where-clause to select the tuples to be deleted.

– If the where-clause is omitted, all the tuples are deleted

• Tuples are deleted from only one table at a time, but the deletion may propagate to tuples in other relations

– This happens if a referential triggered action clause has been set to any foreign key constraint by the DDL.

• The options are set null, cascade, and set default

– These options must be qualified by either on delete or on update

Examples of delete

• delete from employee
where lname=‘Brown’

• Delete everyone in the Research department

• delete from employee
where dno IN
( select dnumber from department
where dname=‘Research’);

Update

• It is used with a where-clause that selects the tuples to be modified

• Updating a PK value may propagate to the foreign key values of tuples in other relations

– This happen if a referential triggered action is specified when the table was created.

Update examples

• update project
set plocation = ‘Bellaire’, dnum = 5
where pnumber =10;

• Give everyone in the research department a 10% raise.

• update employee
set salary = salary * 1.1
where dno IN
( select dnumber
from department
where dname=‘Research’);