Shopping mall managent system

shopping mall managment system

2. CREATE TABLE shopping_mall
(
branch_id number(20)primary key,
branch_name varchar2(20),
Street VARCHAR2(20) ,
City VARCHAR2(20) ,
State CHAR(20),
Zip VARCHAR2(10));

INSERTING VALUES IN SHOPPING MALL

DELETE VALUES FROM SHOPPING MALL
delete from shopping_mall
where branch_id=001

3. DELETE COLUMN IN TABLE
alter table shopping_mall drop
column state

CREATE TABLE employee
(
employee_id number(20) primary key,
first_name varchar(20),
last_name varchar(20),
email varchar(20),
phone_no number(20),
salary number(20),
branch_id number(20),
foreign key(branch_id)
references shopping_mall(branch_id)
);

4. AFTER INSERTING VALUES IN EMPLOYEE TABLE:





CREATE TABLE customers (
customer_id NUMBER(20)primary key,
customer_name VARCHAR2(30),
Street VARCHAR2(20) ,
City VARCHAR2(20) ,
State CHAR(20),

5. Zip VARCHAR2(10) ,
Phone VARCHAR2(12)
Product_id number(20)
Foreign key(product_id)
References products(product_id)
);

After inserting values:

6. CREATE TABLE products
(
product_id number(20)primary key,
product_name varchar(20),
product_cost number(20),
exp_date char(20),
sales_date char(20),
customer_id number(20),
foreign key(customer_id)
references customers(customer_id)
);

After inserting values in products:

7. CREATE TABLE Orders
(
Order_Id number(20),
Order_no number(20),
customer_ID number(20) references customers(customer_ID),
Product_ID number(20) references Products(Product_ID),
Primary key (order_id,Customer_id, Product_ID)
);

After inserting values in orders

8. Joins
Select
shopping_mall.branch_id,shopping_mall.branch_name,employee.employee_id,employee.first_name
from shopping_mall,employee
where shopping_mall.branch_id=employee.branch_id

select
customers.customer_id,customers.customer_name,customers.phone,products.product_id,products.pro
duct_name,products.product_cost,orders.order_id,orders.customer_id,orders.product_id
from customers,products,orders
where customers.product_id=products.product_id

select
customers.customer_id,customers.customer_name,customers.phone,products.product_id,products.pro
duct_name,products.product_cost,orders.order_id,orders.customer_id,orders.product_id
from customers,products,orders
where customers.product_id=products.product_id and orders.product_id=customers.product_id