DZone
Java Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Java Zone > Database SQL Excercises

Database SQL Excercises

Sandeep Bhandari user avatar by
Sandeep Bhandari
·
May. 07, 11 · Java Zone · Interview
Like (0)
Save
Tweet
6.98K Views

Join the DZone community and get the full member experience.

Join For Free

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

Database sql

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Top Soft Skills to Identify a Great Software Engineer
  • Ultra-Fast Microservices: When Microstream Meets Wildfly
  • Synchronization Methods for Many-To-Many Associations
  • OpenTelemetry in Action: Identifying Database Dependencies

Comments

Java Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo