SQL_Chapter4_ASSIGNMENT

 ---1. What is the cost of the costliest software development in Basic?

select TITLE, SCOST from SOFTWARE where SCOST=(select MAX(SCOST) from SOFTWARE)

--2. Display the Cost of Package Developed By each Programmer

select PNAME, title, dcost, sum(dcost) over(partition by pname) as [total cost of packages] from SOFTWARE

--3. Who are the Programmers who celebrate their Birthdays during the Current Month?

select PNAME, month(DOB) as [month] from PROGRAMMER where month(DOB)=MONTH(GETDATE())

select PNAME, datename(mm, DOB) as [month] from PROGRAMMER where DATENAME(MM, DOB)= DATENAME(mm, getdate())

--4. Display the sales values of the Packages Developed by each Programmer?

select distinct PNAME, sum(SCOST*SOLD) over(partition by pname) as [sales values] from SOFTWARE

--5. Display the Number of Packages sold by Each Programmer

select distinct PNAME, COUNT(title) over(partition by pname) as[packages sold] from SOFTWARE

select pname, COUNT(title) from SOFTWARE group by PNAME

---6. Display each programmer’s name, costliest and cheapest Packages Developed by him or her

select PNAME, title, scost, 

FIRST_VALUE(TITLE) 

over(partition by pname order by scost desc) 

as [costliest],

LAST_VALUE(TITLE)

over (partition by pname order by scost desc

Range between unbounded preceding and unbounded following) 

as [cheapest]

from SOFTWARE

-- 7 Display each institute name with the number of Courses, Average Cost per Course

select distinct INSTITUTE, 

count( distinct COURSE) as [number of courses],

AVG( distinct [COURSE FEE])as [avg cost per course]

from STUDIES 

group by INSTITUTE

--8. Display each institute Name with Number of Students.

select distinct INSTITUTE, COUNT(PNAME) as [no of students] from STUDIES group by INSTITUTE

--9. List the programmers (form the software table) and the institutes they studied.

select DISTINCT p.PNAME, S.INSTITUTE from PROGRAMMER p

join STUDIES S on p.PNAME=S.PNAME

--10. How many packages were developed by students, who studied in institute that charge lowest course fee

select sp.PNAME, COUNT(TITLE) as [no of packages] from SOFTWARE sp

join STUDIES st on sp.PNAME=st.PNAME

where st.[COURSE FEE]=(select MIN([COURSE FEE]) from STUDIES)

group by sp.PNAME

order by [no of packages]

--11. What is the AVG salary for those whose software sales is more than 50,000/-.

select p.PNAME, AVG(p.SALARY) as [AVERAGE_SALARY] from PROGRAMMER p

join SOFTWARE s on p.PNAME=s.PNAME

where (s.SCOST*s.SOLD)>50000

group by p.PNAME

--12. Which language listed in prof1, prof2 has not been used to develop any package

select PROF1 as [Lang not used to develop] from 

(select distinct PROF1 from PROGRAMMER

union

select distinct PROF2 from PROGRAMMER) p

where PROF1 not in (select distinct DEVELOPIN from SOFTWARE)

--13. Display the total sales value of the software, institute wise

select distinct st.INSTITUTE, sum(sw.SCOST*sw.SOLD) over(partition by st.institute ) as [SALES VALUE] from STUDIES st

inner join SOFTWARE sw on st.PNAME=sw.PNAME

--14. Display the details of the Software Developed in C By female programmers of Pragathi.

select* from SOFTWARE 

where DEVELOPIN='c' and PNAME IN (select DISTINCT PNAME FROM PROGRAMMER WHERE GENDER='f')

and PNAME in (select distinct PNAME from STUDIES where INSTITUTE like ('p%'))

--15. Display the details of the packages developed in Pascal by the Female Programmers

select * from SOFTWARE 

where 

DEVELOPIN='pascal'

and

PNAME in (select distinct PNAME from PROGRAMMER where GENDER='f')

--16. Which language has been stated as the proficiency by most of the Programmers

select PROF1, total_count from

(select PROF1, [total_count],

RANK() over(order by total_count desc) as [ranked]

from

(select distinct PROF1, COUNT(PROF1) over (partition by prof1) as [total_count]

from

(select PNAME, PROF1 from PROGRAMMER

union 

select PNAME, PROF2 from PROGRAMMER) p) pp)ppp

where ranked=1

---doubted ans

--- with CTE as (select PROF1 from PROGRAMMER

--- union all 

--- select PROF2 from PROGRAMMER

---)

---select PROF1, COUNT(*) as total_count 

---from CTE

---group by Prof1

---order by total_count desc;

--17. Who is the Author of the Costliest Package?

select PNAME from SOFTWARE where SCOST=(select MAX(scost) from SOFTWARE)

--18. which package has the Highest Development cost?

select * from SOFTWARE where DCOST= (select MAX(dcost) from SOFTWARE)

--19. Who is the Highest Paid Female COBOL Programmer?

select top 1 PNAME from PROGRAMMER where GENDER='f' and PROF1='cobol' or PROF2='cobol' order by SALARY desc

                        ---OR----

with CTE as

(select * from PROGRAMMER where GENDER='f' and PROF1='cobol' or PROF2='cobol')

select pname from CTE where SALARY=(select MAX(SALARY) from CTE)

--20. Display the Name of Programmers and Their Package

select PNAME, TITLE from SOFTWARE order by PNAME

--21. Display the Number of Packages in Each Language Except C and C++

select DEVELOPIN, COUNT(DEVELOPIN) as [NUMBER OF PACKAGES] from SOFTWARE where DEVELOPIN not in ('C','C++') group by DEVELOPIN

--22. Display AVG Difference between SCOST, DCOST for Each Package

Select avg(SCOST-DCOST) as [avgf_Diff _value] from SOFTWARE

--23. Display the total SCOST, DCOST and amount to Be Recovered for each Programmer for Those Whose Cost has not yet been Recovered

 with CTE as

(select *, (SCOST*SOLD) as SALES_VALUE from SOFTWARE)

select PNAME, SCOST, DCOST, SALES_VALUE from CTE where DCOST>SALES_VALUE

--24. Who is the Highest Paid C Programmers?

select top 1 PNAME, SALARY from PROGRAMMER where PROF1='C' or PROF2='C' order by SALARY desc

--25. Who is the Highest Paid Female COBOL Programmer ? 

select top 1 PNAME from PROGRAMMER where PROF1='COBOL' OR PROF2='COBOL' order by SALARY desc