Creating and Modifying Tables/Views

In this chapter we will be covering some of the most commonly used queries to create and modify tables in SQL.

Examples below are created via PostgreSQL.

1. Creating Tables

1.1. Basic Format (with no input data)

CREATE TABLE /*IF NOT EXISTS*/ table_name (
	column1 DataType TableConstraint DEFAULT default_value,
	column2 DataType,
	column3 DataType);

-- Example:
CREATE TABLE IF NOT EXISTS employees (
	emp_id serial PRIMARY KEY,
	lastname varchar(100),
	firstname varchar(100),
	department varchar(100) NOT NULL,
	start_date timestamp
);

-- ' IF NOT EXISTS ', ' PRIMARY KEY ', ' NOT NULL ' are optional

For all data types, please check PostgreSQL Documentation

1.2. Using Another Table (with input data)

CREATE TABLE new_table_name AS
SELECT column1, column2,…
FROM exiting_table
WHERE …;

-- Example:
CREATE TABLE employees_short AS
SELECT emp_id, lastname, department 
FROM employees;

1.3. Using SELECT Statement (with input data)

i. From a Different Table

SELECT emp_id, lastname, department 
INTO employees_short_v2 
FROM employees

ii. Manual

SELECT numbers, alphabet 
FROM (VALUES 
	  (1,'a'),
	  (2,'b'),
	  (3,'c'),
	  (4,'d'),
	  (5,'e'),
	  (6,'f'),
	  (7,'g'),
	  (8,'h'),
	  (9,'i'),
	  (10,'j')) s (numbers,alphabet)

Prior to creating a table using a SELECT statement, first create the new table with desired datatypes and constraints, and then transfer the data into newly created table:

Step 1: Create a new table with desired columns, and the datatypes and constraints for those columns.

CREATE TABLE employees_short (
 emp_id serial PRIMARY KEY, 
 emp_name text );

Step 2: Copy data from the old table to the new one.

INSERT INTO employees_short(emp_name) 
SELECT emp_name FROM employees;

2. Data Modifications

2.1. Inserting Rows (Populating Table)

INSERT INTO <tablename> VALUES (val1, val2, …)

/*
When we have incomplete data and the table incorporates columns with
default values, we can insert rows using only the available data by 
explicitly specifying those columns.
*/
INSERT INTO <tablename> (col1, col2, ..) VALUES (val1, val2, …)

2.2. Updating Rows

UPDATE <tablename> SET col1 = val1 ,  col2 = val2, …
WHERE condition;

2.3. Deleting Rows

DELETE FROM <tablename> 
WHERE condition; 

-- NOTE: without WHERE clause all records will be deleted

3. Table Modifications (Most Commonly Used)

3.1. Adding columns

ALTER TABLE <tablename> 
ADD COLUMN <column_name1> DataType OptionalConstraint DEFAULT default_value1,
ADD COLUMN <column_name2> DataType OptionalConstraint DEFAULT default_value2;

3.2. Renaming the table

ALTER TABLE <tablename> 
RENAME TO <new_tablename>;

3.3. Dropping tables

DROP TABLE IF EXISTS <tablename>;
-- Note: add CASCADE at the end, if the table is associated with other table(s) ;

Different databases offer different approaches to modify their tables. Therefore, it is advisable to refer to your database documentation before proceeding with any changes. Some of the most commonly used database systems: Postgres, MySQL, SQLite, and Microsoft SQL Server

4. Column Modifications (Most Commonly Used)

4.1. Renaming columns

ALTER TABLE <table_name>
RENAME COLUMN <old_column_name> TO <new_column_name>;

4.2. Changing columns' datatype

ALTER TABLE <table_name>
ALTER COLUMN <column_name> TYPE <new_data_type>;

4.3. Dropping columns

ALTER TABLE <table_name> 
DROP COLUMN <column_name>;

Column Modifications Summary

ALTER TABLE <table_name>

• ADD COLUMN <column_name> DataType DEFAULT '......';

• RENAME COLUMN <column_name> TO <new_column_name>;

• ALTER COLUMN <column_name> TYPE <new_column_type>;

• DROP COLUMN <column_name>;

For complete list of PostgreSQL column modifications, please check the documentation.

If you need to see the datatype for existing tables in Postgres, please check the Displaying Existing Tables' Columns and Their DataTypes in the Data Types section.

5. Creating Views

A view is a stored query that acts as a virtual table. When you query a view, PostgreSQL dynamically executes the underlying query and presents the results as if they were stored in a table. For more information about views please visit What is a View? section under Database Fundamentals.

5.1. Creating View(s)

CREATE VIEW <view_name> AS 
SELECT <column_names> FROM <table_name>;

-- Once the view is created, we perform queries the same way
SELECT * 
FROM <view_name>;

5.2. Deleting View(s)

DROP VIEW [IF EXISTS] view_name [, ...] [CASCADE | RESTRICT];
-- IF EXISTS: Optional clause to prevent an error if the view does not exist.
-- view_name: The name of the view you want to delete.
-- CASCADE | RESTRICT: Optional clauses to specify the behavior if there are dependent objects. 
    -- CASCADE deletes dependent objects as well 
    -- RESTRICT raises an error if there are dependent objects

-- Deleting single view
DROP VIEW IF EXISTS view1

-- Deleting multiple views
DROP VIEW IF EXISTS view1, view2, view3;

5.3. View Real World Example

Scenario:

Suppose we have two tables: orders and customers. we want to create a view that displays each customer’s name and the total amount they have spent on all their orders.

Tables Setup

-- create tables
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
);

-- Insert sample data
INSERT INTO customers (customer_name) VALUES
('John Doe'),
('Jane Smith'),
('Alice Johnson');

INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2023-01-10', 100.50),
(1, '2023-02-05', 200.75),
(2, '2023-03-01', 150.00),
(3, '2023-04-10', 250.30),
(2, '2023-05-22', 50.25);

Creating the View:

Now, let's create a view called customer_total_spending that shows each customer's name and the total amount they've spent on their orders.

CREATE VIEW customer_total_spending AS
SELECT 
    c.customer_name,
    SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o USING (customer_id)
GROUP BY c.customer_name;

Querying the View:

Now you can query the view just like a table:

SELECT * FROM customer_total_spending;

Result:

The query on the view will return the following:

diffCopy code customer_name  | total_spent 
----------------+-------------
 John Doe       |      301.25
 Jane Smith     |      200.25
 Alice Johnson  |      250.30

Benefits of the View:

• Simplification: The view simplifies querying by pre-joining the customers and orders tables and performing the aggregation.

• Security: If you want to give a user access to only the summarized data (e.g., total spending) without exposing the full details of each order, you can grant access to the view but not the underlying tables.