Write the SQL code that counts the number of assignments ordered for each project. Call this returned column as “NUM_of_ASSIGNMENT”. Your query should also show the PROJ_NUM. Only show those projects that ordered more than 1 assignment and revise the order of your table by NUM_of_ASSIGNMENT from high to low. You will use the ASSIGNMENT table.
Write the SQL code that counts the number of assignments ordered for each project. Call this returned column as “NUM_of_ASSIGNMENT”. Your query should also show the PROJ_NUM. Only show those projects that ordered more than 1 assignment and revise the order of your table by NUM_of_ASSIGNMENT from high to low. You will use the ASSIGNMENT table.
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
Related questions
Question
1) Write the SQL code that counts the number of assignments ordered for each project. Call this returned column as “NUM_of_ASSIGNMENT”. Your query should also show the PROJ_NUM. Only show those projects that ordered more than 1 assignment and revise the order of your table by NUM_of_ASSIGNMENT from high to low. You will use the ASSIGNMENT table.
Transcribed Image Text:JOB
500
501
502
503
504
505
506
507
508
509
510
JOB_CODE
JOB DESCRIPTION
JOB CHG HOUR
JOB LAST UPDATE
15
18
22
25
Table name: JOB
JOB_CODE JOB DESCRIPTION JOB_CHG_HOUR
Programmer
Systems Analyst
Database Designer
Electrical Engineer
Mechanical Engineer
Relational Diagram
EMPLOYEE
Bio Technician
General Support
EMP NUM
EMP LNAME
EMP_FNAME
EMP INITIAL
EMP_HIREDATE
JOB_CODE
EMP YEARS
Civil Engineer
Clerical Support
DSS Analyst
Applications Designer
Rolling Tide
Starflight
Table name: PROJECT
PROJ_NUM PROJ_NAME PROJ_VALUE
Evergreen
1453500.00
Amber Wave
3500500.00
805000.00
2650500.00
ASSIGNMENT
35.75
96.75
125.00
84.50
67.90
55.78
26.87
45.95
48.10
34.55
18.36
ASSIGN NUM
ASSIGN_DATE
PROJ_NUM
EMP_NUM
ASSIGN JOB
ASSIGN_CHG_HR
ASSIGN HOURS
ASSIGN CHARGE
JOB_LAST_UPDATE
20-Nov-15
20-Nov-15
24-Mar-16
20-Nov-15
20-Nov-15
20-Nov-15
20-Nov-15
20-Nov-15
24-Mar-16
20-Nov-15
20-Nov-15
PROJ_BALANCE EMP_NUM
1002350.00 103
2110346.00 108
500345.20 102
2309880.00 107
PROJECT
PROJ NUM
PROJ_NAME
PROJ_VALUE
PROJ_BALANCE
EMP_NUM
Table name: EMPLOYEE
EMP NUM EMP LNAME EMP_FNAME EMP INITIAL EMP HIREDATE JOB_CODE EMP YEARS
News
Senior
Arbough
Ramoras
Johnson
Smithfield
Alonzo
Washington
Smith
Olenko
Wabash
Smithson
Joenbrood
Jones
Bawangi
Pratt
101
102
103
104
105
106
107
108
109
110
111
112
113
===
34
114
115
116
117
118
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
Williamson
Frommer
22-Mar-16 18
22-Mar-16 22
22-Mar-16 18
22-Mar-16 18
22-Mar-16 25
22-Mar-16 22
22-Mar-16 25
22-Mar-16 18
23-Mar-16 15
23-Mar-16 15
23-Mar-16 25
23-Mar-16 18
23-Mar-16 25
23-Mar-16 22
23-Mar-16 15
23-Mar-16 22
23-Mar-16 18
23-Mar-16 25
24-Mar-16 25
24-Mar-16 15
24-Mar-16 22
24-Mar-16 22
John
David
June
Anne
Alice
William
Maria
Ralph
Larry
Gerald
Geoff
Darlene
Delbert
Annelise
Travis
Gerald
Angie
James
24-Mar-16 22
24-Mar-16 15
24-Mar-16 18
103
117
117
103
108
104
113
103
115
117
105
108
115
104
103
105
117
117
104
101
108
115
105
103
117
503
509
509
503
501
501
508
503
501
509
502
501
501
501
Database name: Ch07_ConstructCo
Table name: ASSIGNMENT
ASSIGN_NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN JOB ASSIGN_CHG_HR ASSIGN HOURS ASSIGN_CHARGE
503
502
509
509
501
502
501
501
502
503
509
ОТ ШУУ
H
E
DBW AB EX
K
BLH¬
08-Nov-00 502
12-Jul-89 501
01-Dec-96 503
15-Nov-87 501
01-Feb-93 502
22-Jun-04 500
10-Oct-93 500
22-Aug-91 501
18-Jul-97 501
11-Dec-95 505
04-Apr-91 506
23-Oct-94 507
15-Nov-96 508
20-Aug-93 508
25-Jan-92 501
05-Mar-97 510
19-Jun-96 509
04-Jan-05 510
84.50
34.55
34.55
84.50
96.75
96.75
50.75
84.50
96.75
34.55
105.00
96.75
96.75
96.75
84.50
105.00
34.55
34.55
110.50
125.00
110.50
110.50
125.00
84.50
34.55
3.5
4.2
2.0
5.9
2.2
4.2
3.8
0.9
5.6
2.4
4.3
3.4
2.0
2.8
6.1
4.7
3.8
22
4.9
3.1
12
2.7
4.9
3.5
3.3
4.2
365198191571旧619 0 15 167
23
25
21
21
20
295.75
145.11
69.10
498.55
212.85
406.35
192.85
76.05
541.80
82.92
451.50
328.95
193.50
270.90
515.45
493.50
131.29
76.01
541.45
387.50
298.35
541.45
437.50
278.85
145.11
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY