IT6 MOCK

Question 1

You are writing a SELECT statement to find every product whose name contains a specific character.

Which keyword should you use in your WHERE clause?

Answer 1

LIKE

Question 2

You need to populate a table named EmployeeCopy with data from an existing table named Employee.

Which statement should you use?

Answer 2

INSERT INTO EmployeeCopy
SELECT *
FROM Employee

Question 3

Another developer is trying to add a column named Prefix in a table by using the following query:

ALTER TABLE Person ADD Prefix varchar(4) NOT NULL;

The developer receives an error message after running the query. The developer is unable to remember the exact error message. What is likely the cause of this problem.

Answer 3

You should run the DROP CONSTRAINT query before running the ALTER TABLE query

Question 4

You need to normalize a database to first normal form. Which two requirements must you meet?

Answer 4

*Exclude repeating groups & *Exclude duplicate rows

Question 5

Which statement creates an index?

Answer 5

CREATE TABLE Employee
(EmployeeID INTEGER PRIMARY KEY)

Question 6

You have a Customer table that stores information about customers and Order table that stores information about orders placed. Each table contain CustomerID column. You join the two tables by using the CustomerID column in each table.

You run a query that selects the following data:
-All customers and their orders
-Customers who have no orders

Which type of join do these results represent?

Answer 6

Full Join

Question 7

Which SQL statement is a data manipulation language (DML) statement?

Answer 7

SELECT EmployeeName FROM Employee
WHERE EmployeeName = ‘Jack Smith’;

Question 8

You have a table named Product. You create a view that includes all the products from the Product table that are in the Furniture category. You execute a statement on the Product table that deletes all the products in the Furniture category. After you execute the statement, the result set of the view is:

Answer 8

Empty

Question 9

You execute the following query:

SELECT EmployeeID, FirstName, DepartmentName

FROM Employee, Department

This type of operation is called a/an:

Answer 9

Cartesian Product

Question 10

Which SQL statement will return the country and number of orders in each country where the number of orders is less than 50?

orderID represents an individual order, Country represents the country, and Orders represent the number of orders.

Answer 10

SELECT COUNT (OrderID) , Country
FROM Orders
GROUP BY Country
HAVING COUNT (orderID) < 50

Question 11

Which keyword combines the results of two queries and return only rows that appear in both result sets?

Answer 11

INTERSECT

Question 12

Structuring a table in relational database. In each statement select TRUE or FALSE. Each item will receive partial credit for each correct selection.

Answer 12

Each value in a field in a table must be unique (FALSE)
Each row in a table must be unique (TRUE)
Each column name in a table must be unique (TRUE)

Question 13

You have a table named Product. The Product table has columns for ProductDescription and ProductCategory. You need to change the ProductCategory value for all the spoons in the Product table to 43. A ProductDescription of spoon indicates that the items is a spoon.
Which statement should you use?

Answer 13

UPDATE Product
SET ProductCategory = 43
WHERE ProductDescription = ‘spoon’

Question 14

You are creating a database object named Student to store the following data. Which syntax should you use to create the object?

Answer 14

CREATE TABLE Student (
ID INT,
Name VARCHAR(100),
Age INT)

Question 15

The Products table contains the following data:
Which query will retrieve ItemName and Price when chocolate appears in the ItemDescription column?

Answer 15

SELECT ItemName, Price
FROM Products
WHERE ItemDescription LIKE ‘%chocolate%’;

Question 16

You need to disable User1’s access to view the data in the Customer table. Which statement should you use?

Answer 16

REVOKE SELECT ON Customer
FROM User1

Question 17

You have a Department table and an Employee table in your database. You need to ensure that an employee can be assigned to only an existing department. What should you apply to the Employee table?

Answer 17

A foreign key

Question 18

You accept an IT internship at a local charity. The charity asks you to keep a record of its volunteers by using a database table named Volunteer. The table has the following columns and rows. You must update the table when volunteers ask to be removed from mailing lists. You need to delete all records with the GivenName Tin. Which SQL statement should you use?

Answer 18

DELETE FROM Volunteer WHERE GivenName = ‘Tia’

Question 19

Which statement creates a composite key?

Answer 19

CREATE TABLE Order
(OrderID INTEGER,
OrderItemID INTEGER,
PRIMARY KEY (OrderID, OrderItemID) )

Question 20

Which statement should you use to remove a foreign key?

Answer 20

ALTER TABLE

Question 21

Which query will increase the price of item 1 by 6 percent?

Answer 21

UPDATE Products
SET Price = Price * 1.06
WHERE ItemNumber = 1;

Question 22

You have a table named Product that contains one million rows. You need to search for product information using the following query:

SELECT ProductName, Price FROM Product WHERE Category = ‘Science Books’
What will make this type of search more efficient?

Answer 22

A non-clustered index on the Category column

Question 23

You have a table named Employee that includes the following columns:

EmployeeID
EmployeeName

Which statement should you use to return the number of rows in the table?

Answer 23

SELECT COUNT (*)
FROM Employee

Question 24

One reason to create a stored procedure is to:

Answer 24

Improve performance

Question 25

You execute the following statement.
INSERT INTO Road VALUES (1234, 36)
What is the result?

Answer 25

A new row in the table

Question 26

You need to remove a view named EmployeeView from your database. Which statement should you use?

Answer 26

DROP VIEW EmployeeView

Question 27

Select TRUE If the statements is true, Otherwise, select FALSE.

Answer 27

A full database backup is a copy of all the data in the entire database (TRUE)

A transaction log backup backs all the data in the database (FALSE)

A differential backup copies only data that was changed before the last full backup (FALSE)

A file or filegroup restore specified a portion of the database to recover (TRUE)