Schema and Concepts in SQL

• Basic standard SQL syntax – there are variations in existing RDBMS systems
• SQL schema
• Identified by a schema name
• Includes an authorization identifier and descriptors for each element
• Schema elements include
• Tables, constraints, views, domains, and other constructs
• Each statement in SQL ends with a semicolon
The CREATE TABLE Command in SQL
• Specifying a new relation
• Provide name of table
• Specify attributes, their types and initial constraints
• Can optionally specify schema:
• CREATE TABLE COMPANY.EMPLOYEE ...
or
• CREATE TABLE EMPLOYEE ...
The CREATE TABLE Command in SQL
(cont’d.)
• Base tables (base relations)
• Relation and its tuples are actually created and stored as a file by the DBMS
• Virtual relations (views)
• Created through the CREATE VIEW statement. Do not correspond to any physical
file.

Slide 6- 10
COMPANY relational database schema (Fig.)
One possible database state for the COMPANY relational database schema
One possible database state for the COMPANY relational database
schema – continued
SQL CREATE TABLE data definition statements for defining the COMPANY schema
SQL CREATE TABLE data definition statements for defining the COMPANY
schema -continued
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.
Giving Names to Constraints
• Using the Keyword CONSTRAINT
• Name a constraint

• Useful for later altering

Default attribute values and referential integrity triggered action specification
Specifying Constraints on Tuples
Using CHECK
Check: - When check reserved keyword is added to column it
ensures that only valid data should takes entry.
syntax:-
1. Column-name data-type check (column operator value)
OR

2. Column-name data-type constraint <restriction-name>

Check (column operator value)
Example-1:

CREATE TABLE Persons (

PID int Primary Key,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
City varchar(25),
Age int,
CHECK (Age>=18)
);
Example-2:

CREATE TABLE Persons (

PID int Primary Key,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City=‘Mubmai')
);
Syntax to add constraint for existing table

ALTER TABLE Persons ADD

CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City=‘Mumbai');

ALTER TABLE Persons Add constraint citychk Check (city IN

(‘Mumbai’, ‘Chennai’, ‘Kolkata’)));

Syntax to drop constraint

ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;
The SELECT-FROM-WHERE Structure of
Basic SQL Queries
• Basic form of the SELECT statement:
Basic Retrieval Queries
Basic Retrieval Queries (Contd.)
Ambiguous Attribute Names
• Same name can be used for two (or more) attributes in different
relations
• As long as the attributes are in different relations
• Must qualify the attribute name with the relation name to prevent
ambiguity
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 8. 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;
Aliasing, Renaming and Tuple
Variables (contd.)
• The attribute names can also be renamed

EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr,

Gend, Sal, Sssn, Dno)

• The “AS” may be dropped in most SQL implementations

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) result
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
• For aggregate operations duplicates must be accounted
• Use the keyword DISTINCT in the SELECT clause
• Only distinct tuples should remain in the result

Query) Retrieve the salary of every employee

SELECT ALL Salary
FROM EMPLOYEE;

Query) Retrieve all distinct salary values

SELECT DISTINCT Salary
FROM EMPLOYEE;
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
• LIKE comparison operator
• Used for string pattern matching
• % replaces an arbitrary number of zero or more characters
• underscore (_) replaces a single character
LIKE Clause (Example)

SQL> SELECT *
FROM CUSTOMERS
WHERE SALARY LIKE '200%';
LIKE Clause (Example)
Query) Retrieve all employees whose address is in Houston.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE ‘%Houston%’;

Query) Find all employees who were born during the 1970s.
SELECT Fname
FROM EMPLOYEE
WHERE Bdate LIKE ‘_ _ 7 _ _ _ _ _ _ _’;
Query) List student names whose name starts with ‘s’.
SQL> select sname from student11 where sname like 's%';

Query) List student names whose name contains ‘s’ as third literal.
SQL> select sname from student11 where sname like '__s%';

Query) List student names whose contains two ‘s’ anywhere in the name.
SQL> select sname from student11 where sname like '%s%s%';
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’;
Between Operator
Query) Retrieve all employees in department 5 whose salary is between
$30,000 and $40,000.
SELECT *
FROM EMPLOYEE
WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5;
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
• Consider The CUSTOMERS Table Having The Following Records
ORDER BY Clause example (continued)
•SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY;

•SQL> SELECT * FROM CUSTOMERS

ORDER BY NAME DESC;
Basic SQL Retrieval Query Block
Aggregate functions in SQL
• It is a function where the values of multiple rows are
grouped together as input on certain criteria to form a
single value of more significant meaning.
Various Aggregate Functions
1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()
Sample data:
Id Name Salary Count(*):
--------------------------- Returns total number of records .i.e 6.
1 A 80
2 B 40
Count(salary):
3 C 60
4 D 70
Return number of Non Null values over the
5 E 60 column salary. i.e 5.
6 F
Null
Count(Distinct Salary):
Return number of distinct Non Null values
over the column salary .i.e 4
Sample data:
Id Name Salary sum(salary): Sum all Non Null values of
--------------------------- Column salary i.e., 310
1 A 80
2 B 40
3 C 60
4 D 70
sum(Distinct salary): Sum of all distinct Non-
5 E 60 Null values i.e., 250.
6 F
Null
Sample data:
Id Name Salary Avg(salary) = Sum(salary) / count(salary)
--------------------------- = 310/5
1 A 80
2 B 40
Avg(Distinct salary) = sum(Distinct salary) /
3 C 60
4 D 70
Count(Distinct Salary)
5 E 60 = 250/4
6 F
Null
Sample data:
Id Name Salary Min(salary): Minimum value in the salary
--------------------------- column except NULL i.e., 40.
1 A 80
2 B 40
3 C 60
4 D 70
Max(salary): Maximum value in the salary
5 E 60 i.e., 80.
6 F
Null
GROUP BY:- Group by is used to make each a
number of groups of rows in a relation, where the
number of groups depends on the relation
instances.

Syntax:
SELECT Column1, Column2
FROM Table_name
WHERE [ Conditions ]
GROUP BY Column1, Column2
ORDER BY Column1, Column2;
Group By Clause example (continued)

SQL> SELECT ADDRESS, SUM(SALARY)

FROM CUSTOMERS
GROUP BY NAME;
HAVING :- The extension of GROUP BY is
HAVING clause which can be used to specify the
qualification over group.

Syntax:
Select [Distinct] Att_list
From Table_list
Where Condition
Group By Grouping_list
Having Group_conditiion;
Example-1:
Query: list the project number, project name and number of
employees who works on the on the project or all the projects?
SQL>
select pname,pnumber,count(essn)
from project,work_on
where pnumber=pno
group by (pnumber,pname);
Example-2:
Query: list all employee with 2 or more dependents?
SQL>
select fname
from employee
where ssn in(select essn
from dependent
group by (essn)
having count(essn)>=2);
Multiset operations

(a) Two tables, R(A) and S(A).

(b) R(A)UNION ALL S(A).
(c) R(A) EXCEPT ALL S(A).
(d) R(A) INTERSECT ALL S(A).
Summary
• SQL
• A Comprehensive language for relational database management
• Data definition, queries, updates, constraint specification, and
view definition
• Covered :
• Data definition commands for creating tables
• Commands for constraint specification
• Simple retrieval queries
• Database update commands