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)
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>;
For complete list of PostgreSQL column modifications, please check the documentation.
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.
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
andorders
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.
Last updated
Was this helpful?