SQL> Oracle SQL, Chapter 9
SQL>
SQL> -- Oracle SQL, chapter 9
SQL> -- BMIS441 Data Base Management
SQL> -- Jason Chen
SQL> -- Row: 0
SQL> -- students are required to work questions with #2 and #5
SQL>
SQL>
SQL> -- 1. a)
SQL> SELECT title, contact, phone
2 FROM books, publisher
3 WHEREbooks.pubid = publisher.pubid;
TITLE CONTACT PHONE
------
REVENGE OF MICKEY TOMMIE SEYMOUR 000-714-8321
HOW TO MANAGE THE MANAGER TOMMIE SEYMOUR 000-714-8321
E-BUSINESS THE EASY WAY JANE TOMLIN 010-410-0010
BUILDING A CAR WITH TOOTHPICKS JANE TOMLIN 010-410-0010
HOLY GRAIL OF ORACLE DAVID DAVIDSON 800-555-1211
DATABASE IMPLEMENTATION DAVID DAVIDSON 800-555-1211
HANDCRANKED COMPUTERS DAVID DAVIDSON 800-555-1211
COOKING WITH MUSHROOMS RENEE SMITH 800-555-9743
THE WOK WAY TO COOK RENEE SMITH 800-555-9743
HOW TO GET FASTER PIZZA RENEE SMITH 800-555-9743
BODYBUILD IN 10 MINUTES A DAY RENEE SMITH 800-555-9743
TITLE CONTACT PHONE
------
PAINLESS CHILD-REARING SEBASTIAN JONES 800-555-8284
BIG BEAR AND LITTLE DOVE SEBASTIAN JONES 800-555-8284
SHORTEST POEMS SEBASTIAN JONES 800-555-8284
14 rows selected.
SQL>
SQL> --b)
SQL> SELECT title, contact, phone
2 FROM books JOIN publisher
3 USING(pubid);
TITLE CONTACT PHONE
------
REVENGE OF MICKEY TOMMIE SEYMOUR 000-714-8321
HOW TO MANAGE THE MANAGER TOMMIE SEYMOUR 000-714-8321
E-BUSINESS THE EASY WAY JANE TOMLIN 010-410-0010
BUILDING A CAR WITH TOOTHPICKS JANE TOMLIN 010-410-0010
HOLY GRAIL OF ORACLE DAVID DAVIDSON 800-555-1211
DATABASE IMPLEMENTATION DAVID DAVIDSON 800-555-1211
HANDCRANKED COMPUTERS DAVID DAVIDSON 800-555-1211
COOKING WITH MUSHROOMS RENEE SMITH 800-555-9743
THE WOK WAY TO COOK RENEE SMITH 800-555-9743
HOW TO GET FASTER PIZZA RENEE SMITH 800-555-9743
BODYBUILD IN 10 MINUTES A DAY RENEE SMITH 800-555-9743
TITLE CONTACT PHONE
------
PAINLESS CHILD-REARING SEBASTIAN JONES 800-555-8284
BIG BEAR AND LITTLE DOVE SEBASTIAN JONES 800-555-8284
SHORTEST POEMS SEBASTIAN JONES 800-555-8284
14 rows selected.
SQL>
SQL> --2. a)
SQL> SELECTfirstname, lastname, order#
2 FROM customers, orders
3 WHERE customers.customer# = orders.customer#
4 AND shipdate IS NULL
5 ORDER BY orderdate;
FIRSTNAME LASTNAME ORDER#
------
BECCA NELSON 1012
LEILA SMITH 1016
KENNETH FALAH 1015
GREG MONTIASA 1019
BONITA MORALES 1018
KENNETH JONES 1020
6 rows selected.
SQL>
SQL> --b)
SQL> SELECTfirstname, lastname, order#
2 FROM customers JOIN orders
3 USING(customer#)
4 WHERE shipdate IS NULL
5 ORDER BY orderdate;
FIRSTNAME LASTNAME ORDER#
------
BECCA NELSON 1012
LEILA SMITH 1016
KENNETH FALAH 1015
GREG MONTIASA 1019
BONITA MORALES 1018
KENNETH JONES 1020
6 rows selected.
SQL>
SQL>
SQL> --3.a)
SQL> SELECTc.customer#, firstname, lastname
2 FROM customers c, orders o, orderitems i, books b
3 WHERE c.customer# = o.customer#
4 AND o.order# = i.order#
5 AND i.isbn = b.isbn
6 AND category = 'FITNESS';
CUSTOMER# FIRSTNAME LASTNAME
------
1001 BONITA MORALES
SQL>
SQL>
SQL> --b)
SQL> SELECT customer#, firstname, lastname
2 FROM customers JOIN orders USING(customer#)
3 JOIN orderitemsUSING(order#)
4 JOIN books USING(isbn)
5 WHERE category = 'FITNESS';
CUSTOMER# FIRSTNAME LASTNAME
------
1001 BONITA MORALES
SQL>
SQL>
SQL> --4.a)
SQL> SELECT DISTINCT title
2 FROM customers c, orders o, orderitems i, books b
3 WHEREc.customer# = o.customer#
4 ANDo.order# = i.order#
5 ANDi.isbn = b.isbn
6 ANDfirstname = 'JAKE'
7 ANDlastname = 'LUCAS';
TITLE
------
PAINLESS CHILD-REARING
HOW TO MANAGE THE MANAGER
SQL>
SQL>
SQL> --b)
SQL> SELECT DISTINCT title
2 FROM customers JOIN orders USING(customer#)
3 JOIN orderitemsUSING(order#)
4 JOIN books USING(isbn)
5 WHEREfirstname = 'JAKE'
6 ANDlastname = 'LUCAS';
TITLE
------
PAINLESS CHILD-REARING
HOW TO MANAGE THE MANAGER
SQL>
SQL>
SQL> --5. a)
SQL> SELECT title, TO_CHAR((paideach-cost), '$999.99') AS Profit
2 FROM customers c, orders o, orderitems i, books b
3 WHEREc.customer# = o.customer#
4 ANDo.order# = i.order#
5 ANDi.isbn = b.isbn
6 ANDfirstname = 'JAKE'
7 ANDlastname = 'LUCAS'
8 ORDER BY orderdate, paideach-cost desc;
TITLE PROFIT
------
PAINLESS CHILD-REARING $37.45
HOW TO MANAGE THE MANAGER $16.55
PAINLESS CHILD-REARING $37.45
SQL>
SQL>
SQL> --b)
SQL> SELECT title, TO_CHAR((paideach-cost), '$999.99') AS Profit
2 FROM customers JOIN orders USING(customer#)
3 JOIN orderitemsUSING(order#)
4 JOIN books USING(isbn)
5 WHEREfirstname = 'JAKE'
6 ANDlastname = 'LUCAS'
7 ORDER BY orderdate, (paideach-cost) desc;
TITLE PROFIT
------
PAINLESS CHILD-REARING $37.45
HOW TO MANAGE THE MANAGER $16.55
PAINLESS CHILD-REARING $37.45
SQL>
SQL>
SQL> --6. a)
SQL> SELECT title
2 FROM books, bookauthor, author
3 WHEREbooks.isbn = bookauthor.isbn
4 ANDbookauthor.authorid = author.authorid
5 ANDlname = 'ADAMS';
TITLE
------
DATABASE IMPLEMENTATION
SQL>
SQL> --b)
SQL> SELECT title
2 FROM books JOIN bookauthor USING(isbn)
3 JOIN author USING(authorid)
4 WHERElname = 'ADAMS';
TITLE
------
DATABASE IMPLEMENTATION
SQL>
SQL>
SQL> --7.a)
SQL> SELECT gift
2 FROM books, promotion
3 WHERE retail BETWEEN minretail AND maxretail
4 AND title = 'SHORTEST POEMS';
GIFT
------
BOOK COVER
SQL>
SQL>
SQL> --b)
SQL> SELECT gift
2 FROM books JOIN promotion
3 ON retail BETWEEN minretail AND maxretail
4 WHERE title = 'SHORTEST POEMS';
GIFT
------
BOOK COVER
SQL>
SQL>
SQL> --8.a)
SQL> SELECT lname, fname, title
2 FROM books b, orders o, orderitems i, customers c, bookauthor t, author a
3 WHEREc.customer# = o.customer#
4 ANDo.order# = i.order#
5 ANDi.isbn = b.isbn
6 ANDb.isbn = t.isbn
7 ANDt.authorid = a.authorid
8 ANDfirstname = 'BECCA'
9 ANDlastname = 'NELSON';
LNAME FNAME TITLE
------
ROBINSON ROBERT BIG BEAR AND LITTLE DOVE
WHITE WILLIAM HANDCRANKED COMPUTERS
WHITE LISA HANDCRANKED COMPUTERS
BAKER JACK PAINLESS CHILD-REARING
FIELDS OSCAR PAINLESS CHILD-REARING
ROBINSON ROBERT PAINLESS CHILD-REARING
JONES JANICE REVENGE OF MICKEY
7 rows selected.
SQL>
SQL>
SQL> --b)
SQL> SELECT lname, fname, title
2 FROM customers c JOIN orders o ON c.customer# = o.customer#
3 JOINorderitems i ON o.order# = i.order#
4 JOIN books b ON i.isbn = b.isbn
5 JOINbookauthor t ON b.isbn = t.isbn
6 JOIN author a ON t.authorid = a.authorid
7 WHEREfirstname = 'BECCA'
8 ANDlastname = 'NELSON';
LNAME FNAME TITLE
------
ROBINSON ROBERT BIG BEAR AND LITTLE DOVE
WHITE WILLIAM HANDCRANKED COMPUTERS
WHITE LISA HANDCRANKED COMPUTERS
BAKER JACK PAINLESS CHILD-REARING
FIELDS OSCAR PAINLESS CHILD-REARING
ROBINSON ROBERT PAINLESS CHILD-REARING
JONES JANICE REVENGE OF MICKEY
7 rows selected.
SQL>
SQL>
SQL> --9. a)
SQL> SELECT title, o.order#, state
2 FROM books b, orders o, orderitems i, customers c
3 WHEREc.customer#(+) = o.customer#
4 ANDo.order#(+) = i.order#
5 ANDi.isbn(+) = b.isbn;
TITLE ORDER# ST
------
COOKING WITH MUSHROOMS 1018 FL
DATABASE IMPLEMENTATION 1018 FL
BODYBUILD IN 10 MINUTES A DAY 1003 FL
COOKING WITH MUSHROOMS 1003 FL
DATABASE IMPLEMENTATION 1003 FL
PAINLESS CHILD-REARING 1016 FL
E-BUSINESS THE EASY WAY 1006 FL
COOKING WITH MUSHROOMS 1008 ID
REVENGE OF MICKEY 1009 WA
COOKING WITH MUSHROOMS 1009 WA
COOKING WITH MUSHROOMS 1000 WA
TITLE ORDER# ST
------
REVENGE OF MICKEY 1014 TX
HOLY GRAIL OF ORACLE 1007 TX
BIG BEAR AND LITTLE DOVE 1007 TX
DATABASE IMPLEMENTATION 1007 TX
E-BUSINESS THE EASY WAY 1007 TX
COOKING WITH MUSHROOMS 1020 WY
PAINLESS CHILD-REARING 1011 GA
PAINLESS CHILD-REARING 1001 GA
HOW TO MANAGE THE MANAGER 1001 GA
DATABASE IMPLEMENTATION 1002 IL
DATABASE IMPLEMENTATION 1013 WY
TITLE ORDER# ST
------
BIG BEAR AND LITTLE DOVE 1017 FL
REVENGE OF MICKEY 1012 MI
HANDCRANKED COMPUTERS 1012 MI
PAINLESS CHILD-REARING 1012 MI
BIG BEAR AND LITTLE DOVE 1012 MI
REVENGE OF MICKEY 1019 GA
SHORTEST POEMS 1005 GA
DATABASE IMPLEMENTATION 1010 NJ
COOKING WITH MUSHROOMS 1015 NJ
PAINLESS CHILD-REARING 1004 NJ
HOW TO GET FASTER PIZZA
TITLE ORDER# ST
------
THE WOK WAY TO COOK
BUILDING A CAR WITH TOOTHPICKS
35 rows selected.
SQL>
SQL>
SQL> --b)
SQL> SELECT title, order#, state
2 FROM books b LEFT OUTER JOIN orderitems i USING(isbn)
3 LEFT OUTER JOIN orders o USING(order#)
4 LEFT OUTER JOIN customers c USING(customer#);
TITLE ORDER# ST
------
COOKING WITH MUSHROOMS 1018 FL
DATABASE IMPLEMENTATION 1018 FL
BODYBUILD IN 10 MINUTES A DAY 1003 FL
COOKING WITH MUSHROOMS 1003 FL
DATABASE IMPLEMENTATION 1003 FL
PAINLESS CHILD-REARING 1016 FL
E-BUSINESS THE EASY WAY 1006 FL
COOKING WITH MUSHROOMS 1008 ID
REVENGE OF MICKEY 1009 WA
COOKING WITH MUSHROOMS 1009 WA
COOKING WITH MUSHROOMS 1000 WA
TITLE ORDER# ST
------
REVENGE OF MICKEY 1014 TX
HOLY GRAIL OF ORACLE 1007 TX
BIG BEAR AND LITTLE DOVE 1007 TX
DATABASE IMPLEMENTATION 1007 TX
E-BUSINESS THE EASY WAY 1007 TX
COOKING WITH MUSHROOMS 1020 WY
PAINLESS CHILD-REARING 1011 GA
PAINLESS CHILD-REARING 1001 GA
HOW TO MANAGE THE MANAGER 1001 GA
DATABASE IMPLEMENTATION 1002 IL
DATABASE IMPLEMENTATION 1013 WY
TITLE ORDER# ST
------
BIG BEAR AND LITTLE DOVE 1017 FL
REVENGE OF MICKEY 1012 MI
HANDCRANKED COMPUTERS 1012 MI
PAINLESS CHILD-REARING 1012 MI
BIG BEAR AND LITTLE DOVE 1012 MI
REVENGE OF MICKEY 1019 GA
SHORTEST POEMS 1005 GA
DATABASE IMPLEMENTATION 1010 NJ
COOKING WITH MUSHROOMS 1015 NJ
PAINLESS CHILD-REARING 1004 NJ
HOW TO GET FASTER PIZZA
TITLE ORDER# ST
------
THE WOK WAY TO COOK
BUILDING A CAR WITH TOOTHPICKS
35 rows selected.
SQL>
SQL>
SQL> --10.a)
SQL> SELECT customers.customer#
2 FROM books, orders, orderitems, customers
3 WHEREcustomers.customer# = orders.customer#
4 ANDorders.order# = orderitems.order#
5 ANDorderitems.isbn = books.isbn
6 AND state = 'FL'
7 AND category = 'COMPUTER';
CUSTOMER#
------
1001
1001
1003
SQL>
SQL> --b)
SQL> SELECT customer#
2 FROM books JOIN orderitems USING (isbn)
3 JOIN orders USING (order#)
4 JOIN customers USING (customer#)
5 WHERE state = 'FL'
6 AND category = 'COMPUTER';
CUSTOMER#
------
1001
1001
1003
SQL>
SQL>
SQL> SPOOL OFF