CRUD Operations

• CRUD OPERATION

  • CREATE
    • Databases, Tables or Views
    • Users, prermissions and security groups
  • READ
    • Example: SELECT statements
  • UPDATE
    • Amend existing database records
  • DELETE

• CREATE

  • CREATE TABLE unique table name
  • (column name, data type, size)

        CREATE TABLE test_table(
            test_date date,
            test_name, varchar(20),
            test_int int
        )
  

  A few considerations when creating a table

  • Table and column names
  • Type of data each column will store
  • Size or amount of data stored in the column

• INSERT

  Syntax:

        INSERT INTO table_name(col1, col2, col3)
        VALUES
            ('value1', 'value2', value3)
  

• INSERT SELECT

  Syntax:

    INSERT INTO table_name (col1, col2, col3)
    SELECT
        column1, 
        column2, 
        column3
    FROM other_table
    WHERE
        -- conditions apply
  

  Note:

  • Don’t Use SELECT *
  • Be specific in case table structure changes

• UPDATE

  Syntax:

    UPDATE table
    SET column = value,
    WHERE 
        -- Condition(s);
  

  Don’t forget the WHERE clause!

    UPDATE table
    SET
        column1 = value1,
        column2 = value2
    WHERE
        -- Coondition(s);
  

• DELETE

  Syntax:

    DELETE 
    FROM table
    WHERE
        -- Conditions
  

• TRUNCATE

  It clears the entire table at once

    TRUNCATE TABLE table_name
  

• Declaring Variables

  We declare variables to avoid repition

        DECLARE @test_int INT
        DECLARE @my_artist VARCHAR(100)
  

• Using SET to set valuies to variables

        SET @test_int = 5
        SET @my_artist = 'AC/CD"
  

  Using variables to select columns from artist table where name is set to the value stored in @my_artist

        SELECT *
        FROM artist
        WHERE name = @my_artist;
  

• Temporary Tables

        SELECT
            col1, 
            col2, 
            col3 INTO #my_temp_table
        FROM my_existing_table
        WHERE
            -- Conditions
  

  #my_temp_table exists until connection or session ends

    -- Remove table manually
    DROP TABLE #my_temp_table