MYSQL
1. Create a table EMPLOYEE with constraints
Step-1 Create a database:
CREATE DATABASE Bank;
Step-2 Display the databases
SHOW DATABASES;
Step-3: Enter into database
Use Bank;
Step-4: CREATE THE TABLE EMPLOYEE
CREATE TABLE EMPLOYEE(Ecode int primary key,Ename varchar(20) NOT NULL,
Dept varchar(15),City varchar(15), sex char(1), DOB date, Salary float(12,2));
2. Insert data into the table
INSERT INTO EMPLOYEE
VALUES(1001,"Atul","Production","Vadodara","M","1992-10-23",23000.50);
3. Add a new column in a table.
ALTER TABLE EMPLOYEE ADD address varchar(50);
4. Change the data-type and size of an existing column
ALTER TABLE EMPLOYEE MODIFY city char(30);
5. Write SQL queries using SELECT, FROM, WHERE clause based on EMPLOYEE
table
1. List the name of female employees in EMPLOYEE table.
SELECT Ename FROM EMPLOYEE WHERE sex=’F’;
2. Display the name and department of those employees who work in surat
and salary is greater than 25000.
SELECT Ename, Dept FROM EMPLOYEE WHERE city=’surat’ and salary > 25000;
3. Display the name of those female employees who work in Mumbai.
SELECT Ename FROM EMPLOYEE WHERE sex=’F’ and city=’Mumbai’;
4. Display the name of those employees whose department is marketing or RND.
SELECT Ename FROM EMPLOYEE WHERE Dept=’marketing’ OR Dept=’RND’;
5. List the name of employees who are not males.
SELECT Ename, Sex FROM EMPLOYEE WHERE sex!=’M’;
6. Queries using DISTINCT, BETWEEN, IN, LIKE, IS NULL, ORDER BY, GROUP BY,
HAVING
A. Display the name of departments. Each department should be displayed once.
SELECT DISTINCT(Dept) FROM EMPLOYEE;
B. Find the name and salary of those employees whose salary is between 35000
and 40000.
SELECT Ename, salary FROM EMPLOYEE WHERE salary BETWEEN 35000 and
40000;
C. Find the name of those employees who live in guwahati, surat or jaipur city.
SELECT Ename, city FROM EMPLOYEE WHERE city
IN(‘Guwahati’,’Surat’,’Jaipur’);
D. Display the name of those employees whose name starts with ‘M’.
SELECT Ename FROM EMPLOYEE WHERE Ename LIKE ‘M%’;
E. List the name of employees not assigned to any department.
SELECT Ename FROM EMPLOYEE WHERE Dept IS NULL;
F. Display the list of employees in descending order of employee code.
SELECT * FROM EMPLOYEE ORDER BY ecode DESC;
G. Find the average salary at each department.
SELECT Dept, avg(salary) FROM EMPLOYEE group by Dept;
H. Find maximum salary of each department and display the name of that department which has
maximum salary more than 39000.
SELECT Dept, max(salary) FROM EMPLOYEE group by Dept HAVING
max(salary)>39000;
7. Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ), COUNT( )
a. Find the average salary of the employees in employee table.
SELECT avg(salary) FROM EMPLOYEE;
b. Find the minimum salary of a female employee in EMPLOYEE table.
SELECT Ename, min(salary) FROM EMPLOYEE WHERE sex=’F’;
c. Find the maximum salary of a male employee in EMPLOYEE table.
SELECT Ename, max(salary) FROM EMPLOYEE WHERE sex=’M’;
d. Find the total salary of those employees who work in Guwahati city.
SELECT sum(salary) FROM EMPLOYEE WHERE city=’Guwahati’;
e. Find the number of tuples in the EMPLOYEE relation.
SELECT count(*) FROM EMPLOYEE;
