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...
-
The opening and the closing are the most emphatic parts of any message. How can writers avoid sounding demanding and abrupt in these two most important sections?
-
Describe the state of innovation in Canada today.
-
You need to understand the approach described in question 3 in More Genetic TIPS before answering this question. A muscle-specific gene was cloned and then subjected to promoter bashing. As shown...
-
Maserati Corporation purchased a new machine for its assembly process on August 1, 2012. The cost of this machine was $150,000. The company estimated that the machine would have a salvage value of...
-
National health insurance refers to an insurance scheme that covers the entire population and is usually established by national registration. While available literature shows that utilization levels...
-
The Rollins Sporting Goods Company manufactures baseballs for the professional minor and major leagues at its plants in Costa Rica. According to the rules of major league baseball, a baseball must...
-
Find the value of in between 0 <0 < 360 for the equation below 2 tano sin 0 = tan0
-
What are the three steps in decision making?
-
Explain the concept of Internet assurance services. What types of assurance are provided?
-
Describe the six steps in the purchase-to-pay process.
-
What factors distinguish data from information?
-
From the following, calculate net sales: Purchases $101 Sales Discount $21 Gross Sales 176 Operating Expenses 48 Sales Returns and Allowances 18
-
1. Using relevant examples discuss the five most important variables that may cause the market for demand curve for labor to shift 2. Why do you think our economy continues to experience wage...
-
Experiment: Tossing four coins Event: Getting three heads Identify the sample space of the probability experiment and determine the number of outcomes in the event. Draw a tree diagram when...
-
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...
-
For several years, the Otis Capital USA Corp has been exploring for gold on its mining claims held on public lands near the Idaho-Montana border. Otis filed a proposal to expand its current...
-
When GTE Sylvania discovered it was losing market share to other television manufacturers, it developed a franchise plan that limited the number of retailers selling its product in each area....
-
Visa, MasterCard, American Express, and Discover control practically all credit card transactions in the United States. The credit card industry is a two-sided market; that is, cardholders benefit...
Study smarter with the SolutionInn App