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&controls=1&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
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
);
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
In [9]:
%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');
Out[10]:
In [11]:
%%sql
SELECT * FROM courses
Out[11]:
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
In [9]:
%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%')
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
In [9]:
%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';
Out[13]:
In [14]:
%%sql
SELECT course_author, count(1) AS course_count
FROM courses
WHERE course_status= 'published'
GROUP BY course_author
Out[14]: