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' or PROF2='C'


--8. What are the Languages studied by Male Programmers?


Select Distinct PROF1 as'Languages studied by Male Programmers' From Programmer 

where GENDER='M' 

UNION 

Select Distinct PROF2 as'Languages studied by Male Programmers' From Programmer 

where GENDER='M'


--9. Display the details of the Programmers who joined before 1990

SELECT PNAME as Names, DOJ FROM PROGRAMMER WHERE DATENAME(YY, DOJ)<1990


--10. 

Who are the authors of the Packages, which have recovered more than double the Development cost?

select distinct pname as authors from software  where (SCOST*SOLD)>(DCOST*2);

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