Week 8
Structured Query Language (SQL)
Writing SQL Commands
• SQL statement consists of reserved words and
user-defined words.
– Reserved words are a fixed part of SQL and must
be spelt exactly as required and cannot be split
across lines.
– User-defined words are made up by user and
represent names of various database objects such
as relations, columns, views.
2
CREATE DATABASE
• The CREATE DATABASE statement is used to
create a database.
• Syntax:
CREATE DATABASE dbname;
• Example:
CREATE DATABASE studentInformationSystem;
USE STATEMENT
• The USE statement is used to choose a
database.
• Syntax:
USE dbname;
• Example:
USE studentInformationSystem;
CREATE TABLE
• The CREATE TABLE statement is used to create a table in a
database.
• Tables are organized into rows and columns
• Table must have a name
• Syntax:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
An ERD example
matrix name
code
STUDENT ENROL SUBJECT
name credit
grade
Tables for ERD
STUDENT
matrix name
SUBJECT
code name credit
ENROL
matrix code grade
CREATE TABLE
• The CREATE TABLE statement is used to create a table in a
database.
• Tables are organized into rows and columns
• Table must have a name
• Syntax:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
CREATE TABLE EXAMPLE
STUDENT
matrix name
The SQL codes:
CREATE TABLE student
(
matrix varchar(6),
name varchar(40),
PRIMARY KEY (matrix)
);
CREATE TABLE EXAMPLE (cont.)
SUBJECT
code name credit
The SQL codes:
CREATE TABLE subject
(
code varchar(7),
name varchar(20),
credit int,
PRIMARY KEY (code)
);
CREATE TABLE EXAMPLE (cont.)
ENROLL
matric code grade
The SQL codes:
CREATE TABLE enroll
(
matric varchar(6),
code varchar(7),
grade varchar(2),
FOREIGN KEY (matrix) REFERENCES student(matrix),
FOREIGN KEY (code) REFERENCES subject(code)
);
STUDENT
matrix name
113100 Nurfatin binti Mohd
113101 Siti binti Samuri
SQL INSERT INTO Statement
• The INSERT INTO statement is used to insert
new records in a table.
• Syntax:
INSERT INTO table_name
VALUES (value1,value2,value3,...);
INSERT INTO EXAMPLE
The SQL codes:
INSERT INTO student (matrix, name)
VALUES ('113100', 'Nurfatin binti Mohd‘);
INSERT INTO student (matrix, name)
VALUES ('113101', 'Siti binti Samuri‘);
SQL SELECT STATEMENT
• The SELECT statement is used to display
records from a table
• The following SQL code will display all records
from table student:
SELECT *FROM student;
SELECT Statement
FROM Specifies table(s) to be used.
WHEREFilters rows.
GROUP BY Forms groups of rows with same
column value.
HAVING Filters groups subject to some
condition.
SELECT Specifies which columns are to
appear in output.
ORDER BY Specifies the order of the output.
16
Staff
17
Example: All Columns, All Rows
Purpose: To list full details of all staff.
SELECT staffNo, fName, lName, address, position,
DOB, salary, branchNo
FROM Staff;
• Can use * as an abbreviation for ‘all columns’:
SELECT *
FROM Staff;
18
Example All Columns, All Rows
19
Example: Specific Columns, All Rows
Produce a list of salaries for all staff, showing
only staff number, first and last names, and
salary.
SELECT staffNo, fName, lName, salary
FROM Staff;
20
Example: Specific Columns, All Rows
21
Example: Use of DISTINCT
List the property numbers of all properties that
have been viewed.
SELECT propertyNo
FROM Viewing;
Viewing
22
Example: Use of DISTINCT
• Use DISTINCT to eliminate duplicates:
SELECT DISTINCT propertyNo
FROM Viewing;
23
Example: Calculated Fields
Produce list of monthly salaries for all staff,
showing staff number, first/last name, and
salary.
SELECT staffNo, fName, lName, salary/12
FROM Staff;
24
Example: Calculated Fields
• To name column, use AS clause:
SELECT staffNo, fName, lName, salary/12
AS monthlySalary
FROM Staff;
25
Example: Comparison Search Condition
List all staff with a salary greater than 10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;
26
Example: Range Search Condition
List all staff with a salary between 20000 and
30000
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary>=20000 AND salary <= 30000;
27
Example: Pattern Matching
Find all owners with the string ‘Glasgow’ in their address.
SELECT ownerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;
28
Example: Single Column Ordering
List salaries for all staff, arranged in descending
order of salary.
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
29
Example: Single Column Ordering
30
SELECT Statement - Aggregates
ISO standard defines five aggregate functions:
• COUNT returns number of values in specified column.
• SUM returns sum of values in specified column.
• AVG returns average of values in specified column.
• MIN returns smallest value in specified column.
• MAX returns largest value in specified column.
31
Example: Use of MIN, MAX, AVG
Find minimum, maximum, and average
staff salary.
SELECT MIN(salary) AS myMin,
MAX(salary) AS myMax,
AVG(salary) AS myAvg
FROM Staff;
32
Example: Subquery of equality
• The following SQL code will display certain
records from table student based on the
condition given:
SELECT *FROM student
WHERE matrix=113100;
OUTPUT:
matrix name age address Email
113100 Nurfatin binti 19 No. 8, Jalan 2/4, Taman fatin@[Link]
Mohd Seri Serdang, 43300
Seri Kembangan,
Selangor
SQL DELETE STATEMENT
• The DELETE statement is used to delete rows
in a table.
• Syntax:
DELETE FROM table_name
WHERE some_column=some_value;
• Example:
DELETE FROM student
WHERE matrix=113100;
SQL DROP TABLE
• The DROP TABLE statement is used to delete
a table in a database.
• Syntax:
DROP TABLE table_name;
• Example:
DROP TABLE student;