Tuesday, July 16, 2013

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)


Tutorials on SAP-ABAP

Adobe Interactive Forms Tutorials

Business Server Pages (BSP)

Userexits/BADIs

Web Dynpro for ABAP (Step by step procedure for web dynpro,Tutorials on Web Dynpro,)

ALV Tutorials

goodsites