DATABASE

MANAGEMEN
T SYSTEMS
Couse code:CSC403
Prof. Juhi Janjua
 Module 4: • Overview of SQL

Structured • Data Definition Commands

• Integrity constraints: key constraints, Domain Constraints,
Query Referential integrity , check constraints
• Data Manipulation commands
Language • Data Control commands
(SQL) • Set and string operations
• Aggregate function-group by, having
• Views in SQL, joins
• Nested and complex queries
• Triggers
What is SQL?
• SQL stands for Structured Query Language.
• It is used for storing and managing data in relational database management system
(RDMS).
• It is a standard language for Relational Database System. It enables a user to create, read,
update and delete relational databases and tables.
• All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their
standard database language.
• SQL allows users to query the database in a number of ways, using English-like
statements.
SQL Rules
SQL follows the following rules:
• Structure query language is not case sensitive. Generally, keywords of SQL are written in
uppercase.
• Statements of SQL are dependent on text lines. We can use a single SQL statement on one
or multiple text line.
• Using the SQL statements, you can perform most of the actions in a database.
 • DDL changes the structure of the table like creating a table,
Data deleting a table, altering a table, etc.

Definition • All the command of DDL are auto-committed that means it

permanently save all the changes in the database.
Language(D Here are some commands that come under DDL:
DL) • CREATE
• ALTER
• DROP
• TRUNCATE
SQL CREATE table
• Creating a basic table involves naming the table and defining its columns and each column's data type
(INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n)).
Syntax:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
Example
SQL Datatype
SQL ALTER table
• ALTER TABLE statement specifies how to add, modify, drop or delete columns in a table. It
is also used to rename a table.
Adding columns in a table:
ALTER TABLE customers
ADD customer_age INT;
Adding multiple columns in the existing table:
ALTER TABLE customers
ADD (customer_type varchar2(50),
customer_address varchar2(50));
SQL ALTER table …
• Modifying column of a table:
ALTER TABLE customers
MODIFY customer_address varchar2(100)
• Dropping column of a table:
ALTER TABLE customers
DROP COLUMN customer_name;
SQL ALTER table …
• Renaming column of a table:
ALTER TABLE customers
RENAME COLUMN customer_name to cname;
• Renaming table:
ALTER TABLE customers
RENAME TO retailers;
SQL DROP • Used to remove a relation (base table) and its definition
• The relation can no longer be used in queries, updates, or any
table other commands since its description no longer exists
• Example:

DROP TABLE customers;

Slide
8- 12
SQL TRUNCATE table
• It is used to delete complete data from an existing table.
• You can also use DROP TABLE command to delete
complete table but it would remove complete table
structure form the database and you would need to re-
create this table once again if you wish to store some
data.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE retailers;
 • Integrity constraints are a set of rules. It is used to maintain
the quality of information.
Integrity • It ensure that the data insertion, updating, and other
processes must be performed in such a way that data
constraints integrity is not affected.
• It is used to guard against accidental damage to the
database.
Constraints

NOT NULL UNIQUE

• Ensures that a column cannot • Ensures that all values in a
have a NULL value. That is, you column are different.
will be not allowed to insert a
new row in the table without
specifying any value to this field.
PRIMARY KEY constraint

• A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
CREATE TABLE Persons (
CREATE TABLE Persons (
ID int NOT NULL,
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
LastName
FirstName varchar(255),
varchar(255) NOT NULL,
Age int,
FirstName varchar(255),
PRIMARY KEY (ID)
Age int
);
);

ALTER TABLE Persons

ADD PRIMARY KEY (ID);
 FOREIGN KEY constraint
• Uniquely identifies a row/record in another table

CREATE TABLE Orders ( ALTER TABLE Orders

OrderID int NOT NULL, ADD CONSTRAINT FK_PersonOrder
OrderNumber int NOT NULL, FOREIGN KEY (PersonID) REFERENCES Persons(PersonID
PersonID int,
PRIMARY KEY (OrderID),
ALTER TABLE Orders
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
); DROP CONSTRAINT FK_PersonOrd
er;
Referential Integrity
• In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key
of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in
Table 2.
 CHECK constraint
• CHECK constraint is used to limit the value range that can be placed in a column.

CREATE TABLE Persons ( CREATE TABLE Persons (

ID int NOT NULL, ID int NOT NULL,
LastName varchar(255) NOT NULL, LastName varchar(255) NOT NULL,
FirstName varchar(255), FirstName varchar(255),
Age int, Age int,
CHECK (Age>=18) City varchar(255),
); CONSTRAINT CHK_Person CHECK (Age>=18 AND City=‘Mumb
);
 DEFAULT constraint
• The default value will be added to all new records if no other value is specified.

CREATE TABLE Persons ( ALTER TABLE Persons

ID int NOT NULL, MODIFY City DEFAULT ’Pune’;
LastName varchar(255) NOT NULL,
FirstName varchar(255),
ALTER TABLE Persons
Age int,
ALTER City DROP DEFAULT;
City
varchar(255) DEFAULT ’Mumbai’
);
 • SQL has one basic statement for retrieving information
from a database; the SELECT statement
• Basic form of the SQL SELECT statement is called a mapping
or a SELECT-FROM-WHERE block

Retrieval SELECT <attribute list>

FROM <table list>
Queries in WHERE <condition>
SQL
• <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
Relational
Database Schema
Populated
Database
Simple SQL Queries (contd.)
Retrieve the birthdate and address of the employee whose name is 'John B. Smith'.
Q0: SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME='John' AND MINIT=‘B’ AND LNAME='Smith’

• Similar to a SELECT-PROJECT pair of relational algebra operations:

• The SELECT-clause specifies the projection attributes and the WHERE-clause specifies
the selection condition
Simple SQL Queries (contd.)
Retrieve the name and address of all employees who work for the 'Research' department.

Q1: SELECT FNAME, LNAME, ADDRESS

FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO

• Similar to a SELECT-PROJECT-JOIN sequence of relational algebra operations

• (DNAME='Research') is a selection condition (corresponds to a SELECT operation in relational
algebra)
• (DNUMBER=DNO) is a join condition (corresponds to a JOIN operation in relational algebra)
Simple SQL Queries (contd.)
For every project located in 'Stafford', list the project number, the controlling department number, and
the department manager's last name, address, and birthdate.

Q2: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS

FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford'

• In Q2, there are two join conditions

• The join condition DNUM=DNUMBER relates a project to its controlling department
• The join condition MGRSSN=SSN relates the controlling department to the employee who manages
that department
Ambiguous Attribute Names
Same name can be used for two (or more) attributes
• As long as the attributes are in different relations
• Must qualify the attribute name with the relation name to prevent
ambiguity
UNSPECIFIED WHERE-clause

• A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the FROM-
clause are selected
• This is equivalent to the condition WHERE TRUE
Retrieve the SSN values for all employees.
SELECT SSN
FROM EMPLOYEE

• If more than one relation is specified in the FROM-clause and there is no join condition, then the
CARTESIAN PRODUCT of tuples is selected
ALIASES (contd.)

• Aliasing can also be used in any SQL query for convenience

• Can also use the AS keyword to specify aliases

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN
Use of the
Asterisk
• Specify an asterisk (*)
• Retrieve all the attribute values of the
selected tuples
USE OF DISTINCT
• SQL does not treat a relation as a set; duplicate tuples can appear
• To eliminate duplicate tuples in a query result, the keyword DISTINCT is used

SELECT SALARY may generate duplicate

FROM EMPLOYEE salary values

SELECT DISTINCT SALARY no duplicate salary

FROM EMPLOYEE values
 Data
Manipulatio • DML commands are used to modify the database. It is responsible
for all form of changes in the database.
n • The command of DML is not auto-committed that means it can't
permanently save all the changes in the database. They can be
Language(D rollback

ML) • Here are some commands that come under DML:

• INSERT
• UPDATE
• DELETE
 • In its simplest form, it is used to add one or more
tuples to a relation
• Attribute values should be listed in the same order as
the attributes were specified in the CREATE TABLE
command
Example:
INSERT U1: INSERT INTO EMPLOYEE
VALUES ('Richard','K','Marini', '653298653', '30-
DEC-52’, '98 Oak Forest,Katy,TX', 'M’, 37000,'987654321', 4
)
 INSERT (contd.)

• An alternate form of INSERT specifies explicitly the attribute names that correspond to the values
in the new tuple
• Attributes with NULL values can be left out
• Example: Insert a tuple for a new EMPLOYEE for whom we only know the FNAME, LNAME, and
SSN attributes.
U1A: INSERT INTO EMPLOYEE (FNAME, LNAME, SSN)
VALUES ('Richard', 'Marini', '653298653')
INSERT (contd.)

• Example: Suppose we want to create a temporary table that has the name, number of
employees, and total salaries for each department.
• A table DEPTS_INFO is created by U3A, and is loaded with the summary information
retrieved from the database by the query in U3B.
U3A: CREATE TABLE DEPTS_INFO
(DEPT_NAME VARCHAR(10),
NO_OF_EMPS INTEGER,
TOTAL_SAL INTEGER);

U3B: INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)

SELECT DNAME, COUNT (*), SUM (SALARY)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME ;
INSERT (contd.)

• Note: The DEPTS_INFO table may not be up-to-date if we change the tuples in either the
DEPARTMENT or the EMPLOYEE relations after issuing U3B. We have to create a view
(see later) to keep such a table up to date.
DELETE
• Removes tuples from a relation
• 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; the
table then becomes an empty table
• The number of tuples deleted depends on the
number of tuples in the relation that satisfy
the WHERE-clause
DELETE (contd.)

• Examples:
U4A: DELETE FROM EMPLOYEE
WHERE LNAME='Brown’

U4B: DELETE FROM EMPLOYEE

WHERE SSN='123456789’

U4C: DELETE FROM EMPLOYEE

WHERE DNO IN (SELECT DNUMBER FROM
DEPARTMENT
WHERE DNAME='Research')

U4D: DELETE FROM EMPLOYEE

UPDATE

• Used to modify attribute values of one or more selected tuples

• A WHERE-clause selects the tuples to be modified
• An additional SET-clause specifies the attributes to be modified and their new values
• Each command modifies tuples in the same relation
• Referential integrity should be enforced
UPDATE (contd.)
• Example: Change the location and controlling department number of project
number 10 to 'Bellaire' and 5, respectively.
U5: UPDATE PROJECT
SET PLOCATION = 'Bellaire', DNUM = 5
WHERE PNUMBER=10
UPDATE (contd.)
• Example: Give all employees in the 'Research' department a 10% raise in salary.
U6: UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research')

• In this request, the modified SALARY value depends on the original SALARY value in each tuple
• The reference to the SALARY attribute on the right of = refers to the old SALARY value before
modification
• The reference to the SALARY attribute on the left of = refers to the new SALARY value after
modification
 • Data Control Language (DCL) helps users to
retrieve and modify the data stored in the
database with some specified queries.
Data Control • DCL commands are used to grant and take back
Language(D authority from any database user.
CL) • Here are some commands that come under DCL:
• Grant
• Revoke
GRANT
• SQL Grant command is specifically used to provide privileges to database objects for an
user.
• This command also allows users to grant permissions for other users too.
Syntax:
GRANT privilege_name on object_name
to user_name
Example:
GRANT INSERT,SELECT on Department to U1
GRANT ALL PRIVELEGES on Employee to U2
REVOKE
• Revoke command withdraw user privileges on database objects if any granted. It does
operations opposite to the Grant command. When a privilege is revoked from a particular
user U, then the privileges granted to all other users by user U will be revoked.
Syntax:
REVOKE privilege_name on object_name from user_name
Example:
REVOKE INSERT on Department from U1
 • Set operators combine the results of two
component queries into a single result.
• Queries containing set operators are called
compound queries

Set operations
Set operators(contd..)
UNION
• The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
• In the union operation, all the number of datatype and columns must be same in both the
tables on which UNION operation is being applied.
• The union operation eliminates the duplicate rows from its resultset.
Syntax:
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
UNION(contd..)

SELECT * FROM First

UNION
SELECT * FROM Second;
 UNION ALL
• Union All operation is equal to the Union operation. It returns the
set without removing duplication and sorting the data.
Syntax:
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;
Example:
SELECT * FROM First
UNION ALL
SELECT * FROM Second
 INTERSECT
• The Intersect operation returns the common rows from both the SELECT statements.
Syntax:
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;
Example:
SELECT * FROM First
INTERSECT
SELECT * FROM Second
 MINUS
• Minus operator is used to display the rows which are present in the first query but absent
in the second query.
• It has no duplicates.
Syntax:
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;
Example:
SELECT * FROM First
MINUS
SELECT * FROM Second
 • String functions are used to perform an operation on input
string and return an output string.
• Following are the string functions defined in SQL:

String
operations
String functions (contd..)
• ASCII(): This function is used to find the ASCII value of a character.
Syntax: SELECT ascii('t’) from dual;
Output: 116
• CHAR_LENGTH(): This function is used to find the length of a word.
Syntax: SELECT char_length('Hello!’);
For oracle: SELECT length('Hello!’) from dual;
Output: 6
• CONCAT_WS(): This function is used to add two words or strings with a symbol as concatenating
symbol.
Syntax: SELECT CONCAT_WS('_', ‘SQL’,’Programming');
Output: SQL_Programming
Note: for oracle method is concat(), it will concatenate two strings
String functions (contd..)
LOWER(): This function is used to convert the upper case string into lower case.
Syntax: SELECT LOWER(‘SQL PROGRAMMING’);
Output: sql programming
Example:
SELECT LOWER(FNAME) AS LowercaseEmployeeName
FROM Employee;
String functions (contd..)
UPPER(): This function is used to make the string in upper case.
Syntax: SELECT UPPER(‘sql programming’);
Output: SQL PROGRAMMING
Example:
SELECT UPPER(FNAME) AS UpperCaseEmployeeName
FROM Employee;
SQL LIKE operator
• The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
• There are two wildcards often used in conjunction with the LIKE operator:
• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one single character
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
SQL LIKE operator(contd..)
Example

Oracle string functions link

https://docs.oracle.com/middleware/1221/biee/BIVUG/GUID-BBA975C7-B2C5-4C94-A007-28775680F6A5.htm#BILUG685
 • Function where the values of multiple rows are grouped
together as input on certain criteria to form a single
value of more significant meaning

Aggregate
functions
Aggregate functions(contd…)
COUNT()
• It is used to Count the number of rows in a database table.
• It can work on both numeric and non-numeric data types.
• COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table.
• COUNT(*) considers duplicate and Null.
Syntax:
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )
Example:
Select COUNT(*) from emp: Returns total number of records .i.e 6.
COUNT(salary): Return number of Non Null values over the column salary. i.e 5.
COUNT(Distinct Salary): Return number of distinct Non Null values over the column salary i.e. 4
 Aggregate functions(contd…)

SUM()
• Sum function is used to calculate the sum of all selected columns.
• It works on numeric fields only.
Syntax:
SUM()
or
SUM( [ALL|DISTINCT] expression)
Example:
Select SUM(salary) as Sum from emp
Sum all Non Null values of Column salary i.e., 310
Select SUM(Distinct salary) from emp
Sum of all distinct Non-Null values i.e., 250.
Aggregate functions(contd…)

• It is used to calculate the average value of the numeric type.

• It returns the average of all non-Null values.
Syntax:
AVG()
or
AVG( [ALL|DISTINCT] expression )
Example:
Select AVG(salary) from emp
= Sum(salary) / count(salary) = 310/5
Select AVG(Distinct salary) as Average from emp
= sum(Distinct salary) / Count(Distinct Salary) = 250/4
Aggregate functions(contd…)

MAX()
• It is used to find the maximum value of a certain column.
• This function determines the largest value of all selected values of a column.
Syntax:
MAX()
or
MAX( [ALL|DISTINCT] expression )
Example:
SELECT MAX(Salary)
FROM emp;
Aggregate functions(contd…)

MIN()
• It is used to find the minimum value of a certain column.
• This function determines the smallest value of all selected values of a column.
Syntax:
MIN()
or
MIN( [ALL|DISTINCT] expression )
Example:
SELECT MIN(Salary)
FROM emp;
Aggregate functions with GROUP BY

• SQL has a GROUP BY-clause for specifying the grouping attributes, which must also appear in
the SELECT-clause.
• In many cases, we want to apply the aggregate functions to subgroups of tuples in a relation.
Syntax:
SELECT column_name(s), aggregate_function (aggregate_expression)
FROM table_name
WHERE condition
GROUP BY column_name(s)
[ORDER BY column_name(s) [ASC|DESC];]
Using GROUP BY with the SUM Function

SELECT dept_id, SUM(salary) AS total_salaries

FROM employees
GROUP BY dept_id;
Using GROUP BY with the COUNT Function

SELECT category_id, COUNT(*) AS total_products

FROM products
WHERE category_id IS NOT NULL
GROUP BY category_id
ORDER BY category_id;
Using GROUP BY with the MIN function

SELECT dept_id, MIN(salary) AS lowest_salary

FROM employees
GROUP BY dept_id;
Queries

Q. For each department, retrieve the department number, the

number of employees in the department, and their average
salary.

SELECT DNO, COUNT (*), AVG (SALARY)

FROM EMPLOYEE
GROUP BY DNO

Q. For each project, retrieve the project number, project name,

and the number of employees who work on that project.

SELECT PNUMBER, PNAME, COUNT (*)

FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
 THE HAVING-CLAUSE

• Sometimes we want to retrieve the values of these functions for only those groups that satisfy certain
conditions
• The HAVING-clause is used for specifying a selection condition on groups (rather than on individual
tuples)
Syntax:
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
Example
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;