Practical Examples2

More Practical Examples

1.    Assuming system date as current date, solve the following

i)     Get the last day of week

ii)    Get the first day of week

iii)   Get the last day of the month

Note: The solution you provided should work for any given date.

2.    In the Northwind database, get the customerid,companyname and no.of orders made by each customer.

3.    A table is having the following information-

id	name	Age
1	A	25
2	B	26
3	C	24
2	B	26

Write a query to delete duplicate records.(only one record with sid 2 should exist).

4.    In my database I have a table with a column name ‘MyColumn1’. Write a query to get the names of the tables that contains this column.

A department store has many sections such as Toys, Cosmetics, Clothing, Household Items, and Electronics etc. Each section has many employees. Employees can belong to only one section. In addition, each section also has a head that is responsible for the section’s performance.

The department store also has many customers who purchase goods from various sections. Customers can be of two types Regular and Ad-hoc. Regular customers get credit at the department store. Maximum credit limit allowed is Rs.10000.

The store procures goods from various suppliers. The goods are stored in a warehouse and transferred to the store as and when requirement comes up. Quantity of goods supplied cannot be less than 0 and cannot be greater than 10000 for a particular supply.

The store has a computerized system for all its operations. Given below are the tables in the database:

·         Section ( SectionNo int, Description Varchar(30), Section_Head_EmpNo int)

·         Employees( EmpNo int, First_Name Varchar(30), Last_Name Varchar(30), Address Varchar(30), Grade int, Salary Numeric(9,2), SectionNo int, Date_Joined Datetime)

·         Customers( CustNo int, First_Name Varchar(30), Last_Name Varchar(30), CustType char(1) (with check constraint R/A), Credit_Limit Numeric(9,2), Credit_Card_No bigint, Credit_Card_Type Varchar(8))

·         Suppliers (SuppNo int,Name Varchar(30),Address Varchar(30), City Varchar(30))

·         Products (ProductNo int identity, Product_Category_Code Char(3),Description Varchar(30), Price Numeric(9,2), Qty_On_Hand int)

·         Product_CategoryMaster(Product_Category_Code Char(3),Description Varchar(15))

·         Suppliers_Products ( SuppNo int, ProductNo int, Date_Supplied Datetime, Qty_Supplied int)

·         Customers_Products( TransactionID int, CustNo int, ProductNo int, Date_of_Purchase Datetime, Qty_Purchased int)

The column names which have been underlined are the primary keys for the tables.

Create the tables with all appropriate constraints. Use the constraints UNIQUE, NOT NULL, CHECK, PRIMARY KEY, FOREIGN KEY etc. wherever necessary

5.            Find all employees whose names begin with ‘A’ and end with ‘A’. 

6.            Find the total salary paid by each section to employees.

7.            Display the section name and the name of the person who heads the section.

8.            Display supplier names and cities. If the city is null, display ‘LOCAL’.

9.            Display the customer names and the customer type. If the customer type is ‘R’ displays ‘Regular’, if the customer type is ‘A’ display ‘Ad-hoc’.

10.         Try creating a view which when used, will display the supplier names, product names , the quantity supplied and the date supplied for all records in Supp_Products. The listing should be in alphabetical order of supplier names.

11.         List all suppliers who have not supplied any products. Use an outer join to do this.

12.         Display the average salary of employees in each section.

13.         Display the customer no and the total number of products purchased by him, for those customers who have purchased at least 2 or more different products. Sort the listing in ascending order of customer number.

14.         Display the customer name and product names which have been purchased by him. The customer and product names should be in upper case.

15.         Display the products that have been supplied more than a month ago.

16.         Display employee names and the date joined for employees. Date should be in the format ‘DD-MMM-YYYY’.

17.         Display employee names and the number of months between today’s date and the joining date.

18.         Display product names and the price rounded to the nearest integer.

19.         Find the product which has the greatest price in each category.

T-SQL programming

20.         Write a Procedure code which will let you insert a record into the Section table. Use variables to represent the values of section_no,description and section_head_empno. What happens if you try to insert a value of section_no which already exists in the section table? Write proper error handler.

21.         Write a stored procedure to get the total quantity supplied for a particular product. The procedure should total the quantity, and the calling block should print the product no, product description and the total quantity supplied. The procedure should raise an exception if the total quantity supplied is 0, or the product does not exist.

22.         Write a procedure to insert a new record into the Suppliers_Products table, and also update the corresponding Qty_On_Hand for the product using a trigger.

Assume  an Employee and department tables with following description

Create table employee (empNo int,empName varchar(20),

empSalary numeric(8,2),grade char(1),location varchar(20))

create table department(deptno int,location varchar(20))

23.         Write a procedure to display the following information to the given employee number: Employee name (first_name + last_name) Employee section no Employee Designation Obtain the designation as follows:

Grade	Designation
1	Vice President
2	Senior Manager
3	Assistant Manager
4	Section Supervisor
5	Sales Assistant

24.         Whenever the location of the department changes, update the employee location also. (Use an AFTER UPDATE trigger for this).

25.         Add a new column called Performance_Measure Number(3,1) to the EMP table. The column can take values between 1 and 10(Use a check constraint to implement this). Whenever the performance measure of an employee is 8 or more, insert a record into a table called EXCEPTIONAL_EMPLOYEES, which contains the columns EMPNO and PERFORMANCE_LINKED_BONUS. Write a [AFTER INSERT OR AFTER UPDATE] trigger for this.

26.         Write a Trigger that gets fired whenever employee salaries are updated for a particular grade. The trigger inserts a record into the table SALARY_UPDATE_LOG that contains the USERNAME, DATE OF UPDATION of the user who updated the salary.

27.         Using northwind database, create a view that gets the information about orderid, orderdate, productid, proudctname , quantityorderd. Enter a new order using the view.(used instead of insert trigger)

28.         Write a user-defined function that gets the details of orders made by a given customerid. Call this function from a t-SQL program.(function should return a table).

Solutions:

1.

i) DECLARE @Date datetime

   select @Date = GETDATE()

   SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the       week'

ii) SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'

iii) SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)), DATEADD(m,1,@Date))) AS 'Last day of the month'

2.       select c.customerid,companyname,count(*) nooforderes from customers c inner join orders o

on c.customerid=o.customerid

group by c.customerid,companyname

3.

create table t1

(id int,name char(10),age int)

insert into t1

select 1,'A',25 union all

select 2,'B',26 union all

select 3,'C',24 union all

select 2,'B',26

 First, run the above GROUP BY query to determine how many sets of duplicate PK values exist, and the count of duplicates for each set.

Select the duplicate key values into a holding table. For example:

SELECT id,name,count=count(*)

INTO holdkey

FROM t1

GROUP BY id,name

HAVING count(*) > 1                                                  

Select the duplicate rows into a holding table, eliminating duplicates in the process. For example:

SELECT DISTINCT t1.*

INTO holddups

FROM t1, holdkey

WHERE t1.id = holdkey.id

AND t1.name = holdkey.name

                                                

           

At this point, the holddups table should have unique PKs, however, this will not be the case if t1 had duplicate PKs, yet unique rows (as in the SSN example above). Verify that each key in holddups is unique, and that you do not have duplicate keys, yet unique rows. If so, you must stop here and reconcile which of the rows you wish to keep for a given duplicate key value. For example, the query:

SELECT id, name, count(*)

FROM holddups

GROUP BY id, name                                                                  

should return a count of 1 for each row. If yes, proceed to step 5 below. If no, you have duplicate keys, yet unique rows, and need to decide which rows to save. This will usually entail either discarding a row, or creating a new unique key value for this row. Take one of these two steps for each such duplicate PK in the holddups table.

Delete the duplicate rows from the original table. For example:

DELETE t1

FROM t1, holdkey

WHERE t1.id = holdkey.id

                                                            

Put the unique rows back in the original table. For example:

INSERT t1 SELECT * FROM holddups

4 .      select o.name from syscolumns c,sysobjects o where c.id=o.id and c.name='MyColumn1'

(or)

select name from sysobjects where ID IN(select ID from syscolumns where name='MyColumn1')

5. select empno,First_Name,Last_name from employees where first_name like 'A%A'

6.       select sectionno,SUM(salary) TotalSal from employees group by sectionno

7.       select sec.sectionno,description,e.empno,First_name from section sec inner join employees e

on sec.section_head_empno=e.empno

8.       select suppno,name,isnull(city,'Local') from suppliers

9.       select Custno,First_name,Last_name,

case CustType

                    when 'R' then 'Regular'

                    when 'A' then 'Ad-hoc'

end from customers

         

10.     create view vSupOrders

as

select Sup.suppno,Sup.name,Sp.productno,p.description,qty_supplied, date_supplied from Suppliers sup inner join Suppliers_Products sp

on sup.SuppNo=sp.SuppNo

inner join Products p on sp.ProductNo=p.ProductNo

order by sup.Name

select * from vsuporders

11.     select sup.suppno,Name from suppliers sup left outer join suppliers_products sp

on sup.suppno=sp.suppno where sp.productno is null

12.     select sectionno,avg(salary) from employees group by sectionno

13.     select custno,productno,COUNT(productno) NoOfOrders from customers_products group by custno,productno order by custno

14.     select upper(first_name),upper(description) from customers cu inner join customers_products cp on cu.custno=cp.custno

inner join products p on cp.productno=p.productno

15.     select productno,date_supplied from suppliers_products where date_supplied

16.     select first_name,last_name,convert(char,date_joined,105) from employees

17.     select first_name,last_name,DATEDIFF(mm,date_joined,getdate()) from employees

18.     select productno,description,ceiling(price) from products

19.     select p.productno,p.product_category_code,p.price

from products p inner join (select product_category_code,max(price) maxPrice from products group by product_category_code)  a

on p.product_category_code=a.product_category_code and p.Price=maxPrice order by p.Product_Category_Code

20.     create procedure pInsertSection

(@sno int,@desc varchar(30),@shempno int)

as

begin

          begin try

                   insert into Section values(@sno,@desc,@shempno)

          end try

          begin catch   

                   if ERROR_NUMBER()=2627

                      print 'Duplicate SectionNo specified. Violated Primary key’'

                   else

print Convert(varchar(30),Error_Number()) + '    ' + ERROR_MESSAGE()                          

          end catch

end

exec pinsertsection 3,'Sports',2003

21.    

alter procedure pGetProductQtySupplied(@pid int)

as

begin

  declare @count int

  select @count= COUNT(*) from Suppliers_Products where ProductNo=@pid

  if @count=0   

            raiserror('Productno doenot exist',10,1) 

else   

          select p.productno,p.description,sum(qty_supplied) from products p,suppliers_products su

          where p.productno=su.productno and su.productno=@pid group by p.productno,p.description

         

end

exec pGetProductQtySupplied 10

22.    

create procedure pInsertSupplierProducts(@sno int,@pid int,@dtSupp datetime,@qtySupp int)

as

begin

          insert into Suppliers_Products values(@sno,@pid,@dtSupp,@qtySupp)  

end

create trigger tUpdateProdcutQty

on Suppliers_products

for insert

as

begin

          declare @qSupp int,@pid int

          select @qSupp=qty_supplied from inserted

          select @pid=productno from inserted

          update products set qty_on_hand=Qty_on_hand+@qsupp where productno=@pid

end

exec pinsertsupplierproducts 102,1,'3/13/10',100

Assume  an Employee and department tables with following description

Create table emp (empNo int,empName varchar(20),

empSalary numeric(8,2),grade char(1),location varchar(20))

create table department(deptno int,location varchar(20))

23.    

select first_name + ' ' + last_name,sectionno,

case Grade

          when 1 then 'Vice President'

          when 2 then 'Senior Manager'

          when 3 then 'Assistant Manager'

          when 4 then 'Section Supervisor'

          when 5 then 'Sales Assistant'

end Designation from employees

         

24.    

create trigger trgUpdateLocation

on department

for update

as

begin

          declare @oldloc varchar(20),@newloc varchar(20)

          select @newloc=location from inserted

          select @oldloc=location from deleted

          update emp set location=@newloc where location=@oldloc

end

testing:

select * from emp

select * from department

update department set location='Sec' where location='Secunderabad'

25.     alter table emp add Performance_measure numeric(3,1) constraint ck_emp_pm check(performance_measure between 1 and 10)

create table EXCEPTIONAL_EMPLOYEES

(EMPNO int,PERFORMANCE_LINKED_BONUS numeric(7,2))

create trigger trgPeromance

on emp

for insert,update

as

begin

          declare @eid int,@pm numeric(3,1)

          select @eid=empno from inserted

          select @pm=performance_measure from emp where empno=@eid

          if @pm>=8

            insert into exceptional_employees values(@eid,10000)  

end

select * from emp

select * from EXCEPTIONAL_EMPLOYEES

update emp set performance_measure=9 where empno=100

26.     create table SALARY_UPDATE_LOG

(username varchar(20),DateofUpdate datetime)

create trigger trgUpdateSalary

on emp

for update

as

begin

          insert into SALARY_UPDATE_LOG values(System_user,getdate())

end

testing:

select * from emp

select * from SALARY_UPDATE_LOG

update emp set empsalary=empsalary+50000 where empNo=101

27.    

CREATE VIEW Customerorders_vw 

with schemabinding 

as 

select o.orderid,o.orderdate,oi.productid,oi.quantity,oi.unitprice 

from dbo.orders o inner join dbo.[order details] oi 

on o.orderid=oi.orderid 

inner join dbo.products p on oi.productid=p.productid

select * from customerorders_vw

insert into customerorders_Vw values(1,GETDATE(),2,10,150)

create trigger trgCustOrdInsert on customerorders_vw

instead of insert

as

begin

          if(select count(*) from inserted)>0

                   insert into dbo.[order details](orderid,productid,unitprice,quantity)

                   select i.orderid,i.productid,i.unitprice,i.quantity

                   from inserted i join orders  o

                   on i.orderid=o.orderid

                  

                   if @@rowcount=0

                             raiserror('no matching orders. cannot perform insert',10,1)

end

insert into customerorders_Vw values(1,GETDATE(),2,10,150)

28.    

create function fnGetOrders(@cid NCHAR(5))

returns table

as

    return(select Cu.Customerid,o.orderid,o.orderdate,od.productid,

p.productname, od.unitprice,od.quantity

          from customers cu inner join orders o on cu.customerid=o.customerid

          inner join [order details] od on o.orderid=od.orderid

          inner join products p on od.productid=p.productid  

          where cu.customerid=@cid)

Testing:

select * from dbo.fnGetOrders('HANAR')

dbcc checkident('customers',reseed,30)