Lecture 6: PostgreSQL part 3

Lecture 6: PostgreSQL part 3#

Introduction#

SQL (Structured Query Language) is a powerful language used for managing and manipulating relational databases. It consists of five main components:

  1. DDL (Data Definition Language):

    • Used for defining and modifying database structures.

    • Examples: CREATE, ALTER, DROP.

  2. DQL (Data Query Language):

    • Used for querying data from the database.

    • Example: SELECT.

  3. DML (Data Manipulation Language):

    • Used for manipulating data within the database.

    • Examples: INSERT, UPDATE, DELETE.

  4. DCL (Data Control Language):

    • Used for controlling access to data within the database.

    • Examples: GRANT, REVOKE.

  5. TCL (Transaction Control Language):

    • Used for managing transactions within the database.

    • Examples: COMMIT, ROLLBACK.

Today, we will focus on DML (Data Manipulation Language), specifically on how to insert, modify, and delete rows in SQL.

Learning Objectives#

  • Understand how to insert rows into a table using various methods, including by column position, column name, multiple rows at once, and from another table.

  • Learn how to safely modify existing rows using the UPDATE statement, including best practices to avoid accidental modifications.

  • Gain knowledge on how to delete rows using the DELETE statement, truncate a table using the TRUNCATE statement, and return data from INSERT, UPDATE, and DELETE operations using the RETURNING clause.

Connect to database#

Make sure you edit the credentials.json file with appropriate username and password as in worksheet1

# load the ipython-sql extension
%load_ext sql

import json
import urllib.parse

with open('data/credentials.json') as f:
    login = json.load(f)
    
username = login['user']
password = urllib.parse.quote(login['password'])
host = login['host']
port = login['port']

Let’s connect to your dvdrental database

%sql postgresql://{username}:{password}@{host}:{port}/dvdrental

%%sql
DROP TABLE IF EXISTS employees;

Sample Table#

Let’s create a sample table named employees:

%%sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

%%sql
SELECT * FROM employees;

Inserting Rows#

Insert by Column Position#

To insert a row by specifying values in the order of columns:

%%sql
INSERT INTO employees VALUES (1, 'John', 'Doe', 30, 'Engineering');

  • Explanation:

    • The values are assigned to columns from left to right.

    • The order of values should be the same as the order of columns in the table.

    • It’s not mandatory to provide a value for every column in the table, unless they are explicitly set as non-nullable.

To see the changes:

%%sql
SELECT * FROM employees;

Insert by Column Name#

To insert a row by specifying column names and corresponding values:

%%sql
INSERT INTO employees (first_name, last_name, age, department) 
VALUES ('Jane', 'Smith', 25, 'Marketing');

  • Explanation:

    • This method allows you to specify only the columns you want to insert values into.

    • The order of values should match the order of the specified column names.

    • This is useful when you don’t want to insert values for all columns, especially if some columns have default values or are nullable.

To see the changes:

%%sql
SELECT * FROM employees;

Let’s also try to insert a row with only first_name and last_name. The rest of the columns will be filled by default

%%sql
INSERT INTO employees (first_name, last_name) 
VALUES ('Quan', 'Nguyen');

%%sql
SELECT * FROM employees;

Insert Multiple Rows at Once#

To insert multiple rows in a single statement:

%%sql
INSERT INTO employees (first_name, last_name, age, department)
VALUES 
    ('Alice', 'Johnson', 28, 'Sales'),
    ('Bob', 'Brown', 35, 'HR'),
    ('Charlie', 'Davis', 40, 'Finance');

  • Explanation:

    • This method allows you to insert multiple rows in a single INSERT statement.

    • Each set of values should match the order of the specified column names.

    • This is more efficient than inserting rows one by one.

To see the changes:

%%sql
SELECT * FROM employees;

Insert from Another Table#

To insert rows from another table:

Creating another_table#

%%sql
CREATE TABLE another_table (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

Insert Sample Data into another_table#

%%sql
INSERT INTO another_table (first_name, last_name, age, department)
VALUES 
    ('Emily', 'Clark', 29, 'Engineering'),
    ('Michael', 'Scott', 45, 'Engineering'),
    ('Pam', 'Beesly', 30, 'Sales');

%%sql
SELECT * FROM another_table;

Insert from another_table to employees#

%%sql
INSERT INTO employees (first_name, last_name, age, department)
SELECT first_name, last_name, age, department
FROM another_table
WHERE department = 'Engineering';

  • Explanation:

    • This method allows you to insert rows into a table by selecting data from another table.

    • The SELECT statement should return the same number of columns and compatible data types as the INSERT statement.

    • This is useful for copying data from one table to another based on certain conditions.

To see the changes:

%%sql
SELECT * FROM employees;

Modifying Rows#

To update existing rows in a table:

%%sql
UPDATE employees
SET age = 31
WHERE first_name = 'John' AND last_name = 'Doe';

  • Explanation:

    • The UPDATE statement modifies existing rows in the table.

    • The SET clause specifies the columns to be updated and their new values.

    • The WHERE clause specifies which rows should be updated. Without a WHERE clause, all rows in the table will be updated.

To see the changes:

%%sql
SELECT * FROM employees;

Warning

Why UPDATE Can Be Dangerous

The UPDATE statement is used to modify existing rows in a table. However, it can be dangerous because:

  • Accidental Modifications: If the WHERE clause is incorrect or missing, you might accidentally update all rows in the table.

  • Unintended Changes: A wrong search condition can lead to unintended modifications, affecting more rows than intended.

Best Practices for Using UPDATE

  • Use a SELECT Statement First:

  • Before running an UPDATE statement, use a SELECT statement with the same WHERE clause to ensure that the rows returned are the ones you intend to update.

Create a Temporary Table:

Another method is to create a temporary table to test your UPDATE statement. This allows you to verify the changes without affecting the actual data.

-- Example: Create a temporary table
CREATE TEMP TABLE temp_employees AS
SELECT * FROM employees;

-- Test the UPDATE statement on the temporary table
UPDATE temp_employees
SET age = age + 1
WHERE department = 'HR';

-- Verify the changes
SELECT * FROM temp_employees WHERE department = 'HR';

Deleting Rows#

To delete rows from a table:

%%sql
DELETE FROM employees
WHERE department = 'HR';

  • Explanation:

    • The DELETE statement removes rows from the table.

    • The WHERE clause specifies which rows should be deleted. Without a WHERE clause, all rows in the table will be deleted.

    • Use the DELETE statement with caution, especially without a WHERE clause, to avoid accidentally removing all data from the table.

To see the changes:

%%sql
SELECT * FROM employees;

Truncating a Table#

To remove all rows from a table quickly:

%%sql
TRUNCATE TABLE another_table;

  • Explanation:

    • The TRUNCATE statement removes all rows from a table, similar to the DELETE statement without a WHERE clause.

    • It is faster than DELETE because it does not generate individual row delete operations.

    • It resets any auto-increment counters on the table.

To see the changes:

%%sql
SELECT * FROM another_table;

Returning Data from Insert, Update, Delete#

The table modifying commands INSERT, UPDATE, and DELETE all accept a RETURNING clause which returns the rows that have been modified by these commands. The returning clause can be helpful in reliably identifying the rows that have been modified, without having to run a separate SELECT statement after table modification.

%%sql
INSERT INTO employees (first_name, last_name, age, department)
VALUES ('David', 'Miller', 32, 'IT')
RETURNING *;

To see the changes:

%%sql
SELECT * FROM employees;

  • Explanation:

    • The RETURNING clause allows you to return values from the rows that were modified by the INSERT, UPDATE, or DELETE statement.

    • This is useful for retrieving the values of auto-increment columns or other computed values.

%%sql
UPDATE employees
SET age = 33
WHERE first_name = 'David' AND last_name = 'Miller'
RETURNING *;

To see the changes:

%%sql
SELECT * FROM employees;

%%sql
DELETE FROM employees
WHERE first_name = 'David' AND last_name = 'Miller'
RETURNING *;

To see the changes:

%%sql
SELECT * FROM employees;
Contents