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 : ASSIGNMENT2. TABLE: EMPLOYEEE EMPLOYEEE ASSIGNMENTEMP NU . EMP LNAM - EMP FNAM - EMP INITIA - EMP HIREDAT JOB COL - EMP YEAI -4101- 102E 103ASSIGN_NUM - ASSIGN_DATE. PROJ_NUM . EMP_NUM . ASSIGN_JOB . ASSIGN_CHG_HR- ASSIGN_CHARGE.295.75ASSIGN_HOURSNewsSeniorG08-Nov-00 50212 Jul-89 50101-Dec-96 503John6.100122-Mar-14 1810350384.503.5DavidH.17100222-Mar-14 2211750934 554.2145.11100322-Mar-14 1811750934.552.069.10ArboughJuneE10100422-Mar 14 1810350384.505.9498.55- 104RamorasAnneK15-Nov-87 50119- 105+ 106O 107- 108JohnsonSmithfieldAlonzoWashingtonAliceWilliam1005K01-Feb-93 5021422-Mar-14 2522-Mar-14 2222 Mar-14 2510850196.7596.7522212.85406.3510061045014.222-Jun-04 5002100711350850 7538192 85MariaD10-Oct-93 50013100822-Mar-14 1810350384.500.970.05RalphLarry22-Aug-91 50118-Jul-97 501B15100923-Mar-14 1511550196 755.6541.80+ 109- 110- 111+ 112Smith9101023 Mar-14 1511750934.552.482.92OlenkoWabash101123-Mar.14 25105502105.004.3451.50Gerald11-Dec-95 5051104-Apr-91 50623-Oct-94 507101223-Mar-14 1810850196.753.4328.95GeoffB15193.50270.90101323-Mar-14 2511550196.752.0SmithsonDarlene12101423-Mar-14 2210450198.752.8113JoenbroodDelbertK15-Nov-96 50810101523-Mar-14 1510350384.500.1515.45- 114E 1154 116JonesAnnelise20-Aug-93 50813101623-Mar-14 22105502105.004.7493.50BawangiTravisB25-Jan-92 5011523-Mar-14 1823 Mar-14 25101711750934.553.8131.29GeraldAngie101811750934.552276.01Pratt05-Mar-97 5101019-Jun-96 50924-Mar-14 2524-Mar-14 1524-Mar-14 2224-Mar-14 221019104501110.504.9541.45国117Williamson101020101502125.003.1387.50- 118FrommerJames04-Jan-05 51021021108501110.502.7298.351022115501110.504.9541.45437.50278. 85102324-Mar-14 22105502125.003.5102424-Mar-14 1510350384.503.3102524-Mar-14 1011750934.554.2145.113. TABLE: JOB曲 IORJOBJOB_COI . JOB DESCRIPTION- JOE CHO_HOL - JOB_LAST_UPDA -+500+ 50135.75ProgrammerSyslems AnalystDatabase DesignerElectical EngineerMecharical EngineerCivil EngineerClerical SupportDSS AnalystApplications DesignerBio TechnicianGeneral Support20-Nov-134. TABLE: PROJECT90.7520-Nuv-13+ 502125.0024-Mar-14田 503+ 504I PROJECTPROJ_NL. PROJ_NAME . PROJ_VALUE . PROJ_BALANC. EMP_NU.84.5020-Nav-1367.9020-Nov-13+50555.7820-Nov-13Cvergreon1453500.001002350.00 1032110346.00 108+ 50626.8720 Nov 13+ 18O 22+ 25Amber Wave3500500.00+ 5074 508+ 5094 51045.9520-Nov-13Roling Tide805000.00500345.20 10224-Mar- 1420-Nov-1320-Nov-1348.10Starfight2650500.002309880.00 10734.5518.36

The first query should validate the the ASSIGN_CHARGE values in the ASSIGNMENT table. ASSIGN_CHARGE…

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 SumOtASSIGN_HOURS SumOtASSIGN CHARGE News 3.1 387.50 1664.65 1218.70 101 Arbough Ramoras 103 19.7 11.9 12.5 8.3 3.8 12.5 18.8 104 105 Johnson 1382.50 Washington Joenbrood Bawangi Williamson 108 840. 15 192.85 1276.75 649.54 113 115 117

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 SumOtASSIGN_HOURS SumOtASSIGN CHARGE News 3.1 387.50 1664.65 1218.70 101 Arbough Ramoras 103 19.7 11.9 12.5 8.3 3.8 12.5 18.8 104 105 Johnson 1382.50 Washington Joenbrood Bawangi Williamson 108 840. 15 192.85 1276.75 649.54 113 115 117

Np Ms Office 365/Excel 2016 I Ntermed

1st Edition

ISBN:9781337508841

Author:Carey

Publisher:Carey

Chapter9: Working With Text Functions And Creating Custom Formats

Section: Chapter Questions

Problem 3RA

See similar textbooks

Similar questions

Please refer to the following diagram for Q19 to Q20. PERSON PK P_ID P_LNAME P_FNAME ΕMPLOYΕ P_ID EMP_HIRE_DATE STUDENT P ID STUDY ΜAIOR PK, FK1 PK, FK1 19. Which of the following statements is/are true? (1) A Person must be either a Student or an Employee. (2) A Person can neither be a Student nor an Employee. (3) A Person must be a Student or an Employee or both. a. (1) only b. (3) only c. (1) and (2) only d. (1), (2) and (3).
Access Assignment Problem: JMS TechWizards is a local company that provides technical services to several small businesses in the area. The company currently keeps its technicians and clients’ records on papers. The manager requests you to create a database to store the technician and clients’ information. The following table contains the clients’ information. Client Number Client Name Street City State Postal Code Telephone Number Billed Paid Technician Number AM53 Ashton-Mills 216 Rivard Anderson TX 78077 512-555-4070 $315.50 $255.00 22 AR76 The Artshop 722 Fisher Liberty Corner TX 78080 254-555-0200 $535.00 $565.00 23 BE29 Bert's Supply 5752 Maumee Liberty Corner TX 78080 254-555-2024 $229.50 $0.00 23 DE76 D & E Grocery 464 Linnell Anderson TX 78077 512-555-6050 $485.70…
herpever applicable 2. Title field in Instructor table should allow only Mr., Ms., Mrs., Dr., Prof. as values apply check constraints
Given the following relation that stores details of student registration: Stu_ID Name Town Province Course RegisteredDate S101 Malak Al Nadabi Al Khuwair Western ITDR2106 02-02-2020 S101 Malak Al Nadabi Al Khuwair Western ITDR1105 14-09-2019 S102 Nadar Al Hashli Daesit Southern ITDR1102 05-02-2020 S102 Nadar Al Hashli Daesit Southern ITDR1101 05-02-2020 S103 Basit Barkat Ruwi Central ITDR1105 06-02-2020 S104 Yeshan Al Khuwair Western ITDR2106 15-09-2019 If the functional dependencies are consistent with the above relation, put TRUE. If they are not, put FALSE. Stu_ID à Name, Town Town à Province Course à RegisteredDate RegisteredDate à Course Stu_ID, Course à RegisteredDate Stu_ID à Course Stu_ID
View 3 Seneca Courses Inc. Employee Forms All employee information for SCI is maintained on file forms. The following are sample employee information forms: EMP_ID 7839 EMP_ID 7654 EMP_NAME JOHN CHIU EMP_NAME ANDRE MARTIN SOC_INS 123-234-345 SOC_INS 678-345-123 POSITION MANAGER POSITION SALES SUPERVISOR SUPERVISOR JOHN CHIU HIREDATE November 13, 1999 HIREDATE September 24, 1999 EMP_ID 7434 EMP_ID 7435 EMP_NAME PARVEZ RAI EMP_NAME DANIEL CHOY SOC_INS 234-123-432 SOC_INS 345-312-234 POSITION SALES POSITION CLERK SUPERVISOR JOHN CHIU SUPERVISOR HIREDATE June 24, 1980 HIREDATE August 4, 1998 Business Rules: 1. SOC_INS must always be different for each employee. 2. EMP_NAME must always have a value. Normalize the above user view. Document all steps including UNF, 1NF, Dependencies, 2NF, and 3NF…
1. The data type for Borrower_name in Borrowers Table is: A. Short text C. Currency B. Long text D. Yes/No Borrowers Field Name Borrower ID Borrower Name Phone num Membership_activation Data Type AutoNumber Short Text Short Text Yes/No
Lab 8-1: KimTay Pet Supplies Task 6: Create the GET_INVOICE_DATE procedure to obtain the customer ID, first and last names of the customer, and the invoice date for the invoice whose number currently is stored in I_INVOICE_NUM. Place these values in the variables I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE respectively. When the procedure is called it should output the contents of I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE. IN SQL CODE. CUSTOMER CUST_ID FIRST_NAME LAST_NAME ADDRESS CITY STATE POSTAL EMAIL BALANCE CREDIT_LIMIT REP_ID 125 Joey Smith 17 Fourth St Cody WY 82414 jsmith17@example.com 80.68 500.00 05 182 Billy Rufton 21 Simple Cir Garland WY 82435 billyruff@example.com 43.13 750.00 10 227 Sandra Pincher 53 Verde Ln Powell WY 82440 spinch2@example.com 156.38 500.00 15 294 Samantha Smith 14 Rock Ln Ralston WY 82440 ssmith5@example.com 58.60 500.00 10 314 Tom Rascal 1 Rascal Farm Rd Cody WY 82414 trascal3@example.com 17.25 250.00 15 375 Melanie Jackson 42…
Use the following table to answer the question below: Table name: Customers Column Name Role Data Type Length Constraint customerID stores the ID of a customer Char 5 Primary key custFName stores the first name of a customer Varchar2 35 Cannot be null custLName Stores the last name of a customer Varchar2 35 Cannot be null custGender Store the gender of a customer Char 1 It should be ‘M’ or ‘F’ custDOB Stores the date of birth of a customer Date custIncome Store the income of a customer Number 4 Write a SQL query that displays the number of customers who born on the 16th day of a month . Write in SQL a query that displays the ids of the customers who have the highest income . Create a view named ‘customerDetails’ to hold all the details of customers whose first names start with ‘S’ and their incomes are between 500 and 1000 Omani Rials .
Table: Task Primary key: TaskID Foreign key: Team MemberID TaskID 1 2 3 Description Build login screen Implement inventory management Add logo to splahs 1 screen Duration StartDate 2022-05-06 4 10 TeamMemberID 2 1 2022-05-15 1 2022-05-06 2 Write a SQL statement that will add the below row to the Task table. TaskID Description Duration StartDate Team MemberID 3 4 Implement 2022-05-07 email notifications Write a SQL statement that will change the value of the Task description with ID 3 to "Add logo to splash screen".
Database Schema The schema for the Ch07_FACT database is shown below and should be used to answer the next several problems. Click this image to view it in its own tab. FIGURE P7.56 THE CH07_FACT ERD CHECKOUT PATRON PK Check Num PK Pat ID FK1 Book_Num FK2 Pat_ID Check_Out_Date Check_Due_Date Check_In_Date >0-----H- Pat_FName Pat LName Pat_Type BOOK AUTHOR PK Book_Num PK Au ID Book_Title Book_Year Book_Cost Book_Subject FK1 Pat_ID Au_FName Au_LName Au_BirthYear WRITES PK,FK1 Book Num PK,FK2 Au ID The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection of current technology e-books for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book,
Normalise the data : Table: Administrator Field Name Data Field Constraints Description size type varchar Admin ID 15 Primary Key Contains the Admin id Admin char 50 Not null Contains the Name Admin name Not null Contains email Email varchar 20 the Address char 10 Not null Contains the address Phone int 8 Not null Contains the Number phone number Table: Product Field Name Data type Field size Constraints Description Product ID varchar 20 Primary Key Contains the product id Product Name char 25 Not null Contains product name Category char 13 Not null Contains the category CustomerlD varchar 15 Foreign key Contains the customer id
Use the following table to answer the questions below: Table name: Customers Column Name Role Data Type Length Constraint customerID stores the ID of a Char 5 Primary key customer custFName stores the first name of a Varchar2 35 Cannot be null customer custLName Stores the last name of a Varchar2 35 Cannot be null customer custGender Store the gender of a Char 1 It should be M customer or 'F' custDOB Stores the date of birth of Date a customer custIncome Store the income of a Number customer a. Write in SQL a command that creates the table Customers according to the above description b. Write in SQL a statement that adds a new column named accountID of type Char(8). This column should be defined as a foreign key that relates the table Customers to the table Account c. Write in SQL a command that deletes the customers whose date of birth is after 22-Dec-1990 d. Write a SQL query that displays the number of customers who born on the 16th day of a month e. Write in SQL a query that…