An Introduction to SQL

For CS420

2

Overview of SQL

It is the standard language for relational systems, although imperfect

Supports data definition (DDL) and data manipulation (DML)

DDL: CREATE TABLE, ALTER TABLE DML: SELECT, INSERT, DELETE,

UPDATE

3

Example of DDL

CREATE TABLE [dbo].[S] ([SNO] [varchar] (50) NOT NULL ,[Sname] [varchar] (50) NOT NULL ,[Status] [int] NOT NULL ,[City] [varchar] (50) NOT NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[S] ADD CONSTRAINT [PK_S] PRIMARY KEY CLUSTERED (

[SNO]) ON [PRIMARY]

4

Examples of Restrict and Project

Restrict• -- List all the SNO with a Status greater than 10

Select SNO

From S

Where Status > 10

Project is also implemented by SELECTSelect SNO, SNAME

From S

5

Examples of Restrict and Project (2) Select without duplication

Select Distinct SNOFrom SP

Select Distinct SNOFrom SPOrder By SNO – Order By SNO DESC

Select with other conditionsSelect SNOFrom SWhere city = ‘London’ and Status between 1 and 30

6

Examples of Restrict and Project (4) Select with other conditions

• List the average QTY of all the shipments

Select Avg(QTY) –Min, Max, Count, Sum

From SP

What is the difference?Select Count (SNO)

From SP

Select Count (distinct SNO) – not supported by ACCESS

From SP

7

Group By Why isn’t this one correct

Select SNO, Avg(QTY)

From SP

GROUP BY• List the average QTY for each SNO

Select SNO, Avg(QTY)

From SP

Group By SNO

8

Group By (2) GROUP BY

• List the average QTY of shipment with QTY greater than 100 for these SNO whose average QTY of shipments great than 100 is great than 500

Select SNO, Avg(QTY)

From SP

Where QTY > 100

Group By SNO

Having Agv(QTY) > 500

9

Group By (3) GROUP BY

• List, for each part, the number of Suppliers the total amount of shipment (sum of QTY).

Select SNO, count(SNO) As [No of S], sum(QTY) as [Total QTY]

From SP

Group By SNO

10

Sub Query GROUP BY

• List the supplier names for suppliers who ship ‘P2’

Select SName

From S

Where SNO in

(select SNO from SP where PNO = ‘P2’)

11

Sub Query (2) GROUP BY

• List the supplier names for suppliers who ship all parts

Select SName

From S

Where SNO (select SNO

From SP

Group By SNO

Having count(*) = (select count(*) from P)

12

Update operators in SQL - Insert

Insert with valuesINSERT INTO TEMP (PNo, Color)

Values(‘P200’, ‘Green’)

Insert is implemented by INSERT INTOINSERT INTO TEMP (PNo, Color)

SELECT P#, Color FROM P

WHERE COLOR = ‘Red’;

13

Update operators in SQL - Delete

Delete is implemented by DELETEDELETE FROM SP

WHERE PNO = ‘P2’;• Deletes all shipments for part P2

14

Update operators in SQL - Update

Update is implemented by UPDATE UPDATE S

SET STATUS = 2 * STATUS, CITY = ‘Rome’

WHERE CITY = ‘Paris’;• Doubles the status of the Parisian suppliers and

moves them to Rome.