Database SQL Excercises

Every developer should have basic knowledge of database and here are some of the exercises that I recently wrote for beginners.

One can use them to have a hands on experience of the SQL language.
Solutions will be posted later.

If someone can help me with posting/sending the solutions then it will be great.

1) Create a user with and grant the CONNECT, SESSION and CREATE privileges to him/her.

2) Login using the newly created user and create a table SALARY_STRUCTURE with columns as “EmpId, Basic, LTA, Medical, Special, Food_Coupons”.
          a. The emp id cannot be floating number and should be unique
          b. All salary components can be floating point numbers
          c. LTA Not greater than Rs1000
          d. Medical Not greater than Rs1250
          e. Food_Coupons Not greater than Rs1000

3) Insert random data for 20 employees into the table.

4) Write a SQL query to fetch the total Salary of all the employees.

5) Write a SQL query to list the employee id’s of those employees which have Medical > 500 and Food_Coupons < 300

6) Write a SQL query which displays the HRA component of the salary of each employee. HRA is calculated as 12% of Basic.

7) Write a SQL query to fetch the employee details whose total of all the components is greatest.

8) Write a SQL query which lists only the number of employees (without details) who have Basic greater than sum of LTA and Food_Coupons

9) Alter the table to remove the Special component and add a new component Variable which cannot be greater than Rs10000.

10) Write a query to display the All rows and columns of the table but the columns should have alias as the first character of the column name like B,L,M,V,F

11) Write a SQL query to display all the users which have average of all the salary components equal to any of the salary component. 6,11,20,8,7,14 has average of 11 and is one of the component.

12) Create another t able as EMPLOYEE_DETAILS with columns as “EmpId, Name, Age, Address, PAN No.”
        a. The emp id cannot be floating number and should be unique and should be       consistent  with SALARY_STRUCTURE table (Foreign Key constraint)
         b. Age must be numeric
         c. PAN No can be alpha numeric

13) Add the details of the employees. How will you make sure that there is no employee which has a salary record but no record in EMPLOYEE_DETAILS table?

14) Modify the query created in step 7 to also display the corresponding details from the EMPLOYEE_DETAILS table.

15) Create a SAVEPOINT at this stage

16) DELETE the rows of 5 employees from the EMPLOYEE_DETAILS table who have the lowest salary (using the cascade option). How is DELETE different from TRUNC.

17) DROP the table SALARY_STRUCTURE. You will need to delete the foreign key constraint before doing this.

18) DROP the user from database. You may need ADMIN access for this.

19) Now rollback to the SAVEPOINT created earlier.

20) Commit the changes made in the database so far.

From http://extreme-java.blogspot.com/2011/04/database-sql-exercises.html