Re: Sql Northwind DB
From: arva (anonymous_at_discussions.microsoft.com)
Date: 09/07/04
- Next message: Hugo Kornelis: "Re: Sql Northwind DB"
- Previous message: Hugo Kornelis: "Re: Sql Northwind DB"
- In reply to: Hugo Kornelis: "Re: Sql Northwind DB"
- Next in thread: Hugo Kornelis: "Re: Sql Northwind DB"
- Reply: Hugo Kornelis: "Re: Sql Northwind DB"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 7 Sep 2004 01:25:00 -0700
Thanks for guiding me ..
I have solved the queries but 5,10 Question i unable to
solve. I am posting all my answer.
Please check and let me know is it o.k. or not
and give the solution for 5,10 query
Provide a SQL statement and sample result set that will
list all Customers within the CA state.
select CompanyName from customers where state = 'CA'
2.Provide a SQL statement and sample result set that will
count the total orders for all Customers within the CA
state.
Select count(od.OrderID) as OrderCount from Orders od,
Customers ct where od.customerID = ct.customerID and
state = 'CA'
3.Provide a SQL statement and sample result set that will
count orders for all Customers within the CA state during
the 2002 calendar year sorted by Customer name.
select ct.CompanyName as Customer,count(od.OrderID) as
OrderCount from Orders od, Customers ct where
od.customerID = ct.customerID and (OrderDate
>= '2002/01/01 00:00:00' and OrderDate <= '2002/12/31
23:59:59') and state = 'CA' group by ct.CompanyName
Order by ct.CompanyName
4.Provide a SQL statement and sample result set that will
list all Customers within the CA state that have placed 10
or more orders during the 2002 calendar year.
select ct.CompanyName as Customer from Orders od,
Customers ct where od.customerID = ct.customerID and
(OrderDate >= '2002/01/01 00:00:00' and OrderDate
<= '2002/12/31 23:59:59') and state = 'CA' group by
ct.CompanyName having count(od.OrderID)>=10
5.Customer Perks Program. Management would like to setup
a Platinum Buyer Club for all customers who have
consistently placed 10 or more orders each year for the
previous 10 years. Provide a SQL statement and sample
result set that will list the qualifying Customers, the
average placed orders during a year, and the total number
of orders placed each year. This SQL statement should
bring back one record per Customer for the Program.
6.Invoice for an Order. With each order shipped, there is
an invoice to the customer. Provide a SQL statement and
sample result set that provides the following information:
a.Shipping information (Shipping Name, Address, City,
State, Zip, Country)
b.Customer information (Customer Name, Address, City,
State, Zip, Country)
c.Sales Person (Employee who took the order)
d.Order Information (Order Number, Order Date, Required
Date, Shipped Date, Shipper)
e.Order Details (Product Category, Product, Unit Price,
Quantity, Discount, Total Price)
SELECT Od.ShipName, Od.ShipAddress, Od.ShipCity,
Od.ShipState, Od.ShipZipCode,
Od.ShipCountry,ct.CompanyName AS CustomerName, ct.Address,
ct.City,
ct.State, ct.ZipCode, (FirstName + ' ' + LastName) AS
Salesperson,
Od.OrderID as OrderNo, Od.OrderDate, Od.RequiredDate,
Od.ShippedDate, Shippers.CompanyName As ShipperName,
Odt.ProductID as 'Product Category',
Products.ProductName as Product, Odt.UnitPrice,
Odt.Quantity, Odt.Discount,
(CONVERT (money,(Odt.Quantity * Odt.UnitPrice * ((100-
Odt.Discount)/100)))) AS 'Total Price' FROM Shippers
INNER JOIN
(Products INNER JOIN ((Employees INNER JOIN
(Customers ct INNER JOIN Orders Od ON ct.CustomerID =
Od.CustomerID)
ON Employees.EmployeeID = Od.EmployeeID) INNER JOIN
Order_Details Odt ON Od.OrderID = Odt.OrderID) ON
Products.ProductID = Odt.ProductID)
ON Shippers.ShipperID = Od.ShipperID
7.Product Analysis for Marketing. Marketing department
would like to advertise new products to customers who
purchase similar category of products during the 2002
year. Provide a SQL statement and sample result set that
will list the product categories and the total units
ordered during the 2002 calendar year by Customer. If
there were no products ordered within the category,
display a zero.
SELECT Catg.CategoryName, pdt.ProductName, Sum(CONVERT
(money,(Odt.Quantity * Odt.UnitPrice * ((100-
Odt.Discount)/100)))) AS ProductSales
FROM (Categories Catg INNER JOIN Products pdt ON
Catg.CategoryID = pdt.CategoryID)
INNER JOIN (Orders Od INNER JOIN Order_Details Odt ON
Od.OrderID = Odt.OrderID)
ON pdt.ProductID = Odt.ProductID WHERE
(Od.ShippedDate>= '2002/01/01 00:00:00' and Od.ShippedDate
<= '2002/12/31 23:59:59')
GROUP BY Catg.CategoryName, pdt.ProductName
8.Goodwill Gifts for Suppliers and Shippers. Management
would like to purchase gifts for suppliers and shippers as
goodwill to continue a strong working relationship.
Provide a single SQL statement and a sample result set
that will list suppliers, a list of products supplied, and
their total orders using those products, and list
shippers, order ship state, and the total orders shipped
all during the 2002 calendar year.
select spl.companyname,pdt.productname,count(od.orderID)
as 'Total Order',
shp.CompanyName,od.ShipState,count(od.ShippedDate)
as 'Ordershipped'
from suppliers spl
Inner join products pdt on pdt.supplierID=spl.supplierID
Inner join Orders od on od.ProductID=pdt.ProductID
Inner join Shippers shp on shp.ShipperID=od.ShipperID
Where od.OrderDate >= '2002/01/01 00:00:00' and
od.OrderDate <= '2002/12/31 23:59:59'
group by
spl.companyname,pdt.productname,shp.CompanyName ,od.ShipSta
te
9.Promotions / Order Trend Analysis. Marketing would like
to provide a promotions for products that may not ordered
during particular months. Provide a SQL statement and
sample result set that will provide a list of products and
total units ordered by month broken down by product
category.
SELECT catg.CategoryName, pdt.ProductName, Odt.Quantity
FROM Products pdt INNER JOIN order_details Odt ON
pdt.ProductID = Odt.ProductID INNER JOIN Categories catg
ON pdt.CategoryID = catg.CategoryID INNER JOIN
Orders od ON odt.OrderID = od.OrderID AND odt.ProductID =
od.ProductID
WHERE (Od.OrderDate>= '2003/02/01 00:00:00' and
Od.OrderDate <= '2003/02/31 23:59:59')
Group by catg.CategoryName, pdt.ProductName, Odt.Quantity
ORDER BY catg.CategoryName
10. Employee Bonuses. Employees are given a bonus
each year for the total orders and total order purchase
price. Provide a SQL statement and sample result set that
lists each employee, the total order counts, and total
order purchase price for the past 2 years broken down by
year. Include each active employee regardless of those
who have not been associated with orders taken over the
past 2 years.
>-----Original Message-----
>On Mon, 6 Sep 2004 14:47:34 -0700, arva wrote:
>
>>Please send me the answers to the following questions.
>>This is very urgent,use the northwind Database in Sql
>>server to build your query.
>(snip)
>
>Hi Arva,
>
>Do I smell a class assignment?
>
>I don't think you'll learn SQL by posting your
assignments to a newsgroup
>and copying the answers. You learn by trial and error.
>
>If this is really "very urgent", then I suggest that you
first try to come
>up with some solutions yourself. The first 4 tasks are
pretty basic; they
>shouldn't cause you trouble if you paid any attention at
all during class.
>The remaining 6 are tougher. If you really give it your
best shot but fail
>to come up with the correct results, post back here with
what you have; I
>will try to give you a nudge in the right direction. I
won't give a full
>solution, though. If you don't learn to write queries
now, you'll be
>posting questions to the newsgroups for the rest of your
life....
>
>Best, Hugo
>--
>
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>
- Next message: Hugo Kornelis: "Re: Sql Northwind DB"
- Previous message: Hugo Kornelis: "Re: Sql Northwind DB"
- In reply to: Hugo Kornelis: "Re: Sql Northwind DB"
- Next in thread: Hugo Kornelis: "Re: Sql Northwind DB"
- Reply: Hugo Kornelis: "Re: Sql Northwind DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
