Test Prep--SQL Flashcards
(105 cards)
1
Q
What does RDBMS stand for?
A
Relational Database Management System
2
Q
What is Microsoft’s Cloud based SQL Database called?
A
Micosoft Windows Azure SQL Database
3
Q
What are the two standards for SQL?
A
ISO
ANSI
4
Q
What does ISO stand for?
A
International Organization for Standards
5
Q
What does ANSI stand for?
A
American National Standards Institute
6
Q
If you have a choice between doing something in a standard way or a non standard way, which way should you do it?
A
The Standard way
7
Q
What is the standard way to say “does not equal”?
A
<>
8
Q
Is CAST or CONVERT the standard function?
A
CAST
9
Q
What is the standard way to end all SQL statements?
A
with a ;
10
Q
What does the word “Relation” in relational database refer to?
A
A Table
11
Q
What are the two parts of a relation?
A
Heading
Body
12
Q
What is the heading comprised of?
A
Attributes (or columns)
13
Q
How is an attribute defined?
A
By name and type name. (Ex. User_id=Name and int=type name)
14
Q
How is a body defined?
A
As a set of tuples (basically a row with each tuple being the value of a column). Each tuple’s heading is the heading of the relation (or table).
15
Q
What are the mathmatical theories that underpin the relational model of SQL?
A
Set Theory
Predicate Logic
16
Q
Why is the set {a,a,b,c,c,c,}= set {a,b,c}
A
Because a set has no duplicates and the duplicates get ignored.
17
Q
What is the definition of a sequence?
A
An ordered set. Sets do not themselves have an inherent order.
18
Q
Why is the set {a,a,b,c,c,c,}= set {a,c,a}
A
Because a set has no duplicates and no order.
19
Q
What is a predicate?
A
An expression that when attributed to some object, makes a proposition either true or false (Ex. Create_date>’2012-03-04’)
20
Q
When you add a predicate to a specific piece of data, then what do you have?
A
A proposition. (Ex. Asking whether Create_DATE>’2012-03-04’ for a specific user account.
21
Q
What is a different name for a predicate?
A
A parameterized proposition.
22
Q
What is the difference between SQL and T-SQL?
A
SQL is standard. T-SQL is the dialect of an extension to SQL that Microsoft implements in its RDBMS
23
Q
Why should you try to avoid looping through tables with T-SQL?
A
Because the relational concept states that you should deal with sets as a whole not as individual parts. Iterative solutions should be avoided if at all possible.
24
Q
What are other names for multi-set theory?
A
Bag or superset
25
What is the difference between a set and a multi-set?
The multi-set can have duplicate where the set cannot.
26
If you don't specify the sort using an Order By clause, what order will the data return in?
Any order, you can never guarantee the order if you don't specify it in the query.
27
How does SQL differ from T-SQL in the ordering of columns?
In SQL there is no inherent order to the columns. In T-SQL the order of columns in for instance a "select * from tblName" is set by the order that the columns appear in in the table definition. Also, T-SQL allows you to refer to columns in your order by clause by the numeric order that the columns apear in the Select statement. Order By 1 indicates that the results should be ordered by the first column in the Select statement.
28
According to the relational model, should you ever have a column with no name in your query?
No, if you're using functions or joining two columns together, you should always give your column an alias.
29
Should you ever use numbers to refer to columns in the select statement in your Order by Clause as is allowed by T-SQL?
No. If you change the order of your Select statement, you may forget to change the order of your Order by Clause and then you've broken your query.
30
If you return two columns with the same name, what must you do to make your results relational?
Give them unique alias names.
31
What are the values of a Three valued predicate logic?
True
False
Null
32
Name two aspects in which T-SQL deviates from the relational model?
A table doesn't have to have a key in T-SQL
| T-SQL allows refering to ordinal positions of columns by the Order By clause
33
Is field and record an appropriate way to refer to columns and rows?
No, field and record are UI related terms not database related terms.
34
Is NULL value a correct term?
No--you should say Null Mark or just Null. Null means the absence of a value so null value is illogical
35
What is the difference between Logical and Physical Query processing?
Logical processing is about interpretting the syntax to describe what results should be returned. Physical processing is about the steps the server goes through to return the results.
36
What is the difference between declarative and imperative languages?
Declarative languages are where you define WHAT you want.
| Imperative languages not only define WHAT you want, but HOW you are supposed to get your result.
37
Is TSQL an Declarative or Imperative language?
Declarative. It's the databases's responsibility to figure out how to yield results.
38
What does SEQUEL stand for?
Structured English Query Language. It's an older version and term for SQL.
39
What does SQL stand for?
Structured Query Language
40
List the main clauses used in SQL in the order in which they should be included in a query's syntax:
```
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
```
41
List the Logical Query processing order:
```
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
```
42
How does the logical query processor operate on each of the SQL clauses?
It operates on the tables involved in one or more clauses and returns the results as a virtual table which the next clause then operates on until it gets to the end.
43
Why can't you refer to an alias you've defined in the Select statement in your Where clause?
Because the logical query processing order processes the WHERE clause before the Select Statement so the alias hasn't been defined yet.
44
Which syntax is better for performance (YEAR>=2003 OR YEAR>='20030101')?
YEAR>='20030101' is better for performance.
45
What is the difference between the Where and the Having clauses?
The Where clause operates on the results of the From Clause. The Having clause operates on the results of the Group By clause.
46
Where is a Distinct processed?
As part of the Select statement.
47
Can you reference an Alias given to one attribute in another attribute of the same Select statement?
No. Conceptually--although not necessarily physically--TSQL processes all of the attributes of a Select statement as though it were doing them all at once.
48
Can you refer to aliases defined in a Select statement inside of the Order by clause?
Yes because Order By is processed after Select.
49
What is the result of an Order by clause called?
A cursor.
50
Can you use an Order By clause when defining a View?
No. Views must consume relational data and by definition, ordering data is not relational.
51
If you use a TOP in your select statement and you order the results, when does the TOP get processed?
After the Order BY
52
What does the OFFSET keyword do?
You use it with the Order by and OFFSET means to skip X rows (ex. Order by User_id OFFSET 10 means to skip the first 10 user IDs in the list and return everything else).
53
What does the fetch keyword do?
You use it with the Order by and often with the Offset keyword as well. FETCH indicates how many rows you'd like to return. Fetch 10 means return ten rows.
54
List violations of relational Query writing:
Rows returned are not distinct
All columns are not named
The results are ordered
55
What is another name for table?
Schema
56
Should you ever refer to columns just by column name without including the schema identifier?
No. Including the table name avoids unintentional ambiguity and improves performance.
57
What is best practice for referring to tables?
Best practice is to alias them in 1-3 character names.
58
Is it good practice to use * in your select statement?
No, you should always specify the columns you want to prevent unnecessary performance hits.
59
Why should you always use AS rather than space when assigning Aliases in the select clause?
Because if you forget to separate a comma between your attributes, then it'll be harder for you to spot the error when you get back less rows than you expected. In this case SQL will interpret your mistake as an Alias.
60
What is the difference between SQL and T-SQL in relation to minimum query clause requirements?
SQL requires a Select and FROM clause. T-SQL requires only a Select clause.
61
What are the two ways you can delimit an alias?
With double quotes or square brackets.
62
In what circumstances do you have to delimate (surround with quotes or brackets) an alias because it's defined as an irregular identifier?
If the alias begins with a number
If it's an SQL reserved keyword
If it has embedded spaces
63
What are the two ways to Alias an attribute in T-SQL?
With a space or with an AS
64
What does physical data independence mean?
It means that data formatting should be done by the application, but data type should be defined by the constraints that you wish to put on a piece of data and also by the behaviors you wish for the data to exhibit.
65
What are some of the factors to consider when choosing a data type?
Constraints you need to put on the data
Whether the data can ever be null
The size of the data
66
What are three fixed type data types?
CHAR
NCHAR
BINARY
67
What are three dynamic type data types?
VARCHAR
NVARCHAR
VARBINARY
68
What does fixed type mean in the context of data types?
Fixed types use the storage for the indicated size (ex CHAR(30)) whether the value actually needs all of the space or not.
69
What does dynamic type mean in the context of data types?
It means that only the bytes necessary to store the value are used (plus a few more to store offeset information).
70
Is fixed type or dynamic type better for performance?
Fixed type is better for update performance because no data shifting is required.
Dynamic type is better for storage and for query performance because the only thing being stored, or returned by the query is actual data and not empty space.
71
What are two examples of regular character types?
CHAR
| VARCHAR
72
What are two examples of UNICODE character types?
NCHAR
| NVARCHAR
73
Why would you use Unicode character types rather than regular character types?
Unicode should be used when you need to support more than two languages where one of the languages is English. It requires 2 bytes per character which is more than the 1 byte per character of regular character types, so regular character types should be used if you only need to support English and one other language.
74
Should you ever use the defaul length for CHAR and VARCHAR?
No. In different circumstances, the default length can be different. Be specific.
75
Why is it important to ensure that columns used as joins will have the same data type?
If the SQL engine has to do implicit conversions of the types it can impact performance and perhaps cause indexes to be used inefficiently.
76
How do you indicate a literal for a unicode character string?
N'abc'
77
What are examples of functions that do explicit conversions?
```
CAST
CONVERT
PARSE
TRY_CAST
TRY_CONVERT
TRY_PARSE
```
78
How does a TRY conversion function report a failure to convert?
It returns NULL
79
What is the difference between CAST, CONVERT, and PARSE?
CAST takes parameters for input value and new data type only.
CONVERT takes input value, new data type, and style.
PARSE takes input value, new data type and culture.
80
Why would 5/2 be 2 for an int data type?
Because int only supports whole numbers.
81
When using an expression that involves operands of different types, how does SQL know which operand to convert to the other type?
It converts to the type whose data type precedence is highest.
82
What are four common options for generating unique keys for a table?
Using an identity column property
Using a sequence object
Using a Nonsequential GUID
Using a Sequential GUID
83
What is the difference between an identity column property and a sequence object?
The sequence object is a separate object stored elsewhere in the database from which you request the next sequential number.
The identity column property just gives you a one up number for each new record created.
84
What data type do you use to store Nonsequential GUIDs?
UNIQUEIDENTIFIER
85
What function do you use to generate a Nonsequential GUID?
NEWID
86
What function do you use to generate a Sequential GUID?
NEWSEQUENTIALID
87
How many bytes are required to store a UNIQUEIDENTIFIER?
16
88
How does the size of the unique identifier impact performance?
The bigger the size, the more storage is required, hence the slower the reads. Also if you define your clustered index off of the primary key, then all of your non-clustered indexes will be bound to that big column and each of them gets slower too.
89
What are the 6 Current Date and Time functions?
```
GETDATE
CURRENT_TIMESTAMP
GETUTCDATE
SYSDATETIME
SYSUTCDATETIME
SYSDATETIMEOFFSET
```
90
What is the difference between GETDATE and CURRENT_TIMESTAMP?
They both return the DATETIME data type with the date and time from the SQL server instance you're connected to but CURRENT_Timestamp is standard and therefore recommended.
91
What is the difference between CURRENT_TIMESTAMP, SYSDATETIME, and SYSDATETIMEOFFSET
Current Timestamp returns a DateTime data type.
SYSDateTime returns a DATETIME2 data type which is more precise.
SYSDATETIMEOFFSET returns a DateTime2 data type which is more precise and which includes the GMT offset (ex -4:00)
92
What is the difference between GETUTCDATE and SYSUTCDATETIME?
GETUTCDATE returns a date time data type and SYSUTCDATETIME returns the DATETIME2 data type.
93
What functions return just the date or just the time?
There aren't any. Cast one of the datetime functions to Date or Time.
94
What function do you use to get a day or some other segment out of a datetime value?
DATEPART
95
What is the syntax for DATEPART?
DATEPART('month', CURRENT_TIMESTAMP)
96
What other functions can you use instead of DATEPART?
YEAR
Month
DAY
97
What function do you use to return the name of a day or month instead of the number?
DATENAME
98
What function do you use to compute the end of month date for the inputted date time value?
EOMONTH
99
What functions might you use to construct a date and time from parts?
```
DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS
```
100
What is the syntax for DATEADD?
DATEADD(year, 1, '201202012')
101
What is the syntax for DATEDIFF?
DATEDIF(day, EARLIERDATE, LATERDATE)
102
What is the difference between the SWITCHOFFSET and the TODATETIMEOFFSET functions?
SWITCHOFFSET allows you to pass in any DATETIMEOFFSET value and specify the timezone offset you want to see returned without you having to work out the math.
TODATETIMEOFFSET allows you to pass in a DateTime value and an offset value and you can then create a DATETIMEOFFSET data type value from the parts.
103
What are the two ways to join data strings in SQL?
With the + or the CONCAT functions
104
If you use the + to join data, what happens when one of the values is null?
The whole joined value becomes null.
105
What are two functions that help avoid getting back null values when one of a joined string is null but not the other parts?
Use CONCAT to join the string or use COALESCE around the attribute that may have null values.
