Project 1

TASk 3:

1. SELECT order1.order_no AS orderNo,
customer.cus_name AS CustomerName,customer.cus_city AS city
FROM order1 ,customer
WHERE order1.cus_id=customer.cus_id;

2. CREATE TABLE product(cat_id INT(11),
pro_id INT (12),
cat_name VARCHAR(50),
product_name VARCHAR(50),
product_Price FLOAT,
product_Size VARCHAR(5),
product_color VARCHAR(20),
procudt_Fabric VARCHAR(20),
product_Quantity INT(5),
product_Update TIMESTAMP,
FOREIGN KEY (cat_id) REFERENCES categories(cat_id));
INSERT INTO product(cat_id, pro_id,cat_name,product_name,product_Price ,product_Size,product_color, procudt_Fabric,product_Quantity)
VALUES (101,50,'MensWear','T_shirt',999,'s','red','Baniyen',50),
(102,21,'Womens','Top',899,'xl','orange','cotton',500),
(103,12,'Kids','Frok',1999,'M','green','cotton',100),
(101,15,'MensWear','jens',2999,'l','red','cotton',80),
(103,24,'Kids','Babashowt',499,'s','red','cotton',200),
(101,65,'MensWear','Sports T_Shirt',999,'xl','blue','cotton',70),
(102,48,'Womens','Kurthis',3999,'xxl','pink','cotton',100),
(101,14,'MensWear','Phant',799,'xl','brown','cotton',300),
(103,13,'Kids','Midiset',599,'s','red','cotton',60),
(101,59,'MensWear','shorts',999,'s','red','cotton',90),
(101,60,'MensWear','T_shirt',999,'m','red','cotton',0);
ALTER TABLE product RENAME COLUMN product_name TO pro_name;
ALTER TABLE product RENAME COLUMN product_Price TO prod_Price;
ALTER TABLE product RENAME COLUMN product_Size TO prod_Size;
ALTER TABLE product RENAME COLUMN product_color TO prod_color;
ALTER TABLE product RENAME COLUMN product_Quantity TO prod_Quantity;

3. SELECT cus_id AS customerId FROM order1 WHERE ord_qty>=1;

SELECT c.cus_name AS CustomerName, o.ord_qty AS Items
FROM customer c INNER JOIN order1 o USING(cus_id)
WHERE c.cus_name=o.ord_qty;

4.
SELECT c.cus_name AS CuatomerNamer, o.ord_qty
FROM customer c,order1 o
WHERE cus_name LIKE 'A%a'
AND o.ord_qty>=1;

SELECT DISTINCT c.cus_name AS CustomerName, o.ord_qty AS Items
FROM customer c , order1 o
WHERE o.ord_qty=c.cus_name
OR ord_qty>=1;

5.
SELECT order1.order_no AS orderNo,order1.purch_amt AS Amount,
customer.cus_name AS CustomerName ,customer.cus_city AS City
FROM order1 ,customer
WHERE order1.cus_id=customer.cus_id
AND order1.purch_amt >10000;

SELECT order1.order_no,order1.purch_amt,
customer.cus_name,customer.cus_city, mycard.ord_status
FROM order1 ,customer , mycard
WHERE order1.cus_id=customer.cus_id
AND order1.purch_amt >10000, mycard.ord_status="Deliveried";

6.CREATE TABLE admin1(adm_id INT ,
adm_passward CHAR(10),
adm_name CHAR(30),
PRIMARY KEY (adm_id));
INSERT INTO admin1(adm_id , adm_passward,adm_name,adm_contact) VALUES (1001, 'aaaa', 'Bakya',2365496),
(1002,'bbbb','lakshmi',23654452),
(1003,'cccc','lakshana',27896523);

ALTER TABLE admin1 ADD COLUMN adm_contact INT (10) AFTER adm_name;