Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Hello I need A-V all Query Design SQL form Queen Case Study chapter -3 thank you SQL Server, Oracle Database, and MySQL are available on
Hello I need A-V all Query Design SQL form Queen Case Study chapter -3 thank you
SQL Server, Oracle Database, and MySQL are available on our Web site at www.pearsonhighered.com/kroenke. If you are using the Microsoft Access 2016 QACS_CH02.accdb database, simply copy it to an appropriate location in your Documents folder. Otherwlse, you will need to use the discussion and instructions necessary for setting up the QACS_CHO2 database in the DBMS product you are using: - For Microsoft SQL Server 2017, see online Chapter 10A. - For Oracle Database 12c Release 2 or Oracle Database XE, see online Chapter 10B. - For MySQL 5.7 Community Server, see online Chapter 10C. Once you have set up your QACS_CH02 database, create an SQL script named QACS-CHO2-PQsqL and use it to record and store SQL statements that answer each of the following questions (if the question requires a written answer, use an SQL comment to record your answer): A. Show all data in each of the tables. B. List the LastName, FirstName, and Phone of all customers. C. List the LastName, FirstName, and Phone for all customers with a FirstName of John'. for the QACS ALE_ITEM Table D. List the LastName, FirstName, Phone, SaleDate, and Total of all sales in excess of $100.00. E. List the LastName, FirstName, and Phone of all customers whose first name starts. with 'D'. F. List the LastName, FirstName, and Phone of all customers whose last name includes the characters 'ne'. G. List the LastName, FirstName, and Phone for all customers whose eighth and ninth digits (starting from the left) of their phone number are 56. For example, a phone number ending in " 567" would meet the criteria. H. Determine the maximum and minimum sales Total. I. Determine the average sales Total. J. Count the number of customers. 0. Show the LastName, FirstName, and Phone of all customers who have had an order with Total greater than $100.00. Use a join using JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order. P. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a subquery. Present results sorted by LastName in ascending order and then FirstName in descending order. Q. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a join, but do not use JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order. R. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a join using JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order. S. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery. Present results sorted by LastName in ascending order and then FirstName in descending order. T. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery that is different from the combination used for question S. Present results sorted by LastName in ascending order and then FirstName in descending order. T. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery that is different from the combination used for question S. Present results sorted by LastName in ascending order and then FirstName in descending order. U. Show the LastName, FirstNanne, Phone, and ItemDescription for customers who have bought an Item named 'Desk Lamp'. Also show the LastName, FirstName, and Phone of all the other austomers. Present results sorted by. Item in ascending order, then LastName in ascending order, and then FirstName in descending order. HINI: In Microsoft Access 2016 you will either need to use a UNION statement or a sequence of two queries to solve this, because Microsoft Access disallows nesting an INNER join inside a LEFT or RIGHT join. The other DBMS products can do it with one query (not a UNION statement). v. Who referred each customer to Marcia's Dry Cleaning? Show columns named CustomerLastName, CustomerFirstName, ReferredByLastName, and ReferredByFirstName. Include the names of customers who were not referred by any other customer in the results of the query. SQL Server, Oracle Database, and MySQL are available on our Web site at www.pearsonhighered.com/kroenke. If you are using the Microsoft Access 2016 QACS_CH02.accdb database, simply copy it to an appropriate location in your Documents folder. Otherwlse, you will need to use the discussion and instructions necessary for setting up the QACS_CHO2 database in the DBMS product you are using: - For Microsoft SQL Server 2017, see online Chapter 10A. - For Oracle Database 12c Release 2 or Oracle Database XE, see online Chapter 10B. - For MySQL 5.7 Community Server, see online Chapter 10C. Once you have set up your QACS_CH02 database, create an SQL script named QACS-CHO2-PQsqL and use it to record and store SQL statements that answer each of the following questions (if the question requires a written answer, use an SQL comment to record your answer): A. Show all data in each of the tables. B. List the LastName, FirstName, and Phone of all customers. C. List the LastName, FirstName, and Phone for all customers with a FirstName of John'. for the QACS ALE_ITEM Table D. List the LastName, FirstName, Phone, SaleDate, and Total of all sales in excess of $100.00. E. List the LastName, FirstName, and Phone of all customers whose first name starts. with 'D'. F. List the LastName, FirstName, and Phone of all customers whose last name includes the characters 'ne'. G. List the LastName, FirstName, and Phone for all customers whose eighth and ninth digits (starting from the left) of their phone number are 56. For example, a phone number ending in " 567" would meet the criteria. H. Determine the maximum and minimum sales Total. I. Determine the average sales Total. J. Count the number of customers. 0. Show the LastName, FirstName, and Phone of all customers who have had an order with Total greater than $100.00. Use a join using JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order. P. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a subquery. Present results sorted by LastName in ascending order and then FirstName in descending order. Q. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a join, but do not use JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order. R. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a join using JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order. S. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery. Present results sorted by LastName in ascending order and then FirstName in descending order. T. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery that is different from the combination used for question S. Present results sorted by LastName in ascending order and then FirstName in descending order. T. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery that is different from the combination used for question S. Present results sorted by LastName in ascending order and then FirstName in descending order. U. Show the LastName, FirstNanne, Phone, and ItemDescription for customers who have bought an Item named 'Desk Lamp'. Also show the LastName, FirstName, and Phone of all the other austomers. Present results sorted by. Item in ascending order, then LastName in ascending order, and then FirstName in descending order. HINI: In Microsoft Access 2016 you will either need to use a UNION statement or a sequence of two queries to solve this, because Microsoft Access disallows nesting an INNER join inside a LEFT or RIGHT join. The other DBMS products can do it with one query (not a UNION statement). v. Who referred each customer to Marcia's Dry Cleaning? Show columns named CustomerLastName, CustomerFirstName, ReferredByLastName, and ReferredByFirstName. Include the names of customers who were not referred by any other customer in the results of the queryStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started