structured query language

 RAMESHWAR NAGODE

DBSchoolManagement Practical

 

createtable tblDepartment(Deptid intprimarykey,DeptName varchar(50))

 

insertinto tblDepartment values(1,'BCs');

insertinto tblDepartment values(2,'MCs');

insertinto tblDepartment values(3,'MCA');

 

select*from tblDepartment

 

 

createtable tblTeachers(Teachid int, TName varchar(50),Tsalary money,TPhone varchar(15),JoinD varchar(15),Deptid intforeignkeyreferences tblDepartment(Deptid));

 

 

insertinto tblTeachers values(1,'Sandeep Nagaode',100000,'8888080407','07/02/2016',1)

insertinto tblTeachers values(2,'Gitanjali Nagode',100000,'8888080401','07/02/2017',1)

insertinto tblTeachers values(3,'Kiran Supekar',50000,'775774697','07/02/2018',2)

insertinto tblTeachers values(4,'Sanket Nagode',12000,'888841554407','07/02/2019',3)

insertinto tblTeachers values(5,'Vaibhav Mate',10000,'88825690407','07/02/2020',3)

select*from tblTeachers

Queries

 

--Calculate sum of Departmenty wise salary.

selectSUM(Tsalary)from tblTeachers groupby Deptid

 

--Find max Salary.

selectMAX(Tsalary)as maxsalary from tblTeachers

 

--Find min Salary.

selectMIN(Tsalary)as minsalary from tblTeachers

 

--Count Dept wise Teachers

select Deptid,count(*)as "TotalTeachers" FROM tblTeachers groupby Deptid;

 

 

--order by Dept wise

select*from tblTeachers orderby Deptid

 

--Order by Teachid wise

select*from tblTeachers orderby Teachid

 

--Count total Teachers

selectcount(Teachid)as Totalteachers from tblTeachers

 

--Apply upper case to Department column

selectUPPER(DeptName)as DeptName from tblDepartment

--Apply lower case to TName column

selectLOWER(TName)as TName from tblTeachers

 

--Search Teacher whose name starts from 's'.

select*from tblTeachers where TName like's%'

 

--Search teacher whose name has 'a'char in it.

select*from tblTeachers where TName like'%a%'

 

--Get joining date of MCA department teachers

select(JoinD)from tblTeachers where Deptid=3