Week 8 Structured Query Language (SQL)
Week 8 Structured Query Language (SQL)
Week 8 Structured Query Language (SQL)
2
Writing SQL Commands
3
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
address
Tables for ERD
STUDENT
matrix name age address email
COURSE
course_code course_name credit matrix
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 age address email
16
Example: All Columns, All Rows
17
Example All Columns, All Rows
18
Example: Specific Columns, All Rows
19
Example: Specific Columns, All Rows
20
Example: Use of DISTINCT
SELECT propertyNo
FROM Viewing;
21
Example: Use of DISTINCT
• Use DISTINCT to eliminate duplicates:
22
Example: Calculated Fields
23
Example: Calculated Fields
24
Example: Comparison Search Condition
25
Example: Range Search Condition
26
Example: Range Search Condition
27
Example: Range Search Condition
28
Example: Pattern Matching
29
Example: Single Column Ordering
30
Example: Single Column Ordering
31
SELECT Statement - Aggregates
33
Example: Use of Count
34
Example: Use of Count
numberOfStaf
numberOfStaff
35
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@usim.edu.my
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;