Download - SQL NOTES FULL.pdf
![Page 1: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/1.jpg)
![Page 2: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/2.jpg)
ERD is a diagram that depicts entity
relationship model entities, attributes
and relations. It also displays
connectivity and cardinality.
![Page 3: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/3.jpg)
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
![Page 4: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/4.jpg)
![Page 5: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/5.jpg)
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
![Page 6: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/6.jpg)
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
![Page 7: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/7.jpg)
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.
![Page 8: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/8.jpg)
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;
![Page 9: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/9.jpg)
COLLECTION OF DATA
MANAGED USING DATABASE MANAGEMENT SYSTEM (DBMS) eg:
› SQL SERVER
› ORACLE
![Page 10: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/10.jpg)
STRUCTURED QUERY LANGUAGE
LANGUAGE USED TO COMMUNICATE WITH A RELATIONAL DBMS
![Page 11: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/11.jpg)
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
SELECTINSERTUPDATEDELETE
CREATEALTERDROP
COMMITROLLBACK
DCL
GRANTREVOKE
![Page 12: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/12.jpg)
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
CREATEALTERDROP
DCL
![Page 13: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/13.jpg)
Syntax:
CREATE DATABASE <Database_name>
PSPCREATE DATABASE PSP;
Database created.
USE PSP;
Database PSP is open and ready to use.
![Page 14: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/14.jpg)
Syntax:
CREATE TABLE <Table_name>
(Column datatype);
![Page 15: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/15.jpg)
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.
![Page 16: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/16.jpg)
Staff_ID Staff_Name Salary Address
Table Staff
![Page 17: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/17.jpg)
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.
![Page 18: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/18.jpg)
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.
![Page 19: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/19.jpg)
Syntax:
ALTER TABLE table
DROP (Column);
Staff_ID Staff_Name Salary Address
ALTER TABLE STAFFDROP Test;
Table altered.
![Page 20: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/20.jpg)
Syntax:DROP TABLE table;
DROP TABLE STAFF;
TABLE DROPPED
![Page 21: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/21.jpg)
![Page 22: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/22.jpg)
![Page 23: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/23.jpg)
STRUCTURED QUERY LANGUAGE
LANGUAGE USED TO COMMUNICATE WITH A RELATIONAL DBMS
![Page 24: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/24.jpg)
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
INSERTUPDATEDELETE
DCL
![Page 25: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/25.jpg)
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
INSERTUPDATEDELETE
DCL
![Page 26: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/26.jpg)
Syntax:
INSERT INTO table [(Column [, Column…])]
VALUES (Value [, Value…]);
**Note: This will insert only one row.
![Page 27: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/27.jpg)
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
![Page 28: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/28.jpg)
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
![Page 29: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/29.jpg)
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
![Page 30: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/30.jpg)
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
![Page 31: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/31.jpg)
Syntax:
UPDATE table
SET Column = Value [, Column = Value,…]
[WHERE condition]
**Note: Update more than one row at a time, if required.
![Page 32: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/32.jpg)
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
![Page 33: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/33.jpg)
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
![Page 34: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/34.jpg)
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
![Page 35: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/35.jpg)
Syntax:
DELETE [FROM] table
[WHERE condition];
**If no rows are deleted, a message “0 rows deleted” is returned.
![Page 36: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/36.jpg)
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
![Page 37: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/37.jpg)
DELETE FROM STAFF;
3 rows deleted
Staff_ID Staff_Name Salary Address
![Page 38: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/38.jpg)
![Page 39: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/39.jpg)
Objective
Use data manipulation statements of
SQL on a given database: SELECT
commands
![Page 40: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/40.jpg)
SQL
DATA RETRIEVAL
DML DDLTransaction
Control
SELECT
DCL
![Page 41: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/41.jpg)
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
![Page 42: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/42.jpg)
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
![Page 43: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/43.jpg)
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
![Page 44: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/44.jpg)
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
![Page 45: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/45.jpg)
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
![Page 46: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/46.jpg)
SELECT Staff_ID, Staff_Name, Salary, Salary + 200 asSalary_Bonus, 0.1*(Salary + 200) Comm FROM STAFF;
• USING PARENTHESES
![Page 47: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/47.jpg)
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
![Page 48: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/48.jpg)
Dept_ID
IT
IT
IE
IT
4 rows selected
• DUPLICATE ROWS
SELECT Dept_ID FROM STAFF;
![Page 49: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/49.jpg)
Dept_ID
IT
IE
2 rows selected
• ELIMINATE DUPLICATE ROWS
SELECT DISTINCT Dept_ID FROM STAFF;
![Page 50: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/50.jpg)
Syntax:
SELECT *|{DISTINCT column|expression [alias]…}
FROM table
[WHERE conditions(s)};
• LIMITING THE ROWS SELECTED
- USING WHERE clause
![Page 51: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/51.jpg)
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
![Page 52: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/52.jpg)
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
![Page 53: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/53.jpg)
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
![Page 54: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/54.jpg)
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
![Page 55: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/55.jpg)
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
![Page 56: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/56.jpg)
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
![Page 57: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/57.jpg)
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
![Page 58: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/58.jpg)
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
![Page 59: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/59.jpg)
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
![Page 60: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/60.jpg)
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
![Page 61: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/61.jpg)
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
![Page 62: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/62.jpg)
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
![Page 63: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/63.jpg)
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
![Page 64: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/64.jpg)
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
![Page 65: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/65.jpg)
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
![Page 66: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/66.jpg)
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
![Page 67: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/67.jpg)
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
![Page 68: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/68.jpg)
Types of Group Functions
AVG
COUNT
MAX
MIN
SUM
VARIANCE
![Page 69: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/69.jpg)
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
![Page 70: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/70.jpg)
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
![Page 71: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/71.jpg)
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
![Page 72: SQL NOTES FULL.pdf](https://reader034.vdocuments.us/reader034/viewer/2022050723/563dbb83550346aa9aadd046/html5/thumbnails/72.jpg)
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