Write the SQL to create a view called CommissionedSalaries to display the calculated salaries (including commission)...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Write the SQL to create a view called CommissionedSalaries to display the calculated salaries (including commission) of employees that receive a commission. Your results should include the following employee details: EMPLOYEE_ID LAST_NAME FIRST_NAME DEPARTMENT_NAME JOB TITLE SALARY COMMISSION_PCT COMMISSIONED_SALARY Sort your results by the employee details and commissioned salary (descending). Write the SQL to create a view called Manager EmployeeCount to display the number of employees a manager manages. Your results should include the following details: EMPLOYEE ID LAST NAME FIRST NAME DEPARTMENT_NAME JOB_TITLE EMPLOYEE COUNT Sort your results by the manager details and employee count (descending). Write the SQL to create a view called ManagerEmployeeList to display the employee details for the employees a manager manages. Your results should include the following details: EMPLOYEE ID LAST NAME FIRST NAME DEPARTMENT_NAME JOB_TITLE Sort your results by the manager details. Write the SQL to delete the Salaries View from the database. Commissioned Write the SQL to create a synonym for the employees table named MANAGERS. Write a SQL statement to display employees' IDs and names along with their manager's ID and name using the MANAGERS synonym you created in previous question. Sort your results by the managers' last names and the employees' last names. Write the SQL to create an index named IX_BOOK_NAME for the BOOK_TITLE_column in the BOOKS table. Write the SQL to create an index named IX_CUST_NAME for the the LAST NAME and FIRST NAME columns in the CUSTOMERS table. Write the SQL to create an index named IX_ORDER_DATE for the ORDER_DATE and CUSTOMER_ID columns in the ORDERS table. Describe how the addition IX_BOOK_NAME, IX_ORDER_DATE performance. IX_CUST_NAME, indexes affect of the and query F * Dow EMP_DETAILS_VIEW EMPLOYEE_ID JOB_TITLE MANAGER_ID EMPLOYEES DEPARTMENTS JOBS DEPARTMENT_NAME FIRST NAME LAST_NAME SALARY * COMMISSION_PCT P COUNTRY_ID REGIONS REGION_ID REGION_NAME PK_REG_ID (REGION_ID) NUMBER (6) VARCHAR2 (35) NUMBER (6) VARCHAR2 (30) VARCHAR2 (20) VARCHAR2 (25) NUMBER (8,2) NUMBER (2,2) NUMBER VARCHAR2 (25) COUNTRIES CHAR (2) COUNTRY_NAME VARCHAR2 (40) REGION_ID NUMBER PK_COUNTRY_C_ID (COUNTRY_ID) FK_COUNTR_REG (REGION_ID) F F * KF PK_JOBS (JOB_ID) * * JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY * * JOBS VARCHAR2 (10) VARCHAR2 (35) NUMBER (6) NUMBER (6) MANAGER_ID LOCATION_ID DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME PK_DEPT (DEPARTMENT_ID) FK_DEPT_LOC (LOCATION_ID) FK_DEPT_MGR (MANAGER_ID) IX_DEPT_LOCATION (LOCATION_ID) V P LOCATION_ID STREET_ADDRESS POSTAL CODE CITY NUMBER (4) VARCHAR2 (30) NUMBER (6) NUMBER (4) LOCATIONS STATE_PROVINCE COUNTRY_ID NUMBER (4) VARCHAR2 (40) VARCHAR2 (12) VARCHAR2 (30) VARCHAR2 (25) CHAR (2) PK_LOC_ID (LOCATION_ID) FK_LOC_C_ID (COUNTRY_ID) IX_LOC_CITY (CITY) IX_LOC_COUNTRY (COUNTRY_ID) IX_LOC_STATE_PROVINCE (STATE_PROVINCE) Small Company ERD F K * EMPLOYEE_ID START_DATE END_DATE JOB_ID JOB_HISTORY VARCHAR2 (10) NUMBER (4) DEPARTMENT_ID PK_JOB_HISTORY (EMPLOYEE_ID, START_DATE) FK_JOB_HIST_DEPT (DEPARTMENT_ID) FK_JOB_HIST_JOB (JOB_ID) NUMBER (6) DATE DATE IX_JOB_HIST_DEPT (DEPARTMENT_ID) IX_JOB_HIST_EMP (EMPLOYEE_ID) IX_JOB_HIST_JOB (JOB_ID) CITY STATE CUSTOMER_ID FIRST_NAME LAST_NAME STREET_ADDRESS CUSTOMERS NUMBER (6) VARCHAR2 (25) VARCHAR2 (25) VARCHAR2 (50) VARCHAR2 (50) VARCHAR2 (50) VARCHAR2 (10) CHAR (50) VARCHAR2 (25) VARCHAR2 (50) DATE VARCHAR2 (1) ZIP_CODE COUNTRY PHONE NUMBER EMAIL DATE OF BIRTH GENDER PK_CUSTOMERS (CUSTOMER_ID) U F F IF EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL EMPLOYEES PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID BONUS * PK_EMPLOYEES (EMPLOYEE_ID) UK_EMP_EMAIL (EMAIL) NUMBER (6) VARCHAR2 (20) VARCHAR2 (25) VARCHAR2 (25) VARCHAR2 (20) DATE VARCHAR2 (10) NUMBER (8,2) NUMBER (2,2) NUMBER (6) NUMBER (4) VARCHAR2 (5) FK_EMP_DEPT (DEPARTMENT_ID) FK_EMP_JOB (JOB_ID) FK_EMP_MGR (MANAGER_ID) IX_EMP_DEPT (DEPARTMENT_ID) IX_EMP_JOB (JOB_ID) IX_EMP_MGR (MANAGER_ID) IX EMP_NAM (LAST_NAME, FIRST_NAME) P ORDER_NBR F CUSTOMER_ID ORDER_DATE ORDER_TOTAL NUMBER (12) NUMBER (6) TIMESTAMP NUMBER (8,2) F SALESPERSON_ID NUMBER (6) ORDERS www PK_ORDERS (ORDER_NBR) FK_ORD_CUST (CUSTOMER_ID) FK_ORD_EMP (SALESPERSON_ID) * BOOK_CATEGORY_CD BOOK_CATEGORY_NAME BOOK_CATEGORY_DESCRIPTION * PK_BOOK_CATEGORY (BOOK_CATEGORY_CD) * BOOK_CATEGORY BOOK_ID BOOK_TITLE BOOK_DESCRIPTION BOOK_CATEGORY_CD BOOKS BOOK_PRICE BOOK REVIEWS USER_RATING PK_BOOKS (BOOK_ID) UNIT_PRICE QUANTITY CHAR (2) VARCHAR2 (100) VARCHAR2 (2000) ORDER_ITEM_ID UF* ORDER_NBR UF* BOOK_ID FK_BOOK_BOOK_CAT (BOOK_CATEGORY_CD) NUMBER (6) VARCHAR2 (250) VARCHAR2 (2000) CHAR (2) NUMBER (8,2) NUMBER (5) NUMBER (2,1) ORDER_ITEMS NUMBER (20) NUMBER (12) NUMBER (6) NUMBER (8,2) NUMBER (8) PK_ORDER_ITEMS (ORDER_ITEM_ID) UK_ORD ITM (ORDER_NBR, BOOK_ID) FK_ORD_ITM_ORD (ORDER_NBR) FK_ORD_ITM_PROD (BOOK_ID) Write the SQL to create a view called CommissionedSalaries to display the calculated salaries (including commission) of employees that receive a commission. Your results should include the following employee details: EMPLOYEE_ID LAST_NAME FIRST_NAME DEPARTMENT_NAME JOB TITLE SALARY COMMISSION_PCT COMMISSIONED_SALARY Sort your results by the employee details and commissioned salary (descending). Write the SQL to create a view called Manager EmployeeCount to display the number of employees a manager manages. Your results should include the following details: EMPLOYEE ID LAST NAME FIRST NAME DEPARTMENT_NAME JOB_TITLE EMPLOYEE COUNT Sort your results by the manager details and employee count (descending). Write the SQL to create a view called ManagerEmployeeList to display the employee details for the employees a manager manages. Your results should include the following details: EMPLOYEE ID LAST NAME FIRST NAME DEPARTMENT_NAME JOB_TITLE Sort your results by the manager details. Write the SQL to delete the Salaries View from the database. Commissioned Write the SQL to create a synonym for the employees table named MANAGERS. Write a SQL statement to display employees' IDs and names along with their manager's ID and name using the MANAGERS synonym you created in previous question. Sort your results by the managers' last names and the employees' last names. Write the SQL to create an index named IX_BOOK_NAME for the BOOK_TITLE_column in the BOOKS table. Write the SQL to create an index named IX_CUST_NAME for the the LAST NAME and FIRST NAME columns in the CUSTOMERS table. Write the SQL to create an index named IX_ORDER_DATE for the ORDER_DATE and CUSTOMER_ID columns in the ORDERS table. Describe how the addition IX_BOOK_NAME, IX_ORDER_DATE performance. IX_CUST_NAME, indexes affect of the and query F * Dow EMP_DETAILS_VIEW EMPLOYEE_ID JOB_TITLE MANAGER_ID EMPLOYEES DEPARTMENTS JOBS DEPARTMENT_NAME FIRST NAME LAST_NAME SALARY * COMMISSION_PCT P COUNTRY_ID REGIONS REGION_ID REGION_NAME PK_REG_ID (REGION_ID) NUMBER (6) VARCHAR2 (35) NUMBER (6) VARCHAR2 (30) VARCHAR2 (20) VARCHAR2 (25) NUMBER (8,2) NUMBER (2,2) NUMBER VARCHAR2 (25) COUNTRIES CHAR (2) COUNTRY_NAME VARCHAR2 (40) REGION_ID NUMBER PK_COUNTRY_C_ID (COUNTRY_ID) FK_COUNTR_REG (REGION_ID) F F * KF PK_JOBS (JOB_ID) * * JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY * * JOBS VARCHAR2 (10) VARCHAR2 (35) NUMBER (6) NUMBER (6) MANAGER_ID LOCATION_ID DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME PK_DEPT (DEPARTMENT_ID) FK_DEPT_LOC (LOCATION_ID) FK_DEPT_MGR (MANAGER_ID) IX_DEPT_LOCATION (LOCATION_ID) V P LOCATION_ID STREET_ADDRESS POSTAL CODE CITY NUMBER (4) VARCHAR2 (30) NUMBER (6) NUMBER (4) LOCATIONS STATE_PROVINCE COUNTRY_ID NUMBER (4) VARCHAR2 (40) VARCHAR2 (12) VARCHAR2 (30) VARCHAR2 (25) CHAR (2) PK_LOC_ID (LOCATION_ID) FK_LOC_C_ID (COUNTRY_ID) IX_LOC_CITY (CITY) IX_LOC_COUNTRY (COUNTRY_ID) IX_LOC_STATE_PROVINCE (STATE_PROVINCE) Small Company ERD F K * EMPLOYEE_ID START_DATE END_DATE JOB_ID JOB_HISTORY VARCHAR2 (10) NUMBER (4) DEPARTMENT_ID PK_JOB_HISTORY (EMPLOYEE_ID, START_DATE) FK_JOB_HIST_DEPT (DEPARTMENT_ID) FK_JOB_HIST_JOB (JOB_ID) NUMBER (6) DATE DATE IX_JOB_HIST_DEPT (DEPARTMENT_ID) IX_JOB_HIST_EMP (EMPLOYEE_ID) IX_JOB_HIST_JOB (JOB_ID) CITY STATE CUSTOMER_ID FIRST_NAME LAST_NAME STREET_ADDRESS CUSTOMERS NUMBER (6) VARCHAR2 (25) VARCHAR2 (25) VARCHAR2 (50) VARCHAR2 (50) VARCHAR2 (50) VARCHAR2 (10) CHAR (50) VARCHAR2 (25) VARCHAR2 (50) DATE VARCHAR2 (1) ZIP_CODE COUNTRY PHONE NUMBER EMAIL DATE OF BIRTH GENDER PK_CUSTOMERS (CUSTOMER_ID) U F F IF EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL EMPLOYEES PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID BONUS * PK_EMPLOYEES (EMPLOYEE_ID) UK_EMP_EMAIL (EMAIL) NUMBER (6) VARCHAR2 (20) VARCHAR2 (25) VARCHAR2 (25) VARCHAR2 (20) DATE VARCHAR2 (10) NUMBER (8,2) NUMBER (2,2) NUMBER (6) NUMBER (4) VARCHAR2 (5) FK_EMP_DEPT (DEPARTMENT_ID) FK_EMP_JOB (JOB_ID) FK_EMP_MGR (MANAGER_ID) IX_EMP_DEPT (DEPARTMENT_ID) IX_EMP_JOB (JOB_ID) IX_EMP_MGR (MANAGER_ID) IX EMP_NAM (LAST_NAME, FIRST_NAME) P ORDER_NBR F CUSTOMER_ID ORDER_DATE ORDER_TOTAL NUMBER (12) NUMBER (6) TIMESTAMP NUMBER (8,2) F SALESPERSON_ID NUMBER (6) ORDERS www PK_ORDERS (ORDER_NBR) FK_ORD_CUST (CUSTOMER_ID) FK_ORD_EMP (SALESPERSON_ID) * BOOK_CATEGORY_CD BOOK_CATEGORY_NAME BOOK_CATEGORY_DESCRIPTION * PK_BOOK_CATEGORY (BOOK_CATEGORY_CD) * BOOK_CATEGORY BOOK_ID BOOK_TITLE BOOK_DESCRIPTION BOOK_CATEGORY_CD BOOKS BOOK_PRICE BOOK REVIEWS USER_RATING PK_BOOKS (BOOK_ID) UNIT_PRICE QUANTITY CHAR (2) VARCHAR2 (100) VARCHAR2 (2000) ORDER_ITEM_ID UF* ORDER_NBR UF* BOOK_ID FK_BOOK_BOOK_CAT (BOOK_CATEGORY_CD) NUMBER (6) VARCHAR2 (250) VARCHAR2 (2000) CHAR (2) NUMBER (8,2) NUMBER (5) NUMBER (2,1) ORDER_ITEMS NUMBER (20) NUMBER (12) NUMBER (6) NUMBER (8,2) NUMBER (8) PK_ORDER_ITEMS (ORDER_ITEM_ID) UK_ORD ITM (ORDER_NBR, BOOK_ID) FK_ORD_ITM_ORD (ORDER_NBR) FK_ORD_ITM_PROD (BOOK_ID)
Expert Answer:
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these databases questions
-
One of your colleagues has designed a new transdermal patch to deliver insulin through the skin to diabetic Patients in a controlled way, eliminating the need for painful injections. She has...
-
The back emf in a motor is 72 V when operating at 1800 rpm. What would be the back emf at 2300 rpm if the magnetic field is unchanged?
-
For the following exercises, use the formula for the sum of the first n terms of a geometric series to find the partial sum. 10 - n=1 -2. 2 n-1
-
Continuing to focus on evidence associated with the act, concealment, and conversion, use the evidentiary material to continue the examination. In addition, as the examiner also start to think of...
-
Deborah Kellogg buys Breathalyzer test sets for the Denver Police Department. The quality of the test sets from her two suppliers is indicated in the following table: For example, the probability of...
-
Charles holds a one-year $1000face value, taxable bond with a coupon rate of 7%. Suppose he faces a tax rate of 32%. How much tax will he pay for income earned on the investment? And, assuming he...
-
Mimi Couturier is a design company that specializes informalwear for women. The company's fashion innovators usecomputer-assisted design software to create what they think womenshould wear. The...
-
( a ) What is centralization; with examples discuss advantages and disadvantages of centralized system. b ) The information for two mutually exclusive projects are given belowProject AInitial...
-
The following information is consumption and investment data for a closed economy. Figures are in billions of dollars. C = 60+.6Y 1 = 16 = 30 29. Refer to equations above. The equilibrium level of...
-
Jason Scott, an internal auditor for Northwest Industries, is auditing Springers Lumber & Supply, Northwests building materials outlet in Bozeman, Montana. His supervisor, Maria Pilier, asked him to...
-
1. Define and explain the difference between Money Market and Capital Market. Present examples. 2. Define and explain four Generally Accepted Accounting Principles. 3. What is the difference between...
-
The consecutive reaction A > B > C is stationary and isothermally operated, carried out in an ideal continous stirred tank in such a way that B is present in the maximum concentration (Cbmax)....
-
Jackson was one of the lawyers of a legal finn which provided legal services to its customers. One of its customers, Fresh and Clean Company, would sign a contract with Jackson's firm for the legal...
-
Separate variables and use partial fractions to solve the initial value problems in Problems 18. Use either the exact solution or a computer-generated slope field to sketch the graphs of several...
-
The owner of Colonial Adventure Tours knows the importance of the SQL language in database management. He realizes that he can use SQL to perform the same functions that you performed with queries in...
-
The following exercises are based on the TAL Distributors database as designed in Example 1 in this chapter. In each exercise, represent your answer in DBDL and with a diagram. You may use any of the...
-
What is a visibility symbol in UML?
-
A dump truck, whose bed is made of steel, holds an old steel watering trough. The bed of the truck is slowly raised until the trough begins to slide. What is the acceleration of the trough as it...
-
A vendor at the local art fair ties her tent to the concrete-filled coffee can shown in Figure P5.34. A stiff breeze comes up and the string becomes taut. What is the maximum value that the string...
-
The acceleration of the spacecraft in which the Apollo astronauts took off from the moon was 3.4 m/s 2 . On the moon, g = 1.6 m/s 2 .What was the apparent weight of a 75 kg astronaut during takeoff?
Study smarter with the SolutionInn App