Consider the tables dept and employee alongside. Create the tables accordingly and write SQL Queries for the same :
Employee
+------+--------------+------------+------------+--------+-------+------------+
| eno | name | doj | dob | gender | dcode | salary |
+------+--------------+------------+------------+--------+-------+------------+
| 1001 | george k | 2013-09-02 | 1991-09-01 | male | d01 | 1000000.00 |
| 1002 | ryma sen | 2012-12-15 | 1990-12-15 | female | d03 | 1500000.00 |
| 1003 | mohitesh | 2013-02-03 | 1987-09-04 | male | d05 | 1200000.00 |
| 1004 | manila sahai | 2012-12-09 | 1986-11-14 | female | d01 | 1300000.00 |
| 1005 | r sahay | 2013-11-18 | 1987-03-31 | male | d02 | 1400000.00 |
| 1006 | jaya priya | 2014-06-09 | 1985-06-23 | female | d05 | 1100000.00 |
| 1007 | anil jha | 2014-01-17 | 1984-10-19 | male | d04 | 1400000.00 |
+------+--------------+------------+------------+--------+-------+------------+
Dept
+-------+----------------+----------+
| dcode | department | location |
+-------+----------------+----------+
| d01 | infrastructure | delhi |
| d02 | marketing | delhi |
| d03 | media | mumbai |
| d04 | human resource | mumbai |
| d05 | finance | kolkata |
+-------+----------------+----------+
CREATE DATABASE Lab17;
USE Lab18;
CREATE TABLE DEPT (
dcode CHAR(3) PRIMARY KEY,
department VARCHAR(30),
location VARCHAR(30)
);
INSERT INTO DEPT VALUES
('d01', 'infrastructure', 'delhi'),
('d02', 'marketing', 'delhi'),
('d03', 'media', 'mumbai'),
('d04', 'human resource', 'mumbai'),
('d05', 'finance', 'kolkata');
CREATE TABLE EMPLOYEE (
eno INT PRIMARY KEY,
name VARCHAR(30),
doj DATE,
dob DATE,
gender CHAR(6),
dcode CHAR(3),
salary DECIMAL(12, 2),
CONSTRAINT fk FOREIGN KEY (dcode) REFERENCES DEPT (dcode)
);
INSERT INTO EMPLOYEE VALUES
(1001, 'George K', '2013-09-02', '1991-09-01', 'male', 'd01', 1000000.00),
(1002, 'Ryma Sen', '2012-12-15', '1990-12-15', 'female', 'd03', 1500000.00),
(1003, 'Mohitesh', '2013-02-03', '1987-09-04', 'male', 'd05', 1200000.00),
(1004, 'Manila Sahai', '2012-12-09', '1986-11-14', 'female', 'd01', 1300000.00),
(1005, 'R Sahay', '2013-11-18', '1987-03-31', 'male', 'd02', 1400000.00),
(1006, 'Jaya Priya', '2014-06-09', '1985-06-23', 'female', 'd05', 1100000.00),
(1007, 'Anil Jha', '2014-01-17', '1984-10-19', 'male', 'd04', 1400000.00);
i) Display Eno, Name, Gender, Dob from the Table Employee in ascending order of age.
ii) To display the name of all male employees from the table employee who were born in the year 1987
iii) To display the count of male and female employees with the headings as gender, and count.
iv) To display the eno and name of all employees who were born between ‘ 1987-01-01’ and ‘1991-12-01’ , the oldest first.
v) Display Name and department of each employee.
vi)Display the count of employees and average salary(rounded to nearest integer) in each department.
vii) Display the employee no, name , location and salary of the employees who work at Mumbai or Delhi and have a salary of more than 1300000.
viii) Display each city and the average salary of corresponding employees where the average salary is greater than 1200000.
ix) Increase the salary of female employees by 15% and male employees by 12%
x) Display the details (eno, name, salary) of the employee(s) receiving the highest salary in the company.
xi) Display the department code (dcode) and the total salary paid to employees in that department, but only for departments where the total salary exceeds 2000000.
xii)Display the name and salary of all employees who work in the 'delhi' location.
xiii)Display the gender and average salary for each gender, showing only those groups whose average salary is greater than 1250000.
xiv) Display the names of all employees who belong to either the 'infrastructure' or 'finance' department.
xv) Delete all employees from the EMPLOYEE table who work in the 'media' department.
What is the output of the following queries?
i)Select dcode,count(*) from employee group by dcode having count(*) >1;
ii)Select name, department from employee e, dept d where e.dcode = d.dcode and eno <1003;
iii) Select max(doj),min(dob) from employee;