Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --1
- SELECT COUNT(ProductID)
- FROM Production.Product;
- SELECT COUNT(ProductCategoryID)
- FROM Production.ProductCategory;
- SELECT COUNT(ProductSubcategoryID)
- FROM Production.ProductSubcategory;
- --2
- SELECT *
- FROM Production.Product
- WHERE Color IS NULL;
- --3
- SELECT DISTINCT YEAR(OrderDate), SUM(TotalDue)
- FROM Sales.SalesOrderHeader
- GROUP BY YEAR(OrderDate)
- ORDER BY YEAR(OrderDate);
- --4
- SELECT COUNT(*) AS customer_number FROM Sales.Customer;
- SELECT COUNT(*) AS employee_number FROM HumanResources.Employee;
- --5
- SELECT COUNT(*) FROM (
- SELECT * FROM Production.TransactionHistory
- UNION
- SELECT * FROM Production.TransactionHistoryArchive) as transactions
- GROUP BY Year(transactions.TransactionDate);
- --6
- SELECT Production.Product.ProductID, Production.Product.Name AS product_name, Production.ProductCategory.Name AS category_name
- FROM Production.Product
- JOIN Production.ProductSubcategory ON Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID
- JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID
- LEFT JOIN Sales.SalesOrderDetail ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID
- LEFT JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
- LEFT JOIN Sales.Customer ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
- WHERE Sales.Customer.CustomerID IS NULL
- GROUP BY Production.ProductCategory.Name, Production.Product.Name, Production.Product.ProductID;
- --7
- SELECT MAX(DiscountPct*ListPrice)
- FROM Production.Product
- JOIN Sales.SpecialOfferProduct ON Product.ProductID=SpecialOfferProduct.ProductID
- JOIN Sales.SpecialOffer ON SpecialOfferProduct.SpecialOfferID = SpecialOffer.SpecialOfferID
- GROUP BY ProductSubcategoryID;
- --8
- SELECT ProductID
- FROM Production.Product
- GROUP BY ProductID, ListPrice
- HAVING (SELECT AVG(ListPrice) FROM Production.Product)<ListPrice;
- --9
- SELECT AVG(Product.ProductID), MONTH(SalesOrderHeader.OrderDate)
- FROM Sales.SalesOrderDetail
- JOIN Production.Product ON SalesOrderDetail.ProductID = Product.ProductID
- JOIN Sales.SalesOrderHeader ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
- GROUP BY MONTH(SalesOrderHeader.OrderDate)
- ORDER BY MONTH(SalesOrderHeader.OrderDate);
- --10
- SELECT
- territory.Name 'Country',
- AVG(Datediff(day, OrderDate, ShipDate)/1.0) 'Average shipment time'
- FROM Sales.SalesOrderHeader header
- inner join Sales.SalesTerritory territory on header.TerritoryID = territory.TerritoryID
- group by territory.Name;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement