1A)
STUDENT NAME:- RENISH SURANA
STUDENT ID:- 100670335 ASSIGNMENT 1(DAD)

TASK 1
1B)
One state many have many supplier.
One supplier may purchase many products.
One product must belong to one category.
One category may have many products.
One product must be purchased from one supplier.
One supplier must be located in one state.

1C)
THE RELATIONAL SCHEMA ARE:-

--CATEGORY(Catcode,Catname)
--primary key Catcode
--
--STATE(Statecode,Statename)
--primary key Statecode
--
--SUPPLIER(Suppcode,Suppname,City,Statecode)
--primary key Suppcode
--foreign key Statecode References STATE
--
--PRODUCT(Prodid,Descr,Sellingprice,Purchasecost,Weight_kg,Suppcode,Catcode)
--primay key prodid
--foreign key suppcode References …show more content…
--foreign key Catcdode References CATEGORY
--

1D)
--Prompt Creating Table Category;

create table CATEGORY ( catcode varchar2(4), catname varchar2(30), primary key (catcode) );

--Prompt Creating table STATE;

create table STATE ( statecode varchar2(4), statename varchar2(30), primary key (statecode) );

--Prompt Creating Table SUPPLIER;

create table SUPPLIER ( suppcode number(4), suppname varchar2(30), city varchar2(30), statecode varchar2(4) , primary key (suppcode), foreign key (Statecode) References STATE );

--Prompt Creating Table PRODUCT;

create table PRODUCT( prodid number(4), descr varchar2(30), sellingprice number(6,2), purchasecost number(6,2), weight_kg number(6,2), suppcode number (4), catcode varchar2(4), primary key (Prodid), foreign key (Suppcode) References SUPPLIER, foriegn key (Catcode) References CATEGORY );

1E)
DROP TABLE CATEGORY CASCADE CONSTRAINTS;
DROP TABLE STATE CASCADE CONSTRAINTS;
DROP TABLE SUPPLIER CASCADE CONSTRAINTS;
DROP TABLE PRIDUCT CASCADE CONSTRAINTS;

--Prompt Creating Table Category;

create table CATEGORY ( catcode varchar2(4), catname varchar2(30), primary key (catcode) );

--Prompt Creating table …show more content…
TASK 2
2 A)
-- Prompt Query 2A
SELECT *
FROM CATEGORY
ORDER BY catcode ASC;

SELECT *
FROM STATE
ORDER BY statecode ASC;

SELECT *
FROM PRODUCT
ORDER BY prodid ASC;

SELECT *
FROM SUPPLIER
ORDER BY suppcode ASC;

B)
--Prompt Query 2B
INSERT INTO SUPPLIER (suppcode,suppname,city,statecode) VALUES (7,'Dud Goods','Perth','WA');

C)
--Prompt Query 2C
INSERT INTO PRODUCT (prodid,descr,sellingprice,purchasecost,weight_kg,catcode,suppcode) VALUES (11,'Bad Thing',200,100,25,'HOME',2);

D)
-- Prompt Query 2D
SELECT prodid,descr,catcode,sellingprice
FROM PRODUCT
ORDER BY catcode ASC,sellingprice DESC;

E)
-- Prompt Query 2E
SELECT prodid,descr,catcode,sellingprice
FROM PRODUCT
ORDER BY sellingprice DESC;

F)
--Prompt query 2F
SELECT prodid,descr,sellingprice,purchasecost,sellingprice-purchasecost as "Markup Amount"
FROM PRODUCT
ORDER BY "Markup Amount" ASC;

G)
--Prompt Query 2G
SELECT prodid,descr,(sellingprice-purchasecost)/purchasecost*100 as "Mark up percentage"
FROM PRODUCT
ORDER BY "Mark up percentage" ASC;

H)
--Prompt Query 2H
SELECT prodid,descr
FROM PRODUCT
WHERE upper(descr) LIKE upper('%chair%')
OR upper(descr) LIKE upper('%table%')
ORDER BY descr;

I)
--Prompt Query