GRADE 12 : PRACTICAL 14

 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');

PRACTICE : 

1. Display the unique departments from Teacher Table.
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);

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;