Part 1 Part 2

UPDATE

The UPDATE statement is used to modify the existing records in a table. Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

What is a database table?

DELETE

The DELETE statement is used to delete the existing records in a table. Be careful when updating records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

What is a database table?

TOP    LIMIT    ROWNUM

The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records … because without it, returning a large number of records would impact on performance.

What is a database table? What is a database table?

AS

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.

What is a database table?

  • Aliases can be useful when:
    1. There are more than one table involved in a query.
    2. Functions are used in the query.
    3. Column names are big or not very readable.
    4. Two or more columns are combined together.

Alias for Columns

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.

What is a database table?

Example 1

  • Create two aliases:

    • One for the CustomerID column.
    • One for the CustomerName column.
    SELECT CustomerID AS ID, CustomerName AS Customer
    FROM Customers;
    

Example 2

  • Create two aliases:

    • One for the CustomerName column.
    • One for the ContactName column.
    SELECT CustomerName AS Customer, ContactName AS [Contact Person]
    FROM Customers;
    

Example 3

  • Create an alias named “Address” that combines four columns (Address, PostalCode, City and Country):

    SELECT CustomerName, Address, PostalCode, City, Country AS Address
    FROM Customers;
    

Example 4

Select all the orders from the Customers table with CustomerID=4 (Around the Horn). We use the “Customers” and “Orders” tables, and give them the table aliases of “c” and “o” respectively.

What is a database table?

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c,
Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

MIN()    MAX()

The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.

What is a database table?

COUNT()    AVG()    SUM()

The COUNT() function returns the number of rows that match a specified criteria. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column.

What is a database table?