Write SQL SELECT statements for each of the following queries: Solved

Write SQL SELECT statements for each of the following queries: Solved

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)

 

 

 

 

 

 

 
Powered by