Basic SQL

Database Systems CS203

Week 04
17th-19th Sep-2018
Specifying Constraints in SQL

Basic constraints:
Relational Model has 3 basic constraint

types that are supported in SQL:

Key constraint: A primary key value cannot be
duplicated
Entity Integrity Constraint: A primary key value
cannot be null
Referential integrity constraints : The “foreign key “
must have a value that is already present as a primary
key, or may be null.
 Specifying Attribute Constraints

Other Restrictions on Attribute Domains:

Default value of an attribute
●

●Default<value>

●NULL is not permitted for a particular attribute (NOT NULL)

Check Clause
●

● Apply to each tuple individually

●Row-Based constraint
●Check (Dept_Create_date <= Mgr_start_date);
●Dnumber INT NOT NULL CHECK (Dnumber >0 AND Dnumber <21)
●Create DOMAIN D_NUM AS INTEGER CHECK D_NUM >0 AND D_NUM
<21)
 Specifying Key and Referential
Integrity Constraints

PRIMARY KEY clause

Specifies one or more attributes that make up the primary key
of a relation
Dnumber INT PRIMARY KEY;
UNIQUE clause
Specifies alternate (secondary) keys (called CANDIDATE Keys in
the relational model).
Dname VARCHAR(15) UNIQUE;
 Specifying Key and Referential
Integrity Constraints (cont’d.)

FOREIGN KEY clause

Default operation: reject update on violation
Attach referential triggered action clause
Options include SET NULL, CASCADE, and SET DEFAULT
Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both
ON DELETE and ON UPDATE
CASCADE option suitable for “relationship” relations
 Giving Names to Constraints
Using the Keyword CONSTRAINT
Name a constraint
Useful for later altering
Default attribute values and referential
integrity triggered action specification (Fig. 6.2)
 Basic Retrieval Queries in SQL
SELECT statement
One basic statement for retrieving information from a database
SQL allows a table to have two or more tuples
that are identical in all their attribute values
Unlike relational model (relational model is strictly set-theory
based)
Multiset or bag behavior
Tuple-id may be used as a key
 The SELECT-FROM-WHERE Structure
of Basic SQL Queries

Basic form of the SELECT statement:

 The SELECT-FROM-WHERE Structure
of Basic SQL Queries (cont’d.)

Logical comparison operators

=, <, <=, >, >=, and <>
Projection attributes
Attributes whose values are to be retrieved
Selection condition
Boolean condition that must be true for any retrieved
tuple. Selection conditions include join conditions (see
Ch.8) when multiple relations are involved.
Basic Retrieval Queries

Query 1: Retrieve the birthdate and address of

the employee(s) whose name is ‘John B. Smith’.
Query2: Retrieve the name and address of all
employees who work for the ‘Research’ department.
Solution of Query 2

SELECT Fname, Lname, Address

FROM EMPLOYEE, DEPARTMENT
WHERE Dname = ‘Research’ AND Dnumber = Dno;

SELECT Fname, EMPLOYEE.Name, Address

FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.Name = ‘Research’ AND
DEPARTMENT.Dnumber = EMPLOYEE.Dnumber;

SELECT EMPLOYEE.Fname, EMPLOYEE.LName,EMPLOYEE.Address

FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.DName = ‘Research’ AND
DEPARTMENT.Dnumber = EMPLOYEE.Dno;
Aliasing and Renaming

Aliases or tuple variables

Declare alternative relation names E and S to refer to
the EMPLOYEE relation twice in a query:

Query 3. For each employee, retrieve the employee’s

first and last name and the first and last name of his
or her immediate supervisor.
SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;
 Unspecified WHERE Clause
and Use of the Asterisk

Missing WHERE clause

Indicates no condition on tuple selection
Effect is a CROSS PRODUCT
Result is all possible tuple combinations (or the Algebra
operation of Cartesian Product– see Ch.8) result

Query 4: Select all Employee Ssns

Select Ssn From Employee;
Query 5: all combinations of Employee Ssn and Department
Dname.
Select Ssn, Dname from Employee, Department;
 Unspecified WHERE Clause
and Use of the Asterisk (cont’d.)

Specify an asterisk (*)

Retrieve all the attribute values of the selected tuples
The * can be prefixed by the relation name; e.g., EMPLOYEE *
 Tables as Sets in SQL
SQL does not automatically eliminate duplicate tuples
in query results
Use the keyword DISTINCT in the SELECT clause
Only distinct tuples should remain in the result
 Tables as Sets in SQL (cont’d.)
Set operations
UNION, EXCEPT (difference), INTERSECT
Corresponding multiset operations: UNION ALL, EXCEPT ALL,
INTERSECT ALL)
Type compatibility is needed for these operations to be valid
 Substring Pattern Matching and
Arithmetic Operators
LIKE comparison operator
Used for string pattern matching
% replaces an arbitrary number of zero or more characters
underscore (_) replaces a single character
Examples: WHERE Address LIKE ‘%Houston,TX%’;
WHERE Ssn LIKE ‘_ _ 1_ _ 8901’;
BETWEEN comparison operator
E.g., : WHERE(Salary BETWEEN 30000 AND 40000) AND Dno = 5;
 Arithmetic Operations

Standard arithmetic operators:

Addition (+), subtraction (–), multiplication (*), and division (/)
may be included as a part of SELECT

Query. Show the resulting salaries if every employee working on

the ‘ProductX’ project is given a 10 percent raise.

SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal

FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND
P.Pname=‘ProductX’;
 Ordering of Query Results
Use ORDER BY clause
Keyword DESC to see result in a descending order of values
Keyword ASC to specify ascending order explicitly
Typically placed at the end of the query

ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC

Viewing two Attributes
SELECT Fname|| ' ' ||Lname AS "Employee Full Name"
FROM employee ;
SELECT Fname || ' ('|| (Salary)||') ‘|| AS "Employee"
FROM employee;