Data Base

It is a numeric data type, it stores interger values from

-2⁶³ to 2⁶³ -1

SSAS, SSRS, SSIS,SSMS

SQL Server Analysis Services

SS Reporting Services

SS Integration Service

SS Management Services

Returns a string that is the result of concatenating two or more string values.

CONCAT ( string_value1, string_value2 [, string_valueN ] )

CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions. For more information about data type conversions, see CAST and CONVERT (Transact-SQL).

Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions

DIFFERENCE ( character_expression , character_expression )

Returns a value formatted with the specified format and optional culture in SQL Server 2012. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

FORMAT ( value, format [, culture ] )

FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid.

FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

It is the SQL command used to retrieve data from a database.

3 things you need to do for a proper SELECT query

1. The colums you need to retrieve
2. The table you want to retrieve them from
3. The conditions (if any) that the data must satisfy

It allows you to retrieve records that satisfy a range condition

i.e. list of people whose bday is between certain dates

ex. WHERE birthdate BETWEEN 'xxxx' and 'xxxx'

It prevents certain data that you dont want to see.

ex. WHERE NOT last_name = 'Jones'

ex WHERE last_name <> 'Jones'

When you have a list of values you'd like to search for

ex. WHERE last_name IN ('Jones', 'Smith', 'Thomas')

ex Where last_name NOT IN ('Jones', 'Smith', 'Thomas')

_ Any single character

% Any series of zero or more characters

[a-f] any singer character in the range a-f

[^a-f] any single character not in the range a-f

[abc] Any single character contained in the last (a,b,c)

[^abc] any single character not contained in the list(a,b,c)

You use this in the WHERE statement as a condition

ex. IS NULL and IS NOT NULL

It is a way to sort your data

ex. ORDER BY last_name

you can use ASC (ascending) or DESC (Descending)

This allows you to answer more complicated questions about datasets.

ex How to come up with number of records that meet a certain condition

AVG returns the average of the values in a group

Count Returns a count of the number of items in a group

MAX returns the largest value in a group

MIN Returns the smallest value

SUM Returns the sum of all values in the group

STDEV Returns the stat std dev of all values in the group

VAR Returns the stat variance of all values in the grp

Examples of using aggregate functions

COUNT

SELECT COUNT (*)

FROM Students

SELECT COUNT(DISTINCTIVE(last_name))

FROM students

SELECT DISTINCT(last_name)

FROM students

Examples of using aggregate functions

Using minimum, maximum, average, sum values and count

SELECT min(abs), max (abs), avg(abs)

FROM students

SELECT sum(abs)

FROM students

WHERE gender = 'male'

The GROUP BY statement is used in conjunction with the aggregate functions (like SUM and COUNT) to group the result-set by one or more columns.

SELECT gender, avg(abs)

FROM students

GROUP BY gender

SELECT gender AS 'Gender'

, min(abs) AS 'Lowest ABS

, max(abs) AS 'Highest Absence'

,avg(abs) AS 'Average Abs'

FROM students

GROUP BY gender

INNER JOIN:  allows you to match related records from different tables

OUTER JOIN:  Also include  records from one of both tables that do not have corresponding records in the other table

Self Joins are a special case in which you join a table with itself to compare records in the same table.

You creat an INNER JOIN by including the 2 tables in the FROM clause with the INNER JOIN keyword and specifying the join condition using the ON keyword

ex. Select x

FROM oldDB

INNER JOIN newDB

ON table=table

What is an OUTER JOIN?

What are the types of OUTER JOINS?

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

What is a LEFT OUTER JOIN?

The LEFT OUTER JOIN includes rows that appear in the left table but dont have a matching record in the right table.  It includes both records that have a non matching value for the join attributes and records that have NULL values for the join attributes

What is a FULL OUTER JOIN?

The CASE statement allows you to perform different actions based upon the value of a database column. 

ex. A principal  wanted to expand the absence scoring system

Cases start with CASE and end with END

Students with 2 or fewer absences 'good'

3 to 5 absences 'warning'

6+ violation

ex. Select absence, 'abs rating' = CASE When abs IS NULL THEN 'Good Job!'

1.  It allows you to limit the data users can access.  ex. you can create a view that returns only certain rows from a table and then grant users permission to access the view

2.  Views reduce complexity for end users.  If end users aren't comfortable writing complex SQL queries, you can write the query for them and then hide the complexity of in a view