Structured Query Language
Anuradha Pai
Assistant Professor
Dept. of CSE
BTI College of Engineering
Company Database
SQL Create Table
SQL Create Table
Attribute Data Types and Domains in SQL
Concept Explanation Examples / Notes
A named set of valid values that
Example: Domain
an attribute can take; defined
Domain PhoneNumber can be defined
using SQL data types or user-
as CHAR(10)
defined types.
SQL provides standard data INTEGER, CHAR(n),
Built-in SQL Data Types types that can be assigned to VARCHAR(n), FLOAT, REAL,
attributes. DATE, TIME, DECIMAL(p, d)
- CHAR(n): Fixed length 'Smith' stored as 'Smith ' in
Character Strings
- VARCHAR(n): Variable length CHAR(10)
Attribute Data Types and Domains in SQL
- INTEGER or INT: Whole numbers
- SMALLINT: Smaller range of integers DECIMAL(p,d) or NUMERIC(p,d) for fixed-
Numeric Types
- FLOAT(p), REAL, DOUBLE PRECISION for point with precision
approximate values
Some systems support BOOLEAN with
Boolean (Optional) Not standard in all implementations
values TRUE, FALSE, and UNKNOWN.
- DATE: Stores year, month, and day
Date and Time Types - TIME: Stores hour, minute, second Format for DATE: YYYY-MM-DD
- TIMESTAMP: Combines both
Domains can be defined using CREATE
User-Defined Domains DOMAIN to restrict attribute values CREATE DOMAIN SSN_TYPE AS CHAR(9);
based on existing types.
SQL allows NULL as a special marker for
Null Values Not the same as 0 or empty string
unknown or missing data.
Specifying Constraints in SQL
Constraint Type Description Example / Syntax
Enforces that values in a column Salary DECIMAL(10,2) ensures
Domain Constraint must be of a specific data type numeric value with up to 10
and meet the domain rules. digits, 2 after the decimal.
Specifies that a column must
Fname VARCHAR(15) NOT
NOT NULL Constraint always have a value (cannot be
NULL
NULL).
Assigns a default value to a
Default Constraint column if no value is specified Dno INT DEFAULT 1
during insertion.
Limits values in a column based
Check Constraint CHECK (Salary > 0)
on a logical condition.
Enforces uniqueness and NOT
Key Constraint (PRIMARY) NULL on a column or set of PRIMARY KEY (Ssn)
columns.
Specifying Constraints in SQL
Constraint Type Description Example / Syntax
Enforces uniqueness but allows
Key Constraint (UNIQUE) UNIQUE (Dname)
NULLs.
Ensures a value in one table (foreign FOREIGN KEY (Dno)
Referential Integrity key) must match a value in another REFERENCES
table (primary key). DEPARTMENT(Dnumber)
FOREIGN KEY (Mgr_ssn)
ON DELETE CASCADE / SET Controls behavior when referenced
REFERENCES EMPLOYEE(Ssn)
NULL data is deleted.
ON DELETE SET NULL
Constraints defined at the end of the
Table-level Constraints table definition and can involve PRIMARY KEY (Essn, Pno)
multiple columns.
Constraints defined directly after the
Column-level Constraints Age INT CHECK (Age >= 18)
column definition.
Referential Triggered Actions in SQL
Clause Qualifier Effect on Foreign Key Example Usage
FOREIGN KEY
When the referenced tuple is
(Super_ssn)
deleted, the foreign key in
SET NULL ON DELETE REFERENCES
referencing tuples is set to
EMPLOYEE(Ssn) ON
NULL.
DELETE SET NULL
FOREIGN KEY
When the primary key in the
(Super_ssn)
referenced tuple is updated, the
CASCADE ON UPDATE REFERENCES
change is propagated to
EMPLOYEE(Ssn) ON
referencing tuples.
UPDATE CASCADE
FOREIGN KEY
(Dept_no)
The foreign key in referencing REFERENCES
ON DELETE or ON
SET DEFAULT tuples is set to a default value DEPARTMENT(Dnumbe
UPDATE
defined earlier. r) ON DELETE SET
DEFAULT (requires a
SELECT-FROM-WHERE Clause Structure
SELECT <attribute list>
FROM <table list>
WHERE <condition>;
SELECT-FROM-WHERE Clause
Query0: 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’;
Query1: 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;
SELECT-FROM-WHERE Clause
Query 2. 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’
Ambiguous Attribute Names
Fully qualified attribute names can be used for clarity even if there is no
ambiguity.
Example:
Query1: Retrieve the name and address of all employees who work for the
‘Research’ department.
SELECT [Link], [Link], [Link]
FROM EMPLOYEE, DEPARTMENT
WHERE [Link] = ‘Research’
AND
[Link] = [Link];
Ambiguous Attribute Names
The ambiguity of attribute names also arises in the case of queries that
refer to the same relation twice, as in the following example.
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 [Link], [Link], [Link], [Link]
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = [Link];
Alias Naming or Renaming,
EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno)
Query1: Retrieve the name and address of all employees who work for
the ‘Research’ department.
SELECT [Link], [Link], [Link]
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE [Link] = ‘Research’ AND [Link] = [Link];
Unspecified WHERE Clause
Query: Select all EMPLOYEE Ssns
SELECT Ssn
FROM EMPLOYEE;
Query: Select all combinations of EMPLOYEE Ssn and DEPARTMENT
Dname in the database.
SELECT Ssn, Dname
FROM EMPLOYEE, DEPARTMENT;
Use of the Asterisk
Query Q1C Retrieve all the attribute values of any EMPLOYEE who works in
DEPARTMENT number 5
SELECT *
FROM EMPLOYEE
WHERE Dno = 5;
Query Q1D Retrieve all the attributes of an EMPLOYEE and the attributes of the
DEPARTMENT in which he or she works for every employee of the ‘Research’
department
SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = ‘Research’ AND Dno = Dnumber;
Use of the Asterisk
Query: Specifies the CROSS PRODUCT of the EMPLOYEE and
DEPARTMENT relations.
SELECT *
FROM EMPLOYEE, DEPARTMENT;
Tables as Sets in SQL
Query 11. Retrieve the salary of every employee
SELECT ALL Salary
FROM EMPLOYEE;
Query11A: Retrieve all distinct salary values
SELECT DISTINCT Salary
FROM EMPLOYEE;
Tables as Sets in SQL
Query 4. Make a list of all project numbers for projects that involve an employee
whose last name is ‘Smith’, either as a worker or as a manager of the department
that controls the project.
( SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber
AND Mgr_ssn = Ssn
AND Lname = ‘Smith’ )
UNION
(SELECT DISTINCT Pnumber
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber = Pno
AND Essn = Ssn
AND Lname = ‘Smith’ );
Substring Pattern Matching
Query 12. Retrieve all employees whose address is in Houston, Texas.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE ‘%Houston,TX%’;
Query 12A. Find all employees who were born during the 1950s.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Bdate LIKE ‘_ _ 7 _ _ _ _ _ _ _’;
Arithmetic Operators
Query 13. Show the resulting salaries if every employee working on the
‘ProductX’ project is given a 10% raise.
SELECT [Link], [Link], 1.1 * [Link] AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = ‘ProductX’;
BETWEEN
Query 14. 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;
INSERT Operation
INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno)
VALUES (‘Robert’, ‘Hatcher’, ‘980760540’, 2);
(This is rejected if referential integrity checking is provided by DBMS.)
INSERT INTO EMPLOYEE (Fname, Lname, Dno)
VALUES (‘Robert’, ‘Hatcher’, 5);
(This is rejected if NOT NULL checking is provided by DBMS.)
INSERT from another table
CREATE TABLE WORKS_ON_INFO
( Emp_name VARCHAR(15),
Proj_name VARCHAR(15),
Hours_per_week DECIMAL(3,1) );
INSERT INTO WORKS_ON_INFO ( Emp_name, Proj_name,
Hours_per_week )
SELECT [Link], [Link], [Link]
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE [Link] = [Link] AND [Link] = [Link];
Create new table that has same attributes like
existing table
CREATE TABLE D5EMPS LIKE EMPLOYEE
(SELECT E.*
FROM EMPLOYEE AS E
WHERE [Link] = 5) WITH DATA;
DELETE OPERATION
Query: Write an SQL query to delete all employees whose last name is 'Brown’.
DELETE FROM EMPLOYEE
WHERE Lname = ‘Brown’;
DELETE OPERATION
Query: Write an SQL query to delete the employee whose Social Security Number
(SSN) is '123456789’.
DELETE FROM EMPLOYEE
WHERE Ssn = ‘123456789’;
DELETE OPERATION
Query: Write an SQL query to delete all employees who belong to
department number 5.
DELETE FROM EMPLOYEE
WHERE Dno = 5;
DELETE OPERATION
Query: Write an SQL query to delete all records from the EMPLOYEE
table.
DELETE FROM EMPLOYEE;
UPDATE OPERATION
Update5: Update the location and controlling department number of project
number 10 to ‘Bellaire’ and 5
UPDATE PROJECT
SET Plocation = ‘Bellaire’, Dnum = 5
WHERE Pnumber = 10;
Update6: Give all employees in the ‘Research’ department a 10% raise in
salary
UPDATE EMPLOYEE
SET Salary = Salary * 1.1
WHERE Dno = 5;
NULL CHECK
Query 18. Retrieve the names of all employees who do not have
supervisors.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL;
Nested Queries
Query 16. Retrieve the name of each employee who has a dependent
with the same first name and is the same sex as the employee.
SELECT [Link], [Link]
FROM EMPLOYEE AS E
WHERE [Link] IN ( SELECT [Link]
FROM DEPENDENT AS D
WHERE [Link] = D.Dependent_name
AND [Link] = [Link] )
IN Operator
Query: Select the Essns of all employees who work the same (project,
hours)combination on some project that employee ‘John Smith’ (whose Ssn =
‘123456789’) works on.
SELECT DISTINCT Essn
FROM WORKS_ON
WHERE (Pno, Hours) IN ( SELECT Pno, Hours
FROM WORKS_ON
WHERE Essn = ‘123456789’ );
Rewrite Nested Query
Query 16A. Retrieve the name of each employee who has a dependent
with the same first name and is the same sex as the employee.
SELECT [Link], [Link]
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] = D.Dependent_name;
EXISTS in SQL
Query 6. Retrieve the names of employees who have no dependents.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn = Essn );
EXISTS in SQL
Query 7. List the names of managers who have at least one dependent.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn = Essn )
AND
EXISTS ( SELECT *
FROM DEPARTMENT
WHERE Ssn = Mgr_ssn );
Explicit Set of Values
Query 17. Retrieve the Social Security numbers of all employees who
work on project numbers 1, 2, or 3.
SELECT DISTINCT Essn
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);
Renaming in SQL
Query8 For each employee, retrieve the employee’s first and last name and
the first and last name of his or her immediate supervisor.
SELECT [Link] AS Employee_name,
[Link] AS Supervisor_name
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = [Link];
Joins in DBMS
INNER JOIN
Query 1. Retrieve the name and address of all employees who work for
the ‘Research’ department.
SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
WHERE Dname = ‘Research’;
NATURAL JOIN
Query Q1B. Retrieve the name and address of all employees who work
for the ‘Research’ department.
SELECT Fname, Lname, Address
FROM (EMPLOYEE
NATURAL JOIN
(DEPARTMENT AS DEPT (Dname, Dno, Mssn, Msdate)))
WHERE Dname = ‘Research’;
LEFT OUTER JOIN
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 [Link] AS Employee_name,
[Link] AS Supervisor_name
FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S
ON E.Super_ssn = [Link])
Join Multiple Tables
Query 2. 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 JOIN DEPARTMENT ON Dnum = Dnumber)
JOIN EMPLOYEE ON Mgr_ssn = Ssn)
WHERE Plocation = ‘Stafford’;
Aggregate Functions
Query 19. Find the sum of the salaries of all employees, the maximum
salary, the minimum salary, and the average salary.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM EMPLOYEE;
Q19A: Rename the attributes
SELECT SUM (Salary) AS Total_Sal,
MAX (Salary) AS Highest_Sal,
MIN (Salary) AS Lowest_Sal,
AVG (Salary) AS Average_Sal
FROM EMPLOYEE;
Aggregate Functions
Query 20. Find the sum of the salaries of all employees of the
‘Research’ department, as well as the maximum salary, the minimum
salary, and the average salary in this department.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
WHERE Dname = ‘Research’;
Aggregate Functions
Queries 21 and 22. Retrieve the total number of employees in the
company (Q21) and the number of employees in the ‘Research’
department (Q22).
Q21: SELECT COUNT (*)
FROM EMPLOYEE;
Q22: SELECT COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO = DNUMBER AND DNAME = ‘Research’;
Aggregate Functions
Query 23. Count the number of distinct salary values in the database.
SELECT COUNT (DISTINCT Salary)
FROM EMPLOYEE
Aggregate Functions
Query 5. List the names of all employees with two or more dependents.
SELECT Lname, Fname
FROM EMPLOYEE
WHERE (SELECT COUNT (*)
FROM DEPENDENT
WHERE Ssn = Essn ) > = 2;
GROUP BY Clause
Query 24. 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;
GROUP BY Clause
Query 25. For each project, retrieve the project number, the 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;
HAVING Clause
Query 26. 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.
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;
GROUP BY Clause
Query 27. For each project, retrieve the project number, the project
name, and the number of employees from department 5 who work
on the project.
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber = Pno AND Ssn = Essn AND Dno = 5
GROUP BY Pnumber, Pname;
HAVING Clause
Query 28. For each department that has more than five employees,
retrieve the department number and the number of its employees
who are making more than $40,000.
SELECT Dno, COUNT (*)
FROM EMPLOYEE
WHERE Salary>40000 AND Dno IN
( SELECT Dno
FROM EMPLOYEE
GROUP BY Dno
HAVING COUNT (*) > 5)
GROUP BY Dno;
Views (Virtual Tables) in SQL
Concept Description
A view is a virtual table created using a SQL query. It does not store data itself
View Definition
but provides a way to look at data from one or more tables.
CREATE VIEW view_name AS SELECT ... defines a view based on a SELECT
Syntax
query.
Views can be queried like base tables using SELECT. The underlying base tables
Data Access
supply the actual data.
- Simplify complex queries- Provide security by exposing only selected
Use Cases
columns- Support logical data independence
Views (Virtual Tables) in SQL
Concept Description
A view is updatable if it is defined from a single table without aggregates,
Updatable Views
GROUP BY, or joins.
Non-updatable Views Views involving joins, aggregates, or groupings are not updatable.
If a view is updatable, you can perform INSERT, UPDATE, or DELETE
View Modification
through the view, affecting the base table.
Ensures that any inserted or updated tuples through the view satisfy the
WITH CHECK OPTION
view's WHERE condition.
Use DROP VIEW view_name; to remove a view definition (the base table
Dropping a View
remains unchanged).
Views (Virtual Tables) in SQL
• Ensures only employees under 30 are visible and updatable through the view.
CREATE VIEW YoungEmployees AS
SELECT Fname, Lname, Age FROM EMPLOYEE
WHERE Age < 30
WITH CHECK OPTION;
Summary of SQL Queries
SELECT <attribute and function list>
FROM <table list>
[ WHERE <condition> ]
[ GROUP BY <grouping attribute(s)> ]
[ HAVING <group condition> ]
[ ORDER BY <attribute list> ];
Summary of SQL Syntax