Question

Table name: Package PACK PACKNAME            PACKV PAcKTYPE            PACKCOST ---- -------------      ------- -----        ------------- AC11 Quick Accounting     4.1   Accounting           754.95 AC12 Accounting MIS       4.0   Accounting          2000.00 AC13 QuickBook            2005 Accounting           300.00 DB11 Manta                1.5   Database             380.00 DB13 SQL Server           2005 Database             500.00 DB14 My SQL               2005 Database             300.00 DB22 Manta                2.1   Database             430.25 SS11 EasyCal              5.5   Spreadsheet          225.15 WP04 Word Power           2     Word Processing      118.00 WP07 Good Word            3.2   Word Processing      35.00 WP14 GOOGLE               2     Word Processing      118.00
Table name: Software PACK TAGNUM INSTDATE                SOFTCOST ---- ------ ---------------------------------- AC11 32807 1995-09-13 00:00:00.000 754.95 AC11 32809 1998-09-13 00:00:00.000 754.95 AC11 37691 1998-09-13 00:00:00.000 754.95 AC12 32809 1998-09-13 00:00:00.000 2000.00 DB11 32808 1996-12-03 00:00:00.000 380.00 DB11 37691 1995-06-15 00:00:00.000 380.00 DB22 37691 1997-05-27 00:00:00.000 430.25 DB22 57772 1997-05-27 00:00:00.000 430.25 WP04 32808 1996-01-12 00:00:00.000 180.50 WP04 37691 1995-06-15 00:00:00.000 180.50 WP04 57772 1998-05-27 00:00:00.000 180.50 WP07 59836 1995-10-30 00:00:00.000 35.00 WP07 77740 1995-05-27 00:00:00.000 70.00		Table name: Computer COMP MFRNAME    PROCT ---- ---------- ------ C101 COMPAQ     486DX C102 COMPAQ     PENTI C103 COMPAQ     PENTI D111 Dell       simm D145 DELL       486DX D155 DELL       486DX D165 DELL MIC   486DX D245 DELL       PENTI H120 NULL       NULL H125 HP         486SX H225 HP         486DX
Table name: Employee empnum empname         empphone ------ --------------- -------- 119    Robert Oden     1312 123    Douglas Daly    1213 223    Tim Duncan      1213 356    Tracy Yao       1214 456    David Johnson   1214 525    Tracy Sharp Jr 1311 533    Tracy Sharp II 1412 625    Tracy Sharp     1311 633    Tracy Johnson   1412 911    Robert NoPC     1312	Table name: PC tagnum comp empnum location ------ ---- ------ ------------- 32807 D145 NULL   Accounting          32808 D145 123    Sales                32809 C101 356    Sales               32810 C101 456    Accounting          37691 D155 625    Info Sys            37692 H125 456    Home                37693 H125 NULL   Home                57772 H225 123    Info Sys            59836 H225 625    Info Sys            59837 H225 633    Info Sys            77739 C102 625    NULL 77740 C101 625    Accounting

Write SQL SELECT statements for each of the following queries. You need to use the Computer DDL in the assignment folder to create COMPUTER tables in your own database.

1. Find out the number of employees who use more than one computer.
2. Find out the PCs which has the highest total cost of software installed on the PCs.
3. Find out all the information of the software package that is installed in more than one department.
4. List all the information of the employees who use more PCs than the average number of PCs used by all the employees and the total cost of the software installed on the PC is greater than the average cost of all the PCs.
5. How many employees are not using the software package that is over $500?
6. How many employee use the most expensive computer, in terms of the total cost of software package installed on the computer?
7. How many software are accounting but are not installed in the accounting department?
8. List the name of the department that has more than 3 computers and more than 4 software installed in the last 3 years.
9. Double the cost of the software package that has the highest cost.
10. Delete those employees who uses the computer that has accounting software but is not located in the accounting department.

Answer 1

1)select count(*)"Number Of Employeer" from PC group by empnum having count(*)>1;
2)Select Comp from PC where tagnum in(Select Tagnum from Software where softcost=(Select max(Softcost) from software);
3)Select * from Software inner join PC on Software.tagnum=PC.tagnum where Location in(Select Location from PC group by Location having count(*)>1);
4)select count(*)"Number Of Employeer" from PC,Software where Software.tagnum=PC.tagnum group by empnum having count(*)>avg(count(*) and sum(Softcost)>(Select avg(Softcost) from Software));
5)Select count(*) from Software where Softcost<300;
6)Select count(tagnum)"Number of Employee" from Software,PC group by empnum having sum(Softcost)=(Select max(sum(cost)) from Software,PC group by empnum));
7)Select Packname from Package outer join Software on Package.pack=Software.pack where Packtype='Accounting' and tagnum not in(Select tagnum from PC where Location='Accounting');
8)update Software set Softcost=softcost*2 where Softcost=(Select max(Softcost) from Software)
9)Delete from Package where Packname in(Select Packname from Package outer join Software on Package.pack=Software.pack where Packtype='Accounting' and tagnum not in(Select tagnum from PC where Location='Accounting'))
  

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........