PracticeFolder/demp.sql at main · SwifSys/PracticeFolder · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
CREATE TABLE CLIENT_MASTER (
	ClientNo VARCHAR(7),
	Name VARCHAR(15),
	City VARCHAR(10),
	Pincode INTEGER,
	State VARCHAR(15),
	BalDue INTEGER
);
CREATE TABLE PRODUCT_MASTER (
	ProductNo VARCHAR(7),
	Description VARCHAR(20),
	ProfitPercent INTEGER,
	UnitMeasure VARCHAR(6),
	QtyOnHand INTEGER,
	ReorderLvl INTEGER,
	SellPrice INTEGER,
	CostPrice INTEGER
);
CREATE TABLE SALESMAN_MASTER (
	SALESMANNO VARCHAR(8),
	NAME VARCHAR(7),
    ADDRESS1 VARCHAR(5),
    ADDRESS2 VARCHAR(10),
    CITY VARCHAR(10),
    PINCODE INTEGER,
    STATE VARCHAR(15)
);
CREATE TABLE SALES_ORDER_DETAILS(
	SALESMANNO VARCHAR(8),
	SALAMT INTEGER,
    TGTTOGET INTEGER,
    YTDSALES INTEGER,
    REMARKS VARCHAR(10)
);
INSERT INTO CLIENT_MASTER  VALUES
		("C00001", "Ivan Bayross", "Mumbai", 400054, "Maharashtra", 15000),
		("C00002", "Mamta Muzumdar", "Madras", 780001, "Tamil Nadu", 0),
		("C00003", "Chhaya Bankar", "Mumbai", 400057, "Maharashtra", 5000),
		("C00004", "Ashwini Joshi", "Bangalore", 560001, "Karnataka", 0),
		("C00005", "Hansel Colaco", "Mumbai", 400060, "Maharashtra", 2000),
		("C00006", "Deepak Sharma", "Mangalore", 560050, "Karnataka", 0);

Insert into PRODUCT_MASTER values
		('P00001','T-Shirts',5,'Piece',200,50,350,250),
		('P0345','Shirts',6,'Piece',150,50,500,350),
		('P06734','Cotton Jeans',5,'Piece',100,20,650,450),
		('P07865','Jeans',5,'Piece',100,20,750,500),
		('P07868','Trousers',2,'Piece',150,50,850,550),
		('P07885','Pull Overs',2.5,'Piece',80,30,700,450),
		('P07965','Denim Shirts',4,'Piece',100,40,350,250),
		('P07975','Lycra Tops',5,'Piece',70,30,300,175),
		('P08865','Skirts',5,'Piece',75,30,450,300);

INSERT INTO SALESMAN_MASTER VALUES
		("S00001","AMAN","A/14",'WORLI',"MUMBAI",400002,"MAHARASTRA"),
        ("S00002","OMKAR","65",'NARIMAN',"MUMBAI",400001,"MAHARASTRA"),
        ("S00003","RAJ","P-7",'BANDRA',"MUMBAI",400032,"MAHARASTRA"),
        ("S00004","ASHISH","A/5",'JUHU',"MUMBAI",400044,"MAHARASTRA");
INSERT INTO SALES_ORDER_DETAILS VALUE
		("S00001",3000,100,50,"GOOD"),
        ("S00002",3000,200,100,"GOOD"),
        ("S00003",3000,200,100,"GOOD"),
        ("S00004",3500,200,150,"GOOD");


/*3*/
SELECT NAME FROM CLIENT_MASTER;
SELECT * FROM CLIENT_MASTER;
SELECT NAME,CITY,STATE FROM CLIENT_MASTER;
SELECT DESCRIPTION FROM PRODUCT_MASTER;
SELECT NAME FROM CLIENT_MASTER WHERE CITY="MUMBAI";
SELECT SALESMAN_MASTER.NAME FROM SALESMAN_MASTER INNER JOIN SALES_ORDER_DETAILS ON SALESMAN_MASTER.SALESMANNO = SALES_ORDER_DETAILS.SALESMANNO WHERE SALES_ORDER_DETAILS.SALAMT = 3000;
/*4*/
UPDATE CLIENT_MASTER set City = 'Bangalore' where ClientNo = 'C00005';
UPDATE CLIENT_MASTER set BailDue = 1000 where ClientNo = 'C00001';
UPDATE PRODUCT_MASTER set CostPrice = 950 where ProductName = 'Trousers';
UPDATE SALESMAN_MASTER set City = 'Pune';


SELECT NAME FROM CLIENT_MASTER WHERE NAME LIKE "_a%";
SELECT * FROM CLIENT_MASTER WHERE CITY LIKE "M%";
SELECT * FROM CLIENT_MASTER WHERE CITY IN ("Bangalore", "Mangalore");
SELECT * FROM CLIENT_MASTER WHERE BALDUE > 10000;
SELECT * FROM PRODUCT_MASTER WHERE SELLPRICE > 500 AND SELLPRICE <= 750;
SELECT * FROM CLIENT_MASTER where CLIENTNO = 'C00001' or CLIENTNO = 'C00002';
SELECT PRODUCTNO, DESCRIPTION, SELLPRICE, SELLPRICE*0.15 as NEW_PRICE FROM PRODUCT_MASTER WHERE SELLPRICE > 500;
SELECT NAME, CITY, STATE FROM CLIENT_MASTER WHERE STATE != "Maharashtra";
SELECT count(*) FROM SALES_ORDER_DETAILS;
SELECT avg(SELLPRICE) FROM PRODUCT_MASTER;
SELECT max(SELLPRICE) AS MAX_PRICE, min(SELLPRICE) AS MIN_PRICE FROM PRODUCT_MASTER;
SELECT count(*) FROM PRODUCT_MASTER WHERE SELLPRICE <= 500;
SELECT * FROM PRODUCT_MASTER WHERE QTYONHAND < REORDERLVL;