database lab final

DATABASE lab final

--1

CREATE DATABASE labFinal;

USE labFinal;

CREATE TABLE Department(
dep_id INT PRIMARY KEY,
dep_name VARCHAR(50) NOT NULL,
dep_location VARCHAR(50) NOT NULL,
No_of_Employees INT NOT NULL
);


CREATE TABLE Employee(
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
job_name VARCHAR(50) NOT NULL,
manager_id INT,
Joindate DATE NOT NULL,
salary FLOAT NOT NULL,
commission FLOAT,
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES Department(dep_id)
);


INSERT INTO Department(dep_id,dep_name,dep_location,No_of_Employees)
VALUES(1001, 'FINANCE', 'Dhanmondi-11',9),
(2001,'AUDIT', 'Dhanmondi-32',5),
(3001,'PROGRAMMER','Dhanmondi-32',20),
(4001,'PRODUCTION','Dhanmondi-32',8);

SELECT * FROM Department;

INSERT INTO Employee(emp_id,emp_name,job_name,Joindate,salary,dep_id)
VALUES(68319,'KAYLING','PRESIDENT','2001-11-18',6000.00,1001);

INSERT INTO Employee
VALUES(65646,'Kalam','MANAGER',68319,'2004-04-02',2957.00,1700,2001),
(64989,'LABIB','SALESMAN',66928,'1991-02-20',1700.00,400.00,3001),
(65271,'WADE','SALESMAN',66928,'1991-02-22',1350.00,600.00,3001),
(66564,'MADDEN','SALESMAN',66928,'1991-09-28',1350.00,1500.00,3001),
(68454,'TUCKER','SALESMAN',66928,'1991-09-08',1600.00,0.00,3001),
(69324,'MARKER','CLER',67832,'1992-01-23',1400.00,200.00,1001);

INSERT INTO Employee(emp_id,emp_name,job_name,manager_id,Joindate,salary,dep_id)
VALUES(66928,'BLAZE','MANAGER',68319,'2001-05-01',2750.00,3001),
(67832,'CLARE', 'MANAGER',68319,'2003-06-09',2550.00,1001),
(67858,'SCARLET','ANALYST',65646,'2005-04-19',3100.00,2001),
(69062,'AZAD','ANALYST',65646,'2004-04-02',3100.00,2001),
(63679,'LABU','CLERK',69062,'1990-12-18',900.00,2001),
(68736,'ADNRES','CLERK',67858,'1997-05-23',1200.00,2001),
(69000,'JULIUS','CLERK',66928,'1991-12-03',1050.00,3001);

SELECT * FROM Employee;

-- 2
 SELECT SUM(No_of_Employees)
 AS total_employee
 FROM department;

 -- 3
 SELECT *
 FROM employee
 WHERE(employee.job_name = ( SELECT employee.job_name FROM employee WHERE emp_name= 'AZAD'));

 -- 4
SELECT emp_id,emp_name, job_name
FROM employee
WHERE job_name IN (SELECT job_name FROM employee WHERE emp_name = 'LABU' OR emp_name = 'LABIB');

-- 5
--Incorrect Question

 -- 6
 SELECT *
 FROM employee
 WHERE commission IS NULL;

 -- 7
  SELECT employee.emp_id, employee.emp_name, department.dep_name
  FROM employee LEFT JOIN department ON employee.dep_id= department.dep_id
  WHERE dep_name = 'Finance' OR dep_name = 'Programming' OR dep_name = 'Production';

 -- 8
 SELECT MAX(salary) AS Highest_Salary
 FROM employee
 WHERE job_name='SALESMAN';


 -- 9
 SELECT emp_name
 FROM employee
 WHERE emp_name LIKE '%a%';

 -- 10
 SELECT MAX(commission) AS higher_commission
 FROM employee
 WHERE job_name = 'salesman';

 --11
 SELECT department.dep_name,AVG(salary) AS avg_salary
 FROM employee RIGHT JOIN department ON employee.dep_id=department.dep_id
 GROUP BY department.dep_name;


 -- 12
SELECT Employee.emp_id, Employee.emp_name,D.dep_name
FROM Employee
INNER JOIN Department D ON Employee.dep_id = D.dep_id
WHERE dep_location = 'Dhanmondi-32';