DFC 20123:

DATABASE DESIGN

CHAPTER 4
STRUCTURED QUERY LANGUAGE
(SQL)

1
 APPLY SQL COMMANDS TO A DATABASE
4.1.1 STATE THE USE OF STRUCTURED QUERY LANGUAGE (SQL).
4.1.2 EXPLAIN THE SQL DATA TYPES
4.1.3 IDENTIFY THE TYPES OF SQL STATEMENTS:
4.1.4 IDENTIFY THE DDL STATEMENTS IN DEFINING THE TABLE STRUCTURE
4.1.5 IDENTIFY THE TYPES OF CONSTRAINTS IN DDL STATEMENTS: COLUMN CONSTRAINTS
AND TABLE CONSTRAINTS
4.1.6 USE THE FUNCTIONS OF THE BASIC DDL COMMANDS:
4.1.7 IDENTIFY THE USES OF DML STATEMENTS
4.1.8 USE THE FUNCTIONS OF THE FOLLOWING DML COMMANDS:
4.1.9 USE THE FUNCTIONS SQL ADVANCED COMMANDS:
4.1.10 USE THE FUNCTIONS SQL AGGREGATE FUNCTIONS:

2
STRUCTURED QUERY LANGUAGE

 SQL, short for Structured Query Language is a

simple programming language used for accessing
and managing data in relational databases.
 SQL can be divided into two parts:
 The Data Manipulation Language (DML)
 The Data Definition Language (DDL)
 Transaction Control Language (TCL)
 Data Control Language (DCL)
3
 USED OF SQL

 Allow user to create database and relation structure

 Perform basic data management tasks, such as the
insertion, modification, and deletion of data from the
relations

4
EXPLAIN SQL DATA TYPES

 Each column in a database table is required to have a name and a data type.
 An SQL developer must decide what type of data that will be stored inside each
column when creating a table.
 The data type is a guideline for SQL to understand what type of data is expected
inside of each column, and it also identifies how SQL will interact with the
stored data.
 In MySQL there are three main data types: string, numeric, and date and time.

 Note: Data types might have different names in different database. And even if the
name is the same, the size and other details may be different! Always check the
documentation! 5
STRING DATA TYPES

6
NUMERIC DATA TYPES

7
DATE & TIME DATA TYPES

8
9
 TYPES OF SQL

 DDL (Data Definition Language)

 Consists of the SQL commands that can be used to define the database schema. It simply
deals with descriptions of the database schema and is used to create and modify the
structure of database objects in the database.
 Examples of DDL commands:
 CREATE – is used to create the database or its objects (like table, index, function, views, store
procedure and triggers).
 DROP – is used to delete objects from the database.
 ALTER-is used to alter the structure of the database.
 TRUNCATE–is used to remove all records from a table, including all spaces allocated for the
records are removed.
 COMMENT –is used to add comments to the data dictionary.
 RENAME –is used to rename an object existing in the database.
10
TYPES OF SQL

 DML (Data Manipulation Language)

 The SQL commands that deals with the manipulation of data present
in the database belong to DML or Data Manipulation Language and
this includes most of the SQL statements.
 Examples of DML:
 INSERT – is used to insert data into a table.
 UPDATE – is used to update existing data within a table.
 DELETE – is used to delete records from a database table.

11
TYPES OF SQL

 DCL (Data Control Language)

 DCL includes commands such as GRANT and REVOKE which
mainly deals with the rights, permissions and other controls of the
database system.
 Examples of DCL commands:
 GRANT-gives user’s access privileges to database.
 REVOKE-withdraw user’s access privileges given by using the
GRANT command.

12
TYPES OF SQL

 TCL (Transaction Control Language)

 TCL commands deals with the transaction within the database.
 Examples of TCL commands:
 COMMIT– commits a Transaction.
 ROLLBACK– rollbacks a transaction in case of any error occurs.
 SAVEPOINT–sets a savepoint within a transaction.
 SET TRANSACTION–specify characteristics for the transaction.

13
DDL STATEMENTS IN DEFINING THE TABLE
STRUCTURE

 DDL statements are used to build and modify the structure of your tables and
other objects in the database. When you execute a DDL statement, it takes effect
immediately.
 DDL commands :
 CREATE
 USE
 ALTER
 DROP

14
TYPES OF CONSTRAINTS IN DDL STATEMENTS

 SQL constraints are used to specify rules for the data in a table.
 Constraints are used to limit the type of data that can go into a
table.
 This ensures the accuracy and reliability of the data in the table.
 If there is any violation between the constraint and the data action,
the action is aborted.
 Constraints can be :
 column level
 table level. 15
 Column level constraints apply to a column, and table level constraints apply to
the whole table.
 The following constraints are commonly used in SQL:
 NOT NULL - Ensures that a column cannot have a NULL value
 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies
each row in a table
 FOREIGN KEY - Uniquely identifies a row/record in another table
 CHECK - Ensures that all values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column when no value is specified
 INDEX - Used to create and retrieve data from the database very quickly
16
CONNECTING TO MYSQL USING COMMAND
PROMPT FOR WINDOWS

17
USE FUNCTIONS OF THE BASIC DDL
COMMANDS
CREATING DATABASE AND TABLE
 Create database
 Syntax: CREATE DATABASE database_name;
 Example : CREATE DATABASE Company;

 Create table
 Syntax:

CREATE TABLE table_name

(
column_name1 data type1 [NULL|NOT NULL],
18

column_name1 data type2 …….

USE TABLE

 USE
 Syntax: USE database_name;
 Example : USE Company;

 Create table
 Syntax:

CREATE TABLE table_name

(
column_name1 data type1 [NULL|NOT NULL],
column_name1 data type2 ……. 19

);
CREATE TABLE : EXAMPLE
CREATE TABLE worker
(
workerno VARCHAR (5) NOT NULL,
workername VARCHAR (15),
position VARCHAR (15),
address VARCHAR (20),
entrydate DATE,
tel_no VARCHAR (7),
salary DECIMAL (7,0),
20
PRIMARY KEY (workerno)
);
WORKER TABLE

21
ANOTHER TABLE : BUILDING
CREATE TABLE building
(
buildno VARCHAR(5) NOT NULL,
address VARCHAR(20),
city VARCHAR(10),
buildtype VARCHAR(10) ,
roomno INTEGER,
rent DECIMAL(4,0),
workerno VARCHAR(5),
PRIMARY KEY (buildno),
22
FOREIGN KEY (workerno) REFERENCES worker (workerno)
);
BUILDING TABLE

23
 ALTER TABLE
 ALTER TABLE statement is used to add or drop columns in
an existing table.
 Syntax:
ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name

24

DROP COLUMN column_name

 EXAMPLE: ADD COLUMN

 Consider this table:

 Add column “Gender” in the worker table:

ALTER TABLE worker
ADD gender VARCHAR (10);
25
DROP DATABASE/TABLE

 DROP DATABASE
 Syntax: DROP database database_name;
 Example: DROP DATABASE company;

 DROP TABLE
 Syntax: DROP TABLE table_name;
 Example: DROP TABLE worker;
26

**Will delete the entire table.

 DML

 Data Manipulation Language (DML) is a family of computer languages

used by computer programs and/or database users to insert, delete and update
data in a database.
 Data Manipulation Language comprises the 'SQL-data change' statements,
which modify stored data but not the schema or database objects.
 DDL commands:
 INSERT
 SELECT
 UPDATE
 DELETE
27
 INSERT STATEMENT
 INSERT INTO - used to insert new rows into a table.
 Syntax:

INSERT INTO table_name (column1, column2,…)

VALUES (value1, value2,…);
 Enter data to each column:

INSERT INTO worker VALUES INSERT INTO worker VALUES

('A01','JOHN','MANAGER','CHERAS', ('A01', 'JOHN', 'MANAGER', 'CHERAS',
'01- JANUARY-1995',12345,7000) – '1995-01-01', 12345, 7000) – mysql
oracle

 Enter data into selected columns:

UPDATE worker SET <COLUMN YG NK INSERT DATA> WHERE 28

<PRIMARY KEY> = ??
WORKER TABLE

29
BUILDING TABLE

30
SELECT STATEMENT

 With SQL, we can query a database and have a result set returned.
 All queries are based on the SELECT command.
 Syntax:

SELECT column_name(s)
FROM table_name;

* SELECT, FROM can be written in lower case.

31
SELECT STATEMENT

 Select all columns:

SELECT *
FROM worker;
Result: will display the entire table.

32
 SELECT STATEMENT

 Select certain columns:  Select all columns:

SELECT workerno, workername SELECT *

FROM worker; FROM worker;
 Result:  Result: will display the entire table.

33
UPDATE STATEMENT

 The UPDATE statement is used to modify the data in a table.

 Syntax:
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value

34
UPDATE STATEMENT

 The UPDATE statement is used to modify the data in

a table.
 Syntax:
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value;

35
 EXAMPLE
 Building

 Change the number of room of buildno B04:

UPDATE building
SET roomno = 4
WHERE buildno = 'B04';
36
EXAMPLE
UPDATE building
SET roomno=5, rent=475
WHERE buildno = 'B04'

 Update several columns in a row:

 If we want to change the room no and rent of building:
 Result:

37
DELETE STATEMENT

 To delete all rows:

 It is possible to delete all rows in a table without deleting
the table. This means that the table structure, attributes, and
indexes will be intact:
DELETE FROM table_name
Or
DELETE * FROM table_name
38
DELETE STATEMENT
 DELETE – to delete rows in a table.
 Syntax:
DELETE FROM table_name
WHERE column_name = some_value;
 Consider this table:

39
EXAMPLE
 To delete a row – deleting building ‘B06’ record.

DELETE FROM building

WHERE buildno = 'B06';
Result:

 To delete all rows in a table:

 It is possible to delete all rows in a table without deleting the table. This means
that the table structure, attributes, and indexes will be intact:
40
DELETE FROM table_name
Or
SQL QUERIES

• Some of SQL advanced commands :

• DISTINCT
• WHERE
• AND & OR
• ORDER BY
• WILDCARDS
• IN & BETWEEN
• INTERSECT
41
• GROUP BY & HAVING
DISTINCT STATEMENT

 The DISTINCT keyword is used to return only distinct (different)

values.
 Consider this table: worker

42
DISTINCT STATEMENT

 If we use: SELECT address FROM worker;

result

43
 DISTINCT STATEMENT

 If we use:
SELECT DISTINCT address FROM worker;
 Result:

44
CALCULATED FIELD

 Example:
SELECT workerno, workername, salary /2
FROM worker;
 Result:

45
RENAME COLUMN

 To rename a column, use AS statement.

 Example:

SELECT workername as Name

FROM worker;
 Result:

46
WHERE STATEMENT

 WHERE clause is to specify a selection criterion.

 Syntax:
SELECT column_name(s)
FROM table_name
WHERE conditions;

47
WHERE STATEMENT

 With WHERE clause, the following operators can be used:

Operator Description
= Equal
<> Not equal
> Greater than *in some versions of SQL, <>
< Less than operator may be written as !=
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you
want to return for at least one of 48

the columns
SIMPLE QUERIES…

 List all the workers you earn more than 4000.

SELECT workername, salary
FROM worker
WHERE salary >4000;
 Result:

49
SIMPLE QUERIES…
WHERE & OR

 List all worker who live in Bangi or Kajang.

SELECT workername, address
FROM worker
WHERE address = 'Bangi'
OR address = 'Kajang‘;
 Result:

*OR is used to join two or

more conditions.

50
SIMPLE QUERIES…
WHERE, AND & BETWEEN

 List worker who live in Bangi and her name is Ani

SELECT workername, salary
FROM worker
WHERE salary BETWEEN 3000 AND 9000;
 Result:

*BETWEEN…AND operator selects a

range of data between two values.
*can be numbers, texts or dates.

51
SIMPLE QUERIES…
WHERE & IN
 List the Director and Vice Director.

SELECT workername, position

FROM worker
WHERE position
IN ('MANAGER', 'VICE MANAGER');
 Result:

*IN can be used if you know

the exact value that you
seek for at least one of the
columns.

52
 List of data that were taken from other table.
 For Example :

Consider the table building

List of workername & address from table building :

RESULT :

SELECT workername, workerno, address

FROM worker
WHERE workerno IN 53

(SELECT workerno FROM building);

SIMPLE QUERIES…
WHERE & NOT IN

 List the worker who is not living in Bangi.

SELECT workername, address
FROM worker
WHERE address NOT
RESULT :
IN ('BANGI');
Or
SELECT workername, address
FROM worker
WHERE address <> 'BANGI‘;
54
SIMPLE QUERIES…
WHERE & LIKE

 List all the building in Taman Kota.

SELECT buildno, address
FROM building
WHERE address LIKE '%TAMAN KOSAS%';
Result:

55
WILDCARD STATEMENT
 LIKE - used to specify a search for a pattern in a column.

 A "%" sign can be used to define wildcards (missing letters in the pattern) both before and

after the pattern.

 All the wildcards can also be used in combinations!

 Using LIKE:

a) The following SQL statement will return persons with first names that start with an 'O':

SELECT *

FROM Persons

WHERE FirstName 56

LIKE 'O%' ;
WILDCARD STATEMENT

 A wildcard character is used to substitute one or more characters in a string.

 Wildcard characters are used with the SQL LIKE operator. The LIKE operator is
used in a WHERE clause to search for a specified pattern in a column.

57
WILDCARD STATEMENT

58
WILDCARDS STATEMENT %

b)The following SQL statement will return persons with first names that end with an
'a':
SELECT * FROM Staff
WHERE FirstName
LIKE '%a' ;

c)The following SQL statement will return persons with first names that contain the
pattern 'la':
SELECT *
FROM Staff
WHERE FirstName
59
LIKE '%la%' ;
WILDCARDS STATEMENT _
• Consider the following table :

60
• The following SQL statement selects all customers with a City starting
with any character, followed by "ondon":
Example
SELECT * FROM Customers
WHERE City LIKE '_ondon';

61
• The following SQL statement selects all customers with a City
starting with "L", followed by any character, followed by "n",
followed by any character, followed by "on":

Example
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

62
SIMPLE QUERIES…
NULL

 Find worker who doesn’t have phone number.

 Consider this table : worker

63
SIMPLE QUERIES…
NULL

SELECT workername, tel_no

FROM worker
WHERE tel_no IS NULL;

 Result:

64
ORDER BY STATEMENT

 The ORDER BY clause is used to sort the rows.

 Syntax:
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC];

65
ORDER BY STATEMENT

 ORDERS table:

Company OrderNumber
Sega 3412
ABC Shop 5678 SELECT Company, OrderNumber
W3Schools 6798 FROM Orders
W3Schools 2312 ORDER BY Company;

 Example: to display the company names in alphabetical

order:

66
ORDER BY STATEMENT

 Result:
Company OrderNumber
ABC Shop 5678 SELECT Company, OrderNumber
Sega 3412 FROM Orders
W3Schools 6798 ORDER BY Company, OrderNumber;
W3Schools 2312

 Example: to display the company names in alphabetical

order AND the OrderNumber in numerical order:

67
ORDER BY STATEMENT

 Result:
Company OrderNumber
ABC Shop 5678
Sega 3412 SELECT Company, OrderNumber
W3Schools 2312 FROM Orders
W3Schools 6798 ORDER BY Company DESC;

 Example: To display the company names in reverse

alphabetical order:

68
 ORDER BY STATEMENT

Company OrderNumber SELECT Company, OrderNumber

W3Schools 6798 FROM Orders
 Result:
W3Schools 2312 ORDER BY OrderNumber ASC;
Sega 3412
ABC Shop 5678

 Example: To display the order number in ascending order:

69
ORDER BY STATEMENT

 Result:

Company OrderNumber
W3Schools 2312
Sega 3412
ABC Shop 5678
W3Schools 6798

70
INTERSECT STATEMENT

 The INTERSECT clause in SQL is used to combine two SELECT statements but
the dataset returned by the INTERSECT statement will be the intersection of the
data-sets of the two SELECT statements.
 In simple words, the INTERSECT statement will return only those rows which
will be common to both of the SELECT statements.

The INTERSECT statement will return only those

rows present in the DARK GREEN shaded region.
i.e. common to both of the data-sets.

71

Note: The number and type of fields present in the two data-sets must be same and similar.
INTERSECT STATEMENT

• Syntax :

SELECT column-1, column-2 …… FROM table 1 WHERE…..

INTERSECT
SELECT column-1, column-2 …… FROM table 2 WHERE…..

72
EXAMPLE
• Consider the following tables and sample queries:

SELECT ID, NAME, Amount, Date

FROM Customers

LEFT JOIN Orders ON Customers.ID

= Orders.Customer_id

INTERSECT

SELECT ID, NAME, Amount, Date

FROM Customers RIGHT JOIN Orders

ON Customers.ID =

Orders.Customer_id;

73
EXAMPLE

74
GROUP BY STATEMENT

 GROUP BY... was added to SQL because aggregate functions (like

SUM) return the aggregate of all column values every time they are
called, and without the GROUP BY function it was impossible to
find the sum for each individual group of column values.
 Syntax:
SELECT column, SUM(column)
FROM table
GROUP BY column ;
* SUM can be changed with another aggregate functions

75
EXAMPLE

 Sales table:

Company Amount
W3Schools 5500
IBM 4500
W3Schools 7100

 Finding the sales amount for each company:

SELECT Company, SUM(Amount) FROM Sales;

76
 Is this correct? Why?
EXAMPLE

SELECT Company, SUM(Amount)

FROM Sales
GROUP BY Company;

77
HAVING STATEMENT

 HAVING... was added to SQL because the WHERE

keyword could not be used against aggregate
functions (like SUM), and without HAVING... it
would be impossible to test for result conditions.
 HAVING clause is used to filter data based on the
group functions. This is similar to WHERE
condition but is used with group functions.
 Syntax:
SELECT column, SUM(column)
FROM table
GROUP BY column
HAVING SUM (column) condition value;
78
EXAMPLE…
 Sales:
Company Amount
W3Schools 5500
IBM 4500
W3Schools 7100

SELECT company, SUM( amount )

FROM sales
GROUP BY company
HAVING SUM( amount ) >10000;
79
AGGREGATE FUNCTIONS

 Some basic SQL aggregate functions:

FUNCTION OUTPUT
COUNT Return the number of rows containing not null values
SUM The sum of all values for a selected attribute in a given
column
AVG Return average of values in a specified column
MAX Return largest of value in a specified column
MIN Return smallest of valuesin a specified column

80
AGGREGATE FUNCTIONS

 AVG and SUM - Can be used for numeric data only

 COUNT, MAX and MIN - Can be used for numeric and non-
numeric data.

81
AGGREGATE FUNCTIONS

 COUNT EXAMPLE:
 Calculate the number of employees who lives in Bangi.

SELECT COUNT( ADDRESS ) AS Live_in_Bangi

FROM worker
WHERE ADDRESS = 'BANGI';

82
 EXPLAIN THE DIFFERENCE…

Company OrderNumber
Sega 3412
ABC Shop 5678
W3Schools 6798
W3Schools 2312

SELECT COUNT(company)
SELECT COUNT
AS Company (DISTINCT company) AS
FROM customer_order; Company FROM
customer_order;

83
AGGREGATE FUNCTIONS

 SUM EXAMPLE:
SELECT worker no, SUM( rent ) AS RENT
FROM building
WHERE workerno = 'A02‘;

84
AGGREGATE FUNCTIONS

 MAX, MIN AND AVERAGE EXAMPLE:

SELECT
min( salary ) AS MIN,
max( salary ) AS MAX,
avg( salary ) AS SALARY
FROM worker;

85