SQL – QUERIES

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'