Database Fundamentals (aioTestKing)

Lakukan tugas rumah & ujian kamu dengan baik sekarang menggunakan Quizwiz!

You are creating a table to store customer data. The AccountNumber column uses values that always consist of one letter and four digits. Which data type should you use for the AccountNumber column?

CHAR

Which statement will result in the creation of an index?

CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY)

You need to enable a new employee to authenticate to your database. Which command should you use?

CREATE USER

One difference between a function and a stored procedure is that a function:

Must return a value

Y/N: A differential backup copies only data that was changed before the last full backup

NO

What two elements are required to define a column?

A name A data type

You have a table named Student that contains 100 rows. Some of the rows have a NULL value in the FirstName column. You execute the following statement: DELETE FROM Student What is the result?

All rows in the table will be deleted

You have a table named Product that contains one million rows. You need to search for product information in the Product table by using the product's unique ID. What will make this type of search more efficient?

An index

What is one difference between an update statement and a delete statement?

An update statement does not remove rows from a table

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. When volunteers ask to be removed from mailing lists, the table must be updated. You need to use a transaction to ensure that the database has data integrity and referential integrity. Which statement should you use?

BEGIN TRANSACTION VolunteerDelete; DELETE FROM Volunteer WHERE Id = 13; COMMIT TRANSACTION VolunteerDelete;

You accept an IT internship at a local charity. The charity wants you to help them with compliance and auditing requirements. You need to ensure that a column or combination of columns uniquely identifies each row of a table. Which constraint should you define?

Primary Key

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

REVOKE SELECT ON Customer FROM User1

You need to combine the results of two queries into a single result that contains all of the rows from both queries. Which Structured Query Language (SQL) statement should you use?

UNION

Which keyword can be used in a create table statement?

UNIQUE

You have a table that contains information about all students in your school. Which SQL keyword should you use to change a student's first name in the table?

UPDATE

What defines the amount of storage space that is allocated to a value in a column?

data type

You have two tables. Each table has three rows. How many rows will be included in the Cartesian product of these two tables?

9 Cartesian means every value. So, 3 rows times 3 rows is 9.

Which two keys establish a relationship between two tables?

Foreign Primary

Use the _____________ command to give a user permission to read the data in a table.

GRANT SELECT

Which command should you use to give a user permission to read the data in a table?

GRANT SELECT

You need to store product names that vary from three to 30 characters. You also need to minimize the amount of storage space that is used. Which data type should you use?

VARCHAR(30)

Use the ______________ keyword in a SELECT statement to return rows that meet a specific condition.

WHERE

Which keyword would you use in a select statement to return rows that meet a specific condition?

WHERE

You have a table of products with fields for ProductID, Name, and Price. You need to write an UPDATE statement that sets the value in the InStock field to Yes for a specific ProductID. Which clause should you use in your update statement?

WHERE

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

INSERT

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

INSERT DELETE UPDATE

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

INSERT INTO EmployeeCopy SELECT * FROM Employee

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?

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

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?

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

You need to store product quantities, and you want to minimize the amount of storage space that is used. Which data type should you use?

INTEGER

The _______________ keyword combines the results of two queries and returns only rows that appear in both result sets.

INTERSECT

Denormalization is performed in order to:

Improve query performance

You need to store the first name, last name, and student ID for 100 students. This information will be stored in a table as:

100 rows and three columns

You work at a restaurant and they ask you to help them with a data issue. They provide you with the following recipe data: ID RecipeName PrimaryIngredient Category 1 Chicken Parm chicken entree 2 chicken strips chicken entree 3 spaghetti bacon entree 4 baconpopcorn bacon snack You need to normalize the data to third normal form. How many tables should you create?

3

A database contains two tables named Customer and Order. You execute the following statement: DELETE FROM Order WHERE CustomerID = 209 What is the result?

All orders for CustomerID 209 are deleted from the Order table

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

ALTER

You need to rename a column in a database table. Which data definition language (DDL) statement should you use?

ALTER

Which statement should you use to remove a foreign key?

ALTER TABLE

You have a table named Customer. You need to add a new column named District. What statement should you use?

ALTER TABLE Customer ADD (District INTEGER)

You execute the following statement: SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department This type of operation is called a/an:

Cartesian product

You delete rows in a table named Order. The corresponding rows in the OrderItem table are automatically deleted. This process is an example of a/an:

Cascade delete

Your class project requires that you help a charity to create a website that registers volunteers. The website must store the following data about the volunteers: Given name Surname Telephone number Email address You need to recommend a correct way to store the data. What do you recommend?

Create a table that contains columns that are named given name, surname, phone number, and email

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

Which statement deletes the rows where the employee's phone number is not entered?

DELETE FROM Employee WHERE Phone IS NULL

You need to delete a database table. Which data definition language (DDL) keyword should you use?

DROP

The ____________ command removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

DROP TABLE

Which command should you use to remove a table from a database?

DROP TABLE

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

DROP VIEW EmployeeView

Which category of SQL statements is used to add, remove, and modify database structures?

Data definition language (DDL)

You assign User1 a set of permissions that include the WITH GRANT OPTION. The WITH GRANT OPTION enables User1 to:

Delegate permissions to other users

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

EXECUTE

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:

Empty

One reason to create a stored procedure is to:

Improve performance (if two answers required: Give the user control of the query logic)

You need to retrieve data from two related database tables based on a column that exists in both tables. Which command should you use in a query?

JOIN

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?

LIKE

A view can be used to:

Limit access to specific rows or columns of data in a table

Y/N: A filtered index is a clustered index that is optimized for queries that select a small percentage of rows from a table

NO

Y/N: A transaction log backup backs up all the data in the database

NO

You have two tables. Each table has three rows. _________ rows will be included in the Cartesian product of these two tables.

Nine

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?

No rows will be deleted from the table

You have a Customer table and an Order table. You join the Customer table with the Order table by using the CustomerID column. The results include: All customers and their orders Customers who have no orders Which type of join do these results represent?

Outer Join** (I think it should be inner join)

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

PRIMARY KEY

First normal form requires that a database excludes:

Repeating groups

Which database term is used to describe the process of applying a backup to a damaged or corrupt database?

Restore

______________ is a database term used to describe the process of applying a backup to a damaged or corrupt database.

Restore

You need to establish a set of permissions that you can routinely assign to new users. What should you create?

Role

The component that holds information for a single entry in a table is called a:

Row

You need to store the contact information for each student in your school database. You should store each student's information in a:

Row

Create a query that returns a set of table data by using the _____________ statement.

SELECT

Which keyword must be included in a create view statement?

SELECT

You are writing an SQL statement to retrieve rows from a table. Which data manipulation language (DML) command should you use?

SELECT

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 COUNT (*) FROM Employee

You have a database table named SongInformation. You need to create a Structured Query Language (SQL) query to retrieve only the names of songs that sold more than 1000 compact discs (CDs). Which query should you use?

SELECT Name FROM SongInformation WHERE CDsSold > 1000

You need to list the name and price of each product, sorted by price from highest to lowest. Which statement should you use?

SELECT Name, Price FROM Product ORDER BY Price DESC

You have a table that contains product IDs and product names. You need to write an UPDATE statement to change the name of a specific product to glass. What should you include in the update statement?

SET ProductName = 'glass'

A named group of SQL statements that can be executed in a database is called a:

Stored procedure

_______________ removes all rows from a table without logging the individual row deletions.

TRUNICATE TABLE

On which database structure does an insert statement operate?

Table

On which database structure does an update statement operate?

Table

Data in a database is stored in:

Tables

At 3:00 P.M. (1500 hours), you create a backup of your database. At 4:00 P.M. (1600 hours), you create a table named Customer and import data into the table. At 5:00 P.M. (1700 hours), your server fails. You run a script to apply only the 3:00 P.M. backup to your database. What is the result of the script?

The Customer table no longer exists

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. Which statement should you use?

UPDATE Product SET ProductCategory = 43 WHERE ProductDescription = 'spoon'

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: Id GivenName 1 Tia 2 Susana When volunteer information changes, you must update the table. You need to change Tia's name to Kimberly. Which statement should you choose?

UPDATE Volunteer SET GivenName = 'Kimberly' WHERE GivenName = 'Tia'

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

When data becomes corrupted in the database

Y/N: A clustered index sorts and stores the data columns of a table or view in order, based on the clustered index key

YES

Y/N: A file or filegroup restore specifies a portion of the database to recover

YES

Y/N: A full database backup is a copy of all of the data in the entire database.

YES

Y/N: A non-clustered index is defined on a table or view by using a clustered index or heap

YES

Y/N: A unique index ensures that the index key contains no duplicate values and that every row in the table or view is unique

YES

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 foreign key

You have the following table definition: CREATE TABLE Road (RoadID INTEGER NOT NULL, Distance INTEGER NOT NULL) The Road table contains the following data: You execute the following statement: RoadID Distance 1234 22 1384 34 INSERT INTO Road VALUES (1234, 36) What is the result?

a new row in the table

The ProductID column is the primary key. The CategoryID column is a foreign key to a separate table named Category. You execute the following statement: INSERT INTO Product VALUES (3296, 'Table', 4444) What is the result?

a primary key constraint violation

You have a table named Employee that includes four columns. You execute the following statement: SELECT * FROM Employee Which columns are returned?

all columns

A clustered index improves the performance of queries on columns that ____________

are accessed sequentially

Which type of index changes the order in which the data is stored in a table?

clustered

A view can be used to _______________.

limit access to specific rows or columns of data in a table

A clustered index improves the performance of queries that _________________

return large result sets

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

row of data to a table

The user can perform ____________ actions on data that is on the server

unlimited

The user can perform ______________ actions on database objects that are on the server

unllimited


Set pelajaran terkait

1.06 Quiz: Power and Government Part 2

View Set

English in Mind 1, Unit 3: Who's your hero?, EIM 1 Unit 3

View Set

SATA practice questions exam 1 (med surg 3)

View Set