Questions 30 - 36 from the one I had to take last week
30. Consider the following TSQL statement:
SELECT E.ID,
E.F_NAME,
E.L_NAME,
E.DATE_HIRED,
P.JOB,
P.EMP_ID
FROM EMPLOYEE E,
POSITION P
WHERE E.SALARY > 50000
AND E,ID = P.EMP_ID
AND P.JOB - "PROGRAMMER"
Which of the following indexes would provide the LEAST benefit in this query?
A. CREATE INDEX idx1 on EMPLOYEE(DATE_HIRED)
B. CREATE INDEX idx2 on EMPLOYEE(SALARY)
C. CREATE INDEX idx3 on POSITION(EMP_ID)
D. CREATE INDEX idx4 on POSITION(JOB)
31. When altering a column of a table in TSQL, which of the following statements is TRUE?
A. You cannot change a NOT NULL column to NULL if any rows contain null values in the column.
B. You cannot change a NULL column to NOT NULL if any rows contain null values in the column.
C. The altered column must retain the same data type.
D. Altered columns must be added to the end of the table.
32. Consider the following TSQL statement:
SELECT NAME, SALARY
INTO #EMP_SALARY_LIST
FROM EMPLOYEE
WHERE ID = 27
Which of the following statements is TRUE in regards to the statement?
A. The tables must have the same columns in order for this statement to execute without error.
B. Both tables must exist prior to running the statement.
C. One of these tables will be dropped when the user logs out.
D. As part of the copy process to the second table, all data is deleted from the first table.
33. The relational operator 'less then sign-greater than sign' (replace the words with the actual symbols) means which of the following?
A. less than or greater than
B. equal to
C. not equal to
D. less than or equal to
34. Consider the following TSQL statements:
CREATE TABLE TEsTER (
ID INT NOT NULL,
F_NAME varchar(30) NOT NULL,
L_NAME varchar(30) NOT NULL
)
CREATE TABLE TEsT (
TEST_ID INT NOT NULL,
TESTER_ID INT NOT NULL,
TEST_NAME VARCHAR(30) NOT NULL,
TEST_DATE DATETIME NULL
)
Which of the following queries will return all testers, even if they have not taken a test yet, and their corresponding test name?
A. SELECT l_name,f_name,test_name LEFT JOIN test ON TESTER.tester_id = TEST.tester_id
B. SELECT l_name,f_name,test_name FROM tester, test on tester.id = test.tester_id
C. SELECT l_name,f_name,test_name FROM tester, test WHERE test.tester_id =* tester.id
D. SELECT l_name,f_name,test_name FROM tester RIGHT JOIN test WHERE tester.id = test.tester_id
35. Consider the following TSQL syntax:
SELECT title_id, NULLIF(royalty,10) AS Amount FROM titles
What must royalty be to have null returned?
A. 5
B. 10
C. 25
D. NULL
36. An advantage of using an index is it ...
A. ...enables more efficient table reads and can improve query execution time.
B. ...increase the page length enabling more data returned in the same amount of time.
C. ...improves entropy of arrangement and sorting to allow for improved sorting algorithms.
D. ...assists in data aggregation which combines possibilities into queriable units.
SELECT E.ID,
E.F_NAME,
E.L_NAME,
E.DATE_HIRED,
P.JOB,
P.EMP_ID
FROM EMPLOYEE E,
POSITION P
WHERE E.SALARY > 50000
AND E,ID = P.EMP_ID
AND P.JOB - "PROGRAMMER"
Which of the following indexes would provide the LEAST benefit in this query?
A. CREATE INDEX idx1 on EMPLOYEE(DATE_HIRED)
B. CREATE INDEX idx2 on EMPLOYEE(SALARY)
C. CREATE INDEX idx3 on POSITION(EMP_ID)
D. CREATE INDEX idx4 on POSITION(JOB)
31. When altering a column of a table in TSQL, which of the following statements is TRUE?
A. You cannot change a NOT NULL column to NULL if any rows contain null values in the column.
B. You cannot change a NULL column to NOT NULL if any rows contain null values in the column.
C. The altered column must retain the same data type.
D. Altered columns must be added to the end of the table.
32. Consider the following TSQL statement:
SELECT NAME, SALARY
INTO #EMP_SALARY_LIST
FROM EMPLOYEE
WHERE ID = 27
Which of the following statements is TRUE in regards to the statement?
A. The tables must have the same columns in order for this statement to execute without error.
B. Both tables must exist prior to running the statement.
C. One of these tables will be dropped when the user logs out.
D. As part of the copy process to the second table, all data is deleted from the first table.
33. The relational operator 'less then sign-greater than sign' (replace the words with the actual symbols) means which of the following?
A. less than or greater than
B. equal to
C. not equal to
D. less than or equal to
34. Consider the following TSQL statements:
CREATE TABLE TEsTER (
ID INT NOT NULL,
F_NAME varchar(30) NOT NULL,
L_NAME varchar(30) NOT NULL
)
CREATE TABLE TEsT (
TEST_ID INT NOT NULL,
TESTER_ID INT NOT NULL,
TEST_NAME VARCHAR(30) NOT NULL,
TEST_DATE DATETIME NULL
)
Which of the following queries will return all testers, even if they have not taken a test yet, and their corresponding test name?
A. SELECT l_name,f_name,test_name LEFT JOIN test ON TESTER.tester_id = TEST.tester_id
B. SELECT l_name,f_name,test_name FROM tester, test on tester.id = test.tester_id
C. SELECT l_name,f_name,test_name FROM tester, test WHERE test.tester_id =* tester.id
D. SELECT l_name,f_name,test_name FROM tester RIGHT JOIN test WHERE tester.id = test.tester_id
35. Consider the following TSQL syntax:
SELECT title_id, NULLIF(royalty,10) AS Amount FROM titles
What must royalty be to have null returned?
A. 5
B. 10
C. 25
D. NULL
36. An advantage of using an index is it ...
A. ...enables more efficient table reads and can improve query execution time.
B. ...increase the page length enabling more data returned in the same amount of time.
C. ...improves entropy of arrangement and sorting to allow for improved sorting algorithms.
D. ...assists in data aggregation which combines possibilities into queriable units.