Problems are based on the following query: SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = '615' GROUP BY CUS_CODE; Assuming that you generate 15,000 invoices per month, what recommendation would you give the designer about the use of derived attributes?

Answered: Image /qna-images/answer/e3bfae51-4d8c-479c-8ce1-1f82f2641064.jpg

Problems are based on the following query: SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = '615' GROUP BY CUS_CODE; Assuming that you generate 15,000 invoices per month, what recommendation would you give the designer about the use of derived attributes?

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter4: Constraints

Section: Chapter Questions

Problem 1RQ: What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?

See similar textbooks

Similar questions

What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?
Assuming that you generate 15,000 invoices per month, what recommendation would you give the designer about the use of derived attributes? SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = '615' GROUP BY CUS_CODE;
Explain how attributes are transformed into columns. What column properties do you take into account when making the transformations?
Consider the following relations: EMP_NO EMP_NAME EMP_GRADE JOB_ID DEPTNO START_DATE EMP DEPTNO DEPTNAME LOC LOW_SAL HIGH_SAL DEPT Display the name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title. Display the employee name, employee No and start date of employees hired between February 1998, and May 1998. Order the query in ascending order by start date. Create a unique listing of all jobs that are in department 80. Include the location of the department in the output. Write a query to display job ID, total salary and employee name whose job_id contains “REP” and total salary is above 13000. Display the data in ascending order according to total salary. Create a query that displays the employees’ last names and indicates the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand dollars.
Refer to the dimension tables and EmployeeFact table below. Job Date Location EmployeeFact •JobID •DatelD •LocationID • ( OEmployeelD Title FullDate StreetAddress oJobID JobCode DayNumber City PostalCode OLocationID LevelCode MonthNumber oHireDatelD OBirthDatelD ) SalaryAmount BonusAmount MonthName State Year CountryCode EducationYears PerformanceRating The following query reports the employee with the highest salary amount per postal code for employees with level code 8. Complete the missing values. SELECT CountryCode, EmployeeID, MAX(_ _(A)_ FROM (B). Location, Job WHERE EmployeeFact.LocationID = Location.LocationID AND EmployeeFact.JobID = (C). AND = 8 GROUP (A) Ex: Identifier (B) (C)
Refer to the dimension tables and EmployeeFact table below. Job Date EmployeeFact •JobID •DatelD • ( OEmployeelD Title FullDate oJobID JobCode DayNumber OLocationID o StartDatelD o EndDatelD) SalaryAmount LevelCode MonthNumber MonthName Year BonusAmount EducationYears PerformanceRating The following query reports the highest bonus amount of all employees by year and job code. Complete the missing values. SELECT Date.Year, (A). MAX(__(B)__) FROM Date, Job, Date StartDate, Date EndDate, EmployeeFact WHERE Job.JobID = EmployeeFact.JobID %3D AND StartDate.DateID = EmployeeFact.StartDateID AND EndDate.DateID = EmployeeFact.EndDateID AND L(C)_ BETWEEN StartDate. Year AND EndDate. Year GROUP BY _(D)_; (A) Ex: Identifier (C) (B) (D)
Which normal form requires that, no non-key attribute should functionally depend on a part of the primary key? 3rd Normal Form 4th Normal Form 2nd Normal Form 1st Normal Form
The team wants to track potential list of users who may have forgotten their items on the cars. Therefore, you need to return USER_FIRST_NAME, USER_LAST_NAME, TRAVEL_START_TIME, TRAVEL_END_TIME information from the USERS and TRAVELS tables connected inside a JOIN statement by the USING function and USER_ID field
Task 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. Task: Create the DRIVER_STATUS function to create clusters of drivers based on experience. THIS CODE YOU SEE HERE IS WRONG. THERE IS AN ERROR AT LINE 2 DELIMITER < CREATE FUNCTION DRIVER_STATUS(int DRIVERID) RETURNS VARCHAR BEGIN DECLARE //to store number of travels Count int; //to store level Level VARCHAR; //Selects according to passed value SELECT COUNT(*) INTO Level FROM TRAVEL WHERE DRIVER_ID = DRIVERID; //returns level IF( Count > 4) THEN RETURN…
Task 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. SQL Database Test: SELECT DRIVER_ID, DRIVER_STATUS(DRIVER_ID) FROM DRIVERS Create the DRIVER_STATUS function Expected Results: DRIVER_ID DRIVER_STATUS(DRIVER_ID) 2001 MASTER 2002 ROOKIE 2003 PRO 2004 ROOKIE 2005 ROOKIE
Task 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. Task: Create the DRIVER_STATUS function to create clusters of drivers based on experience. (Create the DRIVER_STATUS function)
Task 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. Task: Create the DRIVER_STATUS function to create clusters of drivers based on experience.