SQL Cheat Sheet

Digged out by DBSY ( Database System ) notes from Temasek Polytechnic. I realised that I need to refer to this once in a while. Good to post this out for easy reference. Will update this when I see a need to. If you have feedback on this, please feel free to contact me using the contact me form or email me at [email protected]

SELECT
[ALL | DISTINCT | DISTINCTROW ]
column name, …
[FROM table name]
[WHERE where_condition]
[GROUP BY { column name | expr | position}]
[HAVING where_condition]
[ORDER BY { column name | expr | position}]
[ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

INSERT INTO [table name]
[( [column name]{, [column name]}) ]
VALUES
([constant value] , {[constant value]} ){,([constant
value]{[constant value]})} [select statement])

DELETE FROM
[table name]
[WHERE [selection condition]]

UPDATE [tablename]
SET <column name>=<value expression> {, <column name>=<value expression>}
[WHERE <selection condition>]

Subquery Syntax

SELECT [DISTINCT] select_list
FROM table_list
WHERE
{expression {[NOT] IN | comp_op {ANY|ALL|SOME]} | [NOT] EXISTS}
(SELECT DISTINCT
subquery_select_list
FROM table_list WHERE conditions)
[GROUP BY group_by_list [HAVING conditions]]
[ORDER BY order_by_list]]

C# SQL Connection Code

public void ConnectToDatabase()
{

//string connectionString = “”;
//string connectionString = ConfigurationManager.ConnectionStrings[“wcfConn”].ToString();

if (conn.State != ConnectionState.Open)
{
conn.ConnectionString = connectionString;
conn.Open();
comm.Connection = conn;
}
comm.Parameters.Clear();
}

If you do it from code behind, it will look something like this,

data source={INSERT IP / localhost here };initial catalog={insert database name here};User ID={insert user id here};Password={Insert Password here}

If you insert from web.config, it will look something like this,

<connectionStrings>
<add name="wcfConn"
connectionString="data source={Insert IP/localhost};initial catalog={Insert database name};User ID={Insert username here};Password={Insert Password here};"
providerName="System.Data.SqlClient" />
</connectionStrings>