sql notes full.pdf
TRANSCRIPT
ERD is a diagram that depicts entity
relationship model entities, attributes
and relations. It also displays
connectivity and cardinality.
Student Student
ID
StudentName StudentAddress
10DIP13F
1001
MOHD ASYRAF BIN
HAMID
5, Jalan Telaga
10DIP13F
1002
AHMAD BIN ISHAK 102, Lorong Sutera
Course
COURSE ID NAME COURSE
DFC2033 DATABASE SYSTEM
DFC2043 OPERATING SYSTEM
Learning Outcomes:Student should able to
State the use of Structured Query Language (SQL). Define the two main sublanguages in SQL
a. Data Definition Language (DDL)b. Data manipulation Language (DML)
Use the functions of the four basic DDL commandsa. CREATEb. USEc. ALTERd. DROP
Structured Query Language (SQL), is a database
language.
Allow user to:
1. create database and table structures,
2. to perform basic data management chores (add,
delete, and modify),
3. and to perform complex queries design to
transform the raw data into useful information.
INTRODUCTION TO SQL
SQL meets these ideal database language requirements well.
1. It is a data definition language (DDL) : SQL includes commands to create the database table structures, as well as to define access rights to the database.
2. It is a data manipulation language (DML) : It includes commands to insert, update, delete and retrieve data within the database table.
SQL is relatively easy to learn. Its command set has a basic vocabulary of less than 100 words.SQL is a non- procedural language : you have to command what is to be done ; you don’t have to worry about how it is to be done.
The command structure consists of standard English words such as CREATE TABLE, INSERT, SELECT.
Example:
CREATE TABLE Staff
(staffno VARCHAR (5),
lName VARCHAR (15),
salary DECIMAL (7,2));
INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);
SELECT staffno, lName, salary
FROM Staff
WHERE salary > 10000;
COLLECTION OF DATA
MANAGED USING DATABASE MANAGEMENT SYSTEM (DBMS) eg:
› SQL SERVER
› ORACLE
STRUCTURED QUERY LANGUAGE
LANGUAGE USED TO COMMUNICATE WITH A RELATIONAL DBMS
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
SELECTINSERTUPDATEDELETE
CREATEALTERDROP
COMMITROLLBACK
DCL
GRANTREVOKE
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
CREATEALTERDROP
DCL
Syntax:
CREATE DATABASE <Database_name>
PSPCREATE DATABASE PSP;
Database created.
USE PSP;
Database PSP is open and ready to use.
Syntax:
CREATE TABLE <Table_name>
(Column datatype);
CREATE TABLE STAFF
(Staff_ID VARCHAR(7) NOT NULL UNIQUE,
Staff_Name VARCHAR(100) ,
Salary numeric(6,2),
Address VARCHAR(200)
PRIMARY_KEY (Staff_ID));
Table created.
PSP
STAFF
**Primary Key enforces uniqueness of the column on which they are defined.
Staff_ID Staff_Name Salary Address
Table Staff
Syntax:
ALTER TABLE table
ADD (Column datatype)
[, Column datatype]…;
Staff_ID Staff_Name Salary Address Test
15.21
ALTER TABLE STAFFADD Test numeric (5,2);
Table altered.
Syntax:
ALTER TABLE table
MODIFY (Column datatype)
[, Column datatype]…;
Staff_ID Staff_Name Salary Address Test
15.2
ALTER TABLE STAFFMODIFY Test Numeric (5,1);
Table altered.
Syntax:
ALTER TABLE table
DROP (Column);
Staff_ID Staff_Name Salary Address
ALTER TABLE STAFFDROP Test;
Table altered.
Syntax:DROP TABLE table;
DROP TABLE STAFF;
TABLE DROPPED
STRUCTURED QUERY LANGUAGE
LANGUAGE USED TO COMMUNICATE WITH A RELATIONAL DBMS
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
INSERTUPDATEDELETE
DCL
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
INSERTUPDATEDELETE
DCL
Syntax:
INSERT INTO table [(Column [, Column…])]
VALUES (Value [, Value…]);
**Note: This will insert only one row.
INSERT INTO STAFF (Staff_Id, Staff_Name,
Salary, Address)
VALUES
(‘1’, ‘ ALEX’, ‘1000.10’, ‘SELANGOR’)
1 row created.
Staff_ID Staff_Name Salary Address
1 ALEX 1000.10 SELANGOR
INSERT INTO STAFF (Staff_Id, Staff_Name,
Salary, Address)
VALUES
(‘2’, ‘ AHMAD’, ‘2000.20’, NULL)
1 row created.
Staff_ID Staff_Name Salary Address
1 ALEX 1000.10 SELANGOR
2 AHMAD 2000.20
INSERT INTO STAFF VALUES
(‘3’, ‘ RAJU’, ‘3000.30’, ‘PERAK’)
1 row created.
Staff_ID Staff_Name Salary Address
1 ALEX 1000.10 SELANGOR
2 AHMAD 2000.20
3 RAJU 3000.30 PERAK
INSERT INTO STAFF (Address, Staff_ID,
Staff_Name
VALUES
(‘PERAK’, ‘4’, ‘ ALI’, )
Staff_ID Staff_Name Salary Address
1 ALEX 1000.10 SELANGOR
2 AHMAD 2000.20
3 RAJU 3000.30 PERAK
4 ALI PERAK
Syntax:
UPDATE table
SET Column = Value [, Column = Value,…]
[WHERE condition]
**Note: Update more than one row at a time, if required.
UPDATE STAFF
SET Address = ‘MELAKA’
WHERE Staff_Id = ‘2’;
1 row updated
Staff_ID Staff_Name Salary Address
1 ALEX 1000.10 SELANGOR
2 AHMAD 2000.20 MELAKA
3 RAJU 3000.30 PERAK
4 ALI PERAK
UPDATE STAFF
SET Address = ‘JOHOR’
4 rows updated
Staff_ID Staff_Name Salary Address
1 ALEX 1000.10 JOHOR
2 AHMAD 2000.20 JOHOR
3 RAJU 3000.30 JOHOR
4 ALI JOHOR
UPDATE STAFF
SET Address = ‘SELANGOR’, Salary = ’4000’
WHERE Staff_ID = ‘1’;
1 row updated
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
2 AHMAD 2000.20 JOHOR
3 RAJU 3000.30 JOHOR
4 ALI PERAK
Syntax:
DELETE [FROM] table
[WHERE condition];
**If no rows are deleted, a message “0 rows deleted” is returned.
DELETE FROM STAFF
WHERE Staff_ID = ‘2’;
1 row deleted
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
3 RAJU 3000.30 JOHOR
4 ALI PERAK
DELETE FROM STAFF;
3 rows deleted
Staff_ID Staff_Name Salary Address
Objective
Use data manipulation statements of
SQL on a given database: SELECT
commands
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
SELECT
DCL
SELECTING ALL COLUMNS
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
2 AHMAD 2000.20 JOHOR
3 RAJU 3000.30 JOHOR
4 ALI PERAK
SELECT * FROM STAFF;
4 rows selected
SELECTING SPECIFIC COLUMNS
Staff_ID Staff_Name Address
1 ALEX SELANGOR
2 AHMAD JOHOR
3 RAJU JOHOR
4 ALI PERAK
SELECT Staff_ID, Staff_Name, Address FROM STAFF;
4 rows selected
USING ALIASES
ID Name Address
1 ALEX SELANGOR
2 AHMAD JOHOR
3 RAJU JOHOR
4 ALI PERAK
SELECT Staff_ID as ID, Staff_Name as Name, Address FROM STAFF;
4 rows selected
USING ARITHMETIC OPERATORS (*, / , +, -)
Staff_ID Staff_Name Salary Salary + 200
1 ALEX 4000 4200
2 AHMAD 2000.20 2200.20
3 RAJU 3000.30 3200.30
4 ALI
SELECT Staff_ID, Staff_Name, Salary, Salary + 200 FROM STAFF;
4 rows selected
USING ARITHMETIC OPERATORS (*, / , +, -)
OPERATOR PRECEDENCE
SELECT Staff_ID, Staff_Name, Salary, Salary + 200 asSalary_Bonus, 0.1*Salary + 200 Comm FROM STAFF;
Staff_ID Staff_Name Salary Salary_Bonus Comm
1 ALEX 4000 4200 600
2 AHMAD 2000.20 2200.20 400.02
3 RAJU 3000.30 3200.30 500.03
4 ALI
SELECT Staff_ID, Staff_Name, Salary, Salary + 200 asSalary_Bonus, 0.1*(Salary + 200) Comm FROM STAFF;
• USING PARENTHESES
Staff_ID Staff_Name Salary Salary_Bonus Comm
1 ALEX 4000 4200 420
2 AHMAD 2000.20 2200.20 220.2
3 RAJU 3000.30 3200.30 320.3
4 ALI
4 rows selected
• USING PARENTHESES
Dept_ID
IT
IT
IE
IT
4 rows selected
• DUPLICATE ROWS
SELECT Dept_ID FROM STAFF;
Dept_ID
IT
IE
2 rows selected
• ELIMINATE DUPLICATE ROWS
SELECT DISTINCT Dept_ID FROM STAFF;
Syntax:
SELECT *|{DISTINCT column|expression [alias]…}
FROM table
[WHERE conditions(s)};
• LIMITING THE ROWS SELECTED
- USING WHERE clause
SELECT * FROM STAFF
WHERE Staff_ID = ‘2’;
Staff_ID Staff_Name Salary Address
2 AHMAD 2000.20 JOHOR
• USING THE WHERE clause
1 row selected
SELECT * FROM STAFF
WHERE Staff_Name = ‘AHMAD’;
Staff_ID Staff_Name Salary Address
2 AHMAD 2000.20 JOHOR
• USING THE WHERE clause – USING CHARACTERSTRINGS AND DATE
1 row selected
SELECT * FROM STAFF
WHERE Salary < 3000;
Staff_ID Staff_Name Salary Address
2 AHMAD 2000.20 JOHOR
• USING THE WHERE clause – USING COMPARISONCONDITIONS (= , > , >= , < , <= , <>)
1 row selected
SELECT * FROM STAFF
WHERE Salary BETWEEN 1000 AND 4000;
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
2 AHMAD 2000.20 JOHOR
3 RAJU 3000.30 JOHOR
• USING THE WHERE clause – USING BETWEEN CONDITION
3 rows selected
SELECT * FROM STAFF
WHERE Staff_ID IN (‘1’ , ‘4’);
• USING THE WHERE clause – USING IN CONDITION
2 rows selected
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
4 ALI PERAK
SELECT * FROM STAFF
WHERE Staff_Name LIKE ‘AL%’;
• USING THE WHERE clause – USING LIKE CONDITION
2 rows selected
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
4 ALI PERAK
SELECT * FROM STAFF
WHERE Staff_Name LIKE ‘%X’;
• USING THE WHERE clause – USING LIKE CONDITION
1 row selected
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
SELECT * FROM STAFF
WHERE Salary IS NULL;
• USING THE WHERE clause – USING NULLCONDITION
1 row selected
Staff_ID Staff_Name Salary Address
4 ALI PERAK
SELECT * FROM STAFF
WHERE Salary >= 1000
AND Staff_Name LIKE ‘A%’;
• USING THE WHERE clause – USING THE AND OPERATOR
2 rows selected
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
2 AHMAD 2000.20 JOHOR
SELECT * FROM STAFF
WHERE Salary >= 1000
OR Staff_Name LIKE ‘A’%;
• USING THE WHERE clause – USING THE OR OPERATOR
4 rows selected
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
2 AHMAD 2000.20 JOHOR
3 RAJU 3000.30 JOHOR
4 ALI PERAK
SELECT * FROM STAFF
WHERE Address NOT IN (‘JOHOR’);
Staff_ID Staff_Name Salary Address
1 ALEX 1000 SELANGOR
4 ALI PERAK
• USING THE WHERE clause – USING THE NOT OPERATOR
2 rows selected
RULES OF PRECEDENCE
ORDER EVALUATED OPERATOR
1 Arithmetic Operators
2 Concatenation Operator
3 Comparison Conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT logical condition
7 AND logical condition
8 OR logical condition
OVERRIDE RULES OF PRECEDENCE BY USING PARENTHESES
SELECT * FROM STAFF
WHERE Address = ‘PERAK’
OR Address = ‘JOHOR’
AND Salary >= 2000;
Staff_ID Staff_Name Salary Address
2 AHMAD 2000.20 JOHOR
3 RAJU 3000.30 JOHOR
4 ALI PERAK
• USING THE WHERE clause – RULES OF PRECEDENCE
3 rows selected
SELECT * FROM STAFF
WHERE (Address = ‘PERAK’
OR Address = ‘JOHOR’)
AND Salary >= 2000;
Staff_ID Staff_Name Salary Address
2 AHMAD 2000.20 JOHOR
3 RAJU 3000.30 JOHOR
• USING THE WHERE clause – RULES OF PRECEDENCE
2 rows selected
SELECT * FROM STAFF
ORDER BY Salary; DEFAULT ASC
Staff_ID Staff_Name Salary Address
2 AHMAD 2000.20 JOHOR
3 RAJU 3000.30 JOHOR
1 ALEX 4000 SELANGOR
4 ALI PERAK
• USING THE SORTING ORDER BY clause –ASCENDING (ASC), DESCENDING (DESC)
4 rows selected
SELECT * FROM STAFF
ORDER BY Salary DESC;
Staff_ID Staff_Name Salary Address
1 ALEX 4000 SELANGOR
3 RAJU 3000.30 JOHOR
2 AHMAD 2000.20 JOHOR
4 ALI PERAK
• USING THE SORTING ORDER BY clause –ASCENDING (ASC), DESCENDING (DESC)
4 rows selected
SELECT * FROM STAFF
ORDER BY Salary DESC;
Staff_ID Staff_Name Salary Address
3 RAJU 3000.30 JOHOR
2 AHMAD 2000.20 JOHOR
4 ALI PERAK
1 ALEX 4000 SELANGOR
• USING THE MULTIPLE SORTING ORDER BY clause– ASCENDING (ASC), DESCENDING (DESC)
4 rows selected
Types of Group Functions
AVG
COUNT
MAX
MIN
SUM
VARIANCE
Using AVG, MAX, MIN and SUM functions
AVG(sal) MAX(sal) MIN(sal) SUM(sal)
4150 8000 950 12450
SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)
FROM emp;
sal
950
8000
3500
Output
Using COUNT functions
COUNT(*) returns the number of rows in a table
COUNT(*)
2
SELECT COUNT(*)
FROM emp
WHERE deptno = 30;
Output
emp_c
ode
ename job deptno hiredate
001 JAMES PROGRAMMER 20 20-MAC-90
003 ADAMS CLERK 30 11-JUL-99
002 SMITH MANAGER 30 03-FEB-97
All columns in the SELECT list that are not in group functions must be
in the GROUP BY clause.
Execute GROUP BY statement
emp_code ename job deptno sal
001 JAMES PROGRAMMER 20 3500
003 ADAMS CLERK 30 950
002 SMITH MANAGER 30 8000
004 LINA CLERK 20 650
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;
deptno AVG(sal)
20 2075
30 4475
Output
Using GROUP BY clause on multiple columns.
Execute GROUP BY statement
emp_code ename job deptno sal
001 JAMES PROGRAMMER 20 3500
003 ADAMS CLERK 30 950
002 SMITH MANAGER 30 8000
004 LINA CLERK 20 650
SELECT deptno, job, sal
FROM emp
GROUP BY deptno, job;
deptno sal job
20 3500 PROGRAM
MER
20 650 CLERK
30 950 CLERK
30 8000 MANAGER
Output