Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Foundations of Relational SQL > Flashcards

Foundations of Relational SQL Flashcards

1
Q

Why write SQL in a standard way whenever possible?

A
  1. It’s more portable between platforms
  2. Your knowledge is more portable”

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What mathematical model is standard SQL based on?

A

The Relational Model

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How is a relation, from the Relational Model, represented in SQL?

A

SQL is not able to perfectly represent a relation. However, its best attempt to represent a relation is with a table.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What two components is does a relation have?

A

A heading and a body

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What does a heading consist of?

A

A heading consists of a set of attributes, which SQL attempts to represent with columns.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What two components should each attribute have to be relational?

A
  1. A name that is unique. To be relational no two attributes should have the same name
  2. An assigned type.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What order are the attributes in?

A

The attributes are not required to be in any specific order

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What order will the attributes be in if you use a SELECT * statement?

A

They will be ordered based on the order in which they were defined when initially creating the table.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What does a body consist of?

A

A body consists of a set of tuples, which SQL attempts to represent with rows.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What two branches of mathematics are the foundation for the relational model?

A
  1. Set Theory
  2. Predicate Logic

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is a set?

A

A collection of distinct objects, called the elements of the set.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a proposition?

A

It is a declarative sentence that is true or false. Example, Paul lives in Minneapolis.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a declarative sentence?

A

A declarative sentence makes a statement and ends with a period.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is a predicate?

A

Something having the form of a declarative sentence with parameters that are not yet known, so it cannot be decided whether it is true or false. Example, Paul lives in x.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How do you make a predicate into a proposition?

A

Supply values for the unknown parameters. So Paul lives in x becomes Paul lives in Minneapolis. Now you can decide whether the statement is true or not.

SQL Server 70-461 01-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are predicates used for in the relational model?

A
  1. To enforce data integrity
  2. To filter data
  3. To define the data model

SQL Server 70-461 01-01

17
Q

Does SQL Server allow duplicates in a table?

A

Yes, it does not perfectly follow relational theory in this way. SQL Server allows you to create a table without a key. In strict terms, this is not relational.

SQL Server 70-461 01-01

18
Q

How could you follow relational theory with regard to not allowing duplicates when creating a table in SQL Server?

A

By using a primary key or unique constraint.

SQL Server 70-461 01-01

19
Q

Does SQL Server allow duplicates in a query?

A

Yes. For example, you could do SELECT Country FROM HR.Employees and you could get duplicate countries if they exist in the data.

SQL Server 70-461 01-01

20
Q

How could you follow relational theory with regards to a query returning duplicates?

A

You could use the DISTINCT operator.

SQL Server 70-461 01-01

21
Q

By default, what order is data from a query returned in?

A

The query returns a relational result, which by definition has no prescribed order. The data may appear to be in a certain order due to how the engine decided to execute the query, but the only way you can count on the data being in a specific order every time is to have an ORDER BY clause in the SQL statement.

SQL Server 70-461 01-01

22
Q

Once you add an ORDER BY clause to a SQL statement, it is no longer relational. What kind of operation is it?

A

It is a cursor

Note: the use of the term cursor here is conceptual. T–SQL also supports anobject called a cursor that is defined based on a result of a query, and that allows fetchingrows one at a time in a specified order.

SQL Server 70-461 01-01

23
Q

Should you use ordinal position or attribute name in the ORDER BY clause?

A

Always use the attribute name. The ordinal position is the order in which the column is listed in the select statement. The query would come back in the wrong order if you added a column to the SELECT list but didn’t update the ordinal position of the attribute in the ORDER BY clause to reflect this.

SQL Server 70-461 01-01

24
Q

Using an ORDER BY clause is not relational. On top of that, which method of listing attributes in the ORDER BY clause is not relational?

A

Referencing the attributes by ordinal position is not relational, refer to them by name instead.

SQL Server 70-461 01-01

25
Q

What is a cursor?

A

A cursor retrieves data from a result set one row at a time instead of operating on all of the rows in a result set at one time.

SQL Server 70-461 01-01

26
Q

Do you have to assign a name to an attribute created using an expression?

A

T–SQL does not require you to assign a name. However, to be relational, and to follow best practice, you should always give an attribute a name.

SQL Server 70-461 01-01

27
Q

What should you do if the query you are creating uses two attributes with the same name from two different tables?

A

In order to be relational, you will need to make sure to use an alias for at least one of them. T–SQL allows the return of two attributes with the same name in a query. To be relational, all attributes must have a unique name.

SQL Server 70-461 01-01

28
Q

How did Codd deviate from a purely relational model when it comes to a predicate evaluating to True or False?

A

He added a third value, unknown. This allows a predicate to evaluate to true, false or unknown. This is not strictly relational. However, Codd believed it was important to support missing values.

SQL Server 70-461 01-01

29
Q

Name seven ways in which T–SQL deviates from the relational model.

A
  1. Allowing two attributes from two different tables with the same name in a query without assigning an alias to one of them. To be relational, attributes need to have unique names.
  2. Allowing ordinal numbers to be used in the ORDER BY clause.
  3. Allowing an attribute that was created using an expression to have no name. To be relational, all attributes must have names.
  4. Allowing a table without a key.
  5. Allowing a query to return a result with duplicates (use DISTINCT to mitigate this and return a relational result.
  6. Allowing rows to be returned in a specific order using the ORDER BY clause. To be relational, a result set would have no guaranteed order.
  7. Some believe that a relational model should only have two–valued logic, which wouldn’t allow for the use of nulls. T–SQL allows the use of nulls.

SQL Server 70-461 01-01

30
Q

When speaking about T–SQL, what is the correct terminology to use, column and row or field and record?

A

The correct terminology is column and row.

Column and row are considered logical constructs. A table is considered logical.

Fields and records are considered physical items, such as a field in the user interface or a record that is stored in a file.

SQL Server 70-461 01-01

31
Q

What is the correct way to refer to a null?

A

The correct way would be to call it a null mark. Lots of people call it a null value, this is incorrect. A null, by definition, has no value. It is a mark for a missing value.

SQL Server 70-461 01-01

32
Q

When should you write SQL code in a standard way and when should you write in a nonstandard way?

A
  • Write SQL code in a standard way whenever possible.
  • Only choose the nonstandard option when it has some important benefit that the standard option doesn’t.
  • As an example, T–SQL supports <> and != for “not equal to”. <> is standard, != is not. Preference would therefore be to use <>.

SQL Server 70-461 01-01

33
Q

What is meant by “relational” in Relational Model?

A

The word “relational” in Relational Model is referring to the mathematical concept relation, not relationships between tables as is most often assumed.

SQL Server 70-461 01-01

34
Q

Name three characteristics of a set.

A
  1. The set should be considered as a whole, not its individual elements
  2. There are no duplicates {a, b, c} is considered equivalent to {a, a, b, c, c, c}
  3. Order of the elements is irrelevant {a, b, c} is considered equivalent to {b, a, c, c, a, c}

SQL Server 70-461 01-01

Interview Prep - SQL (26 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions