--Day4 DB-Development, 12/8/2010
--------------------------------------------------------------------------------
Grouping and Sorting
--------------------------------------------------------------------------------

use employeeDB
select * from employees

alter table employees
update employees set salary = 4550 where ecode = 105
update employees set deptid = 209 where ecode = 105

select * from employees

select * from employees
order by salary

select * from employees
order by salary,deptid

select * from employees
order by salary desc,deptid asc

select deptid,sum(salary) "Total Salary", avg(salary) "Average Salary", max(salary) "Maximum Salary", min(salary) "Minimum Salary", count(deptid) "No. of Employees" from employees
where deptid >= 202
group by deptid
having avg(salary) > 3000
order by "Average Salary"

select * from employees

create table Product
(
ProdName varchar(20),
CatID int
)

drop table product

insert into Product values('Pepsi',1)
insert into Product values('Coco Cola',1)
insert into Product values('Mazza',2)
insert into Product values('Limca',2)
insert into Product values('Thumsup',1)

select * from product

create table ProdType
(
ProdType varchar(20),
CatID int Primary key
)
insert into ProdType values('Tin',1)
insert into ProdType values('Bottles',2)
select * from prodtype
select * from product

select ProdType from ProdType where catid = (Select catid from product where ProdName = 'pepsi')

select p.prodname,t.prodtype,p.catid from product p,prodtype t where p.catid=t.catid

select prodname, ProdType = case when catid = 1 then 'Tin'
when catid = 2 then 'Bottle'
end, catid
from product

select * from product

2> Display the titles from the table "Title" along with price category as
"Cheap","Expensive","Really Cheap"

create table title
(
title varchar(20),
Price money
)

select * from title
update title set Price = 40.00 where title = 'C#'
delete title where title = 'C#'

insert into title values ('C#', 40.00)
insert into title values ('JAVA', 66.00)
insert into title values ('.net', 59.00)
insert into title values ('C', 25.00)
insert into title values ('SQL', 30.00)
insert into title values ('Oracle', 19.00)

select title,Price,
PriceCat = case
when Price<60 and Price>30 then 'Cheap'
when Price>=60 then 'Expensive'
when Price<=30 then 'Really Cheap'
end
from title

3) Create a sql CLR procedure to write some text data to a file. e.g. exec sp_WriteToFile 'c: est.txt', ''
[Hint: Use logic to write to file as]
use System.IO;
public void WriteToFile (String fname,string data)
{
FileStream fs  = new FileStream(fname,FileMode.createNew,FileAccess.write);
StreamWriter swr = new streamWriter(fs);
swr.WriteLine(data);
swr.close();
fs.close();
}

select * from sys.sysdatabases
use employeeDB
update employees set salary = null where ecode = 101
select * from employees
___________________________________________
Usage of isnull(salary,0)
___________________________________________

select sum(salary) "Total Salary", avg(isnull(salary,0)) "Average Salary", max(salary) "Max Salry",count(isnull(salary,0)) "No. Of Employees"
from employees

use adventureworks
select * from sales.specialoffer
___________________________________________
FIRST

create procedure SALES.GetDiscounts
as
select Description,DiscountPct,Type,Category,StartDate,EndDate,MinQty,MaxQty
from sales.SpecialOffer
order by startdate, enddate

drop procedure GetDiscounts
exec SALES.GetDiscounts
___________________________________________
SECOND

CREATE PROCEDURE SALES.GetDiscountsForCategory (@Category varchar(50))
as
select Description,DiscountPct,Type,Category,StartDate,EndDate,MinQty,MaxQty
from sales.SpecialOffer where category = @category

exec SALES.GetDiscountsForCategory 'Customer'

___________________________________________
THIRD
use adventureworks
alter PROCEDURE SALES.GetDiscountsForCategoryAndDate (@Category varchar(50), @DateToCheck datetime)
as
set @DateToCheck=isnull(@DateToCheck,getdate())
select Description,DiscountPct,Type,Category,StartDate,EndDate,MinQty,MaxQty
from sales.SpecialOffer where category = @category and (@DateToCheck between startdate and enddate)

drop procedure SALES.GetDiscountsForCategoryAndDate
select * from sales.specialoffer

exec SALES.GetDiscountsForCategoryAndDate 'Reseller',null

___________________________________________
FOURTH

create function sales.GetMaximumDiscountForCategory (@category varchar(50)) returns float
as
begin
declare @date datetime, @Dscnt float
set @date = getdate()
select @Dscnt=max(DiscountPct) from sales.specialoffer where category = @category and enddate>@date
return @Dscnt
end

drop function sales.GetMaximumDiscountForCategory
declare @Dscnt float
select @Dscnt =  sales.GetMaximumDiscountForCategory('customer')
select @dscnt
select * from sales.specialoffer

___________________________________________
FIFTH

alter function sales.GetDiscountsForDate (@date datetime) returns table
as
return
(select * from sales.SpecialOffer
where @date between Startdate and endDate)
select * from sales.specialoffer

select * from sales.GetDiscountsForDate ('2004-07-01')

___________________________________________