SQL Notes

 ---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 ea...

 --1. How many Programmers Don’t know PASCAL and C select PNAME AS [Programmers] from PROGRAMMER where PROF1 not in ('PASCAL', 'C') AND PROF2 not in ('PASCAL', 'C') --2. Display the details of those who don’t know Clipper, COBOL or PASCAL select *  from PROGRAMMER where PROF1 NOT IN('Clipper','COBOL','PASCAL')  AND PROF2 NOT IN('Clipper','COBOL','PASCAL') --3. Display each language name with AVG Development Cost, AVG Selling Cost and AVG Price per Copy select DEVELOPIN as [software names], AVG(dcost) as [avg development cost], AVG(scost)as [avg selling cost], AVG(SCOST/nullif(SOLD,0)) as [Average price] from SOFTWARE group by DEVELOPIN --4.  List the programmer names (from the programmer table) and No. Of Packages each has developed.  select P.PNAME AS Programmers, count(S.title) as [no of packages] from PROGRAMMER p  inner join SOFTWARE s on p.PNAME=s.PNAME group by p.PNAME o...