SQL-syntax

CREATING A DATABASE

CREATE DATABASECREATE DATABASE database_nameCREATE DATABASECREATE DATABASE database_name
[ON { [PRIMARY](NAME = logical_name,FILENAME ='physical_name'[,SIZE = size][,MAXSIZE = max_size UNLIMITED][,FILEGROWTH = growth_increment])}[,...n]][LOG ON{(NAME = logical _name,FILENAME = 'physical_name'[,SIZE=size UNLIMITED][,MAXSIZE = max_size UNLIMITED][,FILEGROWTH = growth_increment])}[,...n]][FOR LOAD FOR ATTACH]

MODIFYING DATABASE

MODIFYING DATABASE ALTER DATABASE database
{ ADD FILE [,...n] [TO FILEGROUP filegroup_name] ADD LOG FILE [,...n] REMOVE FILE logical_name ADD FILEGROUP filegroup_name REMOVE FILEGROUP filegroup_name MODIFY FILE MODIFY FILEGROUP filegroup_name, filegroup_property}(NAME = logical_name,FILENAME ='physical_name'[,SIZE = size][,MAXSIZE = max_size UNLIMITED][,FILEGROWTH = growth_increment])

CREATING TABLES

CREATE TABLE table_name
(
column_name datatype [Null / Not Null]
[IDENTITY (SEED, INCREMENT)
[DEFAULT constraint_expression]
)

Constraints:Constraints:

Primary Key Constraint
Foreign Key Constraint
Unique Key Constraint
Check Constraint
Default Constraint

MODIFYING A TABLE

ALTER TABLE table_name
[WITH CHECK WITH NO CHECK]
ADD column_name data_type * Adding a Column
[DROP COLUMN column_name] * Dropping a Column

CREATING TABLES WITH CONSTRAINTS

CREATE TABLE table_name
(
column_name data_type CONSTRAINT constraint_name constraint_type [,…N]
)

ALTER TABLE table_name
*Adding constraint
ADD CONSTRAINT constraint_name constraint_type(column_name)
*Dropping constraint
Drop CONSTRAINT constraint_name
DROPPING A TABLE
DROP TABLE table_name [,…n]


SELECT STATEMENT

SELECT [ ALL DISTINCT ][ TOP n [PERCENT] [ WITH TIES] ]

[ INTO new_table ]
[ FROM ]
[ WHERE ]
[ GROUP BY [ALL] group_by_expression [,...n]
[ WITH { CUBE ROLLUP } ]]
[ HAVING ]
[ ORDER BY { column_name [ ASC DESC ] } [,...n] ]
[ COMPUTE { { AVG COUNT MAX MIN SUM }(expression)}[,.n]
[ BY expression [,...n] ]
[ FOR BROWSE ] [ OPTION ( [,...n) ] QUERYING AND REPORTING
Selecting Columns
SELECT column_name1[,column_name2]
FROM table_name

Selecting All Columns
SELECT * FROM table_name

Manipulating Column Names
SELECT column_name ‘column_alias’
[, column_name ‘column_alias’]
FROM table_name
Or
SELECT column_alias = column_name
[, column_alias = column_name]
FROM table_name
STORED PROCEDURE

Creating Stored Procedures
CREATE PROC[EDURE] procedure_name [ ; number ] [ @parameter data_type [,@parameter data_type] [ = default [ OUTPUT ] ][ WITH { RECOMPILE ENCRYPTION
RECOMPILE , ENCRYPTION } ] AS sql_statement [ ...n ]



Modifying a Stored Procedures
ALTER PROC[EDURE] procedure_name [ ; number ] [ @parameter data_type [,@parameter data_type] [ = default [ OUTPUT ] ][ WITH { RECOMPILE ENCRYPTION
RECOMPILE , ENCRYPTION } ] AS sql_statement [ ...n ]



Viewing a Stored Procedure
Sp_help – listed the various procedures created in a DB
Sp_helptext – displays the definition of the procedure
Deleting a Stored Procedure
DROP PROCEDURE procedure_name [,..n]TRIGGERS

Creating Triggers
CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT DELETE UPDATE }
[WITH ENCRYPTION]
AS
sql_statements




Modifying Triggers
ALTER TRIGGER trigger_name
ON table_name
FOR {INSERT DELETE UPDATE }
[WITH ENCRYPTION]
AS
sql_statements



Viewing a Triggers
Sp_help – listed the various triggers created in a DB
Sp_helptext – displays the definition of the triggers
Deleting a Triggers
DROP TRIGGER trigger_name [,..n] FUNCTIONS

Creating a Function
CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH <> [ [,] ...n] ]
[ AS ]
BEGIN function_body RETURN scalar_expressionEND

Altering a Function
ALTER FUNCTION [ owner_name. ] function_name ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH <> [,...n] ]
[ AS ]
BEGIN function_body RETURN scalar_expressionEND

Drop Function
DROP FUNCTION { [ owner_name . ] function_name } [ ,...n ]
VIEWS

Creating Views
CREATE VIEW owner.view_name [(column [,n])]
[WITH {ENCRYPTION
SCHEMABINDING
VIEW_METADATA} [,N]]
AS
select_statement
[WITH CHECK OPTION] Altering Views
ALTER VIEW owner.view_name [(column [,n])]
[WITH {ENCRYPTION
SCHEMABINDING
VIEW_METADATA} [,N]]
AS
select_statement
[WITH CHECK OPTION]

Renaming a View
Sp_rename old_view_name new_view_name
Prints the text of rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view
Sp_helptext object_name
Getting Information on a View
Sysobjects or information_schema.tables : View names
Sysdepends or information_schema.view_table_usage : Base object names
Syscomments or information_schema.views : View definition
Syscolumns or information_schema.view_column_usage : columns that are defined in a view
MANIPULATION DATA

INSERTING ROWS

INSERT [INTO] {table_name} [(column_list)]
VALUES {DEFAULT VALUES values_list Select_statement

DELETING ROWS

DELETE [FROM] {table_name}
[WHERE condition]
UPDATING ROWS

UPDATE {table_name}
SET column_name = value
FROM table_name
WHERE condition

Truncating a Table

TRUNCATE TABLE table_name INDEXES
Create an Index
CREATE [UNIQUE][CLUSTERED NONCLUSTERED] INDEX index_name
ON table_name (column_name[, column_name]…)