SQL Assignment – Module 5

 

                                     SQL Assignment – Module 5 SQL Assignment – Module 5


Questions :


1. Display the names of the highest paid programmers for each Language. 

2. Display the details of those who are drawing the same salary. 

3. Who are the programmers who joined on the same day?

 4. Who are the programmers who have the same Prof2? 

5. How many packages were developed by the person who developed the cheapest package, where did he/she study?


Answer :

---ARNAB BOSE---


SELECT * FROM PROGRAMMER

SELECT * FROM SOFTWARE

SELECT * FROM STUDIES


---Q1---


WITH LanguageHighestPaid AS (

    SELECT DISTINCT

        PROF1 AS LANGUAGE,

        FIRST_VALUE(PNAME) OVER(PARTITION BY PROF1 ORDER BY SALARY DESC) AS HIGHEST_PAID_PROGRAMMER

    FROM PROGRAMMER

    UNION

    SELECT DISTINCT

        PROF2 AS LANGUAGE,

        FIRST_VALUE(PNAME) OVER(PARTITION BY PROF2 ORDER BY SALARY DESC) AS HIGHEST_PAID_PROGRAMMER

    FROM PROGRAMMER

)

SELECT * FROM LanguageHighestPaid


---OR---


SELECT 

  lang.prof,

  (

    SELECT TOP 1 pname

    FROM programmer

    WHERE prof1 = lang.prof OR prof2 = lang.prof

    ORDER BY salary DESC

  ) as Name,

  (

    SELECT MAX(Salary)

    FROM programmer

    WHERE prof1 = lang.prof OR prof2 = lang.prof

  ) as MaxSalary

FROM (

  SELECT prof1 as prof FROM programmer 

  UNION

  SELECT prof2 as prof FROM programmer 

) AS lang


---OR---


WITH cte AS (

  SELECT PNAME, SALARY, PROF1 PROF FROM programmer

  UNION 

  SELECT PNAME, SALARY, PROF2      FROM programmer

)

SELECT p1.PNAME, p1.PROF, p1.SALARY

FROM cte p1

LEFT JOIN cte p2

  ON p1.PROF = p2.PROF AND p1.SALARY < p2.SALARY

WHERE p2.PNAME IS NULL


---ARNAB BOSE---



---Q2---


SELECT P1.PNAME  Programmer1,

P1.SALARY  Salary,

P2.PNAME  Programmer2

FROM PROGRAMMER P1

INNER JOIN PROGRAMMER P2 

ON P1.SALARY = P2.SALARY 

AND 

P1.PNAME != P2.PNAME

ORDER BY Salary


---OR---


Select * From PROGRAMMER Where Salary in

(Select Salary From PROGRAMMER Group by Salary Having Count(Salary ) > 1)


---ARNAB BOSE---



---Q3---


SELECT P1.PNAME  Programmer1,

P1.DOJ DOJ,

P2.PNAME  Programmer2

FROM PROGRAMMER P1

INNER JOIN PROGRAMMER P2 

ON P1.DOJ= P2.DOJ

AND 

P1.PNAME != P2.PNAME

ORDER BY P1.DOJ


---OR---


select a.pname,a.doj from programmer a,PROGRAMMER b

where a.doj=b.doj and a.pname <> b.pname


---OR--


SELECT DOJ, STRING_AGG(PNAME, ', ') AS JoinedProgrammers

FROM PROGRAMMER

GROUP BY DOJ

HAVING COUNT(*) > 1


---ARNAB BOSE---



---Q4---


SELECT P1.PNAME AS Programmer1, P2.PNAME AS Programmer2, P1.PROF2 AS CommonProf2

FROM PROGRAMMER P1

JOIN PROGRAMMER P2 ON P1.PROF2 = P2.PROF2 AND P1.PNAME < P2.PNAME



---OR---


SELECT DISTINCT a.pname, a.prof2

FROM PROGRAMMER a, PROGRAMMER b

WHERE a.prof2 = b.prof2 AND a.pname <> b.pname



---Q5---



SELECT

    P.PNAME,

    COUNT(S.PNAME) AS Total_Developed_Packages,

    MIN(S.DCOST) AS Minimum_Cost

FROM

    PROGRAMMER P

LEFT JOIN

    SOFTWARE S ON P.PNAME = S.PNAME

GROUP BY

    P.PNAME


---ARNAB BOSE---


Comments

Post a Comment

Popular posts from this blog

SQL Assignment – Module 4

                          SQL Assignment – Module 4  SQL Assignment – Module 4 Questions : 1. What is the cost of the costliest software development in Basic?  2. Display details of Packages whose sales crossed the 2000 Mark.  3. Who are the Programmers who celebrate their Birthdays during the Current Month?  4. Display the Cost of Package Developed By each Programmer.  5. Display the sales values of the Packages Developed by each Programmer.  6. Display the Number of Packages sold by Each Programmer.  7. Display each programmer’s name, costliest and cheapest Packages Developed by him or her.  8. Display each institute name with the number of Courses, Average Cost per Course.  9. Display each institute Name with Number of Students.  10. List the programmers (form the software table) and the institutes they studied.  11. How many packages were developed by students, who...
Read more

SQL Assignments TABLE

Assignments TABLE CREATE TABLE STUDIES (     PNAME VARCHAR(500),     INSTITUTE VARCHAR(500),     COURSE VARCHAR(500),     --COURSE_FEE decimal (10,2) ----------or COURSE_FEE int ); INSERT INTO STUDIES (PNAME, INSTITUTE, COURSE, COURSE_FEE) VALUES     ('ANAND', 'SABHARI', 'PGDCA', 4500),     ('ALTAF', 'COIT', 'DCA', 7200),     ('JULIANA', 'BDPS', 'MCA', 22000),     ('KAMALA', 'PRAGATHI', 'DCA', 5000),     ('MARY', 'SABHARI', 'PGDCA', 4500),     ('NELSON', 'PRAGATHI', 'DAP', 6200),     ('PATRICK', 'PRAGATHI', 'DCAP', 5200),     ('QADIR', 'APPLE', 'HDCA', 14000),     ('RAMESH', 'SABHARI', 'PGDCA', 4500),     ('REBECCA', 'BRILLIANT', 'DCAP', 11000),     ('REMITHA', 'BDPS', 'DCS', 6000),     ('REVATHI', 'SABHARI...
Read more

SQL Assignment – Module 1

SQL Assignment – Module 1                         SQL Assignment – Module 1  SQL Assignment – Module 1 Questions Problem Statement: Consider yourself to be Sam and you have been given the below tasks to complete using the Table – STUDIES, SOFTWARE & PROGRAMMER.  1. Find out the selling cost AVG for packages developed in Pascal.  2. Display Names, Ages of all Programmers.  3. Display the Names of those who have done the DAP Course.  4. Display the Names and Date of Births of all Programmers Born in January.  5. Display the Details of the Software Developed by Ramesh.  6. Display the Details of Packages for which Development Cost have been recovered.  7. Display the details of the Programmers Knowing C.  8. What are the Languages studied by Male Programmers? 9 . Display the details of the Programmers who joined before 1990.  10. Who are the authors of the Packages, which have ...
Read more