SQL-Chapter3-Assignment

-

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

order by [no of packages]


--5. List each PROF with the number of Programmers having that PROF and the number of the packages in that PROF.


select p.PROF1 as [Profile], COUNT(developin) as [No of programs], count(distinct s.PNAME ) as [No of programmers]

from (select distinct PROF1 from PROGRAMMER union select distinct prof2 from PROGRAMMER) p

left join SOFTWARE s on p.PROF1=s.DEVELOPIN

group by p.PROF1


--6. How many packages are developed by the most experienced programmer form BDPS?

SELECT st.PNAME, COUNT(sw.TITLE) AS [NO OF PROGRAMMS] from STUDIES st 

left join PROGRAMMER pg on st.PNAME=pg.PNAME

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

where st.INSTITUTE='BDPS' AND pg.DOJ=(select MIN(DOJ) FROM PROGRAMMER P LEFT JOIN STUDIES S ON P.PNAME=S.PNAME WHERE S.INSTITUTE='BDPS')

GROUP BY st.PNAME


--7.  How many packages were developed by the female programmers earning more than the highest paid male programmer

select COUNT(*) as [number of female programmers] from PROGRAMMER where GENDER='F' and SALARY>(select Max(SALARY) from PROGRAMMER where GENDER='M')


--8. How much does the person who developed the highest selling package earn and what course did HE/SHE undergo.

select sw.PNAME as [Names], sw.SCOST, (sw.SCOST*sw.SOLD) as [earnings], st.COURSE from SOFTWARE Sw

left join STUDIES St on Sw.PNAME=St.PNAME

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


--9. . In which institute did the person who developed the costliest package study?

select St.INSTITUTE from STUDIES St 

left join SOFTWARE sw on St.PNAME=sw.pname

where sw.DCOST=(select MAX(dcost) from SOFTWARE)


--10. Display the names of the programmers who have not developed any packages

select PG.PNAME as [Names] from PROGRAMMER PG 

left join SOFTWARE Sw on PG.PNAME=Sw.PNAME

where PG.PNAME not in (select distinct PNAME from SOFTWARE)  

--or

select PNAME from PROGRAMMER where PNAME not in  (select distinct PNAME from SOFTWARE)


--11. Display the details of the software that has developed in the language which is neither the first nor the second proficiency

SELECT S.TITLE FROM SOFTWARE S

LEFT JOIN (SELECT DISTINCT PROF1 FROM PROGRAMMER UNION SELECT DISTINCT PROF2 FROM PROGRAMMER) P 

ON S.DEVELOPIN=P.PROF1

WHERE S.DEVELOPIN <> P.PROF1


--12.  Display the details of the software Developed by the male programmers Born before 1965 and female programmers born after 1975

select s.PNAME, s.TITLE, S.DEVELOPIN from SOFTWARE S 

right join (select PNAME, DATENAME(YYYY,DOB) AS [YEAR] from PROGRAMMER where GENDER='M' AND DATENAME(YYYY,DOB)<1965

Union

select PNAME, DATENAME(YYYY,DOB) AS [YEAR] from PROGRAMMER where GENDER='F' AND DATENAME(YYYY,DOB)>1975) P

on S.PNAME=P.PNAME


--13.Display the number of packages, No. of Copies Sold and sales value of each programmer institute wise.

select 

st.PNAME, 

st.INSTITUTE, 

count(sw.TITLE) as [number of packages], 

sum(sw.sold) as[no of copies sold], 

sum(SCOST*SOLD) as [sales value] 

from SOFTWARE sw 

left join STUDIES st on sw.PNAME=st.PNAME

group by st.PNAME, st.INSTITUTE


--14.  Display the details of the Software Developed by the Male Programmers Earning More than 3000

select s.* from (select PNAME, SALARY from PROGRAMMER where GENDER='m' and SALARY>3000) p

left join SOFTWARE s on p.PNAME=s.PNAME


--15.Who are the Female Programmers earning more than the Highest Paid male?

select PNAME from PROGRAMMER where GENDER='f' and SALARY>(select MAX(salary) from PROGRAMMER where GENDER ='M')


--16.  Who are the male programmers earning below the AVG salary of Female Programmers?

select PNAME from PROGRAMMER where GENDER='m' and SALARY <(select AVG(salary) from PROGRAMMER where GENDER='F')


--17.  Display the language used by each programmer to develop the Highest Selling and Lowest-selling package.

select distinct DEVELOPIN from SOFTWARE where SCOST=(select MIN(scost) from SOFTWARE)

union

select distinct DEVELOPIN from SOFTWARE where SCOST=(select Max(scost) from SOFTWARE)


--18.  Display the names of the packages, which have sold less than the AVG number of copies.

select TITLE from SOFTWARE where SOLD<(select avg(SOLD) from SOFTWARE)


--19. Which is the costliest package developed in PASCAL

select * from SOFTWARE where DEVELOPIN='pascal' and DCOST=(select MAX(DCOST) from SOFTWARE where DEVELOPIN='pascal')


--20. How many copies of the package that has the least difference between development and selling cost were sold.

select SOLD from SOFTWARE where (DCOST-SCOST)=(select MIN(dcost-scost) from SOFTWARE)


--21. Which language has been used to develop the package, which has the highest sales amount?


select DEVELOPIN from SOFTWARE where (scost*sold)=(select MAX(scost*sold) from SOFTWARE)


--22. Who Developed the Package that has sold the least number of copies?

select PNAME from SOFTWARE where SOLD=(select MIN(SOLD) from SOFTWARE)


--23. Display the names of the courses whose fees are within 1000 (+ or -) of the Average Fee


select * from STUDIES where [COURSE FEE] between (select AVG ([COURSE FEE])-1000 from STUDIES) and (select AVG ([COURSE FEE])+1000 from STUDIES)


--24. Display the name of the Institute and Course, which has below AVG course fee. 

SELECT INSTITUTE, COURSE FROM STUDIES WHERE [COURSE FEE]< (select AVG([COURSE FEE]) FROM STUDIES)


--25. Which Institute conducts costliest course.


SELECT INSTITUTE FROM STUDIES WHERE [COURSE FEE]=(SELECT MAX([COURSE FEE]) from STUDIES)


--26. What is the Costliest course?

select COURSE from STUDIES where [COURSE FEE]=(select MAX([COURSE FEE]) from STUDIES)


Comments

Post a Comment

Popular posts from this blog

Assignment module 1&2

-
 create database assignment1; use assignment1; /*Problem Statement:  ABC Fashion is a leading retailer with a vast customer base and a team of dedicated sales  representatives. They have a Sales Order Processing System that helps manage customer  orders and interactions.*/  -- Salesman table creation  CREATE TABLE Salesman (  SalesmanId INT,  Name VARCHAR(255),  Commission DECIMAL(10, 2),  City VARCHAR(255),  Age INT  );  --Salesman table record insertion  INSERT INTO Salesman (SalesmanId, Name, Commission, City, Age)  VALUES  (101, 'Joe', 50, 'California', 17),  (102, 'Simon', 75, 'Texas', 25),  (103, 'Jessie', 105, 'Florida', 35),  (104, 'Danny', 100, 'Texas', 22),  (105, 'Lia', 65, 'New Jersey', 30);  -- Customer table creation  CREATE TABLE Customer (  SalesmanId INT,  CustomerId INT,  CustomerName VARCHAR(255),  PurchaseAmount INT,  );  -- Cust...
Read more

Assignment Module 3&4

-
create database assignment2 use assignment2 select * from jomato -------------------------------------------------------------------------------------------------------- /* Dataset: Jomato  About the dataset:  You work for a data analytics company, and your client is a food delivery platform similar to  Jomato. They have provided you with a dataset containing information about various  restaurants in a city. Your task is to analyze this dataset using SQL queries to extract valuable  insights and generate reports for your client. */ -----------------------------------------------------------------------------------------------------   --Tasks to be performed:  -- 1.Create a user-defined functions to stuff the Chicken into ‘Quick Bites’. Eg: ‘Quick Chicken Bites’  create function fn_modify_restaurant_type(  @oldtype nvarchar(100),  @newtype nvarchar(100)  )  returns table  as  return ( select Orderid,  ...
Read more

SQL_chapter1_assignment

-
 --1.Find out the selling cost AVG for packages developed in Pascal. Select AVG(SCOST) as [Average selling cost] From SOFTWARE Where DEVELOPIN='PASCAL'; --2.Display Names, Ages of all Programmers. select pname as Names, DATEDIFF(YY,[DOB],GETDATE()) as [Age] from programmer; --3.Display the Names of those who have done the DAP Course. Select PNAME as Names, COURSE from STUDIES where COURSE = 'DAP'; --4.Display the Names and Date of Births of all Programmers Born in January. Select PNAME as Names,DOB, DATENAME(MM, DOB) as [Month] from PROGRAMMER where DATENAME(MM, DOB) = 'January' --5.Display the Details of the Software Developed by Ramesh select * from SOFTWARE where PNAME='Ramesh' --6. Display the Details of Packages for which Development Cost have been recovered. select * , (SCOST*SOLD) as [Total Sale] from SOFTWARE  where (SCOST*SOLD) > DCOST --7. Display the details of the Programmers Knowing C select pname from PROGRAMMER where PROF1='C' o...
Read more