Develop SQL using Oracle

Develop SQL using Oracle
• You need to create all the tables from scratch, populate them and work on the SQL queries.There are 14 tasks in total.
• Marks for each task has been indicated next to each task.

CASE STUDY:
Mr. Matt Damon is the data architect hired by Bargain Shoppe, to work on the spreadsheets and convert it into relational databases. Matt has decided to prototype the ‘delivery of goods’ aspect of the business. Delivery of goods take place from the Wholesalers’ places of business to the Store. Your task is to help Matt construct the tables in the database and write frequently run queries.

Task 1 (5 marks)
Create tables based on the schema given below:
Table Name: SHOP
Column-name Datatype
SHOPID (PK) CHAR(4)
ADDRESS VARCHAR2(40)
CITY VARCHAR2(15)
STATE VARCHAR2 (3)
CONTACTNO CHAR(10)
Table Name: APPLIANCE
Column-name Datatype
APPLIANCEID(PK) CHAR(4)
MODELID(PK) CHAR(4)
APPLIANCENAME VARCHAR2(25)
MANUFACTUREYEAR NUMBER(4)
PRICE NUMBER(6,2)
Table Name:DELIVERY
Column-name Datatype
DELIVERYID(PK) CHAR(5)
SHOPID(FK) ???
APPLIANCEID(FK) ???
MODELID(FK) ???
WHOLESALERID(FK) ???
DELIVERYDATE DATE
QUANTITY NUMBER(2)
PRICEPERUNIT NUMBER(6,2)
Table Name: WHOLESALER
Column-name Datatype
WHOLESALERID(PK) CHAR(4)
BUSINESSNAME VARCHAR2 (25)
ADDRESS VARCHAR2(40)
CITY VARCHAR2(15)
STATE VARCHAR2 (3)
CONTACTNO CHAR(10)
(PK)-Primary Key.
(FK)-Foreign Key.
???-You need to find these values from other tables.
Task 2 (4 marks)
Populate the tables with values given below
SHOP Table
SHOPID ADDRESS CITY STATE CONTACTNO
S001 23 BROADWAY SYDNEY NSW 0298762234
S002 39 AUBURN SYDNEY NSW 0290224533
S003 486 PHILLIP CANBERRA ACT 0278923455
S004 56 TORQUAY GEELONG VIC 0357788990
S005 99 ST KILDA MELBOURNE VIC 0356331121
S006 30 RIVERSIDE LAUNCESTON TAS 0344556611
S007 7 CAIRNS ST CAIRNS QLD 0756112098
S008 19 CLARE ADELAIDE SA 0834553555
S009 903 HERBERT DARWIN NT 0855667788
S010 19 YOKINE PERTH WA 0823451122

APPLIANCE Table
APPLIANCEID MODELID APPLIANCENAME MANUFACTUREYEAR PRICE
F001 M001 2 DOOR FRIDGE 2015 2344.67
F001 M034 SMART FRIDGE 2014 8899.09
F001 M099 1 DOOR FRIDGE 2013 777.99
TV01 M001 SMART LED TV 2015 6778.93
TV01 M003 LCD TV 2012 2211.12
TV01 M015 3D SMART LCD TV 2014 3446.78
W001 M001 WASHING MACHINE TL 2014 904.83
W001 M099 WASHING MACHINE FL 2015 1988.29
W001 M321 WASHING MACHINE NODRYER 2010 655.21
K001 M001 KETTLE-1L 2013 88.50
K001 M089 KETTLE-5L 2015 121.90
MI01 M034 MICROWAVE-20L 2012 200.00
MI01 M009 MICROWAVE-34L 2013 300.00

DELIVERY Table
DELIVERYID SHOP ID APPLIANCEID MODELID WHOLESALERID DELIVERY DATE QUANTITY PRICE
D001 S001 F001 M001 W001 01/AUG/15 45 2000.9
D002 S001 TV01 M015 W003 03/AUG/15 23 3000.7
D003 S003 TV01 M001 W005 11/AUG/15 21 6000.4
D004 S004 F001 M034 W002 19/AUG/15 18 8000.9
D005 S004 W001 M099 W003 16/AUG/15 10 1500
D006 S005 TV01 M001 W002 10/AUG/15 21 6000
D007 S007 W001 M099 W002 21/AUG/15 34 1500
D008 S002 F001 M001 W007 21/AUG/15 45 2000.9
D009 S006 W001 M001 W006 11/AUG/15 41 800.55
D010 S010 W001 M001 W003 10/AUG/15 26 800.55
D011 S008 F001 M034 W001 29/AUG/15 50 8000
D012 S009 TV01 M015 W003 22/AUG/15 10 3000.7
D013 S009 MI01 M034 W004 29/AUG/15 70 150
D014 S007 K001 M001 W005 20/AUG/15 23 50.3
D015 S002 MI01 M009 W002 07/AUG/15 40 200

WHOLESALER Table
WHOLESALERID WHOLESALERNAME ADDRESS CITY STATE CONTACTNO
W001 GOT WHOLESALERS 24 LANECOVE RD SYDNEY NSW 0298999099
W002 SUITS WHOLESALERS 562 PRINCESS ST CANBERRA ACT 0290009000
W003 RING WHOLESALERS 435 REASON RD PERTH WA 0844445555
W004 BARRY WHOLESALERS 11 SYMPHONY DRIVE DARWIN NT 0811112222
W005 MURPHY WHOLESALERS 77 PILOT ST HOBART TAS 0355567778
W006 PABLO WHOLESALERS 49 COGNO RD ADELAIDE SA 0899990000
W007 ELLIS WHOLESALERS 133 PARTNER DRIVE MELBOURNE VIC 0344556622

Task 3 (2 marks)
Write anSQL Statement that displays wholesaler’s business name and the city they are based in.
Task 4 (2 marks)
Write anSQL Statementthat displays details of shop (address, city, state and contact number). Contact number should not have the area code. In other words, the first two digits in contact number must not be displayed
Task 5 (3 marks)
Write anSQL Statement that displays details fromappliance table (name, year of manufacture and price) and perform a multi-level sorting [year of manufacture (descending) followed by price (ascending)]. Price has to be displayed with a ‘$’ character in the output.
Task 6 (3 marks)
Write anSQL Statement that displays all details of shops from ‘NSW’or ‘VIC’ if they address has the letter ‘O’ somewhere in it.

Task 7 (3 marks)
Write anSQL Statement that displays delivery details -id, city and state the delivery is scheduled for and the total cost of the delivery (you need to calculate this value). Show delivery only if the date of delivery is on or after 20th of August, 2015. Use proper alias for any computational columns.
Task 8 (3 marks)
Write anSQL Statementthat displaysthe shop details -id and the total number of appliances delivered to each shop and sort the results by shop id.
Task 9 (3 marks)
Write anSQL Statementthat displaysall columns from Delivery and Wholesaler tables with just one instance of Wholesaler ID Column.
Task 10 (4 marks)
Write anSQL Statement that displays delivery details (id, date of delivery and quantity), if the quantity is less than 20 or over 30 and date of delivery is after 15th of August,2015. Sort the results by date of delivery.
Task 11 (4 marks)
Write anSQL Statementthat displaysthe details of shops (address, city and state) that have been supplied by wholesalers from NSW, TAS, NT and WA.
Task 12 (5 marks)
Write anSQL Statement that displays the names of the states and a corresponding count of deliveries to shops in those states. Display only the states that have 3 letters in their name. Sort the results by the count of deliveries in descending order.
Task 13 (5 marks)
Write anSQL Statement that displays all details of appliances if they are delivered on 21st of August, 2015 or on the 29th of August, 2015
Task 14 (5 marks)
Write anSQL Statement that displays only top 3 wholesalers’ business names and their corresponding average of quantities. (Top 3 businesses are those who have the highest average in the quantity of items)

FOR YOUR ASSIGNMENTS TO BE DONE AT A CHEAPER PRICE PLACE THIS ORDER OR A SIMILAR ORDER WITH US NOW