By using the information from the first photo, use .SQL to create tables and insert the provided data for number 1-5. Include SQL codes and screenshots.

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
icon
Concept explainers
Question

By using the information from the first photo, use .SQL to create tables and insert the provided data for number 1-5. Include SQL codes and screenshots.

NOTE: Use the attached .SQL to create tables and insert data. Include SQL codes and screenshots
of your results.
500
501
502
503
504
505
506
507
508
509
510
Table name: JOB
JOB_CODE JOB DESCRIPTION
Programmer
Systems Analyst
Database Designer
Electrical Engineer
Mechanical Engineer
1923
15
108
18
22
JOB CODE
JOB DESCRIPTION
JOB CHG HOUR
JOB_LAST_UPDATE
25
EMPLOYEE
Relational Diagram
ASSIGNMENT
YASSIGN NUM
ASSIGN DATE
PROJ_NUM
Civil Engineer
Clerical Support
DSS Analyst
Applications Designer
Bio Technician
General Support
Table name: PROJECT
PROJ_NUM PROJ_NAME
Evergreen
Amber Wave
Rolling Tide
Starflight
EMP NUM
EMP UNAME
EMP_FNAME
EMP INITIAL
EMP HIREDATE
JOB CODE
EMP YEARS
JOB CHG HOUR JOB LAST UPDATE
35.75
20-Nov-13
96.75
20-Nov-13
125.00
24-Mar-14
84.50
20-Nov-13
67.90
20-Nov-13
55.78
20-Nov-13
26.87
20-Nov-13
45.95
20-Nov-13
48.10
24-Mar-14
34.55
20-Nov-13
18.36
20-Nov-13
EMP NUM
ASSIGN JOB
ASSIGN CHG HR
ASSIGN HOURS
ASSIGN CHARGE
PROJ_VALUE
1453500.00
3500500.00
805000.00
2650500.00
PROJ BALANCE EMP_NUM
1002350.00 103
2110346.00 108
500345.20 102
2309880.00 107
PROJECT
PROI 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
John
David
ne
Anne
Alice
William
Maria
Washington Ralph
Smith
Olenko
Wabash
Smithson
Joenbrood
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
News
Senior
Arbough
Ramoras
Johnson
Smithfield
Alonzo
Jones
Bawangi
Pratt
Williamson Angie
Frommer
James
Larry
Gerald
Geoff
Darlene
Delbert
Annelise
Travis
Gerald
22-Mar-14 18
22-Mar-14 22
22-Mar-14 18
22-Mar-14 18
22-Mar-14 25
22-Mar-14 22
22-Mar-14 25
22-Mar-14 18
23-Mar-14 15
23-Mar-14 15
23-Mar-14 25
23-Mar-14 18
23-Mar-14 25
23-Mar-14 22
23-Mar-14 15
23-Mar-14 22
23-Mar-14 18
23-Mar-14 25
24-Mar-14 25
24-Mar-14 15
24-Mar-14 22
24-Mar-14 22
24-Mar-14 22
24-Mar-14 15
24-Mar-14 18
6
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
Database name: Ch07_ConstructCo
503
509
509
503
501
501
508
503
501
509
502
501
501
501
503
502
509
509
501
502
501
501
502
503
509
CHEYY
G
K
K
DEWAEMY
K
PLIS
08-Nov-00 502
12-Jul-89 501
01-Dec-96 503
15-Nov-87 501
01-Feb-93 502
Table name: ASSIGNMENT
ASSIGN NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN JOB ASSIGN CHO HR ASSIGN HOURS ASSIGN_CHARGE
1001
84.50
34.55
34.55
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
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
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
42
102
20
59
22
42
3.8
DE
09
5.6
24
43
3.4
20
2.8
35797GHEARN32
6.1
3.8
22
49
433ANU
12
23
16
25
19
8
49
975
19
21
15
17
21
18
16
19
20
15
16
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
Transcribed Image Text:NOTE: Use the attached .SQL to create tables and insert data. Include SQL codes and screenshots of your results. 500 501 502 503 504 505 506 507 508 509 510 Table name: JOB JOB_CODE JOB DESCRIPTION Programmer Systems Analyst Database Designer Electrical Engineer Mechanical Engineer 1923 15 108 18 22 JOB CODE JOB DESCRIPTION JOB CHG HOUR JOB_LAST_UPDATE 25 EMPLOYEE Relational Diagram ASSIGNMENT YASSIGN NUM ASSIGN DATE PROJ_NUM Civil Engineer Clerical Support DSS Analyst Applications Designer Bio Technician General Support Table name: PROJECT PROJ_NUM PROJ_NAME Evergreen Amber Wave Rolling Tide Starflight EMP NUM EMP UNAME EMP_FNAME EMP INITIAL EMP HIREDATE JOB CODE EMP YEARS JOB CHG HOUR JOB LAST UPDATE 35.75 20-Nov-13 96.75 20-Nov-13 125.00 24-Mar-14 84.50 20-Nov-13 67.90 20-Nov-13 55.78 20-Nov-13 26.87 20-Nov-13 45.95 20-Nov-13 48.10 24-Mar-14 34.55 20-Nov-13 18.36 20-Nov-13 EMP NUM ASSIGN JOB ASSIGN CHG HR ASSIGN HOURS ASSIGN CHARGE PROJ_VALUE 1453500.00 3500500.00 805000.00 2650500.00 PROJ BALANCE EMP_NUM 1002350.00 103 2110346.00 108 500345.20 102 2309880.00 107 PROJECT PROI 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 John David ne Anne Alice William Maria Washington Ralph Smith Olenko Wabash Smithson Joenbrood 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 News Senior Arbough Ramoras Johnson Smithfield Alonzo Jones Bawangi Pratt Williamson Angie Frommer James Larry Gerald Geoff Darlene Delbert Annelise Travis Gerald 22-Mar-14 18 22-Mar-14 22 22-Mar-14 18 22-Mar-14 18 22-Mar-14 25 22-Mar-14 22 22-Mar-14 25 22-Mar-14 18 23-Mar-14 15 23-Mar-14 15 23-Mar-14 25 23-Mar-14 18 23-Mar-14 25 23-Mar-14 22 23-Mar-14 15 23-Mar-14 22 23-Mar-14 18 23-Mar-14 25 24-Mar-14 25 24-Mar-14 15 24-Mar-14 22 24-Mar-14 22 24-Mar-14 22 24-Mar-14 15 24-Mar-14 18 6 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 Database name: Ch07_ConstructCo 503 509 509 503 501 501 508 503 501 509 502 501 501 501 503 502 509 509 501 502 501 501 502 503 509 CHEYY G K K DEWAEMY K PLIS 08-Nov-00 502 12-Jul-89 501 01-Dec-96 503 15-Nov-87 501 01-Feb-93 502 Table name: ASSIGNMENT ASSIGN NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN JOB ASSIGN CHO HR ASSIGN HOURS ASSIGN_CHARGE 1001 84.50 34.55 34.55 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 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 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 42 102 20 59 22 42 3.8 DE 09 5.6 24 43 3.4 20 2.8 35797GHEARN32 6.1 3.8 22 49 433ANU 12 23 16 25 19 8 49 975 19 21 15 17 21 18 16 19 20 15 16 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
1. Write the SQL code that will list all information of employees with Job_code and
Job_description.
2. Write the SQL code to show job_descriptions available in the company.
3. Write the SQL code to show the difference between project value and project
balance, which will be shown as 'ROI'. This will be a derived attribute 'ROI'.
4. Write the SQL code to list all information of employees who are working on
Evergreen project (you need to join tables)
5. Write the SQL code to display the information of employees who are working as
either programmer or system analyst.
Transcribed Image Text:1. Write the SQL code that will list all information of employees with Job_code and Job_description. 2. Write the SQL code to show job_descriptions available in the company. 3. Write the SQL code to show the difference between project value and project balance, which will be shown as 'ROI'. This will be a derived attribute 'ROI'. 4. Write the SQL code to list all information of employees who are working on Evergreen project (you need to join tables) 5. Write the SQL code to display the information of employees who are working as either programmer or system analyst.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps with 7 images

Blurred answer
Knowledge Booster
Query Syntax
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.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education