Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Write the SQL to create a view called CommissionedSalaries to display the calculated salaries (including commission) of employees that receive a commission. Your results

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed

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)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

More Books

Students also viewed these Databases questions

Question

3. Vary your pace and volume in speaking. Use silence for emphasis.

Answered: 1 week ago

Question

What is a visibility symbol in UML?

Answered: 1 week ago