SQL

The FORMAT() function is normally applied on formatting column with Date value like YYYY, MM, and DD.

SQL FORMAT() Syntax

SELECT FORMAT(column_name(s)) FROM Table

Consider the following table for this exercise

Users

The UNIQUE Contraints identifies a record in a database table uniquely. Unlike PRIMARY KEY contraints, you can have many UNIQUE contraint in a table.

UNIQUE Contraints Syntax - MySQL

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, UNIQUE (column_name1) .... )

UNIQUE Contraints Syntax - SQL Server / Oracle / MS Access

The UNION Operator is used to combine the result set of two or more SELECT Statement. This operator is useful if you have two table that share the same column name and data type and you want to combine it in a single Query.

Take note that both table must have the same column name and data type.

The DEFAULT Constraint is used to insert the default values into a column during design time.

The default values is used in new record if no value is specified during INSERT Statement.

The default

SQL DEFAULT Constraint Syntax

CREATE TABLE table_name ( column_name1 data_type DEFAULT Value, column_name2 data_type DEFAULT Value, column_name3 data_type DEFAULT Value, .... )

Example

COUNT is a simple aggregate function provided by SQL. As it is clear from its name that it simply counts the number of records in the table and returns the total count.

There are three (3) different usage for the Count function.

• SQL COUNT(column_name)
• COUNT(*)
• COUNT(DISTINCT column_name)

Consider the following table for this exercise

Employess

The data type determines the kind of values that users can store in the field. Like every programming languages, the SQL also has specific data type to store data. Following are some data type and there descriptions in which SQL allows us to store data.

Microsoft Access Data Types

Source: Microsoft Access Help

The AUTO INCREMENT generates a new number that increment by 1 from the previous number. This will allow a unique number to be associated in a row when it is inserted into a table.

You can set the AUTO INCREMENT during creation of the table or you can also use ALTER TABLE Statement if you have already created the table.

The following example will create a table named "Users" and add a Primary Key with Auto Increment in ID field.

Syntax for SQL Server

The ALTER TABLE statement is used to change the structure of an existing table like adding, deleting, or modifying of column.

SQL ALTER TABLE Syntax

To add a new column to the existing table use the following syntax:
ALTER TABLE table_name ADD column_name datatype

To remove a column from a table use the following syntax:
ALTER TABLE table_name DROP TABLE column_name

After you create a database using the CREATE Database statement, you can now create a table. Table is where you store your data in your database.

SQL CREATE TABLE Syntax

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... )

Example # 1

CREATE TABLE Users ( Firstname text, Lastname text, Salary float, DeptID int )

Users

The HAVING Clause is always used after the GROUP BY clause, it can not come without the GROUP BY clause. It works the same as the WHERE Clause, it is therefore also used to apply conditions on the aggregate_functions which was impossible by using simple WHERE clause.

The HAVING clause simply contains the conditions to filter data just like in WHERE clause.

SQL HAVING Syntax

SELECT column_name(s), aggregate_function(column_name) FROM Table WHERE some_condition GROUP BY grouping_condtion HAVING aggregate_function(column_name) operator value