Hassan Tariq INTRODUCTION TO SQL INTRODUCTION TO SQL

Hassan Tariq INTRODUCTION TO SQL

INTRODUCTION TO SQL What is SQL? – When a user wants to get some information from a database file, he can issue a query. – A query is a user–request to retrieve data or information with a certain condition. – SQL is a query language that allows user to specify the conditions. (instead of algorithms) HASSAN TARIQ – FAST NUCES

INTRODUCTION TO SQL Concept of SQL – The user specifies a certain condition. – The program will go through all the records in the database file and select those records that satisfy the condition. (searching). – Statistical information of the data. – The result of the query will then be stored in form of a table. HASSAN TARIQ – FAST NUCES

SQL - QUERY HIERARCHY Transact-SQL a sequence of SQL statements Data Manipulation Language Insert/delete/update Data Definition Language Create/alter/drop HASSAN TARIQ – FAST NUCES

SQL QUERY HIERARCHY SELEC T FROM WHERE HASSAN TARIQ – FAST NUCES GROUP BY HAVIN G ORDER

LOGICAL QUERY PROCESSING • 1 • 2 • 3 • 4 • 5 FROM • 6 ORDER BY HASSAN TARIQ – FAST NUCES WHERE GROUP BY HAVING SELECT

SQL QUERY Basic form: (plus many more bells and whistles) SELECT attributes FROM relations (possibly multiple, joined) WHERE conditions (selections) HASSAN TARIQ – FAST NUCES 7

SIMPLE SQL QUERY Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works SELECT * FROM Product WHERE category=‘Gadgets’ “selection” HASSAN TARIQ – FAST NUCES 8

A NOTATION FOR SQL QUERIES Input Schema Product(PName, Price, Category, Manfacture SELECT Name, Price, Manufacturer FROM Product WHERE Price > 100 Answer(PName, Price, Manfacturer) Output Schema HASSAN TARIQ – FAST NUCES 9

SELECTIONS What goes in the WHERE clause: � x = y, x <= y, etc For number, they have the usual meanings � For CHAR and VARCHAR: lexicographic ordering � � Expected � � conversion between CHAR and VARCHAR For dates and times, what you expect. . . Pattern matching on strings: s LIKE p (next) HASSAN TARIQ – FAST NUCES 10

THE LIKE OPERATOR � � s LIKE p: pattern matching on strings p may contain two special symbols: � � % = any sequence of characters _ = any single character Product(Name, Price, Category, Manufacturer) Find all products whose name mentions ‘gizmo’: SELECT * FROM Products WHERE PName LIKE ‘%gizmo%’ HASSAN TARIQ – FAST NUCES 11

COMPARISON Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi SELECT PName, Price, Manufacturer FROM Product WHERE Category IN (‘Photography’, ’Household’) Use of IN HASSAN TARIQ – FAST NUCES PName Price Manufacturer Single. Touch $149. 99 Canon Multi. Touch $203. 99 Hitachi 12

COMPARISON Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi SELECT PName, Price, Manufacturer FROM Product WHERE Price BETWEEN 19 and 30 Use of BETWEEN HASSAN TARIQ – FAST NUCES PName Price Manufacturer Gizmo $19. 99 Gizmo. Works Powergizmo $29. 99 Gizmo. Works 13

ELIMINATING DUPLICATES Category SELECT DISTINCT category FROM Product Gadgets Photography Household Compare to: Category SELECT category FROM Product Gadgets Photography Household HASSAN TARIQ – FAST NUCES 14

AGGREGATE FUNCTIONS COUNT() Function SUM() Function MAX() Function MIN() Function SELECT COUNT(column_name) FROM table_name SELECT SUM(column_name) FROM table_name SELECT MAX(column_name) FROM table_name SELECT MIN(column_name) FROM table_name HASSAN TARIQ – FAST NUCES

AGGREGATE FUNCTIONS AVG() Function IS NULL Clause TOP Clause SELECT AVG(column_name) FROM table_name select * from STUDENT where name is null SELECT TOP 2 * FROM Persons HASSAN TARIQ – FAST NUCES

ORDERING THE RESULTS SELECT pname, price, manufacturer FROM Product WHERE category=‘gizmo’ AND price > 50 ORDER BY price, pname Ordering is ascending, unless you specify the DESC keyword. Ties are broken by the second attribute on the ORDER BY list, etc. HASSAN TARIQ – FAST NUCES 17

ORDERING THE RESULTS SELECT Category FROM Product ORDER BY PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi HASSAN TARIQ – FAST NUCES ? 18

GROUPING SELECT. . . FROM. . . WHERE condition ; GROUP BY groupexpr [HAVING requirement] Group functions: COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) – groupexpr specifies the related rows to be grouped as one entry. Usually it is a column. – WHERE condition specifies the condition of individual rows before the rows are group. HAVING requirement specifies the condition involving the whole group. HASSAN TARIQ – FAST NUCES

GROUPING Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi SELECT Manufacturer, COUNT(*) as Total FROM Product GROUP BY Manufacturer Use of GROUP BY HASSAN TARIQ – FAST NUCES Manufacturer Total Gizmo. Works 2 Canon 1 Hitachi 1 20

GROUPING WITH CONDITION Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi SELECT Manufacturer, COUNT(*) as Total FROM Product GROUP BY Manufacturer HAVING COUNT(*)>2 Use of GROUP BY with HAVING HASSAN TARIQ – FAST NUCES Manufacturer Total Gizmo. Works 2 21

UNION, INTERSECTION AND DIFFERENCE OF TABLES The union of A and B (A B) A B A table containing all the rows from A and B. HASSAN TARIQ – FAST NUCES

UNION, INTERSECTION AND DIFFERENCE OF TABLES The intersection of A and B (A B) A B A table containing only rows that appear in both A and B. HASSAN TARIQ – FAST NUCES

UNION, INTERSECTION AND DIFFERENCE OF TABLES The difference of A and B (A– B) A B A table containing rows that appear in A but not in B. HASSAN TARIQ – FAST NUCES

UNION Product. Set 1 PName Category Gizmo Gadgets Micro. USB Gadgets Powergizmo Gadgets SELECT FROM UNION SELECT FROM PName Product. SET 1 PName Product. SET 2 UNION HASSAN TARIQ – FAST NUCES Product. Set 2 SELECT PName FROM Product. SET 1 UNION ALL SELECT PName FROM Product. SET 2 UNION ALL PName Gizmo Powergizmo Micro. USB Powergizmo 25

INTERSECT Product. Set 1 PName Category Gizmo Gadgets Micro. USB Gadgets Powergizmo Gadgets Product. Set 2 SELECT PName FROM Product. SET 1 INTERSECT SELECT PName FROM Product. SET 2 INTERSECT PName Powergizmo HASSAN TARIQ – FAST NUCES 26

SQL STATUS BASIC SQL ADVANCED SQL HASSAN TARIQ – FAST NUCES