...

Databases & SQL Data Manipulation & Analysis Workshop

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Databases & SQL Data Manipulation & Analysis Workshop
Databases & SQL
Data Manipulation & Analysis
Workshop
Databases



Database (DB): organized collection of data
Database Management System (DBMS):
software that controls organization, storage,
retrieval, and security of data in a database
Database Server :
computer dedicated to
database storage and
retrieval; it holds the
DBMS & databases
2
Databases

Different types of DBMS software:


Proprietary: Oracle, IBM DB2, MS SQL Server, …
Open Source: PosgreSQL, MySQL, SQLite, …


Note: Often called relational DBMS (RDBMS)
Fortunately, don’t have to learn each one
seprately, b/c they all use the same way
(SQL) to work with the stored data
DBMS
SQL
User
3
Structured Query Language

SQL (a.k.a. “sequel”): computer language
designed for managing data in DBMS


All major DBMSs (MS, Oracle, DB2,…) support
(similar versions of) SQL
E.g. Bank database: customer transactions
We can write an SQL program to…


Find all customers who didn’t pay monthly bill
Update a customer’s address, etc…
4
Data Organization

Relational DB’s store data in multiple tables.
For every table:
1.
2.
Each record/observation forms a row
Each field/variable forms a column
field
record

First_Name
Last_Name
Address
City
John
Smith
54 Felix Ave
Toronto
Philip
Doe
82 Orinoco St
Kitchener
Mary
Blyth
123 Axiom St
Mississauga
There are other ways to organize data,
E.g. Trees
(XML/JSON data)

A.k.a.
tidy data
Tidy Data

Is following data tidy?
Variables
Obs
.
(blood
pr.)
Drug
X
Drug
Y
Grou
p
Drug Blood
Pr.
1
A
X
130
2
B
X
135
Group A
130
-
3
C
X
140
Group B
135
150
4
A
Y
-
Group C
140
135
5
B
Y
150
6
C
Y
135
6
Example

Want to organize my mp3 collection; data on


Title, Artist, Length, Album, and Genre
Spreadsheet way of organizing data
Title
Artist
Length
Album
Genre
“Rolling in the deep”
Adele
3:48
“21”
Soul
“Someone like you”
Adele
5:45
“21”
Soul
“Bad Romance”
Lady Gaga
4:19
“The Pop Heroes”
Pop
“Meet Me Halfway”
Black Eyed Peas
4:03
“The Pop Heroes”
Pop
“Single Ladies”
Beyonce
3:13
“The Pop Heroes”
Pop

Problem: Can have many redundancies when
using single table for all data
7
Relational DB’s (RDB’s)

Relational model for mp3 DB
Tracks Table
Track ID
Title
Artist
Length
Album ID
1
“Rolling in
the deep”
Adele
3:48
1
2
“Someone
like you”
Adele
5:45
1
3
“Bad
Romance”
Lady Gaga
4:19
2
4
“Meet Me
Halfway”
Black
Eyed Peas
4:03
2
5
“Single
Ladies”
Beyonce
3:13
2

Albums Table
Album ID
Album
Genre
1
“21”
Soul
2
“The Pop
Heroes”
Pop
Data stored in multiple “connected” tables
8
Another Example

Library’s database:
Publisher
Table
Author
Table
Book
Table

RDB’s are predominant way of managing
financial records, medical records, personal
information, manufacturing & logistical data, etc
9
Primary & Foreign Keys

Primary Key (PK) is a unique identifier of
each record in a table


E.g. Student ID # in ROSI
Foreign Key (FK) is used to connect tables


Simply put, an FK field is another table’s PK,
which implies a one-to-many relationship (1↔∞)
E.g. For each order there must be one customer,
but each customer there can be many orders
10
One-to-One Relationships

Sometimes, tables have
one-to-one relationship
(i.e. one child per parent)


employee_tbl
employee_pay_tbl
emp_id
last_name
first_name
address
phone
emp_id
position
salary
bonus
E.g. Employee pay in separate table (for privacy)
How would you create a one-to-one
relationship with PK & FK’s?

Make FK in “child” table be its own PK 
 each record in either table is unique
11
Many-to-Many Relationships

Company DB with customers & orders

One customer can buy many products & one
product can have many buyers (customers)
Many-to-many
relationship:

customer_tbl
products_tbl
cust_id
cust_name
cust_phone
prod_id
prod_name
cost
FK’s only allow (1↔∞); how can we implement
many-to-many (∞↔∞) relationship in SQL?
 Use junction tables
12
Junction Tables

A junction table is a table that contains FK’s
from two or more other tables


Implicitly imposes a many-to-many relationship
E.g. orders table is junction of customers &
products tables
junction table
customer_tbl
orders_tbl
products_tbl
cust_id
cust_name
cust_phone
order_id
cust_id
prod_id
prod_id
prod_name
cost
Example

Sample university DB design:
denotes one
denotes many
14
Querying a Table

Query: an inquiry into the DB using SELECT


Most important element in SQL! Used to extract
data from DB, and more…
E.g. SELECT statement
album_name
SELECT album_name FROM albums_tbl;
column

21
Pop Heroes
table
OR
SELECT
*
FROM albums_tbl;
asterisk ( ) stands for all columns
*
album_id
album_name
1
21
2
Pop Heroes
Distinct Results
● ALL returns all
rows (default)
cust_id
first_name
last_name
1
John
Black
2
Mary
Adams
3
John
Jones
first_name
SELECT ALL first_name
FROM customer_tbl;
John
Mary
John
● DISTINCT returns distinct
rows from results table
SELECT DISTINCT first_name
FROM customer_tbl;
first_name
John
Mary
first_name
SELECT DISTINCT first_name, last_name
FROM customer_tbl;
last_name
John
Black
Mary
Adams
John
Jones
16
Order Results

cust_id
first_name
last_name
1
John
Black
2
Mary
Adams
3
John
Jones
ORDER BY: order query results in ASCending
/ DESCending order, based on some field

E.g.
SELECT first_name, cust_id
FROM customer_tbl
ORDER BY first_name;
first_name
cust_id
John
1
John
3
Mary
2
first_name
cust_id
John
3
John
1
Mary
2
Equivalent to (default ascending order):
ORDER BY first_name ASC
SELECT first_name, cust_id
FROM customer_tbl
ORDER BY last_name DESC;
17
Order Results

cust_id
first_name
last_name
1
John
Black
2
Mary
Adams
3
John
Jones
Can also order query results based on
multiple columns:
SELECT first_name, cust_id
FROM customer_tbl
ORDER BY first_name ASC, last_name DESC;
first_name
cust_id
John
3
John
1
Mary
2
Sort by 1st column, & if there is a tie, resolve by 2nd column

ORDER BY also used for Numeric, Date types
18
Example

ord_num
cust_id
prod_id
qty
ord_date
16C17
090
222
2
1999-10-17
18D778
287
90
10
1999-10-17
23E934
432
13
20
1999-10-15
32A132
43
222
25
1999-10-10
56A901
232
11235
1
1999-10-22
56A917
12
907
100
1999-09-30
Give results of query:
SELECT DISTINCT prod_id
FROM orders_tbl;
19
General SELECT Syntax
(MS Access SQL)
20
Filtering

WHERE clause used to specify subset of
table’s rows which satisfy some conditions

Lots of flexibility, from using different:




Condition Types: Equality, Range, Membership
Data Types: Numeric, String, Date
Composite Conditions: AND, OR
Etc (Functions, Subqueries, …)
21
Equality Conditions

Equality condition
(SELECT * FROM tbl_name WHERE condition

)
Data Type
Example
Notes:
Number
salary = 65000
Numbers with or without quotes
Text
first_name = ‘Jonh’
Case insensitive
Date
d_o_b = #6/21/96#
Inequality


Inequality operator : <>
E.g. WHERE salary <> 65000
22
Range Conditions

Operatators: >, <, <=, >=

E.g. salary < 65000, or d_o_b >= ‘1995-1-1’

For strings, characters ordered as (in ASC):
0, …, 9 , a, b, …, z (case not relevant: ‘A’=‘a’)



E.g. ‘a’ < ‘aa’ < ‘aab’ < ‘ab’ < ‘ac’
E.g. ‘1’ < ’115’ < ’13’ (!! numbers stored as strings !!)
BETWEEN … AND … operator:

E.g. salary BETWEEN 20000 AND 50000
 Limits inclusive & lower limit first
23
Example

dog_id
name
age
1
MAX
7
2
JACK
NULL
3
SNOW
3
1
SELECT dog_id FROM dog_tbl WHERE name > ‘K’;
3
 SELECT dog_id FROM dog_tbl WHERE name <= ‘M’;

Includes only up to name ‘M’, (i.e. not
surnames starting with ‘M’)
 SELECT dog_id FROM dog_tbl WHERE age < 5;

2
3
NULL values excluded from comparisons (i.e.
NULL is not < 0, or < ‘a’, or any other comparison)
 SELECT * FROM dog_tbl WHERE age BETWEEN 10 AND 5;

Returns nothing because lower limit must go 1st
Ø
24
Combining Conditions with
Logical Operators

Logical Operators:


Both cond. true
OR
Either cond. true
NOT
Cond. NOT true
E.g. age BETWEEN 11 AND 16 , equivalent to:
.

AND
.
age >= 11 AND age <= 16
E.g. age > 11 , equivalent to: NOT age <= 11
Combine many conditions with parentheses ()

E.g. age > 11 AND ( first_name = ‘John’
OR first_name = ‘Paul’ ) → everyone with first
name John or Paul, and older than 11 years
25
Membership Conditions


Membership operator: IN ( val_1, val_2, … )
Checking equality to any value in a set


E.g. age IN ( 11,15,17 ), equivalent to:
age = 11 OR age = 15 OR age = 17
For non-membership, use NOT IN
E.g. age NOT IN ( 11,15,17 ) equivalent to
. (age <> 11) AND (age <> 15) AND
(age <> 15)

26
Matching Conditions

Used with strings for imposing more
complicated conditions


E.g. Find string that contains a certain character
of sequence of characters
LIKE operator: find partial string matches
strings with the help of wildcard characters
Wildcard
Matches
? (question mark)
exactly one character
 (asterisk)
any # of characters (even 0)
27
LIKE Operator

Examples of LIKE operator

last_name LIKE ‘d*’ matches all last names
that start with a ‘d’

last_name LIKE ‘?d*’ matches all last names
whose 2nd letter is a ‘d’

last_name LIKE ‘*d*’ matches all last names
that contain the letter ‘d’

last_name LIKE ‘??d’ matches all last names
that are 3-letter long & end in ‘d’
Note: Even more flexible (& more complicated) way to match strings
is by using Regular Expressions (REGEXP operator)
28
Practice Question

Write an SQL query that returns the first &
last name of all employees that live in
Indianapolis AND their last name contains “L”
SELECT first_name, last_name
FROM employee_tbl
WHERE city = ‘Indianapolis' AND
last_name LIKE '*L*';
29
Selecting Data from Multiple
Tables

Know how to extract data from single table


SELECT … FROM tbl_name WHERE …
But how to extract data from multiple tables?

E.g. Select all product names (prod_tbl) for which
there exists an order (ord_tbl)
prod_tbl
prod_id
prod_name
1
Mars
2
Kit-Kat
3
Twix
PK
ord_tbl
ord_id
prod_id
1
2
2
3
FK
30
INNER JOIN

Take two tables and return a bigger
combined table, which only contains rows
(records) for which the original tables’ values
satisfy join condition
SELECT *
FROM prod_tbl INNER JOIN ord_tbl
ON prod_tbl.prod_id = ord_tbl.prod_id;

join condition
Refer to table’s column using dot (.) symbol

E.g.
ord_tbl.prod_id
table name column name
(prod_id column from
ord_tbl table)
31
Example
prod_id
prod_name
ord_id
prod_id
1
Mars
1
2
2
Kit-Kat
2
3
3
Twix
3
NULL
SELECT *
FROM prod_tbl INNER JOIN ord_tbl
ON prod_tbl.prod_id = ord_tbl.prod_id;
prod_id
prod_name
ord_id
prod_id
2
Kit-Kat
1
2
3
Twix
2
3
32
Join Conditions

Inner joins mainly used on FK relations
FROM T1 INNER JOIN T2 ON T1.PK = T2.FK;

But can also operate on arbitrary columns

E.g. Employees & customers with same 1st name
cust_id
f_name
…
emp_id
f_name
…
cust_id
emp_id
1
Mark
…
1
John
…
2
1
2
John
…
2
Andy
…
2
3
3
Tina
…
3
John
SELECT cust_tbl.cust_id, emp_tbl.emp_id
FROM cust_tbl INNER JOIN emp_tbl
ON cust_tbl.f_name = emp_tbl.f_name;
33
Column Aliases

Query results, by default, inherit column
names from table fields


But default names are not always suitable
Column Aliases used to describe results
SELECT cust_tbl.name AS [customer name],
prod_tbl.name AS [product name]
FROM ... INNER JOIN .... ;
Customer name
Product name
Mark
Twix
Mark
Kit Kat
Tina
Kit Kat
Column alias used for
explaining results;
34
Table Aliases

Use table alias within query with AS command

E.g. prod_tbl refered to by alias T1 in query
SELECT *
FROM prod_tbl AS T1 INNER JOIN ord_tbl
WHERE T1.prod_id = ord_tbl.prod_id;

Use table
aliases for
brevity /
description
Can refer to tables using their alias
SELECT T1.prod_name
FROM prod_tbl AS T1 INNER JOIN ord_tbl
WHERE T1.prod_id = ord_tbl.prod_id;
35
Joining ≥3 Tables

Get list of customer names & all products
they ever ordered in the last year
(T1)
CUST_TBL
cust_id (PK)
cust_name
(T2)
(T3)
ORD_TBL
cust_id (FK)
prod_id (FK)
PROD_TBL
prod_id (PK)
prod_name
SELECT T1.cust_name, T3.prod_name
FROM (cust_tbl AS T1
INNER JOIN ord_tbl AS T2
ON T1.cust_id = T2.cust_id)
INNER JOIN prod_tbl AS T3
ON T2.prod_id = T3.prod_id;
1st inner join
(T1 ↔ T2)
2st inner join
(T2 ↔ T3)
36
Example
cust_id
cust_name
cust_id
prod_id
1
Mark
1
1
2
John
1
2
3
Tina
3
2
prod_id prod_name
1
Twix
2
Kit Kat
SELECT T1.cust_name, T3.prod_name
FROM (cust_tbl AS T1 INNER JOIN ord_tbl AS T2
ON T1.cust_id = T2.cust_id)
INNER JOIN prod_tbl AS T3
ON T2.prod_id = T3.prod_id;
cust_name
prod_name
Mark
Twix
Mark
Kit Kat
Tina
Kit Kat
37
OUTER JOIN


INNER JOIN only returns matching rows
What if you want all products & their orders


I.e. list including all products, irrespective of
whether they have an order or not
OUTER JOIN returns all rows of one table,
joined with matching rows of another table


LEFT OUTER JOIN: return all rows of left (1st)
table, with matching rows of right (2nd) table
RIGHT OUTER JOIN: return all rows of right (2nd)
table, with matching rows of left (1st) table
38
OUTER JOIN

Example
prod_id prod_name
cust_id
prod_id
1
Twix
1
1
2
Kit Kat
1
2
3
Mars
3
2
SELECT *
FROM prod_tbl LEFT OUTER JOIN ord_tbl
ON prod_tbl.prod_id = ord_tbl.prod_id;
prod_id
prod_name
cust_id
prod_id
1
Twix
1
1
2
Kit Kat
1
2
2
Kit Kat
3
2
3
Mars
non-matching rows are empty
matching rows
@ prod_id = 2
39
ON and WHERE Clause

For joined tables, ON and WHERE conditions
are interchangeable.

E.g. All of the bellow queries are equivalent
SELECT * FROM T1 INNER JOIN T2
ON T1.fk = T2.pk WHERE T1.pk > 2;
SELECT * FROM T1 INNER JOIN T2
ON T1.fk = T2.pk > 2 AND T1.pk ;
Preferred version
(easier to interpret):
● ON for matches
● WHERE for rest
SELECT * FROM T1 INNER JOIN T2
ON T1.pk > 2 WHERE T1.fk = T2.pk;
40
Practice Question

Write an SQL query that returns a table of all
the customer names combined with all the
descriptions of the products they ordered
SELECT T1.cust_name, T3.prod_desc
FROM (customer_tbl AS T1
INNER JOIN orders_tbl AS T2
ON T1.cust_id = T2.cust_id)
INNER JOIN products_tbl AS T3
ON T2.prod_id = T3.prod_id;
41
Grouping & Aggregating Data


So far, query results come as list of records
For reporting/analyzing data, we often need
to aggregate and/or group them


E.g. Find total sales for last month, or total sales
grouped by products
SQL offers sets of commands for


Aggregating: COUNT, SUM, AVG, MIN, MAX
Grouping: GROUP BY, HAVING
42
COUNT

Counts # of records,
with 2 versions

prod_id
cust_id
date_out
1
1
2011-09-10
2
1
2011-09-13
2
3
2011-10-02
3
4
COUNT(*): # of rows in results table
SELECT COUNT(*) FROM ord_tbl;

COUNT(*)
4
COUNT(col_name): # of non-NULL values in field
SELECT COUNT(prod_id) FROM ord_tbl;
COUNT(prod_id)
4
SELECT COUNT(date_out) AS ‘# ord out’
FROM ord_tbl;
# ord out
3
43
COUNT

Count several fields
prod_id
cust_id
date_out
1
1
2011-09-10
2
1
2011-09-13
2
3
2011-10-02
3
4
SELECT COUNT(*), COUNT(date_out) ‘# ord out’
FROM ord_tbl;


For aggr. fn's only
COUNT(*)
# ord out
4
3
SELECT prod_id, COUNT(date_out) X
Combine with WHERE clause
SELECT COUNT(*) ‘# ord pending’
FROM ord_tbl WHERE date_out IS NULL;
# ord pending
1
44
SUM & AVG

Sum & (arithmetic) Average
of set of (non NULL) values
prod_id
prod_name
price
1
Mars
0.50
2
Twix
0.60
3
Kit-Kat
 SELECT SUM(price), COUNT(price), AVG(price)
FROM prod_tbl;

SUM(price)
COUNT(price)
AVG(price)
1.10
2
0.55
SUM & AVG not for use with string / date types

Query still runs (data type conversion), but results
don’t always make sense
45
MIN & MAX

Minimum & Maximum of
set of (non NULL) values
prod_id
prod_name
price
1
Mars
0.50
2
Twix
0.60
3
Kit-Kat
 SELECT MIN(price), MAX(price), MIN(prod_name)
FROM prod_tbl;

MIN (price)
MAX(price)
MIN(prod_name)
0.50
0.60
Kit-Kat
MIN & MAX can be used with string / date types


For date types, order from earliest to latest
For string types, order alphabetically (‘a’<‘ab’<‘c’…)
46
GROUP BY

Aggregate results by group

prod_id
qty
1
1
1
1
2
3
3
NULL
E.g. Total # of orders by product
SELECT prod_id, COUNT(*) ‘# ord’
FROM ord_tbl
GROUP BY prod_id;

prod_id
# ord
1
2
2
1
3
GROUP BY clause groups data with respect to to
distinct values of grouping field

If no aggregate results requested  SELECT DISTINCT
SELECT prod_id,
FROM ord_tbl
GROUP BY prod_id;
prod_id
SELECT DISTINCT prod_id
FROM ord_tbl;
1
2
47
3
prod_id
cust_id
qty
1
1
1
1
1
1
2
1
3
2
2
5
prod_id
cust_id
COUNT(*)
SUM(qty)
1
1
2
2
2
1
1
3
2
2
1
5
GROUP BY

Group by ≥2 fields
SELECT prod_id, cust_id,
COUNT(*), SUM(qty)
FROM ord_tbl
GROUP BY prod_id, cust_id;
distinct pairs

If grouping field not SELECT’ed, it will not show
up in results (but they are still grouped by it)
SELECT prod_id, COUNT(*) ‘#’
FROM ord_tbl
GROUP BY prod_id, cust_id;
SELECT COUNT(*) ‘#’
FROM ord_tbl
GROUP BY prod_id;
prod_id
#
1
2
2
1
2
1
2
2
#
48
HAVING


Filtering aggregate results
cust_id
qty
1
1
1
1
1
1
2
1
3
2
2
5
3
2
7

WHERE clause: for conditions before aggregation

HAVING clause: for conditions after aggregation
Total quantities for customer 1, by product
SELECT prod_id, SUM(qty)
FROM ord_tbl
WHERE cust_id = 1
GROUP BY prod_id;

prod_id
prod_id
SUM(qty)
1
2
2
3
# of orders by product, for total quantities > 5
SELECT prod_id, COUNT(*)
FROM ord_tbl
GROUP BY prod_id
HAVING SUM(qty) > 5;
prod_id
COUNT(*)
2
2
3
1
49
Grouping & Aggregating
Joined Tables
prod_id
cust_id
qty
1
1
1
prod_id
prod_name
price
1
Mars
0.50
1
1
1
2
Twix
0.60
2
1
3
3
Kit-Kat
NULL
2
2
5
4
Snickers
0.55
3
2
7
SELECT prod_name, COUNT(*), SUM(qty)
FROM prod_tbl T1 INNER JOIN ord_tbl T2
ON T1.prod_id = T2.prod_id
GROUP BY prod_name
HAVING SUM(qty) > 5
ORDER BY SUM(qty);
prod_name
COUNT(*)
SUM(qty)
Kit-Kat
1
7
Twix
2
8
50
Practice Question

Write query to return the product descriptions
of all products which have orders with total
quantity > 20
SELECT T2.prod_desc, SUM(T1.qty) AS [Total Qty]
FROM orders_tbl AS T1
INNER JOIN products_tbl AS T2
ON T1.prod_id = T2.prod_id
GROUP BY T2.prod_desc
HAVING SUM(T1.qty)>20
ORDER BY SUM(T1.qty) DESC;
51
Manipulating Query Results

Often, need to manipulate raw query data

E.g. Find total cost for each order, from
order quantity & product (unit) cost
prod_id
cost
1
0.50
2
0.60
cust_id
prod_id
SELECT qty, cost
FROM ord_tbl INNER JOIN prod_tbl
ON ord_tbl.prod_id = prod_tbl.prod_id;
qty
qty
cost
0.50
1
1
1
1
2
1
5
5
0.50
1
2
3
3
0.60
BUT, we
actually want
qty × cost :
qty × cost
0.50
2.50
1.80
52
Manipulating Query Results

Operators/functions for manipulating query
results depend on data type

Different from aggregate functions (e.g. AVG),
since they operate along rows, not columns

E.g.
qty
cost
qty
cost
1
0.50
1
0.50
5
0.50
5
0.50
3
0.60
3
0.60
AVG(qty) = 3
qty × cost
= 0.5
53
Mathematical Operators

+, −, *, /
order cost
SELECT qty * cost AS ‘order cost’
FROM ord_tbl INNER JOIN prod_tbl
ON ord_tbl.prod_id = prod_tbl.prod_id;

0.50
2.50
1.80
Apply to numbers, not only table fields

E.g. apply $.10 discount to all orders
SELECT qty * cost – .10 ‘order cost’
FROM ord_tbl INNER JOIN prod_tbl
ON ord_tbl.prod_id = prod_tbl.prod_id;

SELECT ( (3 * 5) + 7) / 2;
order cost
0.40
2.40
1.70
11
54
Mathematical Functions

Single argument: SQRT(), EXP(), LOG(), COS(),
SIN(), ABS(), SIGN(), FLOOR(), CEIL()
SELECT LOG( EXP(2) );
2
SELECT ROUND( 2.2 );
2
SELECT CEIL( 2.2 );
3
SELECT SIGN( -3 );
−1

Double argument: POW(), MOD(), LOG(), ROUND()
SELECT POW( 2, 3 );
8
SELECT MOD( 5, 2 );
1
SELECT LOG( 8, 2 );
3
SELECT ROUND( 2.1345, 2 );
(2 3 )
(5 / 2 remainder)
(log 2 8)
2.13
Note: Functions return NULL in case of error, e.g. SQRT(-2)=NULL
55
Subqueries

Subquery : a query contained within another
SQL statement (called containing statement)




Subquery always enclosed within parentheses ()
Usually (but not always) executed prior to
containing statement
Subqueries can return a value, column, or even
table, just like regular queries
Containing SQL statement
E.g.
SELECT ...
FROM ...
WHERE C1 = ( SUM(qty*cost) FROM ord_tbl );
56
Subquery
Subqueries


Useful for feeding values into containing
statement that are not known beforehand, but
can be extracted from DB with a query
prod_id price
Example: Find cheapest product
1
6.50

Cannot use WHERE:
don’t know MIN(price)
beforehand

SELECT *
FROM prod_tbl
WHERE price =
?;
2
2.45
3
9.12
⁞
⁞
Using subquery for finding minimum price
SELECT * FROM prod_tbl
WHERE price = ( SELECT MIN(price) FROM prod_tbl ) ;
57
Subquery Returning Multiple
Values

Find customers with order for the
same product(s) as customer 1
SELECT DISTINCT cust_id FROM ord_tbl
WHERE cust_id <> 1 AND prod_id IN
(SELECT prod_id FROM ord_tbl
WHERE cust_id = 1);
cust_id
prod_id
1
2
1
3
2
1
2
3
3
2
3
3
4
1
2



Subquery returns two values: 3
To compare prod_id with multiple values, use
membership conditions: IN / NOT IN
cust_id
Containing query returns:
2
3
58
Practice Question

Count how many distinct customers placed at
least one order
SELECT COUNT(*) AS [# distinct customer orders]
FROM (SELECT DISTINCT cust_id FROM orders_tbl);
59
Online SQL Resources

Intro to MS Access SQL Queries

More general tutorials (not limited to Access)



http://www.1keydata.com/sql/sql.html
http://www.w3schools.com/sql/default.asp
http://www.tizag.com/sqlTutorial/
60
Fly UP