Font size
WorksheetsDatabase Reviewer
Total questions: 59
Worksheet time: 50mins
You need to disable User1’s access to view the data in the Customer table. Which statement should you use?
REVOKE User1 FROM Customer
REMOVE User1 FROM Customer
REMOVE SELECT ON Customer FROM User1
REVOKE SELECT ON Customer FROM User1
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?
A data type
An index
A foreign key
A unique constraint
A primary key
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.
SELECT Country, orderID FROM Orders GROUP BY Country WHERE COUNT(orderID) < 50
SELECT COUNT (OrderID), Country FROM Orders GROUP BY Country HAVING COUNT (orderID) < 50
SELECT Country, orderID FROM Orders HAVING COUNT (orderID)
SELECT COUNT (OrderID), Country FROM Orders HAVING COUNT (orderID) < 50 GROUP BY Country
For each of the following statements, select True if the statement is true. Otherwise, select False. Each correct selection is worth a partial point. (True or False)
(a)
A transaction log backup backs up all the data in the database (True or False)
(a)
A differential backup copies only data that was changed before the last full backup (false or false)
(a)
A file or filegroup restore specified a portion of the database to recover (True or False)
(a)
You create a table names Games that contains the review scores of recently released video games. You need to create a view that returns an alphabetical list of game names. Name represents the game name. Which query should you use?
CREATE VIEW MyGame AS SELECT Name FROM Games ORDER BY Name
CREATE VIEW MyGame AS SELECT * FROM Games
CREATE VIEW MyGame AS SELECT Name FROM Games
CREATE VIEW MyGame AS SELECT * from Games WHERE Name BETWEEN ‘A’ AND ‘I’
Your database contains a table named Customer. You need to delete the record from the Customer table that has a CustomerID of 12345. Which statement should you use?
DELETE FROM Customer WHERE CustomerID = 12345
UPDATE Customer DELETE * WHERE CustomerID = 12345
DELETE CustomerID FROM Customer WHERE CustomerID = 12345
UPDATE CustomerID FROM Customer DELETE * WHERE CustomerID = 12345
Each value in a field in a table must be unique
True
False
Each row in a table must be unique
True
False
Each column name in a table must be unique
True
False
You need to remove a view named EmployeeView from your database. Which statement should you use?
DROP EmployeeView
DELETE VIEW EmployeeView
DELETE EmployeeView
DROP VIEW EmployeeView
What database term is used to describe the relationship between ProductID and ProductCategory?
Deterministic
Compositional
Relatively dependent
Functionally dependent
Cohort
Answer the Following.
Answer:
(a)
You need to recommend a data type in a database table to run financial functions against the charged amounts. Which data type should you recommend?
Varchar
Binary
Bit
Decimal
Which two columns should you select? (Choose 2)
LanguageID
LanguageName
Country
Region
ChapterID
You execute the following statement. INSERT INTO Road VALUES (1234, 36) What is the result?
A syntax error
An error stating that NULL values are not allowed
A new row in the table
An error stating that duplicate IDs are not allowed
Which keyword combines the results of two queries and returns only rows that appear in both result sets?
JOIN
INTERSECT
ALL
UNION
You need to create a report of data in the student’s table. The report must meet the following requirements:
• Display all students who enrolled on or after June 1, 2020
• Display all students who graduated in 2022.
• Return the result set in order of enrollment, with the most recent enrollment date first.
enrollment_date specifies the enrollment date.
graduation date specifies the graduation date.
an academic status of Graduated indicates that a student has graduated.
WHERE enrollment_date >= ‘2020-06-01’ AND academic_status = ‘Graduated’ AND graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date ASC
SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ AND academic_status = ‘Graduated’ OR graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date
SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ OR academic_status = ‘Graduated’ AND graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date DESC
SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ OR academic_status = ‘Graduated’ OR graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date DESC
This type of operation is called a/an:
Outer join
Equi-join
Intersection
Cartesian product
You have a Customer table that stores information about customers and anOrder table that stores information about orders placed. Each table contains a 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?
Complete join
Partial join
Inner join
Full join
You need to create a database to provide a view of North America mammals (NorthAmericanMammals_View). Drag the code segments from the list on the left to the correct locations on the right. You may use each code segment once, more than once, or not at all.
Code Segments
a) CREATE VIEW (dbo) . (NorthAmericanMammals_View)
b) UPDATE VIEW (dbo) . (NorthAmericanMammals_View)
d) AS SELECT VIEW a.Id, a.Name
e) FROM Animal a
f) FROM Animal b
Answer Segments:
Blank-1
Blank-2
Blank-3
WHERE a.Class = 'Mammals'
AND a.InNorthAmerica = 1
A, C, and E
B, D, and E
B, D, and C
A, E, and B
Your company stores customer social security numbers in a column named SSN in a table named Customers. New compliance laws prohibit your company from 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 if removes the SSN column from the Customers table?
ALTER TABLE Customers DELETE SSN;
ALTER TABLE Customers DROP SSN;
ALTER TABLE Customers DROP COLUMN SSN;
ALTER TABLE Customers DELETE COLUMN SSN;
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?
The varchar(4) data type is invalid for the Person table
You should run the DROP CONSTRAINT query before running the ALTER TABLE query
The Person table is empty
The DEFAULT keyword should be used to specify a default value
Which statement will correctly update your table?
UPDATE LoanedBooks SET Books = 0 WHERE (NAME = ‘Harry’ or City = ‘San Francisco’)
UPDATE LoanedBooks SET Books = 0 WHERE (NAME In ‘Harry’, ‘San Francisco’)
UPDATE LoanedBooks SET Books = 0 WHERE (NAME = ‘Harry’ AND City = ‘San Francisco’)
INSERT INTO LoanedBooks SET Books = 0 WHERE ID = 4
Which syntax should you use to create the object?
CREATE TABLE (ID INT, Name VARCHAR (100), Age INT)
CREATE (TABLE Student ID INT, Name VARCHAR (100), Age INT)
CREATE TABLE Student (ID INT, Name VARCHAR (100), Age INT)
CREATE Student (ID INT, Name VARCHAR (100), Age INT)
What is the correct data type for StudentName field?
BIT
DECIMAL
CHAR
VARCHAR
What is the appropriate data type for GradeLevel
DECIMAL
INT
BIT
VARCHAR
What is the appropriate data type for DaysAbsent?
DECIMAL
INT
BIT
VARCHAR
What should you do?
Remove the GROUP BY clause
Remove the ORDER BY clause
Change the HAVING clause to HAVING COUNT(Title)= 1
Change the HAVING clause to HAVING COUNT (1) = 1
Which query correctly runs a result set for all orders where the ship_state excludes Texas (TX) and Arizona (AZ)?
SELECT * FROM Orders WHERE ship_state NOT = ‘TX’ OR ship_state NOT = ‘AZ’
SELECT * FROM Orders WHERE ship_state NOT = ‘TX’ AND ship_state NOT = ‘AZ’
SELECT * FROM Orders WHERE NOT ship_state = ‘TX’ OR NOT ship_state = ‘AZ’
SELECT * FROM Orders WHERE NOT ship_state = ‘TX’ AND NOT ship_state = ‘AZ’
Which statement creates an index?
CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY)
CREATE TABLE Employee (EmployeeID INTEGER INDEX)
CREATE TABLE Employee (EmployeeID INTEGER NULL)
CREATE TABLE Employee (EmployeeID INTEGER DISTINCT)
Which query should you use?
SELECT COUNT(ID), AVG(UnitPrice+Quantity), MAX(UnitPrice+Quantity), SUM(UnitPrice+Quantity) FROM ItemsOnOrder, GROUP BY ItemNumber, LineItemTotal
SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder HAVING ItemNumber, Quantity, UnitPrice
SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder
SELECT SUM(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder
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?
BETWEEN
FETCH
LIKE
IN
What will make this type of search more efficient?
A clustered index on the Price column
A non-clustered index on the Price column
A non-clustered index on the Category column
A clustered index on the ProductName column
You have two tables named SalesPerson and Sales. You need to ensure that each record in the Sales table has a valid associated salesperson record in the SalesPerson table. Which database object should you add to the Sales table?
Clustered index
Foreign key
Primary key
Non-clustered index
You are developing a SQL statement to create a table. Which two SQL keywords are valid to use in a CREATE TABLE statement? (Choose 2)
PRIMARY KEY
CONSTRAINT
INSERT INTO
ORDER BY
You delete rows in a table named Order. The corresponding rows in theOrderItem table are automatically deleted. This process is an example of a/an:
Inherited delete
Waterfall delete
Domino delete
Cascade delete
Functional delete
How many rows are returned by the SQL statement?
7
4
6
5
You must update the table when volunteers ask to be removed from mailing lists. You need to delete all records with the GivenName Tia. Which SQL statement should you use?
DELETE FROM Volunteer WHERE GivenName = ‘Tia’
DELETE FROM Volunteer WHERE GivenName EQUALS ‘Tia’
DELETE FROM Volunteer WHERE GivenName IS ‘Tia’
DELETE FROM Volunteer WHERE GivenName==’Tia’
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?
SELECT SUM(*) FROM Employee
SELECT * FROM Employee
SELECT COUNT(rows) FROM Employee
SELECT COUNT(*) FROM Employee
Which query will retrieve ItemName and Price when chocolate appears in the ItemDescription column?
SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE ‘%chocolate%’;
SELECT ItemName, Price FROM Products WHERE ItemDescription IN ‘%chocolate%’;
SELECT ItemName, Price FROM Products WHERE ItemDescription = ‘chocolate’;
SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE ‘chocolate’;
Which statement deletes the rows where the employer’s phone number is not entered?
DELETE FROM Employee WHERE Phone = NULL
DELETE FROM Employee WHERE Phone IS NOT NULL
DELETE FROM Employee WHERE Phone = NULLABLE
DELETE FROM Employee WHERE Phone IS NULL
Which feature does a relational database use to ensure that data entered into a column is valid?
An index
A primary key
A constraint
An attribute
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 item is a spoon. Which statement should you use?
SET Product TO ProductCategory = 43 WHERE ProductDescription = ‘spoon’
UPDATE Product SET ProductCategory = 43 WHERE ProductDescription = ‘spoon’
SET Product WHERE ProductDescription = ‘spoon’ TO ProductCategory = 43
UPDATE Product WHERE ProductDescription = ‘spoon’ SET ProductCategory = 43
You have a table name 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:
Deleted
Archived
Empty
Unchanged
A table has clustered index. Complete the statements by selecting the correct option from each drop-down list.
A clustered index improves the performance of queries that _____
A clustered index improves the performance of queries on columns that ____
Return large results sets
are accessed randomly
You execute a statement inside a transaction to delete 100 rows from a table. The transaction fails after only 40 rows are deleted. What is the result in the database?
The table will be corrupted
No rows will be deleted form the table
The transaction will restart
40 rows will be deleted form the table
Blank 1
PhoneNumber
WHERE
GROUP BY
ORDER BY
Blank 2
IS NOT NULL
WHERE
ORDER BY
GROUP BY
Blank 3
ORDER BY
PhoneNumber
GROUP BY
INSERT
Which query will increase the price of item 1 by 6 percent?
ALTER Products SET Price = Price * 1.06 WHERE ItemNumber = 1;
USE Products SET Price = Price * 1.06 WHERE ItemNumber = 1;
UPDATE Products SET Price = Price * 1.06 WHERE ItemNumber = 1;
SET Price = Price * 1.06 FROM Products WHERE ItemNumber = 1;
One reason to create a stored procedure is to:
Improve performance
Bypass case sensitivity requirements
Give the user control of the query log
Minimize storage space
You need to normalize a database to first normal form. Which two requirements must you meet? (Choose two)
Exclude repeating groups
) Exclude foreign keys
Exclude duplicate rows
Exclude composite keys
Which SQL statement is a data manipulation language (DML) statement?
SELECT * INTO Employee FROM NewHires
ALTER TABLE Employee ADD EmployeeName VARCHAR
SELECT EmployeeName FROM Employee WHERE EmployeeName = ‘Jack Smith’;
INSERT INTO Employee VALUES (‘Jack Smith’);
You have a table named Customer that has columns named CustomerID, Firstname, and DateJoined CustomerID is a primary key. You execute the following query:
SELECT CustomerID, FirstName, DateJoined FROM Customer
How are the rows organized in the result set?
In no predictable order
In alphabetical order by FirstName
In the order in which the rows were inserted
In chronological order by DateJoined
Blank 1
TOP 10
GROUP BY 10
COUNT
IS NULL
Blank 2
IS NOT NULL
IS NULL
GROUP BY 10
ORDER BY
Blank 3
GROUP BY 10
ORDER BY
TOP 10
COUNT
The stored procedure returns all null value. You verify that there is data in the Person table. What is like the cause of this problem?
The plus (+) operator cannot be used to append character data
You must specify the NULLIF keyword in the query
You must specify the JOIN keyword in the SELECT statement
The Prefix or firstName columns have null values
You need to populate a table named EmployeeCopy with data from an existing table named Employee. Which statement should you use?
FROM Employee INTO EmployeeCopy
INSERT INTO EmployeeCopy SELECT * FROM Employee
SELECT INTO EmployeeCopy SELECT FROM Employee
COPY INTO Employee SELECT FROM Employee
Which statement should you use to remove a foreign key?
DELETE TABLE
DELETE FOREGIN KEY
ALTER TABLE
ALTER FOREIGN KEY
Blank 1
AND
OR
WHERE
ORDER BY
Blank 2
>
<
=
ORDER BY
Blank 3
ORDER BY
GROUP BY
AND
WHERE
Blank 4
ArrivalTime DESC
OriginAirport
Arrival Time
DepartureTime
Which statement creates a composite key?
CREATE TABLE Order (OrderID INTEGER , OrderItemID INTEGER, PRIMARY KEY (OrderID, OrderItemID))
CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY OrderID, PRIMARY KEY OrderItemID))
CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY)
CREATE TABLE Order (OrderID INTEGER PRIMARY KEY, OrderItemID INTEGER PRIMARY KEY)
