Table

Create

Create New

CREATE TABLE IF NOT EXIST table_name(
        column1 datatype,
        column2 datatype,
        column3 datatype,
        .... )
  • The column parameter specify the name of the columns for the new table
  • The datatype specifies the type of data the column can hold
  • Keep in mind, just running a SQL query doesn’t actually create a table for the data we extract. It just stores it in our local memory. 
  • To save it, we’ll need to download it as a spreadsheet or save the result into a new table. 
  • If we know the table doesn’t exist we can omit the IF NOT EXIST part
  • Below PersonID will hold an integer and the rest will hold characters with max length of 255 chars.
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Here is what we just created:

PersonID LastName FirstName Address City
         

Create from Another

  • A copy of an existing table can also be created using CREATE TABLE AS
  • The new table gets the same column definitions.
  • All columns or specific columns can be selected.
  • If you create a new table using an existing table, the new table will be filled with the existing values from the old table.
  • Syntax:
CREATE TABLE new_table_name AS
SELECT column1, column2,...    
FROM existing_table_name     
WHERE ....;}
  • Let’s create a table called NewTable from ExistingTable
CREATE TABLE    NewTable AS 
SELECT          column1, column4, column89 
FROM            ExistingTable;}`

Next we’ll insert values into our table

Insert

Insert Into New

Examples are in How To Insert

certain columns

  • If you are adding values to specific columns in a table
  • Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)

omit columns

NOTE:

  • If you insert into 3 columns and the table contains more than 3
  • If those additional columns are not to contain an auto-increment fields, then NULL will be inserted into the columns that were not listed in the INSERT INTO statement
  • Here is an example
# -- Here we only wish to insert into 3 columns - exlucing CustomerID which is an auto-increment field
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
  • As you see in the output below, the columns omitted from the INSERT INTO statement contain null
CustomerID CustomerName ContactName Address City PostalCode Country
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90
 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91
 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal null null Stavanger null Norway

all columns

  • If your adding values to all the columns in the table you do not need to specify the column names
  • Make sure the order of the values is in the same order as the columns
  • Syntax:
INSERT INTO table_name
VALUES (*value1*, *value2*, *value3*, ...)

auto-increment

  • Most tables will have an auto-increment field, for IDs or other values, so there is no need to insert into that specific column
  • Once you insert a record for the other columns the auto-increment column will self-generate

multiple rows

  • Just as we did above for one row you can just list all the rows one after the other like this:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

Insert Into Select

SQL does not include a function for importing data. Instead, a method you can use to import data from one table to another is to use the INSERT INTO command together with a SELECT statement.

  • Here we just want to copy columns from one table to another, since SQL does not provide a copy function we use
  • INSERT INTO SELECT just as we did before but this time we specify the table we want to copy from in WHERE
  • INSERT INTO SELECT copies data from one table and inserts it into another table
  • INSERT INTO SELECT requires that the data types in source and target tables match
  • The existing records in the target table are not affected
  • Syntax:
INSERT INTO     [destination_table_name]
SELECT          [column names, separated by commas, or * for all columns]
FROM            [source_table_name]
WHERE           [condition]

Example:

  • Here we are copying all the columns from customers
  • Into customer_promotion
  • We happen to have a filtering WHERE statement as well
INSERT INTO     customer_promotion
SELECT          *
FROM            customers
WHERE           total_sales = 0 AND postal_code = '12345'

certain columns

  • The statement above can be used to copy * all certain columns into another table
  • Here we’ll show how to copy certain columns
  • Syntax:
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition

Update

Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) should be updated.

If you omit the WHERE clause, all records in the table will be updated!

  • Syntax:
UPDATE  table_name
SET     column1 = value1,
        column2 = value2, ...
WHERE   condition;

Table Customers

CustomerID CustomerName ContactName Address City PostalCode Country
1
 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

single record

  • Update the first customer with a new contact person AND new city
UPDATE  Customers
SET     ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE   CustomerID = 1;
Output:
CustomerID CustomerName ContactName Address City PostalCode Country
1
 Alfreds Futterkiste Alfred Schmidt Obere Str. 57 Frankfurt 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

multiple records

  • Update multiple records: set all ContactName in “Mexico” to “Juan”
UPDATE  Customers
SET     ContactName = 'Juan'
WHERE   Country = 'Mexico';

multiple conditions

  • Of course WHERE can have multiple conditions to meet before an UPDATE takes place
  • Here we want to set the number of doors to ‘four’
  • When 3 conditions are true OR when another set of conditions is true
UPDATE  CarsInfo
SET     num_of_doors = 'four'
WHERE
        (make = 'dodge' AND fuel_type = 'gas' AND body_style = 'sedan')
        OR
        (make = 'mazda' AND fuel_type = 'diesel' AND body_style = 'sedan');

Drop

Syntax: DROP TABLE TableName;

The DROP TABLE statement is used to drop an ENTIRE table in a database. Dropping/deleting a table is not reversible.

Truncate

Syntax: TRUNCATE TABLE TableName;

The TRUNCATE TABLE statement is used to delete the data INSIDE a table, but not the table itself.

TRUNCATE TABLE   table_name;

Alter

The ALTER TABLE statement is used to

  • add, delete, or modify columns in an existing table
  • add and drop various constraints on an existing table

Some like: SQL/Oracle/MS Access might have different commands depending on their version such as

  • MODIFY COLUMN instead of RENAME COLUMN
  • EXEC sp_rename oldTableName, newTableName, column_name

Add

By default: all the entries are initially assigned the value NULL. Then you can use UPDATE statements to add necessary column values if you didn’t do it at the time you added the columns

Syntax:

ALTER TABLE     TableName
ADD             column_name datatype;

# ___ VARIATION ___
ALTER TABLE     TableName
ADD   COLUMN    column_name datatype;

Example:

ALTER TABLE   TableName
ADD           date_of_birth  date;

Drop Column

NOT to be confused with TRUNCATE. Drop will completely erase the column, contents and all.

Syntax:

ALTER TABLE     TableName
DROP            column_name;

Rename Column

Syntax:

ALTER TABLE     TableName
RENAME COLUMN   oldcolumn_name  to newcolumn_name;

Constraints

  • SQL constraints are used to specify rules for data in a table.
  • Constraints can be specified when the table is created with the CREATE TABLE statement, or
  • after the table is created with the ALTER TABLE statement.

Syntax

ALTER TABLE  TableName (
                column1 datatype constraint,
                column2 datatype constraint,
                column3 datatype constraint,
                 ....
             );

Here is a list of some constraints:

  • NOT NULL - Ensures that a column cannot have a NULL value
  • UNIQUE - Ensures that all values in a column are different
  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
  • FOREIGN KEY - Prevents actions that would destroy links between tables
  • CHECK - Ensures that the values in a column satisfies a specific condition
  • DEFAULT - Sets a default value for a column if no value is specified
  • CREATE INDEX - Used to create and retrieve data from the database very quickly

Modify Column

Modify datatype

ALTER TABLE     TableName
MODIFY          column_name datatype;

# ___ EXAMPLE ___
MODIFY         telephone_number  CHAR(20);

Temp Table

Create

  • Temporary tables are automatically deleted when the session is terminated
  • We can create a temp table by simply using SELECT followed by INTO
  • Instead of CREATE followed by INTO, all we do here is subset the column(s) we want directly INTO a temp table
  • Similar to using AS in a statement, SELECT followed by INTO will assign it to a TempTable that we name on the fly
SELECT     cutomerID, customer_name, orderID
INTO       TempTable
FROM       .....

Of course now you can use it as any other table, for example you can view it

SELECT   TOP 50 *
FROM     TempTable