Use the Small Company ERD to complete the steps listed below. Read each of the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Use the Small Company ERD to complete the steps listed below. • Read each of the questions listed in the left column of the table below. • Write the answer to your question and/or the required SQL statement in the corresponding cell in the right column that answers the question. (Test your statement to confirm the results before submitting.) • Save this document once you have added your answers and uploaded it as part of your submission. A. Using Joins (30 points) Joins a. Write a query using a join that displays employees' IDs, names, and department names. Sort your results by department and employee last name. b. Write a query using a join that displays employees' IDs, names, and job names. Sort your results by department and employee last name. c. Write a query using a join that displays employees IDs, names, job and department names. Sort your results by department, job, and employee last name. d. Write a query using a self-join to display employees IDs and names along with their manager's ID and name. Sort your results by the managers' last names and the employees' last names. e. Write a query using a join that displays customers' IDs, names, order numbers, and order dates. Sort your results by order date (descending) and customers' names. f. Write a query using a join that displays customer customers' IDs, names, order numbers, order dates, and book titles associated with the order. Sort your results by order date (descending), customers' last names, and book title. The company would like to send coupons to customers that like to read fiction. Write a query to display a list of customers that have purchased book titles that are categorized as fiction (BOOK_CATEGORY_CD= F). Your results should include the customers' name and email. It should also include the order number, date, and book title for their previous purchase of fiction book. Sort your results by the customers' last name, order date, and book title. h. 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 and the order number column. Sort your results by the customers' last name and then first name. i. The company is performing an analysis of their inventory. They are considering purging books that are not popular with their customers. To do this they need a list of books that have never been purchased. Write a query using a join that provides this information. Your results should include all the book details and the order number column. Sort your results by the book title. B. Using Set Operators (30 points) Set Operators a. The budget at the company is tight. The president is considering making cuts. He would like a report that lists people who meet the following criterion: o salary> 12500 and manager_id is not null o hire_date > to_date("1996-01-01','yyyy-mm- dd') Using the Boolean operators AND and OR produced an inaccurate report. Write a query that uses the set operator INTERSECT to produce an accurate report. b. Describe why the using Boolean operators produces different results than the set operator INTERSECT in this scenario. c. The President of the company is also a customer. Write a SQL statement that adds his customer record to the database. Give him CUSTOMER_ID 999. His address is 1999 Green Mile Lane in Los Angeles, California 90210. d. The company had a data breach and needs to contact everyone in their database - employees and customers. Write a SQL statement that uses the UNION set operator to create a list of employees and customers. Your results should include the employees and customers' first names, last names, and email addresses and be sorted by last name. e. Write a SQL statement that uses the UNION ALL set operator to create a list of employees and customers. Your results should include the employees and customers' first names, last names, and email addresses and be sorted by last name. f. Write a SQL statement that uses the INTERSECT set operator to create a list of employees and customers. Your results should include the employees and customers' first names, last names, and email addresses and be sorted by last name. - Write a SQL statement that uses the MINUS set operator to create a list of employees and customers. Your results should include the employees and customers' first names, last names, and email addresses and be sorted by last name. h. Explain the differences in your results to questions #d-g. i. Which SQL statement should you use to create the list needed to contact everyone in the database about the data breach? Use the Small Company ERD to complete the steps listed below. • Read each of the questions listed in the left column of the table below. • Write the answer to your question and/or the required SQL statement in the corresponding cell in the right column that answers the question. (Test your statement to confirm the results before submitting.) • Save this document once you have added your answers and uploaded it as part of your submission. A. Using Joins (30 points) Joins a. Write a query using a join that displays employees' IDs, names, and department names. Sort your results by department and employee last name. b. Write a query using a join that displays employees' IDs, names, and job names. Sort your results by department and employee last name. c. Write a query using a join that displays employees IDs, names, job and department names. Sort your results by department, job, and employee last name. d. Write a query using a self-join to display employees IDs and names along with their manager's ID and name. Sort your results by the managers' last names and the employees' last names. e. Write a query using a join that displays customers' IDs, names, order numbers, and order dates. Sort your results by order date (descending) and customers' names. f. Write a query using a join that displays customer customers' IDs, names, order numbers, order dates, and book titles associated with the order. Sort your results by order date (descending), customers' last names, and book title. The company would like to send coupons to customers that like to read fiction. Write a query to display a list of customers that have purchased book titles that are categorized as fiction (BOOK_CATEGORY_CD= F). Your results should include the customers' name and email. It should also include the order number, date, and book title for their previous purchase of fiction book. Sort your results by the customers' last name, order date, and book title. h. 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 and the order number column. Sort your results by the customers' last name and then first name. i. The company is performing an analysis of their inventory. They are considering purging books that are not popular with their customers. To do this they need a list of books that have never been purchased. Write a query using a join that provides this information. Your results should include all the book details and the order number column. Sort your results by the book title. B. Using Set Operators (30 points) Set Operators a. The budget at the company is tight. The president is considering making cuts. He would like a report that lists people who meet the following criterion: o salary> 12500 and manager_id is not null o hire_date > to_date("1996-01-01','yyyy-mm- dd') Using the Boolean operators AND and OR produced an inaccurate report. Write a query that uses the set operator INTERSECT to produce an accurate report. b. Describe why the using Boolean operators produces different results than the set operator INTERSECT in this scenario. c. The President of the company is also a customer. Write a SQL statement that adds his customer record to the database. Give him CUSTOMER_ID 999. His address is 1999 Green Mile Lane in Los Angeles, California 90210. d. The company had a data breach and needs to contact everyone in their database - employees and customers. Write a SQL statement that uses the UNION set operator to create a list of employees and customers. Your results should include the employees and customers' first names, last names, and email addresses and be sorted by last name. e. Write a SQL statement that uses the UNION ALL set operator to create a list of employees and customers. Your results should include the employees and customers' first names, last names, and email addresses and be sorted by last name. f. Write a SQL statement that uses the INTERSECT set operator to create a list of employees and customers. Your results should include the employees and customers' first names, last names, and email addresses and be sorted by last name. - Write a SQL statement that uses the MINUS set operator to create a list of employees and customers. Your results should include the employees and customers' first names, last names, and email addresses and be sorted by last name. h. Explain the differences in your results to questions #d-g. i. Which SQL statement should you use to create the list needed to contact everyone in the database about the data breach?
Expert Answer:
Related Book For
Posted Date:
Students also viewed these databases questions
-
Illustrate the seven different types of friezes when they are classified according to their symmetries. Imagine the figure shown to be continued infinitely to the right and left. The symmetry group...
-
Three of the four molecular orbitals for cyclobutadiene are pictured here. Place them in order of increasing energy. (See Figures 9.13, 9.15, 9.16, and 9.18 and the relation of orbital energy and...
-
Recent trends in recruiting rely on social capital by ______. Multiple choice question. making use of structural holes between groups in a social network where there are few relationships bridging...
-
Progressive Company reported the following asset values in 2014 and 2015: In addition, in 2015, Progressive had sales of $4,800,000; cost of goods sold for the year was $2,900,000. As of the end of...
-
15. A smaller merry go round has a radius of 4.58 m and a mass of 800 kg. It is spinning at a rate of one revolution every seven seconds. How much work will it take to slow the merry go round down to...
-
Baldwin Realty, Inc., had the following transactions for the month of May, 2010. The following T-accounts have been set up, for Baldwin Realty, Inc., with their beginning balances as of May 1,2010....
-
A firm has invested $14,000 in machinery with a 7- year useful life. The machinery will have no salvage value , as the cost to remove it will equal its scrap value. The uniform annual benefits from...
-
In order to have more efficient supply chains, when the world becomes your market, what term has been used to describe how intermediaries are often critical to facilitate effective commerce?
-
If we accept the Sharpe model as a description of expected returns, using the data in Table 16.1, find the expected return on a stock in the construction industry with the following characteristics....
-
A taxpayer is considering buying a fully taxable corporate bond (5 year maturity), yielding 6% annual returns (compounded and payable annually)and a face value of $1,000. Alternately, the taxpayer is...
-
1.Simplify using the correct arithmetic order of operations. 52 - 16.5 + (19 - 11) 2 2. A 128-pound person is holding 5 identical bags of potatoes and, when he stands on a scale, it reads 191 pounds....
-
Find the amount of interest earned on $3700 invested at simple interest rate of 5% for 8 years. 5. There are 45 red balls and 15 white balls in a bag. If you reach into the bag and select one ball at...
-
(Please add more information to this product than what I have put in the our product section!) and list the websites you used :) Product: Absolute Dahlia Our Product: we are going to create for our...
-
Question 3. Prove by induction the identity (n+1)(n+2)... (2n-1) 2n 1.3.5.... (2n-1) for any natural n. 2n
-
Write a java GUI program to draw the picture of a house as shown below. Hint: You can draw different shapes (line, triangle, rectangle, and square) to accomplish this. 10 Note: You are free to scale...
-
Which of the following is not an operating cash flow? Select one: a. Payment of income tax b. Purchase of equipment for cash c. Payment of cash for operating expenses d. Collection of cash from...
-
XYZ Inc. a calendar year, accrual basis corporation, had the following items during 2021: Gross revenue from operations Cost of goods sold $420,000 ($180,000) $9,000 LT capital gain .LT capital...
-
Karlson Software Company is located in State H, which enables employers to reduce their contribution rates under the experience-rating system. From 2002 to 2011, inclusive, the company's total...
-
The names of the employees of Hogan Thrift Shop are listed on the following payroll register. Employees are paid weekly. The marital status and the number of allowances claimed are shown on the...
-
Adams, Inc., pays its employees' weekly wages in cash. A supplementary payroll sheet that lists the employees' names and their earnings for a certain week is shown below. Complete the payroll sheet...
-
Use the Allstott 2020 income statement that follows and the balance sheet from exercise 10-6 to compute the following: a. Allstott's rate of inventory turnover and days inventory outstanding for 2020...
-
Use the financial statements of Allstott, Inc., in exercises 10-6 and 10-7. 1. Compute the company's debt ratio at December \(31,2020\). 2. Compute the company's times-interest-earned ratio for 2020....
-
Use the financial statements of Allstott, Inc., in cxercises 10-6 and 10-7 to compute the following profitability measures for 2020. Show each computation. a. Return on sales b. Asset turnover c....
Study smarter with the SolutionInn App