Solutions – DML or CRUD Operations using Postgresql

DML or CRUD Operations using Postgresql

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

In [1]:
%%HTML
<iframe width="560" height="315" src="https://www.youtube.com/embed/pqtKUUI5cCo?rel=0&amp;controls=1&amp;showinfo=0" frameborder="0" allowfullscreen></iframe>

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.

Solution – Create Table

In [3]:
%load_ext sql
In [4]:
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
In [7]:
%%sql

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(60) NOT NULL,
    course_author VARCHAR(40) NOT NULL,
    course_status VARCHAR(9),
    course_published_dt DATE
);
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
Out[7]:
[]

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.

Solution – Inserting Data

In [8]:
%load_ext sql
The sql extension is already loaded. To reload it, use:
  %reload_ext sql
In [9]:
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
In [10]:
%%sql

INSERT INTO courses 
    (course_name, course_author, course_status, course_published_dt) 
VALUES 
    ('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', NULL),
    ('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', NULL),
    ('Pipeline Orchestration - Python', 'Bob Dillon', 'draft', NULL),
    ('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');
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
12 rows affected.
Out[10]:
[]
In [11]:
%%sql

SELECT * FROM courses
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
12 rows affected.
Out[11]:
course_id course_name course_author course_status course_published_dt
1 Programming using Python Bob Dillon published 2020-09-30
2 Data Engineering using Python Bob Dillon published 2020-07-15
3 Data Engineering using Scala Elvis Presley draft None
4 Programming using Scala Elvis Presley published 2020-05-12
5 Programming using Java Mike Jack inactive 2020-08-10
6 Web Applications – Python Flask Bob Dillon inactive 2020-07-20
7 Web Applications – Java Spring Mike Jack draft None
8 Pipeline Orchestration – Python Bob Dillon draft None
9 Streaming Pipelines – Python Bob Dillon published 2020-10-05
10 Web Applications – Scala Play Elvis Presley inactive 2020-09-30
11 Web Applications – Python Django Bob Dillon published 2020-06-23
12 Server Automation – Ansible Uncle Sam published 2020-07-05

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.

Solution – Updating Data

In [8]:
%load_ext sql
The sql extension is already loaded. To reload it, use:
  %reload_ext sql
In [9]:
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
In [12]:
%%sql

UPDATE courses 
SET course_status = 'published', 
    course_published_dt = current_date 
WHERE course_status='draft' AND 
    (course_name LIKE '%Python%' OR course_name LIKE '%Scala%')
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
2 rows affected.
Out[12]:
[]

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
In [8]:
%load_ext sql
The sql extension is already loaded. To reload it, use:
  %reload_ext sql
In [9]:
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
In [13]:
%%sql

DELETE FROM courses 
WHERE course_status !='draft' AND course_status !='published';
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.
Out[13]:
[]
In [14]:
%%sql

SELECT course_author, count(1) AS course_count
FROM courses
WHERE course_status= 'published'
GROUP BY course_author
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.
Out[14]:
course_author course_count
Bob Dillon 5
Elvis Presley 2
Uncle Sam 1

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.