SQL Expressions Continued V

Consider the following table schemas and then go over SQL queries and their corresponding expressions in response to the queries that follow:

Student:

matNr | sName

Professor:

pname | psalary

Class:

classNr | room | day | pname

                                     fk

Takes:

matNr | classNr | grade

TA:

matNr | classNr | hours | tasalary

Schema updates

Adding a column:

ALTER TABLE tablename

ADD column_name data_type

Example:

ALTER TABLE Student

Add Birthday Date

·         NOT NULL cannot be specified here

Dropping a column:

ALTER TABLE tablename

DROP columnname CASCADE|RESTRICT

DROP requires appending CASCADE or RESTRICT

With CASCADE, all references and views that refer to this column will automatically be deleted with the drop. With RESTRICT, the dropping is executed only if there are no such references to this column.

Example:

ALTER TABLE Student

DROP Birthday CASCADE

Definition of a Domain

A domain is essentially a data type with optional constraints (restrictions on the allowed set of values).

CREATE DOMAIN domainname AS type

[CHECK (condition)]

Examples:

CREATE DOMAIN MatNr AS CHAR(6);

CREATE DOMAIN GenderType AS CHAR

CHECK (VALUE IN (‘M’, ‘F’));

The second example allows to define restrictions on the values in the desired domain GenderType.

Creation and Deletion of Views

 CREATE VIEW view_name { (column name, ……)} AS

SELECT_statement

DROP VIEW view_name;

Example:

CREATE VIEW CSE303_Students (name, matNo) AS

SELECT sName, matNr

FROM Student, Takes

WHERE Student.matNr = Takes.matNr

And ClassNr = ‘CSE303’;

Normally, views are not stored like base tables but are generated just in time when a query accesses the view. Some systems allow storing views for performance reasons.

Referring to the Bank Enterprise, here are some sample SQL queries and corresponding SQL expressions:

Customer:

customer_id | customer_name | customer_street | customer_city

Branch

branch_name | branch_city | assets

Account:

account_number | type | balance | branch_name

                                                             fk

Deposit:

customer_id | account_number

Loan:

loan_number | branch_name | amount

                                 fk

Borrow:

customer_id | loan_number

Payment:

pay_no | loan_number | date | amount

                       fk

Employee:

employee_id | name | designation | contact_no

Serves:

customer_id | employee_id | date

The Rename Operation

 Q For all customers who have a loan from the bank, find their names, loan numbers and loan amount.

While we can write the SQL expression for the above as

SELECT customer_name, loan_number, amount FROM borrow, loan WHERE borrow.loan_number=loan.loan_number

We can write the above expression using a rename operation as:

Select customer_name, T.loan_number, S.amount

From borrow AS T, loan as S

WHERE T.loan_number =  S.loan_number

UNION & UNION ALL

 Q Find all bank customers having a loan, an account or both at the bank.

We can write the expressiom for the above query using a normal UNION operation as:

(SELECT customer_name FROM deposit

UNION

(SELECT customer_name FROM borrow)

If we want to retain all duplicates, we must write UNION ALL in place of UNION as:

(SELECT customer_name FROM deposit)

           UNION ALL

(SELECT customer_name FROM borrow)

 

INTERSECT  &  INTERSECT ALL

Q Find all customers who have both a loan and an account at the bank.

(SELECT DISTINCT customer_name FROM deposit)

                      INTERSECT

(SELECT DISTINCT customer_name FROM borrow)

If we want to retain duplicates, we write:

(SELECT DISTINCT customer_name FROM deposit)

                      INTERSECT ALL

(SELECT DISTINCT customer_name FROM borrow)