11. Given the following table, which will result in an error?

My_Table
Name
Occupation
Age

A. SELECT NAME,OCCUPATION FROM MY_TABLE ORDER BY AGE
B. SELECT NAME,AGE ,OCCUPATION FROM MY_TABLE ORDER BY AGE
C. SELECT AGE,* FROM MY_TABLE ORDER BY AGE
D. SELECT * FROM MY_TABLE ORDER BY AGE, GROUP BY OCCUPATION

12. Consider the following query:

Payroll
Name
Salary
Date_Hired

SELECT NAME,
(CASE WHEN SALARY - 0
THEN 'INTERN'
ELSE 'EMPLOYEE'
END) AS TYPE,
DATE_HIRED
FROM PAYROLL

Which of the following statements is FALSE in regards to the query?

A. This query will return 3 columns.
B. This query will return a SALARY of 0 for each row that the TYPE = "INTERN".
C. This query will return a column that is not found in the PAYROLL table.
D. An alias is treated as a column in the query results.

13. Consider the following TSQL statement:

UPDATE EMPLOYEES
SET EMPLOYEES.SALARY = PAYROLL.SALARY
FROM PAYROLL, EMPLOYEES
WHERE EMPLOYEES.ID = PAYROLL.EMP_ID

Which of the following statements is TRUE in regards to the update statement?

A. This statement will result in an error.
B. This statement will update both the EMPLOYEES and PAYROLL tables.
C. This statement will never update any rows.
D. This statement will update the SQLARY column in the EMPLOYEES table.

14. Which of the following statements will create a variable in TSQL?

A. DECLARE @COUNT INTEGER
B. INT $COUNT
C. @@COUNT INTEGER
D. DEFINE @COUNT INT

15. Consider the following procedure:

CREATE PROCEDURE dbo.P_RETRIEVE_DEMO (@EMP_ID decimal(8,0))
AS
DECLARE @EMP_EXISTS integer
BEGIN
SELECT @EMP_EXISTS = (SELECT 1 FROM EMPLOYEES WHERE EMP_ID = @EMP_ID)

IF @EMP_EXISTS = 1 BEGIN
SELECT 'This employee is in the system.'
END
ELSE
BEGIN
SELECT 'This employee is not in the system.'
END
END
GO

Which one of the following statements is TRUE in regards to the procedure?

A. @EMP_ID is determined in a sub-query.
B. @EMP_EXISTS is a parameter passed into the procedure.
C. @EMP_EXISTS will contain the same number as @EMP_ID if the employee is in the system.
D. @EMP_ID is a parameter passed into the procedure.

16. Which of the following statements is FALSE when dropping a table using TSQL?

A. All indexes for the dropped table are marked invalid.
B. All procedures referencing the tables are dropped.
C. All synonyms for the dropped table are deleted.
D. The dropped table's table definition is dropped from the data dictionary.

17. Consider the following TSQL statement:

SELECT COUNT(*), EMPLOYEE_TYPE FROM EMPLOYEE GROUP BY EMPLOYEE_TYPE

This statement will return which of the following?

A. The total number of EMPLOYEE_TYPE values in the EMPLOYEE table.
B. The number of rows in the EMPLOYEE table for each EMPLOYEE_TYPE.
C. The number of rows in the EMPLOYEE table.
D. The total number of distinct values of EMPLOYEE_TYPE in the EMPLOYEE table.

18. Consider the following TSQL statement:

SELECT EMP_ID,SOC_NUM
INTO #MATCH_EMPS
FROM EMPLOYEES
WHERE TYPE = 'INTERN'

Which of the following statements is TRUE about #MATCH_EMPS?

A. It is a variable.
B. It is a parameter.
C. It is a temporary table.
D. It is a materialized view.

19. which TSQL command is used to iterate through each row in a cursor?

A. SELECT INTO
B. FETCH
C. FOREACH
D. UPDATE CURSOR ROW

20. Which of the following TSQL statements show the correct way to use concatenation in a SELECT clause?

A. SELECT F_NAME + ' ' + L_NAME FROM EMPLOYEES
B. SELECT F_NAME. ' ' . L_NAME FROM EMPLOYEES
C. SELECT F_NAME && ' ' && L_NAME FROM EMPLOYEES
D. SELECT F_NAME || ' ' || L_NAME FROM EMPLOYEES