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: Maharastra, ctname: Bombay
Countryname: India ,statename: Tamilnadu, ctname:chennai

6. Insert this values into address table in following order
Statename=AP, Cityname=Hyd, Countryname=India

7. Change city name in place of HYD with Hyderabad in address table.

8. Rename table name from address to address info.

9. Remove all AP state records from address info table.

10. Remove all records from add address info table.

11. Drop address info table from the database.

12. Delete java and .NET Books from books table.

13. Increase 50% price for SQL Server book in books table.

Exercise 2:

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 display design engineer married female details.

MY SOLUTIONS

 CREATE TABLE Books(
Bookid SMALLINT,
Bookname NVARCHAR(50),
Bookprice SMALLMONEY,
CONSTRAINT chk_Books CHECK(Bookid BETWEEN 0 AND 2000)
)
INSERT INTO Books(Bookid,Bookname,Bookprice)
VALUES(1,'Reasoning',500)
INSERT INTO Books(Bookid,Bookname,Bookprice)
VALUES(2,'c programming',600)
INSERT INTO Books(Bookid,Bookname,Bookprice)
VALUES(3,'c++ programming',400)
INSERT INTO Books(Bookid,Bookname,Bookprice)
VALUES(2,'.net',500)
INSERT INTO Books(Bookid,Bookname,Bookprice)
VALUES(3,'Java',400)
INSERT INTO Books(Bookid,Bookname,Bookprice)
VALUES(1,'Sql server',600)

 CREATE TABLE Address(
Countryname NVARCHAR(20),
Statename   VARCHAR(20),
  Cityname   VARCHAR(20)
)

--INSERT INTO Address(Statename,Cityname)
-- VALUES('','')

 INSERT INTO Address(Countryname,Statename,Cityname)
VALUES('India','AP','Hyd')
INSERT INTO Address(Countryname,Statename,Cityname)
VALUES('India','Maharastra','Bombay')
INSERT INTO Address(Countryname,Statename,Cityname)
VALUES('India','Tamilnadu','chennai')
 
INSERT INTO Address(Statename,Cityname,Countryname)
VALUES('AP','Hyd','India')
 
UPDATE Address
   SET Cityname='Hyderabad'
 WHERE Cityname='Hyd'

 EXEC sp_rename 'Address', 'AddressInfo'; 

  DELETE FROM AddressInfo
       WHERE Statename = 'AP'

 DELETE FROM AddressInfo;

 DROP TABLE AddressInfo

 DELETE FROM Books
      WHERE Bookname IN ('.net','Java')

 UPDATE Books
   SET Bookprice = Bookprice * 1.50
 WHERE Bookname = 'Sql server'

SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.JobTitle = 'Tool Designer'

 SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.JobTitle IN ('Tool Designer','Design Engineer')
ORDER BY e.JobTitle

 SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.JobTitle = 'Marketing Specialist'
  AND e.Gender = 'F'
SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.MaritalStatus = 'S'
  AND e.Gender = 'F'

 SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.VacationHours BETWEEN 40 AND 50
ORDER BY e.VacationHours
SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours,
      e.VacationHours + e.SickLeaveHours AS 'TotalHoursAway'
 FROM HumanResources.Employee AS e 
 SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours,
      e.VacationHours + e.SickLeaveHours AS 'TotalHoursAway'
 FROM HumanResources.Employee AS e 
WHERE e.JobTitle LIKE '%Engineer%'

 SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.VacationHours >= 40 
ORDER BY e.VacationHours

 SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.JobTitle NOT LIKE '%Tool Designer%'
SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.VacationHours NOT BETWEEN 20 AND 40
ORDER BY e.VacationHours
SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.JobTitle NOT IN ('Tool Designer','Design Engineer')
ORDER BY e.JobTitle

 SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.HireDate BETWEEN '2001-01-01' AND '2001-12-31'
ORDER BY e.HireDate
    

 SELECT e.BusinessEntityID,
               e.JobTitle,
   e.BirthDate,
   e.MaritalStatus,
         e.Gender,
e.HireDate,
   e.VacationHours,
   e.SickLeaveHours
 FROM HumanResources.Employee AS e 
WHERE e.MaritalStatus = 'M'
  AND e.Gender = 'F'
  AND  e.JobTitle = 'Design Engineer'


Comments

Post a Comment

Popular posts from this blog

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 Sub-Queries

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

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