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)

 

 

 

 

 

QUESTIONS:

 

 

1.      Create a procedure to find out the computer model (computer tables) installed in more than one department that have the accounting software installed.  

2.      Create a procedure to find out the information of the employee who uses the highest total amount of software package. 

3.      Create a procedure to find out all the information of the PCs that do not have the highest total amount of cost of a software package type, given the name of the type (e.g., accounting). Check the validity of the given type.

4.      Create a procedure to list all the information of employee, given the name of the employee (e.g., Tracey), who use more than three PCs made by one particular vendor, given the name of the vendor (e.g., Dell), or use a particular type of software package, given the type of the package (e.g., accounting). Check the validity of these given information.