Table name Package PACK COST PACKV PACKTYPES PACK PACKNAME 754.95 4.1 Accounting
ID: 3817255 • Letter: T
Question
Table name Package PACK COST PACKV PACKTYPES PACK PACKNAME 754.95 4.1 Accounting AC11 Quick Accounting 2000.00 4.0 Accounting AC12 Accounting MIS 300.00 2005 Accounting AC13 Quick Book 380.00 Database DB11 Manta 500.00 2005 Database DB13 SQL Server 300.00 2005 Database DB14 My SQL 430.25 2.1 Database DB22 Manta. 225.15 5.5 Spreadsheet SS11 EasyCal 118.00 Word Processing WPO4 Word Power 35.00 3.2 Word Processing POT Good Word Word Processing 118.00 P14 GOOGLE Table name: Computer Table name Software COMP MFRNAME PROCT SOFT COST PACK TAGNUM INSTDATE 486DX. C101 COMPAQ 1995 09-13 00:00 00.000 754.95 AC11 32807 PENTI C102 COMPAQ 1998 09 13 00:00 00.000 754.95 AC11 32 809 PENTI C103 COMPAQ 1998 09 13 00:00 00.000 754.95 AC11 37691 1998 09-13 00:00:00.000 2000.00 D111 Dell Simm AC12 328 09 D145 DELL 486DX. 1996-12-03 00:00 00.000 380.00 DB11 32 808 D155 DELL 486 DX 1995 06 15 00:00 00.000 380.00 DB11 37691 D165 DELL MIC 486DX. 1997 05 27 00:00 00.000 430.25 DB22 37 691 PENTI 1997 05 27 00:00 00.000 430.25 D245 DELL DB22 57772 1996 01 12 00:00:00.000 180.50 NULL H 120 NULL WP04 32 808 486SX 1995 06 15 0 0 0 0 00.000 180 50 H 125 HP WP04 37691 486DX 1998 05 27 00:00 00.000 180 50 H225 HP WP04 57772 WP07 59 836 1995 10 30 00:00 00.000 35.00 1995-05-27 00:00 00.000 70.00 WP07 77740 Table name Employee Table name PC emp phone tagnum comp empnum location empnum emp name Robert Oden 119 13 12 32807 D1 45 NULL Accounting Douglas Daly 123 12 13 Sales 32808 D145 123 Tim Duncan 223 12 13 Sales 32809 C101 356 356 1214 Tracy Yao 32810 C101 456 Accounting 456 David Johnson 1214 37691 D155 625 Info Sys Tracy Sharp Jr 13 11 525 37692 H125 456 Home 533 Tracy Sharp II 1412 37693 H125 NULL Home 625 Tracy Sharp 13 11 57772 H225 123 Info Sys 633 Tracy Johnson 14 12 59836 H225 625 Info Sys 911 Robert NoPC 1312 Info Sys 59837 H225 633 77739 C102 625 NULL Accounting 77740 C101 625Explanation / Answer
Create PROCEDURE emp_info(
@mfrname varchar(200),
@packtype varchar(200),
@empname varchar(200)
)
AS
BEGIN
select e.* from Employees e
join
(select pc.empnum no
from pc
join software
on pc.tagnum=software.tagnum
join computer
on pc.comp=computer.comp
and computer.mfrname = @mfrname
group by pc.empnum,computer.mfrname
having count(computer.mfrname) >=3
union
select pc.empnum no
from pc
join software
on pc.tagnum=software.tagnum
join computer
on pc.comp=computer.comp
join package
on software.pack=package.pack
where package.packtype= @packtype) a
on e.empnum=a.no
where e.empname = @empname;
END
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.