MySQL

35
There are 2 tables whose field descriptions and the table contents are given
below:

Create these 2 tables and insert the records as given below:

Table Name : EMP

Field Description Of EMP TABLE

Field Name Constraints Type

EMPNO PRIMARY KEY INTEGER(4)
ENAME VARCHAR(10)
JOB VARCHAR(10)
MGR INTEGER(4)
HIREDATE DATE
SAL DECIMAL(7,2)
COMM DECIMAL(7,2)
DEPTNO INTEGER(2)

TABLE : EMP

EMP Table Creation:

CREATE TABLE emp(EMPNO INT(4) PRIMARY KEY, ENAME
VARCHAR(10),JOB VARCHAR(9), MGR INT(4), HIREDATE DATE, SAL
DECIMAL(7,2), COMM DECIMAL(7,2), DEPTNO INT(2));

Adding records into the EMP table:

mysql> INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,'2010-12-

17',5000,800,20);

36
 mysql> INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'2012-02-
10',6000,500,30);
mysql> INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'2015-07-
10',4000,900,30);
mysql> INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7698,'2010-08-
12',8000,NULL,20);
mysql> INSERT INTO EMP VALUES(7934,'JAMES','CLERK',7698,'2011-12-
12',3000,NULL,10);

FIELD DESCRIPTION OF DEPT TABLE

Name Constraints Type

PRIMARY
DEPTNO INTEGER(2)
KEY
DNAME VARCHAR(14)
LOC VARCHAR(13)

TABLE:DEPT

mysql> create table dept(deptno int(2) primary key, dname varchar(14),loc

varchar(13));
mysql> insert into dept values(10,'accounting','new york');

37
mysql> insert into dept values(20,'research','dallas');
mysql> insert into dept values(30,'sales','chicago');
mysql> insert into dept values(40,'operations','boston');

Based on the above tables, write SQL commands for the following:

1. Display all the records having salary greater than 3000 from the table emp.

2. Display empno and ename of all employees .

3. List all unique department numbers from the emp table.

38
4. Display the names of employees having job field other than SALESMAN.

5. Display the names of employees having hiredate above 01/01/2015.

6. Write a query to display ename, job and sal whose salary is greater than or
equal to 3000 and job is MANAGER .

7. Write a query to increase the salary of all employees by 1000 .

39
8. Store 100 to all employees having commission NULL.

9. Change the salary to 5000 and commission to 0 of those employees whose

salary is greater than 3000.

10. Modify the job field to size 50.

11. Add a new field birthdate of date type.

12. Display the minimum and maximum salary with headings MINSAL
&MAXSAL respectively.

40
13. Display the jobs where number of employees is less than 3.

14. Display the employees in descending order of name.

15. Display the name, salary and annual salary with heading
“ANNUALSALARY” of all MANAGERS.

41
16. List the names of the employees of only those employees who have their
employee number as 7934,7369,7499.

17. List the names of employees having four lettered names and ending with D.

18. List the names of employees whose names have ‘L’ in the third place.

19. Delete the records having job field MANAGER.

42
20. Display the names of employees getting salary within the range 5000
and7000.

21. Display the sum and average of the salary of all the employees.

22. Display the name and job of employees in descending order of names.

23. Display the names of the employees along with the department names.

43
24. Display the names of employees who works in Chicago.

25. Display the name and location of employees who works in sales department.

26. Display the sum of the salary of employees in each job.

27. Calculate the no.of employees in each job.

44
28.Display the deptno and no of employees of each department.

29. Display the no of employees of each department if the number of

employees isgreater than 1.

30. Display the department no and average salary of each department.

***********************

45
Table:product
Field Name Type Size Constraints
p_id Varchar 20 Primary key
Pname Varchar 20 Not null
manufacturer Varchar 20 Not null
Price Integer 5

create table product(p_id varchar(20) primary key,pname varchar(20) not

null,manufacturer varchar(20) not null,price integer(5));
Table:client
Field Name Type Size Constraints
c_id Varchar 20 Primary key
cname Varchar 20 Not null
city Varchar 20 Not null
p_id Varchar 20 Refers product
table

create table client(c_id varchar(20) primary key,cname varchar(20) not null,city

varchar(20) not null,p_id varchar(20) references product(p_id));
Contents of product table
p_id pname Manufacturer price
Talcum Powder LAK 40
TP01
FW05 Face Wash ABC 45
BS01 Bath Soap ABC 55
SH06 Shampoo XYZ 120
FW12 Face Wash XYZ 95

insert into product values('TP01','Talcum Powder','LAK',40);

insert into product values('FW05','Face Wash','ABC',45);
insert into product values('BS01','Bath Soap','ABC',55);
insert into product values('SH06','Shampoo','XYZ',120);
insert into product values('FW12','Face Wash','XYZ',95);

46
Contents of client table
c_id cname City p_id
01 Cosmetic Shop Delhi FW05
06 Total Health Mumbai BS01
12 Live Life Delhi SH06
15 Pretty Woman Delhi FW12
16 Dreams Bangalore TP01

insert into client values('01','Cosmetic Shop','Delhi','FW05');

insert into client values('06','Total Health','Mumbai','BS01');
insert into client values('12','Live Life','Delhi','SH06');
insert into client values('15','Pretty Woman','Delhi','FW12');
insert into client values('16','Dreams','Bangalore','TP01');

47
Write MYSQL commands for the following:-
1. Display the details of those clients from Delhi.
Select * from client where city=’Delhi’;

2. Display the manufacturers of Bath Soap.

Select manufacturer from product where pname=’Bath Soap’;

3. Display the names of different products available.

Select distinct pname from product;

48
4. Display the number of manufacturers available.
Select count(distinct manufacturer) from product;

5. Display the different cities of the clients.

Select distinct city from client;

6. Display the manufacturers of Face Wash.

Select manufacturer from product where pname=’Face Wash’;

7. Add a new row with the following details:SH01,Shampoo,LOR,130

Insert into product values(‘SH01’,’Shampoo’,’LOR’,130);

49
8. Increase the price of Talcum powder by 10.
Update product set price=price+10 where pname=’Talcum Powder’;

9. Change the price of Bath Soap to 50.

Update product set price=50 where pname=’Bath Soap’;

10. Delete all Talcum Powder item

Delete from product where pname=’Talcum Powder’;

11. Delete all the products of XYZ manufacturer.

Delete from product where manufacturer=’XYZ’;

12.Display the products supplied by XYZ manufacturer

Select pname from product where manufacturer=’XYZ’;

50
13. Display the product names starting with B.
Select pname from product where pname like ‘B%’;

14.Display the product,manufacturer and price sorted by pname.For products with

same name sort by manufacturer in ascending order.

Select pname,manufacturer,price from product order by pname,manufacturer;

15.Display the product,manufacturer and price sorted by pname. For products with
same name sort by manufacturer in descending order.

Select pname,manufacturer,price from product order by pname,manufacturer desc;

51
16. Display the client names in the descending order of their cities.
Select cname from client order by city desc;

17. Modify the data type of manufacturer to varchar(30)

Alter table product modify manufacturer varchar(30);

18. Add a column Expirydate in the product table.

Alter table product add expirydate date;

19.Delete the column Expiry date.

Alter table product drop expirydate;

52
 20. Display the number of cities of the client table.
Select count(distinct city) from client;

21.Display the highest price of the product.

select max(price) from product;

22. Display the number of different products in the product table.

Select count(distinct pname) from product;

53
23. Display the number of different manufacturers in the product table.
Select count(distinct manufacturer) from product;

24. Display the number of products of each manufacturer.

Select count(pname),manufacturer from product group by manufacturer;

25. Display the clients from each city.

Select cname,city from client group by city;

54
 26. Display the number of clients from each city.
Select count(cname),city from client group by city;

27.Display the minimum and maximum price of each manufacturer

Select min(price),max(price),manufacturer from product group by
manufacturer;

28. Display the number of products of each manufacturer.

Select count(pname),manufacturer from product group by manufacturer;

55
29. Display the highest price of each manufacturer.
Select manufacturer,max(price) from product group by manufacturer;

30. Display the number of clients of each city.

Select count(cname),city from client group by city;

31. Display the product.p_id,product name,client name and price of all products.
Select product.p_id,pname,cname,price from client,product where
product.p_id=client.p_id;

56
 32. Display the client details with their product and price.
Select client.*,pname,price from product,client where
product.p_id=client.p_id;

33. Display the clients of Face Wash.

Select cname from product,client where product.p_id=client.p_id and
pname=’Face Wash’;

34. Display the clients from Mumbai who use Shampoo.

Select cname from product,client where product.p_id=client.p_id and
city=’Mumbai’ and
pname=Shampoo’;

35. Display the name of the client from Delhi who uses Face Wash.
Select cname from product,client where product.p_id=client.p_id and
pname=’Face Wash’ and city=’Delhi’;

57
 36. Display the client name and product name of all clients.
Select cname,pname from product,client where product.p_id=client.p_id;

37. Display all the clients from Bangalore who use Talcum Powder.
Select cname from product,client where product.p_id=client.p_id and
city=’Bangalore’
and pname=’Talcum Powder’;

38. Display the equijoin of the two tables.

Select * from product,client where product.p_id=client.p_id;

58
39.Display the natural join of the two tables.
Select * from client natural join product;

40. Display the Cartesian product of product and client .

Select * from product ,client;

59