Introduction to SQL
K L J Lasantha
M.Sc. (Comp. Sc.), MCSSL
History of SQL
• 1970–E. F. Codd develops relational database concept
• 1974-1979–System R with Sequel (later SQL) created at IBM
Research Lab
• 1979–Oracle markets first relational DB with SQL
• 1981 – SQL/DS first available RDBMS system on DOS/VSE
• Others followed: INGRES (1981), IDM (1982), DG/SGL (1984),
Sybase (1986)
• 1986–ANSI SQL standard released
• 1989, 1992, 1999, 2003, 2006, 2008–Major ANSI standard
updates
• Current–SQL is supported by most major database vendor
SQL Environment
• Data Definition Language (DDL)
• Commands that define a database, including
creating, altering, and dropping tables and
establishing constraints
• Data Manipulation Language (DML)
• Commands that maintain and query a
database
• Data Control Language (DCL)
• Commands that control a database, including
administering privileges and committing data
DDL, DML,
DCL, and the
database
development
process
Attribute names
Tables in SQL Table name
Product
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Tuples or rows
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
Tables Explained
• The schema of a table is the table name and its
attributes:
Product(PName, Price, Category, Manufacturer)
• A key is an attribute whose values are unique;
we underline a key
Product(PName, Price, Category, Manufacturer)
Simple SQL Query
Product
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
SELECT *
MultiTouch $203.99 Household Hitachi
FROM Product
WHERE Category=‘Gadgets’
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
“selection”
Powergizmo $29.99 Gadgets GizmoWorks
Simple SQL Query
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
SELECT PName, Price, Manufacturer MultiTouch $203.99 Household Hitachi
FROM Product
WHERE Price > 100
PName Price Manufacturer
“selection” and
SingleTouch $149.99 Canon
“projection”
MultiTouch $203.99 Hitachi
SELECT *
The LIKE FROM Products
operator WHERE PName LIKE ‘%gizmo%’
• s LIKE p: pattern matching on strings
• p may contain two special symbols:
• % = any sequence of characters
(* is used in some DBMS)
• _ = any single character
(? is used in some DBMS)
Eliminating Duplicates
Category
SELECT DISTINCT Category Gadgets
Photography
FROM Product Household
Compare to:
Category
SELECT Category Gadgets
Gadgets
FROM Product Photography
Household
SELECT PName, Price, Manufacturer
Ordering the
Results FROM Product
WHERE Category=‘gizmo’ AND Price > 50
ORDER BY Price, PName
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
Ordering is ascending, unless you specify the DESC keyword.
Keys and Foreign Keys
Company CName StockPrice Country
Primary Key GizmoWorks 25 USA
Canon 65 Japan
Foreign
Hitachi 15 Japan key
PName Price Category Manufacturer
Product Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
Joins
Product (PName, Price, Category, Manufacturer)
Company (CName, StockPrice, Country)
Find all products under $200 manufactured in Japan;
return their names and prices.
Join
between Product
and Company
SELECT PName, Price
FROM Product, Company
WHERE Manufacturer=CName AND Country=‘Japan’
AND Price <= 200
Q&A
Thank you
