Creating and Modifying Tables

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 ' is 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)

Table schemas for the tables created other than the 1st method (Basic Format) should be adjusted upon creation. Therefore, to create a table using a SELECT statement, first create the table with desired datatypes and constraints, and then transfer the data into newly created table: -- Step 1: Create a new table with a serial column.

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 you have incomplete data and the table incorporates columns with
default values, you 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. Instead of storing data directly, views store queries that retrieve data from one or more underlying tables in the database. When you query a view, PostgreSQL dynamically executes the underlying query and presents the results as if they were stored in a table.

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);