Deck 5: Transact:SQL Primer

Question 1

What type of functions are used to summarize data in a grouped column or for all records in a table?

A)Summary procedures
B)Aggregate functions
C)Grouping statements
D)Totaling processes

Answer 1

B

Question 2

If Col1 contains the string "Hello" and Col2 is Null, what is the result of SELECT Col1 + Col2?

A)"Hello"
B)Error
C)Null
D)"HelloNull"

Answer 2

C

Question 3

Which command returns the product of two columns returned in a column named ProdC1C2?

A)SELECT Col1 * Col2 AS ProdC1C2
B)SELECT AS ProdC1C2 Col1 x Col2
C)SELECT FROM Col1 * Col2 TOTAL ProdC1C2
D)SELECT (Col1, Col2)PRODUCT ProdC1C2

Answer 3

A

Question 4

Which command selects all columns in a table?

A)SELECT #
B)SELECT ;
C)SELECT *
D)SELECT @

Question 5

Which command changes the database context?

A)OPEN [Database Name]
B)USE [Database Name];
C)SELECT [Database Name];
D)SWITCH [Database Name]

Question 6

If you want to remove duplicates from a result set, which command should you use?

A)SELECT AS
B)SELECT UNIQUE
C)SELECT ONLY
D)SELECT DISTINCT

Question 7

Which command will result in records that have a Col1 value greater than 100?

A)SELECT Col1 FROM Table1 IF Col1 GT 100
B)SELECT Col1 FROM Table1 WHERE Col1 > 100
C)SELECT Col1 > 100 FROM Table1
D)IF Col1 > 100 SELECT * FROM Table1

Question 8

Which of the following represents correct syntax for a SELECT statement?

A)SELECT [Column Name] FROM [Table Name]
B)SELECT [Object Name] FOR [Column Name];
C)SELECT [Database Name] From [Column Name];
D)SELECT [Table Name] FROM [Row Name]

Question 9

A string concatenation operation evaluates to null if all of the input columns have a value.

Question 10

What keyword can you include in a SELECT statement to create a column alias for the column returned in the result set?

A)FROM
B)FOR
C)ALIAS
D)AS

Question 11

Which clause do you use with a join operation to specify which columns from each table should be used as the matching key?

A)FROM
B)ON
C)BY
D)WITH

Question 12

The database schema is defined by the DDL components in the SQL programming language.

Question 13

If you want to compare a value in a column against a list of values, which syntax would you use?

A)WHERE [Column Name] LIKE ([Value1], [Value2], [Value3])
B)WHERE [Column Name] = ([Value1], [Value2], [Value3])
C)WHERE [Column Name] IN ([Value1], [Value2], [Value3])
D)WHERE [Column Name] EXISTS ([Value1], [Value2], [Value3])

Question 14

Which of the following is true about the SQL language?

A)it is case sensitive
B)keywords must be written in uppercase
C)the semicolon is a statement terminator
D)statement terminators are required at the end of a statement

Question 15

Which syntax should you use to sort the result set in reverse alphabetic order.

A)ORDER ON [Column Name] Z-A
B)SORT REVERSE BY [Column Name]
C)SORT ON [Column Name] REVERSE
D)ORDER BY [Column Name] DESC

Question 16

A database operation that can be used to merge and retrieve data stored in more than one table view is called a SELECT.

Question 17

Transact-SQL is the variant of SQL developed by Microsoft and Sybase.

Question 18

SQL is an imperative programming language.

Question 19

Which of the following best describes the IntelliSense feature in Query Editor?

A)it is a database building wizard
B)it is a text AutoComplete feature
C)it is a visual query builder
D)it is used to create tables

Question 20

Which command returns all the columns for records in which the value of Col1 is greater than 10 but has a value less than Col2?

A)SELECT * FROM Table1 WHERE Col1 10
B)SELECT ALL FROM Table1 WHEN Col1 10
C)SELECT * FROM Table1 IF Col1 > 10 BUT Col1 < Col2
D)SELECT ALL FROM Table1 LIKE Col1 > 10 NOT Col1 < Col2

Question 21

Define the logical operators AND, OR, and NOT.

Question 22

Data ___________ language allows you to configure the logical security in the database.

Question 23

What are the keywords CHECK, UNIQUE, and NOT NULL referred to as?

A)limits
B)keys
C)constraints
D)references

Question 24

Use the ___________ statement along with ADD or DROP to modify or rename existing views and tables.

Question 25

How does the SQL language handle case and what are the recommendations on using case when constructing SQL statements?

Question 26

Use the _________ keyword to match a string based on a pattern.

Question 27

How do you combine data using joins?

Question 28

Describe the use of the SELECT statement.

Question 29

What are the key requirements to keep in mind when creating an INSERT statement?

Question 30

Which type of merge statement only combines rows that exist in two result sets?

A)UNION
B)EXCEPT
C)INTERSECT
D)JOIN

Question 31

How can you ease the managing of permissions across different objects?

Question 32

Which type of join returns all columns from both tables and only returns rows where the join column values are equal?

A)inner
B)left outer
C)right outer
D)full outer

Question 33

Describe why you would use the data control language component of the SQL programming language.

Question 34

What is the name for the type of query where a SQL query is embedded within another SQL query?

A)nested query
B)embedded query
C)subquery
D)inner query

Question 35

What is a qualified name?

Question 36

What is a null value and how does it affect arithmetic or string operations in SQL statements?

Question 37

Use the syntax SELECT ________ (expression)[Column Name] to limit the result set to the number of rows defined by the expression.

Question 38

Which statement is used to modify column values of an existing row in a table?

A)ALTER
B)CHANGE
C)MODIFY
D)UPDATE

Question 39

Use the syntax WHERE [Column Name] ______________ (SELECT * FROM [Table Name 2])when you want to compare a value of a column against the results of a subquery.

Question 40

What is a statement terminator and how are they used in the SQL language?