Write sql statements that will retrieve the data

Assignment Help PL-SQL Programming
Reference no: EM13780715

Write SQL statements that will retrieve the following data from a database, using Subqueries and Joins. Using the Northwind database, write a SQL SELECT statement that will retrieve the data for the following questions

For each order, display the Company Name, City, and the Country for the customer who placed the order.

  • Include the Order Date and the Required Date. Order the results by Company Name in ascending order.
  • 830 rows returned.

For each order, list the OrderID, Orderdate, Product Name, UnitPrice, Quantity Ordered, and Total Cost. Label the column 'Product Cost.'

  • For each product (including the discount), only show those orders that were placed in March of 1997.
  • Sort the data by the OrderID, then the Product Name.
  • 77 rows returned.

For each order, display the OrderID, ShipName, and the Employee First and Last Name.

  • Order the results by Employee Last Name in descending order, then by Employee First Name in ascending Order, then by OrderID in ascending order.
  • 830 rows returned.

Modify the first query to list the orders with customer details, but include all customers even if they have not placed an order.
832 rows returned.

  • List all customers (include CustomerId and Company Name) who have placed less than 5 orders.
  • Include those customers who have placed 0 orders. Name the count field "OrderCount."
  • Order by number of placed orders in reverse order.
  • 18 rows returned.

Display the ProductName, and UnitPrice of all products that have a unit price larger than Tarte au Sucre.

  • Order the results by UnitPrice in descending order.
  • 7 rows returned.

Display the Customer Name of all customers who have placed orders in 1996.

  • Order the results by Company Name in ascending order.
  • 67 rows returned.

Display the OrderID of all orders that where placed after all orders placed by Bottom-Dollar Markets.

  • Order the result by OrderID in ascending order.
  • 28 rows returned.

List the Company Name of all U.S.-based customers who are NOT located in the same state (or region) as any of the employees.

  • Order the results by Company Name.
  • 10 rows returned.

Display the Product Names of all products that were placed by customers in CA.

  • Order the result by Product Name in ascending order.
  • Eliminate duplicate rows in the results.
  • 10 Rows Returned.

Query #1

For each order, display the Company Name, City, and the Country for the customer who placed the order.

• Include the Order Date and the Required Date.
• Order the results by Company Name in ascending order.

Hints & Requirements:

• Inner Join on CustomerID from [Orders] and [Customers]. The order of the ON statement will change results
• 830 rows returned.

Query #2

For each order,

• List the OrderID, Orderdate, Product Name, UnitPrice, Quantity Ordered, and Total Cost. Label the column 'Product Cost.'
• For each product (including the discount), only show those orders that were placed in March of 1997.
• Sort the data by the OrderID, then the Product Name.

Hints & Requirements:

• Join on orderid from [Orders] and [Order Details] tables.
• Join on productID from [Products] and [Order Details] tables.
• ‘Product cost' is a calculated field which includes *(1.0-discount)
• 77 rows returned

Query #3

For each order:

• Display the OrderID, ShipName, and the Employee First and Last Name.
• Order the results by:
o Employee Last Name in descending order, then by
o Employee First Name in ascending order, then by
o OrderID in ascending order

Hints & Requirements:

• Join on EmployeeID from [Orders] and [Employees] tables.
• 830 rows returned.

Query #4

Modify the first query to list the orders with customer details, but include all customers even if they have not placed an order.

Hints & Requirements:

• Left outer join on CustomerID field from [Customers] and [Orders] tables.
• 832 rows returned.

Query #5

List all customers (include CustomerId and Company Name) who have placed less than 5 orders.

• Include those customers who have placed 0 orders.
• Name the count field "OrderCount."
• Order by number of placed orders (ie OrderCount) in reverse order.

Hints & Requirements:

• Left outer join on customerid field from customers and orders table. Must use count(orderid) in query, and later in the SQL statement. Use a GROUP BY and HAVING.
• 18 rows returned

Query #6

Display the ProductName, and UnitPrice of all products that have a unit price larger than ‘Tarte au Sucre'.

• Order the results by UnitPrice in descending order.

Hints & Requirements:

• Two WHERE clauses required. Sub query required in the first WHERE clause
• 7 rows returned.

Query #7

Display the Customer Name of all customers who have placed orders in 1996.

• Order the results by Company Name in ascending order.

Hints & Requirements:

• Join CUSTOMERS and ORDERS.
• Use the keyword DISTINCT.
• 67 rows returned

Query #8

Display the OrderID of all orders that where placed after all orders placed by "Bottom-Dollar Markets".

• Order the result by OrderID in ascending order.

Hints & Requirements:

• Three WHERE clauses required for this query.
o First WHERE clause checks for OrderDate and uses a sub query with ALL keyword.
o Second WHERE clause use equals and sub query.
o Third WHERE clause uses equal and company name.
• 28 rows returned.

Query #9

List the Company Name of all U.S.-based customers who are NOT located in the same state (or region) as any of the employees.

• Order the results by Company Name.

Hints & Requirements:

• Use the CUSTOMERS and EMPLOYEES tables. Use two WHERE clauses. First WHERE clause uses reverse(NOT) set notation and sub-query. Second WHERE clause is a compound statement testing country and null.
• 10 rows returned

Query #10

Display the Product Names of all products that were placed by customers in CA.

• Order the result by Product Name in ascending order.
• Eliminate duplicate rows in the results.

Hints & Requirements:

• Join Customer, Orders and [Order Details]
• Use the keyword: DISTINCT (look it up)
• 10 rows returned

Reference no: EM13780715

Questions Cloud

Write paper on should student be allowed to eat during class : Write a paper on 'Should The Students Be Allowed To Eat During Class?'.
Types of events or gimmicks : Let's explore how commercialism changes sports. When sports become entertainment packages what types of events or gimmicks do we tend to see?
How would you handle a juvenile : As a parole officer, how would you handle a juvenile who will turn 18 years old in 2 months and who persistently violates his curfew, which is a condition of his parole program? Include course material in your answer as required
What kind of malicious attack : In this assignment, you will research and select one well-known virus or a malicious code attack. Then, write a one-page essay explaining the following questions
Write sql statements that will retrieve the data : Write SQL statements that will retrieve the following data from a database, using Subqueries and Joins. Using the Northwind database, write a SQL SELECT statement that will retrieve the data for the following questions
Analyze the case study big mart- cheap goods : Read and analyze the case study Big Mart: Cheap Goods At What Price? You must address all six questions under the questions to consider section after the conclusion of the case study
Examine influence of web information on global citizenship : Examine the influence of web-based information on global citizenship and multicultural understanding.
Describe and explain the controversy surrounding the penny : Describe and explain the controversy surrounding the penny, and then tell your thoughts on the matter. Include information from a reading material, a short passage and a short video, and you need to help me write an essay about it.
Explain the process the juvenile will follow after arrest : Describe the process the juvenile will follow after arrest, from intake, through court, sentencing, and punishment or rehabilitation. The process should be based on actual state laws and practices of your state or a state in which you are familiar

Reviews

Write a Review

PL-SQL Programming Questions & Answers

  Write sql statement to produce single column

Write an SQL statement to produce a single column called ItemLocation that combines the SKU_Description,the phrase "is located in."

  Create report using wizard or based on sql query

Create a report using the wizard or based on an SQL query that that shows Courses that instructors are approved to teach. Show Course Number, First Name, and Last Name of Instructor.

  Sql statement which select names and owners of great danes

Write SQL statement which would select each of the following: names and owners of all Great Danes and all attributes of poodles whose balance is no greater than $50.

  Write a program in towers of hanoi

How to write a program in towers of hanoi using pl/sql language

  Database analysis and design

Implement the initial database design for the Customer Relationship Management VIEW as described below in your selected RDBMS. You need to create the Entity-Relationship model from your normalization and Database analysis, define tables, primary k..

  Create a procedure named basket

After a shopper completes an order, a procedure is called to update the following columns in the Basket table: orderplaced, subtotal, shipping, tax and total.

  Submit the table creation statements for the database model

submit the table creation statements for the database model. submit them all in a single script file.also submit a

  Create tables using sql ddl

Write the SQL DDL to create the database that contains each of the relations shown in the above ERD. You will need to provide - Your DDL code for each table that you create and a screenshot showing each table that is created.

  Assignment related to sql querries

Write a SELECT statement that returns these columns from the Products table: The DateAdded column, A column that uses the CAST function to return the DateAdded column with its date only (year, month, and day)

  Write a select statement that returns the productname

Write a SELECT statement that returns the ProductName and ListPrice columns from the Products table.

  Sql script-creating the database and inserting data

Understanding the SQL script, creating the database and inserting data and display all product information for products that contain the string ‘saw' in their description.

  Stored procedure named spinsertcategory

Write a script that creates and calls a stored procedure named spInsertCategory. First, code a statement that creates a procedure that adds a new row to the Categories table. To do that

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd