Database Essentials using Postgres - Exercise - Exercise - DML or CRUD Operations using Postgresql

Title: Comprehensive Guide on Database Operations

Description Paragraph:
In this article, you will be taken through the basics of Database Operations through SQL. By the end of this article, you will have a clear understanding of creating tables, inserting data, updating and deleting rows, and validating the outcomes.

Explanation for the video:
The embedded YouTube video provides visual aid to complement the text in the article. Watch the video to gain a better understanding of the concepts discussed in the article.

Click here to watch the video

Key Concepts Explanation

Description of the key concepts. Provide key concepts as subheadings and explain them with inline code examples. Multi-line code examples should be highlighted using code highlighter.

Key Concept 1

In this section, you will learn about creating a table in SQL with various column types.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(60),
    course_author VARCHAR(40),
    course_status VARCHAR(15),
    course_published_dt DATE

Key Concept 2

Description of the key concept 2 with inline code examples
Multi line code examples (if any) should be highlighted using code highlighter

Hands-On Tasks

Description of the hands-on tasks. Provide a list of tasks that the reader can perform to apply the concepts discussed in the article.

  1. Task 1: Create a table named ‘courses’ with the specified column names and types.
  2. Task 2: Insert sample data into the ‘courses’ table according to the provided data.


Summary of the main points discussed in the article. Encourage the reader to practice or engage with the community for further learning.

Exercises - Database Operations

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

Exercise 1 - Create Table

Create table - courses

Provide the CREATE TABLE script using the provided column names and data types.

Exercise 2 - Inserting Data

Provide the INSERT INTO statements for inserting data into the 'courses' table using the provided data.

Exercise 3 - Updating Data

Provide the UPDATE statement to change the status of specific courses along with the published date.

Exercise 4 - Deleting Data

Provide the DELETE statement to remove courses that are not in 'draft' or 'published' status.


SELECT course_author, COUNT(1) AS course_count
FROM courses
WHERE course_status = 'published'
GROUP BY course_author
ORDER BY course_count DESC;
Course Author Course Count
Bob Dillon 5
Elvis Presley 2
Uncle Sam 1

Watch the video tutorial here