way2onlineLearning

  1. WAQ to display average price for all beverages products?   select AVG(isnull(p.unitprice,0)) as "avg product price" from dbo.Products P           join dbo.Suppliers s on s.SupplierID=P.SupplierID   join dbo.Categories c on c.CategoryID=p.CategoryID   where CategoryName='Beverages' 2. WAQ to display unique titles in the employee table?         SELECT distinct e.Title  FROM [dbo].[Employees] AS  e 3. WAQ to display Total Sales for each customer in October 1996 (based on OrderDate).       Show the result in CustomerID, CompanyName, and total sales, sorted in total sales in Decending order      select * from [dbo].[Customers] where CustomerID='SPLIR' select * from [dbo].[Orders] where CustomerID='SPLIR' select * from [dbo].[Order Details] where orderID in ( select orderID from [dbo].[Orders] where CustomerID='SPLIR') select cm.CustomerID,cm.Compan...

-- Inner join   select * from dbo.Products P           join Suppliers s on s.SupplierID=P.SupplierID OUTPUT: -- inner join using where clause   select * from dbo.Products P,            Suppliers s where s.SupplierID=P.SupplierID Output:

    use Northwind select * from [dbo].[Categories] select * from dbo.Categories select * from Categories select * from [Northwind].[dbo].[Categories] select * from Northwind.dbo.Categories

 /* ** Copyright Microsoft, Inc. 1994 - 2000 ** All Rights Reserved. */ SET NOCOUNT ON GO USE master GO if exists (select * from sysdatabases where name='Northwind') drop database Northwind go DECLARE @device_directory NVARCHAR(520) SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1) FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1 EXECUTE (N'CREATE DATABASE Northwind   ON PRIMARY (NAME = N''Northwind'', FILENAME = N''' + @device_directory + N'northwnd.mdf'')   LOG ON (NAME = N''Northwind_log'',  FILENAME = N''' + @device_directory + N'northwnd.ldf'')') go if CAST(SERVERPROPERTY('ProductMajorVersion') AS INT)<12  BEGIN   exec sp_dboption 'Northwind','trunc. log on chkpt.','true'   exec sp_dboption 'Northwind','select into/bulkcopy','true' END ELSE ALTER DATABASE [Northw...