Scripts

1.

PAGE 1

use master use abisanka create database sarkar create table abi (names varchar(20), address varchar(30), phone int, uid varchar(5) primary key) drop table abi select * from abi insert into abi values('abisanka','hooghly','123456','001') insert into abi values('amar','dankuni','456123','002') insert into abi values('bidit','haldia','234789','003') insert into abi values('subhash','hooghly','458723','004') update abi set address='calcutta' where uid='001' alter table abi add stream varchar(10) alter table abi drop column stream delete from abi select * from abi select * from abi where address like '%y' select * from abi where address like 'h%' select * from abi where address like '%oo%' 2.

PAGE 2

/*CREATE TABLE*/ create table student (studentid int identity(1,2), studentname varchar(20), studentfees int) select * from student

/*INSERT DATA*/ insert into student(studentname,studentfees)values('abisanka',5000) insert into student(studentname,studentfees)values('Rahul',5560) insert into student(studentname,studentfees)values('Bidit',7000) insert into student(studentname,studentfees)values('Amar',5890) insert into student(studentname,studentfees)values('Subhas',8000) insert into student(studentname,studentfees)values('Badal',9000) insert into student(studentname,studentfees)values('Sameer',4400) insert into student(studentname,studentfees)values('Raj',5000) insert into student(studentname,studentfees)values('Abhijit',4000) insert into student(studentname,studentfees)values('Soumya',4400) insert into student(studentname,studentfees)values('Sonu',8500) insert into student(studentname,studentfees)values('Badal',8000) insert into student(studentname,studentfees)values('Rohit',6000) /*LIKE*/ select studentname from student where studentname like 'a%' select studentname from student where studentname like '__[d]%' select studentname from student where studentname like '%r' select studentname from student where studentname like '%bh%' /*BETWEEN*/ select studentfees from student where studentfees between 5000 and 7000 /*NOT IN*/ select studentfees, studentid from student where studentfees not in (select studentfees from student where studentname='badal') /*IN*/ select studentfees, studentname, studentid from student where studentfees in (select studentfees from student where studentname like 'a%')

/*CREATE TABLE*/ create table admin (sid int identity(1,2), sclass int) select * from admin

/*PROCEDURE*/ /*insert*/

create procedure sp_insertadmin @sclass int as insert into admin values(@sclass) Go exec sp_insertadmin 10 exec sp_insertadmin 08 exec sp_insertadmin 06 exec sp_insertadmin 11 exec sp_insertadmin 12 exec sp_insertadmin 19 exec sp_insertadmin 10 /*create*/ create procedure sp_copy as create table tbl (age int, salary int) go exec sp_copy /*insert*/ create procedure sp_inserttbl @age int, @salary int as insert into tbl values(@age,@salary) Go exec sp_inserttbl 20,12000 exec sp_inserttbl 20,12000 exec sp_inserttbl 25,16000 exec sp_inserttbl 21,13000 exec sp_inserttbl 27,18000 /*delete*/ create procedure sp_deletetbl @age int as delete from tbl where age=@age go exec sp_deletetbl 20 select * from tbl /*update*/ create procedure sp_updatetbl @age int, @salary int as update tbl

set salary=@salary where age=@age go exec sp_updatetbl 21,50000 /*alter*/ create procedure sp_altertbl as alter table tbl add car varchar(20) go exec sp_altertbl /*JOIN*/ select student.studentname, student.studentfees,admin.sclass from student inner join admin on student.studentid=admin.sid select student.studentname,admin.sclass from student left outer join admin on student.studentid=5 select distinct student.studentname,admin.sclass from student left outer join admin on student.studentid=5 select student.studentname,admin.sclass from student right outer join admin on student.studentid=7

select * from student select * from admin select avg(studentfees) "Average" from student select min(studentfees) "Minimum Fees" from student select max(studentfees) "Maximum Fees" from student alter table student add extra_from_avg int

/*PROCEDURE*/ create procedure sp_alld @studentid int as update student

set extra_from_avg=((select studentfees from student where studentid=@studentid)-(select avg(studentfees) from student)) where studentid=@studentid go exec sp_alld 1 select * from student select getdate() "Date Time" select count(*) from student sp_helpdb sp_helpdb abisanka select table_name from information_schema.tables

drop procedure sp_alld

--go select * from student select max(studentid) from student select studentfees from student where studentid=4 3.

PAGE 3

use abisanka select getdate() as Date select cast('sarkar' as varchar(10)) as Abisanka select Getdate() as Date, CONVERT(varchar(15),Getdate(),100)as Date100 select Getdate() as Date, CONVERT(varchar(15),Getdate(),1)as Date1 select Getdate() as Date, CONVERT(varchar(15),Getdate(),4)as Date4 select Getdate() as Date, CONVERT(varchar,Getdate(),111)as Date111 select getdate() as Date, datediff(year,'11/29/1983', getdate()) as Age select getdate() as Date, datediff(day,'11/29/1983', getdate()) as Days select getdate() as Date, datediff(month,'11/29/1983', getdate()) as Months

select Datename(year,Getdate()) as Years select Datename(month,Getdate()) as Months select Datename(day,Getdate()) as Days select datepart(month,getdate()) as Months select datepart(day,getdate()) as Days select datepart(month,'10/24/2008') as Months select dateadd(mm,11,getdate()) select dateadd(hh,11,getdate()) select dateadd(yy,100,getdate()) set datefirst 1 create function sarkar() returns table as return(select * from student) alter function sarkar() returns table as return(select studentid from student)

select * from dbo.sarkar() create function adds(@a int,@b int) returns int as Begin declare @s int set @s=0 if(@a>@b) Begin set @s=(@a-@b) end else begin set @s=(@a+@b) end return(@s) end create function i5(@name varchar(10), @address varchar(20)) returns varchar(80) as Begin declare @temp varchar(10) declare @total varchar(50) set @temp='Lives in' set @total=@name + @temp + @address return (@total) end

select dbo.i5('Amar','Dankuni') sp_renamedb abisanka sarkar /*Answers*/ select getdate() as Date, datediff(week,'01/01/2004',getdate()) select datepart(month,getdate()) create table icra1 (ids int identity, names varchar(20), address varchar(20)) insert into icra1(names,address) values('A','kolkata') insert into icra1(names,address) values('B','salt lake') insert into icra1(names,address) values('C','new town') select * from icra1 set identity_insert icra1 on update icra1 set ids=6 where names='c' insert into icra1(ids,names,address) values(6,'d','rajarhat')

select square(4) select sqrt(4) select convert(varchar(15),getdate(),1) as Dates create function f(@a int) returns int as Begin declare @i int declare @result int set @i=1 set @result=1 while(@i<=@a) Begin set @result=@i*@result set @i=@i+1 End return (@result)

end

select dbo.f(5) create function i7(@name varchar(10), @address varchar(20)) returns int as Begin declare @a int set @a=0 declare @total varchar(50) set @total=@name+@address print '@total' return @a end select dbo.i7('jahgjh','kjhkj')

4.

PAGE 4

use abisanka select table_name from information_schema.tables select * from abi select * from admin select * from icra1 select * from reg select * from student select * from tbl alter table student add less_from_avg int /*CURSOR*/ declare d2 cursor for select studentid from student declare @ids int declare @temp1 int declare @temp2 int open d2 fetch next from d2 into @ids while @@fetch_status=0 Begin set @temp1=(select studentfees from student where studentid=@ids) set @temp2=(select avg(studentfees) from student)

if(@temp1>@temp2) update student set extra_from_avg=(@temp1-@temp2) where studentid=@ids else update student set less_from_avg=(@temp2-@temp1) where studentid=@ids fetch next from d2 into @ids end close d2 deallocate d2

sp_helpdb abisanka

create table [table] (names varchar(10), roll int) select * from [table] select * from sysobjects

5.

PAGE 5

select * from student for xml auto select * from sysobjects where xtype='FN' exec sp_stored_procedures 6.

ASSIGNMENT

--CREATE create table t_artist (artist_id int identity(001,1) primary key, artist_name varchar(20) not null) select * from t_artist drop table t_artist create table t_album (album_id int identity(01,2) primary key, album_name varchar(40) not null, album_vol int, album_cost decimal(10,2), release_date varchar(15)) --check(release_date like '[dd/mm/yyyy]')) select * from t_album

drop table t_album create table t_genre (gen_id int identity(20,2) primary key, gen_type varchar(20) not null) select * from t_genre drop table t_genre create table t_song (song_id int identity(100,1) primary key, song_name varchar(50) not null, song_duration decimal(10,2), album_id int references t_album, gen_id int references t_genre) select * from t_song drop table t_song create table t_artist_song (artist_id int references t_artist, song_id int references t_song) select * from t_artist_song drop table t_artist_song

--INSERT insert into t_artist(artist_name) values('Cellin Dion') insert into t_artist(artist_name) values('Backstreet Boys') insert into t_artist(artist_name) values('Nachiketa') insert into t_artist(artist_name) values('Sonu Nigam') insert into t_artist(artist_name) values('Indraneel Sen') insert into t_artist(artist_name) values('Enrique Iglious') insert into t_artist(artist_name) values('Shaan') insert into t_artist(artist_name) values('Shankar-Ehsaan-Loy') insert into t_artist(artist_name) values('Britney Spears') insert into t_artist(artist_name) values('Anup Ghosal') select * from t_artist select * from t_song insert into t_album(album_name,album_vol,album_cost,release_date) values('Titanic',760,124.35,'22/03/2001') insert into t_album(album_name,album_vol,album_cost,release_date) values('Blue & Black',606,100.00,'31/05/2002') insert into t_album(album_name,album_vol,album_cost,release_date) values('Ei Besh Bhalo Achi',NULL,95.75,'02/04/2000')

insert into t_album(album_name,album_vol,album_cost,release_date) values('Dewana',567,NULL,'03/05/1998') insert into t_album(album_name,album_vol,album_cost,release_date) values('Neel Dhrubatara',609,65.98,NULL) insert into t_album(album_name,album_vol,album_cost,release_date) values('Rhythms',725,NULL,NULL) insert into t_album(album_name,album_vol,album_cost,release_date) values('Tanha Dil',NULL,76.50,NULL) insert into t_album(album_name,album_vol,album_cost,release_date) values('Dil Chahta Hai',NULL,NULL,'12/03/2003') insert into t_album(album_name,album_vol,album_cost,release_date) values('Special',NULL,NULL,NULL) insert into t_album(album_name,album_vol,album_cost,release_date) values('GupiGayenBaghaBayen',567,NULL,'31/07/1976') select * from t_album insert into t_genre(gen_type) values('Romantic') insert into t_genre(gen_type) values('Rock') insert into t_genre(gen_type) values('Tragic') insert into t_genre(gen_type) values('Evergreen') insert into t_genre(gen_type) values('Pop') select * from t_genre

insert into t_song(song_name,song_duration,album_id,gen_id) values ('Titanic',5.05,1,20) insert into t_song(song_name,song_duration,album_id,gen_id) values ('The Call',4.35,3,22) insert into t_song(song_name,song_duration,album_id,gen_id) values ('Nilanjana',NULL,5,24) insert into t_song(song_name,song_duration,album_id,gen_id) values ('Is Kadar Pyar',5.45,7,20) insert into t_song(song_name,song_duration,album_id,gen_id) values ('Ei Baluka Belay',NULL,9,26) insert into t_song(song_name,song_duration,album_id,gen_id) values ('Rhythm Divine',7.02,11,28) insert into t_song(song_name,song_duration,album_id,gen_id) values ('Gumsum Ho Kyon',6.34,13,20) insert into t_song(song_name,song_duration,album_id,gen_id) values ('Koi Kahe kehta Rahe',7.46,15,22) insert into t_song(song_name,song_duration,album_id,gen_id) values ('Everyone',NULL,17,28) insert into t_song(song_name,song_duration,album_id,gen_id) values ('Maharaja',5.20,19,26) select * from t_song insert into t_artist_song(artist_id,song_id) values(1,100) insert into t_artist_song(artist_id,song_id) values(2,101) insert into t_artist_song(artist_id,song_id) values(3,102) insert into t_artist_song(artist_id,song_id) values(4,103) insert into t_artist_song(artist_id,song_id) values(5,104) insert into t_artist_song(artist_id,song_id) values(6,105) insert into t_artist_song(artist_id,song_id) values(7,106) insert into t_artist_song(artist_id,song_id) values(8,107) insert into t_artist_song(artist_id,song_id) values(9,108) insert into t_artist_song(artist_id,song_id) values(10,109)

select * from t_artist_song

--ANSWERS -- 3. create procedure sp_song @song varchar(50) as select p.artist_name as Artist,q.song_duration as Duration,r.gen_type as Genre,s.album_name as Album,s.album_vol as Volume from t_artist p,t_song q,t_genre r, t_album s,t_artist_song t where p.artist_id=t.artist_id and q.song_id=t.song_id and q.gen_id=r.gen_id and q.album_id=s.album_id and q.song_name=@song go exec sp_song 'Titanic'

--4. create view mylist2 with schemabinding as select p.song_name,q.artist_name,p.song_id from dbo.t_song p,dbo.t_artist q,dbo.t_artist_song r where q.artist_id=r.artist_id and p.song_id=r.song_id select * from mylist2 drop view mylist2 create unique clustered index ind on mylist2(song_id)

--5. create procedure sp_album @album varchar(50)

as select s.album_id as AlbumID,s.album_vol as Volume,s.album_cost as Cost,s.release_date as Released_On, q.song_name as Song,p.artist_name as Artist,q.song_id as SongID,r.gen_type as Genre, q.song_duration as Duration,r.gen_id as GenreID--,count(q.song_name) as Total from t_artist p, t_song q, t_genre r, t_album s, t_artist_song t

where p.artist_id=t.artist_id and q.song_id=t.song_id and q.gen_id=r.gen_id and q.album_id=s.album_id and s.album_name=@album go exec sp_album 'Titanic' --6. alter procedure sp_final6 @artist_name varchar(20),@song_name varchar(50),@song_duration decimal(10,2) as declare @artist_id int,@song_id int,@album_id int,@max int --Begin If (exists(select artist_name from t_artist where artist_name=@artist_name)and(exists(select song_name from t_song where song_name=@song_name))) Begin Print 'Record Exists ! Please Enter something else' End else set @max=(select max(album_id)from t_song) If (exists(select artist_name from t_artist where artist_name=@artist_name)and(not exists(select song_name from t_song where song_name=@song_name))) Begin insert into t_song(song_name,song_duration,album_id) values (@song_name,@song_duration,@max) set @artist_id=(select artist_id from t_artist where artist_name=@artist_name) set @song_id=(select song_id from t_song where song_name=@song_name) insert into t_artist_song(artist_id,song_id) values (@artist_id,@song_id) End

else If (not exists(select artist_name from t_artist where artist_name=@artist_name)and(not exists(select song_name from t_song where song_name=@song_name))) Begin insert into t_artist(artist_name) values(@artist_name) insert into t_song(song_name,song_duration,album_id) values (@song_name,@song_duration,@max) set @artist_id=(select artist_id from t_artist where artist_name=@artist_name) set @song_id=(select song_id from t_song where song_name=@song_name) insert into t_artist_song(artist_id,song_id) values (@artist_id,@song_id) End --End GO

exec sp_final6 'Shaan','Chand',6.00 exec sp_final6 'Kumar Sanu','jab se',7.26

select * from t_artist select * from t_song select * from t_artist_song

--7. create procedure sp_or18 @album varchar(30) as declare @album_vol int,@album_cost int,@release_date varchar(20) set @album_vol=(select album_vol from t_album where album_name=@album) set @album_cost=(select album_cost from t_album where album_name=@album) set @release_date=(select release_date from t_album where album_name=@album) if((@album_vol is NULL) and (@album_cost is NULL) and (@release_date is NULL)) Begin Print 'NO RECORD FOUND!!!' End /*if((@album_vol is NULL) and (@album_cost is NULL)) Begin select release_date from t_album where album_name=@album Print'ALBUM VOLUME AND ALBUM COST NOT FOUND ' End if((@album_cost is NULL) and (@release_date is NULL)) Begin select album_vol from t_album where album_name=@album

Print'RELEASE DATE AND ALBUM COST NOT FOUND ' End if((@album_vol is NULL)and(@release_date is NULL)) Begin select release_date from t_album where album_name=@album Print'AlBUM VOLUME AND ALBUM COST NOT FOUND ' End if(@album_vol is NULL) Begin select release_date,album_cost from t_album where album_name=@album Print 'ALBUM VOLUME NOT FOUND' End if(@album_cost is NULL) Begin select release_date,album_vol from t_album where album_name=@album Print 'ALBUM COST NOT FOUND' End if(@release_date is NULL) Begin select album_vol,album_cost from t_album where album_name=@album Print 'RELEASE DATE NOT FOUND' End*/ Go exec sp_or18 'special' exec sp_or15 'Tanha Dil'

--8. -- MODIFYING QUESTION No. 6's ANSWER create procedure sp_test20 @artist_name varchar(20),@song_name varchar(50),@song_duration decimal(10,2)--,@album_id int,@gen_id int as declare @artist_id int,@song_id int

If (exists(select artist_name from t_artist where artist_name=@artist_name)) Begin Print 'Artist Exists ' Print 'No Record has been updated' End If (not exists(select artist_name from t_artist where artist_name=@artist_name)) Begin insert into t_artist(artist_name) values(@artist_name)

insert into t_song(song_name,song_duration) values (@song_name,@song_duration) set @artist_id=(select artist_id from t_artist where artist_name=@artist_name) set @song_id=(select song_id from t_song where song_name=@song_name) insert into t_artist_song(artist_id,song_id) values (@artist_id,@song_id) Print 'Three tables have been updated' End Go

select * from t_artist select * from t_song select * from t_artist_song exec sp_test20 'Mohit Chauhan','Tum Se hi',5.6

SELECT @@CPU_BUSY SELECT @@IDLE 7.

ASSIGNMENT 2

create database bappa create table Client_master( Client_no varchar(6) primary key, Name varchar(20)not null, Address1 varchar(30), Address2 varchar(30), City varchar(15), Pincode int, State varchar(15), Bal_due decimal(10,2), check (Client_no like 'C%')) create table Product_master( Product_no varchar(6) primary key, Description varchar(15) not null, Profit_percent decimal(4,2) not null, Unit_measure varchar(10) not null, Qty_on_hand int not null, Reorder_lvl int not null, Sell_price decimal(8,2) not null, Cost_price decimal(8,2) not null, check (Product_no like 'P%'))

create table Salesman_master( Salesman_no varchar(6) primary key, Salesman_name varchar(20) not null, Address1 varchar(30) not null, Address2 varchar(30), City varchar(20), Pincode varchar(8), Sate varchar(20), Sal_amt decimal(8,2) not null, Tgt_to_get decimal(6,2) not null, Yrd_sales decimal(6,2) not null, Remarks varchar(60), check (Salesman_no like 'S%')) create table Sales_order( Order_no varchar(6) primary key, Order_date datetime, Client_no varchar(6) references Client_master(Client_no), Dely_addr varchar(25), Salesman_no varchar(6) references Salesman_master(Salesman_no), Dely_type char, Billed_yn char, Dely_date datetime, Order_status varchar(10), check (Order_no like 'O%')) create table Sales_order_details( Order_no varchar(6) references Sales_order(Order_no), Product_no varchar(6) references Product_master(Product_no), Qty_ordered int, Qty_disp int, Product_rate decimal(10,2)) insert into Client_master values ('C00001','Ivan Bayross','','','Bombay',400054,'Maharashtra',15000) insert into Client_master values ('C00002','Vandana Saitwal','','','Madras',780001,'Tamil Nadu',0) insert into Client_master values ('C00003','Pramada Jaguste','','','Bombay',400057,'Maharashtra',5000) insert into Client_master values ('C00004','Basu Navindgi','','','Bombay',400056,'Maharashtra',0) insert into Client_master values ('C00005','Ravi Sreedharan','','','Delhi',100001,'Delhi',2000) insert into Client_master values ('C00006','Rukmini','','','Bombay',400050,'Maharashtra',0) insert into Product_master values('P00001','1.44 Floppies',5,'Piece',100,20,525,500) insert into Product_master values('P03453','Monitors',6,'Piece',10,3,12000,11280) insert into Product_master values('P06734','Mouse',5,'Piece',20,5,1050,1000) insert into Product_master values('P07865','1.22 Floppies',5,'Piece',100,20,525,500) insert into Product_master values('P07868','Keyboards',2,'Piece',10,3,3150,3050) insert into Product_master values('P07885','CD Drive',2.5,'Piece',10,3,5250,5100) insert into Product_master values('P07965','540 HDD',4,'Piece',10,3,8400,8000) insert into Product_master values('P07975','1.44 Drive',5,'Piece',10,3,1050,1000) insert into Product_master values('P08865','1.22 Drive',5,'Piece',2,3,1050,1000) insert into Salesman_master

values('S00001','Kiran','A/14','Worli','Bombay','400002','Maharastra',3000,100,50,'Good') insert into Salesman_master values('S00002','Manish','65','Nariman','Bombay','400001','Maharastra',3000,200,100,'Good') insert into Salesman_master values('S00003','Ravi','P-7','Bandra','Bombay','400032','Maharastra',3000,200,100,'Good') insert into Salesman_master values('S00004','Ashish','A/5','Juhu','Bombay','400044','Maharastra',3500,200,150,'Good') insert into Sales_order values('O19001','12-Jan-96','C00001','','S00001','F','N','20-Jan-96','In Process') insert into Sales_order values('O19002','25-Jan-96','C00002','','S00002','P','N','27-Jan96','Cancelled') insert into Sales_order values('O46865','18-Feb-96','C00003','','S00003','F','Y','20-Feb96','Fulfilled') insert into Sales_order values('O19003','03-Apr-96','C00001','','S00001','F','Y','07-Apr96','Fulfilled') insert into Sales_order values('O46866','20-May-96','C00004','','S00002','P','N','22-May96','Cancelled') insert into Sales_order values('O19008','24-May-96','C00005','','S00004','F','N','26-May-96','In Process') insert into Sales_order_details values('O19001','P00001',4,4,525) insert into Sales_order_details values('O19001','P07965',2,1,8400) insert into Sales_order_details values('O19001','P07885',2,1,5250) insert into Sales_order_details values('O19002','P00001',10,0,525) insert into Sales_order_details values('O46865','P07868',3,3,3150) insert into Sales_order_details values('O46865','P07885',3,1,5250) insert into Sales_order_details values('O46865','P00001',10,10,525) insert into Sales_order_details values('O46865','P03453',4,4,1050) insert into Sales_order_details values('O19003','P03453',2,2,1050) insert into Sales_order_details values('O19003','P06734',1,1,12000) insert into Sales_order_details values('O46866','P07965',1,0,8400) insert into Sales_order_details values('O46866','P07975',1,0,1050) insert into Sales_order_details values('O19008','P00001',10,5,525) insert into Sales_order_details values('O19008','P07975',5,3,1050) /*ANSWERS TO THE ASSIGNMENT*/ --3.a) select Name from client_master where (name like '_a%') --b) select Name from client_master where (City like '_a%') --c) select Name from client_master where City like 'Delhi' or City like 'Bombay' --d)

select Name from Client_master where (Bal_due > 10000) --e) select *from Sales_order where convert(varchar(10),Month(Order_date),100)=1 --f) select *from Sales_order where Sales_order.Client_no = 'C00001' or Sales_order.Client_no='C00002' --g) select description from Product_master where Sell_price>2000 and Sell_price<=5000 --h) select sell_price as Old_price, Sell_price *.15 as New_Price from Product_master --i) select Name,City,State from Client_master where State not in ('Maharashtra') --j) select count(Qty_on_hand) as Total_order from Product_master select sum(Qty_on_hand) as Total_order from Product_master --j) select count(Order_no) from Sales_order_details --k) select avg(Sell_price) as Avg_Sell_Price,avg(Cost_price) as Avg_Cost_price from Product_master select avg(Sell_price) - avg(Cost_price) as Avg_Profit from Product_master select avg(Product_rate) as Avg_Rate from Sales_order_details --l) select max(Product_rate) as Max_Price,min(Product_rate) as Min_Price from Sales_order_details --m) select count(product_no)from Sales_order_details where Product_rate >=1500 --n) select * from Product_master where Qty_on_hand < Reorder_lvl

--4(a) select order_no,convert(varchar(20),order_date,109) from sales_order

--4(b) select datename(month,dely_date),datename(day,dely_date) from sales_order --4(c) select replace(convert(varchar(11),order_date,6),' ','-') from sales_order --4(d) select dateadd(dd,15,getdate()) --4(e) select datediff(day,dely_date,getdate())from sales_order --5(a) select p.description,sum(s.qty_disp) from Product_master p,sales_order_details s where p.product_no=s.product_no group by p.description --5(b) select p.description,sum(s.qty_disp * s.product_rate) from Product_master p left outer join sales_order_details s on p.product_no=s.product_no group by p.description --5(c) select c.client_no, avg(so.qty_disp) from client_master c, sales_order_details so,sales_order s where so.order_no=s.order_no and s.client_no=c.client_no group by c.client_no having sum(so.qty_ordered*so.product_rate)<=15000 select qty_ordered*product_rate, product_no,order_no from sales_order_details

--5(d) select sum(s.qty_disp*s.product_rate) from sales_order_details s, sales_order p where s.order_no=p.order_no and month(p.order_date)=1 group by p.order_no --6(a) select c.name,p.description from client_master c, product_master p,sales_order_details so,sales_order s where s.client_no =c.client_no and s.order_no=so.order_no and so.product_no=p.product_no and

c.name='Ivan Bayross' --6(b) select p.description,so.qty_disp from product_master p,sales_order s,sales_order_details so where s.order_no=so.order_no and so.product_no=p.product_no and month(s.dely_date)= month(getdate()) --6(c) select distinct p.product_no,p.description from product_master p,sales_order_details s where p.product_no=s.product_no and s.qty_ordered=(select max(qty_ordered) from sales_order_details) --6(d) select distinct c.name from client_master c,product_master p, sales_order s, sales_order_details so where c.client_no=s.client_no and so.order_no=s.order_no and so.product_no=p.product_no and p.description='CD Drive' --6(e) select p.product_no,so.order_no from sales_order_details so,product_master p where so.product_no=p.product_no and p.description='1.44 Floppies' and so.qty_ordered<=5 --6(f) select c.name, p.description,so.qty_ordered from product_master p,client_master c,sales_order_details so, sales_order s where p.product_no=so.product_no and so.order_no=s.order_no and s.client_no=c.client_no and c.name='Ivan bayross' or c.name='Vandana Saitwal' --6(g) select c.client_no, p.description,so.qty_ordered from product_master p,client_master c,sales_order_details so, sales_order s where p.product_no=so.product_no and so.order_no=s.order_no and s.client_no=c.client_no and c.client_no='c00001' or c.client_no='c00002' --7(a)

select product_no,description from product_master where qty_on_hand=(select max(qty_on_hand) from product_master)

--7(b) select name,city,state,pincode from client_master where client_no = (select client_no from sales_order where order_no='O19001') --7(c) select distinct name from client_master,sales_order where client_master.client_no=sales_order.client_no and order_date<'05-01-1996' --7(d) select c.client_no,c.name from client_master c,sales_order s, product_master p, sales_order_details so where c.client_no=s.client_no and s.order_no=so.order_no and so.product_no=p.product_no and p.description='1.44 Drive' --7(e) select c.name,sum(so.qty_ordered*so.product_rate)as Amount from client_master c,sales_order s,sales_order_details so where so.order_no=s.order_no and s.client_no=c.client_no group by c.name having sum(so.qty_ordered*so.product_rate)>=10000

--8(a) select (p.description) + ' ' + 'Worth Rs.'+' '+ (Convert(varchar(15),sum(s.qty_disp*s.Product_rate)))+' '+ 'was sold' as Result from product_master p,sales_order_details s where p.product_no=s.product_no group by p.description --8(b)

select (p.description) + ' ' + 'Worth Rs.'+' '+ (Convert(varchar(15),sum(s.qty_disp*s.Product_rate)))+' '+ 'was ordered in the month'+' '+ (convert(varchar(10),datename(month,so.order_date))) as Result from product_master p,sales_order_details s,sales_order so where p.product_no=s.product_no and so.order_no=s.order_no group by p.description,so.order_date --8(C) select (c.name) + ' ' + 'has placed order'+' '+ (s.order_no)+' '+ 'on'+' '+(convert(varchar(11),s.order_date)) as Result from client_master c,sales_order s where c.client_no=s.client_no