Chapter 8
Multiple Choice: #1 to #5
1.
Which of the following SQL statements isn’t valid?
SELECT
address || city || state || zip "Address" FROM customers WHERE
lastname 1∕4 'SMITH';
2.
Which clause is used to restrict rows or perform selection?
SELECT
3. Which of the following SQL statements is
valid?
SELECT
order# FROM orders WHERE shipdate 1∕4 NULL;
4.
Which of the following returns a list of all customers’ names sorted in
descending order by city within state?
SELECT firstname,
lastname FROM customers SORT BY desc state, city;
5.
Which of the following doesn’t return a customer with the last name THOMPSON in
the query results?
SELECT
* FROM customers;
Review Questions: #1 to #5
1. Which clause of an SQL query is used to restrict
the number of rows returned?
Use WHERE clause to restrict the number of rows returned.
2. Which clause of an SQL query displays the results
in a specific sequence?
ORDER BY clause displays the results in specific
sequence.
3. Which operator can you use to find any books with a
retail price of at least $24.00?
>=
4.
Which operator should you use to find NULL values?
IS [NOT] NULL
5. The IN-comparison operator is similar to which
logical operator?
LIKE operator
Hands-on Exercises: #1 to #10
1. Which customers
live in New Jersey? List each customer’s last name, first name, and state.
SQL>
SELECT CUSTOMER#, FIRSTNAME, LASTNAME, STATE
FROM CUSTOMERS
WHERE
STATE = 'NJ'
/
CUSTOMER# FIRSTNAME LASTNAME
ST
----------
---------- ---------- --
1019 JENNIFER SMITH
NJ
1020 KENNETH FALAH
NJ
2. Which orders
shipped after April 1, 2009? List each order number and the date it shipped.
SELECT ORDER#, SHIPDATE from ORDERS
WHERE
SHIPDATE > '1-APR-09'
SQL>
/
ORDER# SHIPDATE
----------
---------
1000 02-APR-09
1004 05-APR-09
1005 02-APR-09
1006 02-APR-09
1007 04-APR-09
1008 03-APR-09
1009 05-APR-09
1010 04-APR-09
1011 05-APR-09
1013 04-APR-09
1014 05-APR-09
ORDER# SHIPDATE
----------
---------
1017 05-APR-09
3. Which books
aren’t in the Fitness category? List each book title and category.
SELECT TITLE, CATEGORY
FROM
BOOKS
WHERE
CATEGORY <> 'FITNESS'
SQL>
/
TITLE CATEGORY
------------------------------
------------
REVENGE
OF MICKEY FAMILY LIFE
BUILDING
A CAR WITH TOOTHPICKS CHILDREN
DATABASE
IMPLEMENTATION COMPUTER
COOKING
WITH MUSHROOMS COOKING
HOLY
GRAIL OF ORACLE COMPUTER
HANDCRANKED
COMPUTERS COMPUTER
E-BUSINESS
THE EASY WAY COMPUTER
PAINLESS
CHILD-REARING FAMILY LIFE
THE
WOK WAY TO COOK COOKING
BIG
BEAR AND LITTLE DOVE CHILDREN
HOW
TO GET FASTER PIZZA SELF HELP
TITLE CATEGORY
------------------------------
------------
HOW
TO MANAGE THE MANAGER BUSINESS
SHORTEST
POEMS LITERATURE
4. Which customers
live in Georgia or New Jersey? Put the results in ascending order by last name.
List each customer’s customer number, last name, and state. Write this query in
two different ways.
SELECT Customer#, LastName, State
FROM
CUSTOMERS
WHERE
STATE ='GA'or state ='NJ'
ORDER
BY Lastname
SQL>
SQL>
/
CUSTOMER# LASTNAME ST
----------
---------- --
1020 FALAH NJ
1010 LUCAS GA
1018 MONTIASA GA
1019 SMITH NJ
OR
SQL>
SELECT Customer#, Lastname, State
FROM
CUSTOMERS
WHERE
STATE = 'GA' or STATE = 'NJ'
ORDER
By Lastname ASC
/
CUSTOMER# LASTNAME ST
----------
---------- --
1020 FALAH NJ
1010 LUCAS GA
1018 MONTIASA GA
1019 SMITH NJ
5. Which orders
were placed on or before April 1, 2009? List each order number and order date.
Write this query in two different ways.
SELECT *
FROM
ORDERS
WHERE
orderdate <'01-APR-09'
SQL>
/
ORDER#
CUSTOMER# ORDERDATE SHIPDATE
SHIPSTREET SHIPCITY SH SHIPZ SHIPCOST
----------
---------- --------- --------- ------------------ --------------- -- -----
----------
1000
1005 31-MAR-09 02-APR-09 1201 ORANGE AVE SEATTLE WA 98114 2
1001
1010 31-MAR-09 01-APR-09 114 EAST SAVANNAH ATLANTA GA 30314 3
1002
1011 31-MAR-09 01-APR-09 58 TILA CIRCLE CHICAGO IL 60605 3
6. List all
authors whose last name contains the letter pattern “IN.” Put the results in
order of last name, then first name. List each author’s last name and first
name.
SELECT Lname, Fname
FROM
AUTHOR
WHERE
Lname LIKE '%IN%'
ORDER
BY Lname, Fname
SQL>
/
LNAME FNAME
----------
----------
AUSTIN JAMES
MARTINEZ SHEILA
ROBINSON ROBERT
WILKINSON ANTHONY
7. List all
customers who were referred to the bookstore by another customer. List each
customer’s last name and the number of the customer who made the referral.
SQL>
SELECT *
FROM
customers
WHERE
referred IS NOT NULL
/
CUSTOMER# LASTNAME FIRSTNAME
ADDRESS CITY ST ZIP REFERRED RE EMAIL
----------
---------- ---------- -------------------- ------------ -- ----- ---------- --
------------------------------
1007 GIANA TAMMY
9153 MAIN STREET AUSTIN
TX 78710 1003 SW
treetop@zep.net
1009 PEREZ JORGE
P.O. BOX 8564 BURBANK CA 91510 1003 W
jperez@canet.com
1013 NGUYEN NICHOLAS
357 WHITE EAGLE AVE. CLERMONT
FL 34711 1006 SE
nguy33@sat.net
1016 DAUM MICHELL 9851231 LONG ROAD BURBANK
CA 91508 1010 W
1019 SMITH JENNIFER
P.O. BOX 1151
MORRISTOWN NJ 07962 1003 NE
8. Display the
book title and category for all books in the Children and Cooking categories.
Create three different queries to accomplish this task:
a) a search pattern
operation
SELECT Title, Category
FROM
Books
WHERE
category like 'C%N%'
SQL>
/
TITLE CATEGORY
------------------------------
------------
BUILDING
A CAR WITH TOOTHPICKS CHILDREN
COOKING
WITH MUSHROOMS COOKING
THE
WOK WAY TO COOK COOKING
BIG
BEAR AND LITTLE DOVE CHILDREN
b) a logical
operator
SELECT Title, Category
FROM
Books
Where
Category = 'CHILDREN' or Category = 'COOKING'
SQL>
/
TITLE CATEGORY
------------------------------
------------
BUILDING
A CAR WITH TOOTHPICKS CHILDREN
COOKING
WITH MUSHROOMS COOKING
THE
WOK WAY TO COOK COOKING
BIG
BEAR AND LITTLE DOVE CHILDREN
c) another
operator not used in a or b.
SELECT Title, Category
From
Books
Where
Category IN ('COOKING', 'CHILDREN')
SQL>
/
TITLE CATEGORY
------------------------------
------------
BUILDING
A CAR WITH TOOTHPICKS CHILDREN
COOKING
WITH MUSHROOMS COOKING
THE
WOK WAY TO COOK COOKING
BIG
BEAR AND LITTLE DOVE CHILDREN
9. Use a search
pattern to find any book title with “A” for the second letter and “N” for the
fourth letter. List each book’s ISBN and title. Sort the list by title in descending
order.
SELECT
ISBN, Title
FROM
Books
WHERE
Title LIKE '_A_N%'
ORDER
BY Title DESC
/
ISBN TITLE
----------
------------------------------
2491748320
PAINLESS CHILD-REARING
10. List the title
and publish date of any computer book published in 2005. Perform the task of
searching for the publish date by using three different methods:
a) a range
operator
SELECT
Title, PubDate
2 FROM
Books
3
WHERE (PubDate BETWEEN '01-Jan-05' AND '31-DEC-05') AND (Category =
'COMPUTER')
4 /
TITLE PUBDATE
------------------------------
---------
HOLY
GRAIL OF ORACLE 31-DEC-05
HANDCRANKED
COMPUTERS 21-JAN-05
b) a logical
operator
SELECT TITLE, PubDate
FROM
Books
WHERE
PubDate >= '01-Jan-05' AND PubDate <='31-DEC-05' AND Category =
'COMPUTER'
SQL>
/
TITLE PUBDATE
------------------------------
---------
HOLY
GRAIL OF ORACLE 31-DEC-05
HANDCRANKED
COMPUTERS 21-JAN-05
c) a search
pattern operation.
SELECT Title, PubDate
FROM
Books
WHERE
PubDate LIKE '%_05' AND Category = 'COMPUTER'
SQL>
/
TITLE PUBDATE
------------------------------
---------
HOLY
GRAIL OF ORACLE 31-DEC-05
HANDCRANKED
COMPUTERS 21-JAN-05
0 comments:
Post a Comment