SQL

1
SQL
Spring 2008

• Structured Query Language
• (Simple Query Language)
• Lecture slides by Dr. Sara Cohen
• Update by Haggai Roitman Winter 2007/8

2
What is the Relational Model?

• Relation Table
• Note Overloading of the word "relation". Here we
  DO NOT mean relationships from an ER diagram!
• Relations have a schema which states
• the names of the columns of the relation
• Relations also have names
• An instance of a relation is a set of tuples
  (rows)
• Can be empty!
• No duplicates!

3
Query Components

• A query can contain the following clauses
• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• Only SELECT and FROM are mandatory
• Order of clauses is always as above

4
Basic SQL Query
SELECT Distinct target-list FROM
relation-list WHERE condition

• relation-list A list of relation names (possibly
  with a range-variable after each name)
• target-list A list of fields onto which the
  query projects
• condition A Boolean condition
• DISTINCT Optional keyword to delete duplicates

5
Basic SQL Query
SELECT Distinct target-list FROM
relation-list WHERE condition

• Evaluation
• Compute the cross product of the tables in
  relation-list.
• Delete all rows that do not satisfy condition.
• Delete all columns that do not appear in
  target-list.
• If Distinct is specified eliminate duplicate rows.

6
Cartesian Product

• Cartesian product is a binary operation it gets
  2 relations as input
• The result of a Cartesian product of two
  relations is a new relation that contains a tuple
  for each pair of tuples from the two input
  relation (concatenated).
• The number of tuples in the results is always the
  product of the number of tuples in each input
  relation
• Denoted with x

Concatenation ?????
7
Example Relations
S
R

T
S Students T Teachers R Studies
8
(No Transcript)
9
Basic SQL Query
SELECT Distinct A1,,An FROM R1,,Rm WHERE C
1.Make cross product among R1, R2,Rm 2.For
each row in the result check if condition C is
satisfied (is truth), if not remove the row
from the result 3.Cut all the columns from the
result except for A1, A2,An
10
Query Without WHERE
SELECT Distinct A1,,An FROM R1,,Rm
1.Make cross product among R1, R2,Rm 2.Cut
all the columns from the result except for A1,
A2,An
11
Query Without Projection
SELECT Distinct FROM R1,,Rm WHERE C
1.Make cross product among R1, R2,Rm 2.For
each row in the result check if condition C is
satisfied (is truth), if not remove the row
from the result
What if we remove Distinct?
12
Query Without Projection, Without WHERE
SELECT Distinct FROM R1,,Rm
13
Example Tables Used
14
What Are You Asking?
SELECT DISTINCT sname, age FROM Sailors WHERE
ratinggt7

• What does this compute?
• When would the result be different if we
  removed distinct?

15
And What Now?
SELECT DISTINCT sname FROM Sailors, Reserves
WHERE Sailors.sid Reserves.sid and bid
103
16
Range Variables
SELECT S.sname FROM Sailors S, Reserves R
WHERE S.sid R.sid and R.bid 103

• Range variables are good style.
• They are necessary if the same relation appears
  twice in the FROM clause

17
A Few SELECT Options

• Select all columns
• SELECT
• FROM Sailors S
• Rename selected columns
• SELECT S.sname AS Sailors_Name
• FROM Sailors S
• Applying functions (e.g., Mathematical
  manipulations)
• SELECT (age-5)2
• FROM Sailors S

18
The WHERE Clause

• Numerical and string comparison
• !,ltgt,, lt, gt, gt, lt, between(between val1 AND
  val2)
• String comparison is according to the
  alphabetical order!
• Logical components AND, OR, NOT
• Null verification IS NULL, IS NOT NULL
• Example
• SELECT sname
• FROM Sailors
• WHERE agegt40 AND rating IS NOT NULL

19
The LIKE Operator

• A pattern matching operator
• Basic format colname LIKE pattern
• Example
• _ is a single character
• is 0 or more characters

SELECT sid FROM Sailors WHERE sname
LIKE R_y
Do the following match R_y? Rolly Roy Ry
20
What is this?
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sid R.sid
21
Are any of these the same?
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sid R.sid
Q1
SELECT DISTINCT R.sid FROM Sailors S, Reserves
R WHERE S.sid R.sid
Q2
SELECT R.sid FROM Reserves R
Q3
22
Example

• Suppose we have the following table instances

23
Example (cont.)
Q1
Q2
Q3
Q1 Q3 ! Q2
24
Sailors whove reserved two different boats
SELECT R1.sid FROM Reserved R1, Reserved R2 WHERE
R1.sid R2.sid AND R1.bid ltgt
R2.bid
25
Names of sailors that reserved red boats
SELECT S.sname FROM Sailors S, Reserves R,
Boats B WHERE S.sid R.sid and R.bid
B.bid and B.color 'red'
26
Color of boats reserved by Bob
SELECT color FROM Sailors S, Reserved R, Boats
B WHERE S.sid R.sid AND R.bid b.bid
AND S.snameBob
27
Order Of the Result

• The ORDER BY clause can be used to sort results
  by one or more columns
• The default sorting of the columns in the ORDER
  BY clause is in ascending order
• Can specify ASC or DESC

28
Example
SELECT sname, rating, age FROM Sailors S
WHERE age gt 50 ORDER BY rating ASC, age DESC
Primary ascending sort by rating
Secondary descending sort by age
29
Names of sailors that reserved either red or
green boats
SELECT DISTINCT S.sname FROM Sailors S,
Reserves R, Boats B WHERE S.sid R.sid and
R.bid B.bid and (B.color 'red' or
B.color'green')
30
Other Relational Algebra Operators

• So far, we have seen selection, projection and
  Cartesian product
• How do we do operators UNION and MINUS?
• UNION (A, B) union of all the elements from the
  two sets.
• MINUS(A, B) all the elements of group A minus
  elements that also exist in the set B.

31
Sailors whove reserved red or green boat
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid B.bid
and B.color red UNION SELECT S.sname
FROM Sailors S, Boats B, Reserves R WHERE S.sid
R.sid and R.bid B.bid and B.color
green
32
Sailors whove reserved red and green boat
SELECT S.sname FROM Sailors S, Boats B1,
Reserves R1, Boats B2, Reserves R2 WHERE
S.sid R1.sid and R1.bid B1.bid and
B1.color red and S.sid R2.sid and R2.bid
B2.bid and B2.color green
33
Nested Queries
34
Nested Queries
Names of sailors whove reserved boat 103
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid FROM Reserves R WHERE
R.bid 103)
Nested query
The SELECT, FROM and WHERE clauses can have
sub-queries. Conceptually, they are computed
using nested loops. What would happen if we
wrote NOT IN?
35
Rewrite the Previous Query Using MINUS
SELECT S.sname FROM Sailors S MINUS SELECT
S.sname FROM Sailors S, Reserves R WHERE S.sid
R.sid AND R.bid IN
(SELECT B.bid FROM Boats
B WHERE B.color'red'))
36
Set-Comparison Queries
Sailors who are not the youngest
SELECT FROM Sailors S1 WHERE S1.age gt ANY
(SELECT S2.age FROM Sailors S2)
We can also use op ALL (op is gt, lt, , gt, lt, or
ltgt).
37
Commands used during the laboratory part of the
SQL lecture
38
MySQL Creating and using a database

• mysqlgtCREATE DATABASE Temp
• This command creates a new DB in MySQL
• mysqlgtUSE Temp
• To select DB named Temp for further working
  with it

39
MySQL Tables Creating

• mysqlgtSHOW TABLES
• Shows the list of all the existing tables in the
  current DB
• mysqlgtCREATE TABLE Sailors(
• gtsid INTEGER PRIMARY KEY,
• gtsname VARCHAR(10),
• gtrating INTEGER,
• gtbirthday DATE)

40
MySQL Tables Creating cont.

• mysqlgtCREATE TABLE Boats(
• gtbid INTEGER PRIMARY KEY,
• gtcolor VARCHAR(10))

41
MySQL Tables Creating cont.

• mysqlgtCREATE TABLE Reserves(
• gtsid INTEGER,
• gtbid INTEGER,
• gtday DATE,
• gtPRIMARY KEY(sid, bid, day),
• gtFOREIGN KEY(sid) REFERENCES Sailors(sid),
• gtFOREIGN KEY(bid) REFERENCES Boats(bid))

42
MySQL Looking into the Tables Schema

• mysqlgtDESCRIBE Sailors
• Describes the table Sailors (returns its schema)

43
MySQL drop vs. delete

• mysqlgtDROP TABLE Boats
• Removes the whole table Boats (with its schema)
• mysqlgtDELETE FROM Boats WHERE color green
• Will remove all the records that satisfy the
  condition colorgreen

44
MySQL Updating a Table Records

• We can update rows in a table
• General format
• UPDATE Table
• SET Field1value1,,,FieldNvalueN
• WHERE Cond
• Now we can increase the citys population
• UPDATE Sailors SET sname Bob Johnson
• WHERE sid 12

45
MySQL Inserting single records into the table

• There are two formats used to insert data into
  table
• mysqlgtINSERT INTO table_name SET
• -gt columnName1 value1,
• -gt columnName2 value2,
• -gt ...
• -gt
• mysqlgtINSERT INTO table_name
• -gt (columnName1, columnName2, ...)
• -gt VALUES (value1, value2, ...)
• 1st Option may not be working in this version

46
Loading data from a text file into the table

• Loading a large set of records at once
• per line (values separated by tabs)
• Use \N for a null value
• Inserting the File Data Set
• mysqlgt LOAD DATA LOCAL INFILE '/path/file.dat'
  INTO TABLE Table_Name

47
References

• Reference Manual http//dev.mysql.com/doc/refman/
  5.0/en/
• In the book of Ramakrishnan, chapter 5
• For further reading only chapters 3 (creating
  tables from ERD) and 4 (the mathematical
  background of SQL relational algebra)

48
Asignment 4 (optional!!!)

• Given the following tables, fill the SQL queries
  in the next slides

Suppliers (sid, sname, email) Parts (pid, pname,
color) Catalog(sid, pid, cost)
49
Lab Task I

• Create DB named TEMP1
• Select this DB
• Create the three Tables from the previous slide
  and see the schema of the tables
• Write text files named suppliers.dat,
  parts.dat, catalog.dat and save them in some
  directory, the files should include the
  appropriate test data for the tables
• Load the data to the tables from the files
• Try your homework SQL queries and see if you get
  the correct results, try to fix the queries
• Now lets see the correct queries, try to run the
  queries if they are different from yours

50
The commands

• mysqlgt CREATE DATABASE Temp
• Query OK, 1 row affected (0.08 sec)
• mysqlgt USE Temp
• Database changed
• mysqlgt SHOW Tables
• Empty set (0.07 sec)
• mysqlgt CREATE TABLE Suppliers(
• -gt sid INTEGER PRIMARY KEY,
• -gt sname VARCHAR(10),
• -gt email VARCHAR(12))
• Query OK, 0 rows affected (0.19 sec)
• mysqlgt CREATE TABLE Parts(
• -gt pid INTEGER PRIMARY KEY,
• -gt pname VARCHAR(10),
• -gt color VARCHAR(3) CHECK(color "RED" or
  color "GREEN" or color "BLUE")

51
Commands cont.

• mysqlgt CREATE Table Catalog(
• -gt sid INTEGER,
• -gt pid INTEGER,
• -gt cost INTEGER,
• -gt FOREIGN KEY (sid) REFERENCES Suppliers
  (sid) ON DELETE CASCADE,
• -gt FOREIGN KEY (pid) REFERENCES Parts (pid)
  ON DELETE CASCADE)
• Query OK, 0 rows affected (0.52 sec)

52
Creating the data files (in Wordpad)

• Catalog.dat
• 1 10 25
• 3 12 40
• 3 13 55
• 3 14 55
• 3 10 5
• 2 10 24
• 2 14 60
• 4 13 65
• 4 14 70

• Suppliers.dat
• 1 Moshe mmm_at_tx
• 2 Tehila teh_at_gmail
• 3 SONY sony_at_com
• Ericson eric_at_t2
• Parts.dat
• 10 Table RED
• 11 Book GREEN
• 12 IPhone GREEN
• 13 TV BLUE
• Laptop RED

53
Loading the Data to DB

• mysqlgt LOAD DATA LOCAL INFILE 'C/Documents and
  Settings/lera/Desktop/suppliers.dat' INTO TABLE
  Suppliers
• Then insert data into Parts and finally into
  Catalog
• Start trying your SQL queries!?

54
Query 1

• Find the names of suppliers who supply some red
  part.

SQL
55
Query 2

• Find the pids of parts supplied by at least two
  different suppliers

SQL
56
Query 3

• Find the pids of the most expensive parts
  supplied by suppliers named Sony

SQL
57
Query 1

• Find the names of suppliers who supply some red
  part.

SQL SELECT DISTINCT S.sname FROM Suppliers
S WHERE S.sid IN ( SELECT DISTINCT
CATALOG.sid FROM CATALOG C, PARTS P WHERE P.pid
C.pid and P.color RED)
58
Query 2

• Find the pids of parts supplied by at least two
  different suppliers

SQL SELECT DISTINCT C1.pid FROM CATALOG C1,
CATALOG C2 WHERE C1.pid C2.pid and C1.sid !
C2.sid
59
Query 3

• Find the pids of the most expensive parts
  supplied by suppliers named Sony

SQL SELECT C1.pid FROM CATALOG C1, SUPPIERS
S1 WHERE C1.sid S1.sid AND S1.snameSONY
AND C1.cost gt ALL(SELECT C2.cost
FROM SUPPLIERS S2, CATALOG C2 WHERE
S2.sname SONY AND S2.sid C2.sid)
60
Web Development(General Idea)

• Communication Protocol (HTTP)
• HTML Language

61
How Does It all Work?
What happens when a link is pressed?
62
HTML(HyperText Markup Language)

• Web pages are written in HTML, which defines the
  style in which the page should be displayed.

lta href"http//www.undergraduate.technion.ac.il/c
atalog/facs009.html"gt course syllabuslt/agt
Text written on the link
URL (the destination)
63
(No Transcript)
64
Resource (HTML Page)
DNS Server
URL
Web Server
IP Address
Browser
HTTP Request
HTTP Response
Resource
65
DNS
http//www.undergraduate. technion.ac.il/
catalog/facs009.html
Web Server
132.68.238.21
File System
66
HTTP
67
Common Protocols

• In order for two remote machines to understand
  each other they should
• speak the same language and coordinate their
  conversation
• The solution is to use protocols, e.g.,
• FTP File Transfer Protocol
• SMTP Simple-Mail Transfer Protocol
• NNTP Network-News Transfer Protocol
• HTTP HyperText Transfer Protocol

68
The HTTP Conversation"

• A Web Browser knows how to send an HTTP request
  for a resource
• A Web Server is a program that listens for HTTP
  requests and knows how to send appropriate HTTP
  responses
• There are 2 standard versions of HTTP HTTP 1.0
  and HTTP 1.1

69
Resources and URLs
70
Resources

• A resource is a chunk of information that can be
  identified by a URL (Universal Resource Locator)
• A resource can be
• A file, e.g., html, text, image
• A dynamically created page (more about this later
  on)
• What we see on the browser can be a combination
  of some resources
• When an html page is displayed with images we are
  actually seeing several resources at once

How Many?
How do we get them all?
71
Basic Syntax
protocol//domain/path
Basic Format of a URL
http//iew3.technion.ac.il/sarac/index.html
http//iew3.technion.ac.il/sarac
ftp//ctan.unsw.edu.au/tex-archive/misc.zip
72
HTML
73
What is HTML?

• It is a Markup Language
• It is used to write web pages specify the role
  of different parts of the page and the style that
  should be used when displaying the page
• HTML gives authors the means to
• Publish online documents with text, images, etc.
• Retrieve online information via hypertext links
• Design forms for conducting transactions with
  remote services, for searching for information,
  making reservations, ordering products, etc.

74
A simple HTML page
lthtmlgt ltheadgtlttitlegtMy First HTML
Pagelt/titlegtlt/headgt ltbodygtltfont
colorredgt Hello World Wide
Web!lt/fontgtlt/bodygt lt/htmlgt
75
A simple HTML page

• HTML contains text, separated by tags

• Generally, tags come in pairs, an opening tag and
  a closing tag

• Tags can have attributes, which have values

lthtmlgt ltheadgtlttitlegt My First HTML Page
lt/titlegtlt/headgt ltbodygtltfont colorredgt Hello
World Wide Web!lt/fontgtlt/bodygt lt/htmlgt
76
Some General Rules

• HTML page is surrounded by the html tag
• 2 Basic parts
• Head Consists of things that describe the
  document (e.g., title shown on the browser bar)
• Body Consists of the content of the document

lthtmlgt ltheadgtlttitlegt My First HTML Page
lt/titlegtlt/headgt ltbodygtltfont colorredgt Hello
World Wide Web!lt/fontgtlt/bodygt lt/htmlgt
77
More General Rules

• Tags are not case sensitive (ltheadgt, ltHEADgt,
  ltHeadgt are the same)
• Whitespace in an html document is ignored
• HTML files should end with .htm or .html
• In HTML, there is an exception to almost every
  rule!

78
The ltBODYgt

• Headings lth1gt, , lth6gt where h1 is the largest
  one
• Paragraphs ltpgt (optional closing tag)
• Line breaks ltbrgt (no closing tag)
• Horizontal lines lthrgt (no closing tag)
• Formatted text bold ltbgt, italics ltigt, underline
  ltugt
• Font colors and styles ltfont color red
  faceArialgt

79
Another Example

• lthtmlgtltheadgt lttitlegtExample 2lt/titlegtlt/headgtlt
  !-- Here is a comment --gt
• ltbodygt lth1gtThis is an example of an HTML
  pagelt/h1gt ltpgtHere is ltbgtemphasizedlt/bgt text and
  there is also ltigtitaliclt/igt text here.
• ltbrgt Here is a new line lt/pgt
• ltpgtIs this ltfont colorblue
  faceArialgteasylt/fontgt? ltpgtlthrgtAnd some parting
  words... Good Bye
• lt/bodygt
• lt/htmlgt

80
Another Example
81
Targil HTML

• Use the file HTML.zip that I sent to you by
  e-mail with this ppt
• Unzip the file in any folder in the TEMP
  directory
• Now lets complete the file, so it will look like
  the website from the next slide

82
Main Screen

• List
• Table
• Image

83
Links

• Basic Link
• lta href"http//iestud.technion.ac.il"gtVisit
  IEStud!lt/agt
• Marked Point (an anchor)
• lta namepoint hrefURL" gtVisit URL!lt/agt
• Going to a marked point
• lta hrefpoint"gtGoTo point!lt/agt

84
Frames

• With frames, you can display more than one HTML
  document in the same browser window. Each HTML
  document is called a frame, and each frame is
  independent of the others
• Example
• ltframeset cols"25,75"gt   
• ltframe src"frame_a.htm"gt   
• ltframe src"frame_b.htm"gt
• lt/framesetgt

85
List Screen
This is Frame_a (25)
This is Frame_b (75)
86
LIST Tags

• There are two basic kinds of list
• ltOLgt Ordered List lt/OLgt
• ltULgt Unordered List lt/ULgt
• To add a List Item use the tag ltLIgt
• Example
• ltLIgt First Item
• Bullet Type is defined as below
• ltUL typesquaregt

87
Table Screen
88
Tables
89
Tables (2)

• Example
• lttable border"1"gt
• lttrgt
• ltthgtHeadinglt/thgt
• ltthgtAnother Headinglt/thgt
• lt/trgt
• lttrgt
• lttdgtrow 1, cell 1lt/tdgt
• lttdgtrow 1, cell 2lt/tdgt
• lt/trgt
• lttrgt
• lttdgtrow 2, cell 1lt/tdgt
• lttdgtrow 2, cell 2lt/tdgt
• lt/trgt
• lt/tablegt

90
The Last Assignment

• Your Job is to build your homepage (you can use
  the HTML.zip file or make a completely new HTML
  file, without frames).

91
The Last Assignment

• Your website should include at least one list,
  at least few pictures ordered in a table, a list
  of favorite links and if you dont use frames,
  then anchors with jumps to the middle and
  beginning of the page
• Once you have a tx account, you could place your
  files in the public_html directory and have your
  website on tx