Exercises – Database Operations

Let’s create a table and perform database operations using direct SQL.

Exercise 1 - Create Table

Create table - courses

  • course_id - sequence generated integer and primary key

  • course_name - which holds alpha numeric or string values up to 60 characters

  • course_author - which holds the name of the author up to 40 characters

  • course_status - which holds one of these values (published, draft, inactive).

  • course_published_dt - which holds date type value.

Provide the script as answer for this exercise.

Exercise 2 - Inserting Data

  • Insert data into courses using the data provided. Make sure id is system generated.

Course Name

Course Author

Course Status

Course Published Date

Programming using Python

Bob Dillon

published

2020-09-30

Data Engineering using Python

Bob Dillon

published

2020-07-15

Data Engineering using Scala

Elvis Presley

draft

Programming using Scala

Elvis Presley

published

2020-05-12

Programming using Java

Mike Jack

inactive

2020-08-10

Web Applications - Python Flask

Bob Dillon

inactive

2020-07-20

Web Applications - Java Spring

Mike Jack

draft

Pipeline Orchestration - Python

Bob Dillon

draft

Streaming Pipelines - Python

Bob Dillon

published

2020-10-05

Web Applications - Scala Play

Elvis Presley

inactive

2020-09-30

Web Applications - Python Django

Bob Dillon

published

2020-06-23

Server Automation - Ansible

Uncle Sam

published

2020-07-05

Provide the insert statement(s) as answer for this exercise.

Exercise 3 - Updating Data

Update the status of all the draft courses related to Python and Scala to published along with the course_published_dt using system date.

Provide the update statement as answer for this exercise.

Exercise 4 - Deleting Data

Delete all the courses which are neither in draft mode nor published.

Provide the delete statement as answer for this exercise.

Validation - Get count of all published courses by author and make sure output is sorted in descending order by count.

SELECT course_author, count(1) AS course_count
FROM courses
WHERE course_status= 'published'
GROUP BY course_author

Copy to clipboard

Course Author

Course Count

Bob Dillon

5

Elvis Presley

2

Uncle Sam

1