SQL Tour

1
SQL Tour

• Creation of example database to SQL Server
  (Transact-SQL language)
• Example queries

2

• CREATE TABLE S (
• SNO VARCHAR(3),
• SNAME VARCHAR(20),
• STATUS DECIMAL(5),
• CITY VARCHAR(20),
• PRIMARY KEY (SNO)
• )

3

• CREATE TABLE P (
• PNO VARCHAR(3),
• PNAME VARCHAR(20),
• COLOR VARCHAR(20),
• WEIGHT DECIMAL(5),
• CITY VARCHAR(20),
• PRIMARY KEY (PNO)
• )

4

• CREATE TABLE SP (
• SNO VARCHAR(3),
• PNO VARCHAR(3),
• QTY DECIMAL(5),
• PRIMARY KEY (SNO,PNO),
• FOREIGN KEY (SNO)
• REFERENCES S,
• FOREIGN KEY (PNO)
• REFERENCES P
• )

5

• Dropping (destroying) a table (if something goes
  wrong just drop the table and create again)
• DROP TABLE SP
• Query the contents of a table
• SELECT
• FROM P

6

• INSERT
• INTO S
• ( SNO, SNAME, STATUS, CITY )
• VALUES
• ("S1", "Smith", 20, "London")
• SELECT
• FROM S

7

• INSERT
• INTO S
• ( SNO, SNAME, STATUS, CITY )
• VALUES
• ("S1", "Test", 20, "Lahti")
• What happens???!!!

8

• INSERT
• INTO S
• ( SNO, SNAME, STATUS, CITY )
• VALUES
• ("S2", "Jones", 10, "Paris")
• SELECT
• FROM S

9

• Updating an existing row
• UPDATE S
• SET CITY "Paris"
• WHERE SNO "S2"

10
Now you should have the database ready.
11

• DELETE
• FROM S
• WHERE SNO "S5"
• SELECT
• FROM S

12

• DELETE
• FROM S
• WHERE SNO "S1"
• SELECT
• FROM S
• Still there???!!!

13

• DELETE
• FROM SP
• WHERE SNO "S1"
• AND PNO "P1"

14

• SELECT CITY
• FROM S
• SELECT CITY
• FROM S
• WHERE SNO "S4"
• SELECT SNAME, CITY
• FROM S
• WHERE STATUS lt 30

15

• Write a query...
• ... which returns the home city of Jones.
• ... which returns the names of parts with weight
  over 12.
• ... which shows what colors are found in which
  cities (from table p).
• tip SELECT DISTINCT ...

16

• SELECT COLOR, CITY
• FROM P
• SELECT DISTINCT COLOR, CITY
• FROM P

17

• Write a query which shows for each supplied part
  (part found in table sp) the cities, where a
  supplier for that part can be found.
• SELECT DISTINCT SP.PNO, S.CITY
• FROM SP, S
• WHERE SP.SNO S.SNO

18

• SELECT FROM S
• ORDER BY STATUS
• SELECT FROM S
• ORDER BY STATUS DESC

19

• SELECT SUM(QTY) AS TOTAL
• FROM SP
• WHERE PNO "P2"
• SELECT PNO, SUM(QTY) AS TOTAL
• FROM SP
• GROUP BY PNO

20

• SELECT PNO, SUM(QTY) AS TOTAL
• FROM SP
• GROUP BY PNO
• ORDER BY TOTAL DESC

21

• Number of suppliers
• SELECT COUNT()
• FROM S
• Number of suppliers currenty supplying parts
• SELECT COUNT(DISTINCT SNO)
• FROM SP

22

• interesting experiments
• SELECT 'hello', sno, 3, qty100
• FROM SP
• SELECT 'S' cast(max(substring(sno,2,1)) 1 as
  varchar)
• FROM S
• SELECT sname,
• CASE WHEN status lt 20 THEN 'small'
• WHEN status between 20 and 29 THEN 'mid'
• ELSE 'big' END as status
• FROM S

non-standard concatenation operator in
Transact-SQL (standard is )