Write a query against the Sales.Orders table that returns orders placed in June 2015. 2)Write a query against the Sales.Orders table that returns orders placed on the last day of the month. 3)Write a query against the HR.Employees table that returns employees with a last name containing the letter e twice or more
1)
For all Questions in this Formative, paste full screenshots of your Microsoft SQL Server Management Studio, showing object explorer, query and query results.
To complete all the questions download and restore the TSQLV4
Write a query against the Sales.Orders table that returns orders placed in June 2015.
2)Write a query against the Sales.Orders table that returns orders placed on the last day of the month.
3)Write a query against the HR.Employees table that returns employees with a last name containing the letter e twice or more
4)Write a query against the Sales.OrderDetails table that returns orders with a total value (quantity * unitprice) greater than 10,000, sorted by total value.
5)To check the validity of the data, write a query against the HR.Employees table that returns employees with a last name that starts with a lowercase English letter in the range a through z.
6)Write a query against the Sales.Orders table that returns the three shipped-to countries with the highest average freight in 2015.
7)Write a query against the Sales.Orders table that calculates row numbers for orders based on order date ordering (using the order ID as the tiebreaker) for each customer separately.
8)Using the HR.Employees table, write a SELECT statement that returns for each employee the gender based on the title of courtesy. For ‘Ms.’ and ‘Mrs.’ return ‘Female’; for ‘Mr.’ return ‘Male’; and in all other cases (for example, ‘Dr.‘) return ‘Unknown’.
9)Return US customers, and for each customer return the total number of orders and total quantities.
10)
Return customers and their orders, including customers who placed no orders:
Tables involved: Sales.Customers and Sales.Orders
11)
Return customers with orders placed on February 12, 2016, along with their orders:
Tables involved: Sales.Customers and Sales.Orders
12)Return all customers, and for each return a Yes/No value depending on whether the customer placed orders on February 12, 2016: Tables involved: Sales.Customers and Sales.Orders
13)Write a query that returns all orders placed on the last day of activity that can be found in the Orders table: Table involved: Sales.Orders
14)
Write a query that returns all orders placed by the customer(s) who placed the highest number of orders. Note that more than one customer might have the same number of orders:
Table involved: Sales.Orders
15)
Write a query that returns employees who did not place orders on or after May 1, 2016:
Tables involved: HR.Employees and Sales.Orders
16)
Write a query that returns countries where there are customers but not employees:
Tables involved: Sales.Customers and HR.Employees
17)
Write a query that returns the maximum value in the orderdate column for each employee:
Table involved: Sales.Orders table
18)
Run the following code to create the dbo.Customers table in the TSQLV4 database:
USE TSQLV4;
DROP TABLE IF EXISTS dbo.Customers;
CREATE TABLE dbo.Customers
(
custid INT NOT NULL PRIMARY KEY,
companyname NVARCHAR(40) NOT NULL,
country NVARCHAR(15) NOT NULL,
region NVARCHAR(15) NULL,
city NVARCHAR(15) NOT NULL
);
Insert into the dbo.Customers table a row with the following information: custid: 100 companyname: Coho Winery country: USA region: WA city: Redmond
19)Insert into the dbo.Customers table all customers from Sales.Customers who placed orders.
20)Use a SELECT INTO statement to create and populate the dbo.Orders table with orders from the Sales.Orders table that were placed in the years 2014 through 2016.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 3 images
