Exercise - Database Operations¶
Let’s create a table and perform database operations using direct SQL.
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). Make sure to enforce to store one of these 3 values as part of course_status.
course_published_dt - which holds date type value.
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 |
Update the status of all the draft courses related to Python and Scala to published along with the course_published_dt using system date.
Delete all the courses which are neither in draft mode nor published.
Get count of all published courses by author and make sure output is sorted in descending order by count.
Course Author |
Course Count |
|---|---|
Bob Dillon |
5 |
Elvis Presley |
2 |
Mike Jack |
1 |
Uncle Sam |
1 |