Topics:

● Data Types
● CREATE
● DROP
● Specifying constraints
● ALTER
● INSERT
● SELECT-FROM-WHERE
● UPDATE
Data Types:
● Numeric: INT, SMALLINT, BIGINT, FLOAT/REAL, DOUBLE, DEC(i, j)
● Character-String:
○ Fixed-length : CHAR(n), ‘n’ is no. of chars
○ Varying-length : VARCHAR(n), ‘n’ is max no. of chars ; CLOB(Character Large
Object);
○ Chars must be placed within single-quotes (‘.....’)
● Bit-String:
○ Fixed-length : BIT(n)
○ Varying-Length : BIT VARYING(n) ; BLOB(Binary Large Object)
○ Bit strings must be placed between single-quotes but preceded by ‘B’
Data Types:
● Boolean: values can be either TRUE or FALSE or UNKNOWN
● Date:
○ Has the form: YYYY-MM-DD
○ Specified within single-quotes preceded by DATE
○ Eg., DATE ‘2020-09-31’
● Time:
○ Has the form: HH:MM:SS
○ Specified within single-quotes preceded by TIME
○ Eg., TIME ‘12:24:06’
Data Types:
● Timestamp / Datetime:
○ Includes both DATE and TIME
○ Has the form: YYYY-MM-DD<blank_space>HH:MM:[Link]
○ Where X is the fraction of seconds
○ Specified within single-quotes preceded by TIMESTAMP
○ Eg., TIMESTAMP ‘2020-09-31 12:24:06.753462’
CREATE
● Used to create
○ Users: CREATE USER user_name IDENTIFIED BY ‘password’ ;
○ Databases: CREATE DATABASE database_name ;
○ Tables: CREATE TABLE table_name (column1 data_type, column2 data_type,
…. ) ;
○ Domains : CREATE DOMAIN domain_name AS data_type;
DROP
● Used to drop/destroy
○ Users: DROP USER user_name ;
○ Databases: DROP DATABASE database_name ;
○ Tables: DROP TABLE table_name ;
○ Domains : DROP DOMAIN domain_name ;
Specifying Constraints
● To disallow NULL values for an attribute:
○ Use NOT NULL
● To set a default value for an attribute:
○ Use DEFAULT clause
● To restrict an attribute value:
○ Use CHECK clause
● To make an attribute a ‘primary key’:
○ Use PRIMARY KEY clause
Specifying Constraints
● To make an attribute a ‘candidate key’:
○ Use UNIQUE clause
● To make an attribute ‘foreign key’:
○ Use FOREIGN KEY clause
ALTER
● Used to alter / modify :
○ Users: ALTER USER user_name IDENTIFIED BY ‘new_password’ REPLACE
‘old_password’ ;
○ Databases: ALTER DATABASE database_name <modification> ;
○ Tables: ALTER TABLE table_name <type_of_modification> ;
○ Domains : ALTER DOMAIN domain_name <type_of_modification> ;
INSERT
● Used to insert a single row of data into a table
● Syntax:
○ INSERT INTO table_name(column1, column2, …) VALUES (value1, value2, ...) ;
or
○ INSERT INTO table_name VALUES (value1, value2, ...) ;
● The values specified must follow same order as that of column names
● Note: care must be taken to avoid constraint violation during insertion
SELECT-FROM-WHERE
● Used to retrieve information from table(s)
● Syntax:
○ SELECT <attribute list> FROM <table list> WHERE <condition> ;
○ Where <attribute list> : list of attributes whose values are to be retrieved;
separated by comma(,)
○ <table list> : list of tables to be processed ; separated by comma (,)
○ <condition> : identifies the tuples/rows to be retrieved
● If ‘*’ is used for <attribute_list> all the attributes will be selected
● Note: WHERE clause is optional
SQL Operators
● Arithmetic Operators:
○ + (Addition) , - (Subtraction), * (Multiplication), / (Division), % (Modulo)
● Bitwise Operators:
○ & (AND), | (OR), ^ (XOR)
● Comparison Operators:
○ = (Equal to), > (Greater Than), >= (Greater Than or Equal To), < (Less Than), <=
(Less Than or Equal To), <> (Not Equal To)
SQL Operators
● Logical Operators:
○ AND : TRUE if all conditions are TRUE
○ OR : TRUE if any of the conditions is TRUE
○ NOT : TRUE if conditions is FALSE
○ ALL : TRUE if all the Subquery values meet the condition
○ ANY : TRUE if any of the subquery value meet the condition
○ BETWEEN : TRUE if the operand is within the range of comparison
○ EXISTS : TRUE if the subquery returns one or more records
○ IN : TRUE if the operand is equal to one of a list of values
UPDATE
● Used to modify attribute values of one or more selected tuples
● Syntax:
○ UPDATE table_name SET column=value,... WHERE <condition> ;
○ Where table_name : table whose attribute values needs to be modified
○ Column : name of the attribute to be modified
○ Value : new value of the attribute; can be an expression
○ <condition> : used to select tuples to be modified
● Note: care must be taken to avoid constraint violation during update
DELETE
● Used to remove a row/tuple from a table/relation
● Syntax:
○ DELETE FROM table_name WHERE <condition> ;
○ Where table_name : table whose row(s) needs to be removed
○ <condition> : used to select tuple(s) to be removed
● WHERE clause is optional, if omitted removes all tuples from a table
● Note: care must be taken to avoid constraint violation during update
Specifying Constraints in SQL
[Link] Attribute Constraints and Attribute Defaults
[Link] Key and Referential Integrity Constraints
[Link] Names to Constraints
[Link] Constraints on Tuples Using CHECK
Specifying Attribute Constraints and Attribute Defaults

• NOT NULL: A constraint NOT NULL may be specified if NULL is not permitted
for a particular attribute.
• Primary key is always NOT NULL
• Example- Create table employee(
…..
name varchar(12) NOT NULL,
…);
• DEFAULT: It is also possible to define a default value for an
attribute by appending the clause DEFAULT <value> to an
attribute definition.
• Default value is NULL for attributes that do not have the NOT
NULL constraint( if default clause is not specified).
• Example: Specifying a default manager for a new department
• create table department(
Dnumber int NOT NULL,
mgrno char(9) NOT NULL DEFAULT ‘101’,
….);
• CHECK: can restrict attribute or domain values using the
CHECK clause following an attribute or domain definition.

• For example, suppose that department numbers are restricted to

integer numbers between 1 and 20; then, we can change the
attribute declaration of Dnumber in the DEPARTMENT table

Dnumber INT NOT NULL CHECK (Dnumber > 0 AND

Dnumber < 21);
2. Specifying Key and Referential Integrity constraints

• The PRIMARY KEY clause specifies one or more attributes that make up the
primary key of a relation. This constraint specifies that attribute value must not be
NULL and value must be unique across a column.
• For example, the primary key of DEPARTMENT can be specified as follows
• Dnumber INT PRIMARY KEY;
• The UNIQUE clause specifies alternate (secondary) keys,
• Referential integrity is specified via the FOREIGN KEY clause
• A referential integrity constraint can be violated when tuples are inserted or
deleted, or when a foreign key or primary key attribute value is modified.
• The default action that SQL takes for an integrity violation is to reject the update
operation that will cause a violation, which is known as the RESTRICT option.
• If a referential integrity constraint is violated the designer can
specify an alternative action to be taken by attaching a
referential triggered action clause to any foreign key
constraint.
• The options include SET NULL, CASCADE, and SET
DEFAULT. An option must be qualified with either ON
DELETE or ON UPDATE.
• Example: Create table EMPLOYEE (...........
Dno INT NOT NULL,
UNIQUE (DNAME),
foreign key (Dno) references DEPARTMENT(Dnumber)
ON DELETE SET NULL ON UPDATE CASCADE);
• This means if a department tuple is deleted, then the value of
Dno in EMPLOYEE table is automatically set to NULL for all
employees who work in that particular department.
• On the other hand, if Dnumber in DEPARTMENT is updated
then new value is cascaded to Dno for all EMPLOYEE tuples
referencing the updated Dnumber.
• The action for CASCADE ON DELETE is to delete all the
referencing tuples, whereas the action for CASCADE ON
UPDATE is to change the value of the foreign key to the
updated (new) primary key value for all referencing tuples.
3. Giving Names to Constraints
• constraint can be given a constraint name, followed by a
keyword CONSTRAINT. The names of all constraints within a
particular schema must be unique.
• Example Create table Employee
( Dno int NOT NULL DEFAULT 1,
CONSTRAINT EMPPK primary key(SSN),
.....) ;
4. Specifying Constraints on Tuples Using CHECK
• Table constraints can be specified through additional CHECK
clauses at the end of a CREATE TABLE statement.
• These can be called tuple-based constraints because they apply
to each tuple individually and are checked whenever a tuple is
inserted or modified.
• For example, suppose that the DEPARTMENT table had an
additional attribute Dept_create_date, which stores the date
when the department was created.
• Then we could add the following CHECK clause at the end of
the CREATE TABLE statement for the DEPARTMENT table to
make sure that a manager’s start date is later than the
department creation date.
• CHECK (Dept_create_date <= Mgr_start_date);
Basic Retrieval Queries in SQL
The SELECT-FROM-WHERE Structure of Basic SQL Queries
The basic form of the SELECT statement, sometimes called a mapping or a
select-from-where block, formed of the three clauses SELECT, FROM, and
WHERE and has the following form:

SELECT <attribute list>

FROM <table list>
WHERE <condition>;
where
<attribute list> is a list of attribute names whose values are to be retrieved by
the query.
<table list> is a list of the relation names required to process the query.
<condition> is a conditional (Boolean) expression that identifies the tuples to
be retrieved by the query.
1. Retrieve the birth date and address of the employee(s) whose
name is ‘John B. Smith’.
SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname=‘John’ AND Minit=‘B’ AND
Lname=‘Smith’;

2. Retrieve the name and address of all employees who work for
the ‘Research’ department.
SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname=‘Research’ AND Dnumber=Dno;
• For every project located in ‘Stafford’, list the project
number, the controlling department number, and the
department manager’s last name, address, and birth
date.

SELECT Pnumber, Dnum, Lname, Address, Bdate

FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND
Plocation=‘Stafford’;
Ordering of Query Results:
• SQL allows the user to order the tuples in the result of a query
by the values of one or more of the attributes that appear in the
query result, by using the ORDER BY clause.
• Example: Display name of employees in ascending order on
Fname.
• Query: SELECT fname,lname
FROM EMPLOYEE
ORDER BY Fname;
• The default order is in ascending order. We can specify
keyword DESC if we want to see the result in a descending
order of values.
• The keyword ASC can be used to specify ascending order
explicitly.
• For example, if we want descending alphabetical order on
Dname and ascending order on Lname, Fname, then the
ORDER BY clause for retrieving a list of employees and the
projects they are working on,can be written as

SELECT Dname, Lname, Fname, .Pname

FROM DEPARTMENT , EMPLOYEE , WORKS_ON
,PROJECT
WHERE Dnumber= Dno AND Ssn= Essn AND Pno= Pnumber
ORDER BY Dname DESC, Lname ASC, Fname ASC;
GROUP BY and HAVING clause
Syntax:SELECT <column_name(s)>
FROM <table_name(s)>
WHERE <condition>
GROUP BY <column_name>
• In many cases we want to apply the aggregate functions to
subgroups of tuples in a relation, where the subgroups are based
on some attribute values.
• For example, if we want to find the number of employees in
each department, the average salary of employees in each
department,in these cases we need to partition the relation into
group of tuples.
• Each group (partition) will consist of the tuples that have the
same value of some attribute(s), called the grouping
attribute(s). SQL has GROUP BY clause for this purpose.
• For each department, retrieve the department number, the
number of employees in the department, and their average
salary.
Query: SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY Dno;
• HAVING clause can appear in conjunction with GROUP BY
clause. Having provides a condition on group of tuples
associated with each value of grouping attributes.
Syntax: SELECT <column_name(s)>
FROM <table_name(s)>
WHERE <condition>
GROUP BY <column_name>
HAVING <condition>;
• Example: For each project on which more than two employees
work, retrieve the project number, the project name, and the
number of employees who work on the project.
Query: SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
• ANY operator : The ANY operator returns TRUE if the value v is
equal to some value in the set V and is hence equivalent to IN.
Operators that can be combined with ANY include >, >=, <, <=, and <
>
Syntax: SELECT <column_name>
FROM <table_name>
WHERE <column_name><operator>
ANY ( SELECT<column_name>
FROM <table_name>
WHERE<condition>)
• Example: Retrieve the name of employees whose salary is greater
than the salary of one of the employees in department 5:
Query: SELECT Lname, Fname
FROM EMPLOYEE
WHERE Salary > ANY ( SELECT Salary
FROM EMPLOYEE
WHERE Dno=5 );
• ALL operator: For example ,the comparison condition (v >
ALL V) returns TRUE if the value v is greater than all the
values in the set (or multiset) V.
Syntax: SELECT <column_name>
FROM <table_name>
WHERE <column_name><operator>
ALL ( SELECT<column_name>
FROM <table_name>
WHERE<condition>)
• Example1:Retrieve the name of employees whose salary is
greater than the salary of all the employees in department 5:
Query:SELECT Lname, Fname
FROM EMPLOYEE
WHERE Salary > ALL ( SELECT Salary
FROM EMPLOYEE
• IN operator: allows you to easily test if an expression matches
any value in a list of values. It is used to help reduce the need
for multiple OR conditions in a SELECT, INSERT, UPDATE,
or DELETE statement.
• Example1: Retrieve the employee numbers(ENO) of all
employees who work on project numbers 1,2, or 3.
Query: SELECT DISTINCT EENO
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);
• Retrieve names of the employee who works in Research
department.
Query: SELECT Fname,Lname
FROM EMPLOYEE
WHERE Dno IN ( SELECT Dnumber
FROM DEPARTMENT
WHERE Dname=’Research’) ;
• . INSERT Command:
• INSERT is used to add a single tuple to a relation.
• We must specify the relation name and a list of values for the
tuple.
• The values should be listed in the same order in which the
corresponding attributes were specified in the CREATE
TABLE command.
• Example: To add a new tuple to the EMPLOYEE relation
• Query: INSERT INTO EMPLOYEE VALUES (
‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ‘Mysore’,
‘M’, 37000, ‘653298658’, 4 );
• A second form of the INSERT statement allows the user to
specify explicit attribute names that correspond to the values
provided in the INSERT command.
• For example, to enter a tuple for a new EMPLOYEE for whom
we know only the Fname, Lname, Dno, and Ssn attributes, we
can use query
• QUERY: INSERT INTO EMPLOYEE (Fname, Lname, Dno,
Ssn) VALUES (‘Richard’, ‘Marini’, 4, ‘653298653’);
• .The DELETE Command:
• The DELETE command removes tuples from a relation. It
includes a WHERE clause, to select the tuples to be deleted.
• A missing WHERE clause specifies that all tuples in the
relation are to be deleted; however, the table remains in the
database as an empty table.
Example1:DELETE FROM EMPLOYEE
WHERE Lname=‘Varma’;
Example2: DELETE FROM EMPLOYEE
WHERE Ssn=‘123456789’;
Example3: Delete all employee tuples
Query: DELETE FROM EMPLOYEE;
Example4: Delete all employees working in Research
department
DELETE FROM EMPLOYEE
WHERE Dno IN ( SELECT Dnumber
From DEPARTMENT
Where Dname=’Research’);
• The UPDATE command is used to modify attribute values of
one or more selected tuples.
• WHERE clause in the UPDATE command selects the tuples to
be modified from a single relation.
• SET clause in the UPDATE command specifies the attributes to
be modified and their new values.
Syntax: UPDATE <tablename>
SET <attributename= value>
WHERE <attributename=value>
• For example, to change the location and controlling
department number of project number 10 to ‘Bangalore’ and 5.
UPDATE PROJECT
SET Plocation = ‘Bangalore’, Dnum = 5
WHERE Pnumber=10;
Specifying General Constraints as Assertions in SQL

• Each assertion is given a constraint name and is specified via a condition

• For example, to specify the constraint that the salary of an employee must not be greater than the salary of the
manager of the department that the employee works for in SQL, we can write the following assertion:
• The constraint name SALARY_CONSTRAINT is followed by the keyword CHECK, which is followed
by a condition in parentheses that must hold true on every database state for the assertion to be
satisfied.
• The constraint name can be used later to refer to the constraint or to modify or drop it. The DBMS is
responsible for ensuring that the condition is not violated.
• Whenever some tuples in the database cause the condition of an ASSERTION statement to
evaluate to FALSE, the constraint is violated.
• By including the query inside a NOT EXISTS clause, the assertion will specify that the result of this
query must be empty so that the condition will always be TRUE. Thus, the assertion is violated if the
result of the query is not empty.
Triggers in SQL
• specify the type of action to be taken when certain events occur and when certain conditions are
satisfied.

• Syntax of Trigger in SQL

CREATE TRIGGER Trigger_Name
[ BEFORE | AFTER ] [ Insert | Update | Delete]
ON Table_Name]
[ FOR EACH ROW | FOR EACH COLUMN ]
AS
Set of SQL Statement
• Suppose we want to check whenever an employee’s salary is greater than the salary of his or her direct
supervisor in the COMPANY database Several events can trigger this rule: inserting a new employee record,
changing an employee’s salary, or changing an employee’s supervisor
• Suppose we want to check whenever an employee’s salary is greater than the salary of his or her direct
supervisor in the COMPANY database
CREATE TRIGGER INFORM_SUPERVISOR
BEFORE INSERT OR UPDATE OF
SALARY, SUPERVISOR_SSN ON EMPLOYEE
FOR EACH ROW
WHEN
([Link]> (SELECT SALARY FROM EMPLOYEE
WHERE SSN=NEW.SUPERVISOR_SSN))
INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN,[Link]);
(Refer textbook)
• Event
• Such as an insert, deleted, or update operation
• Condition
• Action
• To be taken when the condition is satisfied
Views in SQL
• A view is a “virtual” table that is derived from other tables
• Allows for limited update operations
• Since the table may not physically be stored
• Allows full query operations
• A convenience for expressing certain operations

Slide 9- 52
Specification of Views
• SQL command: CREATE VIEW
• a table (view) name
• a possible list of attribute names (for example, when arithmetic operations
are specified or when we want the names to be different from the attributes
in the base relations)
• a query to specify the table contents

Slide 9- 53
SQL Views: An Example
• Specify a different WORKS_ON table

CREATE VIEW WORKS_ON_NEW AS

SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO=PNUMBER;

Slide 9- 54
Using a Virtual Table
• We can specify SQL queries on a newly create table (view):
SELECT FNAME, LNAME
FROM WORKS_ON_NEW
WHERE PNAME=‘Seena’;

• When no longer needed, a view can be dropped:

DROP WORKS_ON_NEW;

Slide 9- 55
View Implementation, View Update, and Inline Views

• The problem of how a DBMS can efficiently implement a view for efficient querying is complex.
• One strategy, called query modification, involves modifying or transforming the view query into a query on
the underlying base tables.
• The disadvantage of this approach is that it is inefficient for views defined via complex queries that are
time-consuming to execute, especially if multiple view queries are going to be applied to the same view
within a short period of time
• The second strategy, called view materialization, involves physically creating a temporary or permanent
view table when the view is first queried or created and keeping that table on the assumption that other queries
on the view will follow.
• In this case, an efficient strategy for automatically updating the view table when the base tables are updated
must be developed in order to keep the view up-to-date. Techniques using the concept of incremental update
have been developed for this purpose,
• The view is generally kept as a materialized (physically stored) table as long as it is being queried. If the view
is not queried for a certain period of time, the system may then automatically remove the physical table and
recompute it from scratch when future queries reference the view.
• Different strategies as to when a materialized view is updated are possible.
• The immediate update strategy updates a view as soon as the base tables are changed;
• The lazy update strategy updates the view when needed by a view query; and
• The periodic update strategy updates the view periodically