MYSQL PRACTICAL QUESTION- SOLVE THE QUERIES

drop database gems;
create database gems;
use gems;
create table sales(srno int,dos date,dept varchar(20),name varchar(20),prod varchar(20),qty int,price decimal(10,2));
INSERT INTO  SALES VALUES(1,'1912-12-12', 'EXCISE', 'RAJnish', 'mouse', 28, 12400.99);
INSERT INTO  SALES VALUES(2,'2012-12-12', 'custom', 'jayESH', 'keyboard', 28, 120.99);
INSERT INTO  SALES VALUES(3, '1980-12-12', 'Electrical', 'RAJ', 'keyboard', 2, 35000.99);
INSERT INTO  SALES VALUES(4, '1990-06-12', 'Mechanical', 'Jainam', 'mouse', 2, 25000.99);
INSERT INTO  SALES VALUES(5, '1981-08-12', 'Electrical', 'Pravin', 'mobile', 2, 15000.99);
INSERT INTO  SALES VALUES(6, '1980-07-12', 'Mechanical', 'Sandeep', 'harddisk', 2, 12000.10);
INSERT INTO  SALES VALUES(7, '1985-12-12', 'Mechanical', 'Madhav', 'floppy', 2, 11200.99);
INSERT INTO  SALES VALUES(8, '1980-07-12', 'Electrical', 'Madhav', 'keyboard', 2, 32000.99);
INSERT INTO  SALES VALUES(9, '1999-12-12', 'custom', 'Rohan', 'cpu', 2, 43200.99);
INSERT INTO  SALES VALUES(10, '1999-07-12', 'Civil', 'Dipak', 'cpu', 2, 89800.99);
INSERT INTO  SALES VALUES(11, '1980-12-12', 'Excies', 'Diti', 'cpu', 2, 89000.99);
INSERT INTO  SALES VALUES(12, '1999-12-12', 'Electrical', 'Rohan', 'cpu', 2, 43200.99);

INSERT INTO  SALES VALUES(13, '1999-12-12', 'Electrical', 'Rohan', 'cpu', 2, 43200.99);

CREATE TABLE EMP(SRNO INT, AREA VARCHAR(20));
INSERT INTO EMP VALUES(1,'NORTH');
INSERT INTO EMP VALUES(4,'SOUTH');
INSERT INTO EMP VALUES(3,'SOUTH');
INSERT INTO EMP VALUES(6,'SOUTH');
INSERT INTO EMP VALUES(7,'NORTH');
SELECT * FROM SALES;
SELECT * FROM EMP;

SOLVED THE FOLLOWING QUERIES[A]

============================

1. DISPLAY DEPARTMENT WISE TOTAL QUANTITY.
2. DISPLAY SRNO, NAME, AREA FROM SALES AND EMP TABLE.
3. DISPLAY UNIQUE DEPARTMENT FROM SALES.
4. DISPLAY DOS, NAME,PRICE WHOSE DOS IN THE YEAR 1990.
5. DISPLAY  DOS  AND DAYNAME WHERE dos is not on sunday
6. FIND THE CARTISION PRODUCT OF BOTH TABLE
7. FIND THE NO OF UNIQUE PRODUCT FROM SALES TABLE.
8. FIND NAME, PRODUCT WISE TOTAL AMOUNT.
9. FIND THE NAME, DEPT  WHO IS NOT FROM NORTH AREA
10. FIND THE NAME, PRICE WHOSE PRICE IS MORE THAN 10000 AND LESS THAN 20000.
11. FIND DOS, NAME, PRODNAME WHICH NOT SOLD IN THE MONTH OF JULY.
12. DISPLAY NAME, AREA,prod,qty WHO ARE FROM SOUTH AREA AND QTY >1 AND PRODUCT IS NOT mouse.
13. FIND NAME, PRODUCT WHERE PRODUCT IS NO HARDDISK AND DEPARTMENT IS MECHANICAL.
14. DISPLAY FIRST LETTER OF NAME, PRODUCT AND DEPT FROM SALES.
15. DISPLAY LAST LETTER OF NAME, PRODUCT AND DEPT FROM SALES.
16. DISPLAY SECOND LETTER OF NAME, PRODUCT AND DEPT FROM SALES.
17. DISPLAY ALL SALES PERSON NAME , SRNO WHO IS PRESENT IN EMP TABLE.'
18. display name and dept whose name contains 'a' anywhere in name field.

FIND THE  OUTPUT OF THE FOLLOWING QUERIES:[B]

1. SELECT NAME,PRICE,QTY FROM SALES WHERE QTY > 2 AND PRICE <> 35000.99;
2. SELECT A.SRNO, A.NAME, B.AREA FROM SALES A, EMP B WHERE A.SRNO=B.SRNO;
3. SELECT NAME,ROUND(PRICE,0) AS PRICE FROM SALES WHERE QTY=2;
4. SELECT NAME, PROD, DOS FROM SALES WHERE MONTH(DOS)<>7 AND YEAR(DOS)=1980;
5. SELECT COUNT(DISTINCT(DEPT)) FROM SALES WHERE QTY>2;
6. SELECT prod,sum(price),avg(price) from sales group by prod;
7. select name,prod from sales where prod in('cpu','harddisk');
8. select concat(name,'=',prod) from sales where qty<>2;
9. select right(name,1), substr(PROD,3,3) from sales