�ݺ�ߣ

Copyright (c) NR Computer Learning Center . www.nrclc.com
Dr. Vazi Okhandiar, DBA, MBA, MSCS, PMP, MCT
Introduction to SQL

5 Segments of SQL
•Data Definition Language (DDL)
•Data Manipulation Language (DML)
•Data Query Language (DQL)
•Data Control Language (DCL)
•Transaction Control Language(TCL)

SQL Language
• Structured Query language (SQL) (pronounced "sequel") is an "English-like
language" that allows users to easily and quickly access data from a
database. It is a powerful language to offer programmers all the capability
and flexibility they require to access & manipulate data in a database.
• SQL was developed and defined by IBM Research and refined by the
American National Standards Institute (ANSI) as the standard language for
relational database management systems.

Relational Database Management System
• A Relational database (RDBMS) consists of one or more tables with a
relationship between the tables.
• The Relational databases also include tools for
• writing queries
• creating forms for entering data
• developing reports for presenting data in a visually appealing format
• writing stored procedures for executing a program within the database
• developing customized functions
• developing triggers to be automatically invoked by an event.
• Users can create, read, update, and delete (CRUD) data using SQL
Language.

Introduction to SQL
SQL language statements can be divided into the following categories:
•Data Definition Language (DDL)
•Data Manipulation Language (DML)
•Data Query Language (DQL)
•Data Control Language (DCL)
•Transaction Control Language(TCL)

SQL
DDL
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT
DML
INSERT
UPDATE
DELETE
DQL SELECT
DCL
GRANT
REVOKE
TCL
COMMIT
ROLLBACK
SAVEPOINT

Free SQL Online IDE
• https://www.mycompiler.io/new/sql
• https://www.w3schools.com/sql/trysql.asp?filename=trys
ql_asc
• https://www.tutorialspoint.com/execute_sql_online.php
• http://sqlfiddle.com/

1. Data Definition Language (DDL)
• Data Definition Language (DDL) are SQL commands that are used to define
the database structure or schema.
• DDL is used to create, alter and delete database objects (including table,
view, index, stored procedure).
• CREATE - Create an object in the database
• ALTER – Alter the properties of an object in the database
• DROP – Delete an object from the database
• RENAME - Rename an object in the database
• TRUNCATE – Delete the content of the table and free up the space.
• COMMENT – Add comments to the data dictionary

DDL: Create Table Command
Syntax
CREATE TABLE table_name
(column_name data-type [NOT NULL] [, ...]
[, PRIMARY KEY (pcolumn_name [, ...])]
FOREIGN KEY (fcolumn_name) REFERENCES ref_table_name[,...]])
[CHECK (condition)];

Example (Parts & Supplier Tables)
PNO PNAME COLOR WEIGHT CITY
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
SNO SNAME STATUS CITY
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
Parts (P) Table
Supplier (S) Table
SNO PNO QTY
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P3 200
S4 P4 200
S4 P5 300
S4 P6 400
Supplier_Parts (SP) Table

Example (Cont)
CREATE TABLE Supplier
( SNO CHAR(5) NOT NULL Primary key,
SNAME CHAR(20),
STATUS INT,
CITY CHAR(15));
CREATE TABLE Parts
(PNO CHAR(6) NOT NULL Primary key,
PNAME CHAR(20),
COLOR CHAR(6),
WEIGHT INTEGER,
CITY CHAR(15));
CREATE TABLE Supplier_parts
( SNO CHAR(5) NOT NULL,
PNO CHAR(6) NOT NULL,
QTY INTEGER);

DDL: Alter Command
Syntax:
ALTER TABLE <Table Name>
ADD CONSTRAINT <Constraint Name>
FOREIGN KEY <Column Name>
REFERENCES <table Name>(<Column Name>)

Example
ALTER TABLE Supplier_parts
ADD CONSTRAINT PK_Supplier_parts
PRIMARY KEY (SNO, PNO);
ADD CONSTRAINT FK_Supplier
FOREIGN KEY (SNO) REFERENCES Supplier(SNO);
ADD CONSTRAINT FK_Parts
FOREIGN KEY (PNO) REFERENCES Parts(PNO);

2. Data Manipulation Language (DML)
Data Manipulation Language (DML) is the SQL Statement used for
adding, modifying, retrieving, and removing data from a database.
The DML includes the following commands:
•INSERT
•UPDATE
•DELETE

INSERT Command
Syntax
INSERT INTO <table_name>
(Col1, Col2, … Coln)
VALUES
(Data1, Data2, …, Datan)

Example
INSERT INTO Supplier
(SNO, SNAME, STATUS, CITY) VALUES
('S1', 'Smith', 20, 'London'),
('S2', 'Jones', 10, 'Paris'),
('S3', 'Blake', 30, 'Paris'),
('S4', 'Clark', 20, 'London'),
('S5', 'Adams', 30, 'Athens');
INSERT INTO Parts
(PNO, PNAME, COLOR, WEIGHT, CITY) VALUES
('P1', 'Nut', 'Red',12, 'London'),
('P2', 'Bolt', 'Green', 17, 'Paris'),
('P3', 'Screw', 'Blue', 17, 'Rome'),
('P4', 'Screw', 'Red', 14, 'London'),
('P5', 'Cam', 'Blue', 12, 'Paris'),
('P6', 'Cog', 'Red', 19, 'London');
INSERT INTO Supplier_parts
(SNO,PNO,QTY) VALUES
('S1', 'P1', 300),
('S1', 'P2', 200),
('S1', 'P3', 400),
('S1', 'P4', 200),
('S1', 'P5', 100),
('S1', 'P6', 100),
('S2', 'P1', 300),
('S2', 'P2', 400),
('S3', 'P3', 200),
('S4', 'P4', 200),
('S4', 'P5', 300),
('S4', 'P6', 400);

Example (Parts & Supplier Table)
PNO PNAME COLOR WEIGHT CITY
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
SNO SNAME STATUS CITY
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
Parts (P) Table
Supplier (S) Table
SNO PNO QTY
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P3 200
S4 P4 200
S4 P5 300
S4 P6 400
Supplier_Parts (SP) Table

3. Data Query Language (DQL)
DQL is the SQL Statement used for retrieving data from one or more
tables using the SELECT command.
Syntax:
SELECT [DISTINCT] {* | column_name | expression [, ...]}
FROM table_name [, ...]
[WHERE condition ]
[GROUP BY column_name [, ...] [HAVING condition]]
UNION [ALL] select _statement
[ORDER BY {column_name | column_number } [{ASC|DESC}] [, ...]];

SELECT
WHERE CLAUSE
GROUP BY CLAUSE
Having Clause
Order by Clause
Distinct & Top
Case-when-then…

Example (Cont)
5. Display the number of suppliers in the supplier table.
SELECT Count(*) as count
FROM Supplier;
6. Display the total quantity of ‘P2’ parts supplied by all the suppliers.
SELECT sum(QTY) as TOTAL
FROM Supplier_Parts
WHERE PNO=‘P2’;
7. List the part number supplied by 1 or more suppliers.
SELECT PNO
FROM Supplier_Parts
Group By PNO having count(*) > 1;
Output:
5
Output:
600
Output:
P1
P2
P3
P4
P5
P6

Example (Cont)
8. List the top 5 parts supplied by suppliers.
SELECT top 5 *
FROM Supplier_Part;
SQLite: SELECT *
FROM Supplier_Parts
ORDER BY QTY
DESC limit 5;
Output:
S1|P3|400
S2|P2|400
S4|P6|400
S1|P1|300
S2|P1|300

4. Data Control Language (DCL)
• GRANT Command is used for assigning privileges to a user on the
database objects.
Syntax:
GRANT privilege [, ...] ON [TABLE] table_name
TO {user_id [, ...] | PUBLIC };
Example:
GRANT insert ON TABLE Emp TO smith;
• REVOKE command is used to revoke privileges from a user in
performing an action on a database object.
Syntax:
REVOKE privilege [, ...] ON [TABLE] table_name
FROM { user_id [, ...] | PUBLIC};
Example:
Revoke insert on table Emp from smith;

5. Transaction Control Language(TCL)
TCL commands are used to manage transactions in the database.
These commands include:
• COMMIT – to permanently save transactions into the database.
• ROLLBACK – to restore the database to the last committed state
• SAVEPOINT – to temporarily save a transaction so that you can rollback to that
point whenever needed.

Dr. Vazi Okhandiar, DBA, MBA, MSCS, PMP, MCT
https://www.linkedin.com/in/vazi

�ݺ�ߣ

Introduction to SQL

More Related Content

Introduction to SQL

Editor's Notes