Exam 70-461 - Ch 1 - Foundations Of Querying

Front 1

What are the mathematical foundations of T-SQL

Back 1

It is based on standard SQL, which is based on the "relational model", which is based on "set theory" & "predictate theory"

Front 2

1) What is relational model?
2) What is it in terms of SQL?

Back 2

1) A mathematical model for data management and manipulation

2) A "table"

Front 3

A relation, "table" has two components:

Back 3

A table has a Heading and a body.

Front 4

What is a "heading?"

Back 4

A heading is a set of attributes, each of a given type (what SQL attempts to represent with columns).

Front 5

What is a "body"?

Back 5

A "body" is a set of "tuples", which is an ordered set of elements. (SQL attempts to represent as rows)

Front 6

What is a set:

Back 6

A set is a colletion of elements into a whole of definite, distict objects.

Front 7

A set has no ......

Back 7

Order

Front 8

What is a sequence?

Back 8

An ordered set

Front 9

A set is "distinct", meaning:

Back 9

It has no duplicates. If something is true, it can't be more true

Front 10

A set should be considered as a "whole", meaning?

Back 10

You do not interact with the individual elements of the set, but rather the set as a whole.

Front 11

What is a "predicate"?

Back 11

A predicate is an expression that when attributed to some object, make a proposition either true or false.

It is a parameterized proposition.
Eg. "salary greater than R5k"

It can enforce data integrity

Front 12

A relation (SQL attempts to represent with a table) has a heading and a body, what are they?

Back 12

A "heading" is a set of attributes (SQL attempts to represent as a column), each of a given type.

The "body" is a set of tuples.
Tuples are an ordered set of elements.

Front 13

What are the mathematical branches that the relational model are based on?

Back 13

Set theory & predicate logic.

Front 14

What is the difference between SQL & T-SQL?

Back 14

SQL is standard
T-SQL is the dialect & exoension of SQL which Microsoft use in its RDBMS - SQL Server

Front 15

To follow relational theory, you need to enforce ....

Back 15

Uniqueness.
I.e. your table needs a "key"

Front 16

T-SQL is based not so much on set theory (uniqueness), but on ....

Back 16

A "multiset" (also known as a "bag" or a "superset".

Allows duplicates, eg many KZN in a query

Front 17

To remove duplicates from a query: SELECT .....

Back 17

SELECT DISTINCT country

Front 18

If you want to order the rows, you need to add .....

Back 18

ORDER BY

SELECT empid, lastname
FROM HR.Employees
ORDER BY empid;

Front 19

What is a "cursor"

Back 19

What SQL calls the order of rows where the output is guarenteed by the heading attribute

The column is ordered

Front 20

Does SQL allow "Null" values?

Back 20

Yes.

You can assign a Null value

Front 21

Name two aspects in which T-SQL deviates from the Relational model

Back 21

1) A relation has a body with a distinct set of tuples (doesn't allow buplicates), where as a table doesn't have to have a key.

2) T-SQL allows referring to ordinal positions of columns with ORDER BY (you can order)

Front 22

To make sure a table is relational, you need to ....

Back 22

define a "key"

Front 23

What are the correct T-SQL definitions of a field & a record

Back 23

T-SQL calls a field a "column" & a record a "row"

Front 24

Can you refer to a "NULL value"?

Back 24

No!
A NULL is a mark for a missing value, not a value its self

Front 25

Why are the terms "field" and "record" incorrect when referring to a column & a row?

Back 25

A field and a record describe physical things, where are columns and rows are logical elements of a table.

Front 26

Why is it important to use the standard SQL code where possible?

Back 26

Standard code is more portable between platforms