Notes for learing SQL(2)

9. Create Index

Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.

Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.

CREATE INDEX index_nameON table_name (column_name)

10. Truncate a Table

What if we only want to get rid of the data inside a table, and not the table itself? Use the TRUNCATE TABLE command (deletes only the data inside the table):

TRUNCATE TABLE table_name

11. ALTER TABLE

The ALTER TABLE statement is used to add or drop columns in an existing table.

ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name

Note: Some database systems don't allow the dropping of a column in a database table (DROP COLUMN column_name).

12. SQL Functions

SQL has a lot of built-in functions for counting and calculations.
Function SyntaxThe syntax for built-in SQL functions is:

SELECT function(column) FROM table

Types of Functions

There are several basic types and categories of functions in SQL. The basic types of functions are:

Aggregate Functions - Aggregate functions operate against a collection of values, but return a single value.

Scalar functions - Scalar functions operate against a single value, and return a single value based on the input value.

13. SQL GROUP BY and HAVING

GROUP BY...GROUP BY... was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values.

The syntax for the GROUP BY function is:

SELECT column,SUM(column) FROM table GROUP BY column

HAVING...HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions.

The syntax for the HAVING function is:

SELECT column,SUM(column) FROM tableGROUP BY columnHAVING SUM(column) condition value