Software Testing & Quality Assurance Slides - Week 6
Date Published: 05/11/2019
These slides will explain some more SQL operators and statements to Insert a new record, Update and Delete the existing records. We will also learn how to filter values using In and Between operators along Top operator to get the desired number of top records from the database table.

Agenda
  • Basic Database Operators:
    • Update
    • Insert
    • In
    • Top
    • Between

      UPDATE Statement
      • The UPDATE statement is used to update records in a table.
      • Syntax

      UPDATE table_name

      SET column1=value1,column2=value2,...

      WHERE some_column=some_value;

      UPDATE EMPLOYEE SET FirstName = ‘John’ where Id = 5

        INSERT INTO Statement
        • The INSERT INTO statement is used to insert new records in a table.
        • Syntax

        INSERT INTO table_name (column1,column2,column3,...)

        VALUES (value1,value2,value3,...);

        INSERT INTO EMPLOYEE (Id, FirstName, LastName,...)

        VALUES(1, ‘Johnson’, ‘Mike’,….)

          INSERT INTO Statement Contd...
          • INSERT INTO statement can be written without specifying column names.
          • Syntax

          INSERT INTO table_name

          VALUES(value1, value2, value3,…)

            DELETE Statement
            • The DELETE statement is used to delete records in a table.
            • Syntax

            DELETE FROM table_name

            WHERE some_column=some_value;

            Delete from Employee where Id = 1

            AND and OR operators can also be used in the where clause 

              TOP Operator
              • In some situations, you only need TOP some records, no matter how many records are returned by the query.
              • Syntax

              Select TOP [number of records] [column names] from [table name]

              Select TOP 1 * from EMPLOYEE

                IN Operator
                • The IN operator allows you to specify multiple values in a WHERE clause
                • Syntax

                SELECT column_name(s)

                FROM table_name

                WHERE column_name IN (value1,value2,...)

                SELECT * from Emloyee where State IN (‘VA’, ‘NY’)

                  BETWEEN Operator
                  • The BETWEEN operator is used to select values within a range.
                  • The values can be numbers, text, or dates
                  • Syntax

                  SELECT column_name(s)

                  FROM table_name

                  WHERE column_name BETWEEN value1 AND value2;

                  SELECT * from INVENTORY WHERE PRICE BETWEEN 10 AND 20

                    SQL Aliases
                    • SQL aliases are used to temporarily rename a table or a column heading.
                    • Basically, aliases are created to make column names more readable.
                    • Syntax

                    SELECT column_name AS alias_name

                    FROM table_name;

                    SELECT PRICE AS ITEM_PRICE FROM INVENTORY


                      Keywords: sql tutorial, sql insert, sql update, sql delete, sql top, sql in, sql between, sql tutorial for beginners