sql notes full.pdf

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