T SQL NOTES

Primary Key, Foreign Key, Unique, Not Null
INF - each set of column must have a unique value, There is a primary key
2NF - for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence
3NF - there should not be the case that a non-prime attribute is determined by another non-prime attribute,
4NFevery non-prime attribute of table must be dependent on primary key

CREATE  TABLE <table name> {<columnname> <type> <NOT NULL|UNIQUE> ... Constraint <constraint name> <PRIMARY KEY | FOREIGN KEY>(<column name>) REFERENCES <TABLE NAME (COLUMN NAME)> }
varchar(max), int, bigint, money, float [ (precision) ], date, datetime, time [ (fractional second) ], text, image, binary, varbinary(max)
PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL
INDEX

ALTER TABLE <table name>
ADD <column> or ALTER <column> or DROP <column>

DROP TABLE <table name>

INSERT INTO <table name> (<column names>) VALUES (<column values>)

UPDATE <table name>
SET <modification> //modifies column value
WHERE <condition>

DELETE FROM <table name> //deletes row
WHERE <condition>

BEGIN  TRAN  <transaction name>
<INSERT/UPDATE/DELETE>
COMMIT  TRAN  <transaction name> or ROLLBACK  TRAN  <transaction name>

SELECT select_list [AS]
[ INTO new_table_name ] (specifies that the result set is used to create a new table)
FROM table_list
[ WHERE search_conditions ]
[ GROUP BY group_by_list ] (partitions the result set into groups based on the values)
[ HAVING search_conditions ] (an additional filter that is applied to the result set)
[ ORDER BY order_list [ ASC | DESC ] ]

OPERTORS USED
DISTINCT, SUM, AVG, MAX, MIN, COUNT

Conditional Operators FOR WHERE:
= <> > < >= <=
IN , NOT IN (test for several values)
BETWEEN, NOT BETWEEN (intervals)
IS NULL, IS NOT NULL
LIKE, NOT LIKE ( % or _ )
EXISTS, NOT EXISTS (sub queries)
AND, OR

JOINS:
SELECT column_name(s)
FROM  table_name1 INNER JOIN table_name2
ON    table_name1.column_name = table_name2.column_name

table_name1 INNER JOIN table_name2 - excludes data that does NOT satisfy the join
table1 LEFT OUTER JOIN table2 - all rows from table 1 will be included
table1 RIGHT OUTER JOIN table2 - all rows from table 2 will be included
table1 FULL OUTER JOIN table2 - all rows from each table will be included
table_name1 CROSS table_name2Each - row in one table is paired to every row in the other table

QUERY [UNION | INTERSECT | EXCEPT] QUERY


ROW FUNNCTIONS:

SELECT <ROW FUNCTION>

MATH:
ABS, DEGREES, RAND, ACOS
EXP, ROUND, ASIN, LOG, SIN
ATN2, LOG10, SQRT, CEILING
FLOOR, SIGN, ATAN,PI
SQUARE, COS, POWER
TAN, COT, RADIANS

STRING:
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX
SPACE, CHARINDEX, DIFFERENCE, REPLACE
STUFF, LEFT, REPLICATE, SUBSTRING
QUOTENAME, STR,LEN, REVERSE
UNICODE, LOWER, RIGHT
UPPER, LTRIM, RTRIM

DATE/TIME:
DATEADD, DATEDIFF
DATENAME, DATEPART
DAY, MONTH, YEAR
GETDATE, GETUTCDATE

DATA TYPE CASTING:
CAST( 'abc'  AS varchar(5) )
SUBSTRING(Name, 1, 30)  AS  ProductName
CONVERT(int, ListPrice) LIKE '3%';

CASE:
SELECT  title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive‘
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

SUB QUERIES:
QUERY [IN | NOI IN] (QUERY)

VIEWS:
CREATE VIEW <VIEW NAME> AS (QUERY)

PROCEDURE:
CREATE PROCEDURE <PROCEDURE NAME>
AS
(QUERY)
EXECUTE <PROCEDURE NAME>

FUNCTIONS:
CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS BEGIN
declare @Return varchar(30)
  select @return = case @Country
when 'Argentina' then 'South America‘
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America‘
else 'Unknown'
End
return @return
end

TRIGGERS:
CREATE TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
DECLARE @Newname VARCHAR(100)
SELECT @Newname =(SELECT Name FROM INSERTED)
PRINT 'THE STUDENT ' + @Newname + ' IS ADDED.';