+1

Question 1

The ItemsOnOrder table contains the following data.

You need to create a query that display the total number of orders, the average line item total, the highest line item
total, and the grand total of all items ordered. LineItemTotal represents the line item total.

Which query should you use?

Accepted Answer

SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal)
FROM ItemOnOrder

Answer

SELECT COUNT (ID), AVG(LineItemTotal), MAX(LineItemTotal),
SUM(LineItemTotal)
FROM ItemsOnOrder
HAVING ItemNumber, Quantity, UnitPrice

Answer

SELECT COUNT (ID), AVG(Unitprice+Quantity), MAX(UnitPrice+Quantity),
SUM(UnitPrice+Quantity)
FROM ItemOnOrder
GROUP By ItemNumber, LineItemTotal

Answer

SELECT SUM(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal)
FROM ItemOnOrder

Question 2

You create the following query to determine whether Sample Movie appears only once in the Movie table.
SELECT Title
FROM Movie
WHERE Title = ‘Sample Movie’
ORDER BY Title
GROUP BY Title
HAVING COUNT(*) = 1

When you run this Query, if return a syntax error
You need to modify the query to run without error and return accurate result
What should you do?

Accepted Answer

Change the having clause to having count(title) = 1

Answer

Remove the group by clause

Answer

Remove the order by clause

Answer

Change the having clause to having count(1) = 1

Question 3

What would you use to combine columns from two different tables?

Accepted Answer

JOIN

Answer

AND

Answer

BETWEEN

Answer

COMBINE

Question 4

You’re the database admin for a university that stores student information in a table named student. This table has
a foreign key named courseID that links to the primary key in a table named courses.
You need to return the student name and course name using both tables where there is a match on courseID.
You execute the following query:
SELECT students.name, courses.name FROM students
OUTER JOIN courses WHERE student.courseID = courses.courseID

The query returns an error.
How should you correct the query?

Accepted Answer

SELECT students.name, courses.name FROM students
INNER JOIN courses ON students.courseID = courses.coursesID

Answer

SELECT students.name, courses.name FROM students
INNER JOIN courses WHERE students.courseID = courses.courseID

Answer

SELECT students.name,courses.name FROM students
INNER JOIN ON students.courseID = courses.coursesID

Answer

SELECT students.name, courses.name FROM students
INNER JOIN On courses
WHERE students.courseID = courses.courseID

Question 5

You have two tables named Cars and Color as defined below. The two tables are related by ColorId.

How many rows does the SQL statement return?

Accepted Answer

3

Answer

0

Answer

2

Answer

6

Question 6

You have a Customer table and an Order table. You join the Customer table with the Order table by using the
CusromerID column.
The results include:
All customers and their orders
Customers who have no orders

Which type of join do these results represent?

Accepted Answer

Outer join / Full join

Answer

Complete join

Answer

Partial join

Answer

Inner join

Question 7

Which clause used with the SELECT command will return values that you want to exclude from another query's
result set?

Accepted Answer

EXCEPT

Answer

INTERSECT

Answer

SEARCH

Answer

EXCLUDE

Question 8

You execute the following statement:
SELECT DepartmentName FROM Department
WHERE DepartmentID =
 (SELECT DepartmentID
 FROM Employee
 WHERE EmployeeID = 1234)
This statement is an example of a/an:

Accepted Answer

Subquery

Answer

Union

Answer

Outer join

Answer

Cartesian product

Question 9

You have a table that contains the following data
This process is referred to as:

Accepted Answer

normalization

Answer

defragmentation

Answer

fragmentation

Answer

denormalization

Question 10

You need to normalized a database to first normal form.
Which two requirement must you meet? (Choose 2)

Accepted Answer

Exclude repeating groups

Accepted Answer

Exclude duplicate rows

Answer

Exclude foreign key

Answer

Exclude composite key

Question 11

You work at a restaurant and they ask you to help them with a data issue.
They provide you with the following recipe data:

You need to normalize the data to third normal form.
How many tables should you create?

Accepted Answer

3

Answer

1

Answer

2

Answer

4

Question 12

You create the following table, which displays winners of past pet shows.
Pet Competition Winners
Which column prevents the table from being in third normal form?

Accepted Answer

Birthdate

Answer

PetID

Answer

ID

Answer

Year

Question 13

Denormalization is performed in order to:

Accepted Answer

Improve query performance.

Answer

Reduce redundancy.

Answer

Eliminate repeating groups

Answer

Create smaller tables.

Question 14

This question requires that you evaluate the underlined text to determine if it is correct.
You combine data from three tables into one table. The new table includes redundancy to optimize read
performance.The data in the new table has been denormalized.
Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the
statement is incorrect, select the answer choice that makes the statement correct.

Accepted Answer

No change is needed

Answer

Normalized

Answer

Truncated

Answer

Indexed

Question 15

This question requires that you evaluate the underlined text to determine if it is correct.
Use the ALTER statement to add a new table in a database.
Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the
statement is incorrect, select the answer choice that makes the statement correct.

Accepted Answer

CREATE

Answer

No change is needed

Answer

UPDATE

Answer

INSERT

Question 16

You are developing SQL statement to create a table.
Which two SQL keywords are valid to use in a create table statement? (Choose 2)

Accepted Answer

Primary Key

Accepted Answer

Constraint

Answer

Order By

Answer

Insert Into

Question 17

Which keyword can be used in a create table statement?

Accepted Answer

UNIQUE

Answer

ORDER BY

Answer

DISTINCT

Answer

GROUP BY

Question 18

Which command should you use to add a column to an existing table?

Accepted Answer

ALTER

Answer

MODIFY

Answer

UPDATE

Answer

INSERT

Answer

CHANGE

Question 19

You need to rename a column in a database table. Which DDL statement should you use?

Accepted Answer

ALTER

Answer

INSERT

Answer

CREATE

Answer

UPDATE

Question 20

Which statement should you use to remove a foreign key?

Accepted Answer

ALTER TABLE

Answer

DELETE TABLE

Answer

DELETE FOREIGN KEY

Answer

ALTER FOREIGN KEY

Question 21

A developer adds a BirthDate column with NOT NULL but gets an error:
ALTER TABLE Person ADD BirthDate date NOT NULL;
What caused it, and how can it be fixed? Select the correct SQL statement.

Accepted Answer

ALTER TABLE Person 
ADD BirthDate date DEFAULT '1900-01-01' NOT NULL;

Answer

ALTER TABLE Person
ADD BirthDate varchar(10) NOT NULL;

Answer

ALTER TABLE Person
ADD BirthDate int NOT NULL;

Answer

ALTER TABLE Person
ADD BirthDate date NULL;

Question 22

Your company stores customer social security numbers in a column named SSN in a table
named customer. New compliance laws prohibit your companyfrom storing this information.
Running the query below causes an error to occur.

ALTER TABLE Customers
REMOVE SSN;

What changes are needed to the query above so that it removes the SSN column from the customers table ?

Accepted Answer

ALTER TABLE Customers DROP COLUMN SSN;

Answer

ALTER TABLE Customers DELETE SSN;

Answer

ALTER TABLE Customers DELETE COLUMN SSN;

Answer

ALTER TABLE Customers DROP SSN;

Question 23

This question requires that you evaluate the underlined text to determine if it is correct.
The CREATE TABLE command removes one or more table definitions and all data, indexes, triggers,
constraints, and permission specifications for those tables.
Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the
statement is incorrect, select the answer choice that makes the statement correct

Accepted Answer

DROP TABLE

Answer

No change is needed

Answer

TRUNCATE TABLE

Answer

ALTER TABLE

Question 24

What statement would you use to delete the Equipment table?

Accepted Answer

DROP TABLE Equipment

Answer

DELETE TABLE Equipment

Answer

REMOVE TABLE Equipment

Answer

TRUNCATE TABLE Equipment

Question 25

This question requires that you evaluate the underlined text to determine if it is correct.
DELETE TABLE removes all rows from a table without logging the individual row deletions.
Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the
statement is incorrect, select the answer choice that makes the statement correct.

Accepted Answer

TRUNCATE TABLE

Answer

No change is needed

Answer

DROP TABLE

Answer

REMOVE TABLE

Answer

CLEAR TABLE

Question 26

Which of the following is the language element allowing you to use statements, such as INSERT, UPDATE and
DELETE to manipulate the data in any SQL Server table?

Accepted Answer

DML

Answer

DDE

Answer

DDL

Answer

DAT

Question 27

What are three valid data manipulation language (DML) commands? (Choose three.)

Accepted Answer

INSERT

Accepted Answer

DELETE

Accepted Answer

UPDATE

Answer

COMMIT

Answer

OUTPUT

Question 28

In SQL, an insert statement is used to add a:

Accepted Answer

Row of data to a table.

Answer

User to a database

Answer

Table to a database

Answer

Column to a table definition.

Question 29

On which database structure does an insert statement operate?

Accepted Answer

Table

Answer

Role

Answer

Trigger

Answer

User

Answer

Stored procedure

Question 30

You need to add rows to a database table.
Which Structured Query Language (SQL) keyword should you use?

Accepted Answer

INSERT

Answer

JOIN

Answer

CREATE

Answer

UPDATE

Question 31

You have the following table definition:
What is the result?

Accepted Answer

a new row in the table

Answer

an error stating that NULL values are not allowed

Answer

an error stating that duplicate IDs are not allowed

Answer

a syntax error

Question 32

You have the following table definition:
CREATE TABLE Product
(ProductID INTEGER,
Name VARCHAR(20))

You need to insert a new product. The product's name is Plate and the product's ID is 12345.
Which statement should you use?

Accepted Answer

INSERT INTO Product (ProductID, Name)
 VALUES (12345, 'Plate')

Answer

INSERT INTO Product
VALUES (ProductID= 12345, Name = 'Plate')

Answer

INSERT NEW ProductID = 12345, Name = 'Plate'
INTO Product

Answer

INSERT 12345, 'Plate'
INTO Product

Question 33

You need to insert two new products into the Product table. The first product is named Book and has an ID of
125. The second product is named Movie and has an ID of 126.
Which statement should you use?

Accepted Answer

INSERT INTO Product (ID, Name)
 VALUES (125, 'Book')
 INSERT INTO Product (ID, Name)
 VALUES (126, 'Movie')

Answer

INSERT INTO Product
 VALUES (ID= 125, 126) (Name ='Book', 'Movie')

Answer

INSERT 125, 126, 'Book', 'Movie'
 INTO

Answer

INSERT NEW ID = 125 AND 126, Name = 'Book' AND 'Movie'
 INTO P

Answer

NSERT NEW ID = 125, Name = = 'Book'
 INTO Product
 INSERT NEW ID = 126, Name = 'Movie'
 INTO Pro

Question 34

You need to populate a table named EmployeeCopy with data from an existing table named Employee.
Which statement should you use?

Accepted Answer

INSERT INTO EmployeeCopy 
SELECT * FROM Employee

Answer

SELECT * INTO EmployeeCopy
SELECT * FROM Employee

Answer

INSERT *
FROM Employee
INTO EmployeeCopy

Answer

COPY INTO Employee
SELECT *
FROM Employee

Question 35

On which database structure does an update statement operate?

Accepted Answer

Table

Answer

User

Answer

Trigger

Answer

Role

+1

Create a free account to finish this quiz

Questions in this quiz (35)

Information

Feedback

What do you think of the new look?