$20.99
Write SQL SELECT statements for each of the following queries: Solution
1. Write SQL SELECT statements for each of the following queries. You need to use the Computer DDL in the assignment folder to create tables in your own database.
1. How many employees are not using the software package that is over $500?
2. How many employee use the most expensive computer, in terms of the total cost of software package installed on the computer?
3. How many software are accounting but are not installed in the accounting department?
4. List all the information of the software package that has been installed the most (the highest number of installations)
5. List the name of the department that has more than 3 computers and more than 4 software installed in the last 3 years.
6. List the name of the department that uses Dell computer, or includes employees whose names are Tracey, or with accounting software installed.
7. Which manufacturer makes the computer that has the highest total amount of software installed in the last three years?
8. Which employee does not use the computer that has the highest total amount of software installed in the last three years?
9. Double the cost of the software package that is the most popular one, defined as the highest number of installations
10. Delete those employees who uses the computer that has accounting software but is not located in the accounting department.
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
Drop table software
Drop table pc
Drop table package
Drop table employee
Drop table computer
CREATE TABLE [employee] (
[empnum] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[empname] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[empphone] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[empnum]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [computer] (
[COMP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MFRNAME] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROCT] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[COMP]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [package] (
[PACK] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PACKNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PACKV] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAcKTYPE] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PACKCOST] [numeric](10, 2) NULL ,
PRIMARY KEY CLUSTERED
(
[PACK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [pc] (
[tagnum] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[comp] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[empnum] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[location] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [software] (
[PACK] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TAGNUM] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INSTDATE] [datetime] NULL ,
[SOFTCOST] [numeric](10, 2) NULL ,
) ON [PRIMARY]
GO
insert into package values ('AC11','Quick Accounting','4.1 ','Accounting ',754.95)
insert into package values ('AC12','Accounting MIS','4.0 ','Accounting ',2000.00)
insert into package values ('AC13','QuickBook','2005','Accounting ',300.00)
insert into package values ('DB11','Manta','1.5 ','Database ',380.00)
insert into package values ('DB13','SQL Server','2005','Database ',500.00)
insert into package values ('DB14','My SQL','2005','Database ',300.00)
insert into package values ('DB22','Manta','2.1 ','Database ',430.25)
insert into package values ('SS11','EasyCal','5.5 ','Spreadsheet ',225.15)
insert into package values ('WP04','Word Power','2 ','Word Processing ',118.00)
insert into package values ('WP07','Good Word ','3.2 ','Word Processing ',35.00)
insert into package values ('WP14','GOOGLE','2 ','Word Processing ',118.00)
insert into computer values ('C101','COMPAQ ','486DX ')
insert into computer values ('C102','COMPAQ ','PENTI ')
insert into computer values ('C103','COMPAQ ','PENTI ')
insert into computer values ('D111','Dell ','simm ')
insert into computer values ('D145','DELL ','486DX ')
insert into computer values ('D155','DELL ','486DX ')
insert into computer values ('D165','DELL MIC ','486DX ')
insert into computer values ('D245','DELL ','PENTI')
insert into computer values ('H120',NULL, NULL)
insert into computer values ('H125','HP ','486SX ')
insert into computer values ('H225','HP ','486DX ')
insert into employee values ('119','Robert Oden ','1312')
insert into employee values ('123','Douglas Daly','1213')
insert into employee values ('223','Tim Duncan','1213')
insert into employee values ('356','Tracy Yao','1214')
insert into employee values ('456','David Johnson','1214')
insert into employee values ('525','Tracy Sharp Jr','1311')
insert into employee values ('533','Tracy Sharp II','1412')
insert into employee values ('625','Tracy Sharp ','1311')
insert into employee values ('633','Tracy Johnson','1412')
insert into employee values ('911','Robert NoPC ','1312')
insert into pc values ('32807','D145', NULL,'Accounting ')
insert into pc values ('32808','D145','123','Sales ')
insert into pc values ('32809','C101','356','Sales ')
insert into pc values ('32810','C101','456','Accounting ')
insert into pc values ('37691','D155','625','Info Sys ')
insert into pc values ('37692','H125','456','Home ')
insert into pc values ('37693','H125', NULL,'Home ')
insert into pc values ('57772','H225','123','Info Sys ')
insert into pc values ('59836','H225','625','Info Sys ')
insert into pc values ('59837','H225','633','Info Sys ')
insert into pc values ('77739','C102','625',Null)
insert into pc values ('77740','C101','625','Accounting ')
insert into software values ('AC11','32807','1995-09-13 00:00:00',754.95)
insert into software values ('AC11','32809','1998-09-13 00:00:00',754.95)
insert into software values ('AC11','37691','1998-09-13 00:00:00',754.95)
insert into software values ('AC12','32809','1998-09-13 00:00:00',2000)
insert into software values ('DB11','32808','1996-12-03 00:00:00',380.00)
insert into software values ('DB11','37691','1995-06-15 00:00:00',380.00)
insert into software values ('DB22','37691','1997-05-27 00:00:00',430.25)
insert into software values ('DB22','57772','1997-05-27 00:00:00',430.25)
insert into software values ('WP04','32808','1996-01-12 00:00:00',180.50)
insert into software values ('WP04','37691','1995-06-15 00:00:00',180.50)
insert into software values ('WP04','57772','1998-05-27 00:00:00',180.50)
insert into software values ('WP07','59836','1995-10-30 00:00:00',35.00)
insert into software values ('WP07','77740','1995-05-27 00:00:00',70.00)
