1 CREATE TABLES Question
DIVISION
Column Name	DIVISION_ID	DIVISION_NAME
Key Type	PK
Null/Unique		NN, U
FK Table
FK Column
Validation
Datatype	NUMBER	VARCHAR
Length	3	25
Sample data

	10	East Coast
	20	Quebec
	30	Ontario

WAREHOUSE
Column Name	WAREHOUSE_ID	CITY	RATING	FOUND_DATE	DIVISION_ID
Key Type	PK		CK		FK
Null/Unique		NN, U		NN	NN
FK Table					DIVISION
FK Column					DIVISION_ID
Validation			A, B, C, D
Datatype	NUMBER	VARCHAR	CHAR	DATE	NUMBER
Length	3	15	1		3
Sample Data	1	Montreal 	A	Current date	10
	7	Fredericton	B	Current date	10
	10	Toronto	A	Current date	30

SECTION
Column Name	WAREHOUSE_ID	SECTION_ID	DESCRIPTION	CAPACITY
Key Type	PK, FK	PK
Null/Unique			NN
FK Table	WAREHOUSE
FK Column	WAREHOUSE_ID
Datatype	NUMBER	NUMBER	VARCHAR	NUMBER

Length	3	2	50	8

Sample data	1	1	Whse 1 Floor 1	2000
	1	2	Whse 1 Floor 2	500
	7	1	Whse 7 Floor 1	15000


1 (10 marks) Write the required SQL statements to create tables WAREHOUSE, DIVISION and SECTION.


Follow these general rules in the process:
A. Create all CHECK (incl. NOT NULL) and UNIQUE as column level constraints
Constraint names needed for CHECK constraints.
The other constraints (NN and UK) do not need a name.
B. Create all PK and FK constraints at the table level and give them proper names.

PUT ANSWERS starting here

create table division (
  	division_id number(3),
  	division_name varchar2(25)
    		constraint div_division_name_uq unique
    		constraint div_division_name_nn not null,
  	constraint div_division_id_pk primary key (division_id)
  );

create table warehouse (
  	warehouse_id number(3),
  	city varchar2(15)
    		constraint wh_city_uq unique
    		constraint wh_city_nn not null,
  	rating char(1)
    		constraint wh_rating_ck check (rating in('A','B','C','D')),
  	found_date date default sysdate
    		constraint wh_found_date_nn not null,
 	division_id number(3)
    		constraint wh_division_id_nn not null,
  	constraint wh_warehouse_id_pk primary key (warehouse_id),
  	constraint wh_division_id_fk foreign key (division_id) references division(division_id)
    );

create table section (
  	warehouse_id number(3),
  	section_id number(2),
  	description varchar2(50)
    		constraint sec_section_nn not null,
  		capacity number (8),
  	constraint sec_warehouse_id_section_id_pk primary key (warehouse_id, section_id),
  	constraint sec_warehouse_id_fk foreign key (warehouse_id) references warehouse(warehouse_id)
  );


insert all
  	into division values (10, 'East Coast')
  	into division values (20, 'Quebec')
 	into division values (30, 'Ontario')
select 1 from dual;

insert all
  	into warehouse values (1, 'Montreal', 'A', sysdate, 10)
 	into warehouse values (7, 'Fredericton', 'B', sysdate, 10)
 	into warehouse values (10, 'Toronto', 'A', sysdate, 10)
select 1 from dual;

insert all
into section values (1, 1, 'Whse 1 Floor 1', 2000)
  	into section values (1, 2, 'Whse 1 Floor 2', 500)
  	into section values (7, 1, 'Whse 7 Floor 1', 15000)
select 1 from dual;

2 (3 marks) After creating all tables add column MGR_ID to table SECTION as a FK column that is related to the PK column EMPLOYEE_ID in table EMPLOYEE

alter table section
	add (mgr_id number(6,0)
		constraint sec_mgr_id_fk references employees(employee_id));

3 (3 marks) Modify the CHECK constraint on column RATING in table WAREHOUSE, so that it also may accept a new value F.

alter table warehouse
  	drop constraint wh_rating_ck;

alter table warehouse
 	 add constraint wh_rating_ck check (rating in('A','B','C','D','F'));

4 (3 marks) Create a new Sequence called Whse_id_seq that will generate unique numbers for PK values in table WAREHOUSE, so that the numbers start at 320 with the step of 15 and upper limit is 900 and will have NO values stored in the memory.

create sequence whse_id_seq
increment by 15
start with 320
  	maxvalue 900
  	nocache
  	nocycle;

5 (3 marks) Add new row to table WAREHOUSE by using this sequence for a city in Atlanta with unknown rating and division 30. You will assume today’s date as a foundation date. The date is to be entered automatically, meaning you cannot enter a specific date.

insert into warehouse (warehouse_id, city, division_id)
  	values (whse_id_seq.nextval, 'Atlanta', 30);

6 (5 marks) Create table CITIES from table LOCATIONS, but only for location numbers less than 2000 (do NOT create this table from scratch).   You will have 5 to 18 rows

create table cities as
  	(select * from locations
  	where location_id < 2000);

7 (2 marks) Issue command to show the structure of the table CITIES

describe cities;

8 (1 mark) Issue the SELECT command on cities and show result here.

Select * from cities;

LOCATION_ID | STREET_ADDRESS | POSTAL_CODE | CITY | STATE_PROVINCE | COUNTRY_ID
1000		1297 Via Cola di Rie		00989	Roma				IT
1100		93091 Calle della Testa	10934	Venice				IT
1200		2017 Shinjuku-ku		1689	Tokyo	Tokyo Prefecture	JP
1300		9450 Kamiya-cho		6823	Hiroshima			JP
1400		2014 Jabberwocky Rd		26192	Southlake	Texas		US
1500		2011 Interiors Blvd		99236	South San Francisco	California	US
1600		2007 Zagora St	 		50090	South Brunswick	New Jersey	US
1700		2004 Charade Rd		98199	Seattle	Washington		US
1800		147 Spadina Ave	M5V 2L7 	Toronto	Ontario		CA
1900		6092 Boxwood St	YSW 9T2 	Whitehorse	Yukon		CA

9 (5 marks) Create a View called WhsSec_Man_vu that will display for each Warehouse_id and Section_id, the City, Division and manager’s Last_name.
Alias for Last_name should be LName and for Division should be Group.

create view whsSec_man_vu as
 	select s.warehouse_id, s.section_id, w.city, w.division_id "Group", s.mgr_id Lname
    	from warehouse w, section s
    	where s.warehouse_id = w.warehouse_id;

10 (1 mark) What is the SELECT command to issue if in 2 months I want to test if a view was actually created

select * from whssec_man_vu;

11 (1 mark) If you want to modify the view what is the first line of the command

replace view whssec_man_vu;

The following is not on test 2, but you are responsible to have done self-study on the topic.
12 Issue a SET operator to show the rows that were in LOCATIONS but not in CITIES

select * from locations minus select * from cities;

Using the following diagram as a hint and not a perfect representation.
Answer 13, 14, 15 and 16




13 All the rows in A and all the rows in B with no duplicates is the set operator called
[Symbol]

Union

Select * from A union select * from B;

14 All the rows in A and all the rows in B with duplicates [Symbol]

Union All

Select * from A union all select * from B;


15 The rows in common to BOTH A and B tables [Symbol]

Intersect

Select * from A intersect select * from B;


16 Rows that are in A but not in B would use the word [Symbol]

Minus

Select * from A minus select * from B;