Sql Querying Class 1

Front 1

What are the categories of T-SQL Statements?

Back 1

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Control Language (DCL)

Front 2

Name the 4 types of T-SQL Data Manipulation Language Categories (Statements for querying and modifying data)

Back 2

Select

Insert

Update

Delete

Front 3

What are the 3 Data Definition Language Categories (Statements for object definitions)

Back 3

Create

Alter

Drop

Front 4

What are the Data Control Language Categories? (Statements for security permissions)

Back 4

Grant

Revoke

Deny

Front 5

Name the 2 types of T-SQL Language Elements?

Back 5

Predicates

Operators

Front 6

List the Predicates used in T-SQL queries.

Back 6

In

Between

Like

Front 7

Describe the difference in the Predicates 'IN', 'BETWEEN', and 'LIKE'.

Back 7

'In' is used to determine if a value matches any value in a list or subquery

'Between' is used to specify a range of values

'Like' is used to match characters against a pattern

Front 8

List the Comparison Operators used in T-SQL.

Back 8

=, >, <, >=, <=, <>, !=, !>, !<

(equal, greater than, less than greater than or equal to, less than or equal to, not equal to...)

Front 9

What are the 3 Logical Operators in T-SQL?

Back 9

AND

OR

NOT

Front 10

List the Arithmetic Operators used in T-SQL.

Back 10

+, -, *, /, %

(add, subtract, multiply, divide, and modulo)

Front 11

What symbol is used for Concatenation in T-SQL?

Back 11

+

(plus sign)

Front 12

What is the Order of Evaluation of T-SQL Operators?

Back 12

( ) Parentheses

*,/% (multiply, divide, modulo)

+,- (add/positive/concatenate, subtract/negative)

=,<,>, >=, <=, !=,! >, !< (comparison)

NOT

AND

BETWEEN, IN, LIKE, OR

= (Assignment)

Front 13

Name the T-SQL Language Element Functions.

Back 13

String Functions

Date and Time Functions

Aggregate Functions

Mathematical Functions

Front 14

Name the T-SQL String Functions.

Back 14

SUBSTRING

LEFT, RIGHT

LEN

DATALENGTH

REPLACE

REPLICATE

UPPER, LOWER,

RTRIM, LTRIM

Front 15

What are the T-SQL Date and Time Functions?

Back 15

GETDATE

SYSTDATETIME

GETUTCDATE

DATEADD

DATEDIFF

YEAR

MONTH

DAY

Front 16

Name the T-SQL Aggregate Functions one can use in a query.

Back 16

SUM

MIN

MAX

AVG

COUNT

COUNTBIG

Front 17

Bonus: Name the T-SQL Mathematical Functions.

Back 17

RAND

ROUND

POWER

ABS

CEILING

FLOOR

Front 18

What are Variables used for in T-SQL and give some characteristics of them?

Back 18

Temporarily store a value of a specific data type. It must be declared.

It's local, not global.

The name begins with a single @ sign.

It is assigned a data type.

Front 19

What is the syntax for creating a Variable in T-SQL?

Back 19

DECLARE @MyVar int = 30;

Front 20

What are Expressions in T-SQL?

Back 20

Combination of identifiers, values, and operators evaluated to obtain a single result

Can be used in SELECT statements and WHERE clauses.

SELECT=manipulate columns

CASE=to replace values matching a logical expression with another value

WHERE=filtering

As Table Expressions to create temp sets for further processing

Front 21

Name some commonly-used control-of-flow, errors, and transaction statements.

Back 21

IF...ELSE

WHILE

BEGIN...END

TRY...CATCH

BEGIN TRANSACTION (COMMIT TRANSACTION or ROLLBACK TRANSACTION)

Front 22

What are the 2 ways to add Comments to a query?

Back 22

-- ignore this line

/*

This is a block of commented code

*/

Front 23

What command does one use to separate batches from each other in a query?

Back 23

GO

Front 24

What is 'Set Theory' in SQL Server?

Back 24

A mathematical basis for the relational database model.

Front 25

What is a 'Set' in SQL Server?

Back 25

A collection of definite, distinct objects considered as a whole.

Front 26

What are the characteristics of a 'Set Element' in SQL Server?

Back 26

Elements of a set are:

Called Members (such as set of all Customers)

Described by Attributes (FirstName, LastName)

Must be distinct, or unique (AccountNumber)

Front 27

What is the purpose of Predicates in T-SQL queries?

Back 27

Make comparisons and express the results as TRUE or FALSE, or UNKNOWN (NULL).

Front 28

What are some uses for Predicates in T-SQL queries?

Back 28

Filtering data in queries (WHERE & HAVING clauses)

Providing conditional logic to CASE expressions

Joining tables (in the ON filter)

Defining subqueries (in EXISTS test, for example)

Enforcing data integrity (CHECK constraints)

Control of Flow (IF statement)

Front 29

Name the Elements and Expressions of a SELECT statement, in the order they need to be written. Define them, if possible.

Back 29

SELECT - select list - columns to return

FROM - table source - defines table(s) to query

WHERE - search condition - filters rows using a predicate

GROUP BY - group by list - arranges rows by groups

HAVING - search condition - filters groups using a predicate

ORDER BY - order by list - sort the output

Front 30

Name the order in which Elements are processed in a T-SQL query (the logical order of processing).

Back 30

FROM (provides source rows)

WHERE (filters rows from source table)

GROUP BY (organizes the rows)

HAVING (filters out entire groups based on its predicate)

SELECT (determines columns to appear in query)

Front 31

What are the two items you should include in the FROM clause when specifying the table where you will retrieve your data?

Back 31

Schema.Object

Front 32

What character should you use to end all query statements with?

Back 32

semicolon (;)

Front 33

Calculated expressions in a SELECT clause must be __________.

Back 33

Scalar

(meaning, they must return only a single value)

Front 34

What is the most basic way to eliminate duplicates in a T-SQL query?

Back 34

Using the DISTINCT clause

(thus eliminating the hidden default SELECT ALL statement)

Front 35

What is the one exception clause where you can use an Alias created in the SELECT clause of a query, and why?

Back 35

ORDER BY - because of the logical order of processing a SQL query, ORDER BY is processed after SELECT, thus you can refer to your Alias name at that point.

Front 36

What SQL query clauses allow CASE statements?

Back 36

SELECT column list

WHERE or HAVING clauses

ORDER BY clause