Dbms Assignment No 2
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Dbms Assignment No 2 as PDF for free.
More details
- Words: 1,458
- Pages: 9
QUERY SHEET NO. – 2 1. Display the number of packages developed a each language? Sol. SELECT DEV_IN,COUNT(TITLE) AS NO_OF_PACKAGES FROM SOFTWARE GROUP BY DEV_IN; DEV_IN
NO_OF_PACKAGES
ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
2 2 4 1 1 2 1 2 4
2. Display the number of packages developed by each person? Sol. SELECT NAME,COUNT(TITLE) AS NO_OF_PACKAGES FROM SOFTWARE GROUP BY NAME; NAME
NO_OF_PACKAGES
ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
2 2 1 1 3 1 2 2 2 2 1
3. Display the number of male and female programmers? Sol. SELECT COUNT(DISTINCT NAME) AS NO_MALE_FEMALE FROM PROGRAMMER GROUP BY SEX; NO_MALE_FEMALE 7 7 4. Display the number of people born in each year? Sol. SELECT SUBSTR(DOB,8,2) AS YEAR_OF_BIRTH,COUNT(*) AS NO_OF_PEOPLE FROM PROGRAMMER GROUP BY SUBSTR(DOB,8,2); YE
NO_OF_PEOPLE 64 65 66
© Created by Arun Pandey
- 14 System
1 4 1
Data Base Management
67 68 69 70
2 2 1 3
5. Display the number of people joined in each year? Sol. SELECT SUBSTR(DOJ,8,2) AS YEAR_OF_JOIN,COUNT(*) AS NO_OF_PEOPLE FROM PROGRAMMER GROUP BY SUBSTR(DOJ,8,2); YE
NO_OF_PEOPLE 89 90 91 92 93 94
1 4 2 4 2 1
6. Display the number of people born in each month? Sol. SELECT TO_CHAR(DOB,'MON') AS MONTH_OF_BIRTH,COUNT(*) AS NO_OF_PEOPLE FROM PROGRAMMER GROUP BY TO_CHAR(DOB,'MON') ; MON
NO_OF_PEOPLE
APR AUG DEC JAN JUL JUN MAY NOV OCT SEP
2 1 2 2 1 1 1 1 2 1
7. Display the number of people joined in each month? Sol. SELECT TO_CHAR(DOJ,'MON') AS MONTH_OF_JOINING,COUNT(*) AS NO_OF_PEOPLE FROM PROGRAMMER GROUP BY TO_CHAR(DOJ,'MON') ; MON
NO_OF_PEOPLE
APR DEC FEB JAN MAY NOV OCT
5 1 2 2 1 1 2
8. Display the language wise count of prof1? Sol. SELECT PROF1 AS LANGUAGE,COUNT(PROF1) AS LANGUAGE_WISE_COUNT FROM PROGRAMMER GROUP BY PROF1;
© Created by Arun Pandey
- 15 System
Data Base Management
LANGUAGE
LANGUAGE_WISE_COUNT
ASSEMBLY BASIC C C++ CLIPPER COBOL FOXPRO ORACLE PASCAL
1 1 2 1 1 2 1 1 4
9. Display the language wise count of prof2? Sol. SELECT PROF2 AS LANGUAGE,COUNT(PROF2) AS LANGUAGE_WISE_COUNT FROM PROGRAMMER GROUP BY PROF2; LANGUAGE
LANGUAGE_WISE_COUNT
ASSEMBLY BASIC C CLIPPER COBAL COBOL DBASE JAVA ORACLE
1 2 2 1 1 1 4 1 1
10. Display the number of people in each salary group? Sol. SELECT SALARY,COUNT(NAME) AS PEOPLE_IN_SALARY_GP FROM PROGRAMMER GROUP BY SALARY; SALARY
PEOPLE_IN_SALARY_GP 2500 2800 2900 3000 3200 3500 3600 3700 4100 4500
2 2 1 2 2 1 1 1 1 1
11. Display the number of people who studied in each institute? Sol. SELECT SPLACE AS STUDIED_PLACE,COUNT(NAME) AS PEOPLE_STUD_IN_INST FROM STUDIES GROUP BY SPLACE; STUDIED_P
PEOPLE_STUD_IN_INST
APPLE BITS BOPS BRILLIANT
© Created by Arun Pandey
1 1 2 1
- 16 System
Data Base Management
CCIT PRAGATHI SABHARI SSIL
1 3 4 1
12. Display the number of people who studied in each course? Sol. SELECT COURSE AS COURSE_STUDIED,COUNT(NAME) AS PEOPLE_STUD_COURSE FROM STUDIES GROUP BY COURSE; COURS DAP DCA DCAP DCP DCS HOCP PGDCA
PEOPLE_STUD_COURSE 2 5 1 1 1 1 3
13. Display the total development cost of the packages developed in each language? Sol. SELECT DEV_IN AS DEVELOPING_LANGUAGE,SUM(DCOST) AS DEVELOPMENT_COST FROM SOFTWARE GROUP BY DEV_IN; DEVELOPI ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
DEVELOPMENT_COST 6530 8100 29100 1200 3500 55000 7500 97000 94500
14. Display the total selling cost of the packages developed in each language? Sol. SELECT DEV_IN AS DEVELOPING_LANGUAGE,SUM(SCOST) AS SELLING_COST FROM SOFTWARE GROUP BY DEV_IN; DEVELOPI ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
SELLING_COST 2999.95 3599.95 8025 300 3000 21000 800 21000 9449.95
15. Display the cost of the package developed by each programmer?
© Created by Arun Pandey
- 17 System
Data Base Management
Sol. SELECT NAME,SUM(SCOST) AS COST_SOFTWARE FROM SOFTWARE GROUP BY NAME; NAME ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
COST_SOFTWARE 7899.95 3800 3000 9000 22800 750 2399.95 12099.95 3225 4300 900
16. Display the sales values of the packages developed in each programmer? Sol. SELECT NAME,SUM(SCOST*SOLD) AS SALES_VALUES FROM SOFTWARE GROUP BY NAME; NAME ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
SALES_VALUES 84697.85 50000 0 63000 203700 8250 96894.3 55296.35 51975 50200 5400
17. Display the number of packages developed by each programmer? Sol. SELECT NAME,COUNT(TITLE) AS NO_SOFTWARE_BY_EACH FROM SOFTWARE GROUP BY NAME; NAME
NO_SOFTWARE_BY_EACH
ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
2 2 1 1 3 1 2 2 2 2 1
18. Display the sales cost of the packages developed by each programmer language wise? © Created by Arun Pandey
- 18 System
Data Base Management
Sol. SELECT NAME,DEV_IN,SUM(SCOST*SOLD) AS COST_EACH_PACK_PROG FROM SOFTWARE GROUP BY DEV_IN,NAME ORDER BY NAME; NAME ANAND ANAND JAGADESH JAGADESH JULIANA KAMALA MARY MARY MARY PATRICK QADIR QADIR RAMESH RAMESH REMITHA REMITHA REVATHI REVATHI VIJAYA
DEV_IN BASIC PASCAL JAVA ORACLE COBOL DBASE C C++ ORACLE PASCAL ASSEMBLY C DBASE PASCAL ASSEMBLY C BASIC PASCAL C
COST_EACH_PACK_PROG 17197.85 67500 8000 42000 0 63000 103500 28200 72000 8250 56994.3 39900 48000 7296.35 15000 36975 48000 2200 5400
19. Display each programmers name, costliest package and cheapest packages developed by his/her? Sol. SELECT NAME,MAX(SCOST) AS SELLING_COST FROM SOFTWARE GROUP BY NAME ORDER BY NAME; NAME
SELLING_COST
ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
7500 3000 3000 9000 18000 750 1900 12000 2500 3200 900
20. Display each language name with average development, average selling cost and average price per copy? Sol. SELECT DEV_IN AS LANG_NAME,AVG(DCOST) AS AVERAGE_DEV_COST,ROUND(AVG(SCOST)) AS AVERAGE_SELL_COST FROM SOFTWARE GROUP BY DEV_IN ORDER BY DEV_IN; LANG_NAM ASSEMBLY BASIC C
AVERAGE_DEV_COST 3265 4050 7275
© Created by Arun Pandey
- 19 System
AVERAGE_SELL_COST 1500 1800 2006
Data Base Management
C++ COBOL DBASE JAVA ORACLE PASCAL
1200 3500 27500 7500 48500 23625
300 3000 10500 800 10500 2362
21. Display each institute name with number of course, average cost per course? Sol. SELECT SPLACE AS INSTITUTE,COUNT(DISTINCT COURSE) AS NO_OF_COURSE,ROUND(AVG(CCOST)) AS AVERAGE_COST FROM STUDIES GROUP BY SPLACE ORDER BY SPLACE; INSTITUTE APPLE BITS BOPS BRILLIANT CCIT PRAGATHI SABHARI SSIL
NO_OF_COURSE
AVERAGE_COST 1 1 2 1 1 3 2 1
14000 22000 27000 11000 7200 5467 4625 3500
22. Display each institute name with number of students? Sol. SELECT SPLACE AS INSTITUTE,COUNT(NAME) AS NO_OF_STUDENTS FROM STUDIES GROUP BY SPLACE ORDER BY SPLACE; INSTITUTE
NO_OF_STUDENTS
APPLE BITS BOPS BRILLIANT CCIT PRAGATHI SABHARI SSIL
1 1 2 1 1 3 4 1
23. Display the names of male and female programmers? Sol. (SELECT NAME,SEX FROM PROGRAMMER WHERE SEX='M')UNION(SELECT NAME,SEX FROM PROGRAMMER WHERE SEX='F') ORDER BY SEX; NAME JULIANA KAMALA MARY REBECCA REMITHA REVATHI VIJAYA ALTAF ANAND JAGADESH
© Created by Arun Pandey
S F F F F F F F M M M
- 20 System
Data Base Management
NELSON PATRICK QADIR RAMESH
M M M M
24. Display the programmer’s name and their packages? Sol. SELECT NAME,TITLE FROM SOFTWARE WHERE TITLE IN (SELECT TITLE FROM PROGRAMMER GROUP BY NAME) ORDER BY NAME; NAME ANAND ANAND JAGADESH JAGADESH JULIANA KAMALA MARY MARY MARY PATRICK QADIR QADIR RAMESH RAMESH REMITHA REMITHA REVATHI REVATHI VIJAYA
TITLE PARACHUTES VIDEO TITLING PACK SERIAL LINK UTILITY SHARES MANAGEMENT INVENTORY CONTROL PAYROLL PACKAGE FINANCILA ACC S/W CODE GENERATOR READ ME GRAPHIC EDITOR BOMBS AWAY VACCINES HOTEL MANAGEMENT DEAD LEE PC UTILITIES TSR HELP PACKAGE HOTEL MANAGEMENT QUIZ MASTER ISK EDITOR
25. Display the number of packages in each language? Sol. SELECT DEV_IN AS DEV_IN_LANG, COUNT(TITLE) AS NO_OF_PACKAGES FROM SOFTWARE GROUP BY DEV_IN; DEV_IN_L
NO_OF_PACKAGES
ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
2 2 4 1 1 2 1 2 4
26. Display the number of packages in each language for which development cost is lass than 1000? Sol. SELECT DEV_IN,COUNT(TITLE) AS NO_OF_PACKAGES FROM SOFTWARE WHERE DCOST< 1000 GROUP BY DEV_IN; DEV_IN
NO_OF_PACKAGES
ASSEMBLY
© Created by Arun Pandey
1
- 21 System
Data Base Management
C
1 27. Display the average difference between scost and dcost for each language?
Sol. SELECT DEV_IN AS DEV_LANG,ROUND(AVG(DCOST-SCOST),2) AS AVG_DIFF_DEV_SALE_COST FROM SOFTWARE GROUP BY DEV_IN; DEV_LANG
AVG_DIFF_DEV_SALE_COST
ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
1765.03 2250.03 5268.75 900 500 17000 6700 38000 21262.51
28. Display the total scost, dcost and amount to be recovered for each programmer for whose dcost has nat yet been recovered? Sol. SELECT NAME,SCOST,DCOST,ROUND((DCOST-(SCOST-SOLD)),2) AS AMOUNT_TO_BE_RECOVERED FROM SOFTWARE WHERE NAME IN (SELECT NAME FROM SOFTWARE WHERE (DCOST-(SCOST*SOLD))>0); NAME JULIANA MARY MARY MARY REVATHI REVATHI
SCOST 3000 18000 4500 300 1100 3200
DCOST 3500 85000 20000 1200 75000 2100
AMOUNT_TO_BE_RECOVERED 500 67004 15523 994 73902 -1085
29. Display the highest ,lowest and average salaries for those earning more than 2000? Sol. SELECT MAX(SALARY) AS HIGHEST_SALARY,MIN(SALARY) AS LOWEST_SALARY,ROUND(AVG(SALARY),2) AS AVERAGE_SALARY FROM PROGRAMMER WHERE SALARY>3000; HIGHEST_SALARY
LOWEST_SALARY 4500
© Created by Arun Pandey
3200
- 22 System
AVERAGE_SALARY 3685.71
Data Base Management
NO_OF_PACKAGES
ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
2 2 4 1 1 2 1 2 4
2. Display the number of packages developed by each person? Sol. SELECT NAME,COUNT(TITLE) AS NO_OF_PACKAGES FROM SOFTWARE GROUP BY NAME; NAME
NO_OF_PACKAGES
ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
2 2 1 1 3 1 2 2 2 2 1
3. Display the number of male and female programmers? Sol. SELECT COUNT(DISTINCT NAME) AS NO_MALE_FEMALE FROM PROGRAMMER GROUP BY SEX; NO_MALE_FEMALE 7 7 4. Display the number of people born in each year? Sol. SELECT SUBSTR(DOB,8,2) AS YEAR_OF_BIRTH,COUNT(*) AS NO_OF_PEOPLE FROM PROGRAMMER GROUP BY SUBSTR(DOB,8,2); YE
NO_OF_PEOPLE 64 65 66
© Created by Arun Pandey
- 14 System
1 4 1
Data Base Management
67 68 69 70
2 2 1 3
5. Display the number of people joined in each year? Sol. SELECT SUBSTR(DOJ,8,2) AS YEAR_OF_JOIN,COUNT(*) AS NO_OF_PEOPLE FROM PROGRAMMER GROUP BY SUBSTR(DOJ,8,2); YE
NO_OF_PEOPLE 89 90 91 92 93 94
1 4 2 4 2 1
6. Display the number of people born in each month? Sol. SELECT TO_CHAR(DOB,'MON') AS MONTH_OF_BIRTH,COUNT(*) AS NO_OF_PEOPLE FROM PROGRAMMER GROUP BY TO_CHAR(DOB,'MON') ; MON
NO_OF_PEOPLE
APR AUG DEC JAN JUL JUN MAY NOV OCT SEP
2 1 2 2 1 1 1 1 2 1
7. Display the number of people joined in each month? Sol. SELECT TO_CHAR(DOJ,'MON') AS MONTH_OF_JOINING,COUNT(*) AS NO_OF_PEOPLE FROM PROGRAMMER GROUP BY TO_CHAR(DOJ,'MON') ; MON
NO_OF_PEOPLE
APR DEC FEB JAN MAY NOV OCT
5 1 2 2 1 1 2
8. Display the language wise count of prof1? Sol. SELECT PROF1 AS LANGUAGE,COUNT(PROF1) AS LANGUAGE_WISE_COUNT FROM PROGRAMMER GROUP BY PROF1;
© Created by Arun Pandey
- 15 System
Data Base Management
LANGUAGE
LANGUAGE_WISE_COUNT
ASSEMBLY BASIC C C++ CLIPPER COBOL FOXPRO ORACLE PASCAL
1 1 2 1 1 2 1 1 4
9. Display the language wise count of prof2? Sol. SELECT PROF2 AS LANGUAGE,COUNT(PROF2) AS LANGUAGE_WISE_COUNT FROM PROGRAMMER GROUP BY PROF2; LANGUAGE
LANGUAGE_WISE_COUNT
ASSEMBLY BASIC C CLIPPER COBAL COBOL DBASE JAVA ORACLE
1 2 2 1 1 1 4 1 1
10. Display the number of people in each salary group? Sol. SELECT SALARY,COUNT(NAME) AS PEOPLE_IN_SALARY_GP FROM PROGRAMMER GROUP BY SALARY; SALARY
PEOPLE_IN_SALARY_GP 2500 2800 2900 3000 3200 3500 3600 3700 4100 4500
2 2 1 2 2 1 1 1 1 1
11. Display the number of people who studied in each institute? Sol. SELECT SPLACE AS STUDIED_PLACE,COUNT(NAME) AS PEOPLE_STUD_IN_INST FROM STUDIES GROUP BY SPLACE; STUDIED_P
PEOPLE_STUD_IN_INST
APPLE BITS BOPS BRILLIANT
© Created by Arun Pandey
1 1 2 1
- 16 System
Data Base Management
CCIT PRAGATHI SABHARI SSIL
1 3 4 1
12. Display the number of people who studied in each course? Sol. SELECT COURSE AS COURSE_STUDIED,COUNT(NAME) AS PEOPLE_STUD_COURSE FROM STUDIES GROUP BY COURSE; COURS DAP DCA DCAP DCP DCS HOCP PGDCA
PEOPLE_STUD_COURSE 2 5 1 1 1 1 3
13. Display the total development cost of the packages developed in each language? Sol. SELECT DEV_IN AS DEVELOPING_LANGUAGE,SUM(DCOST) AS DEVELOPMENT_COST FROM SOFTWARE GROUP BY DEV_IN; DEVELOPI ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
DEVELOPMENT_COST 6530 8100 29100 1200 3500 55000 7500 97000 94500
14. Display the total selling cost of the packages developed in each language? Sol. SELECT DEV_IN AS DEVELOPING_LANGUAGE,SUM(SCOST) AS SELLING_COST FROM SOFTWARE GROUP BY DEV_IN; DEVELOPI ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
SELLING_COST 2999.95 3599.95 8025 300 3000 21000 800 21000 9449.95
15. Display the cost of the package developed by each programmer?
© Created by Arun Pandey
- 17 System
Data Base Management
Sol. SELECT NAME,SUM(SCOST) AS COST_SOFTWARE FROM SOFTWARE GROUP BY NAME; NAME ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
COST_SOFTWARE 7899.95 3800 3000 9000 22800 750 2399.95 12099.95 3225 4300 900
16. Display the sales values of the packages developed in each programmer? Sol. SELECT NAME,SUM(SCOST*SOLD) AS SALES_VALUES FROM SOFTWARE GROUP BY NAME; NAME ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
SALES_VALUES 84697.85 50000 0 63000 203700 8250 96894.3 55296.35 51975 50200 5400
17. Display the number of packages developed by each programmer? Sol. SELECT NAME,COUNT(TITLE) AS NO_SOFTWARE_BY_EACH FROM SOFTWARE GROUP BY NAME; NAME
NO_SOFTWARE_BY_EACH
ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
2 2 1 1 3 1 2 2 2 2 1
18. Display the sales cost of the packages developed by each programmer language wise? © Created by Arun Pandey
- 18 System
Data Base Management
Sol. SELECT NAME,DEV_IN,SUM(SCOST*SOLD) AS COST_EACH_PACK_PROG FROM SOFTWARE GROUP BY DEV_IN,NAME ORDER BY NAME; NAME ANAND ANAND JAGADESH JAGADESH JULIANA KAMALA MARY MARY MARY PATRICK QADIR QADIR RAMESH RAMESH REMITHA REMITHA REVATHI REVATHI VIJAYA
DEV_IN BASIC PASCAL JAVA ORACLE COBOL DBASE C C++ ORACLE PASCAL ASSEMBLY C DBASE PASCAL ASSEMBLY C BASIC PASCAL C
COST_EACH_PACK_PROG 17197.85 67500 8000 42000 0 63000 103500 28200 72000 8250 56994.3 39900 48000 7296.35 15000 36975 48000 2200 5400
19. Display each programmers name, costliest package and cheapest packages developed by his/her? Sol. SELECT NAME,MAX(SCOST) AS SELLING_COST FROM SOFTWARE GROUP BY NAME ORDER BY NAME; NAME
SELLING_COST
ANAND JAGADESH JULIANA KAMALA MARY PATRICK QADIR RAMESH REMITHA REVATHI VIJAYA
7500 3000 3000 9000 18000 750 1900 12000 2500 3200 900
20. Display each language name with average development, average selling cost and average price per copy? Sol. SELECT DEV_IN AS LANG_NAME,AVG(DCOST) AS AVERAGE_DEV_COST,ROUND(AVG(SCOST)) AS AVERAGE_SELL_COST FROM SOFTWARE GROUP BY DEV_IN ORDER BY DEV_IN; LANG_NAM ASSEMBLY BASIC C
AVERAGE_DEV_COST 3265 4050 7275
© Created by Arun Pandey
- 19 System
AVERAGE_SELL_COST 1500 1800 2006
Data Base Management
C++ COBOL DBASE JAVA ORACLE PASCAL
1200 3500 27500 7500 48500 23625
300 3000 10500 800 10500 2362
21. Display each institute name with number of course, average cost per course? Sol. SELECT SPLACE AS INSTITUTE,COUNT(DISTINCT COURSE) AS NO_OF_COURSE,ROUND(AVG(CCOST)) AS AVERAGE_COST FROM STUDIES GROUP BY SPLACE ORDER BY SPLACE; INSTITUTE APPLE BITS BOPS BRILLIANT CCIT PRAGATHI SABHARI SSIL
NO_OF_COURSE
AVERAGE_COST 1 1 2 1 1 3 2 1
14000 22000 27000 11000 7200 5467 4625 3500
22. Display each institute name with number of students? Sol. SELECT SPLACE AS INSTITUTE,COUNT(NAME) AS NO_OF_STUDENTS FROM STUDIES GROUP BY SPLACE ORDER BY SPLACE; INSTITUTE
NO_OF_STUDENTS
APPLE BITS BOPS BRILLIANT CCIT PRAGATHI SABHARI SSIL
1 1 2 1 1 3 4 1
23. Display the names of male and female programmers? Sol. (SELECT NAME,SEX FROM PROGRAMMER WHERE SEX='M')UNION(SELECT NAME,SEX FROM PROGRAMMER WHERE SEX='F') ORDER BY SEX; NAME JULIANA KAMALA MARY REBECCA REMITHA REVATHI VIJAYA ALTAF ANAND JAGADESH
© Created by Arun Pandey
S F F F F F F F M M M
- 20 System
Data Base Management
NELSON PATRICK QADIR RAMESH
M M M M
24. Display the programmer’s name and their packages? Sol. SELECT NAME,TITLE FROM SOFTWARE WHERE TITLE IN (SELECT TITLE FROM PROGRAMMER GROUP BY NAME) ORDER BY NAME; NAME ANAND ANAND JAGADESH JAGADESH JULIANA KAMALA MARY MARY MARY PATRICK QADIR QADIR RAMESH RAMESH REMITHA REMITHA REVATHI REVATHI VIJAYA
TITLE PARACHUTES VIDEO TITLING PACK SERIAL LINK UTILITY SHARES MANAGEMENT INVENTORY CONTROL PAYROLL PACKAGE FINANCILA ACC S/W CODE GENERATOR READ ME GRAPHIC EDITOR BOMBS AWAY VACCINES HOTEL MANAGEMENT DEAD LEE PC UTILITIES TSR HELP PACKAGE HOTEL MANAGEMENT QUIZ MASTER ISK EDITOR
25. Display the number of packages in each language? Sol. SELECT DEV_IN AS DEV_IN_LANG, COUNT(TITLE) AS NO_OF_PACKAGES FROM SOFTWARE GROUP BY DEV_IN; DEV_IN_L
NO_OF_PACKAGES
ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
2 2 4 1 1 2 1 2 4
26. Display the number of packages in each language for which development cost is lass than 1000? Sol. SELECT DEV_IN,COUNT(TITLE) AS NO_OF_PACKAGES FROM SOFTWARE WHERE DCOST< 1000 GROUP BY DEV_IN; DEV_IN
NO_OF_PACKAGES
ASSEMBLY
© Created by Arun Pandey
1
- 21 System
Data Base Management
C
1 27. Display the average difference between scost and dcost for each language?
Sol. SELECT DEV_IN AS DEV_LANG,ROUND(AVG(DCOST-SCOST),2) AS AVG_DIFF_DEV_SALE_COST FROM SOFTWARE GROUP BY DEV_IN; DEV_LANG
AVG_DIFF_DEV_SALE_COST
ASSEMBLY BASIC C C++ COBOL DBASE JAVA ORACLE PASCAL
1765.03 2250.03 5268.75 900 500 17000 6700 38000 21262.51
28. Display the total scost, dcost and amount to be recovered for each programmer for whose dcost has nat yet been recovered? Sol. SELECT NAME,SCOST,DCOST,ROUND((DCOST-(SCOST-SOLD)),2) AS AMOUNT_TO_BE_RECOVERED FROM SOFTWARE WHERE NAME IN (SELECT NAME FROM SOFTWARE WHERE (DCOST-(SCOST*SOLD))>0); NAME JULIANA MARY MARY MARY REVATHI REVATHI
SCOST 3000 18000 4500 300 1100 3200
DCOST 3500 85000 20000 1200 75000 2100
AMOUNT_TO_BE_RECOVERED 500 67004 15523 994 73902 -1085
29. Display the highest ,lowest and average salaries for those earning more than 2000? Sol. SELECT MAX(SALARY) AS HIGHEST_SALARY,MIN(SALARY) AS LOWEST_SALARY,ROUND(AVG(SALARY),2) AS AVERAGE_SALARY FROM PROGRAMMER WHERE SALARY>3000; HIGHEST_SALARY
LOWEST_SALARY 4500
© Created by Arun Pandey
3200
- 22 System
AVERAGE_SALARY 3685.71
Data Base Management
Related Documents
Dbms Assignment No 2
June 2020 4
Dbms Assignment No. 1
June 2020 4
Dbms Assignment 7.pdf
May 2020 9
Assignment No 2.docx
June 2020 10
Suzie's No 2 Assignment
May 2020 8