Practical 6

1 SELECT cname,city,rating , 'high rating' AS " " FROM customer_aj WHERE rating>=200 UNION
SELECT cname,city,rating , 'low rating' AS " " FROM customer_aj WHERE rating<200;

CNAME   CITY    RATING
Chinmay Surat   300 high rating
Chirag  London  100 low rating
Gita    Rome    200 high rating
Govind  Bombay  300 high rating
Harsh   London  100 low rating
Lalit   Surat   200 high rating
Pratik  Rome    100 low rating

2 SELECT snum,sname, 'salesman from ' AS " " FROM salesman_aj WHERE snum IN (
SELECT snum FROM orders_aj GROUP BY snum HAVING COUNT(onum)>1 ) UNION
SELECT cnum,cname, 'customer from ' AS " " FROM customer_aj WHERE cnum IN (
SELECT cnum FROM orders_aj GROUP BY cnum HAVING COUNT(onum)>1 );

SNUM    SNAME
1001    Piyush  salesman FROM
1002    Sejal   salesman FROM
1007    Rajesh  salesman FROM
2004    Govind  customer FROM
2006    Chirag  customer FROM
2008    Chinmay customer FROM

4 INSERT INTO salesman_aj VALUES(1005,'Rakesh',NULL,14);

1 ROW(s) inserted.

5 INSERT INTO customer_aj (city,cname,cnum) VALUES('London','Pratik',2005);

1 ROW(s) inserted.

6 CREATE TABLE london_staff AS SELECT * FROM salesman_aj WHERE 1=0;

TABLE created

7 INSERT INTO london_staff SELECT * FROM salesman_aj WHERE city='London';

2 ROW(s) inserted.

8 CREATE TABLE daytotals (
tdate DATE,
total INT);

INSERT INTO daytotals SELECT odate,SUM(amount) FROM orders_aj GROUP BY odate;

TABLE created

4 ROW(s) inserted.

9 CREATE TABLE multicust AS SELECT * FROM salesman_aj WHERE 1=0;

TRUNCATE TABLE salesman_aj;

TABLE created

TABLE trucated

-- nhi aya
10 INSERT INTO multicust SELECT * FROM salesman_aj;










SELECT snum,snum,'salesman' AS 'from' FROM salesman_aj WHERE snum IN
(SELECT snum FROM orders_aj groub BY SUM HAVING COUNT (onum)>1) ORDER BY aesc;


CREATE TABLE london4365 LIKE salesman_aj;

-- creating new table as same schema as old table
CREATE TABLE london_staff AS SELECT * FROM salesman_aj WHERE 1=0;

-- inserting the values in tale from old one
INSERT INTO london_staff SELECT * FROM salesman_aj WHERE city='London';

SELECT * FROM  salesman_aj;