/* 1. Write the SQL to get the Employee Count by Manager. Your results should include the following details: Manager Employee ID Manager Last Name Manager First Name Manager Department Name (not Department_ID) Manager Job_Title (not Job_ID) Employee Count Sort your results by employee count (descending). HINT: This will require a self-join on HOL_EMPLOYEES, other JOINS, and a sub- query. */ /* 2. Write the SQL to create a view called vManagerEmployeeCount using your query from Q1. Make this view read-only. In a second SQL statement, Select all rows from your view. */ /* 3. Create a SYNONYM for your view named vMEC. Write a separate SQL statement to select all rows from your view using your synonym. */ /* 4. Using your SYNONYM, write a query to display all results from your view. Sort your results be department name. */ /* 5. Using your SYNONYM, write a query to display results from you view where employee count >= 4. Sort results by Manager last and first names. */ /* 6. Write the SQL to create a sequence with following specifications: Name: SEQ_HOL_BOOK_CATEGORY Starting Value: 10 Maximum Value: 10000000 Increment By: 1

*/ /* 7. Write and run an INSERT statement to create a new Book Category named "Drama". Use you SEQ_HOL_BOOK_CATEGORY to generate a value for the PK of this new row. */ /* 8. Modify (do not recreate) the SEQ_HOL_BOOK_CATEGORY so that it increments by 5 and there is no maximum value. */ /* 9. Write the SQL to remove the HOL_DEPARTMENTS_SEQ from the database. This sequence was created in Module 1 or 2. */ /* 10. Write the SQL to create an index named IX_CUST_NAME for the LAST_NAME and FIRST_NAME columns in the HOL_CUSTOMERS table. */ /* 11. What is the benefit of IX_CUST_NAME?

\begin{tabular}{|l|} \hline Legend \\ \hline Tables provided \\ \hline Tables you complete \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline PK & PRIMARY KEY \\ \hline FK & FOREIGN KEY \\ \hline NN & NOT NULL \\ \hlineU & UNIQUE \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|c|}{ HOL_REGIONS } \\ \hline PK & Region_ID & INTEGER \\ \hline & Region_Name & VARCHAR2(250) \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|c|}{ HOL_COUNTRIES } \\ \hline PK & Country_Code & CHAR(2) \\ \hline & Country_Name & VARCHAR2(250) \\ \hline FK & Region_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_EMPLOYEES } \\ \hline PK & Employee_ID & INTEGER \\ \hline & First_Name & VARCHAR2 (250) \\ \hline & Middle_Name & VARCHAR2(250) \\ \hline NN & Last_Name & VARCHAR2 (250) \\ \hline & Email & VARCHAR2(250) \\ \hline & Phone & VARCHAR2 (50) \\ \hline NN & Hire_Date & DATE \\ \hline & Current_Salary & NUMBER (12,2) \\ \hline & Commision_Pct & NUMBER (2,2) \\ \hline & Bonus & NUMBER(12,2) \\ \hline FK & Job_ID & INTEGER \\ \hline FK & Manager_ID & INTEGER \\ \hline FK & Department_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_LOCATIONS } \\ \hline PK & Location_ID & INTEGER \\ \hline & Street & VARCHAR2(250) \\ \hline & City & VARCHAR2(250) \\ \hline & State_Province & CHAR(2) \\ \hline & Postal_Code & VARCHAR2(50) \\ \hline FK & Country_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_DEPARTMENTS } \\ \hline PK & Department_ID & INTEGER \\ \hline & Department_Name & VARCHAR2(250) \\ \hline FK & Manager_ID & INTEGER \\ \hline FK & Location_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|c|}{ HOL_JOBS } \\ \hline PK & Job_ID & INTEGER \\ \hline NN & Job_Title & VARCHAR2(250) \\ \hline & Max_Salary & NUMBER (12,2) \\ \hline & Min_Salary & NUMBER (12,2) \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_CUSTOMERS } \\ \hline PK & Customer_ID & INTEGER \\ \hline & First_Name & VARCHAR2(250) \\ \hline & Middle_Name & VARCHAR2(250) \\ \hline & Last_Name & VARCHAR2(250) \\ \hline U & Email & VARCHAR2(250) \\ \hline & Phone & VARCHAR2(50) \\ \hline & Birth_Date & DATE \\ \hline & Gender & VARCHAR2(50) \\ \hline FK & Location_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_JOB_HISTORY } \\ \hline PK & Job_History_ID & IDENTITY \\ \hline FK & Employee_ID & INTEGER \\ \hline & Start_Date & DATE \\ \hline & End_Date & DATE \\ \hline FK & Job_ID & INTEGER \\ \hline FK & Department_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_ORDERS } \\ \hline PK & Order_Number & INTEGER \\ \hline & Order_Date & DATE \\ \hline & Order_Total & NUMBER(18,2) \\ \hline FK & Customer_ID & INTEGER \\ \hline FK & Sales_Person_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_BOOKS } \\ \hline PK & ISBN & INTEGER \\ \hline NN & Book_Title & VARCHAR2(250) \\ \hline & Book_Description & VARCHAR2 (4000) \\ \hline & Book_Price & NUMBER (12,2) \\ \hline & Book_Reviews & INTEGER \\ \hline & User_Rating & NUMBER(4,2) \\ \hline FK & Book_Category_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|c|}{ HOL_ORDER_ITEMS } \\ \hline PK & Order_Item_ID & INTEGER \\ \hline & Unit_Price & NUMBER(12,2) \\ \hline & Quantity & INTEGER \\ \hline FK & Order_Number & INTEGER \\ \hline FK & ISBN & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|c|}{ HOL_BOOK_CATEGORY } \\ \hline PK & Book_Category_ID & INTEGER \\ \hline NN & Book_Category_Name & VARCHAR2 (250) \\ \hline & Book_Category_Description & VARCHAR2(4000) \\ \hline \end{tabular} DRMS 130: HOI Small Comnany FRD