21. Consider the following TSQL statement:

Promotions
ID int not null,
Emp_ID int not null,
Promotion_Date datetime not null

Employees
ID int not null,
F_Name varchar(50) not null,
L_Name varchar(50) not null

Select E.ID as EMP_ID,E.F_Name,E.L_Name,P.Promotion_Date
FROM Employees E left join Promotions P
on E.ID = P.Emp_ID
WHERE P.Promotion_Date is null
GROUP BY E.ID,E.F_Name,E.L_Name,P.Promotion_Date

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

A. This query will return all employees that were promoted.
B. This query will return employees that are not in the promotions table.
C. This query will return all the employees that have a promotion date.
D. The query will return an error.

22. Consider the following TSQL statement:

Employees
ID int not null,
F_Name varchar(50) not null,
L_Name varchar(100) not null

DECLARE @ColumnName varchar(255)
SET @ColumnName = ''

SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME)
FROM Information_Schema.Columns
WHERE Table_Name = 'Employees'
AND Date_Type IN ('char','varchar','nchar','nvarchar')
)

What will @ColumnName return?

A. It will return ID.
B. It will return F_Name.
C. It will return L_Name.
D. This query will return an error.

23. Consider the following TSQL statement:

SELECT ID, AVG(SALARY) AS 'AVG',sum(SALARY) AS 'SUM', F_NAME AS 'FIRSTNAME',L_NAME
FROM EMPLOYEES
GROUP BY ID
HAVING L_NAME IS NOT NULL
ORDER BY F_NAME,L_NAME

This query results in an error. Which of the following statements correctly identifies the reason why?

A. The ORDER By clause must use the aliases identified in the SELECT clause.
B. The HAVING clause must contain all aggregate columns.
C. The SELECT clause has incorrect use of aliases.
D. The GROUP BY clause must contain all non-aggregated columns.

24. Consider the following TSQL statement:

CREATE TABLE EMP (
ID int NOT NULL,
F_NAME varchar(30) NOT NULL,
L_NAME varchar(30) NOT NULL,
AGE int NULL,
GENDER varchar(1) NULL
)

Which of the following statements will correctly insert data into this table?

A. INSERT INTO EMP VALUES (100, 'chris','Brown',26,'MALE')
B. INSERT INTO EMP (AGE,GENDER,ID,F_NAME,L_NAME,) VALUES (26,'M',100,'Chris','Brown')
C. INSERT INTO EMP ('100','Chris','Brown', 26,'MALE')
D. INSERT INTO EMP (AGE,GENDER,ID,F_NAME,L_NAME,) VALUES (26,100,'Chris','Brown')

25. A TSQL Trigger is a...

A. ...field in a table that uniquely defines a row in the table.
B. ...rule set up on a column to restrict values being entered.
C. ...specialized stored procedure, bound to a table or view, which executes upon certain events.
D. ...data structure used to provide quick access to data in a database table or view.

26. Consider the following TSQL statement:

SELECT E.ID,
E.F_NAME,
E.L_NAME,
(SELECT TITLE
FROM POSITION P WHERE P.ID = E.ID) 'TITLE'
FROM EMPLOYEE E

This example shows which of the following structures?

A. a Nested Sub Query
B. a Correlated Sub Query
C. a Sub Query Aggregate
D. a Scalar Function

27. Consider the following tables:

Employee:
EMP_ID L_NAME JOB
1 Smith Programmer
2 Jones Manager
3 Mason Data Entry
4 Moen Analyst

Projects:
EMP_ID PROJ_NO
1 p_101
1 p_102
2 p_102
1 p_103
2 p_103
3 p_103

Then consider the following query:

SELECT L_NAME FROM EMPLOYEE
WHERE EMP_ID = (SELECT EMP_ID FROM PROJECTS WHERE PROJ_NO - 'p_102')

Which of the following values is returned by this query?

A. Smith
B. 1, 2
C. Smith, Jones
D. This query returns an error.

28. Consider the following TSQL statement:

WHILE (SELECT SUM(SALARY) FROM PROJECTS) 50000 BEGIN
BREAK
END
END

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

A. If a salary is increased to more than 50000, that salary is no longer increased, but all others will be until the sum of all or equal to 100000.salaries is greater than
B. If a salary is increased to more than 50000, no further salaries will be increased.
C. Salaries will continue to increase until the highest salary is greater than or equal to 100000.
D. Salaries will continue to increase until the sum of all salaries is greater than or equal to 50000.

29. Consider the following table:

Employee:
EMP_ID L_NAME JOB SALARY
1101 Smith Programmer 55000
2913 Jones Manager 76000
2450 Mason Data Entry 39000
4412 Moen Analyst 67000

What is the result of executing the following TSQL code:

CREATE PROCEDURE sp_GeT_Salary
@Emp_ID int,
@Salary money OUTPUT
AS
BEGIN
SELECT @Salary = Salary FROM Employee
WHERE Emp_ID = @Emp_ID

IF @@RowCount = 1 BEGIN
Return
END

Set @Salary = 0
Return 1
END
GO

DECLARE @Value1 money
DECLARE @Value2 money
EXECUTE @Value2 = sp_Get_Salary 2913, @Value1 OUTPUT
SELECT @Value2

A. 1
B. 0
C. 76000
D. 2913