deck_18323252 Flashcards by ANGELO LOZANO

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?

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

How well did you know this?

Not at all

Perfectly

You need to create a report of data in the students 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.
Which query should you use?

SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ OR academic_status = ‘Graduated’ AND graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date DESC

How well did you know this?

Not at all

Perfectly

How many rows are returned by the SQL query on the Cars table?

a. 4

How well did you know this?

Not at all

Perfectly

Which statement deletes a Customer record with ID 12345?

D. DELETE FROM Customer WHERE CustomerID = 12345

How well did you know this?

Not at all

Perfectly

Which SQL statement is a DML operation?

C. INSERT INTO Employee VALUES (‘Jack Smith’)

How well did you know this?

Not at all

Perfectly

Which query retrieves items with ‘chocolate’ in the description?

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

How well did you know this?

Not at all

Perfectly

Which statement creates a composite key?

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

How well did you know this?

Not at all

Perfectly

Structuring a table: Field uniqueness, row uniqueness, column name uniqueness.

False (field uniqueness), True (row uniqueness), True (column name uniqueness)

How well did you know this?

Not at all

Perfectly

Recommended data type for financial amounts?

a. Decimal

How well did you know this?

Not at all

Perfectly

Which statement updates Harry’s books in San Francisco?

D. UPDATE LoanedBooks SET Books = 0 WHERE (NAME = ‘Harry’ AND City = ‘San Francisco’)

How well did you know this?

Not at all

Perfectly

Which statement returns the number of rows in a table?

D. SELECT COUNT(*) FROM Employee

How well did you know this?

Not at all

Perfectly

How to display total points per player on TeamID 1?

SELECT PlayerID, SUM(Points) FROM PlayerStat WHERE TeamID = 1 GROUP BY PlayerID

How well did you know this?

Not at all

Perfectly

Which object ensures valid SalesPerson records in the Sales table?

B. Foreign key

How well did you know this?

Not at all

Perfectly

How to create the NorthAmericanMammals view?

CREATE VIEW [dbo].[NorthAmericanMammals View] AS SELECT a.Id, a.Name FROM Animal a WHERE a.Class = ‘Mammals’ AND a.InNorthAmerica = 1

How well did you know this?

Not at all

Perfectly

What happens to a view after deleting its underlying data?

d. Empty

How well did you know this?

Not at all

Perfectly

Which keyword returns rows appearing in both result sets?

b. INTERSECT

How well did you know this?

Not at all

Perfectly

A reason to create a stored procedure?

c. Improve performance

How well did you know this?

Not at all

Perfectly

First normal form requirements?

a. Exclude duplicate rows; c. Exclude repeating groups

How well did you know this?

Not at all

Perfectly

Which join includes all customers and those with no orders?

a. Full join

How well did you know this?

Not at all

Perfectly

Query to select top 10 uninspected buildings?

SELECT TOP 10 FROM Building WHERE InspectionDate IS NULL ORDER BY InspectionDate

How well did you know this?

Not at all

Perfectly

Assign data types to StudentName, GradeLevel, DaysAbsent?

StudentName VARCHAR; GradeLevel INT; DaysAbsent DECIMAL

How well did you know this?

Not at all

Perfectly

Update ProductCategory for spoons?

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

How well did you know this?

Not at all

Perfectly

Term for ProductID and ProductCategory relationship?

e. functionally dependent

How well did you know this?

Not at all

Perfectly

Result of SELECT without JOIN?

d. Cartesian product

How well did you know this?

Not at all

Perfectly

Which statement creates an index?

A. CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY)

Valid SQL keywords in CREATE TABLE?

A. PRIMARY KEY; B. CONSTRAINT

Delete records with GivenName 'Tia'?

b. DELETE FROM Volunteer WHERE GivenName = 'Tia'

How are rows organized without ORDER BY?

c. In no predictable order

Query for valid extensions sorted by LastName?

SELECT LastName, PhoneNumber, Extension FROM Customers WHERE Extension IS NOT NULL ORDER BY LastName

Query for total orders, averages, max, grand total?

D. SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder

Populate EmployeeCopy from Employee?

C. INSERT INTO EmployeeCopy SELECT * FROM Employee

Result of failed transaction deleting 40/100 rows?

a. No rows will be deleted (if transaction rolled back)

Query for countries with fewer than 50 orders?

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

Result of inserting duplicate RoadID?

d. Error: duplicate IDs (if RoadID is primary key)

Term for automatic deletion of related rows?

b. cascade delete

Increase price of item 1 by 6%?

B. UPDATE Products SET Price = Price * 1.06 WHERE ItemNumber = 1

Backup type accuracy?

True (full backup), False (transaction log), False (differential), True (file restore)

Delete employees with NULL phone numbers?

C. DELETE FROM Employee WHERE Phone IS NULL

Exclude TX and AZ in ship_state?

A. SELECT * FROM Orders WHERE NOT ship_state = 'TX' AND NOT ship_state = 'AZ'

Remove SSN column syntax?

B. ALTER TABLE Customers DROP COLUMN SSN

Fix syntax error in HAVING clause?

D. Remove the ORDER BY clause

Remove a view?

b. DROP VIEW EmployeeView

Remove a foreign key?

d. ALTER TABLE

Ensure employees belong to existing departments?

b. Foreign key

Improve search efficiency on Category?

c. Non-clustered index on Category

Use LIKE for character search?

d. LIKE

Null values in concatenation?

c. Prefix or firstName columns have null values

Composite primary key for ChapterLanguage?

E. ChapterID; F. LanguageID

Feature ensuring valid column data?

c. Constraint

Create alphabetical view of game names?

C. CREATE VIEW MyGame AS SELECT Name FROM Games ORDER BY Name

Disable User1's SELECT access?

D. REVOKE SELECT ON Customer FROM User1

Syntax to create Student table?

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

Query for flights arriving at LGA today?

SELECT FlightNumber FROM Flight WHERE DestinationAirport = 'LGA' AND ArrivalTime > GETDATE() ORDER BY ArrivalTime DESC

Clustered index performance impact?

A clustered index improves performance for large result sets and columns accessed randomly.

QUESTION 1: For a History table column that must record the time of day with time zone awareness, which data type should you use?

datetimeoffset

QUESTION 2: Evaluate the statement ‘Views are database objects that contain all of the data in a database.’ What is the correct revision?

No change is needed

QUESTION 3: Given two tables Cars and Color (related by ColorId), how many rows are returned by the provided SELECT statement?

QUESTION 4: Fill in the blank – The ________ model for database management is based on first‐order predicate logic.

Relational

QUESTION 5: After a MySQL server crash and recovery, which table-maintenance operation should you perform to ensure table integrity?

Perform a table check operation

QUESTION 6: To allow each department’s users to read and update only their own database on SQL Server 2005, what should James do?

Create a Windows Authentication login for each domain user and add them to the db_datareader and db_datawriter roles

QUESTION 7: Which process creates a design for a database to support enterprise operations?

Database design

QUESTION 8: On a Unix-based MySQL server, which command converts zone files so that MySQL’s time zones match the system?

shell> mysql_tzinfo_to_sql /ust/share/zoneinfo | mysql -u root mysql

QUESTION 9: When is the ideal time to back up dynamic log files?

When the server is stopped

QUESTION 10: When attempting to create a database with the name ‘24342’, what is the expected outcome?

An error will be generated stating that a database name should be a combination of numerals and alphabets

QUESTION 11: Which two commands cannot be rolled back?

TRUNCATE and COMMIT

QUESTION 12: Which two prototyping strategies are used in database development?

Evolutionary prototyping and Requirements prototyping

QUESTION 13: Which DML SQL statements support correlated subqueries?

SELECT, UPDATE, and DELETE

QUESTION 14: Regarding the relationship between foreign keys and primary keys, which statements are true?

They create a link between entities, a foreign key ties attributes to a primary key, and a foreign key constraint works with a primary key to enforce referential integrity (i.e. answers A, C, and D)

QUESTION 15: In the CREATE TABLE query for Product, what two errors are present?

A primary key column cannot contain NULL values and ProductName is declared as VARCHAR without a specified length

QUESTION 16: Which database planning step is used to record an object so it is represented visually?

Object modeling

QUESTION 17: To allow old (pre-4.1) MySQL clients to connect without upgrading, which server option should be used?

Run the server with the –old-password option

QUESTION 18: Which file stores updates made after a backup?

Binary log files

QUESTION 19: Which working model of a database system is a design concept?

Conceptual database design

QUESTION 20: Which script converts Unix-type zone files into SQL statements for MySQL?

mysql_tzinfo_to_sql

QUESTION 21: When defining an artificial key for a table, which factors justify its use?

The key must be unique, persistent, cannot be NULL, and the natural key contains highly sensitive data (all apply)

QUESTION 22: Which DDL command removes an object from the RDBMS?

DROP

QUESTION 23: To design a database that supports enterprise operations, which activity should be used?

Database design

QUESTION 24: To prevent unauthorized changes via stored procedures, what is the least‐administrative–effort fix?

Parse and block dangerous input (e.g. single quotes) in the procedure parameters

QUESTION 25: Which feature enables using generic methods for database access?

Abstraction

QUESTION 26: In a distance education university, what is the relationship between a regional center and a study center?

One-to-many

QUESTION 27: To decompose database components only to a limited extent, which approach should be used?

Mixed approach

QUESTION 28: Which constraint gives a default value to a column when none is provided?

DEFAULT constraint

QUESTION 29: Which design represents the user interface and application programs that process a database?

Application Design

QUESTION 30: To add a security layer using a cipher for preventing unauthorized viewing, what should be used?

Encryption

QUESTION 31: Which value cannot be stored in a character column defined as a primary key?

null

QUESTION 32: Which statement about external tables is true?

They can have constraints or triggers

QUESTION 33: Which methods can be used to populate a table?

MERGE statement, Data Pump, INSERT statement, and SQL*Loader

QUESTION 34: The CHECK TABLE command works on which two types of tables?

InnoDB and MyISAM

QUESTION 35: Which three approaches are used in database design?

Inside-out approach, Top-down approach, and Bottom-up approach

QUESTION 36: For a highly sensitive database where security trumps speed, which protection technique is recommended?

Encryption

QUESTION 37: What database term describes preventing simultaneous access to data items?

Lock

QUESTION 38: To index the Orders table for queries that only require orders over $1000, what type of index should be used?

Filtered index

QUESTION 39: To insert records in a fixed-format database table, which data model should be used?

Relational model

QUESTION 40: In the Production table creation code, which line contains an error?

Line 4

QUESTION 41: Which planning step involves interviewing to determine database requirements?

Gathering information

QUESTION 42: Which mechanism provides reliable units of work for recovery?

Database transaction

QUESTION 43: To create a data model independent of any DBMS, which design should you use?

Logical database design

QUESTION 44: According to JDBC API 2.0, which is the lowest level of transaction isolation?

TRANSACTION_NONE

QUESTION 45: To prevent deletion of customers via a trigger, which trigger definitions are acceptable?

A DML trigger using AFTER DELETE with rollback (i.e. options B and D)

QUESTION 46: In planning a database, which step identifies key objects?

Object identification

QUESTION 47: A table’s non-key attribute depends on the whole candidate key. Which normal form is this?

2NF

QUESTION 48: Which DB2 9 commands change the authorization type?

UPDATE DBM CFG and SET AUTHORIZATION

QUESTION 49: Which commands are DML commands?

INSERT, UPDATE, SELECT, and DELETE

QUESTION 50: Which statement correctly describes referential integrity?

No record in a child table can exist without a matching record in the parent table

QUESTION 51: To disable remote connections on a MySQL server running on Windows, which option is used?

–skip-networking

QUESTION 52: To minimize index fragmentation in a heavily transacted table, what fillfactor should be used?

QUESTION 53: To resolve a DML failure on the Sales table, which two actions are appropriate?

Increase tablespace quota and provide appropriate privileges (options C and D)

QUESTION 54: Which process extracts audit trails for security without DBA access?

Native auditing

QUESTION 55: For a table with composite key (AuthorID, TitleID) and an attribute fully dependent on the composite key, which normal form is violated?

2NF

QUESTION 56: Which management activity helps realize the database application stages most efficiently?

Database planning

QUESTION 57: For tablespace point‐in‐time recovery, in which situations should TSPITR be used?

To recover a table that is logically corrupted and to recover a DML statement affecting a subset of the database

QUESTION 58: To record every UPDATE on the Employees table into EmployeeChanges, what should be created?

A DML trigger that queries the updated table

QUESTION 59: When rebuilding an index without the ONLINE keyword, what is the effect?

The index is locked for any DML operation

QUESTION 60: To prevent inconsistent data entry, which feature should be enforced?

Referential integrity

QUESTION 61: Evaluate the statement about the UNION keyword returning only rows that appear in both result sets. What is the correct response?

No change is needed

QUESTION 62: For a 10‐TB database that must be backed up every 2 hours, which backup type should be used?

Incremental

QUESTION 63: Evaluate the statement about attributes ensuring valid data entry. What concept is used?

A constraint

QUESTION 64: (Hotspot) For a table–structuring question involving several Azure services, the correct Yes/No responses are as provided in the exam’s hotspot answers.

(Hotspot responses as provided)

QUESTION 65: Evaluate the statement ‘A key defines the amount of storage space allocated to a value in a column.’ What is correct?

data type

QUESTION 66: To create a view that filters rows, which clause must be included?

WHERE

QUESTION 67: In the State and Address tables, what is the role of StateID in each?

Primary key in State; Foreign key in Address

QUESTION 68: Evaluate the following: A SQL Server Login can use Windows Authentication (Yes); a SQL Server Role provides server-level permissions (Yes); a SQL Server Login is granted permission to database views only (No).

Yes; Yes; No

QUESTION 69: Evaluate these statements: (A) You can delete data using a stored procedure; (B) A function must have a return value; (C) A stored procedure must have a return value. Which are true?

Yes; Yes; No

QUESTION 70: Evaluate the effect of using the simple recovery model on work-loss exposure and log backups. Also, for a user’s server roles, select the appropriate levels (answers provided in exam dropdowns).

Less work-loss exposure; fewer log file backups; (server role answers: less, unlimited)

QUESTION 71: For a user with server roles, complete the statements regarding actions on data and on objects using the exam dropdown values.

Data actions: unlimited; Object actions: read-only

QUESTION 72: In a table, what must be unique?

Each field’s value, each row, and each column name must be unique (as applicable); in the State–Address relationship, StateID in State is unique and in Address it is not

QUESTION 73: Evaluate these statements about indexes: (A) A clustered index sorts and stores data based on its key; (B) A non-clustered index is defined on a table using a clustered index or heap; (C) A unique index ensures no duplicate values; (D) A filtered index is optimized for queries selecting a small percentage of rows.

No; Yes

QUESTION 74: Evaluate these data type statements: (A) VARCHAR contains only numeric characters; (B) NUMERIC contains numbers with decimals; (C) INT contains only whole numbers.

No; Yes

QUESTION 75: In a clustered index illustration, match the answer choices to the FlightNumber and Airline columns as described.

(Answer per exam dropdowns: for FlightNumber, use a clustered index; for Airline, use a non-clustered index)

QUESTION 76: In a SQL Server application, complete the statements about result set characteristics as provided in the exam dropdowns.

(Dropdown answers: e.g., return large result sets; return a range of values using the = operator; do not use ORDER BY or GROUP BY; are accessed randomly; are accessed sequentially; are not unique)

QUESTION 77: For protecting against SQL injection, complete the statements about which computer is used and how it should be formatted (using exam dropdowns).

(Dropdown answers: e.g., client = s01.constoso.com; server = www.contoso.com)

QUESTION 78: Evaluate these backup-related statements: (A) A full database backup copies all data; (B) A transaction log backup does not copy all data; (C) A differential backup copies only data changed since the last full backup; (D) A file or filegroup restore recovers a portion of the database.

Yes; No

QUESTION 79: (Matching – Volunteer project) Match the required data types to the volunteer table columns as specified.

(Match: e.g., StudentName → VARCHAR, GradeLevel → INT, DaysAbsent → DECIMAL, etc.)

QUESTION 80: In matching CourseName to StudentName for school attendance data, which courses match which students?

(Matches as provided: e.g., Brad → [course], Joe → [course], Susan → [course])

QUESTION 81: Evaluate the statement ‘Views are database objects that contain all of the data in a database.’ What is correct?

No change is needed

QUESTION 82: For the Cars and Color tables (related by ColorId), how many rows does the SELECT return?

QUESTION 83: For a charity volunteer table that must be updated when volunteers opt out, which transaction approach should be used?

Use a transaction to ensure referential and data integrity (choose the option provided in the exam – typically Option D)

QUESTION 84: Evaluate the statement ‘Create a query that returns data using the UPDATE statement.’ What is the correct revision?

Replace UPDATE with SELECT

QUESTION 85: Evaluate the statement ‘Truncate is a database term used to describe applying a backup to a damaged database.’ What is correct?

Restore

QUESTION 86: For a charity website collecting volunteer information (Given name, Surname, Telephone, Email), what is the correct storage recommendation?

Create a table with appropriately named columns

QUESTION 87: Evaluate the statement ‘Use the ALLOW SELECT command to give a user permission to read data.’ What is correct?

GRANT SELECT

QUESTION 88: Complete the statements about a user’s server roles regarding actions on data and objects using exam dropdowns.

(Dropdown answers: e.g., data actions: no; object actions: unlimited or configuration as provided)

QUESTION 89: Evaluate the statement about denormalizing data when combining three tables into one for optimized read performance.

No change is needed

QUESTION 90: To delete a database table, which DDL keyword should be used?

DROP TABLE

QUESTION 91: To add rows to a table, which SQL keyword should be used?

INSERT

QUESTION 92: To retrieve data from two related tables based on a common column, which command should be used?

JOIN

QUESTION 93: Which two SQL statements can copy records from an existing table to a new table?

(Answers as provided in exam: Options B and C)

QUESTION 94: Evaluate the statement regarding the UNION keyword returning only rows that appear in both queries.

No change is needed

QUESTION 95: For normalizing recipe data to third normal form, how many tables should be created?

QUESTION 96: For a charity project involving tables Chapter and Language and a junction table ChapterLanguage, which two columns form the composite primary key?

ChapterId and LanguageId

QUESTION 97: Evaluate the statement ‘Use the ALTER statement to add a new table in a database.’ What is correct?

Replace CREATE with CREATE (i.e. use CREATE, not ALTER; correct answer: CREATE)

QUESTION 98: To rename a column in a table, which DDL statement should be used?

ALTER TABLE

QUESTION 99: Evaluate the statement ‘Use indexing to create, remove, or change database objects.’ What is correct?

Data definition language (DDL) statements

QUESTION 100: Evaluate the statement ‘In a database table, each column represents a unique record.’ What is correct?

A row represents a record

QUESTION 101: If two tables each have three rows, how many rows are in their Cartesian product?

QUESTION 102: To ensure each record in the Sales table has a valid associated salesperson, which object should be added?

Foreign key

QUESTION 103: (Matching – school attendance) Match the correct data types to StudentName, GradeLevel, and DaysAbsent.

(e.g., StudentName → VARCHAR, GradeLevel → INT, DaysAbsent → DECIMAL)

QUESTION 104: To change a student’s first name in the Student table, which SQL keyword should be used?

UPDATE

QUESTION 105: Which SQL statement inserts a new product into the Product table with ProductID and Name?

(Answer: Use the INSERT INTO statement with specified values; see exam Option D)

QUESTION 106: To return the number of rows in the Employee table, which query should be used?

(Answer: SELECT COUNT(*) FROM Employee; see exam Option D)

QUESTION 107: In a query to retrieve only song names from SongInformation where sales > 1000, which option is correct?

Option A

QUESTION 108: To update volunteer information (change Tia’s name to Kimberly) in the Volunteer table, which statement should be used?

(Answer: Option D as per exam)

QUESTION 109: Evaluate the statement ‘Use the FROM keyword in a SELECT statement to return rows that meet a specific condition.’ What is correct?

WHERE

QUESTION 110: To combine the results of two queries into one result set containing all rows, which SQL statement should be used?

UNION

QUESTION 111: Evaluate the statement ‘The CREATE TABLE command removes table definitions and all associated data.’ What is correct?

DROP TABLE

QUESTION 112: For the Cars table shown, how many rows are returned by the given SQL statement?

QUESTION 113: First normal form requires that a database excludes what?

Repeating groups

QUESTION 114: If a DELETE transaction fails after deleting 40 of 100 rows, what is the result?

No rows will be deleted (rollback occurs)

QUESTION 115: In an UPDATE statement to change a product’s name to ‘glass’, what clause must be used?

SET ProduetName = ‘glass’

QUESTION 116: To delete the record with CustomerID 12345 from the Customer table, which statement should be used?

(Answer: DELETE FROM Customer WHERE CustomerID = 12345; see Option D)

QUESTION 117: On which database structure does an INSERT statement operate?

Table

QUESTION 118: In an UPDATE statement that sets InStock to Yes for a specific ProductID, which clause is required?

WHERE

QUESTION 119: To insert a new product with name 'Plate' and ProductID 12345 into the Product table, which statement should be used?

(Answer: Use INSERT INTO Product VALUES (12345, 'Plate'); see Option D)

QUESTION 120: With the WITH GRANT OPTION, what can a user do?

Delegate permissions to other users

QUESTION 121: Which type of index changes the physical order of data in a table?

Clustered index

QUESTION 122: To remove a foreign key, which statement should be used?

ALTER TABLE

QUESTION 123: How is student information stored if there are 100 students and three pieces of data per student?

100 rows and 3 columns

QUESTION 124: Which three commands are valid DML commands?

INSERT, UPDATE, and DELETE

QUESTION 125: What is one difference between an UPDATE and a DELETE statement?

An UPDATE does not remove rows; a DELETE removes rows

QUESTION 126: To ensure an employee is assigned only to an existing department, what constraint is applied?

Foreign key

QUESTION 127: To list product name and price sorted by price from highest to lowest, which statement should be used?

(Answer: Use ORDER BY Price DESC; see exam Option D)

QUESTION 128: When deleting rows from Order causes corresponding OrderItem rows to be deleted automatically, what is this process called?

Cascade delete

QUESTION 129: Which SQL statement deletes rows where the employee’s phone number is not entered?

(Answer: DELETE FROM Employee WHERE PhoneNumber IS NULL; see Option A)

QUESTION 130: To insert two new products into the Product table (Book and Movie), which statement should be used?

(Answer: See exam Option BD)

QUESTION 131: When executing SELECT * FROM Employee, which columns are returned?

All columns

QUESTION 132: Which two elements are required to define a column?

A name and a data type

QUESTION 133: For an AccountNumber that is one letter followed by four digits, which data type should be used?

CHAR

QUESTION 134: Which keyword must be included in a CREATE VIEW statement?

UNIQUE is not required; the correct answer is SELECT (as the view’s definition must include a SELECT clause) – however, per the exam the answer is likely WHERE is not applicable; thus answer: (see Option D – exam answer indicates ORDER BY is not required) Actually the provided answer key for Q134 is: UNIQUE (option D) – but based on our file, answer is “SELECT” in the view definition. [Refer to exam key].

QUESTION 135: To add a new column named District to the Customer table, which statement should be used?

(Answer: ALTER TABLE Customer ADD District ... ; see Option D)

QUESTION 136: To remove a view named EmployeeView, which statement should be used?

DROP VIEW EmployeeView

QUESTION 137: What is a named group of SQL statements that can be executed as one unit?

Stored procedure

QUESTION 138: For the Product table with ProductID as primary key and CategoryID as foreign key, what happens when you run the given INSERT statement?

A primary key constraint violation

QUESTION 139: Why is denormalization performed?

To improve query performance

QUESTION 140: To efficiently search for a product by its unique ID in a one‐million–row table, what should be used?

An index

QUESTION 141: To routinely assign a set of permissions to new users, what should you create?

Role

QUESTION 142: To disable User1’s access to view data in the Customer table, which statement should be used?

(Answer: Use the appropriate REVOKE statement; see exam Option C)

QUESTION 143: When executing DELETE FROM Student on a table with 100 rows (some with NULL FirstName), what is the result?

All rows in the table will be deleted

QUESTION 144: Data in a database is stored in what structure?

Tables

QUESTION 145: To store contact information for each student, where should the data be stored?

In a table (each row represents a student)

QUESTION 146: Which category of SQL statements adds, removes, and modifies database structures?

Data definition language (DDL)

QUESTION 147: Joining the Customer table with the Order table (including customers with no orders) produces which type of join?

Outer join

QUESTION 148: To return the number of rows in the Employee table, which statement should be used?

(Answer: SELECT COUNT(*) FROM Employee; see Option D)

QUESTION 149: In the Product table, what is the value returned by SELECT COUNT(*) FROM Product WHERE Quantity > 18?

QUESTION 150: Which command removes a table from a database?

DROP TABLE

QUESTION 151: The provided statement is an example of which operation?

Cartesian product

QUESTION 152: In a SELECT statement, which keyword is used to return rows meeting a condition?

WHERE

QUESTION 153: To change a student’s first name in the Student table, which DML command is used?

UPDATE

QUESTION 154: To populate a table EmployeeCopy with data from Employee, which statement should be used?

INSERT INTO … SELECT

QUESTION 155: To find every product whose name contains a specific character, which keyword is used in the WHERE clause?

QUESTION 156: When executing DELETE FROM Order WHERE CustomerID = 209, what is the result?

All orders for CustomerID 209 are deleted from the Order table

QUESTION 157: To change the ProductCategory for all spoons in the Product table to 43, which statement should be used?

(Answer: Use an UPDATE statement; see exam Option A)

QUESTION 158: To add a column to an existing table, which command should be used?

ALTER

QUESTION 159: For the Product table defined as (ID INTEGER PRIMARY KEY, Name VARCHAR(20), Quantity INTEGER), how many rows are returned by SELECT Name FROM Product WHERE Quantity IS NOT NULL?

QUESTION 160: Which constraint ensures a unique value in the ID column for each customer?

PRIMARY KEY

QUESTION 161: In a table, the component that holds information for a single entry is called what?

Row

QUESTION 162: The statement SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department produces what type of operation?

Cartesian product

QUESTION 163: An INSERT statement in SQL is used to add what to a table?

A row of data

QUESTION 164: To retrieve rows from a table, which DML command should be used?

SELECT

QUESTION 165: To store product names that vary from 3 to 30 characters while minimizing storage, which data type should be used?

VARCHAR (30)

QUESTION 166: One reason to create a stored procedure is to improve what?

Performance

QUESTION 167: Which permission does a user need in order to run a stored procedure?

EXECUTE

QUESTION 168: After deleting all products in the Furniture category from the Product table, what is the result in the view that displays Furniture?

Empty

QUESTION 169: Given the table Road(RoadID INTEGER NOT NULL, Distance INTEGER NOT NULL), what is the result of INSERT INTO Road VALUES (1234, 36)?

A new row is inserted in the table

QUESTION 170: To store product quantities using minimal storage, which data type should be used?

INTEGER

QUESTION 171: Which statement will result in the creation of an index?

(Answer as per exam Option D)

QUESTION 172: To enable a new employee to authenticate to your database, which command should you use?

CREATE USER

QUESTION 173: Which keyword can be used in a CREATE TABLE statement?

UNIQUE

QUESTION 174: To give a user permission to read data from a table, which command should be used?

GRANT SELECT

QUESTION 175: In which situation do you need to perform a restore on a database?

When data becomes corrupted in the database

QUESTION 176: After a 3:00 P.M. backup, a table Customer is created at 4:00 P.M., then the server fails at 5:00 P.M. and only the 3:00 P.M. backup is applied. What is the result?

The Customer table no longer exists

QUESTION 177: Which statement creates a composite key?

(Answer as per exam Option D)

QUESTION 178: Which key uniquely identifies a row in a table?

primary

QUESTION 179: Breaking one table into two related tables is referred to as what?

Normalization

QUESTION 180: In a table containing ProductID and ProductCategory, what term describes the relationship?

Compositional

QUESTION 181: The terms “bitmap,” “b-tree,” and “hash” refer to which type of database structure?

Index

QUESTION 182: One reason to add an index is to improve the performance of which operations?

Select statements

QUESTION 183: When a query is run against the wrong ______, an error may occur. What is missing?

table

QUESTION 184: Which statement best defines a syntax error?

Causes a statement not to run

QUESTION 185: When manipulating data, a missing ______ may cause an error. What is it?

keyword

QUESTION 186: In an UPDATE statement, which clause should always be included to target specific rows?

WHERE

QUESTION 187: In an INSERT INTO … VALUES statement, what must be included in parentheses?

Required fields

QUESTION 188: Which statement is used to copy records from an existing table to a new table?

INSERT INTO … SELECT

QUESTION 189: Before running a DELETE statement, it is advisable to run which statement on the table?

SELECT

QUESTION 190: Which statement is used to copy records from one table into an existing table?

INSERT INTO … SELECT

QUESTION 191: Which keyword removes records from a table?

DELETE

QUESTION 192: Which statement is used to insert values into a table?

INSERT INTO ... VALUES

QUESTION 193: Which statement is used to change existing data in a table?

UPDATE

QUESTION 194: What is the main difference between a DELETE and a TRUNCATE TABLE statement?

DELETE allows specific rows to be removed; TRUNCATE deletes all data in a table

QUESTION 195: While a clustered index is based on a _______, a nonclustered index can be based on any other field.

primary key field

QUESTION 196: What is not allowed in object names in SQL?

spaces

QUESTION 197: Which command removes a table from a database?

DROP TABLE

QUESTION 198: When creating a stored procedure, what type of parameter must be specified?

input, output (choose as applicable – answer: output)

QUESTION 199: Which statement best defines a view?

A stored statement that leads one to specific information

QUESTION 200: To change a view, with what keyword should CREATE be replaced?

ALTER