70-461 Lesson Reviews
Which of the following functions would you consider using to generate surrogate keys? (choose all that apply). A. NEWID B. NEWSEQUENTIALID C. GETDATE D. CURRENT_TIMESTAMP
A & B. NEWID and NEWSEQUENTIALID generate GUIDs either in random or sequential order respectively.
What can you specify as the source data in the USING clause? A. A regular table, table variable or temporary table B. A table expression like a derived table or a CTE C. A stored procedure D. A table function like OPENROWSET or OPENXML
A. A regular table, table variable or temporary table B. A table expression like a derived table or a CTE D. A table function like OPENROWSET or OPENXML
Which of the following literals are language-dependent for the DATETIME data type? A. '2012-02-12' B. '02/12/2012' C. '12/02/2012' D. '20120212'
A. '2012-02-12' B. '02/12/2012' C. '12/02/2012'
Which of the following functions can be used in a CATCH block to return information about the error? A. @@ERROR B. ERROR_NUMBER() C. ERROR_MESSAGE() D. XACT_STATE()
A. @@ERROR B. ERROR_NUMBER() C. ERROR_MESSAGE() D. XACT_STATE()
Which of the following strategies can help reduce blocking and deadlocking by reducing shared locks? A. Add the READUNCOMMITTED table hint to queries B. Use the READ COMMITTED SNAPSHOT option C. Use the REPEATABLE READ isolation level. D. Use the SNAPSHOT isolation level.
A. Add the READUNCOMMITTED table hint to queries B. Use the READ COMMITTED SNAPSHOT option D. Use the SNAPSHOT isolation level.
When referring in the OUTPUT clause to columns from the inserted rows, when should you prefix the columns with the keyword inserted? A. Always B. Never C. Only when the statement is UPDATE D. Only when the statement is MERGE
A. Always
Which of the following T-SQL statement automatically occur in the context of a transaction? A. An ALTER TABLE command B. A PRINT command C. An UPDATE command D. A SET command
A. An ALTER TABLE command C. An UPDATE command
Which function can be used to rank documents based on proximity of words? A. CONTAINSTABLE() B. FREETEXTTABLE() C. SEMANTICKEYPHRASETABLE() D. SEMANTICSIMILARITYTABLE() E. SEMANTICSSIMILARITYDETAILSTABL()
A. CONTAINSTABLE()
What is the difference between the COUNT(*) aggregate function and the COUNT(<expression>) general set function? A. COUNT(*) counts rows; COUNT(<expression>) counts rows where <expression> is not NULL B. COUNT(*) counts columns; COUNT(<expression) counts rows C. COUNT(*) returns a BIGINT; COUNT(<expression>) returns an INT D. There;s no difference between the functions.
A. COUNT(*) counts rows; COUNT(<expression>) counts rows where <expression> is not NULL
Which FOR XML options are valid? A. FOR XML AUTO B. FOR XML MANUAL C. FOR XML DOCUMENT D. FOR XML PATH
A. FOR XML AUTO D. FOR XML PATH
What are the things that the SELECT INTO statement doesn't copy from the source? A. Indexes B. Constraints C. The IDENTITY property D. Triggers
A. Indexes B. Constraints D. Triggers
What can you search for with the CONTAINS predicate? A. Inflectional forms of a word B. Synonyms of a searched word C. Translation of a word D. Text in which a search word is close to another search word E. A prefix of a word or phrase only
A. Inflectional forms of a word B. Synonyms of a searched word D. Text in which a search word is close to another search word E. A prefix of a word or phrase only
Which form of the proximity term defines the distance and the order? A. NEAR((SearchWord1, SearchWOrd2), 5, TRUE) B. NEAR((SearchWord1, SearchWord2), CLOSE, ORDER) C. NEAR((SearchWord1, SearchWOrd2), 5) D. NEAR(SearchWord1, SearchWord2)
A. NEAR((SearchWord1, SearchWOrd2), 5, TRUE)
What kind of XML indexes can you create? A. PRIMARY B. PATH C. ATTRIBUTE D. PRINCIPALNODES
A. PRIMARY B. PATH
Which of the following is only possible when using the MERGE statement in regard to the OUTPUT clause? A. Referring to columns from the source table B. Referring to both the keywords deleted and inserted C. Assigning aliases to output columns D. Using composable DML
A. Referring to columns from the source table
Which of the following OFFSET_FETCH options are valid in T-SQL? A. SELECT ... ORDER BY orderid OFFSET 25 ROWS B. SELECT ... ORDER BY orderid FETCH NEXT 25 ROWS ONLY C. SELECT ... ORDER BY orderid OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY D. SELECT ... <no ORDER BY> OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY
A. SELECT ... ORDER BY orderid OFFSET 25 ROWS C. SELECT ... ORDER BY orderid OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY
Which of the following are not allowed in the PIVOT operator's specification? A. Specifying a computation as input to the aggregate function. B. Specifying a computation as the spreading element C. Specifying a subquery in the IN clause D. Specifying multiple aggregate functions
A. Specifying a computation as input to the aggregate function. B. Specifying a computation as the spreading element C. Specifying a subquery in the IN clause D. Specifying multiple aggregate functions
Which full-text search elements can you use to prevent indexing noisy words? A. Stopwords B. Thesaurus C. Stemmer D. Stoplists
A. Stopwords D. Stoplists
What types of database objects can have synonyms? A. Stored procedures B. Indexes C. Temporary tables D. Database Users
A. Stored procedures C. Temporary tables
Which of the following are true about synonym? A. Synonyms do not store T-SQL Data. B. Synonyms do not require schema names. C. Synonym names can match those of the objects they refer to. D. Synonyms can reference objects in other databases or through linked servers.
A. Synonyms do not store T-SQL Data. D. Synonyms can reference objects in other databases or through linked servers.
Which of the following operators work in T-SQL views? A. The WHERE clause B. The Order By clause C. The UNION or UNION ALL operators D. The GROUP BY clause
A. The WHERE clause C. The UNION or UNION ALL operators D. The GROUP BY clause
Which of the following is applicable to a DELETE statement? A. The statement writes more to the transaction log than TRUNCATE. B. The statement resets an IDENTITY property. C. The statement is disallowed when a foreign key points to the target table. D. The statement is disallowed when an indexed view based on the target table exists.
A. The statement writes more to the transaction log than TRUNCATE.
Which of the following operators removes duplicates from the result? A. UNION B. UNION ALL C. INTERSECT D. EXCEPT
A. UNION C. INTERSECT D. EXCEPT
In an INSERT SELECT statement, how do you generate sequence values in specific order? A. Use the OVER clause in the NEXT VALUE FOR function. B. Specify an ORDER BY clause at the end of the query. C.Use TOP(100) PERCENT and ORDER BY in the query. D. Use TOP(9223372036854775807) and ORDER BY in the query.
A. Use the OVER clause in the NEXT VALUE FOR function.
You want to enforce that a valid supplierid be entered for each productid in the Production.Products table. What is the appropriate constraint to use? A. A unique constraint B. A default constraint C. A foreign key constraint D. A primary key constraint
C. A foreign key constraint
How do you modify a column value in a target row and collect the result of the modification in one visit to the row? A. By using an UPDATE based on a join B. By using an UPDATED based on a table expression C. By using an UPDATE with a variable D. The task cannot be achieve with only one visit to the row.
C. By using an UPDATE with a variable
What are the benefits of using the combination of statements CREATE TABLE and INSERT SELECT over SELECT INTO? A. Using the CREATE TABLE statement, you can control all aspects of the target table. Using SELECT INTO, you can't control some of the aspects, like the destination file group. B. The INSERT SELECT statement is faster than SELECT INTO C. The SELECT INTO statement locks both data and metadata for the duration of the transaction. This means that until the transaction finishes, you can run into blocking related to both data and metadata. If you run the CREATE TABLE and INSERT SELECT statements in separate transactions, locks against metadata will be released quickly, reducing the probability for and duration of blocking related to metadata. D. Using the CREATE TABLE plus INSERT SELECT statements involves less coding than using SELECT INTO.
A. Using the CREATE TABLE statement, you can control all aspects of the target table. Using SELECT INTO, you can't control some of the aspects, like the destination file group. C. The SELECT INTO statement locks both data and metadata for the duration of the transaction. This means that until the transaction finishes, you can run into blocking related to both data and metadata. If you run the CREATE TABLE and INSERT SELECT statements in separate transactions, locks against metadata will be released quickly, reducing the probability for and duration of blocking related to metadata.
How can you create synonyms for the words searched? A. You can edit the thesaurus file. B. You can create a thesaurus table. C. You can use the stopwords for synonyms as well. D. Full-text search does not support synonyms.
A. You can edit the thesaurus file.
What are the benefits of using an UPDATE statement based on joins? A. You can filter the rows to update based on information in related rows in order tables. B. You can update multiple tables in one statement. C. You can collect information from related rows in other tables to be used in the source expressions in the SET clause. D. You can use data from multiple source rows that match one target row to update the data in the target row.
A. You can filter the rows to update based on information in related rows in order tables. C. You can collect information from related rows in other tables to be used in the source expressions in the SET clause.
Which of the following are T-SQL regular identifiers? A. categoryname B. category name C. category$name D. category_name
A. categoryname C. category$name D. category_name
Which of the following in not an XML data type method? A. merge() B. nodes() C. exist() D. value()
A. merge()
What are the advantages of sp_executesql over EXECUTE() command? A. sp_executesql can parameterize search arguments and help prevent SQL injection. B. sp_executesql uses Unicode strings. C. sp_executesql can return data through output parameters
A. sp_executesql can parameterize search arguments and help prevent SQL injection. C. sp_executesql can return data through output parameters
What metadata tables give you a list of constraints in a database? A. sys.key_constraints B. sys.indexes C. sys.default_constraints D. sys.foreign_keys
A. sys.key_constraints C. sys.default_constraints D. sys.foreign_keys
Give two reasons why assigning attribute aliases is important in T-SQL.
An expression that is based on computation results in no attribute name unless you assign one with an alias, and this is not relational. Using attribute aliases, you can assign your own name to a results attribute if you need it to be different that the source attribute name.
How do the COMMIT and ROLLBACK commands work with nested transactions in T-SQL? A. A single COMMIT commits the entire nested transaction. B. A single ROLLBACK rolls back the entire nested transaction. C. A single COMMIT commits only one level of the nested transaction. D. A single ROLLBACK rolls back only one level of the nested transaction.
B. A single ROLLBACK rolls back the entire nested transaction.
Which of the following columns would be appropriate as a surrogate key? A. The time (in hundredths of a second) that the row was inserted B. An automatically increasing integer number C. The last four digits of a social security number concatenated with the first eight digits of a user's last name. D. A uniqueidentifier (GUID) newly selected from SQL Server at the time the row is inserted.
B. An automatically increasing integer number D. A uniqueidentifier (GUID) newly selected from SQL Server at the time the row is inserted.
Which WHEN clauses are required in a MERGE statement at minimum? A. At minimum, the WHEN MATCHED and WHEN NOT MATCHED clauses are required. B. At minimum, only one clause is required, and it can be any of the WHEN clauses. C. At minimum, the WHEN MATCHED clause is required. D. At minimum, the WHEN NOT MATCHED clause is required.
B. At minimum, only one clause is required, and it can be any of the WHEN clauses.
How can you update a table, setting a Column to the result of a window function? A. By using an UPDATE based on a join. B. By using an UPDATE based on a table expression C. By using an UPDATE with a variable. D. The task cannot be achieved.
B. By using an UPDATE based on a table expression
What are the benefits of using a CTE over derived tables? A. CTEs are better performing than derived tables. B. CTEs don't nest; the code is more modulate, making it easier to follow the logic. C. Unlike with derived tables, you can refer to multiple instances of the same CTE name, avoiding repetition of code. D. Unlike derived tables, TEs can be used by all statements in the sessions, and not just the statement degining them.
B. CTEs don't nest; the code is more modulate, making it easier to follow the logic. C. Unlike with derived tables, you can refer to multiple instances of the same CTE name, avoiding repetition of code.
Which FOR XML options can you use to manually format the XML returned? A. FOR XML AUTO B. FOR XML EXPLICIT C. FOR XML RAW D. FOR XML PATH
B. FOR XML EXPLICIT D. FOR XML PATH
How does SET XACT_ABORT ON affect a transaction? A. If a T-SQL error with a severity level > 16 occurs, the transaction will be aborted. B. If a T-SQL error with a severity level > 10 occurs, the transaction will be aborted. C. If a T-SQL error with a severity level > 16 occurs, some statements of the transaction may still be executed. D. If a T-SQL error with a severity level > 10 occurs, some statements of the transaction may still be executed.
B. If a T-SQL error with a severity level > 10 occurs, the transaction will be aborted.
Which of the following techniques can be used to inject unwanted code into dynamic SQL when user input in concatenated with valid SQL commands? A. Insert a comment string of two dashes, then the malicious code, and then a single quotation mark. B. Insert a single quotation mark, then the malicious code, and then a comment string of two dashes. C. Insert the malicious code followed by a single quotation mark and a comment string of two dashes.
B. Insert a single quotation mark, then the malicious code, and then a comment string of two dashes.
How does the PIVOT operation determine what the grouping element is? A. It's the element specified as input to the GROUPING function. B. It's determined by elimination - the element(s) from the queried table that were not specified as the spreading or aggregation elements. C. It;s the element specified in the GROUP BY clause. D. It's the primary key
B. It's determined by elimination - the element(s) from the queried table that were not specified as the spreading or aggregation elements.
Which data type should be used in place of TIMESTAMP? A. VARBINARY B. ROWVERSION C. DATETIME2 D. TIME
B. ROWVERSION
What is the advantage of using THROW in a CATCH block? A. THROW in a CATCH block does not require parameters and so is easier to write. B. THROW re-throws the original error so that the original error can be handled. C. THROW causes an error severity of level 16 automatically. D. The statement before a THROW requires a semicolon.
B. THROW re-throws the original error so that the original error can be handled.
What are the advantages of using a sequence object instead of IDENTITY? A. The IDENTITY property doesn't guarantee that there won't be gaps and the sequence object does. B. The IDENTITY property cannot be added to or removed from an existing column, a DEFAULT constraint with a NEXT VALUE FOR function can be added to or removed from an existing column. C. A new identity value cannot be generated before issuing an INSERT statement, whereas a sequence value can. D. You cannot provide your own value when inserting a row into a table with an IDENTITY column without special permissions. You can specify your own value for a column that normally gets its values from a sequence object.
B. The IDENTITY property cannot be added to or removed from an existing column, a DEFAULT constraint with a NEXT VALUE FOR function can be added to or removed from an existing column. C. A new identity value cannot be generated before issuing an INSERT statement, whereas a sequence value can. D. You cannot provide your own value when inserting a row into a table with an IDENTITY column without special permissions. You can specify your own value for a column that normally gets its values from a sequence object.
Which of the following is applicable to a TRUNCATE statement? A. The statement writes more to the transaction log than DELETE. B. The statement resets an IDENTITY property. C. The statement is disallowed when a foreign key points to the target table. D. The statement is disallowed when an indexed view based on the target table exists.
B. The statement resets an IDENTITY property. C. The statement is disallowed when a foreign key points to the target table. D. The statement is disallowed when an indexed view based on the target table exists.
What is the purpose of the GROUPING and GROUPING_ID functions> A. You can use these functions in the GROUP BY clause to group data. B. You can use these functions to tell whether a NULL in the result represents a placeholder for an element that is not part of the grouping set or an original NULL from the table. C. You can use these functions to uniquely identify the grouping set that the result row is associated with. D. These functions can be used to sort data based on grouping set association - that is, first detail , and then aggregates.
B. You can use these functions to tell whether a NULL in the result represents a placeholder for an element that is not part of the grouping set or an original NULL from the table. C. You can use these functions to uniquely identify the grouping set that the result row is associated with. D. These functions can be used to sort data based on grouping set association - that is, first detail , and then aggregates.
How do you delete rows from a table for which a ROW_NUMBER computation is equal to 1? A. You refer to the ROW_NUMBER function in the DELETE statement's WHERE clause. B. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered DELETE statement against the table expression. C. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered TRUNCATE statement against the table expression D. The task cannot be achieved.
B. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered DELETE statement against the table expression.
Which of the following predicates are search arguments? A. DATE(orderdate) = 1 B. companyname LIKE 'A%' C. companyname LIKE '%A%' D. companyname LIKE '%A' E. orderdate >= '20120212' AND orderdate < '20120213'
B. companyname LIKE 'A%' The LIKE predicate is a search argument when the pattern starts with a known prefix. E. orderdate >= '20120212' AND orderdate < '20120213' Because no manipulation is applied to the filtered column, the predicate is a search argument.
Which conditional expression is supported in XQuery? A. IIF B. if.then.else C. CASE D. switch
B. if.then.else
Which XML data type method do you use to shred XML data to tabular format? A. modify() B. nodes() C. exist() D. value()
B. nodes()
Which of the following is the equivalent of <query 1> UNION <query 2> INTERSECT <query 3> EXCEPT <query 4> A. (<query 1> UNION <query 2>) INTERSECT (<query 3> EXCEPT <query 4>) B.<query 1> UNION (<query 2> INTERSECT <query 3>) EXCEPT <query 4> C. <query 1> UNION <query 2> INTERSECT (<query 3> EXCEPT <query 4>) D, <query 1> UNION (<query 2> INTERSECT <query 3> EXCEPT <query 4>)
B.<query 1> UNION (<query 2> INTERSECT <query 3>) EXCEPT <query 4>
Why is it important to use the appropriate type for an attribute?
Because the type constrains the values to a certain domain of supported values.
Why are window functions allowed only in the SELECT and ORDER BY clauses of a query?
Because they are supposed to operate on the underlying query's result, which is achieved when logical query processing gets to the SELECT phase.
Which function do you use to return the last identity value generates in a specific table? A. MAX B. SCOPE_IDENTITY C. @@IDENTITY D. IDENT_CURRENT
D. IDENT_CURRENT
What is the result of the WITH CHECK OPTION in a view that has a WHERE clause in its SELECT statement? A. Data can no longer be updated through the view B. Data can be updated through the view, but primary key values cannot be changed. C. Data can be updated through the view, but values cannot be changed that would cause rows to fall outside the filer of the WHERE clause. D. Data can b updated through the view, but only columns with check constraints can be changed.
C. Data can be updated through the view, but values cannot be changed that would cause rows to fall outside the filer of the WHERE clause.
Which directive of the FOR XML clause should you use to produce element-centric XML? A. ATTRIBUTES B. ROOT C. ELEMENTS D. XMLSCHEMA
C. ELEMENTS
Which statement(s) are invalid? A. Referring to an attribute that you group by in the WHERE clause. B. Referring to an expression in the GROUP BY clause; for example GROUP BY YEAR(orderdate) C. In a grouped query. referring in the SELECT list to an attribute that is not part of the GROUP BY list and not within an aggregate function D. Referring to an alias defined in the SELECT clause in the HAVING clause
C. In a grouped query. referring in the SELECT list to an attribute that is not part of the GROUP BY list and not within an aggregate function D. Referring to an alias defined in the SELECT clause in the HAVING clause SELECT is evaluated after the GROUP BY and after the HAVING clauses are evaluated.
Which of the following practices are considered bad practices? A. Aliasing columns by using the AS clause B. Aliasing tables by using the AS clause C. Not assigning column aliases when the column is a result of a computation D. Using * in the SELECT list
C. Not assigning column aliases when the column is a result of a computation D. Using * in the SELECT list
What is the default frame window functions use when a window order clause is specified but an explicit window frame clause isn't? A. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW B. ROWS UNBOUNDED PRECEDING C. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW D. RANGE UNBOUNDED PRECEDING
C. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW D. RANGE UNBOUNDED PRECEDING
Which function returns a table with key phrases associated with the full-text indexed column? A. CONTAINSTABLE() B. FREETEXTTABLE() C. SEMANTICKEYPHRASETABLE() D. SEMANTICSIMILARITYTABLE() E. SEMANTICSSIMILARITYDETAILSTABL()
C. SEMANTICKEYPHRASETABLE()
Which kind of dependencies do synonyms have on the objects they refer to? A. Synonyms can be created with the WITH SCHEMABINDING to prevent the underlying objects from being altered. B. Synonyms can refer to other synonyms. C. Synonyms can be created to refer to database objects that do not yet exist. D. Synonyms can be created without an initial schema name, which can be added later.
C. Synonyms can be created to refer to database objects that do not yet exist.
Which clause of the MERGE statement isn't standard? A. The WHEN MATCHED clause B. The WHEN NOT MATCHED clause C. The WHEN NOT MATCHED BY SOURCE clause D. All MERGE clauses are standard
C. The WHEN NOT MATCHED BY SOURCE clause
What is the restriction in regard to the table specified as the target of an OUTPUT INTO clause? A. The table can only be a table variable. B. The table can only be a temporary table. C. The table cannot participate in either side of a foreign key relationship. D. The table cannot have triggers defined on it.
C. The table cannot participate in either side of a foreign key relationship. D. The table cannot have triggers defined on it.
What is the result of WITH SCHEMABINDING in a view? A. The view cannot be altered without altering the table. B. The tables referred to in the view cannot be altered unless the view's SELECT statement is first altered. C. The tables referred to in the view cannot be altered unless the view is direst dropped. D. The view cannot be altered unless the table it refers to are first dropped.
C. The tables referred to in the view cannot be altered unless the view is direst dropped.
Which node type test can be used to retrieve all nodes of an XML instance? A. Asterisk (*) B. comment() C. node() D. text()
C. node()
Which database do you have to install in order to enable Semantic Search feature? A. msdb B. distribution C. semanticsdb D. tempdb
C. semanticsdb
What does CTE stand for?
Common Table Expression
In which operator does the order of the input queries matter? A. UNION B. UNION ALL C. INTERSECT D. EXCEPT
D. EXCEPT
In which case out of the following are you normally not allowed to specify the target column in an INSERT statement? A. If the column has a default constraint associated with it B. If the column allows NULLs C. If the column does not allow NULLS D. If the column as an IDENTITY propertuy
D. If the column as an IDENTITY propertuy
What is the restriction that grouped queries impose on your expressions? A. If the query is a grouped query you must invoke an aggregate function. B. If the query has an aggregate function, it must also have a GROUP BY clause. C. The elements in the GROUP BY clause must also be specified in the SELECT clause. D. If you refer to an element from the queried tables in the HAVING, SELECT, or ORDER BY clauses, it must either appear in the GROUP BY list or be contained by an aggregate function.
D. If you refer to an element from the queried tables in the HAVING, SELECT, or ORDER BY clauses, it must either appear in the GROUP BY list or be contained by an aggregate function.
Which function can be used to find the document that is most semantically similar to a specified document? A. CONTAINSTABLE() B. FREETEXTTABLE() C. SEMANTICKEYPHRASETABLE() D. SEMANTICSIMILARITYTABLE() E. SEMANTICSSIMILARITYDETAILSTABL()
D. SEMANTICSIMILARITYTABLE()
What is the data type of the target values column in the results of an UNPIVOT operator? A. INT B. NVARCHAR(128) C. SQL_VARIANT D. The data type of the source column that you unpivot
D. The data type of the source column that you unpivot
What is the difference between the result of T1 CROSS APPLY T2 and T1 CROSS JOIN T2 (the right table expression isn't correlated to the left)? A. CROSS APPLY filters only rows where the values of columns with the same name are equal; CROSS JOIN just returns all combinations. B. If T1 has rows and T2 doesn't; CROSS APPLY returns an empty set and CROSS JOIN still returns rows from T1. C. If T1 has rows and T2 doesn't; CROSS APPLY still returns the rows form T1 and CROSS JOIN returns an empty set. D. There is no difference.
D. There is no difference. Both return the same result when there's no correlation because CROSS APPLY applies all rows from T2 to each row form T1.
How can you express that the column categoryname allow NULLs? A. categoryname PERMIT NULL NVARCHAR(15) B. categoryname NVARCHAR(15) ALLOW NULL C. categoryname NVARCHAR(15) PERMIT NULL D. categoryname NVARCHAR(15) NULL
D. categoryname NVARCHAR(15) NULL
Which of the following is not a FLWOR clause? A. for B. let C. where D. over E. return
D. over
Which of the following is not a part of the CONTAINS predicate? A. FORMSOF B. THESAURUS C. NEAR D. PROPERTY E. TEMPORARY
E. TEMPORARY
Give an example of not violating the relation model.
Ensuring that all attributes in the result of a query have names.
Explain why understanding the relational model is important for people who write T-SQL code?
Even though T-SQL is based on the relational model, it deviates from it in a number of ways. But it gives you enough tools that if you understand the relational model, you can write in a relational way. Remember these examples: - Don't rely on an order of columns or rows - Always rename result columns - eliminate duplicates if they are possible in the result of your query
What is the logical query processing order of the various query clauses?
FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
You execute a query with TOP (3) WITH TIES option. How many rows will be returned?
Fewer than three, three or more than three rows. If there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are three rows or more without TOP, the query will return three tows. If there are more than three rows in the result, as well as ties with the third row, the query will return more than three rows.
Define FLWOR
For, Let, Where, Order by, Return.
Which keyword(s) can be omitted in the new standard join syntax without changing the meaning on the join?
INNER and OUTER
What is the difference between the ON clause and the WHERE clause?
In outer joins, the ON clause determines matching and the WHERE clause determines filtering.
When a query doesn't have an ORDER BY clause, what is the order in which the rows are returned?
It is arbitrary. There is nothing to determine the order.
What is true about the result of a query without an ORDER BY clause?
It is relational as long as other relational requirements are met.
You want rows to be sorted by orderdate descending and then by orderid descending, what clause would you use?
ORDER BY orderdate DESC, orderid DESC;
You want rows to be sorted by orderdate ascending, and then orderid ascending, what clauses could you use?
ORDER BY orderdate, orderid ORDER BY orderdate ASC, orderid ORDER BY orderdate ASC, orderid ASC
Give me an example for an element from set theory that can improve your understanding of SQL.
One of the most typical mistakes that T-SQL developers make is to assume that a query without an ORDER BY clause always returns the data in a certain order. If you understand set theory, a set has no particular order to its elements, you know you shouldn't make that assumption. The only way to guarantee that the rows will be returned in a certain order is to add an ORDER BY clause.
What is the relationship between SQL and T-SQL?
SQL is the standard language and T-SQL is the dialect in MS SQL Server.
Why is it important to use standard SQL code when possible and know what is standard and what isn't?
Standard SQL code is more portable between platforms, and that makes your knowledge more portable.
What do the RANK and DENSE_RANK functions computer?
The RANK functions returns one more than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one more than the number of distinct ordering values that are lower than the current.
What happens when a scalar subquery returns more than one value?
The query fails at run time.
What is the difference between the simple CASE expression and the searched CASE expression?
The simple CASE expression compares an input expression to multiple possible expressions in the WHEN clauses, and the searched CASE expression uses independent predicates in the WHEN clauses.
Which syntax is recommended to use for cross joins and inner joins, and why?
The syntax with the JOIN keyword because it's consistent without join syntax and is less prone to errors.
What does the therm three-valued logic refer to in T-SQL?
The three possible logic result values of a predicate: true, false, and unknown.
You execute a query with a TOP (3) option. How many rows will be returned?
Three or fewer. If there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are three rows or more without TOP, the query will return three tows.
Turn this query into a CTE, then join the CTE to the Production>Products table to return per each category the products with the minimum unit price. SELECT categoryid, MIN(unitprice) AS mn FROM Production.Products GROUP BY categoryid
WITH CatMin AS ( SELECT categoryid, MIN(unitprice) AS mn FROM Production.Products GROUP BY categoryid ) SELECT P.categoryid, P.productid, P.productid, P.unitprice FROM Production.Products AS P INNER JOIN CatMin AS M ON P.categoryid = M.categoryid AND P.unitprice = M.mn
What is the mandatory clause in a SELECT query, according to T-SQL?
the SELECT clause