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 :
List all teacher names and their departments.
select name,department from teacher;
Display all female teachers.
Select name from teacher where sex='f';
Find teachers who joined before 1998.
Select * from teacher where doj < '1998-01-01';
Show names of teachers with salary above 25,000.
select name,salary from teacher where salary>25000;
List all teachers who do not have a department.
select * from teacher where department is null;
Display the non computer teachers.
select * from teacher where department != 'computer' or department is NULL;
Display the Count of all teachers teachers.
select count(name) as TeacherCount from teacher;
Display the total salary of all teachers.
select sum(Salary) as TotalSalary from teacher;
Display the maximum age among the teachers.
select max(age) as MaxAge from teacher;
Display the earliest DOJ.
select min(doj) as EARLIESTDOJ from teacher;
Display teachers sorted by salary in descending order.
select * from teacher order by salary desc;
List teacher names and their ages who are above 40.
select name,age from teacher where age>40;
Find the average salary of all teachers.
select avg(salary) as AVERAGE_SALARY from teacher ;
Find the average salary of all male teachers.
select avg(salary) from teacher where sex='m';
Find the youngest teacher.
select * from teacher order by age limit 1;
select * from teacher where age = (select min(age) from teacher);
Find the teacher with the maximum salary.
select * from teacher where salary = (select max(salary) from teacher);
List all departments and their HODs.
select deptname,hod from department;
Display departments with a budget over ?4,00,000.
select * from department where budget>400000;
Show department names and room numbers.
select deptname,roomno from department
Find the department with the lowest budget.
select deptname,budget from department
where budget= (select min(budget) from department);
Display the total budget of all departments.
select sum(budget) from department;
List departments in alphabetical order.
select deptname from department order by deptname;
List names that start with s
select name from teacher where name like 's%';
List names that contain and a or an e
select name from teacher where name like '%a%' or '%e%';
List names that contain an a and an e
select name from teacher where name like '%a%' and '%e%';
List names that contain 2 a's
select name from teacher where name like '%a%a%';
List names that contain 'sh' in their name
select name from teacher where name like '%sh%';
List names that have 3rd letter as 'a'
select name from teacher where name like '__a%';
List names that have exactly one letter as 'a'
select name from teacher where name like '%a%' and name not like '%a%a%' ;
List the 8 letter names that end with a.
select name from teacher where name like '_ _ _ _ _ _ _ a';
Display the average salary of teacher :
select avg(salary) from teacher;
Display the department name and the corresponding average salaries:
select department,avg(salary) from teacher group by department;
Display the non-null department name and the corresponding average salaries:
select department,avg(salary)
from teacher
where department is not null
group by department;
Display the non-null department name and the corresponding average salaries, in increasing order of their average salaries.
select department,avg(salary) as AvgSal
from teacher
where department is not null
group by department
order by avgsal;
Display the average salary per sex, in descending order of the average salary
select sex, avg(salary)
from teacher
group by sex
order by avg(salary) desc;
