Showing posts with label SQLSERVER. Show all posts
Showing posts with label SQLSERVER. Show all posts

sqlserver 2008 Date conversion


--Date--

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 108)

SELECT CONVERT(VARCHAR(26), GETDATE(), 109)

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]

SELECT CONVERT(VARCHAR(24), GETDATE(), 113)

SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]

SELECT CONVERT(VARCHAR(19), GETDATE(), 120)

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)

SELECT CONVERT(VARCHAR(23), GETDATE(), 126)

SELECT CONVERT(VARCHAR(26), GETDATE(), 130)

SELECT CONVERT(VARCHAR(25), GETDATE(), 131)

--Date

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]

SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]

SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]

SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]

SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]

SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]

SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]

SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]

SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]

SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]

SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]

SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]

SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]

SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]

SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]

--Date

SELECT DATEADD()

SELECT DATEADD(datepart, 5, GETDATE())

SELECT GETDATE()

SELECT DATEPART()

SELECT DATEDIFF()

SELECT DATENAME()

SELECT DAY()

SELECT MONTH()

SELECT YEAR()

SELECT ASCII()

SELECT CHAR() 

SELECT NCHAR()

SELECT DIFFERENCE()

SELECT LEFT()

SELECT RIGHT()

SELECT LTRIM()

SELECT RTRIM()

SELECT REPLACE()

SELECT QUOTENAME()

SELECT REVERSE()

SELECT CHARINDEX()

SELECT PATINDEX()

SELECT LEN()

SELECT STUFF

SELECT SUBSTRING

SELECT LOWER

SELECT UPPER

SELECT DATEADD(day, 10,'2000-01-05 00:05:00.000');

SELECT DATEDIFF(day, '2000-01-10','2000-01-05');

SELECT DATEPART(day, '2000-02-10');

SELECT SYSDATE();
No comments:

Select Only Date Part From DateTime

SELECT GETDATE()
2012-06-07 10:11:56.107


SELECT CONVERT(VARCHAR(10),GETDATE(),111)
No comments:

Date Format in sqlserver

DATE FORMATS
Format # Query (current date: 12/30/2006) Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30
TIME FORMATS
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840
No comments:

Date conversion in sqlserver

Extended Date Formats
Date FormatSQL StatementSample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
1999-01-24
MM/YYSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]		08/99
MM/YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]12/2005
YY/MMSELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]99/08
YYYY/MMSELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]2005/12
Month DD, YYYY 1SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]July 04, 2006 1
Mon YYYY 1SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]Apr 2006 1
Month YYYY 1SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1
DD Month 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]11 September 1
Month DD 1SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]September 11 1
DD Month YY 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]19 February 72 1
DD Month YYYY 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]11 September 2002 1
MM-YYSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]		12/92
MM-YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]05-2006
YY-MMSELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]		92/12
YYYY-MMSELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]2006-05
MMDDYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]122506
MMDDYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]12252006
DDMMYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]240702
DDMMYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]24072002
Mon-YY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]Sep-02 1
Mon-YYYY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]Sep-2002 1
DD-Mon-YY 1SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]25-Dec-05 1
DD-Mon-YYYY 1SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]25-Dec-2005 1
No comments:

SQL Server Date Formats

Standard Date Formats:

Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)		 Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +
milliseconds		 SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD - SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD - SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds)		 SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM

No comments:

SQL PART 4 G

GROUP BY column

HAVING SUM(column) condition value

This "Sales" Table:

Company Amount

W3Schools 5500

IBM 4500

W3Schools 7100

This SQL:

SELECT Company,SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount)>10000

Returns this result:

Company SUM(Amount)

W3Schools 12600

SQL The SELECT INTO Statement

The SELECT INTO Statement
The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.

Syntax

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source

Make a Backup Copy

The following example makes a backup copy of the "Persons" table:

SELECT * INTO Persons_backup FROM Persons

The IN clause can be used to copy tables into another database:

SELECT Persons.* INTO Persons IN 'Backup.mdb' FROM Persons

If you only want to copy a few fields, you can do so by listing them after the SELECT statement:

SELECT LastName,FirstName INTO Persons_backup FROM Persons

You can also add a where clause. The following example creates a "Persons_backup" table with two columns (FirstName and LastName) by extracting the persons who lives in "Sandnes" from the "Persons" table:

SELECT LastName,Firstname INTO Persons_sandnes FROM Persons WHERE City='Sandnes'

Selecting data from more than one table is also possible. The following example creates a new table "Empl_Ord_backup" that contains data from the two tables Employees and Orders:

SELECT Employees.Name,Orders.Product INTO Empl_Ord_backup FROM Employees

INNER JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID
No comments:

SQL PART 4 F



COUNT(DISTINCT column) Returns the number of distinct results


FIRST(column) Returns the value of the first record in the specified field


LAST(column) Returns the value of the last record in the specified field


MAX(column) Returns the highest value of a column


MIN(column) Returns the lowest value of a column


STDEV(column)


STDEVP(column)


SUM(column) Returns the total sum of a column


VAR(column)


VARP(column)


Scalar functions


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


Useful Scalar Functions in MS Access


Function Description


UCASE(c) Converts a field to upper case


LCASE(c) Converts a field to lower case


MID(c,start[,end]) Extract characters from a text field


LEN(c) Returns the length of a text field


INSTR(c) Returns the numeric position of a named character within a text field


LEFT(c,number_of_char) Return the left part of a text field requested


RIGHT(c,number_of_char) Return the right part of a text field requested


ROUND(c,decimals) Rounds a numeric field to the number of decimals specified


MOD(x,y) Returns the remainder of a division operation


NOW() Returns the current system date


FORMAT(c,format) Changes the way a field is displayed


DATEDIFF(d,date1,date2) Used to perform date calculations


SQL GROUP BY and HAVING


Aggregate functions (like SUM) often need an added GROUP BY functionality.


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


GROUP BY Example


This "Sales" Table:


Company Amount


W3Schools 5500


IBM 4500


W3Schools 7100


And This SQL:


SELECT Company, SUM(Amount) FROM Sales


Returns this result:


Company SUM(Amount)


W3Schools 17100


IBM 17100


W3Schools 17100


The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem:


SELECT Company,SUM(Amount) FROM Sales


GROUP BY Company


Returns this result:


Company SUM(Amount)


W3Schools 12600


IBM 4500


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 table


1 comment:

SQL PART 4 E



SQL Create Database, Table, and Index





Create a Database


To create a database:


CREATE DATABASE database_name



Create a Table
To create a table in a database:

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
)

Example

This example demonstrates how you can create a table named "Person", with four columns. The column names will be "LastName", "FirstName", "Address", and "Age":

CREATE TABLE Person
(
LastName varchar,

FirstName varchar,

Address varchar,
Age int
)
This example demonstrates how you can specify a maximum length for some columns:
CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)
The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:

Data Type Description

integer(size)

int(size)

smallint(size)

tinyint(size) Hold integers only. The maximum number of digits are specified in parenthesis.

decimal(size,d)

numeric(size,d) Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".

char(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.

varchar(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.

date(yyyymmdd) Holds a date

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.

A Unique Index
Creates a unique index on a table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name
ON table_name (column_name)
The "column_name" specifies the column you want indexed.

A Simple Index
Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.

CREATE INDEX index_name
ON table_name (column_name)
The "column_name" specifies the column you want indexed.
Example
This example creates a simple index, named "PersonIndex", on the LastName field of the Person table:
CREATE INDEX PersonIndex
ON Person (LastName)
If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:
CREATE INDEX PersonIndex
ON Person (LastName DESC)

If you want to index more than one column you can list the column names within the parentheses, separated by commas:

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)
SQL Drop Index, Table and Database
Drop Index

You can delete an existing index in a table with the DROP statement.

DROP INDEX table_name.index_name

Delete a Table or Database
To delete a table (the table structure, attributes, and indexes will also be deleted):

DROP TABLE table_name

To delete a database:
DROP DATABASE database_name

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

SQL ALTER TABLE

ALTER TABLE

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

ALTER TABLE table_name

ADD column_name datatypeALTER 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).

Person:

LastName FirstName Address

Pettersen Kari Storgt 20

Example

To add a column named "City" in the "Person" table:

ALTER TABLE Person ADD City varchar(30)

Result:

LastName FirstName Address City

Pettersen Kari Storgt 20

Example

To drop the "Address" column in the "Person" table:

ALTER TABLE Person DROP COLUMN Address

Result:

LastName FirstName City

Pettersen Kari

SQL Functions

SQL has a lot of built-in functions for counting and calculations.
Function Syntax
The 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

Scalar functions

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

Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!

"Persons" table (used in most examples)

Name Age

Hansen, Ola 34

Svendson, Tove 45

Pettersen, Kari 19

Aggregate functions in MS Access

Function Description

AVG(column) Returns the average value of a column

COUNT(column) Returns the number of rows (without a NULL value) of a column

COUNT(*) Returns the number of selected rows

FIRST(column) Returns the value of the first record in the specified field

LAST(column) Returns the value of the last record in the specified field

MAX(column) Returns the highest value of a column

MIN(column) Returns the lowest value of a column

STDEV(column)

STDEVP(column)

SUM(column) Returns the total sum of a column

VAR(column)

VARP(column)

Aggregate functions in SQL Server

Function Description

AVG(column) Returns the average value of a column

BINARY_CHECKSUM

CHECKSUM

CHECKSUM_AGG

COUNT(column) Returns the number of rows (without a NULL value) of a column

COUNT(*) Returns the number of selected rows
No comments:
Subscribe to: Posts (Atom)