SQL Zone is brought to you in partnership with:

Sandeep is a ITA at Tata Consultancy Services. He also is the author of java blog. Sandeep has posted 24 posts at DZone. You can read more from them at their website. View Full User Profile

Database SQL Excercises

05.07.2011
| 5304 views |
  • submit to reddit

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

Published at DZone with permission of its author, Sandeep Bhandari.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Alessandro Santini replied on Sat, 2011/05/07 - 2:14am

I would consider most of these drills like something that should already have been said and done in college years and more interview questions than SQL exercises for juniors. By the way - what has this to do with Java?

Carlos Hoces replied on Sat, 2011/05/07 - 6:25am

Many Java developers do not know anything about SQL, and I am not sure they "should" know it.

Let me put an exercise to you:

1.- There are several Java developers in a team, and they know nothing of SQL commands.

2.- They must have programmatic access to a MySQL engine resources.

3.- You must provide them with plain Java classes and methods for SQL accessing, via JDBC, hidding to them all SQL specific syntax.

4.- You cannot use any external framework, just your own classes and methods.

I believe this to be a much more intereresting exercise for a Java section, don't you? :)

Sandeep Bhandari replied on Sat, 2011/05/07 - 2:03pm

@Alessandro

You are right in saying that it is for beginners to SQL. Why would you go for learning SQL when you are a master.

And yes I have already faced it. SQL knowledge is very much required as a Java Developer in an organization or as a freelancer.

Extreme Java

 

Lieven Doclo replied on Sun, 2011/05/08 - 8:48am

Carlos, ever read the Pragmatic Programmer? Every decent programmer should be a generalizing specialist. I wouldn't want a developer on my team that doesn't know anything about SQL. Example: a developers knows Hibernate, but doesn't know SQL (why would he, Hibernate does the programmatic access to the underlying database resource). I give him a week before he starts running into things like cartesian products of table joins and other nice things that generally bring down a system when deployed in production, all caused by dangerous SQL. So if someone says to a developers "you don't need to know that", he'd better answer "yes I do, I just don't need to be a specialist on that matter"

Andrew Spencer replied on Tue, 2011/05/10 - 3:49am

Yup - see also The Law of Leaky Abstractions (and bearing in mind that ORM tools are particularly leaky)

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.