CPIS-241

Introduction to
Database
Management
Dr HALIMA SAMRA
Structured Query
Language (SQL)

https://networkencyclopedia.com/structured-query-language-sql/
Objectives
Understand the basic requirements for the data manipulation component

Define the SQL basic terminology and concepts

Learn the SQL commands through their four categories and supporting languages

Learn basic SQL Commands for Data Definition

Learn basic SQL CREATE statements for creating databases and tables using constraints

Learn basic SQL ALTER statements for modifying columns inside tables using (ADD, DROP, MODIFY)

Learn basic SQL Commands for Data Manipulation

Learn basic SQL INSERT statements for adding data into a table

Learn basic SQL UPDATE statements for updating existing data within a table

Learn basic SQL DELETE statements for deleting record from a table
Objectives (Cont.)
Learn basic SQL Commands for Data Retrieval

Learn basic SQL SELECT statements and options for processing a single table

Learn the simple form using SELECT/FROM commands

Understand the use of WHERE clause for specifying search criteria using comparison operators , logical
operators, and wildcard search

Learn the basic built-in SQL functions (COUNT, MIN, MAX, SUM, AVG)

Learn the basic use of GROUP BY and HAVING commands

Learn the basic commands for retrieving information from multiple tables using

Subqueries

Joins

Learn how to modify data using UPDATE, DELETE , and DROP statements

Understand the meaning of SQL Views.

 Data Manipulation
❑ Before you can interrogate any database, you must know the following information.
- Database Name.
- Table names.
- The column names contained within a table.
- The data type assigned to each column.
- The data format used for each column.
❑ When you create an SQL query you must determine
- The output that is required.
- The table(s) that are to be used to extract the required output.
 Structured Query Language
SQL stands for Structured Query Language, is a standards-based language used by
relational database management programs primarily for constructing queries.
SQL is not a complete programming language, rather it is a data sublanguage.
- Acronym: SQL
- Pronounced as “S-Q-L” [“Ess-Que-El”]
- Originally developed by IBM as the SEQUEL language in the 1970s
- SQL-92 is an ANSI national standard adopted in 1992.
- SQL:2008 is current standard.

https://networkencyclopedia.com/structured-query-language-sql/
 SQL Commands
SQL contains a series of commands for data query, data manipulation (insert, update and delete),
data definition (schema creation and modification), and data access control.

These SQL commands are mainly categorized into four categories as follows:
1. DDL – Data Definition Language
- DDL is a set of SQL commands used to create, modify, and delete database structures(schema) but not data.

2. DQL – Data Query Language

- DQL is a component of the SQL statement that allows to get data from a database and enforce order on it.

3. DML – Data Manipulation Language

- DML contains SQL commands that deal with the manipulation of data in the database.

4. DCL – Data Control Language

- DCL is the component of the SQL statement that controls access to data and to the database.

https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/
SQL Commands (Languages)
 SQL Commands for Data Definition
The SQL data definition DDL statements include:
CREATE
To create the database and its objects like tables.
ALTER
To modify the structure of the database objects (ADD, DROP, MODIFY) attribute(s).
DROP
To delete a whole database or a table with its full structure.
TRUNCATE
To delete table data while keeping structure.
 SQL Commands for Data Definition(Cont.)
CREATE
There are two CREATE statements available in SQL CREATE DATABASE, CREATE TABLE
1. CREATE DATABASE

Syntax: CREATE DATABASE database_name; Example Query: CREATE DATABASE my_database;

2. CREATE TABLE
CREATE TABLE table_name CREATE TABLE Student
( (
column1 data_type(size),
Syntax: Example Query: ST_ID int(9) NOT NULL,
column2 data_type(size), NAME varchar(20) NOT NULL,
column3 data_type(size), SUBJECT varchar(20) NOT NULL
....
);
);
https://www.geeksforgeeks.org/sql-create/
 SQL Commands for Data Definition(Cont.)
CREATE with CONSTRAINT- I
Creating database tables with PRIMARY KEY constraints
- The SQL CREATE TABLE statement
- The SQL CONSTRAINT keyword

Example: Define the student Id as the primary key of the student table using the constraint keyword.

CREATE TABLE Student

( CREATE TABLE Student

ST_ID int(9) NOT NULL, (

Example Query: NAME
SUBJECT
varchar(20)
varchar(20)
NOT NULL,
NOT NULL,
or ST_ID
NAME
int(9)
varchar(20)
NOT NULL PRIMARY KEY,
NOT NULL,

CONSTRAINT ST_PK PRIMARY KEY(ST_ID) SUBJECT varchar(20) NOT NULL

); );
 SQL Commands for Data Definition(Cont.)
CREATE with CONSTRAINT- II
Creating database tables with composite primary keys using PRIMARY KEY constraints
- The SQL CREATE TABLE statement
- The SQL CONSTRAINT keyword

Example: Define the employee Id and skill Id as composite primary keys of the EMP_SKILL table using the constraint
keyword.
CREATE TABLE EMP_SKILL(
EmpID int(20) NOT NULL,
Example Query: SkillID int(10) NOT NULL,
SkillLevel int(4) NULL,
CONSTRAINT EmpSkill_PK PRIMARY KEY (EmpID, SkillID)
);
 SQL Commands for Data Definition(Cont.)
CREATE with CONSTRAINT- III
Creating database tables with PRIMARY KEY and FOREIGN KEY constraints
- The SQL CREATE TABLE statement
- The SQL CONSTRAINT keyword

Example: Define the employee Id as primary key of the EMPLOYEE table and define the skill Id as a foreign key in the
EMPLOYEE table which reference the SKILL table using the constraint keyword .

CREATE TABLE EMPLOYEE(

EmpID Integer NOT NULL,
SkillID Integer NULL,
EmpName TXT NULL,
Example Query:
CONSTRAINT EmpID_PK PRIMARY KEY (EmpID),
CONSTRAINT Skill_FK FOREIGN KEY(SkillID) REFERENCES SKILL(SkillID)
);
 SQL Commands for Data Definition(Cont.)
ALTER-ADD - I
Adding columns to an existing table using ADD.
- The SQL ALTER statement
- The SQL ADD keyword

Example: Alter the Student table by adding the Age and Address attributes, selecting the appropriate data type for
each attribute, knowing that each attribute is not optional (mandatory field).

ALTER TABLE table_name

ALTER TABLE Student
Syntax: ADD(
ADD(
column1 data_type(size), Example Query: Age int(4) NOT NULL,
column2 data_type(size),
Address varchar(50) NOT NULL
....
);
);

https://www.geeksforgeeks.org/sql-create/
 SQL Commands for Data Definition(Cont.)
ALTER-ADD - II
Adding primary key constraints to an existing table.
- The SQL ALTER statement
- The SQL ADD keyword
- The SQL CONSTRAINTS keyword
Syntax: ALTER TABLE table_name ADD CONSTRAINT colum_PK PRIMARY KEY (column_name);

Example: Alter the Student table by adding a primary key constraints on the ST_ID attribute.

Example Query: ALTER TABLE Student ADD CONSTRAINT STUD_PK PRIMARY KEY (ST_ID);

Note:
If you use ALTER TABLE to add a primary key, then the primary key column(s) must have been declared to (NOT NULL) (when the table was
first created).

https://www.w3schools.com/sql/sql_primarykey.ASP
 SQL Commands for Data Definition(Cont.)
ALTER-DROP
Deleting columns in an existing table using DROP.
- The SQL ALTER statement
- The SQL DROP keyword

Syntax: ALTER TABLE table_name DROP COLUMN column_name;

Example: Alter the Student table by removing the Age attribute(column).

Example Query: ALTER TABLE Student DROP COLUMN Age;

https://www.geeksforgeeks.org/sql-create/
 SQL Commands for Data Definition(Cont.)
ALTER-MODIFY
Modifying columns in an existing table using MODIFY.
- The SQL ALTER statement
- The SQL MODIFY keyword

Syntax:
ALTER TABLE table_name MODIFY column_name column_type(size);

Example: Alter the Student table by modifying the Address attribute(column) to update the data type to varchar(100).

Example Query: ALTER TABLE Student MODIFY Address varchar(100);

https://www.geeksforgeeks.org/sql-create/
 SQL Commands for Data Manipulation
The SQL data manipulation DML statements include:
INSERT
To insert data into a table.
UPDATE
To update existing data within a table.
DELETE
To delete records from a database table.
LOCK
For table control concurrency.
https://www.geeksforgeeks.org/sql-create/
 SQL Commands for Data Manipulation (Cont.)
INSERT INTO
- The INSERT INTO statement of SQL is used to insert a new row in a table.
- There are two ways of using INSERT INTO statement for inserting rows:
ST_ID F_Name L_Name Age
1. Only values: 202122 Amani Ahmed 19

Syntax: INSERT INTO table_name VALUES (value1, value2, value3,…);

Example Query: INSERT INTO Student VALUES (202122, ‘Amani’, ‘Ahmed’,19);

2. Column names and values:

Syntax: INSERT INTO table_name (column1, column2, column3,..) VALUES ( value1, value2, value3,..);

Example Query: INSERT INTO Student(ST_ID,F_Name, L_Name, Age) VALUES (202122, ‘Amani’, ‘Ahmed’,19);

https://www.geeksforgeeks.org/sql-create/
 SQL Commands for Data Manipulation (Cont.)
DELETE FROM
- The DELETE Statement in SQL is used to delete existing records from a table.

1. Delete a single record:

Syntax: DELETE FROM table_name WHERE condition;

Example: Delete from the Student table the student with student id equals 202122.
Example Query: DELETE FROM Student WHERE ST_ID=202122;

2. Delete multiple records :

Syntax: DELETE FROM table_name WHERE condition;

Example: Delete from the Student table all students over the age of 20.

Example Query: DELETE FROM Student WHERE Age>20;

https://www.geeksforgeeks.org/sql-delete-statement/
 SQL Command for Data Retrieval: Queries
SELECT
- SELECT is the SQL data retrieval DQL statement.
- SELECT will retrieve information from the database that matches the specified criteria using the
SELECT/FROM/WHERE framework

The following six commands can be used to create queries that will extract data from a database

Select Indicates a list of columns to be displayed

From Indicates which table(s) the columns selected are to be derived from

Where Is used to restrict records that are to be selected for display

Group by Groups records together based on a specified column list

Having Can only be used in conjunction with the group by clause and places restrictions on groups

Order by Orders the output of the query based on a specified column list
 SQL Command for Data Retrieval: Queries
Displaying Specified Column(s)
Retrieve information from the database that matches the specified criteria using the
simplest form SELECT/FROM commands.
SELECT
SELECT column1, column2, column3,..
Syntax:
FROM FROM table_name;

Example: Display student records (rows) from the Student table based on the following fields
(Student ID, First Name, Last Name, and Age).

SELECT ST_ID, F_Name, L_Name, Age

Example Query:
FROM Student;
 SQL Command for Data Retrieval: Queries
Displaying All Columns
To show all the columns’ values for the rows that match the specified criteria, use an
asterisk ( * ).
SELECT SELECT *
Syntax:
FROM table_name;
FROM
Example: Display all the information from the EMPLOYEE table.

SELECT *
Example Query:
FROM Employee;
 SQL Command for Data Retrieval: Queries
Displaying Each Row Only Once
The DISTINCT keyword can be added to the SELECT statement to prevent duplicate rows from
being displayed.
SELECT EmpID EmpName DeptID

SELECT DISTINCT column_Name 202121 Ahmed 001

Syntax: 202222 Saeed 002
FROM table_name;
FROM 202323 Khalid 001

Example: Display Department Id from the EMPLOYEE table,

no repeated rows should be displayed. DeptID DeptID
001 001
002 002
Example Query: SELECT DISTINCT DeptID 001

FROM Employee;
 SQL Command for Data Retrieval: Queries
Specifying Search Criteria
- The WHERE clause states the matching criteria for the record to be displayed.

SELECT column1, column2, column3,..

SELECT Syntax: FROM table_name
WHERE column(s)condition;
FROM
WHERE Example: Display the following columns (St_Id, First Name, Last Name, Subject Code)
for students enrolled in CPIS 241.

SELECT ST_ID, F_Name, L_Name, Subject_Code

Example Query: FROM Student

WHERE Subject_Code=‘CPIS241’;
 SQL Command for Data Retrieval: Queries
Match Criteria
A list of operators that can be used in a WHERE clause condition.

Operator Definition
= Exact match
SELECT < Less than
<= Less than or equal to
FROM > Greater than
>= Greater than or equal to
WHERE <> Not equal to

Multiple matching criteria may be specified using logical operators.

Operator Definition
AND All conditions must evaluate to true in order for the selected row to match.
OR Only one of the conditions has to evaluate to true in order for the selected row to match.
NOT This operator reverses the outcome of a row condition.
 SQL Command for Data Retrieval: Queries
Operator Examples
Example: Display the names of employees who work in the department number 9 or their
salary code is less than 10.
SELECT SELECT EmpName
FROM EMPLOYEE
WHERE DeptID=9
FROM OR SalaryCode < 10;

WHERE Example: Display the names of employees who work in the department number 9 and their
salary code is less than 10.
SELECT EmpName
FROM EMPLOYEE
WHERE DeptID = 9
AND SalaryCode < 10;
 SQL Command for Data Retrieval: Queries
Finding Data in a List of Values
The WHERE clause may include the IN keyword to specify that a particular column value must be
included in a list of values.
SELECT SELECT EmpName
FROM EMPLOYEE
FROM WHERE DeptID IN (4, 8, 9);

WHERE
Any criteria statement may be preceded by a NOT operator, which is to say that all information will be
shown except that information matching the specified criteria.

SELECT EmpName
FROM EMPLOYEE
WHERE DeptID NOT IN (4, 8, 9);
 SQL Command for Data Retrieval: Queries
Finding Data in a Range of Values
SQL provides a BETWEEN keyword that allows a user to specify a minimum and maximum value on one
line.
SELECT EmpID
202121
EmpName
Ahmed
SalaryCode
10
202222 Saeed 15
SELECT EmpName, SalaryCode
FROM 202323
203245
Khalid
Ali
20
35
FROM EMPLOYEE
202321 Bassam 19
WHERE SalaryCode BETWEEN 10 AND 20;
WHERE
Output:
EmpName SalaryCode
Ahmed 10
Saeed 15
Bassam 19
Khalid 20
 SQL Command for Data Retrieval: Queries
Allowing for Wildcard Searches
- The SQL LIKE keyword allows searches on partial data values.
- Pattern matching is only applicable to those columns that have been assigned data type
SELECT
of text, character or alphanumeric.
FROM - The % symbol is a wildcard character that can represent any character string regardless
of length.
WHERE - The underscore symbol _ is a wildcard character that can be used to represent a single
character in a query.
 SQL Command for Data Retrieval: Queries
Wildcard Search Examples
1. List all employees with a surname starting with the letter B.
SELECT *
FROM EMPLOYEE
SELECT WHERE surname LIKE ‘B%‘;

FROM 2. List all employees with a surname ending with the letter S.
SELECT *
WHERE FROM
WHERE
EMPLOYEE
surname LIKE ‘%S‘;

3. List all employees that have the character pattern 'ea' in their surname.
SELECT *
FROM EMPLOYEE
WHERE surname LIKE ‘%ea% ‘;
 SQL Command for Data Retrieval: Queries
Wildcard Search Examples
4. List all employees that have the digit 3 as the second digit in their employee number.

SELECT SELECT
FROM
*
EMPLOYEE
WHERE EmpNo LIKE ‘_3___ ‘;
FROM
WHERE
 SQL Command for Data Retrieval: Queries
Sorting the Results
- Query results may be sorted using the ORDER BY clause.
SELECT - The output may be sorted in either ascending or descending order. The
default setting is for ascending order.
FROM
- Adding the keywords ASC or DESC to each field in the Order by
ORDER BY command. determines the sort order for the output.
- The default sorting order is the ascending order ASC.
SELECT *
FROM EMPLOYEE
ORDER BY EmpName DESC;
 SQL Command for Data Retrieval: Queries
Built-in SQL Functions
- SQL provides several built-in functions:

COUNT Counts the number of rows that match the specified criteria

MIN Finds the minimum value for a specific column for those rows matching the criteria

MAX Finds the maximum value for a specific column for those rows matching the criteria

SUM Calculates the sum for a specific column for those rows matching the criteria

AVG Calculates the numerical average of a specific column for those rows matching the criteria
 SQL Command for Data Retrieval: Queries
Built-in SQL Functions Examples
1. How many employees work in Department 12?
SELECT COUNT(*)
FROM EMPLOYEE
WHERE DeptID = 12;

2. What is the average salary for employees with more than 10 years of experience?
SELECT Avg(Salary)
FROM EMPLOYEE
WHERE ExpYears>10;

3. What is the lowest and highest salary for an employee in Department 10?

SELECT MIN(Salary),MAX(Salary)
FROM EMPLOYEE
WHERE DeptID = 10;
 SQL Command for Data Retrieval: Queries
Built-in SQL Functions Examples
4. What is the total commission paid to employees hired after 2012?

SELECT SUM(Commission)
SUM(Commission)
FROM EMPLOYEE
4500
WHERE HireDate > 1.1.2012;

Renaming Headings
❑ The standard heading for a calculated field in a report does not indicate enough information as to the
meaning of the calculation.
❑ The AS command can be used to rename the heading of a field in a report.

SELECT SUM(Commission)AS “Total Commission” Total Commission

FROM EMPLOYEE
4500

WHERE HireDate > 1.1.2012;

 SQL Command for Data Retrieval: Queries
Providing Subtotals: GROUP BY
- Subtotals may be calculated by using the GROUP BY clause.

SELECT - The HAVING clause may be used to restrict which data is displayed.

- Example: Display Department Id and number of employees, for departments with more than
FROM
three employees.
EmpID EmpName DeptID
GROUP BY SELECT DeptID, COUNT(*) as “Number of Employees” 202121 Ahmed 001
202222 Saeed 002
FROM EMPLOYEE
202323 Khalid 001
GROUP BY DeptID
HAVING HAVING COUNT(*)>3;
203245 Ali 001
202321 Bassam 002
202423 Rami 001
213245 Majed 001
Output:
244567 Saleh 002
DeptID Number of Employees
001 5
 SQL Command for Data Retrieval: Queries
Retrieving Information from Multiple Tables
Subqueries
❑ As stated earlier, the result of a query is a relation. As a result, a query may feed another query.
This is called a subquery.

Joins
❑ Another way of combining data is by using a join .
- Join [also called an Inner Join]
- Left Outer Join
- Right Outer Join
 SQL Command for Data Retrieval: Queries
Subquery Example
- Example: Display Employee Name for employees who work in the department that
contains the word “ Account”.
SELECT EmpName
FROM EMPLOYEE
WHERE DeptID IN
EmpID EmpName DeptID DeptID DeptName
(SELECT DeptID 202121 Ahmed 001 001 Accounting
FROM DEPARTMENT 202222 Saeed 002 002 Sales
202323 Khalid 001
WHERE DeptName LIKE 'Account% ');

Output:
EmpName

SELECT EmpName Ahmed

FROM EMPLOYEE Khalid

WHERE DeptID IN (001);

 SQL Command for Data Retrieval: Queries
Joins Example
- Example: Display Employee Name for employees who work in the department that contains the
word “ Account”.
SELECT EmpName
FROM EMPLOYEE AS E, DEPARTMENT AS D EmpID EmpName DeptID DeptID DeptName
202121 Ahmed 001 001 Accounting
WHERE E.DeptID = D.DeptID 202222 Saeed 002 002 Sales
AND DeptName LIKE 'Account% ‘; 202323 Khalid 001

OR Output:
EmpName
SELECT EmpName
Ahmed
FROM EMPLOYEE, DEPARTMENT Khalid
WHERE EMPLOYEE.DeptID = DEPARTMENT.DeptID
AND DeptName LIKE 'Account% ‘;
 Modifying Data using SQL
Changing Data Values: UPDATE
- To change the data values in an existing row (or set of rows) use the UPDATE statement.

Example: Update the phone number to (791-555-1234) for the employee with an ID equals 29
UPDATE
in the EMPLOYEE table.
UPDATE EMPLOYEE
SET
SET Phone= ‘791-555-1234’
WHERE EmpID = 29;
WHERE
Example: Update the department ID to 44 for employees whose name begins with the letters(Kr) in
the EMPLOYEE table.
UPDATE EMPLOYEE
SET DeptID= 44
WHERE EmpName LIKE 'Kr % ';
 Modifying Data using SQL
Deleting Data: DELETE
- To delete a row or set of rows from a table use the DELETE statement.

Example: Delete the record of employee with ID equal to 29 from EMPLOYEE table.
DELETE
DELETE FROM EMPLOYEE
WHERE EmpID = 29;
FROM
WHERE
Example: Delete records of employees whose name begins with the letters(Kr) from the EMPLOYEE
table.
DELETE FROM EMPLOYEE
WHERE EmpName LIKE 'Kr % ';
 Modifying Data using SQL
Deleting Database Objects: DROP

- To remove unwanted database objects from the database, use the SQL DROP statement.
DROP - Warning… the DROP statement will permanently remove the object (table) and all data.

TABLE
DROP TABLE EMPLOYEE;
 SQL Views
View in SQL
- A SQL View is a virtual table created by a DBMS-stored SELECT statement
that can combine access to data in multiple tables and even in other views.

https://www.shekhali.com/view-in-sql-server/
 References
1. KROENKE and AUER - DATABASE CONCEPTS (6th Edition) Copyright © 2013 Pearson Education, Inc. Publishing as
Prentice Hall

2. Rob,P, Morris,S & Coronel,C.& Database Systems, Design, Implementation, and Management, 10th Ed,
Thomson Course Technology ISBN: 13:978-1-111-96960-8

3. Silberschatz, Korth and Sudarshan Database System Concepts, 5th Ed.

4. https://www.geeksforgeeks.org/relational-model-in-dbms/
5. https://www.javatpoint.com/dbms-keys#:~:text=Super%20key%20is%20a%20set,can%20also%20be%20a%20key.