# Creating and Modifying Tables/Views Examples below are created via [PostgreSQL](https://www.postgresql.org/). ## 1. Creating Tables ### 1.1. Basic Format (with no input data) ```sql CREATE TABLE /*IF NOT EXISTS*/ table_name ( column1 DataType TableConstraint DEFAULT default_value, column2 DataType, column3 DataType); ``` ```sql -- 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](https://www.postgresql.org/docs/current/datatype.html) ### 1.2. Using Another Table (with input data) ```sql CREATE TABLE new_table_name AS SELECT column1, column2,… FROM exiting_table WHERE …; ``` ```sql -- 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 ```sql SELECT emp_id, lastname, department INTO employees_short_v2 FROM employees ``` #### ii. Manual ```sql 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) ``` {% hint style="info" %} 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. ```sql CREATE TABLE employees_short ( emp_id serial PRIMARY KEY, emp_name text ); ``` Step 2: Copy data from the old table to the new one. ```sql INSERT INTO employees_short(emp_name) SELECT emp_name FROM employees; ``` {% endhint %} ## 2. Data Modifications ### 2.1. Inserting Rows (Populating Table) ```sql INSERT INTO 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 (col1, col2, ..) VALUES (val1, val2, …) ``` ### 2.2. Updating Rows ```sql UPDATE SET col1 = val1 , col2 = val2, … WHERE condition; ``` ### 2.3. Deleting Rows ```sql DELETE FROM WHERE condition; -- NOTE: without WHERE clause all records will be deleted ``` ## 3. Table Modifications (Most Commonly Used) ### 3.1. Adding columns ```sql ALTER TABLE ADD COLUMN DataType OptionalConstraint DEFAULT default_value1, ADD COLUMN DataType OptionalConstraint DEFAULT default_value2; ``` ### 3.2. Renaming the table ```sql ALTER TABLE RENAME TO ; ``` ### 3.3. Dropping tables ```sql DROP TABLE IF EXISTS ; -- 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](https://www.postgresql.org/docs/9.4/sql-altertable.html), [MySQL](https://dev.mysql.com/doc/refman/8.0/en/alter-table.html), [SQLite](https://www.sqlite.org/lang_altertable.html), and [Microsoft SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16\&redirectedfrom=MSDN) ## 4. Column Modifications (Most Commonly Used) ### 4.1. Renaming columns ```sql ALTER TABLE RENAME COLUMN TO ; ``` ### 4.2. Changing columns' datatype ```sql ALTER TABLE ALTER COLUMN TYPE ; ``` ### 4.3. Dropping columns ```sql ALTER TABLE DROP COLUMN ; ```
Column Modifications Summary ALTER TABLE \ * ADD COLUMN \ DataType DEFAULT '......'; * RENAME COLUMN \ TO \; * ALTER COLUMN \ TYPE \; * DROP COLUMN \;
For complete list of PostgreSQL column modifications, please check the [documentation](https://www.postgresql.org/docs/9.4/ddl-alter.html). {% hint style="info" %} 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](/ds-hub/sql/sql-basics/data-types.md) section. {% endhint %} ## 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?](/ds-hub/database/database-fundamentals.md#what-is-a-view) section under [Database Fundamentals](/ds-hub/database/database-fundamentals.md). ### 5.1. Creating View(s) ```sql CREATE VIEW AS SELECT FROM ; -- Once the view is created, we perform queries the same way SELECT * FROM ; ``` ### 5.2. Deleting View(s) ```sql 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 ```sql -- 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. ```sql 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: ```sql SELECT * FROM customer_total_spending; ``` **Result:** The query on the view will return the following: ```diff 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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://dshub.gitbook.io/ds-hub/sql/sql-basics/creating-and-modifying-tables-views.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.