SQL – QUERIES
I. SCHEMAS
Table 1 : STUDIES
PNAME (VARCHAR), SPLACE (VARCHAR), COURSE (VARCHAR), CCOST (NUMBER)
Table 2 : SOFTWARE
PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR), SCOST (NUMBER), DCOST (NUMBER), SOLD (NUMBER)
Table 3 : PROGRAMMER
PNAME (VARCHAR), DOB (DATE), DOJ (DATE), SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHAR), SAL (NUMBER)
LEGEND :
PNAME – Programmer Name, SPLACE – Study Place, CCOST – Course Cost, DEVIN – Developed in, SCOST – Software Cost, DCOST – Development Cost, PROF1 – Proficiency 1
QUERIES :
1Find out the selling cost average for packages developed in Oracle.
Ans: select AVG(SCOST) from SOFTWARE where devin='oracle'
2Display the names, ages and experience of all programmers.
Ans:select pname,year(getdate())-year(dob) age,cast(year(getdate())-year(doj) as varchar) +'.'+ cast(month((getdate())-month(doj)) as varchar)experience from programmer
3Display the names of those who have done the PGDCA course.
Ans: select PNAME from STUDIES where COURSE = 'PGDCA'
4Display the lowest course fee.
Ans:Select MIN(CCOST) from STUDIES
5How many programmers have done the DCA course.
Ans:select count(PNAME) as [Total no of programmers studied DCA course] from STUDIES where COURSE='DCA'
6Display the details of software developed by Rakesh.
Ans: select TITLE,DEVIN,SCOST,DCOST,SOLD from SOFTWARE where PNAME='rakesh'
7How many programmers studied at Pentafour.
Ans: select count(PNAME) from STUDIES where SPLACE='pentafour'
8Display the details of packages for which the development cost has been recovered.
Ans:select TITLE,DEVIN,SCOST,DCOST,SOLD from SOFTWARE
where (SCOST*SOLD)>=DCOST
9What is the price of costliest software developed in VB?
Ans: select MAX(SCOST) as costliest from SOFTWARE where DEVIN='VB'
10How many packages were developed in Oracle ?
Ans: select count(TITLE) as ORACLEcount from SOFTWARE where TITLE='ORACLE'
.jpg)
