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 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;
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]
============================- DISPLAY DEPARTMENT WISE TOTAL QUANTITY.
- DISPLAY SRNO, NAME, AREA FROM SALES AND EMP TABLE.
- DISPLAY UNIQUE DEPARTMENT FROM SALES.
- DISPLAY DOS, NAME,PRICE WHOSE DOS IN THE YEAR 1990.
- DISPLAY DOS AND DAYNAME WHERE dos is not on sunday
- FIND THE CARTISION PRODUCT OF BOTH TABLE
- FIND THE NO OF UNIQUE PRODUCT FROM SALES TABLE.
- FIND NAME, PRODUCT WISE TOTAL AMOUNT.
- FIND THE NAME, DEPT WHO IS NOT FROM NORTH AREA
- FIND THE NAME, PRICE WHOSE PRICE IS MORE THAN 10000 AND LESS THAN 20000.
- FIND DOS, NAME, PRODNAME WHICH NOT SOLD IN THE MONTH OF JULY.
- DISPLAY NAME, AREA,prod,qty WHO ARE FROM SOUTH AREA AND QTY >1 AND PRODUCT IS NOT mouse.
- FIND NAME, PRODUCT WHERE PRODUCT IS NO HARDDISK AND DEPARTMENT IS MECHANICAL.
- DISPLAY FIRST LETTER OF NAME, PRODUCT AND DEPT FROM SALES.
- DISPLAY LAST LETTER OF NAME, PRODUCT AND DEPT FROM SALES.
- DISPLAY SECOND LETTER OF NAME, PRODUCT AND DEPT FROM SALES.
- DISPLAY ALL SALES PERSON NAME , SRNO WHO IS PRESENT IN EMP TABLE.'
- display name and dept whose name contains 'a' anywhere in name field.
FIND THE OUTPUT OF THE FOLLOWING QUERIES:[B]
- SELECT NAME,PRICE,QTY FROM SALES WHERE QTY > 2 AND PRICE <> 35000.99;
- SELECT A.SRNO, A.NAME, B.AREA FROM SALES A, EMP B WHERE A.SRNO=B.SRNO;
- SELECT NAME,ROUND(PRICE,0) AS PRICE FROM SALES WHERE QTY=2;
- SELECT NAME, PROD, DOS FROM SALES WHERE MONTH(DOS)<>7 AND YEAR(DOS)=1980;
- SELECT COUNT(DISTINCT(DEPT)) FROM SALES WHERE QTY>2;
- SELECT prod,sum(price),avg(price) from sales group by prod;
- select name,prod from sales where prod in('cpu','harddisk');
- select concat(name,'=',prod) from sales where qty<>2;
- select right(name,1), substr(PROD,3,3) from sales
0 Comments