Exercises – Database Operations

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.

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