T Sql

Which answer is NOT a type of table index?

1.

nonclustered

2.

unique

3.

heap

4.

hash

Q 1 / 38

T Sql

The keywords AND, IN, LIKE, and between all belong to a category called what?

1.

joining operations

2.

linking operations

3.

criteria operations

4.

logical operations

Q 2 / 38

T Sql

What is the result of this series of statements?

BEGIN TRY SELECT 'Foo' AS Result; END TRY BEGIN CATCH SELECT 'Bar' AS Result; END CATCH

1.

Foo

2.

FooBar

3.

Foo Bar

4.

Bar

Q 3 / 38

T Sql

Given these two tables, which query generates a listing showing student names and the department office location where you could reach each student?

1.

SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students, Departments;

2.

SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students JOIN Departments ON Students.department = Departments.department;

3.

SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students JOIN Departments;

4.

SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students ON Students.department = Departments.department;

Q 4 / 38

T Sql

What is an example of a DDL command in SQL?

1.

TRUNCATE TABLE

2.

DELETE

3.

MERGE

4.

DROP

Q 5 / 38

T Sql

Given the Games table pictured, which query generates the results shown?

FROM Games       GROUP BY MaxPlayers, GameType       ORDER BY MaxPlayers, GameType;       FROM Games       GROUP BY GameType, MaxPlayers       ORDER BY GameType;       FROM Games       GROUP BY GameType, MaxPlayers       ORDER BY GameType;       FROM Games       GROUP BY GameType       ORDER BY MaxPlayers;

1.

SELECT GameType, MaxPlayers, count(*) AS NumberOfGames

2.

SELECT GameType, MaxPlayers, count(*) AS NumberOfGames

3.

SELECT GameType, count(Players) AS MaxPlayers, NumberOfGames

4.

SELECT GameType, MaxPlayers, count(*) AS NumberOfGames

Q 6 / 38

T Sql

Which answer is a possible result of the sequence of commands below?

DECLARE @UniqueID uniqueidentifier = NEWID(); SELECT @UniqueID AS Result;

1.

1

2.

bb261196-66a5-43af-815d-123fc593cf3a

3.

z350mpj1-62lx-40ww-9ho0-4u1875rt2mx4

4.

0x2400001155F04846674AD4590F832C0

Q 7 / 38

T Sql

You need to find all students that are not on the "Chemistry Cats" team. Which query does NOT work for this task?

WHERE team NOT 'Chemistry Cats';       WHERE team <> 'Chemistry Cats';       WHERE team != 'Chemistry Cats';       WHERE NOT team = 'Chemistry Cats';

1.

SELECT * FROM Students

2.

SELECT * FROM Students

3.

SELECT * FROM Students

4.

SELECT * FROM Students

Q 8 / 38

T Sql

You need to write a query that returns all Employees that have a LastName starting with the letter A. Which WHERE clause should you use to fill in the blank in this query?

1.

WHERE LastName = A*

2.

WHERE LastName = LIKE '%A%'

3.

WHERE LastName LIKE 'A%'

4.

WHERE LastName IN ('A*')

Q 9 / 38

T Sql

Which query shows the first name, department, and team of all students with the two lowest points?

`the picture of table is important here and there it can be seen that there are only two value with min points. Secondly, the previous answer was wrong because order by DESC will put highest points into the beginning of result list and TOP(2) will take first two highest points, and we need the lowest points.`

1.

SELECT LIMIT(2) first_name, department, team FROM Students ORDER BY points ASC;

2.

SELECT TOP(2) first_name, deprtment, team FROM Students ORDER BY points DESC;

3.

SELECT TOP(2) WITH TIES first_name, department, team FROM Students ORDER BY points;

4.

SELECT BOTTOM(2) first_name, department, team FROM Students ORDER BY points ASC;

Q 10 / 38

T Sql

What is the result of this statement?

`SELECT FLOOR(-1234.321)`

1.

-1234.3

2.

-1234

3.

-1235

4.

1234.321

Q 11 / 38

T Sql

Which is the best approach to update the last name of the student Donette Figgins to Smith

1.

UPDATE Students SET last_name = 'Smith' WHERE email = 'dfiggins@rouxacademy.com';

2.

UPDATE Students SET last_name = 'Figgins' WHERE email = 'dfiggins@rouxacademy.com';

3.

UPDATE Students SET last_name = 'Figgins' WHERE last_name = 'Smith' AND first-name = 'Donette';

4.

UPDATE Students SET last_name = 'Smith' WHERE last_name = 'Figgins' AND first-name = 'Donette';

Q 12 / 38

T Sql

Which of these data types is an approximate numeric?

1.

real

2.

bit

3.

decimal

4.

numeric

Q 13 / 38

T Sql

You need to remove all data from a table name Products. Which query fully logs the removal of each record?

1.

TRUNCATE FROM Products *;

2.

DELETE FROM Products;

3.

DELETE * FROM Products;

4.

TRUNCATE TABLE Products;

Q 14 / 38

T Sql

What is the result of the following query? SELECT 1 / 2 AS Result;

1.

0.5

2.

error

3.

0

4.

2

Q 15 / 38

T Sql

which data type will most efficiently store a person's age in years?

1.

float

2.

int

3.

tinyint

4.

bigint

Q 16 / 38

T Sql

What is the result of this query?

SELECT 'abc def' AS Result;

1.

abcdef

2.

abcdef

3.

error

4.

abc def

Q 17 / 38

T Sql

To select a random student from the table, which statement could you use?

1.

SELECT TOP(1) first_name, last_name FROM Students ORDER BY NEWID();

2.

SELECT TOP(1) RAND(first_name, last_name) FROM Student;

3.

SELECT TOP(1) first_name, last_name FROM Student;

4.

SELECT TOP(1) first_name, last_name FROM RAND(Student);

Q 18 / 38

T Sql

What result is returned after executing the following commands?

DECLARE @MyVariable int; SET @MyVariable = 1; GO SELECT @MyVariable;

1.

error

2.

1

3.

null

4.

@MyVariable

Q 19 / 38

T Sql

Which statement creates a new database schema named Sales and establish Sharon as the owner?

1.

ALTER USER Sharon WITH DEFAULT_SCHEMA = Sales;

2.

ALTER USER Sharon SET SCHEMA Sales;

3.

CREATE SCHEMA Sales SET OWNER Sharon;

4.

CREATE SCHEMA Sales AUTHORIZATION Sharon;

Q 20 / 38

T Sql

The result of a CROSS JOIN between a table with 4 rows, and one with 5 rows, will give with _ rows.

1.

1024

2.

20

3.

0

4.

9

Q 21 / 38

T Sql

You need to write a query that returns all products that have a SerialNumber ending with "10_3". Which WHERE clause should you use to fill in the blank in this query?

SELECT ProductID, ProductName, SerialNumber     FROM Products______ ; `The underscore will match any single character so you must bracket it to match a literal _, otherwise you could potentially return a serial number ending in '1013', for example.`

1.

`WHERE SerialNumer LIKE '%10_3'`

2.

`WHERE SerialNumer LIKE ('%10'+'_'+'3')`

3.

`WHERE SerialNumer LIKE '%10"_"3'`

4.

`WHERE SerialNumer LIKE '%10[_]3'`

Q 22 / 38

T Sql

When no join type between multiple tables in a query's FROM clause is specified, what type of join is assumed?

1.

INNER

2.

RIGHT

3.

LEFT

4.

FULL

Q 23 / 38

T Sql

How many bytes of storage does the int data type consume?

1.

1 byte

2.

2 bytes

3.

4 bytes

4.

8 bytes

Q 24 / 38

T Sql

What does a RIGHT JOIN ensure?

1.

that only records from the rightmost table will be displayed

2.

that no records from the rightmost table are displayed if the records dont have corresponding records in the left table

3.

that records from the rightmost table will be displayed only if the records have a corresponding value in the leftmost table

4.

that all records from the rightmost table are represented in the result, even if there are no corresponding records in the left table

Q 25 / 38

T Sql

You execute the following three queries. What is the result?

Create table students(id int identity(1000,1), firstname varchar(20),     lastname varchar(30));     insert into students(firstname,lastname)values('mark','twain');     select * from students;   studentid firstname lastname   1 1001 mark twain   studentid firstname lastname   1 1 mark twain       1 1000 mark twain   studentid firstname lastname   1 null mark twain

1.

undefined

2.

undefined

3.

undefined

Q 26 / 38

T Sql

Which Query returns all student names with the highest grade?

`create table students( studentname varchar(50), grade int);` `top(1) with ties will take the highest grade and all other students with the same grade (because they are order by grade) and matches the highest grade.`

1.

select studentname from students where grade=max(grade);

2.

select top(1) studentname from students order by grade;

3.

select top(1) with ties studentname from students order by grade desc;

4.

select studentname,max(grade) from students order by grade desc;

Q 27 / 38

T Sql

What role does "inventory" play?

`select bookid, boooktitle, bookauthor,quantityonhand from inventory.books;` `select * from dbo.books here dbo is a schema and the inventory is also schema; if we'd like to specify a database we should use db_name.schema_name.table_name`

1.

you only want to see results from books currently in inventory

2.

it instructs the query engine to find the books table in the inventory schema

3.

it instructs the query engine to find the books table in the inventory database

4.

it instructs the query engine to join the books table to the inventory schema

Q 28 / 38

T Sql

What is the result of an INNER JOIN between table1 and table2?

1.

Only records that have corresponding entries in table1 and table2 are displayed.

2.

No records from table1 are ever displayed.

3.

All records from table1 are displayed, regardless of whether the records have a corresponding row in table2

4.

Only records that have no corresponding records in table1 or table2 are displayed.

Q 29 / 38

T Sql

To remove all of the content from the Students table but keep the schema, which statement should you use?

1.

TRUNCATE TABLE Students;

2.

TRUNCATE * FROM Students;

3.

DROP TABLE Students;

4.

REMOVE * FROM Students;

Q 30 / 38

T Sql

Review the CREATE TABLE statement below. Which option, when placed in the blank space, ensures that the BookISBN column will not contain any duplicate values?

CREATE TABLE Books (     BookID int PRIMARY KEY,     BookISBN char(13) NOT NULL _____,     BookTitle nvarchar(100) NOT NULL );

1.

NO DUPLICATES

2.

UNIQUE CONSTRAINT AK_Books_BookISBN

3.

DUPLICATE CONSTRAINT (AK_Books_BookISBN)

4.

CONSTRAINT AK_Books_BookISBN UNIQUE

Q 31 / 38

T Sql

Given a table with the following structure, which query will not return the lowest grade earned by any student?

CREATE TABLE Students (     StudentName varchar(50),     Grade int );   SELECT StudentName FROM Students WHERE Grade = (SELECT MIN(Grade) FROM Student);   SELECT TOP(1) Grade FROM Students ORDER BY Grade;   SELECT MIN(Grade) FROM Students ORDER BY Grade;   SELECT MIN(Grade) FROM Students  **Explanation:** `Column "Students.Grade" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.`

1.


SELECT StudentName
FROM Students
WHERE Grade = (SELECT MIN(Grade) FROM Student);

2.


SELECT TOP(1) Grade
FROM Students
ORDER BY Grade;

3.


SELECT MIN(Grade)
FROM Students
ORDER BY Grade;

4.


SELECT MIN(Grade)
FROM Students

Q 32 / 38

T Sql

Given a table with the following structure, which query will not return the lowest grade earned by any student?

![T-SQL-Q33](images/Q33.jpg)

1.

UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id='56295';

2.

UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com' WHERE id='56295';

3.

UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com' WHERE id=56295;

4.

UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id=56295;

Q 33 / 38

T Sql

You would like to have a record added to a TableB every time a record is modified in TableA. What technique should you look at implementing?

1.

You should create a DML trigger on the server.

2.

You should create a DDL trigger on the database.

3.

You should create a DML trigger on TableA.

4.

You should create a DML trigger on TableB.

Q 34 / 38

T Sql

What is the problem with this code?

DECLARE @Counter int; SET @Counter = 1; WHILE @Counter > 0 BEGIN SET @Counter = @Counter +1; END;

1.

There is no END WHILE statement;

2.

The local varaible is not available to the WHILE block.

3.

The query causes an infinite loop.

4.

"Counter" is an invalid variable name.

Q 35 / 38

T Sql

Which is the right query to change the name of the Philosophy Pandas team to the Philosophy Parrots?

![T-SQL-Q36](images/Q36.jpg)

1.

UPDATES Students SET team = 'Philosophy Parrots' WHERE team = 'Philosophy Pandas';

2.

UPDATES Students SET team = `Philosophy Parrots` WHERE team = `Philosophy Pandas`;

3.

UPDATES Students SET team = "Philosophy Parrots" WHERE team = "Philosophy Pandas";

4.

UPDATES Students SET team = Philosophy Parrots WHERE team = Philosophy Pandas;

Q 36 / 38

T Sql

What is the result of this query?

`SELECT 123+'123' AS Result;`

1.

error

2.

'123''123'

3.

123123

4.

246

Q 37 / 38

T Sql

To combine the results of two or more SELECT statements, removing duplicates, which keyword can you use?

1.

DEDUPE

2.

SELECT

3.

MERGE

4.

UNION

Q 38 / 38