Practice B

Consider the following statement:SELECT * FROM Customers WHERE City LIKE '_ondon';What will the LIKE operator '_ondon' search in the Customers table?

A city starting with any character, followed by "ondon"

Which design phase involves designing of the user interface for application programs that will access and manipulate data in a database?

Application

Which role allows a user to modify the data within a column?

Application

Which property of DBMS specifies that either all of the tasks in a given transaction are performed or none of them is performed?

Atomicity

A doctor wants to keep record of all his patients in a database. He added the items such as patient's name, address, id, admit date, discharge date, and so on. What would these items be referred to as?

Attributes

What are the advantages of using keys in RDBMS?Each correct answer represents a complete solution. Choose all that apply.

Avoiding data redundancy Creating data dependency

A database has a table named Product with the following attributes:CatalogProductcodeColorThe attributes are such that Productcode is functionally dependent on Catalog, that is, Catalog → Productcode, and Color is functionally dependent on Productcode, that is, Productcode → Color. Here, Catalog is the primary key. Which of the following is true about this table?

3NF

onsider the following table:Table A: Students S_IDFirst_NameLast_NameScore101MarkHayes25102PeteWelch23103NancyStuart24104DemiSmith24105TimMay25What is the degree of this table?

4

Which of the following is the syntax for the COMMIT command?

COMMIT

Which of the following are characteristics of a simple view?Each correct answer represents a complete solution. Choose all that apply.

Cannot use SQL group functions or grouping of data DML operations are allowed on the view Retrieves data from only one database table

You manage a large database named Sales. The Sales database contains a table named OrderDetails, which is a heavily transacted table with frequent inserts. Indexes in the table often become fragmented due to excessive page splitting. You want to minimize the amount of fragmentation due to page splits. What should you do?

Change the FILLFACTOR for the indexes to 60.

You work as a database administrator for ABC Inc. You want to grant certain privileges to a database user named Fredrick so that he can create a view in his own schema. Which of the following privileges can help him accomplish the task?

Create View

Andrew works as a DBA for ABC Inc. The company uses SQL Server 2008 and has a database named Sales. Andrew wants to add users and grant each of them permissions needed to retrieve/manipulate data in the database. What role should Andrew assign to these users to accomplish the task?

Custom Database

If a value was not entered for a not-null column, which of the following features would have provided value to the column?

Default Constraint

A user named Alfred inserts data in the table EMPLOYEES. Now, what should he do so that the other users are able to see the new data?

Execute a COMMIT statement in the same session.

Which of the following clauses specifies the name of a table or a view from which data is to be retrieved, updated or deleted?

FROM

You are developing a Sales database. The database contains two tables: SalesPerson that identifies details of the person making the sale, and OrderHeader that identifies detail or specific orders. You want to ensure that every order recorded in the OrderHeader table is referenced to a valid person identified in the SalesPerson table. What should be created in the OrderHeader table?

Foreign Key

Andrew works in a company that has a SQL Server 2008 computer. The computer has a database named Sales. The company has its office in an area that is prone to power failure. He has to take a backup that includes all the file groups and database objects. Which of the following types of backups will he perform to accomplish the task?

Full database

Which join specifies that if a row from either table does not match the selection criteria, the row is included in the result set, and its output columns that correspond to the other table are set to NULL.

Full outer

Which of the following SQL commands have the potential to add new data, change existing data, or remove data from a table in some way?Each correct answer represents a complete solution. Choose all that apply.

INSERT UPDATE DELETE

Which of the following statements of the INSERT operation is not valid?

INSERT INTO temp_employees (EMP_ID, NAME) VALUES (100);

Which of the following operators takes the results of two queries and returns only rows that appear in both result sets?

INTERSECT

Which of the following data types should you use to store the age of an employee?

Integer

Which term describes the following statement?"A transaction must be performed such that it is unaffected by any other part of the system."

Isolation

How is normalization helpful in a relational database?Each correct answer represents a complete solution. Choose all that apply.

It minimizes data redundancy. It retrieves data from the table quickly.

Which of the following statements is true about a relationship in a Relational Database Management System (RDBMS)?

It represents an association between two entities.

You work as a database designer for ABC Inc. The company's database consists of two tables Customers and Orders. The marketing manager asked you to create a report that contains the details of pending orders along with their customer's details. Which of the following clauses should you use to accomplish the task?

JOIN

Which of the following is not the type of approach in relational database design?

Middle

Which of the following is the goal of first normal form?

Minimize duplicate information.

Which of the following enforces entity integrity?

Primary Key

You work as a database administrator for ABC Inc. You create a view on a database table named Employee using the below statement:CREATE VIEW Emp AS SELECT * FROM EmployeeAfter creating the view, you add one more column named Empsalary to the table. What will you do to make this column visible in the view?

Recreate the view using CREATE or REPLACE VIEW.

Which of the following shapes are used in an ER diagram?Each correct answer represents a complete solution. Choose all that apply.

Rectangle Ellipse Diamond Line

Which of the following ensures that no inconsistent data is entered into a database?

Refential Integrity

Which of the following statements are true of a function?Each correct answer represents a complete solution. Choose all that apply.

Referred directly in a SELECT statement Returns a value to the caller

You work as a database designer for ABC Inc. The organization has a database named HR. The database contains two tables, namely, Employee and Department. You want all the non-key attributes in both the tables to be functionally dependent on the key attribute. Which of the following actions will you take to normalize the table to 2NF?

Remove the DepartmentName column from the Employee table.

You work as a database designer at a bicycle manufacturing company. You manage a database that contains the Bicycle table, which is used to record information of all bicycles. You need to retrieve information of all bicycles that are blue in color. Which of the following statements should you use to accomplish the task?

SELECT * FROM Bicycle WHERE cycle_color = 'blue'

Pete works as a database administrator for XYZ Inc. The company has a SQL Server 2008 database named Employees in its infrastructure. He wants to retrieve a column named Employee_dependents to be represented as Employee Family Size in the result. For this, he needs to alias the Employee_dependents column to Employee Family Size by keeping in mind that the case of the alias should not change. Which of the following statements will help Pete in accomplishing the task?Each correct answer represents a complete solution. Choose two.

SELECT Employee_dependents "Employee Family Size" from Employees SELECT Employee_dependents AS "Employee Family Size" from Employees

Which of the following types of views are supported by SQL Server 2008?Each correct answer represents a complete solution. Choose all that apply.

Standard Partitioned

In which situation would an OUTER JOIN be used?

The result set needs to contain a complete list of data in one table, as well as match the rows from an INNER JOIN

In which of the following cases is a FULL OUTER JOIN required?

To retrieve all unmatched data from two different tables

Which of the following provides reliable units of work that allow correct recovery from failures and keeps a database consistent even in cases of system failure?

Transaction

Which operator would you use to combine results from two tables, while skipping duplicate values?

UNION

Which of the following statements correctly differentiates UNION from JOIN?

UNION combines results of two SQL queries when there is the same number of columns and data types; JOIN returns rows when there is at least one column match.

Consider the following view statement:CREATE VIEW myviewASSELECT * FROM Employee WHERE Emp_loc IN ('NA', 'EUR')What can you do to ensure that the WHERE clause is not violated when changes are made through the view?

Use WITH CHECK OPTION

Which command removes an object from a SQL Server database?

DROP

Which of the following commands change the properties of an object inside a database?

ALTER

Which of the following enables a user to focus on the coding rather than the syntax for a specific database API?

Abstraction

You have created a table named Employee with the following Data Definition Language (DDL) statement:CREATE Table Employee(EmployeeID Integer NOT NULL,Name Varchar(10) NOT NULL,Address Varchar(20) NOT NULL,ContactNumber Integer,EmployeeRemarks Text)You want to create a full-text search on the Employee table. Which of the following columns can be used to create a full-text index search?Each correct answer represents a complete solution. Choose all that apply.

Address EmployeeRemarks Name

Which DML statement allows you to remove one or more rows from a table?

DELETE FROM <table name> WHERE <condition>

Which of the following statements correctly differentiates the DML command DELETE from the DDL command DROP?

DELETE is used to remove only records from the table, whereas DROP is used to remove the table.

You work as a database designer for ABC Inc. You have to design the database of the company. The database has a table named Emp with the following attributes on the Employee entity:E-mail Login and PasswordNameContactNumberDesignationDepartmentWhich of the following can be used as a primary key?

None of the attributes of the Emp table can be designated as a primary key.

A table can contain which of the following combination of primary and foreign keys?

One primary key and multiple foreign keys

Which of the following are the types of secondary XML indexes?Each correct answer represents a complete solution. Choose all that apply.

Path Value

You manage the Sales database on a SQL Server 2008 server. The database is using the full recovery model. You need to design a backup strategy to meet the following objectives:You have a large window of low activity each night for backups.Other database backups must run as quickly as possible.No more than one hour of data loss is acceptable.What should you do to meet the requirement?

Perform a full backup nightly, differential database backups twice daily, and transaction log backups hourly.