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
Course Author |
Course Count |
|---|---|
Bob Dillon |
5 |
Elvis Presley |
2 |
Uncle Sam |
1 |