Select CustomerID,Sale.SaleID,SUM(SalePrice) as
‘TotalAmt’
FROM Sale,SaleItem
WHERE Sale.SaleID = SaleItem.SaleID
GROUP BY Sale.SaleID
HAVING SUM(SalePrice) > 500
--for the saleId in sale table take the corresponding salid in
saleitem table
--take the sum of saleprice and by groupby condition,they are
grouped by saleID which means we can find the sum of saleprice
--for each group.Having condition only includes those sum values
which are having greater than 500
select ProductName,ListPrice
from SaleItem,Product
where SaleItem.ProductID = Product.ProductID and SaleItem.ProductID
IN (select ProductID from SaleItem where Quantity =2)
-- In the subquery we've taken the ProductID of
those saleItem which has quantity = 2
-- in the where clause of main query we are searching for the
ProductID using IN statement
Customer Product Manufacturer ManufacturerID ManufacturerName Addressi Address2 City State PostalCode Phone Fax Contact URL Sale Saleltem...
The business process we are interested in is sales(i.e., selling products to customers). Develop a logical star- or snowflake-schema dimensional model. orders customers + PK FK orderdetails PK/EK OrderID PK/FK Products UnitPrice Quantity Discount PK OrderID Customer D FK Employeeld Order Date RequiredDate Shipped Date FK ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode Ship Country CustomerID CompanyName ContactName Contact Title Address City Region PostalCode Country Phone shipppers PK Shipperid CompanyName Phone Fax products PK ProductID + employees PK Employeeld LastName...
The Chinook team would like to throw a promotional Music Festival for their top 10 customers who have spent the most in a single invoice. Write a query that returns the first name, last name, and invoice total for the top 10 invoices ordered by invoice total descending. MediaType Artist Album Track Artistld Albumld MediaTypeld Trackld Title Name Name Name Artistld Albumld MediaTypeld Genreld Composer Genre Playlist Track Playlist Milliseconds Genreld Playlistld Playlistld Name Bytes Name Trackld UnitPrice InvoiceLine Employee...
uu Payments check Number paymentate amount customer Number Offices officeCode city phone addressLine1 addressLine2 state country postalCode territory officeLocation # Customers customer Number customerName contactLastName contactFirstName phone addressLine1 addressLine2 city state postalCode country sales RepEmployeeNumber creditimit customer Location Employees employeeNumber lastName firstName extension email reports To job Title officeCode E Product Lines productline textDescription Orders order Number order Date requiredDate shipped Date status comments customerNumber Order Details orderNumber product Code quantity Ordered priceEach orderline Number Products product Code productName productScale...
Display all customers. If a customer has placed any orders, also display the highest..... Tables: CREATE TABLE Sales.Customers ( CustomerId INT NOT NULL IDENTITY, CustomerName NVARCHAR(50) NOT NULL, StreetAddress NVARCHAR(50) NULL, City NVARCHAR(20) NULL, [State] NVARCHAR(20) NULL, PostalCode NVARCHAR(10) NULL, Country NVARCHAR(20) NULL, Contact NVARCHAR(50) NULL, Email NVARCHAR(50) NULL, CONSTRAINT PK_Customers PRIMARY KEY(CustomerId) ); CREATE TABLE HR.Employees ( EmployeeId INT NOT NULL IDENTITY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, BirthDate DATE NOT NULL, HireDate DATE NOT NULL, HomeAddress...
Create the following SQL Server queries that access the Northwind database Same as problem 3 but, limit the list to all customers who placed 3 or more orders. The product id, product name, last date that the product was ordered for all items in the Grains/Cereals category. (Hint: Use MAX) The product ID, product name, and number of distinct customers who ordered that product in 1996 Thank you. Categories Customers Employees Order Details Orders Products Shippers 9 CategoryID CategoryName Description...
Question for SQL in MS SQL Server Management Studio (Please don't use where clause for Joins use from clause) Need to Create a Query to display products purchased by customers in Germany that were not purchased by customers in the US. Customers PK Customer D CompanyName Contact Name Contact Title Address City State OrRegion PostalCode Country Phone char(5) varchar(40) varchar(30) varchar(30) varchar(60) varchar(15) varchar(15) varchar(10) varchar(15) varchar(24) varchar(24) Order Details Orders PK OrderID int FK CustomerlD char(5) FK Employeeld int...
The Northwind database created by Microsoft contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world. Here is the schema of the database: Products (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued); Suppliers (SupplierID, CompanyName, ContactName , ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage); Categories (CategoryID, CategoryName, Description, Picture); Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry); Order_details (ID, OrderID,...
Question for SQL in MS SQL Server Management Studio (Please don't use where clause for Joins use from clause) Need to Create a Query because Northwind Traders wants to make sure that all orders are shipped within 10 days. Display any orders that were not shipped within 10 days. Include the OrderID, the OrderDate, and the number of days it took before the order was shipped. Customers PK Customer D CompanyName Contact Name Contact Title Address City State OrRegion PostalCode...
o Customer Y CustomerID CustomerName Street City State Sales SalesinvoiceID SaleDate Sales Person Customerid Sales_inventory * SalesinvoicelD 3 Itemid Quantity SoldPrice Inventory ItemID Description Color VendorID QuantityOnHand ListPrice 95) 95) Referring to the diagram above, which tables would need to be referenced in the FROM statement for the SELECT statement of "SELECT s.CustomeriD, s.SaleDate, s.SalesinvoicelD, SUM(Quantity SoldPrice) AS Order Total"? A) Sales AS s, Sale_Inventory B) Sale_Inventory, Inventory C) Customer, Sales AS s, Sale_Inventory, Inventory. D) Sales AS s, Inventory
Question for SQL in MS SQL Server Management Studio (Please don't use where clause for Joins use from clause) Need to Create a Query because Northwind Traders has decided to increase their retail prices. Write a query that shows the product name, the current price, and the new price. Make sure the new price is listed to 2 decimal points. Follow below guidelines on new retail price: a. If the category is beverages or dairy products, increase the price by...