MySQL Questions
Create the table EMPLOY using the following schema and populate the table using
the given data:
EMPLOY
CREATE TABLE EMPLOY(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(15) NOT NULL,
JOB VARCHAR(15),
BIRTH_DATE DATE,
GENDER ENUM('MALE','FEMALE'),
SALARY FLOAT(10,2),
COMMISSION FLOAT(10,2),
DNAME VARCHAR(10)
);
INSERT INTO EMPLOY VALUES(1000, 'RAMKRISHNA', 'CLERK' ,'1966-01-11', 'MALE',
17800, NULL ,'SALES');
INSERT INTO EMPLOY VALUES(1020, 'JAMES', 'SALESMAN' ,'1976-10-15', 'MALE',
12000, 3000, 'SALES');
INSERT INTO EMPLOY VALUES(1811, 'JANI', 'ANALYST', '2000-10-01', 'FEMALE',
25000, NULL, 'RESEARCH');
INSERT INTO EMPLOY VALUES(1771, 'BOBAN', 'MANAGER', '1975-08-15', 'MALE',
27000, NULL, 'ADMIN');
INSERT INTO EMPLOY VALUES(1111, 'ANOOP', 'PRESIDENT', '1976-05-18', 'MALE',
30000, NULL, 'ADMIN');
INSERT INTO EMPLOY VALUES(1207, 'SHIYAS', 'CLERK', '2000-01-11', 'MALE',
21000, NULL, 'ACCOUNTS');
INSERT INTO EMPLOY VALUES(1547, 'RESMI', 'MANAGER', '1985-04-20', 'FEMALE',
17500, 5000, 'SALES');
INSERT INTO EMPLOY VALUES(1540, 'RAHUL', 'SALESMAN', '2001-07-28', 'MALE',
15000, 4100, 'SALES');
INSERT INTO EMPLOY VALUES(1897, 'SUMESH', 'SALESMAN', '1994-06-17', 'MALE',
19750, 2500, 'SALES');
INSERT INTO EMPLOY VALUES(1340, 'BINU', 'CLERK', '1989-09-05', 'MALE', 20800,
NULL, 'RESEARCH');
Write SQL queries to perform the following tasks:
1. Display the name and job of all employ who are in Sales department.
SELECT ENAME, JOB FROM EMPLOY WHERE DNAME=’SALES’;
2. Display the name, job and salary of all employ whose salary in the range
20000 – 30000
SELECT ENAME, JOB, SALARY FROM EMPLOY WHERE SALARY BETWEEN
20000 AND 30000;
3. Display name, department name and job of all salesman who are in Sales
department.
SELECT ENAME, DNAME, JOB FROM EMPLOY WHERE JOB=’SALESMAN’ AND
DNAME=’SALES’;
4. Display total number of Male and Female employ.
SELECT GENDER, COUNT(*) FROM EMPLOY GROUP BY GENDER;
5. Display total salary given to each department.
SELECT DNAME, SUM(SALARY) FROM EMPLOY GROUP BY DNAME;
6. Display employ number, name, job and birth date of the youngest employ.
SELECT EMPNO, ENAME, JOB, BIRTH_DATE FROM EMPLOY WHERE
BIRTH_DATE=(SELECT MAX(BIRTH_DATE) FROM EMPLOY);
7. Display employ number, name, job and department of all employ who are not
getting commission.
SELECT EMPNO, ENAME, JOB, DNAME FROM EMPLOY WHERE COMMISSION
IS NULL;
8. Display the employ number, name, job and department of all employ who are
neither in Admin nor in Research departments.
SELECT EMPNO,ENAME,JOB,DNAME FROM EMPLOY WHERE DNAME NOT
IN(‘ADMIN’,’RESEARCH’);
9. Display name, job of all male employ whose salary is less than 20000.
SELECT ENAME,JOB FROM EMPLOY WHERE GENDER=’MALE’ AND
SALARY<20000;
10. Display name and job of all employ whose job title contains the word ‘MAN’ in
it.
SELECT ENAME, JOB FROM EMPLOY WHERE JOB LIKE ‘%MAN%’;
11. Display name job and Net salary(Salary+Commission) of all employ who are
getting commission.
SELECT ENAME, JOB, SALARY+COMMISSION AS ‘NET SALARY’ FROM
EMPLOY WHERE COMMISSION IS NOT NULL;
12. Display maximum and minimum salary given to each department.
SELECT DNAME, MAX(SALARY), MIN(SALARY) FROM EMPLOY GROUP BY
DNAME;
13. Display name and age of all employ.
SELECT ENAME, YEAR(NOW()) – YEAR(BIRTH_DATE) AS AGE FROM EMPLOY;
14. Display the name and salary of all employ in descending order of their salary.
SELECT ENAME, SALARY FROM EMPLOY ORDER BY SALARY DESC;
15. Display all department names and count of employ in each department that
have more than 1 employ.
SELECT DNAME, COUNT(*) FROM EMPLOY GROUP BY DNAME HAVING
COUNT(*)>1;