SQL_Chapter2_Assignment

-

 --1. what is the highest number of copies sold by a package

SELECT MAX(SOLD) FROM SOFTWARE;


--2. Display lowest course fee

select min([course fee]) from STUDIES;


--3. how old is the oldest male programmer

select Max(DATEDIFF(YY,DOB, GETDATE())) as age from PROGRAMMER where GENDER= 'M';


--4.what is AVG age of femle programmers

Select AVG(DATEDIFF(YY,DOB, GETDATE())) as [avg age] from PROGRAMMER where GENDER='F';


--5. Calculate the experience in years for each programmers and display along with the names in descending order?

select pname, DATEDIFF(YY, DOJ, GETDATE()) as[experience in years] from PROGRAMMER order by DATEDIFF(YY, DOJ, GETDATE()) desc;


--6. How many programmers done the PGDCA Course?

select COUNT(Pname) as [programmers done the PGDCA] from STUDIES where course = 'PGDCA'


--7. How much revenue has been earned through sales of Packages Developed in C


select sum(scost*sold) as Revenue from SOFTWARE where DEVELOPIN='C'


--8. How many Programmers Studied at Sabhari?

Select COUNT(PNAME) as [Programmers Studied at Sabhari] from STUDIES where INSTITUTE='Sabhari'


--9. How many Packages Developed in DBASE?

select COUNT(title) as [no of Packages Developed in DBASE] from SOFTWARE where DEVELOPIN='DBASE'


--10. How many programmers studied in Pragathi?

Select COUNT(PNAME) as [Programmers Studied at Pragathi] from STUDIES where INSTITUTE='Pragathi'


--11. How many Programmers Paid 5000 to 10000 for their course?

select count(pname) as[No of programmers] from STUDIES where [COURSE FEE] between 5000 and 10000


--12. How many Programmers know either COBOL or PASCAL

select Count(pname) as [No of Programmers] from PROGRAMMER 

Where PROF1 in ('COBOL', 'PASCAL') or PROF2 in ('COBOL', 'PASCAL')


--13 How many Female Programmers are there?

select COUNT(pname) as[No of Female Programmers] from PROGRAMMER where GENDER='F'


--14. What is the AVG Salary?

select AVG(salary) as [avg salary] from PROGRAMMER


--15. How many people draw salary 2000 to 4000?

SELECT COUNT(pname) as [count of people] from PROGRAMMER where SALARY between 2000 and 4000


--16. Display the sales cost of the packages Developed by each Programmer Language wise

Select developin, sum(Scost) as 'Cost of Sales' from Software Group by DEVELOPIN


--17. Display the details of the software developed by the male students of Sabhari

SELECT title,SOFTWARE.PNAME from SOFTWARE

left join

PROGRAMMER ON

SOFTWARE.PNAME=PROGRAMMER.PNAME

left join

studies on

STUDIES.pname=SOFTWARE.pname 

where STUDIES.INSTITUTE='Sabhari' and programmer.gender='m'


--18.Who is the oldest Female Programmer who joined in 1992?

select PNAME, DOJ FROM PROGRAMMER where DOJ=(select MIN(doj) FROM PROGRAMMER WHERE YEAR(DOJ) LIKE '1992' and GENDER='f')


--19. Who is the youngest male Programmer born in 1965?

select pname, DOB from PROGRAMMER where DOB = (select MAX(DOB) from PROGRAMMER where year(DOB) like'1965')


--20. Which Package has the lowest selling cost?

select TITLE, SCOST from SOFTWARE WHERE SCOST = (SELECT MIN(SCOST) FROM SOFTWARE)


--21. Which Female Programmer earning more than 3000 does not know C, C++, ORACLE or DBASE?

Select PNAME, SALARY from PROGRAMMER where GENDER='F' 

and

SALARY>3000

and 

PROF1 NOT IN ('C', 'C++', 'ORACLE', 'DBASE') 

and

PROF2 NOT IN ('C', 'C++', 'ORACLE', 'DBASE') 



--22.Who is the Youngest Programmer knowing DBASE?

select PNAME, dob, DATEDIFF(yy,dob,GETDATE()) as age from PROGRAMMER 

where   

DATEDIFF(yy,dob,GETDATE())=(select min(DATEDIFF(yy,dob,GETDATE())) from programmer WHERE prof1='DBASE' or Prof2='DBASE')


--23. Which Language is known by only one Programmer?

select prof1 as prof from PROGRAMMER group by PROF1 

having PROF1 not in (select PROF2 from PROGRAMMER) and COUNT(prof1)=1

union

select  PROF2 as prof from PROGRAMMER group by PROF2 

having PROF2 not in (select PROF1 from PROGRAMMER) and COUNT(PROF2)=1



--24. Who is the most experienced male programmer knowing PASCAL?

select pname,concat(DATEDIFF(YY,DOJ, GETDATE()), ' ', 'years') as experience from PROGRAMMER where GENDER='m' 

and

Doj=(select MIN(doj) from PROGRAMMER where PROF1='PASCAL' OR PROF2='PASCAL')

--25. Who is the least experienced Programmer ?

select Pname, DOJ from PROGRAMMER where DOJ=(select MAX(doj) from PROGRAMMER)


--26. Display the Number of Packages in Each Language for which Development Cost is less than 1000


select developin as Lang, count(developin) as [no of package] from SOFTWARE where dcost<1000 group by DEVELOPIN


--27. Display Highest, Lowest and Average Salaries for those earning more than 2000

select MAX(salary) as [Maximum salary], MIN(salary) as [Mimimum salary], AVG(salary) as [Average salary] from PROGRAMMER where SALARY>2000




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