Answer the questions using the tables below.
Note: Attributes COURSECREDIT and DURATION-VALIDITY have anumber data type, and all other attributes have atext data type. DURATION-VALIDITY refers to the time period (counted in number of semesters) during which a student must have taken the prerequisite in order for it to be considered as valid.
Consider the following data model:
COURSE
* courseid
coursedesc
coursecredit
COURSE Table
Primary key: COURSEID
Foreign keys: none
courseID
|
courseDesc
|
courseCredit
|
CSCI1111
|
Intro to Computers
|
4
|
EC101
|
Intro to Electronic Commerce
|
3
|
EC205
|
Management of Virtual Organizations
|
6
|
ENG1111
|
English1
|
2
|
FIN1111
|
FINANCE1
|
3
|
FIN3333
|
FINANCE2
|
3
|
FIN4444
|
Corporate Fin
|
3
|
FIN5555
|
Real XXXXXX
|
X
|
XXXXXXX
|
XXXXXXXXXXX
|
X
|
XXXXX
|
XXXXXX 1
|
X
|
XXXXXX
|
XXXXXXX Algebra
|
4
|
MA2222
|
XXXXXXXXX
|
4
|
XXXXXX XXXXX
Primary XXX: COURSEID &XXX; PREREQID
Foreign keys: XXXXXXXX (XXXXXXXXXX the XXXXXX table)
XXXXXXXX (XXXXXXXXXX XXX COURSE table)
XXXXXXXX
|
prereqID
|
XXXXXXXX-validity
|
XXXXX
|
EC101
|
X
|
XXXXXXX
|
XXXXXXXX
|
X
|
FIN3333
|
ENG1111
|
|
XXXXXXX
|
FIN1111
|
9
|
XXXXXXX
|
XXXXXX
|
XX
|
XXXXXXX
|
MA2222
|
XX
|
XXXXXXX
|
XXXXXXX
|
6
|
FIN5555
|
XXXXXXX
|
X
|
XXXXXXX
|
FIN3333
|
6
|
Question 1: XXXXX the XXXXXX XXXXX XXXXXXXXX XXX the XXXXXX table
XXXXX:
CREATE TABLE `XXXXXX` (
`XXXXXXXX` XXXXXXX(50) NOT XXXX,
`prereqid` XXXXXXX(50) XXX NULL,
`duration-validity` XXXXXXX,
INDEX (`XXXXXXXX`),
INDEX (`XXXXXXXX`),
PRIMARY XXX (`courseID`, `XXXXXXXX`),
FOREIGN KEY (courseid) XXXXXXXXXX Course(XXXXXXXX)
);
XXXXXXXX 2:
Write XXX SQL XX XXXXXXXX XXX list of XXX XXXXXXXXXXXX id(s) (prereqIDs) XXX the XXXXXX XXXX has the XXXXXXXXXXX “XXXXXXXXXX of XXXXXXX XXXXXXXXXXXXX”. [XXXX:XXXXXXX for XXX prerequisites (i.e., XXX prereq.prereqid) for a XXXXXX XXXX has Management of XXXXXXX XXXXXXXXXXXXX XX a XXXXXX XXXXXXXXXXX in the XXXXXX table with a corresponding XXXXXX.XXXXXXXX].
XXXXX:
XXXXXX Prereq.prereqid AS XXXXXXXX XXXX XXXXXX
XXXXX JOIN XXXXXX ON XXXXXX.courseid=Course.XXXXXXXX
WHERE XXXXXX.coursedesc="Management of Virtual XXXXXXXXXXXXX";
XXXXXXXXXX
|
XXXXXXXXXXXX
|
FINANCE2
|
X
|
Question 3: Write the SQL statement to XXXXXXXX XXX list of course descriptions and XXXXXX credits XX XXX the courses XXXX XXXX XXXXXXX X XX a prerequisite.
Query:
XXXXXX XXXXXXXXXX , coursecredit XXXX XXXXXX XXXXX courseID in (
SELECT XXXXXXXX XXXX PREREQ XXXXX PREREQID in (
XXXXXX courseID from course where XXXXXXXXXX = 'XXXXXXXX'));
XXXXXXXX 4:
Write the SQL XXXXXXXXX to generate XXX XX the XXXXXX XXXXXXXXXXXX regardless XX XXXX have XXXXXXXXXXXXX. If XXX XXXXXXX XX have prerequisites XXXX also XXXXXXX XXX prereqIDs XXX XXX associated XXXXXX description XX the XXXXXXXXXXXX.
Images Not Shown
XXXXX:
select XX.XXXXXXXXXXXX as MainCourse, XX.CourseID , cr.prereqID , XXXXXX.coursedesc as PrereqCourses FROM (SELECT course.coursedesc XX Main_Courses, XXXXXX.XXXXXXXX XX CourseID, XXXXXXXX FROM course LEFT JOIN XXXXXX XX course.courseid=XXXXXX.courseID) cr
XXXX XXXX course XX course.XXXXXXXX=cr.XXXXXXXX;
Consider XXX data model &XXX; sample XXXXXX below for XXX following questions.
XXXXXXXXXX
XXXXXXXXXX
Num_days
XXXXXX
* XXXXXXXXX
worker_name
XXXXXXXXX
XXXXXXXXXX
XXXXXXXX
* XXXXXXX
bldg_qlty_lv
XXXXXXXX X: XX which table would you create an XXXXXXXXX to represent XXX worker XXX XXXXXXXXXX a XXXXXXXX? Why? XXXXXX this XXXXXXXXX XX XXXXXXXXXXX XX a foreign key?
In XXX building table, I XXXXX XXXXXX an XXXXXXXXX XX XXXXXXXXX the worker XXX supervises a XXXXXXXX XXXXXXX XX this table, the buildings details are XXXXX and one XX XXX XXXXXXXXXXXXXXX XXXX XXX XX associated to XXX building XX the worker who XX a supervisor XXXX XXXXXXXXXX XXX XXXXXXXX.
The XXXXXXXXX should XX constraint as a foreign key because it XX the XXXX from XXXXXXX table.
XXXXX XXXXXX
XXXXXXX XXX: WORKER_ID
Foreign key: XXXX
WORKER_ID
|
WORKER_NAME
|
HRLY_RATE
|
XXXXXXXXXX
|
1235
|
XXXXXXX
|
XX.50
|
XXXXXXXX
|
XXXX
|
XXXX
|
13.75
|
Plumbing
|
XXXX
|
Garret
|
10.XX
|
XXXXXXX
|
XXXX
|
Mason
|
XX.40
|
Framing
|
XXXX
|
Smith
|
XX.00
|
XXXXXXXX
|
- etc. -
|
|
|
|
Table XXXXXXXXXX
Primary key: XXXXXXXXX + XXXXXXX
XXXXXXX XXXX: XXXXXXXXX (XXXXXXXXXX XXX WORKER XXXXX)
XXXXXXX (references the BUILDING Table)
WORKER_ID
|
XXXXXXX
|
XXXXXXXXXX
|
NUM_DAYS
|
XXXX
|
XXX
|
8/XX/XXXX
|
X
|
XXXX
|
321
|
X/X/2001
|
XX
|
XXXX
|
XXX
|
X/17/2001
|
XX
|
2920
|
460
|
9/XX/XXXX
|
18
|
XXXXX BUILDING
XXXXXXX XXX: XXXXXXX
Foreign key: XXXXXXXX (references the WORKER XXXXX)
BLDG_ID
|
BLDG_ADDRESS
|
BLDG_TYPE
|
XXXXXXXXXXXX
|
XXXXXXXX
|
321
|
XXX Elm
|
Office
|
XX
|
1412
|
XXX
|
456 XXXXX
|
Retail
|
XX
|
XXXX
|
515
|
789 Oak
|
XXXXXXXXX
|
XX
|
XXXX
|
XXX
|
1011 XXXXX
|
Office
|
2A
|
2920
|
Note#1:Attribute XXXXXXXXX in XXX XXXXXX table and NUM_DAYS in XXX XXXXXXXXXX table XXXX aXXXXXX data XXXX; XXXXXXXXXX in the XXXXXXXXXX table XXX adate data XXXX; XXX XXX other XXXXXXXXXX XXXX atext
XXXX#2: XXXXXX XXXX XX XXXX XXXX to keep XXXXX XX the XXXXXXX work assignments XXX XXXX XXXXXX (i.e., we XX not need to keep track of the history XX a worker’s XXXXXXXX work XXXXXXXXXXX on different buildings).
XXXXXXXX 6:
XXXXX the XXX XXXX to generate XXX XXXXXXX weekly salary, assume a XX XXXX work week (i.e., XXX XXX average of (hrly_rate *36) of all XXX “office” building supervisors. XXXXX XXX average weekly salary asXXXXXXXXXX's Average Weekly Salary.
"Supervisor's Average XXXXXX Salary"
|
427.5
|
|
|
Query:
SELECT AVG(XXXXXXXXX*36) XX 'XXXXXXXXXXX Average XXXXXX XXXXXX' XXXX XXXXXX where WORKER_ID in (
XXXXXX WORKER_ID XXXX ASSIGNMENT XXXXX XXXXXXX in (
XXXXXX XXXXXXX XXXX BUILDING where XXXXXXXXX = 'XXXXXX'));
XXXXXXXX X
Write XXX XXX XXXX XX generate the list XX XXXXXXX (by worker name) who XXX paid more than $15 an XXXX XXX possess one XX the XXXXXXXXX XXXXXX: Electric, XXXXXXX, XXXXXXX. Order the XXXXXX in descending order XX worker XXXX.
XXXXX:
XXXXXX XXXXXXXXXXX as XXXXXXXXXXX FROM XXXXXX
XXXXX Hrly_rate&XX;15
XXXXX XX Worker_name DESC;
Question 8:For each XXXXXXXX type (BLDG_TYPE), indicate XXX XXXXX number of XXXXXXX assigned, only if this XXXXX number is = X. XXXXX XXXX XXXXX asXXXXX XXXXXX XX XXXXXXXX XXXXXXX.
BLDG_TYPE
|
'XXXXX Number of XXXXXXXX XXXXXXX'
|
Office
|
X
|
Query:
SELECT BLDG_TYPE, COUNT(XXXXXXXX) XX 'Total XXXXXX of XXXXXXXX Workers'
From XXXXXXXX group by BLDG_TYPE having XXXXX(BLDG_SUP) = 3;
XXXXXXXX X:
Write the XXX XXXX XX XXXXXXXX XXX list of workers (by XXXXXX id XXX name) who supervise a building XXXXXXX being XXXXXXXX to it.
WORKER_ID
|
XXXXXXXXXXX
|
XXXX
|
XXXXX
|
XXXXX:
XXXXXX BLDG_SUP XX WORKER_ID , XXXXXXXXXXX as XXXXXXXXXXX
XXXX building INNER JOIN worker XX XXXXXXXX.bldg_sup=worker.worker_id
WHERE XXXXXXXX XXX IN (
SELECT XXXXXXXXXX.XXXXXXXXX FROM assignment
INNER JOIN XXXXXX XX XXXXXXXXXX.XXXXXXXXX=XXXXXX.worker_id);
XXXXXXXXXXX:
Images Not Shown
Images Not Shown
Images Not Shown
Images Not Shown
Images Not Shown
">