-- SECTION 1: Single-Table SQL Queries --******************************************* -- 1. Write a query to display customers last names, first names, and email addresses. -- Sort your results by last name and first name. -- 2. Use the concatenation operator || to concatenate the customers last and first names into one column. -- Leave a comma and a space between the last and first names. -- Your results should display in 1 column. -- Use a column alias to name the one column Customer Full Name. -- Sort your results by the column alias in descending order. -- 3. Using the concatenation operator || -- Write a query that displays the customers first name, last name, and email address in this sentence: -- -- The customer [FIRST_NAME] [LAST_NAME] can be reached at the email address [EMAIL]. -- -- [FIRST_NAME], [LAST_NAME], and [EMAIL] are placeholders for values from those columns. -- Sort your results by last name and first name. -- 4. Write a query that displays a list of employees. -- Display these Employee data: -- Hire Date -- Last Name -- First Name -- Current Salary -- Current Salary increased by 5% (using an expression) -- Sort by hire date - most recent first - and then employee last name and first name. -- 5. Write a query to display a list of unique (no duplicates) customer IDs that have placed orders.

-- Sort by Customer_ID. -- 6. Write a query to display details (all columns) for employees that -- Are managed by Kevin Mourgos -- Have a salary less than or equal to $10000 -- Have a phone number that ends in 4 -- Sort the results by salary (descending) and then by the employees last and first names. -- 7. Using Boolean operators (AND, OR) and comparison operators (=, >, =,

*/ /* 3. Write a query using a self-join to display Employees IDs and names along with Their managers ID and name. Use column aliases so that it is clear which columns are for the Manager and which columns are for the Employee. Display Manager data first. Sort your results by the managers last & first names and then by the employees last & first names. NOTE: INNER JOIN is OK here. NOTE: I suggest you use table aliases. */ /* 4. The company would like to identify All of the customers that have never placed an order. Write a query using a join that provides this information. Your results should include all the customer details. Sort your results by the customers last & first names. NOTE: INNER JOIN will NOT work here. NOTE: I suggest you use table aliases. */ /* 5. The company needs a list of every Customer and every Book. In addition to Customers who have purchased Books and the Books purchased, This report should also include Customers who have not purchased Books Books that have not been purchased. NOTE: This should NOT be a Cartesian Product. The managers need to see: Customer_Id Last_Name First_Name ISBN Book_Title Book_Price For those Books that have been purchased, they also need to see: Order_Number Order_Date Unit_Price Quantity Sort your results by cistomer last and first name, book title, and order date NOTE: INNER JOIN will NOT work here. NOTE: I suggest you use table aliases. */ --********************************************************** -- SECTION 3: Using Set Operations

--********************************************************** /* The company had a data breach and needs to contact everyone in their database employees and customers. Write 5 SQL statements to create a list of employees and customers. Your results should include the employees and customers first names, last names, and email addresses. Sort each statement by last & first name. */ /* 1. Write the SQL statement using the UNION set operator. */ /* 2. Write the SQL statement using the UNION ALL set operator. */ /* 3. Write the SQL statement using the INTERSECT set operator. */ /* 4a. Write the SQL statement using the MINUS set operator. Use employees first and then customers. 4b. Write the SQL statement using the MINUS set operator. Use customers first and then employees. */ /* 5. Which of the above SQL statements would you use to create the list needed to contact everyone in the database about the data breach? WHY? ANSWER:

DBMS 130: HOL Small Company ERD To HOL_LOCATIONS \begin{tabular}{|l|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 \\ \hline U & UNIQUE \\ \hline \end{tabular} HOL_EMPLOYEES \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 & \multicolumn{2}{|c|}{ HOL_ORDERS } \\ \hline & \multicolumn{2}{|l|}{} \\ \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_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}