COVER PAGE. Task 1. My Database select queries . Write

COVER PAGE

Task 1
My Database select queries
 Write a query that uses an aggregate function.
select count(customerid) from customer;

Write a query that retrieves data from at least two of your tables.
select customer.*, address.* from customer,address where
customer.customerid=address.customerid;
 Write a query that has a primary and secondary sort. One of the sorts must be in the reverse
order.
select customer.*, address.* from customer,address where
customer.customerid=address.customerid
order by customer.firstname desc,address.street;

 Write a query that implements a wildcard search.
select * from customer where lastname like '%n%';
 Write a query that uses a mathematical calculation to on two or more columns and uses a
column alias on the calculation.
select max(customerid)-min(customerid) as "Difference between the first and last
customerid" from customer;

Adventureworks database queries
 Using a Join, display the product name and the sales order detail unit price. Sort the data by the
unit price. Use table aliases.
select p.name, sod.unitPrice from SalesLT.Product p
inner join SalesLT.SalesOrderDetail sod on sod.ProductID=p.ProductID
group by p.name, sod.unitPrice
order by sod.UnitPrice;
 Using a subquery, display the sales order IDs, order quantity, and unit price for all products that
are silver or partially silver in color.
select sod.SalesOrderID,sod.OrderQty,sod.unitPrice,p.name,p.Color from
SalesLT.Product p
inner join salesLT.SalesOrderDetail sod on sod.ProductID=p.ProductID
where p.productID IN (select productID from SalesLT.Product where color like
'%Silver%')
order by sod.UnitPrice;

 Modify the previous subquery so that each order is grouped together as one.
select sod.SalesOrderID,sod.OrderQty,sod.unitPrice,p.name,p.Color from
salesLT.Product p
inner join salesLT.SalesOrderDetail sod on sod.ProductID=p.ProductID
where p.productID IN (select productID from salesLT.Product where color like
'%Silver%')
group by sod.SalesOrderID,sod.OrderQty,sod.unitPrice,p.name,p.Color
order by sod.UnitPrice;
 Using a join, display the product name and description.
select p.name,pd.description from Production.Product p
inner join Production.ProductModel pm on pm.ProductModelID=p.ProductModelID

inner join production.ProductModelProductDescriptionCulture pmp on
pmp.ProductModelID=pm.ProductModelID
inner join production.ProductDescription pd on
pd.ProductDescriptionID=pmp.ProductDescriptionID;
 Display the unit price discount for the product with the highest discount.
select TOP 1 p.name, sod.UnitPriceDiscount from SalesLT.Product p
inner join SalesLT.SalesOrderDetail sod on sod.ProductID=p.ProductID
group by p.name, sod.UnitPriceDiscount
order by sod.UnitPriceDiscount desc;
 Display the customer email addresses sorted alphabetically from Z to A.
select p.EmailAddress from person.EmailAddress p
order by p.EmailAddress desc;

 Display the product names and numbers for all products that started selling July 1, 2002 that
stopped being sold June 30, 2003.
select p.name,p.ProductNumber from SalesLT.Product p
inner join SalesLT.SalesOrderDetail th on th.ProductID=p.ProductID
inner join SalesLT.SalesOrderHeader so on so.SalesOrderID=th.SalesOrderID
where so.OrderDate>='2002-06-01' AND so.OrderDate<='2003-06-30'
group by p.name,p.productNumber;