MySQL Practical

Create an Emp & Dept table and insert data. Implement the following SQL commands on the

Emp or Dept table:

o ALTER table to add new attributes / modify data type / drop attribute

o UPDATE table to modify data

o ORDER By to display data in ascending / descending order

o DELETE to remove tuple(s)

o GROUP BY and find the min, max, sum, count and average

Create a new database as 'emp_db':

create database emp_db;

Switch to the newly created database 'emp_db':

use emp_db;

Create 'Emp' & 'Dept' tables as follows:

Emp table:

create table emp(

emp_id int primary key,

emp_name varchar(30),

emp_sal decimal(10,2),

dept_id varchar(10),

foreign key  (dept_id) references dept(dept_id)

);

Dept table:

create table dept(

dept_id varchar(10) primary key,

dept_name varchar(30),

dept_loc varchar(30)

);

Optional :to view structure of above tables: 

desc emp;

or 

desc dept;

ALTER table to add new attributes / modify data type / drop attribute:

If you need to modify table structure then use Alter command as follows:

To add new column in Emp table:

alter table emp

add (emp_desig varchar(30));

To modify datatype of any column:

alter table emp

modify emp_desig varchar(20);

To drop any of columns from Emp table:

alter table emp

drop emp_desig;

Insert 5-6 records in each table as follows:

insert into dept values('D001', 'IT', 'Andheri');

insert into dept values('D002', 'Marketing', 'Virar');

insert into dept values('D003', 'Sales', 'Mumbai' );

insert into dept values('D004', 'Accounts', 'Dahanu' );

insert into emp values(1, 'Rahul', 23000,'D002');

insert into emp values(2, 'Riya', 28000,'D001');

insert into emp values(3, 'Suraj', 25000,'D001');

insert into emp values(4, 'Om', 22000,'D002');

insert into emp values(5, 'Priyanka', 20000,'D004');

insert into emp values(6, 'Reena', 30000,'D003');

insert into emp values(7, 'Soham', 35000,'D003');

This way you can insert more records in above tables.

UPDATE table to modify data:

Query to update employee salary by 50000 whose id is 1.

update emp

set emp_sal=50000

where emp_id=1;

Q.  Increase the salary by 5% of personals who are working in Marketing (D002) department.

UPDATE emp SET emp_sal=emp_sal*0.5 WHERE dept_id='D002'; 

To display all the records from the 'emp' table use * or else specify column names to display.

select * from emp;

OR

select emp_id, emp_name from emp;

Q. Display Emp Name and Total Salary (sum of Salary and Allowance=2040) of all employees. The column heading ‘Total Salary’ should also be displayed. 

SELECT emp_name, emp_sal+2040 AS "Total Salary" FROM emp; 

ORDER By to display data in ascending / descending order:

select * from emp

order by emp_name desc;

select * from emp

order by emp_name;     [here by default order by clause is having ascending order(asc)]

Q. Display the employee name, department id, and employee salary in descending order of employee salary. 

SELECT emp_name, dept_id, emp|_sal from emp ORDER BY emp_sal DESC;

Group BY clause:

select dept_id,count(*) "No. of employees" from emp 

group by dept_id;

Aggregate Functions: (min(), max(), avg(), sum(), count(*), count(column))

select MAX(emp_sal) from emp;

select min(emp_sal) from emp;

select avg(emp_sal) from emp;

select sum(emp_sal) from emp;

select count(*) from dept;

select count(dept_name) from dept;

Q. Display average salary of employees who are working in IT department.