1. Write the SQL code that will list all employees with Job_cod and Job_description. 2. Write the SQL code to list all employees who are working on Evergreen project (you need to use the project name to search all employees) 3. Write the SQL code that will produce the results shown as below. JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR Systems Analyst Programmer Programmer Systems Analyst PROJ NAME PROJ_VALUEPROJ_BALANCE EMP_LNAME EMP_FNAME EMP_INITIAL Rolling Tide Evergreen Starflight Amber Wave 805000.00 500345.20 Senior David H 501 96.75 35.75 35.75 1453500.00 1002350.00 Arbough June 500 2650500.00 2309880.00 Alonzo Maria 500 3500500.00 2110346.00 Washington Ralph 501 96.75
1. Write the SQL code that will list all employees with Job_cod and Job_description. 2. Write the SQL code to list all employees who are working on Evergreen project (you need to use the project name to search all employees) 3. Write the SQL code that will produce the results shown as below. JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR Systems Analyst Programmer Programmer Systems Analyst PROJ NAME PROJ_VALUEPROJ_BALANCE EMP_LNAME EMP_FNAME EMP_INITIAL Rolling Tide Evergreen Starflight Amber Wave 805000.00 500345.20 Senior David H 501 96.75 35.75 35.75 1453500.00 1002350.00 Arbough June 500 2650500.00 2309880.00 Alonzo Maria 500 3500500.00 2110346.00 Washington Ralph 501 96.75
Chapter6: Additional Database Objects
Section: Chapter Questions
Problem 6HOA: A new table has been requested to support tracking automated emails sent to customers. Create the...
Related questions
Concept explainers
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Question
Transcribed Image Text:NOTE: Use the attached .SQL to create tables and insert data.
Relational Diagram
Database name: Ch07_ConstructCo
ASSIGNMENT
V ASSIGN NUM
Table name: EMPLOYEE
EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_YEARS
JOB
V JOB CODE
JOB DESCRIPTION
JOB CHG HOUR
JOB_LAST_UPDATE
ASSIGN DATE
PROJ_NUM
EMP NUM
08-Nov-00 502
12-Jul-89 501
01-Dec-96 503
12
23
16
101
News
John
G
102
Senior
Arbough
Ramoras
David
ASSIGN JOR
103
June
E
ASSIGN_CHG HR
ASSIGN_HOURS
104
Anne
15-Nov-87 501
25
19
8
19
105
Johnson
Alice
K
01-Feb-93 502
ASSIGN_CHARGE
EMPLOYEE
9 EMP NUM
EMP LNAME
EMP FNAME
EMP INITIAL
PROJECT
9 PROI NUM
PROJ NAME
PROJ_VALUE
PROI_BALANCE
EMP NUM
106
Smithfield
William
22-Jun-04 500
10-Oct-93 500
107
Alonzo
Maria
22-Aug-91 501
18-Jul-97 501
108
Washington
Smith
Ralph
Larry
Gerald
B
21
109
15
EMP HIREDATE
110
Olenko
A
11-Dec-95 505
17
JOB CODE
EMP VEARS
04-Apr-91 506
23-Oct-94 507
111
Wabash
Geoff
B
21
112
Smithson
Darlene
18
16
19
M
113
Delbet
Annelise
Joenbrood
15-Nov-96 508
Table name: JOB
20-Aug-93 508
25-Jan-92 501
05-Mar-97 510
19-Jun-96 509
04-Jan-05 510
114
Jones
115
Bawangi
Pratt
Travis
B
20
JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR JOB_LAST_UPDATE
20-Nov-13
116
Gerald
15
16
500
Programmer
Systems Analyst
35.75
117
Williamson
Frommer
Angie
James
H
501
96.75
20-Nov-13
118
J
7
502
24-Mar-14
Database Designer
Electrical Engineer
Mechanical Engineer
Civil Engineer
Clerical Support
DSS Analyst
125.00
503
84.50
20-Nov-13
504
67.90
20-Nov-13
Table name: ASSIGNMENT
20-Νov-13
20-Nov-13
505
55.78
506
26.87
ASSIGN_NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHO_HR ASSIGN_HOURS ASSIGN_CHARGE
507
45.95
20-Nov-13
1001
22-Mar-14 18
103
503
84.50
3.5
295.75
508
Applications Designer
48.10
24-Mar-14
1002
22-Mar-14 22
117
509
34.55
42
145.11
20-Nov-13
20-Nov-13
509
Bio Technician
34.55
1003
22-Mar-14 18
117
509
34.55
2.0
69.10
510
General Support
18.36
1004
22-Mar-14 18
103
503
84.50
5.9
498.55
1005
22-Mar-14 25
108
501
96.75
22
21285
Table name: PROJECT
PROJ_NUM PROJ NAME PROJ_VALUE PROJ BALANCE EMP_NUM
1006
22-Mar-14 22
104
501
96.75
42
406.35
1007
22-Mar-14 25
113
508
50.75
3.8
192.85
1008
22-Mar-14 18
103
503
84.50
0.9
76.05
15
Evergreen
1453500.00
1002350.00 103
1009
23-Mar-14 15
115
501
96.75
5.6
541.80
18
Amber Wave
3500500.00
2110346.00 108
1010
23-Mar-14 15
117
509
34.55
2.4
82.92
Rolling Tide
Starfight
500345.20 102
2309880.00 107
22
805000.00
1011
23-Mar-14 25
105
502
105.00
4.3
451 50
25
2650500.00
1012
23-Mar-14 18
108
501
96.75
3.4
328.95
1013
23-Mar-14 25
115
501
96.75
20
193.50
1014
23-Mar-14 22
104
501
96.75
2.8
270.90
1015
23-Mar-14 15
103
503
84.50
6.1
515.45
1016
23-Mar-14 22
105
502
105.00
4.7
493.50
23-Mar-14 18
23-Mar-14 25
24-Mar-14 25
1017
117
509
34.55
3.8
131 29
1018
117
509
501
34.55
22
76.01
1019
104
110.50
4.9
541.45
1020
24-Mar-14 15
101
502
125.00
3.1
387 50
1021
24-Mar-14 22
108
501
110.50
2.7
298.35
1022
24-Mar-14 22
115
501
110.50
4.9
541.45
1023
24-Mar-14 22
105
502
125.00
3.5
437 50
1024
24-Mar-14 15
103
503
84.50
3.3
278.85
1025
24-Mar-14 18
117
509
34.55
4.2
145.11
Write the SQL code that will list all employees with Job_cod and Job_description.
2. Write the SQL code to list all employees who are working on Evergreen project (you
need to use the project name to search all employees)
1.
3. Write the SQL code that will produce the results shown as below.
PROJ_NAME
PROJ_VALUEPROJ_BALANCE EMP LNAME EMP_FNAME EMP_INITIAL JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR
Rolling Tide
Evergreen
Starflight
Amber Wave
Systems Analyst
Programmer
Programmer
Systems Analyst
805000.00
500345.20 Senior
David
H
501
96.75
1453500.00
1002350.00 Arbough
June
E
500
35.75
2650500.00
2309880.00 Alonzo
Maria
D
500
35.75
3500500.00
2110346.00 Washington Ralph
501
96.75
Transcribed Image Text:4. Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT
table. Note that ASSIGN_CHARGE is a derived attribute that is calculated by
multiplying ASSIGN_CHG_HR by ASSIGN_HOURS.
5. Write the SQL code that will yield the total number of hours worked for each
employee and the total charges stemming from those hours worked. The result is
shown as below.
EMP_NUM EMP_LNAME SumOfASSIGN_HOURS SumOfASSIGN_CHARGE
101
103
104
105
108
113
387.50
1664.65
News
3.1
Arbough
Ramoras
19.7
11.9
1218.70
1382.50
840.15
192.85
Johnson
12.5
Washington
Joenbrood
8.3
3.8
115
Bawangi
12.5
1276.75
117
Williamson
18.8
649.54
6. Write the SQL code to produce the total number of hours and charges for each of
the projects represented in the ASSIGNMENT table. The result is shown as below.
7. Write the SQL code to display the information of employees who are working as
either programmer or system analyst.
8. Write the SQL code to produce the project name, project value, project balance and
the number of employees per project..
** Please include your SQL codes and screenshots. You must run your code and
get proper outcomes.
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
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning