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.';
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.';
