Unit 3 Assignment - SQL statements - Please be aware that I am using APEX.ORACLE.com and the SQL command statement must run successfully without errors.

1. Which orders shipped after April 1, 2009? List each order number and the date it shipped.

2. Which orders were placed on or before April 1, 2009? List each order number and order date. Write this query two different ways and show results for each.

3. 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) use a range operator, b) use a logical operator, and c) use a search pattern.

I have included the entire database in the form of pictures as its not letting me post the SQL data directly from the file.

AUTHOR tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Add Column Modify Column Rename Column Drop Column Rename Copy Drop Truncate Create Lookup Table Create App Column Name Data Type Nullable Default Primary Key AUTHORID VARCHAR2(4) No 1 LNAME VARCHAR2(10) Yes FNAME VARCHAR2(10) Yes Download | Print BOOKAUTHOR +v Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Add Column Modify Column Rename Column Drop Column Rename Copy Drop Truncate Create Lookup Table Create App Column Name Data Type Nullable Default Primary key ISBN VARCHAR2(10) No 1 AUTHORID VARCHAR2(4) No 2 BOOKS +v Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Add Column Modify Column Rename Column Drop Column Rename Copy Drop Truncate Create Lookup Table Create App Column Name Data Type Nullable Default Primary Key ISBN VARCHAR2(10) No 1 TITLE VARCHAR2(30) Yes PUBDATE DATE Yes PUBID NUMBER(2,0) Yes COST NUMBER(5,2) Yes RETAIL NUMBER(5,2) Yes DISCOUNT NUMBER(4,2) Yes CATEGORY VARCHAR2(12) Yes CUSTOMERS tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Add Column Modify Column Rename Column Drop Column Rename Copy Drop Truncate Create Lookup Table Create App Column Name Data Type Nullable Default Primary key CUSTOMER# NUMBER(4,0) No 1 LASTNAME VARCHAR2(10) No FIRSTNAME VARCHAR2(10) No ADDRESS VARCHAR2(20) Yes CITY VARCHAR2(12) Yes STATE VARCHAR2(2) Yes ZIP VARCHAR2(5) Yes REFERRED NUMBER(4,0) Yes REGION CHAR(2) Yes Download Print ORDERITEMS tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Add Column Modify Column Rename Column Drop Column Rename Copy Drop Truncate Create Lookup Table Create App Column Name Data Type Nullable Default Primary Key ORDER# NUMBER(4,0) No 1 ITEM# NUMBER(2,0) No 2 ISBN VARCHAR2(10) Yes QUANTITY NUMBER(3,0) No PAIDEACH NUMBER(5,2) No ORDERS tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Add Column Modify Column Rename Column Drop Column Rename Copy Drop Truncate Create Lookup Table Create App Column Name Data Type Nullable Default Primary Key ORDER# NUMBER(4,0) No 1 CUSTOMER# NUMBER(4,0) Yes ORDERDATE DATE No SHIPDATE DATE Yes - SHIPSTREET VARCHAR2(18) Yes - SHIPCITY VARCHAR2(15) Yes SHIPSTATE VARCHAR2(2) Yes SHIPZIP VARCHAR2(5) Yes SHIPCOST NUMBER(4,2) Yes PROMOTION tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Add Column Modify Column Rename Column Drop Column Rename Copy Drop Truncate Create Lookup Table Create App Column Name Data Type Nullable Default Primary Key GIFT VARCHAR2(15) Yes MINRETAIL NUMBER(5,2) Yes MAXRETAIL NUMBER(5,2) Yes 1. PUBLISHER tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Add Column Modify Column Rename Column Drop Column Rename Copy Drop Truncate Create Lookup Table Create App Column Name Data Type Nullable Default Primary Key PUBID NUMBER(2,0) No 1 NAME VARCHAR2(23) Yes CONTACT VARCHAR2(15) Yes PHONE VARCHAR2(12) Yes AUTHOR + Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT AUTHORID LNAME FNAME S100 SMITH SAM J100 JONES JANICE A100 AUSTIN JAMES M100 MARTINEZ SHEILA K100 KZOCHSKY TAMARA P100 PORTER LISA A105 ADAMS JUAN B100 BAKER JACK P105 PETERSON TINA W100 WHITE WILLIAM W105 WHITE LISA R100 ROBINSON ROBERT F100 FIELDS OSCAR W110 WILKINSON ANTHONY Download BOOKAUTHOR + Query Count Rows Insert Row Load Data EDIT ISBN AUTHORID 0132149871 S100 0299282519 S100 0401140733 J100 1059831198 P100 1059831198 S100 1915762492 W100 1915762492 W105 2147428890 W105 2491748320 B100 2491748320 F100 2491748320 R100 3437212490 B100 3957136468 A100 4981341710 K100 8117949391 R100 Download row(s) 1 - 15 of 20 Next BOOKAUTHOR tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SOL REST Sample Queries Query Count Rows Insert Row Load Data EDIT ISBN AUTHORID 8843172113 A100 8843172113 A105 8843172113 P105 9247381001 W100 9959789321 J100 Download BOOKS + Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT ISBN TITLE PUBDATE PUBID COST RETAIL DISCOUNT CATEGORY 1059831198 BODYBUILD IN 10 MINUTES A DAY Friday, 21 January, 2005 4 18.75 30.95 FITNESS 0401140733 REVENGE OF MICKEY Wednesday, 14 December, 2005 1 14.2 22 FAMILY LIFE 4981341710 BUILDING A CAR WITH TOOTHPICKS Saturday, 18 March, 2006 2 37.8 59.95 3 CHILDREN 8843172113 DATABASE IMPLEMENTATION Wednesday, 04 June, 2003 3 31.4 55.95 COMPUTER 3437212490 COOKING WITH MUSHROOMS Saturday, 28 February, 2004 4 12.5 19.95 COOKING 3957136468 HOLY GRAIL OF ORACLE Saturday, 31 December, 2005 3 47.25 75.95 3.8 COMPUTER 1915762492 HANDCRANKED COMPUTERS Friday, 21 January, 2005 3 21.8 25 COMPUTER 9959789321 E-BUSINESS THE EASY WAY Wednesday, 01 March, 2006 N 37.9 54.5 COMPUTER 2491748320 PAINLESS CHILD-REARING Saturday, 17 July, 2004 5 48 89.95 4.5 FAMILY LIFE 0299282519 THE WOK WAY TO COOK Saturday, 11 September, 2004 4 19 28.75 COOKING 8117949391 BIG BEAR AND LITTLE DOVE Tuesday, 08 November, 2005 5 5.32 8.95 CHILDREN 0132149871 HOW TO GET FASTER PIZZA Saturday, 11 November, 2006 4 17.85 29.95 1.5 SELF HELP 9247381001 HOW TO MANAGE THE MANAGER Friday, 09 May, 2003 1 15.4 31.95 - BUSINESS 2147428890 SHORTEST POEMS Sunday, 01 May, 2005 5 21.85 39.95 LITERATURE Download row(s) 1 - 14 of 14 CUSTOMERS + Query Count Rows Insert Row Load Data EDIT CUSTOMER# LASTNAME FIRSTNAME ADDRESS CITY STATE ZIP REFERRED REGION EMAIL 1001 MORALES BONITA P.O. BOX 651 EASTPOINT FL 32328 - SE bm225@sat.net 1002 THOMPSON RYAN P.O. BOX 9835 SANTA MONICA CA 90404 W 1003 SMITH LEILA P.O. BOX 66 TALLAHASSEE FL 32306 SE 1004 PIERSON THOMAS 69821 SOUTH AVENUE BOISE ID 83707 NW tpier55@sat.net 1005 GIRARD CINDY P.O. BOX 851 SEATTLE WA 98115 NW cing101@zep.net 1006 CRUZ MESHIA 82 DIRT ROAD ALBANY NY 12211 NE cruztop@axe.com 1007 GIANA TAMMY 9153 MAIN STREET AUSTIN TX 78710 1003 SW treetop@zep.net 1008 JONES KENNETH P.O. BOX 137 CHEYENNE WY 82003 N kenask@sat.net 1009 PEREZ JORGE P.O. BOX 8564 BURBANK CA 91510 1003 W jperez@canet.com 1010 LUCAS JAKE 114 EAST SAVANNAH ATLANTA GA 30314 SE 1011 MCGOVERN REESE P.O. BOX 18 CHICAGO IL 60606 N reesemc@sat.net 1012 MCKENZIE WILLIAM P.O. BOX 971 BOSTON MA 02110 NE will2244@axe.net 1013 NGUYEN NICHOLAS 357 WHITE EAGLE AVE. CLERMONT FL 34711 1006 SE nguy33@sat.net 1014 LEE JASMINE P.O. BOX 2947 CODY WY 82414 N. jaslee@sat.net 1015 SCHELL STEVE P.O. BOX 677 MIAMI FL 33111 SE sschell3@sat.net Download row(s) 1 - 15 of 20 Next CUSTOMERS + v Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT CUSTOMER# LASTNAME FIRSTNAME ADDRESS CITY STATE ZIP REFERRED REGION EMAIL 1016 DAUM MICHELL 9851231 LONG ROAD BURBANK CA 91508 1010 W 1017 NELSON BECCA P.O. BOX 563 KALMAZOO MI 49006 N becca88@digs.com 1018 MONTIASA GREG 1008 GRAND AVENUE MACON GA 31206 SE greg336@sat.net 1019 SMITH JENNIFER P.O. BOX 1151 MORRISTOWN NJ 07962 1003 NE 1020 FALAH KENNETH P.O. BOX 335 TRENTON NJ 08607 NE Kfalah@sat.net Download Previous row(s) 16 - 20 of 20 ORDERS tv Query Count Rows Insert Row Load Data EDIT ORDER# CUSTOMER# ORDERDATE SHIPDATE SHIPSTREET SHIPCITY SHIPSTATE SHIPZIP SHIPCOST 1000 1005 Tuesday, 31 March, 2009 Thursday, 02 April, 2009 1201 ORANGE AVE SEATTLE WA 98114 N 1001 1010 Tuesday, 31 March, 2009 Wednesday, 01 April, 2009 114 EAST SAVANNAH ATLANTA GA 30314 3 1002 1011 Tuesday, 31 March, 2009 Wednesday, 01 April, 2009 58 TILA CIRCLE CHICAGO IL 60605 3 1003 1001 Wednesday, 01 April, 2009 Wednesday, 01 April, 2009 958 MAGNOLIA LANE EASTPOINT FL 32328 4 4 1004 1020 Wednesday, 01 April, 2009 Sunday, 05 April, 2009 561 ROUNDABOUT WAY TRENTON NJ 08601 1005 1018 Wednesday, 01 April, 2009 Thursday, 02 April, 2009 1008 GRAND AVENUE MACON GA 31206 N 1006 1003 Wednesday, 01 April, 2009 Thursday, 02 April, 2009 558A CAPITOL HWY. TALLAHASSEE FL 32307 N 1007 1007 Thursday, 02 April, 2009 Saturday, 04 April, 2009 9153 MAIN STREET AUSTIN TX 78710 7 1008 1004 Thursday, 02 April, 2009 Friday, 03 April, 2009 69821 SOUTH AVENUE BOISE 83707 3 1009 1005 Friday, 03 April, 2009 Sunday, 05 April, 2009 9 LIGHTENING RD. SEATTLE WA 98110 1010 1019 Friday, 03 April, 2009 Saturday, 04 April, 2009 384 WRONG WAY HOME MORRISTOWN NJ 07960 2 1011 1010 Friday, 03 April, 2009 Sunday, 05 April, 2009 102 WEST LAFAYETTE ATLANTA GA 30311 N 1012 1017 Friday, 03 April, 2009 1295 WINDY AVENUE KALMAZOO MI 49002 6 1013 1014 Friday, 03 April, 2009 Saturday, 04 April, 2009 7618 MOUNTAIN RD. CODY WY 82414 N 1014 1007 Saturday, 04 April, 2009 Sunday, 05 April, 2009 9153 MAIN STREET AUSTIN TX 78710 3 Download row(s) 1 - 15 of 21 Next ORDERS tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT ORDER# CUSTOMER# ORDERDATE SHIPDATE SHIPSTREET SHIPCITY SHIPSTATE SHIPZIP SHIPCOST 1015 1020 Saturday, 04 April, 2009 557 GLITTER ST. TRENTON NJ 08606 N 1016 1003 Saturday, 04 April, 2009 9901 SEMINOLE WAY TALLAHASSEE FL 32307 2 1017 1015 Saturday, 04 April, 2009 Sunday, 05 April, 2009 887 HOT ASPHALT ST MIAMI FL 33112 3 1018 1001 Sunday, 05 April, 2009 95812 HIGHWAY 98 EASTPOINT FL 32328 1019 1018 Sunday, 05 April, 2009 1008 GRAND AVENUE MACON GA 31206 2 1020 1008 Sunday, 05 April, 2009 195 JAMISON LANE CHEYENNE WY 82003 2 Download Previous row(s) 16 - 21 of 21 ORDERITEMS + v Query Count Rows Insert Row Load Data EDIT ORDER# ITEM# ISBN QUANTITY PAIDEACH 1000 1 3437212490 1 19.95 1001 1 9247381001 1 31.95 1001 2 2491748320 1 85.45 1002 1 8843172113 2 55.95 1003 1 8843172113 1 55.95 1003 2 1059831198 1 30.95 1003 3 3437212490 1 19.95 1004 1 2491748320 2 85.45 1005 1 2147428890 1 39.95 1006 1 9959789321 1 54.5 1007 1 3957136468 3 72.15 1007 2 9959789321 1 54.5 1007 3 8117949391 1 8.95 1007 4 8843172113 1 55.95 1008 1 3437212490 2 19.95 Download row(s) 1 - 15 of 32 Next ORDERITEMS +v Query Count Rows Insert Row Load Data EDIT ORDER# ITEM# ISBN QUANTITY PAIDEACH 1009 1 3437212490 1 19.95 1009 2 0401140733 1 22 1010 1 8843172113 1 55.95 1011 1 2491748320 1 85.45 1012 1 8117949391 1 8.95 1012 2 1915762492 2 25 1012 3 2491748320 1 85.45 1012 4 0401140733 1 22 1013 1 8843172113 1 55.95 1014 1 0401140733 2 22 1015 1 3437212490 1. 19.95 1016 1 2491748320 1 85.45 1017 1 8117949391 2 8.95 1018 1 3437212490 1 19.95 1018 2 8843172113 1 55.95 Download Previous row(s) 16 - 30 of 32 Next ORDERITEMS tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT ORDER# ITEM# ISBN QUANTITY PAIDEACH 1019 1 0401140733 1 22 1020 1 3437212490 1 19.95 Download Previous row(s) 31 - 32 of 32 Schema WKSP_DBTCMC2020 PROMOTION tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT GIFT MINRETAIL MAXRETAIL BOOKMARKER 0 12 BOOK LABELS 12.01 25 BOOK COVER 25.01 56 FREE SHIPPING 56.01 999.99 Download rows 1.4 of 4 PUBLISHER tv Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT PUBID NAME CONTACT PHONE 1 PRINTING IS US TOMMIE SEYMOUR 000-714-8321 2 PUBLISH OUR WAY JANE TOMLIN 010-410-0010 3 AMERICAN PUBLISHING DAVID DAVIDSON 800-555-1211 4 READING MATERIALS INC. RENEE SMITH 800-555-9743 5 REED-N-RITE SEBASTIAN JONES 800-555-8284 Download row(s) 1 - 5 of 5