--Modify this script so that it meets the requirements of Assignment 1
--When done, complete these tasks:
--1. SAVE this file
--2. EXECUTE this entire script in ISQL Jr
--3. Copy and paste the OUTPUT generated by ISQL Jr into a file named ASS1_1234567_OUTPUT.TXT (where 1234567 is your student id)
--4. ZIP this file with the ASS1_1234567_OUTPUT.TXT file and submit via ESP (see instructions on page 1 of the assignment)
prompt START OF OUTPUT SECTION
prompt Student ID:    XXXXXXXXXXXXXX
prompt Student Name:  XXXXXXXXXXXXXX
prompt Tutor Name:    XXXXXXXXXXXXXX
prompt Tutorial Day:  XXXXXXXXXXXXXX
prompt Tutorial Time: XXXXXXXXXXXXXX
prompt Tutorial Room: XXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 1.1 ENGLISH SENTENCES DESCRIBING THE ERD
--1 State must have Many cities. 1 City must have many warehouses. Warehouses may have 1 city
prompt --------------------------------------------------------------
prompt Task 1.2 DROP ALL TABLES
DROP TABLE WAREHOUSE;
DROP TABLE STOCKITEM;
DROP TABLE MANUFACTURER;
DROP TABLE CITY;
DROP TABLE STATE;


prompt --------------------------------------------------------------
prompt Task 1.3 CREATE STATE TABLE
CREATE TABLE STATE (PRIMARY KEY (STATECODE), STATECODE varchar2(3), STATENAME VarChar2(30) NOT NULL, POPULATION Number(8));
prompt --------------------------------------------------------------
prompt Task 1.4 INSERT STATEMENTS TO POPULATE STATE TABLE WITH VALID DATA
INSERT INTO STATE (STATECODE, STATENAME, POPULATION) VALUES ('ACT' , 'Australian Capital Territory', 382900);
INSERT INTO STATE (STATECODE, STATENAME, POPULATION) VALUES ('NSW' , 'New South Wales', 7439200);
INSERT INTO STATE (STATECODE, STATENAME, POPULATION) VALUES ('NT' , 'Northern Territory',241800);
INSERT INTO STATE (STATECODE, STATENAME, POPULATION) VALUES ('QLD' , 'Queensland', 4676400);
INSERT INTO STATE (STATECODE, STATENAME, POPULATION) VALUES ('SA' , 'South Australia', 1674700);
INSERT INTO STATE (STATECODE, STATENAME, POPULATION) VALUES ('TAS' , 'Tasmania',513400);
INSERT INTO STATE (STATECODE, STATENAME, POPULATION) VALUES ('VIC' , 'Victoria', 5768600);
INSERT INTO STATE (STATECODE, STATENAME, POPULATION) VALUES ('WA' , 'Western Australia',2535700);

prompt --------------------------------------------------------------
prompt Task 1.5 STATE List 1
Select * From STATE
ORDER BY POPULATION DESC;
prompt --------------------------------------------------------------
prompt Task 1.6 PK Test
INSERT INTO STATE (STATECODE,STATENAME,POPULATION) VALUES ( 'NT', 'Northern Tasmania', 190000);
prompt --------------------------------------------------------------
prompt Task 1.7 State Name Size Test
INSERT INTO STATE (STATECODE,STATENAME,POPULATION) VALUES ( 'SWA', 'South West Region of Western Australia', 418000);
prompt --------------------------------------------------------------
prompt Task 1.8 Population SIze Test
INSERT INTO STATE (STATECODE,STATENAME,POPULATION) VALUES ( 'CI', 'Christmas Island', 123000000);
prompt --------------------------------------------------------------
prompt Task 1.9 CREATE CITY TABLE
CREATE TABLE CITY (PRIMARY KEY (CITYID), CITYID number(3), CITYNAME VarChar2(30), STATECODE varchar2(3), FOREIGN KEY (STATECODE)  REFERENCES STATE);
prompt --------------------------------------------------------------
prompt Task 1.10 INSERT STATEMENTS TO POPULATE CITY TABLE WITH VALID DATA
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 1, 'Melbourne', 'VIC');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 2, 'Bayswater', 'VIC');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 3, 'Sydney', 'NSW');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 4, 'Bayswater', 'WA');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 5, 'Geelong', 'VIC');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 6, 'Beconsfield', 'VIC');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 7, 'Adelaide', 'SA');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 8, 'Beconsfield', 'TAS');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 9, 'Springfield', 'TAS');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 10, 'Springfield', 'NSW');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 11, 'Springfield', 'TAS');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 12, 'Springfield', 'SA');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 13, 'Springfield', 'WA');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 14, 'Darwin', 'NT');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 15, 'Wollongong', 'NSW');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 16, 'Brisbane', 'QLD');
INSERT INTO CITY (CITYID, CITYNAME, STATECODE) VALUES ( 17, 'Airlie Beach', 'QLD');
prompt --------------------------------------------------------------
prompt Task 1.11 CITY List
SELECT * FROM CITY
ORDER BY STATECODE ASC;
prompt --------------------------------------------------------------
prompt Task 1.12 List with INNER JOIN
SELECT C.CITYID, C.CITYNAME, S.STATENAME, C.POPULATION
FROM CITY C
INNER JOIN
STATE S ON
C.STATECODE = S.STATECODE;
prompt --------------------------------------------------------------
prompt Task 1.13 FK Test (Must Fail)
INSERT INTO CITY (CITYID,CITYNAME,STATECODE) VALUES (20, 'Bendigo', 'TED');
prompt --------------------------------------------------------------
prompt Task 1.14 NULL FK Test (Must Fail)
INSERT INTO CITY (CITYID,CITYNAME,STATECODE) VALUES (21, 'Newcastle');
prompt --------------------------------------------------------------
prompt Task 1.15 CREATE WAREHOUSE TABLE
CREATE TABLE WAREHOUSE (PRIMARY KEY (WHID), WHID number(3), SECURITY_LEVEL Number(1), QUARANTINE_FACILITIES varchar2(1), MAXNUMBEROFPALLETS Number(6), FREEZER_FACILITIES varchar2(1), COSTPERPALLET Number(4,2), CITYID number(3), FOREIGN KEY (CITYID)  REFERENCES CITY);
prompt -------------------------------------------------------------
prompt Task 1.16 INSERT STATEMENTS TO POPULATE WAREHOUSE TABLE WITH VALID DATA
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (1, 50, 4160, 46.30, 1, 'Y', 'Y');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (2, 52, 8260, 12.50, 5, 'N', 'N');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (1, 54, 19430, 26.45, 1, 'Y', 'Y');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (3, 56, 1490, 17.10, 5, 'Y', '');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (8, 58, 16750, 30.30, 2, 'N', 'Y');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (10, 60, 3290, 26.20, 1, 'N', '');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (14, 62, 3610, 22.90, 3, 'N', 'Y');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (15, 64, 6660, 14.35, 5, 'N', 'Y');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (3, 66, 13160, 16.70, 4, 'Y', 'Y');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (16, 68, 2760, 32.90, 2, 'N', 'Y');
INSERT INTO WAREHOUSE (CITYID, WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES,QUARANTINE_FACILITIES) VALUES (17, 70, 5770, 29.75, 1, 'N', 'N');

prompt Task 1.17
SELECT * FROM WAREHOUSE ORDER BY WHID ASC;

prompt Task 1.17.1 Query 1
SELECT * FROM CITY
WHERE CITYNAME LIKE 'B%' OR CITYNAME = 'D%'
ORDER BY CITYNAME ASC;
prompt --------------------------------------------------------------
prompt Task 1.17.2 Query 2
SELECT WHID, MAXNUMBEROFPALLETS
FROM WAREHOUSE
WHERE (MAXNUMBEROFPALLETS < 3000 OR MAXNUMBEROFPALLETS > 10000)
AND
(SECURITY_LEVEL = 1 OR SECURITY_LEVEL =5)
ORDER BY WHID ASC;
prompt --------------------------------------------------------------
prompt Task 1.17.3 Query 3
SELECT WHID, COSTPERPALLET, SECURITY_LEVEL, FREEZER_FACILITIES
FROM WAREHOUSE
WHERE FREEZER_FACILITIES = 'Y'
AND
(SECURITY_LEVEL = 1 AND COSTPERPALLET < 30)
OR
(SECURITY_LEVEL = 3 AND COSTPERPALLET < 15)
ORDER BY WHID ASC;
prompt --------------------------------------------------------------
prompt Task 1.17.4 Query 4
SELECT * FROM WAREHOUSE
WHERE QUARANTINE_FACILITIES = ' '
ORDER BY WHID ASC;
prompt --------------------------------------------------------------
prompt Task 1.17.5 Query 5
SELECT * FROM CITY
WHERE CITYNAME LIKE '%W%' OR CITYNAME LIKE '%AI%'
ORDER BY CITYNAME ASC;
prompt --------------------------------------------------------------
prompt Task 1.17.6 Query 6
SELECT WHID, MAXNUMBEROFPALLETS, COSTPERPALLET, (MAXNUMBEROFPALLETS * COSTPERPALLET) AS "MAXIMUMCOST"
FROM WAREHOUSE;
prompt --------------------------------------------------------------
prompt Task 1.17.7 Query 7
SELECT W.WHID, W.MAXNUMBEROFPALLETS,S.POPULATION, S.STATENAME, (W.MAXNUMBEROFPALLETS * W.COSTPERPALLET) AS "W.MAXIMUMCOST"
FROM WAREHOUSE W
INNER JOIN CITY C
ON  W.CITYID = C.CITYID
INNER JOIN  STATE S
ON C.STATECODE = S.STATECODE
WHERE POPULATION > 3000000
AND
W.MAXNUMBEROFPALLETS >= 2000
AND W.MAXNUMBEROFPALLETS * W.COSTPERPALLET < 150000
ORDER BY WHID ASC;
prompt --------------------------------------------------------------
prompt Task 1.17.8 Query 7
SELECT  DISTINCT CITYNAME
FROM CITY
ORDER BY CITYNAME ASC;
prompt --------------------------------------------------------------
prompt Task 1.18 Update 1
UPDATE WAREHOUSE
SET MAXNUMBEROFPALLETS = 20000
WHERE WHID = 54;
prompt --------------------------------------------------------------
prompt Task 1.19 Update 2
UPDATE WAREHOUSE
SET COSTPERPALLET = 1.99
WHERE SECURITY_LEVEL = 3;
prompt --------------------------------------------------------------
prompt Task 1.20 Delete 1
DELETE FROM CITY
WHERE CITYID = 13;
prompt --------------------------------------------------------------
prompt Task 1.21 Delete 2 (Must Fail)
DELETE FROM CITY
WHERE CITYID = 3;
prompt --------------------------------------------------------------
prompt Task 1.22 Warehouse Query
SELECT * FROM WAREHOUSE
ORDER BY WHID ASC;
prompt --------------------------------------------------------------
prompt Task 2.1 ENGLISH SENTENCES DESCRIBING THE ERD
--ONE STATE MAY HAVE MANY CITIES
--ONE CITY MUST HAVE ONE STATE
--ONE CITY MAY HAVE MANY WAREHOUSES
--ONE WAREHOUSE MUST HAVE ONE CITY
--ONE WHAREHOUSE MAY HAVE A STOCKITEM
--ONE STOCK ITEM MUST HAVE  A WAREHOUSE
--ONE CITY MAYBE HAVE MANY MANUFACTURERS
--ONE MANUFACTURER MUST HAVE A CITY
prompt --------------------------------------------------------------
prompt Task 2.2 DROP ALL TABLES
--DO NOT ADD DROP STATEMENT HERE. PUT THEM IN SECTION 1.2 ABOVE
--DO NOT ADD DROP STATEMENT HERE. PUT THEM IN SECTION 1.2 ABOVE
prompt --------------------------------------------------------------
prompt Task 2.3 CREATE MANUFACTURER and STOCKITEM TABLES
CREATE TABLE MANUFACTURER (PRIMARY KEY (MANID), MANID number(2), MANNAME varchar2(30), CITYID number(3), FOREIGN KEY (CITYID) REFERENCES CITY);
CREATE TABLE STOCKITEM (PRIMARY KEY (STKID), STKID number(2), STKNAME varchar2(30), SELLINGPRICE number(6,2), PURCHASEPRICE number(6,2), MANID number(2), FOREIGN KEY (MANID) REFERENCES MANUFACTURER, FOREIGN KEY (WHID) REFERENCES WAREHOUSE);
prompt --------------------------------------------------------------
prompt Task 2.4 INSERT STATEMENTS TO POPULATE MANUFACTURER TABLE WITH VALID DATA
INSERT INTO MANUFACTURER (MANID, MANNAME, CITYID) VALUES (41, 'SJ Smiths Goods', 1);
INSERT INTO MANUFACTURER (MANID, MANNAME, CITYID) VALUES (42, 'Rose Tyler Electrics', 3);
INSERT INTO MANUFACTURER (MANID, MANNAME, CITYID) VALUES (43, 'Noble House', 7);
INSERT INTO MANUFACTURER (MANID, MANNAME, CITYID) VALUES (44, 'The Pond', 16);
INSERT INTO MANUFACTURER (MANID, MANNAME, CITYID) VALUES (45, 'Mantha Jones Manufacturing', 1);
INSERT INTO MANUFACTURER (MANID, MANNAME, CITYID) VALUES (46, 'SJ Smiths Goods', 3);

prompt --------------------------------------------------------------
prompt Task 2.5 INSERT STATEMENTS TO POPULATE STOCKITEM TABLE WITH VALID DATA
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (71, 'Electric Fan', 15.5, 6.2, 42, 50);
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (74, 'Drill Set', 26, 9.1, 45, 52);
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (77, 'Sun Lounge', 38, , 43, 56);
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (78, 'Whisk Combo', 11,2.75, 41, 56);
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (82, 'Solar Pump', 5.6, 1.68, 44, 58);
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (84, 'Storage Box', 27.75, 9.72, 46, 56);
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (85, 'Refrigerator', 40, 6.2, 42, 50);
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (88, 'DVD player', 32.5, 18.5, 41, 58);
INSERT INTO STOCKITEM (STKID, STKNAME, SELLINGPRICE, PURCHASEPRICE, MANID, WHID) VALUES (89, 'Garden Tools', 18, 7.2, 46, 50);

prompt --------------------------------------------------------------
prompt Task 2.6 FK Test 1 (Must Fail)
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 2.7 FK Test 2 (Must Fail)
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 2.8.1 FK Test 3 (Must Fail)
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 3.1.1 Query 1
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 3.1.2 Query 2
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 3.1.3 Query 3
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 3.1.4 Query 4
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 3.1.5 Query 5
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 3.2 Delete 1 (Must Fail)
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 3.3 Delete 2 (Must Fail)
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------
prompt Task 4 Query 1
--XXXXXXXXXXXXXXXX
prompt --------------------------------------------------------------