TECHONESPOT
All Tech at one spot
Write SQL commands for the following table Teacher.
How to Create Database ?
Solution :-
Create database School;
Output :-
How to Access Database?
Solution :-
use School
Output :-
How to Create table Teacher.
Solution :-
Create table Teacher
(
TID varchar(10) primary key not null,
Name varchar(100) not null,
age int not null,
dept varchar(50)not null,
DOJ date not null,
sal int not null,
gender varchar(1) not null);
Output :-
Describe the Structure of the Table.
Solution :-
desc Teacher
Output :-
Insert the Data into the Table Teacher.
Solution :-
insert into Teacher values(‘T118′,’Sneha’,40,”Computer”,”2010-01-10″,23000,’F’);
insert into Teacher values(‘T107′,’Yash’,37,”History”,”2008-03-24″,20000,’M’);
insert into Teacher values(‘T105′,’Mohit’,46,”Maths”,”2006-12-12″,12000,’M’);
insert into Teacher values(‘T110′,’Hari’,35,”History”,”2010-07-01″,25000,’M’);
insert into Teacher values(‘T101′,’Sudha’,42,”Maths”,”2004-09-05″,40000,’F’);
insert into Teacher values(‘T121′,’Rita’,38,”Physics”,”2011-04-01″,30000,’F’);
Output :-
Queries
1.To Show information about the teachers of the history department.
Solution :-
Select * from Teacher where dept =’History’;
Output :-
2. To list the names of teachers earning a salary between 20000 and 30000.
Solution :-
Select * from Teacher where sal between 20000 and 30000;
Output :-
3. To Count the number of male Teachers.
Solution :-
Select count(gender) from Teacher where gender=’M’;
Output :-
4. Display Gender wise total number of Teacher.
Solution :-
Select gender,count(gender) from Teacher group by gender;
Output :-
5. To list the name and age of teachers of female teachers in descending order of date of join.
Solution :-
Select Name,age from teacher where gender=’F’ order by DOJ DESC;
Output :-
6.Increase the salary by 10% for Maths departments.
Solution :-
update teacher set sal = sal+(10*sal)/100 where dept=’Maths’;
Output :-
7. To delete the record of teacher Rita.
Solution :-
Delete from teacher where name =’Rita’;
Output :-
