Showing posts with label MSSQL :: SQL Statement. Show all posts
Showing posts with label MSSQL :: SQL Statement. Show all posts

May 13, 2009

SQL Basic Syntax

Select Statement
It is used to retrieve a set of data from a database.
SELECT column_name
FROM table_name

Select Distinct Statement
It is used to retrieve a set of data from a database without contains duplicate values.
SELECT DISTINCT column_name
FROM table_name

Where Clause
It is used to extract only those records that fulfill a specified criterion. It could not be used with aggregate functions.
SELECT column_name
FROM table_name
WHERE expression

Order By Keyword
It is used to sort the result-set by a specified column in ascending(ASC) order or descending(DESC) order.
SELECT column_name
FROM table_name
WHERE expression
ORDER BY column_name ASC

Group By Clause
It is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE expression
GROUP BY column_name

Having Clause
It is used with aggregate functions.
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Example:
SELECT column_name1,SUM(column_name2)
FROM table_name
WHERE expression
GROUP BY column_name1
HAVING SUM(column_name2)< 2000

May 06, 2009

SQL Functions

COUNT() - returns the number of rows of the specified column.
SELECT COUNT(column_name)
FROM table_name
WHERE expression
SELECT COUNT(DISTINCT column_name)
FROM table_name
WHERE expression

AVG() - returns the average value of a numeric column.
SUM() - returns the sum of a specific column.
MAX() - returns the largest value of a specific column.
MIN() - returns the smallest value of a specific column.
FIRST() - returns the first value of a specific column.
LAST() - returns the last value of a specific column.
Example:
SELECT AVG(column_name)
FROM table_name
WHERE expression

MID()
- Extract characters from a text field.
- start_position: the default starting position is 1.
- length: the number of characters to return (optional)
SELECT MID(column_name, start_position, length)
FROM table_name

UCASE() - converts a value to upper case.
LCASE() - converts a value to lower case.
LEN() - returns the length of a text field.
Example:
SELECT UCASE(column_name)
FROM table_name

ROUND()
- Rounds a numeric field to the number of decimals specified.
- decimal: specifies the number of decimals to be returned.
SELECT ROUND(column_name, decimal)
FROM table_name

FORMAT()
- formats how a field is to be displayed.
- format: specifies the format.
SELECT FORMAT(column_name, format)
FROM table_name
Example:
SELECT FORMAT(column_name, 'YYYY-MM-DD') AS New_Date
FROM table_name

SQL Basic Syntax

Create Statement
It is used to create a new database.
CREATE DATABASE database_name
It is used to create a new table.
CREATE TABLE table_name (column_name1 data_type1 NULL|NOT NULL, column_name2 data_type2 NULL|NOT NULL)
It is used to create a new column in a table.
ALTER TABLE table_name
ADD column_name data_type NULL|NOT NULL

Drop Statement
It is used to delete an existing database.
DROP DATABASE database_name
It is used to delete an existing table.
DROP TABLE table_name
It is used to delete an existing column in a table.
ALTER TABLE table_name
DROP COLUMN column_name

Rename Statement
It is used to rename an existing table.
sp_rename existing_table_name, new_table_name
It is used to rename an existing column.
sp_rename existing_column_name, new_column_name, 'COLUMN'

Insert Statement
It is used to insert a new row of data to a table.
INSERT INTO table_name (column_name1, column_name2)
VALUES (value1, value2)
It allows to insert multiple rows into a table. You can retrieve the data from another table and insert it into a new table.
INSERT INTO table_name1 (column_name1, column_name2)
SELECT column_name3, column_name4 FROM table_name2

Update Statement
It is used to update an existing record in a table.
UPDATE table_name
SET column_name1 = value1, column_name2 = value2
WHERE expression

Delete Statement
It is used to delete rows in a table.
DELETE FROM table_name
WHERE expression