Create the below tables :
DEPARTMENT :
TEACHER:
(Include the following constraints :
> Check that 20<=age<=70
> Sex should take only M and F values)
SOLUTION
CREATE TABLE department (
DeptName VARCHAR(20) PRIMARY KEY,
HOD VARCHAR(30),
RoomNo VARCHAR(10),
Budget NUMERIC(7)
);
INSERT INTO department VALUES
('Computer', 'Dr. Sen', 'C101', 500000),
('History', 'Dr. Malhotra', 'H202', 300000),
('Math', 'Dr. Iyer', 'M303', 400000);
CREATE TABLE teacher (
No INT PRIMARY KEY ,
Name VARCHAR(20) NOT NULL,
Age NUMERIC(2) CHECK (Age BETWEEN 20 AND 70),
Department VARCHAR(20),
DOJ DATE,
Salary FLOAT,
Sex VARCHAR(1) CHECK (Sex IN ('M', 'F')),
FOREIGN KEY (Department) REFERENCES department(DeptName)
);
INSERT INTO teacher VALUES
(111,'Jugal', 34, 'Computer', '1997-01-10', 12000, 'M'),
(222,'Sharmila', 31, 'History', '1998-03-24', 20000, 'F'),
(333,'Sandeep', 32, 'Math', '1996-12-12', 30000, 'M'),
(444,'Sangeeta', 35, 'History', '1999-07-01', 40000, 'F'),
(555,'Rakesh', 42, 'Math', '1997-09-05', 25000, 'M'),
(666,'Shyam', 50, NULL, '1998-06-27', 30000, 'M'),
(777,'Shiv Om', 44, 'Computer', '1997-02-25', 21000, 'M'),
(888,'Shalaka', 33, 'Math', '1997-07-31', 20000, 'F');
DeptName VARCHAR(20) PRIMARY KEY,
HOD VARCHAR(30),
RoomNo VARCHAR(10),
Budget NUMERIC(7)
);
INSERT INTO department VALUES
('Computer', 'Dr. Sen', 'C101', 500000),
('History', 'Dr. Malhotra', 'H202', 300000),
('Math', 'Dr. Iyer', 'M303', 400000);
CREATE TABLE teacher (
No INT PRIMARY KEY ,
Name VARCHAR(20) NOT NULL,
Age NUMERIC(2) CHECK (Age BETWEEN 20 AND 70),
Department VARCHAR(20),
DOJ DATE,
Salary FLOAT,
Sex VARCHAR(1) CHECK (Sex IN ('M', 'F')),
FOREIGN KEY (Department) REFERENCES department(DeptName)
);
INSERT INTO teacher VALUES
(111,'Jugal', 34, 'Computer', '1997-01-10', 12000, 'M'),
(222,'Sharmila', 31, 'History', '1998-03-24', 20000, 'F'),
(333,'Sandeep', 32, 'Math', '1996-12-12', 30000, 'M'),
(444,'Sangeeta', 35, 'History', '1999-07-01', 40000, 'F'),
(555,'Rakesh', 42, 'Math', '1997-09-05', 25000, 'M'),
(666,'Shyam', 50, NULL, '1998-06-27', 30000, 'M'),
(777,'Shiv Om', 44, 'Computer', '1997-02-25', 21000, 'M'),
(888,'Shalaka', 33, 'Math', '1997-07-31', 20000, 'F');
PRACTICE :
1. Display the unique departments from Teacher Table.
SELECT DISTINCT Department
SELECT DISTINCT Department
FROM teacher
WHERE Department IS NOT NULL;
2. Display the RECORD COUNT , DEPARTMENT COUNT(with repeats but without NULL) in teacher table;
SELECT COUNT(*) AS RECORD_COUNT,COUNT(Department) AS DEPARTMENT_COUNT
FROM teacher;
3.Display the number of teachers in each department.
SELECT Department, COUNT(*) AS Num_Teachers
FROM teacher
WHERE Department IS NOT NULL
GROUP BY Department;
4.Find the average salary of teachers in each department in increasing order of their average salaries.
SELECT Department, AVG(Salary) AS Avg_Salary
FROM teacher
WHERE Department IS NOT NULL
GROUP BY Department
ORDER BY AVG(Salary);
5.Show the names of departments having more than 2 teachers.
SELECT Department, COUNT(*) AS Teacher_Count
FROM teacher
WHERE Department IS NOT NULL
GROUP BY Department
HAVING COUNT(*) > 2;
6.Show department name, budget, and number of teachers in each department :
SELECT DeptName,Budget,COUNT(No) AS Teacher_Count
FROM department JOIN teacher ON DeptName = Department
GROUP BY DeptName,Budget;
or
SELECT DeptName,Budget,COUNT(No) AS Teacher_Count
FROM department , teacher WHERE DeptName = Department
GROUP BY DeptName,Budget;
7.Display the average salary per sex, in descending order of the average salary(Rounded to nearest hundreds)
SELECT Sex, round(avg(salary),-2) as Avg_Sal
FROM teacher
GROUP BY Sex
ORDER BY Avg_Sal desc;
8.Display the Name, Deparment and DOJ of the earliest joining teacher
SELECT Department, Name, DOJ
FROM teacher
WHERE DOJ = (
SELECT MIN(DOJ)
FROM teacher);
SELECT Department, Name, DOJ
FROM teacher
WHERE DOJ = (
SELECT MIN(DOJ)
FROM teacher);
9.Increase the salary of all teachers in the 'Math' department by 10%
UPDATE teacher
SET Salary = Salary * 1.10
WHERE Department = 'Math';
10.Remove all teachers who are not assigned to any department.
DELETE FROM teacher
WHERE Department IS NULL;