NOTE: Use the attached .SQL to create tables and insert data.Relational DiagramDatabase name: Ch07_ConstructCoASSIGNMENTV ASSIGN NUMTable name: EMPLOYEEEMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_YEARSJOBV JOB CODEJOB DESCRIPTIONJOB CHG HOURJOB_LAST_UPDATEASSIGN DATEPROJ_NUMEMP NUM08-Nov-00 50212-Jul-89 50101-Dec-96 503122316101NewsJohnG102SeniorArboughRamorasDavidASSIGN JOR103JuneEASSIGN_CHG HRASSIGN_HOURS104Anne15-Nov-87 5012519819105JohnsonAliceK01-Feb-93 502ASSIGN_CHARGEEMPLOYEE9 EMP NUMEMP LNAMEEMP FNAMEEMP INITIALPROJECT9 PROI NUMPROJ NAMEPROJ_VALUEPROI_BALANCEEMP NUM106SmithfieldWilliam22-Jun-04 50010-Oct-93 500107AlonzoMaria22-Aug-91 50118-Jul-97 501108WashingtonSmithRalphLarryGeraldB2110915EMP HIREDATE110OlenkoA11-Dec-95 50517JOB CODEEMP VEARS04-Apr-91 50623-Oct-94 507111WabashGeoffB21112SmithsonDarlene181619M113DelbetAnneliseJoenbrood15-Nov-96 508Table name: JOB20-Aug-93 50825-Jan-92 50105-Mar-97 51019-Jun-96 50904-Jan-05 510114Jones115BawangiPrattTravisB20JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR JOB_LAST_UPDATE20-Nov-13116Gerald1516500ProgrammerSystems Analyst35.75117WilliamsonFrommerAngieJamesH50196.7520-Nov-13118J750224-Mar-14Database DesignerElectrical EngineerMechanical EngineerCivil EngineerClerical SupportDSS Analyst125.0050384.5020-Nov-1350467.9020-Nov-13Table name: ASSIGNMENT20-Νov-1320-Nov-1350555.7850626.87ASSIGN_NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHO_HR ASSIGN_HOURS ASSIGN_CHARGE50745.9520-Nov-13100122-Mar-14 1810350384.503.5295.75508Applications Designer48.1024-Mar-14100222-Mar-14 2211750934.5542145.1120-Nov-1320-Nov-13509Bio Technician34.55100322-Mar-14 1811750934.552.069.10510General Support18.36100422-Mar-14 1810350384.505.9498.55100522-Mar-14 2510850196.752221285Table name: PROJECTPROJ_NUM PROJ NAME PROJ_VALUE PROJ BALANCE EMP_NUM100622-Mar-14 2210450196.7542406.35100722-Mar-14 2511350850.753.8192.85100822-Mar-14 1810350384.500.976.0515Evergreen1453500.001002350.00 103100923-Mar-14 1511550196.755.6541.8018Amber Wave3500500.002110346.00 108101023-Mar-14 1511750934.552.482.92Rolling TideStarfight500345.20 1022309880.00 10722805000.00101123-Mar-14 25105502105.004.3451 50252650500.00101223-Mar-14 1810850196.753.4328.95101323-Mar-14 2511550196.7520193.50101423-Mar-14 2210450196.752.8270.90101523-Mar-14 1510350384.506.1515.45101623-Mar-14 22105502105.004.7493.5023-Mar-14 1823-Mar-14 2524-Mar-14 25101711750934.553.8131 29101811750950134.552276.011019104110.504.9541.45102024-Mar-14 15101502125.003.1387 50102124-Mar-14 22108501110.502.7298.35102224-Mar-14 22115501110.504.9541.45102324-Mar-14 22105502125.003.5437 50102424-Mar-14 1510350384.503.3278.85102524-Mar-14 1811750934.554.2145.11Write 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 (youneed to use the project name to search all employees)1.3. Write the SQL code that will produce the results shown as below.PROJ_NAMEPROJ_VALUEPROJ_BALANCE EMP LNAME EMP_FNAME EMP_INITIAL JOB_CODE JOB_DESCRIPTION JOB_CHG_HOURRolling TideEvergreenStarflightAmber WaveSystems AnalystProgrammerProgrammerSystems Analyst805000.00500345.20 SeniorDavidH50196.751453500.001002350.00 ArboughJuneE50035.752650500.002309880.00 AlonzoMariaD50035.753500500.002110346.00 Washington Ralph50196.75 4. Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENTtable. Note that ASSIGN_CHARGE is a derived attribute that is calculated bymultiplying ASSIGN_CHG_HR by ASSIGN_HOURS.5. Write the SQL code that will yield the total number of hours worked for eachemployee and the total charges stemming from those hours worked. The result isshown as below.EMP_NUM EMP_LNAME SumOfASSIGN_HOURS SumOfASSIGN_CHARGE101103104105108113387.501664.65News3.1ArboughRamoras19.711.91218.701382.50840.15192.85Johnson12.5WashingtonJoenbrood8.33.8115Bawangi12.51276.75117Williamson18.8649.546. Write the SQL code to produce the total number of hours and charges for each ofthe 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 aseither programmer or system analyst.8. Write the SQL code to produce the project name, project value, project balance andthe number of employees per project..** Please include your SQL codes and screenshots. You must run your code andget proper outcomes.

According to Bartleby rules, we are answering the first 3 questions. The queries are given below

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

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

See similar textbooks

Related questions

Q: ask 16: List the invoice number and invoice date for each invoice that was created for James…

A: The given is database problem where multiple tables are needed to join to get the required result.

Q: use the following data to add a default value unknown to the structure of part .descripti

A: The given ERD has two tables as: Detail_Line PART The relationship is: One details_line has one PART…

Q: question 9 please use the picture below to answer the following question Write a SQL statement to…

A: Query: The below query is used to create a table named “new_Invoice” which is exactly the same as…

Q: Task 4: Create a view named PROPERTY_OWNERS. It consists of the property ID, office number, square…

A: Answer is given below .

Q: In each question below, you will define a new table based on the following tables.

A: The SQL question involves creating tables and sentences based on parent-child relationships, dog…

Q: Write a SELECT statement that joins the Departments, Courses, and Instructors tables. This statement…

A: To achieve this using a SQL SELECT statement with JOIN operations, filtering for courses with three…

Q: Write a SELECT statement that uses a correlated subquery to return one row per customer,…

A: SYNTAX - SELECT cust.first_name, cust.last_name, o.order_id, o.order_date, o.total_quantityFROM…

Q: Based on the ERD below, write the SQL statement to display the average hourly job charge for a job.

A: Query to display the average hourly job charge for a job. To get average hourly job charge for all…

Q: please use the picture below to answer the following question Display employee number, employee…

A: SELECT EmployeeID, Emp_LName, HireDate FROM Employee e JOIN Position pWHERE e.Emp_LName like '%e%'…

Q: SQL FOR THERES TABLES

A: To write SQL query for the given table

Q: The command DESC DEPARTMENTS shows the following information Name Null? Type DEPARTMENT ID NOT NULL…

A: Let's see the answer

Q: Display customer number, customer name, and location for customers whose Customer_name starts with…

A: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. ORDER BY…

Q: The Driver Relationship team realized that maintaining driver IDs is difficult and requested an…

A: The objective of the question is to modify a database table to automatically increment the DRIVER_ID…

Q: productlines productLine VARCHAR(50) text:Description VARCHAR(4000) htmlDescription MEDIUMTEXT image…

A: A join query combines data from two or more database tables based on a related column, facilitating…

Q: The Magazine table contains three fields. The Cost field is numeric. The Code and Name fields…

A: SELECT * FROM MagazineWHERE Cost>amt1 AND Cost<amt2;

Q: Write a SQL statement to create a foreign key in table Invoice_Detail that refers to the…

A: Write a SQL statement to create a foreign key in table Invoice_Detail that refers to the…

Q: 7. Create the BOOKS table: Column Name Data Type Size Constraints BookID Number 5 PRIMARY KEY NOT…

A: SQL stands for Structured Query Language.

Q: question 6 please use the picture below to answer the following question Write a SQL statement to…

A: Explanation: “UNIQUE” constraint is used to ensure that the column having different values. The…

Q: Write a SQL statement that will display product ID with alias "Prod_ID" and produ name with alias…

A: Create a table Product using CREATE command. Product table will have the columns Product_Id,…

Q: question 11 please use the picture below to answer the following question Write a SQL statement to…

A: Query: The below query is used to modify the InvoiceDate to today’s data for the customer ID 10.…

Q: I need help with this SQL query: Write a MySQL statement to create a table named jobs including…

A: Hello student. Warm welcome from my side. Hope you are doing great. I will try my best to answer…

Q: . Please write SQL codes to query data from member tables based on below criteria Please write a…

A: A number is odd number if its remainder when divided by 2 is not equal to 0

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

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

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!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

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.

Similar questions

2. Write a SQL statement to insert a new user with userId ‘XXXXX’ into the users table and populate at least 5 other columns including column userName. Note: userName column is varchar(20) and its value must be unique in the table users. DO NOT insert into the “created” column but pay attention to it’s value after the insert Paste the SQL below.
Assume the PRODUCT table contains multiple rows. The following code would include: SELECT P_DESCRIPT, P_PRICE FROM PRODUCT WHERE P_CODE IN(SELECT P_CODE FROM LINE GROUP BY P_CODE HAVING COUNT(*) > 1); Group of answer choices A) Select the products that have been ordered before. B) Select the products that have been ordered at least once. C) Select the products that have been ordered at least twice. D) An error message.
For below DEPARTMENT table write a SQL query so that repeated group names are shown with quotation symbol like in above desired output For understanding logic, can you give simple example in Microsoft SQL Server Management Studio 18. Thank you.
Write the SQL code that will produce the results shown as below. 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. 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.
Task 3: The InstantRide Finance team wants to collect the price and discount information with the driver names for each travel in the system. You need to return the TRAVEL_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME, TRAVEL_PRICE, and TRAVEL_DISCOUNT information from the TRAVELS and DRIVERS tables combined over DRIVER_ID field with the ON keyword. Task: Calculate each user's price and discount information. (SQL Database Test)
SQL Server Please help answer question 5 Please type out the answer because I cannot read the last answer because the handwriting is not very clear. Thank you.
16. please use the picture below to answer the following question Write a SQL statement to display Invoice Number and total sale for each Invoice.
Q1. Please write a query statement from emp table to display employee's empno, ename, length of ename, number of 'S',and number of 'L'. SQL> SELECT empno AS "EmpNo", ename AS "Emp Name", ... AS "Name Length", .... AS "# Of S", .... AS "# Of L"FROM emp; [IMAGE below] Material to answer the question above: Material: https://drive.google.com/file/d/14OwWDIybjAIeuHCO7McupV08gJs9nB-8/view?usp=sharing https://drive.google.com/file/d/1npRH8JoJ6GYBzPU3iNjLXluvCyhr2tMu/view?usp=sharing
Create a Pl SQL package named EMP_PKG which will have the above three functions and procedures as follows: CALC_TAX - private CHECK_FNAME - Public AVG_SAL - Public
Based on the ERD below, display the first name, last name and employee initial of anyone whose first name starts with “A”. (Hint: You will use a “Like” phrase.)
question 8 please use the picture below to answer the following question Write a SQL statement to modify the Position table in order to define a constraint to ensure that the column position receives only values 'Sales Rep' or 'Manager'.