SQL Basics

• There are three ways to insert data:
  1. Insert a complete row
  2. Insert a partial row
  3. Insert the result of a query

• Always specify the column names when inserting a row into a table
• Ensure that the primary key is unique
• When inserting a complete row, supply a value for every column

• The INSERT below adds one row to Customers

INSERT INTO Customers ( cust_id,
                        cust_name,
                        cust_address,
                        cust_city,
                        cust_state,
                        cust_zip,
                        cust_country,
                        cust_contact,
                        cust_email
                        )
VALUES ( 1000000006,
         'Toy Land',
         '123 Any Street',
         'New York',
         'NY',
         '11111',
         'USA',
         NULL,
         NULL );

• Specifying column names in INSERT lets you insert only part of a row
• A partial row can only be inserted if the table definition allows it
• A column can be omitted from INSERT if it allows NULL or has a default value

INSERT INTO Customers ( cust_id,
                        cust_name,
                        cust_country
                        )
VALUES ( 1000000007,
         'ToysRUs',
         'USA'
       );

• Besides inserting one row, you can insert many rows at once
• Combine INSERT with SELECT to insert multiple rows
• INSERT SELECT uses the result of a query as the input for INSERT
• The SELECT column names do not need to match those in the destination table
• Values are inserted according to the column positions in SELECT
• Make sure the SELECT does not return unique key values that already exist in the destination table

INSERT INTO Customers ( cust_id,
                        cust_name,
                        cust_address
                        )
SELECT vend_id,
       vend_name,
       vend_address
FROM   Vendors
WHERE  vend_id = 'DLL01';

• A table can be populated without using INSERT
• CREATE SELECT can create a copy of a table
• CREATE SELECT is not supported by every DBMS, including DB2

-- Oracle / MySQL 
CREATE TABLE ordersCopy
     AS (SELECT * FROM Orders);	

-- SQL Server 
SELECT * INTO ordersCopy FROM Orders;	

• These SELECT statements can also use WHERE, GROUP BY and joins
• The result can be copied into only one table, even if its data comes from several tables

65. Add yourself to Vendors

66. Create a backup of Customers named 'custCopy'