6. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Yourquery should retrieve the assignment number, employee number, project number, the storedassignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated bymultiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.7. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hoursworked for each employee and the total charges stemming from those hours worked, sorted byemployee number. The result of running that query is shown below:EMP_NUM EMP LNAME SumOfASSIGN_HOURS SumOfASSIGN CHARGE101News3.1387.50Arbough19.711.912.58.33.81031664.65104Ramoras1218.70105Johnson1382. 50Washington840.15192.851276.75108113Joenbrood115117BawangiWilliamson12.518.8649.54 1. Table : ASSIGNMENTIASSIONVENTASON NM ASSIN SATEPROJ MM. EMP MM. ASSEN JO6 ASSIGN CHS ASSIGN HOURS ASSON CHAGE2 1422 Ma 14.222M1422M 1422M 14221-14 2222 M 14252Mar 142) Ma1423Mer 14100034.55422015 1134561001001000100100100064502242011040167550.25StartAPPCo database stores data for a consulting company that tracks all charges to projects. The14240011734.56charges are based on the hours each employee works on each project. The ASSIGNMENT table stores2Ma 1421Mer 142Me 14252Me 142501the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of thedata. The JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change will1013115501190 0216150187510354523-Mar14 2223Ma-142Ma-14 2524Mar 14 2524Me 14151060013 20be reflected in the ASSIGNMENT table. The structure and contents of the StartAPPCo database are30150210131shown below.10.50AM 142215110.505.0se 4524M142224Ma142M1433421511EMPLOYEEPK EMP NIMgorEMP LNAMEPK JOn_coDE2. TABLE: EMPLOYEEEMP FNAMEJOU DESCHTIONEMP INITIALaEMROVEEJOB OO HOUREMP RCDATEEN E . Eh MPA ,hewSerA reRaas AJt AeSmtteit Wm100lentJOB LAST UPOATEJOB CODE100104106106101914at fa sEMP_VLARSAkreWaing RuGehOekoWVar131510022Aup1s01HE110deadA11-Deca5 10+11116ASSIGNMENTSet Dat12113etrodDetertR11413T ASSGNNUM25 01tavrPstWamee ngeFranneTee1510102110GesdASSIGN DATE4.11719.in36500JumePROJECTPROJ NUMPK PROI NUMEMP MUM3. TABLE: JOBPROI NAMEASSGN KORPROI VALUEASSGN CHG HRPopamee AraDatere DespreCaca ge355PROI ALANCKASGN HOURShon1250024M20wEMP NUMASSIGN CHANGEeche agre20 t20w20oy 132024Mar.430 2020o-Clence Suppet26Aooimoe DesgrerTachscanC pat510184. TABLE: PROJECTE PROECTPRO RO NAE PROJ ALUE PR ALANC E NL1000 e 1021DS E IAmberSaig230 e10

Hey, since multiple questions posted, we will answer first question according to our policy. If you…

6. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.

6. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.

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: BOOK_NUM Times Checked Out BOOK_NUM TIMES Checked Out 5236 12 5236 12 No under score line 5235 5235…

A: SELECT BOOK_NUM, COUNT(check_out_DATE) AS "Times Checked Out " FROM CHECKOUT GROUP BY BOOK_NUM…

Q: CUSTOMER aus CODE CUS LNAME CUS FNAME CuS INITIAL CUS AREACODE CuS PHONE CUS BALANCE PRODUCT P cODE…

A: SQL (Structured Query Language) is a database query language. According to ANSI, it is the…

Q: Write the T-SQL scripts for the following tasks: Write a query to select the employees whose…

A: Please refer to the following step for the complete solution of the problem above.

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: Write a query to display the checkout number, book number, patron ID, checkout date, and due date…

A: The query follows the syntax given below. SELECT column1, column2 FROM table1 WHERE condition ORDER…

Q: 10. Produce a list of each customer number and the third and fourth digits of his or her zip code.…

A: The query for a list of each customer number and the third and fourth digits of his or her zipcode…

Q: 1. Write the SQL query to show all invoices (LGINVOICE) with a total (INV_TOTAL) of at least $100.…

A: Note: Due to company policies I am compelled to solve only one question and that is the first…

Q: b) Write a query to display the patron ID, last name, number of times that patron has ever checked…

A: Algorithm:Join Tables: Join the PATRON and CHECKOUT tables on the Pat_ID to associate patrons with…

Q: Write a query to display the checkout number, book number, and checkout date of all books checked…

A: The query follows the syntax given below.SELECT column1, column2, column3FROM table1WHERE…

Q: Using the following sql file, how would you find List all the rows in which the prescription dates…

A: List all the rows in which the prescription dates occur on or after November 14. --> Select *…

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

6. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your
query should retrieve the assignment number, employee number, project number, the stored
assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by
multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.
7. Using the data in the ASSIGNMENT table, 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, sorted by
employee number. The result of running that query is shown below:
EMP_NUM EMP LNAME SumOfASSIGN_HOURS SumOfASSIGN CHARGE
101
News
3.1
387.50
Arbough
19.7
11.9
12.5
8.3
3.8
103
1664.65
104
Ramoras
1218.70
105
Johnson
1382. 50
Washington
840.15
192.85
1276.75
108
113
Joenbrood
115
117
Bawangi
Williamson
12.5
18.8
649.54

1. Table : ASSIGNMENT
IASSIONVENT
ASON NM ASSIN SATEPROJ MM. EMP MM. ASSEN JO6 ASSIGN CHS ASSIGN HOURS ASSON CHAGE
2 14
22 Ma 14.22
2M14
22M 14
22M 142
21-14 22
22 M 1425
2Mar 14
2) Ma14
23Mer 14
1000
34.55
42
20
15 11
3456
100
100
1000
100
100
1000
6450
22
42
01
104
01
675
50.25
StartAPPCo database stores data for a consulting company that tracks all charges to projects. The
14
24
001
17
34.56
charges are based on the hours each employee works on each project. The ASSIGNMENT table stores
2Ma 14
21Mer 14
2Me 1425
2Me 142
501
the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the
data. The JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change will
1013
115
501
190 0
21
61
501
875
103
545
23-Mar14 22
23Ma-14
2Ma-14 25
24Mar 14 25
24Me 1415
10600
13 20
be reflected in the ASSIGNMENT table. The structure and contents of the StartAPPCo database are
301
502
101
31
shown below.
10.50
AM 1422
15
110.50
5.0
se 45
24M1422
24Ma14
2M14
33
42
1511
EMPLOYEE
PK EMP NIM
gor
EMP LNAME
PK JOn_coDE
2. TABLE: EMPLOYEE
EMP FNAME
JOU DESCHTION
EMP INITIAL
a
EMROVEE
JOB OO HOUR
EMP RCDATE
EN E . E
h MPA ,
hew
Ser
A re
Raas A
Jt Ae
Smtteit Wm
100
lent
JOB LAST UPOATE
JOB CODE
100
104
106
106
10
19
14
at fa s
EMP_VLARS
Akre
Waing Ru
Geh
Oeko
W
Var
13
15
100
22Aup1s01
HE110
dead
A
11-Deca5 10
+111
16
ASSIGNMENT
Set Dat
12
113
etrod
Detert
R114
13
T ASSGNNUM
25 01
tavr
Pst
Wamee nge
Franne
Tee
15
10
10
2
110
Gesd
ASSIGN DATE
4.117
19.in36500
Jume
PROJECT
PROJ NUM
PK PROI NUM
EMP MUM
3. TABLE: JOB
PROI NAME
ASSGN KOR
PROI VALUE
ASSGN CHG HR
Popame
e Ara
Datere Despre
Caca ge
355
PROI ALANCK
ASGN HOURS
hon
12500
24M
20w
EMP NUM
ASSIGN CHANGE
eche agre
20 t
20w
20oy 13
20
24Mar.
430 20
20o-
Clence Suppet
26
Aooimoe Desgrer
Tachscan
C pat
510
18
4. TABLE: PROJECT
E PROECT
PRO RO NAE PROJ ALUE PR ALANC E NL
1000 e 10
21DS E I
Amber
Saig
230 e10

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

SQL: For every invoice, display the invoice number, invoice date and the total dollar amount for all products purchased in the invoice, ordered by invoice number in descending order and then by invoice date in ascending order. Here you will use an aggregate SUM(x,y) function in your SELECT clause to calculate the total dollar amount (a calculated field – name it TOTAL). Don’t forget to join on the tables as well. [hint: you should have 8 rows of output ordered properly]. select inv_number, inv_date sum(list_price) as 'total' from invoice, line order by desc inv_num, asc inv_date. I am getting the error: ORA-00923: FROM keyword not found where expected Am I formulating the query incorrectly?
Employee table: (E_Id,E_name,dept_name,Salary) Write Update query Increase salary by 5000 if salary between 30000 to 35000 Increase salary by 7000 if salary between 35001 to 41000 Increase salary by 9000 if salary between 41001 to 46000
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
Insert into table customerPayment, columns customerOrderld and total result set returned from query a. Select columns id and total from table customerOrder where column orderStateld is equal to subquery i. (select column id from table orderState where column state is equal to "Payment received") Write Test Case 1 as a SQL join query to do the following a. Select using concatenation of columns firstName and lastName from table www customer as Customer b. Select customerOrderId from table customerPayment as "Customer Order" c. Select description from table product as Product d. Select total from table customerOrder as “Product Total" e. Join tables customerPavment, customerOrder, customer, product f. Order by column lastName from table customer Alter table purchaseOrder add column total as data type decimal size 7,2, not null, default value of 0.0