Yamini Chandramouli

Exercises on Stored Procedures and Functions: 1. Create a function that should take two inputs those are product id and location id and should return stock level. Note: Consider product Inventory table in adventurworks database for this requirement. 2. Create a function that should take input as store id and returns the columns ProductID, Name, and the aggregate of line total for each product sold to the store. Note: consider product, sales order details, sales order header and customer tables for this requirements. 3. Someone deleted few records from a table which has an IDENTITY column, so need to create stored procedures to find missing sequential numbers. Note: for this requirement create sample table with identity column and insert some data and delete few records from table. 4. Sales order header table in adventureworks database as follows Requirement: Need to create stored procedure that should take sortdate as argument (varchar datatype) then it should displ...

Exercises on Functions: 1. Write a Function which returns Ename from EMP table by taking empno as argument. 2. Write a scalar value return function which returns sum of two given numbers. 3. Write a Function to display biggest of 3 given numbers(need to take 3 argument for 3 values) 4. Write a Function to display maximum salary of a given deptno from EMP table. 5. Write a Function to display the SUM, AVG sal of employees according to their deptno’s. 6. Write a Function to display details of departments and their corresponding Employee details from DEPT and EMP tables according to deptno. 7. Write a Function to display all the employee details whose salary between first argument value and second argument value. 8. Write a function to display the Names of Employees whose name contain particular character (character we need to take from argument). 9.Write a function to display specific number of senior most employees in arganization. 10.write a table valued return func...

Exercises on TSQL Programing: 1. Write a T-SQL program to display the product of given 2 numbers. 2. Write a program to find maximum, minimum, Midvale of given three no’s. 3. Write a program to display numbers from 1 to10 except 4,6 and 7. 4. Write a program to display the numbers between 40 and 50 numbers in descending order. 5. Write a program to display odd multiples of 5 up to 10. 6. Write a program to display 5th table. 7. Write a program to display 2nd table in table’s format. 8. Write a program to display 5th to 10 tables up to 5 multiples. 9. Write a program to display day of week name based on what week no assigned to variable. 10. Write a program to print message as eligible for vote if person age is more than 18 years otherwise display message not eligible for vote. MY SOLUTIONS: --------------------------------------- --------------------------------------- ---EXERCISES ON T-SQL PROGRAMMING --Q1 DECLARE @VAR1_Q1 INT DECLARE @VAR2_Q1 INT SELEC...

Exercise 7: 1. Write a query to display American Airlines flight’s details. 2. Write a query to find which airline is starting from HYDERABAD. 3. Write a query to display Airline name in which contains more no of flights. 4. Write a query to display no of flights airlines wise. 5. Write a query to find which airline contains more than 5 flights. 6. Write a query to display British airways and Delta airlines flights details. 7. Write a query to display airlines details in which airline contains more than maximum no of flights in Delta Airlines. 8. Write a query to display flight details except Delta airlines. 9. Write a query to display total no of flights based on city from. 10. Write a query to display flights details reaching to New York City. MY SOLUTIONS: ---------------------------------------------- ---------------------------------------------- --SUBQUERY EXERCISE SELECT f.Airlineid       ,f.flightid   ,f.cityfrom   ,f.ci...

Exercise 6: 1. WAQ to display details of Customers with Orders. 2. WAQ to display details of Customers and their Orders made in 2006. 3. WAQ to display details of Sales personnel including their Customers. 4. WAQ to display details of Customers that made Orders in February. 5. WAQ to display details of Products including its Total no. of Orders. 6. WAQ to display details of Products including its Total Sales. 7. WAQ to display details of Customers that have bought keyboards. 8. WAQ to display details of Salespersons and their customers, including the total number of Orders by each Customer. 9. WAQ to display details of Orders sorted both by Products and by year. Solve and create the most efficient view. 10. WAQ to display names (first and last) of all Customers and their relevant Sales person information. MY SOLUTIONS: ----------------------------------------------- ----------------------------------------------- --JOINS EXERCISE SELECT c.customer_Id ...

Exercise 3: 1. WAQ to display Tool Designer designation employee’s details. 2. WAQ to display Design Engineer and Tool designation employee’s details. 3. WAQ to display female market specialist employee’s details. 4. WAQ to display single female employee’s details. 5. WAQ to display employees whose vocation hours between 40 and 50. 6. WAQ to display total number of hours away from work for each employee by adding the number of hours taken for vacation and the number of hours taken as sick leave. 7. WAQ to display employees whose job title contain engineer word. 8. WAQ to display employees whose vacation hours contain more than or equal to 40. 9. WAQ to display all employees except tool designer designation employees. 10.WAQ to display employee details whose vacation hours not in range 20 and 40. 11.WAQ to display employee details except tool designer and designer engineer. 12. WAQ to display employee details who joined from 2001-01-01 to 200-12-31. 13. WAQ to...