Exercises – Database Operations

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.

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