WorksheetsSQL MCQ
Total questions: 51
Worksheet time: 38mins
Which answer is NOT a type of table index?
nonclustered
unique
heap
hash
The keywords AND, IN, LIKE, and between all belong to a category called what?
joining operations
linking operations
criteria operations
logical operations
What is the result of this series of statements?
BEGIN TRY
SELECT 'Foo' ASResult;
END TRY
BEGIN CATCH
SELECT 'Bar' ASResult;
END CATCH
Foo
FooBar
Foo Bar
Bar
Given these two tables, which query generates a listing showing student names
and the department office location where you could reach each student?
SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students, Departments;
SELECT Students.first_name, Students.last_name, Departments.office_location
FROM Students JOIN Departments ON Students.department = Departments.department;
SELECT Students.first_name, Students.last_name, Departments.office_location
FROM Students JOIN Departments;
SELECT Students.first_name, Students.last_name, Departments.office_location
FROM Students ON Students.department = Departments.department;
Given the Games table pictured, which query generates the results shown?
SELECTGameType, MaxPlayers, count(*) ASNumberOfGames
FROMGames
GROUP BYMaxPlayers, GameType
ORDER BYMaxPlayers, GameType;
SELECTGameType, MaxPlayers, count(*) ASNumberOfGames
FROMGames
GROUP BYGameType, MaxPlayers
ORDER BYGameType;
SELECTGameType, count(Players) ASMaxPlayers, NumberOfGames
FROMGames
GROUP BYGameType, MaxPlayers
ORDER BYGameType;
SELECTGameType, MaxPlayers, count(*) ASNumberOfGames
FROMGames
GROUP BYGameType
ORDER BYMaxPlayers;
Which answer is a possible result of the sequence of commands below?
DECLARE@UniqueID uniqueidentifier = NEWID();
SELECT@UniqueID ASResult;
1
bb261196-66a5-43af-815d-123fc593cf3a
z350mpj1-62lx-40ww-9ho0-4u1875rt2mx4
0x2400001155F04846674AD4590F832C0
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';
You need to write a query that returns all Employees that have a LastName starting with the letter A. Which WHEREclause should you use to fill in the blank in this query?
WHERE LastName = A*
WHERE LastName = LIKE '%A%'
WHERE LastName LIKE 'A%'
WHERE LastName IN ('A*')
Which query shows the first name, department, and team of all students with the
two lowest points?
SELECT LIMIT(2) first_name, department, team FROM Students ORDER BY points ASC;
SELECT TOP(2) first_name, deprtment, team FROM Students ORDER BY points DESC;
SELECT TOP(2) WITH TIES first_name, department, team FROM Students ORDER BY
points;
SELECT BOTTOM(2) first_name, department, team FROM Students ORDER BY points ASC;
What is the result of this statement?
SELECT FLOOR(-1234.321)
-1234.3
-1234
-1235
1234.321
Which is the best approach to update the last name of the student Donette
Figgins to Smith
UPDATE Students SET last_name = 'Smith' WHERE email =
UPDATE Students SET last_name = 'Figgins' WHERE email =
UPDATE Students SET last_name = 'Figgins' WHERE last_name = 'Smith' AND firstname = 'Donette';
UPDATE Students SET last_name = 'Smith' WHERE last_name = 'Figgins' AND firstname = 'Donette';
Which of these data types is an approximate numeric?
real
bit
decimal
numeric
You need to remove all data from a table name Products. Which query fully logs
the removal of each record?
TRUNCATE FROM Products *;
DELETE FROM Products;
DELETE * FROM Products;
TRUNCATE TABLE Products;
What is the result of this query?
SELECT 1 / 2 ASResult;
0.5
error
0
2
which data type will most efficiently store a person's age in years?
float
int
tinyint
bigint
What is the result of this query?
SELECT 'abc\
def' ASResult;
abc\def
abcdef
error
abc def
To select a random student from the table, which statement could you use?
SELECT TOP(1) first_name, last_name FROM Students ORDER BY NEWID();
SELECT TOP(1) RAND(first_name, last_name) FROM Student;
SELECT TOP(1) first_name, last_name FROM Student;
SELECT TOP(1) first_name, last_name FROM RAND(Student);
What result is returned after executing the following commands?
DECLARE@MyVariable int;
SET@MyVariable = 1;
GO
SELECT@MyVariable;
error
1
null
@MyVariable
Which statement creates a new database schema named Sales and establish
Sharon as the owner?
ALTER USER Sharon WITH DEFAULT_SCHEMA = Sales;
ALTER USER Sharon SET SCHEMA Sales;
CREATE SCHEMA Sales SET OWNER Sharon;
CREATE SCHEMA Sales AUTHORIZATION Sharon;
The result of a CROSS JOINbetween a table with 4 rows, and one with 5 rows, will
give with _ rows.
1024
20
0
9
You need to write a query that returns all products that have a SerialNumber
ending with "10_3". Which WHEREclause should you use to fill in the blank in this
query?
SELECTProductID, ProductName, SerialNumber
FROMProducts______ ;
WHERE SerialNumber LIKE '%10_3'
WHERE SerialNumber LIKE ('%10'+'_'+'3')
WHERE SerialNumber LIKE '%10"_"3'
WHERE SerialNumber LIKE '%10[_]3'
When no join type between multiple tables in a query's FROMclause is specified,
what type of join is assumed?
INNER
RIGHT
LEFT
FULL
How many bytes of storage does the int data type consume?
1 byte
2 bytes
4 bytes
8 bytes
What does a RIGHT JOINensure?
that only records from the rightmost table will be displayed
that no records from the rightmost table are displayed if the records dont have corresponding records in the left table
that records from the rightmost table will be displayed only if the records have a
corresponding value in the leftmost table
that all records from the rightmost table are represented in the result, even if there are
no corresponding records in the left table
You execute the following three queries. What is the result?
Create tablestudents(id int identity(1000,1), firstname varchar(20),
lastname varchar(30));
insert intostudents(firstname,lastname)values('mark','twain');
select * fromstudents;
studentid firstname lastname 1 1001 mark twain
studentid firstname lastname 1 1 mark twain
studentid firstname lastname 1 1000 mark twain
studentid firstname lastname 1 null mark twain
Given a table with the following structure, which query returns all student names
with the highest grade?
CREATE TABLEStudents (
StudentName varchar(50),
Grade int);
SELECT StudentName FROM Students WHERE Grade = MAX(Grade);
SELECT TOP(1) StudentName FROM Students ORDER BY Grade;
SELECT TOP(1) WITH TIES StudentName FROM Students ORDER BY Grade DESC;
SELECT StudentName, MAX(Grade) FROM Students ORDER BY Grade DESC;
What role does "inventory" play?
selectbookid, boooktitle, bookauthor,quantityonhand from inventory.books;
you only want to see results from books currently in inventory
it instructs the query engine to find the books table in the inventory schema
it instructs the query engine to find the books table in the inventory database
it instructs the query engine to join the books table to the inventory schema
What is the result of an INNER JOINbetween table1 and table2?
Only records that have corresponding entries in table1 and table2 are displayed
No records from table1 are ever displayed
All records from table1 are displayed, regardless of whether the records have a
corresponding row in table2
Only records that have no corresponding records in table1 or table2 are displayed
To remove all of the content from the Students table but keep the schema, which
statement should you use?
TRUNCATE TABLE Students;
TRUNCATE * FROM Students;
DROP TABLE Students;
REMOVE * FROM Students;
Review the CREATE TABLEstatement below. Which option, when placed in the
blank space, ensures that the BookISBN column will not contain any duplicate values?
CREATE TABLEBooks (
BookID int PRIMARY KEY,
BookISBN char(13) NOT NULL_____,
BookTitle nvarchar(100) NOT NULL
);
NO DUPLICATES
UNIQUE CONSTRAINT AK_Books_BookISBN
DUPLICATE CONSTRAINT (AK_Books_BookISBN)
CONSTRAINT AK_Books_BookISBN UNIQUE
Given a table with the following structure, which query will not return the lowest
grade earned by any student?
CREATE TABLEStudents (
StudentName varchar(50),
Grade int
);
SELECTStudentName
FROMStudents
WHEREGrade =(SELECT MIN(Grade) FROMStudent);
SELECT TOP(1) Grade
FROMStudents
ORDER BYGrade;
SELECT MIN(Grade)
FROMStudents
SELECT MIN(Grade)
FROMStudents
ORDER BYGrade;
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?
You should create a DML trigger on the server.
You should create a DDL trigger on the database.
You should create a DML trigger on TableA.
You should create a DML trigger on TableB.
What is the problem with this code?
DECLARE@Counter int;
SET@Counter = 1;
WHILE@Counter > 0
BEGIN
SET@Counter =@Counter +1;
END;
There is no END WHILE statement;
The local varaible is not available to the WHILE block.
The query causes an infinite loop.
"Counter" is an invalid variable name.
What is the result of this query?
SELECT 123+'123' ASResult;
error
'123''123'
123123
246
To combine the results of two or more SELECTstatements, removing duplicates,
which keyword can you use?
DEDUPE
SELECT
MERGE
UNION
You run this series of statements. What is the final result?
CREATE TABLEMyTable (MyValue int);
INSERT INTOMyTable VALUES(1);
WHILE(SELECTMyValue FROMMyTable) < 5
BEGIN
UPDATEMy Table SETMyValue =MyValue + 1;
END;
SELECTMyValue ASResult FROMMyTable;
5
error
1
6
Is there an error with this query? If so, which statement best describes the
problem?
SELECTOrderID, SUM(LineTotal) ASSubTotal
FROMSales
WHERE SUM(LineTotal) > 1000
GROUP BYOrderID
ORDER BYOrderID;
Yes, a WHEREclause cannot be used with an aggregate function.
Yes, you cannot GROUP BYand ORDER BYthe same field.
No, there is nothing wrong with this query.
Yes, the WHEREclause should use the SubTotalalias.
You created the two tables below. Later, you decide that you want the database to
remove all books from the Books table if the related publisher is deleted from the
Publishers table. What command should you run?
CREATE TABLEBooks (
BookID int PRIMARY KEY,
BookTitle nvarchar(100) NOT NULL,
PublisherID int NOT NULL
);
CREATE TABLEPublishers (
PublisherID int PRIMARY KEY,
PublisherName nvarchar(50)
);
ALTER TABLEBooks
ADD CONSTRAINTFK Books_PublisherID
FOREIGN KEY(PublisherID)
REFERENCESPublishers (PublisherID) ON UPDATE SET NULL
ALTER TABLEBooks
ADD CONSTRAINTFK Books_PublisherID
FOREIGN KEY(PublisherID)
REFERENCESPublishers (PublisherID) ON DELETE CASCADE
ALTER TABLEBooks
ADD CONSTRAINTFK_Books_PublisherID
FOREIGN KEY(PublisherID)
REFERENCESPublishers (PublisherID)
ALTER TABLEPublishers
ADD CONSTRAINTFK_Publishers_PublisherID
FOREIGN KEY(PublisherID)
REFERENCESBooks (PublisherID) CASCADE DELETE
Your database currently has a table called Inventory in the Warehouse schema.
You need to move the table to the Products schema. Which query accomplishes this
goal?
ALTER SCHEMA Products TRANSFER Warehouse.Inventory;
ALTER TABLE Warehouse.Inventory TRANSFER Products.Inventory;
ALTER TABLE Warehouse.Inventory ADD SCHEMA Products;
ALTER SCHEMA Warehouse ADD TABLE Inventory;
Which option—when placed in the blank space—establishes the PersonlD column
as the primary key for the table with a nonclustered index?
CREATE TABLE People(
PersonID int NOT NULL,
PersonName nvarchar(50),
_______
);
INDEX ON PersonID (PRIMARY KEY PK_People)
ADD NONCLUSTERED PRIMARY KEY CONSTRAINT PK_People ON PersonID
CONSTRAINT PK_People PRIMARY KEY NONCLUSTERED (PersonID)
PRIMARY KEY CONSTRAINT (PersonID) NONCLUSTERED INDEX
You need to create a simple database backup in the server's Z:\Backups
directory. Which query should you use?
BACKUP MyDatabase TO LOCATION = 'Z:\Backups\MyDatabase.bak';
CREATE BACKUP (DATABASE = 'MyDatabase' TO DISK = 'Z:\Backups\MyDatabase. bak');
BACKUP DATABASE MyDatabase ON 'Z:\Backups\MyDatabase.bak';
BACKUP DATABASE MyDatabase TO DISK = 'z:\Backups\MyDatabase.bak';
Suppose you want to have the name of a transaction called myTransaction
recorded in the transaction log. Which statement represents the best way to
accomplish this?
BEGIN TRAN myTransaction BEGIN LOG;
BEGIN TRAN myTransaction WITH LOG;
BEGIN TRAN myTransaction WITH MARK;
BEGIN TRAN WITH MARK myTransaction;
Though not currently a requirement, what will a future release of SQL Server
require of all SQL statements?Though not currently a requirement, what will a future
release of SQL Server require of all SQL statements?
All statements must end with a semicolon.
All statements must operate on a table of data.
All statements must always be written in uppercase letters.
All statements must include more than one variable.
Which is the best approach to update the last name and email address of a
student with ID 56295?
UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE
id='56295';
UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE
id=56295;
UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com'
WHERE id=56295;
UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com'
WHERE id='56295';
What is the result of this query?
SELECT 123+'abc' ASResult;
123abc
123'abc'
'123abc'
error
What output will the following SQL sequence produce? Assume that the tables
have been created and all the columns exist.
INSERT INTOAccount (acct,bal) VALUES('12345', 100);
UPDATEAccount SETbal=bal+100;
BEGIN;
UPDATEAccount SETbal=bal+100.
ROLLBACK;
SELECTbal FROMAccount WHEREacct='12345';
);
100
200
300
You will get an error because ROLLBACK deletes the row that was update
Which statement deletes a table named Inventory from the Products database?
DROP TABLE Products.Inventory;
USE Products;
DROP TABLE Inventory;
USE Products;
DELETE Inventory;
USE Products.Inventory;
DROP TABLE Inventory;
In a SELECT statement, which clause should always be used with the TOP clause in
order to predictably indicate which rows are affected by TOP?
GROUP BY
HAVING
WHERE
ORDER BY
Which data type should you choose when you nedd to store dates and times that
include time zone information?
datetimeoffset
smalldatetime
datetime
datetime2
What is the result of this query?
SELECT 123+'123' ASResult;
123'123'
error
246
123123
What is an example of a DDL command in SQL ?
merge
drop
delete
truncate table
