DZone
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
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
11 Monitoring and Observability Tools for 2023
Learn more
  1. DZone
  2. Data Engineering
  3. Databases
  4. Database SQL Excercises

Database SQL Excercises

Sandeep Bhandari user avatar by
Sandeep Bhandari
·
May. 07, 11 · Interview
Like (0)
Save
Tweet
Share
7.22K 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

  • What’s New in the Latest Version of Angular V15?
  • How To Best Use Java Records as DTOs in Spring Boot 3
  • Shift-Left: A Developer's Pipe(line) Dream?
  • Building a RESTful API With AWS Lambda and Express

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • 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: