SQL Training: Exercises on T-SQL

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

SELECT @VAR1_Q1 = 2,@VAR2_Q1 = 3

PRINT @VAR1_Q1 * @VAR2_Q1
--Q2
DECLARE @VAR1_Q2 INT
DECLARE @VAR2_Q2 INT
DECLARE @VAR3_Q2 INT

SELECT @VAR1_Q2 = 4,@VAR2_Q2 = 3,@VAR3_Q2 = 5

IF(@VAR1_Q2 > @VAR2_Q2 AND @VAR1_Q2 > @VAR3_Q2)
PRINT 'The greatest number is:' + LTRIM(STR(@VAR1_Q2))
IF(@VAR2_Q2 > @VAR1_Q2 AND @VAR2_Q2 > @VAR3_Q2)
PRINT 'The greatest number is:' + LTRIM(STR(@VAR2_Q2))
IF(@VAR3_Q2 > @VAR1_Q2 AND @VAR3_Q2 > @VAR2_Q2)
PRINT 'The greatest number is:' + LTRIM(STR(@VAR3_Q2))

IF(@VAR1_Q2 < @VAR2_Q2 AND @VAR1_Q2 < @VAR3_Q2)
PRINT 'The least number is:' + LTRIM(STR(@VAR1_Q2))
IF(@VAR2_Q2 < @VAR1_Q2 AND @VAR2_Q2 < @VAR3_Q2)
PRINT 'The least number is:' + LTRIM(STR(@VAR2_Q2))
IF(@VAR3_Q2 < @VAR1_Q2 AND @VAR3_Q2 < @VAR2_Q2)
PRINT 'The least number is:' + LTRIM(STR(@VAR3_Q2))

IF(@VAR1_Q2 < @VAR2_Q2)
BEGIN
  IF(@VAR2_Q2 < @VAR3_Q2)
   PRINT 'The mid-value is:' + LTRIM(STR(@VAR2_Q2))
  ELSE IF(@VAR1_Q2 < @VAR3_Q2)
   PRINT 'The mid-value is:' + LTRIM(STR(@VAR3_Q2))
  ELSE
   PRINT 'The mid-value is:' + LTRIM(STR(@VAR1_Q2))
END
ELSE
BEGIN
  IF(@VAR1_Q2 > @VAR3_Q2)
   PRINT 'The mid-value is:' + LTRIM(STR(@VAR3_Q2))
  ELSE IF(@VAR2_Q2 > @VAR3_Q2)
   PRINT 'The mid-value is:' + LTRIM(STR(@VAR2_Q2))
     ELSE
   PRINT 'The mid-value is:' + LTRIM(STR(@VAR1_Q2))
END

--Q3

DECLARE @start AS INT=0,
        @end AS INT=10
  WHILE(@start<@end)
BEGIN
   SET @start=@start+1
   IF(@start=4 OR @start=6 OR @start=7)
     CONTINUE
    PRINT @start
END
--Q4
DECLARE @start_q4 AS INT=50,
        @end_q4 AS INT=40
  WHILE(@start_q4>=@end_q4)
BEGIN
    PRINT @start_q4
SET @start_q4=@start_q4 - 1
END
--Q5
DECLARE @start_q5 AS INT=1,
        @end_q5 AS INT=10
  WHILE(@start_q5<=@end_q5)
BEGIN
  IF(@start_q5 % 2 <> 0)
   BEGIN
    PRINT 5 * @start_q5
   END
SET @start_q5 = @start_q5 + 1
END
--Q6
DECLARE @start_q6 AS INT=1,
        @end_q6 AS INT=10
  WHILE(@start_q6<=@end_q6)
BEGIN
    PRINT 5 * @start_q6
    SET @start_q6 = @start_q6 + 1
END
--Q7
DECLARE @start_q7 AS INT=1,
        @end_q7 AS INT=10
  WHILE(@start_q7<=@end_q7)
BEGIN
    PRINT '2 * 10 =' + LTRIM(STR(2 * @start_q7))
    SET @start_q7 = @start_q7 + 1
END
--Q8
DECLARE @start_q8 AS INT=1,
        @end_q8 AS INT=5,
@numStart_q8 AS INT = 5,
        @numend_q8 AS INT = 10
WHILE(@numStart_q8 <= @numend_q8)
 BEGIN
  WHILE(@start_q8<=@end_q8)
BEGIN
    PRINT LTRIM(STR(@numStart_q8)) + '*' +  LTRIM(STR(@start_q8)) + '=' + LTRIM(STR(@numStart_q8 * @start_q8))
    SET @start_q8 = @start_q8 + 1
END
SET @start_q8 = 1
  SET @numStart_q8 = @numStart_q8 + 1
 END
--Q9
DECLARE @VAR1_Q9 INT

SELECT @VAR1_Q9 = 7

SELECT CASE @VAR1_Q9
           WHEN 1
      THEN 'Sunday'
   WHEN 2
      THEN 'Monday'
   WHEN 3
  THEN 'Tuesday'
   WHEN 4
  THEN 'Wednesday'
   WHEN 5
  THEN 'Thursday'
   WHEN 6
  THEN 'Friday'
ELSE
   'Saturday'
END AS 'DAYOFWEEK'

--Q10
DECLARE @VAR1_Q10 INT

SELECT @VAR1_Q10 = 9

IF(@VAR1_Q10 > 18)
PRINT 'Eligible to vote'
ELSE
PRINT 'Not Eligible to vote'



Comments

Post a Comment

Popular posts from this blog

SQL Training: Exercises on Stored Procedures and Functions

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...
Read more

SQL Training: Exercise on Clauses,String and Mathematical functions,Date Time and Aggregate Functions

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...
Read more

SQL Training:Exercises on Tables

Exercises on Tables Exercise 1 1. Create table books with columns Bookid (it should accept numbers between 1 to 2000) Bookname (it should accept Unicode variable length characters) Bookprice (it should accept currency values) 2. Insert this given values into books table BookId: 1, BookName: Reasoning, price: 500 BookId: 2, BookName: c programming, price: 600 BookId: 3, BookName: c++ programming, price: 400 BookId: 2, BookName: .net, price: 500 BookId: 3, BookName: Java, price: 400 BookId: 1, BookName: Sql server, price: 600 3. Create Table which contain Address information with columns Countryname (It should allow 20 Unicode characters only). Statename (It should allow 20 non Unicode characters only). Cityname (It should allow allows 20 non Unicode characters only). 4. Insert only Country name and state name into address table. 5. Insert this giving data at time into Address table Countryname: India, statename: AP, ctname: Hyd Countryname: India, statename: ...
Read more