Skip to main content

sql tutorial

SQL DROP Statement

The DROP statement is used to delete object in SQL, with the DROP statement we can remove index, tables and even databases easily.

DROP INDEX

The indexes of the table can be dropped by the using the following command.

SYNTAX for MS Access
DROP INDEX index_name ON table_name

SYNTAX for SQL SERVER
DROP INDEX table_name.index_name

SYNTAX for Oracle
DROP INDEX index_name

SYNTAX for MySQL
ALTER TABLE table_name DROP INDEX index_name

SQL MID() Function

The MID() function is used to extract values from a column. The MID() contain three (3) parameters. The first one is used to select which column to extract, the second one is the starting position, the last one is the number of characters to be extracted.

SQL MID() Syntax

SELECT MID(column_name,start[,length]) FROM table_name

Consider the following table for this exercise

Users

SQL AVG() Function

The AVG function calculates the average of a specified column. It works only on a numeric column. It first sums up all data and then divides it by the total number of rows.For example if the sum of 5 items is 10 then average is 10/5 = 2.

SQL AVG() Syntax

SELECT AVG(column_name) FROM Table

Consider the following table for this exercise

Users

SQL FIRST Function

The FIRST function returns the first value of the selected column. FIRST function is not standard SQL function so it cannot be used with other DBMS like MySQL. However this is very useful with MS Access OR SQL Server.

SQL FIRST() Syntax

SELECT FIRST(column_name) FROM Table

Consider the following table for this exercise

Users

SQL Min() Function

The MIN function is use to return the smallest value of the selected column. MIN can be use only in numeric column.

As with other function, you can also give an Alias to the MIN function.

SQL MIN() Syntax

SELECT Min(column_name) FROM Table

Consider the following table for this exercise

Users

SQL NOW() Function

The NOW() function is an aggregate function used to return the date and time along with other column.

The NOW() function returns the result as dd/mm/yyyy HH:MM:SS AM/PM depending on your regional settings.

SQL NOW() Syntax

SELECT column_name(s),NOW() FROM Table

Consider the following table for this exercise

Users

SQL LEN() Function

The LEN function is useful to count the number of characters in a column you specified as the parameter. LEN is short for length. The LEN function returns the number of characters including spaces or any character within that column.

You can use SQL Alias to name the result of the LEN function.

SQL LEN() Syntax

SELECT LEN(column_name) FROM Table

Consider the following table for this exercise.

Users