Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

MTA 98-364 > Database Fundamentals exam > Flashcards

Database Fundamentals exam Flashcards

1
Q

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

A

Integer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Which Statement will result in the creation of an index?

A

CREATE TABLE Employee

EmployeeID INTEGER PRIMARY KEY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

You need to store product names that vary from three to 30 characters, while minimising the storage spaced used. Which data type should you use?

A

VARCHAR(30)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

One reason to create a stored procedure is to?

A

Improve performance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

EXECUTE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

Row of data to a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

9

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

PRIMARY KEY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

Row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

WHERE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

DROP TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

Row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

Data definition language (DDL)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Data is a database is stored in:

A

Tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Denormalization is performed in order to:

A

Improve query performance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How do you make a search by unique ID more efficient?

A

An index

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Which two keys establish a relationship between two tables?

A

Foreign and Primary

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

Must return a value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Which keyword must be included in a create view statement?

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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

A

DROP VIEW EmployeeView

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

Stored procedure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

A view can be used to:

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

On which database structure does an update statement operate?

A

Table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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

A

Cascade delete

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

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

A

Data type

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

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?

A

CHAR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

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

A

An update statement does not remove rows from a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

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

A foreign key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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

A

100 rows and three columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What are three valid data manipulation language commands?

A

INSERT, DELETE, UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

First normal form requires that a database excludes

A

Repeating groups

33
Q

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?

A

UNION

34
Q

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?

A

Foreign Key

35
Q

While attending college, you accept an IT internship at a local charity. The charity needs to report on data that is related and exists in two tables. You need to establish a relationship between the data that is in the two tables.
Which constraint should you define?

A

Index key

36
Q

You need to rename a column in a database table.

Which data definition language (DDL) statement should you use?

A

ALTER

37
Q

You need to delete a database table.

Which data definition language (DDL) keyword should you use?

A

DROP

38
Q

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

A

When data becomes corrupted in the database

39
Q

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

A

GRANT SELECT

40
Q

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

A

CREATE USER

41
Q

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

A

EXECUTE

42
Q

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:

A

Empty

43
Q

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

A

Row of data to a table

44
Q

You have two tables. Each table has three rows.

How many rows will be included in the Cartesian product of these two tables?

A

9

45
Q

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

A

ALTER

46
Q

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?

A

LIKE

47
Q

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

A

All order for CustomerID 209 are deleted from the Order table

48
Q

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

A

WHERE

49
Q

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

A

Restore

50
Q

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

A

All columns

51
Q

Which two elements are required to define a column? (Choose two.)

A

A name and a data type

52
Q

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

A

An update statement does not remove rows from a table

53
Q

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

A foreign key

54
Q

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

A

create new database users

55
Q

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

A

Non-sequential

56
Q

Which statement should you use to remove a foreign key?

A

ALTER TABLE

57
Q

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?

A

No rows will be deleted from the table

58
Q

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?

A

Primary Key

59
Q

The terms bitmap, b-tree and hash refer to which type of database structure?

A

Index

60
Q

One reason to add an index is to:

A

Improve performance of select statements

61
Q

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

A

Cartesian product

62
Q

You have a Customer table and an Order table. You join the Customer table with the Order table by using the CusromerID column.
The results include:
All customers and their orders Customers who have no orders.

Which type of join do these results represent?

A

Outer join

63
Q

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

A

Role

64
Q

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

A

REVOKE SELECT ON Customer

FROM User1

65
Q

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?

A

An index

66
Q

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

A

ALTER TABLE Customer ADD

District INTEGER

67
Q

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

A

SELECT Name, Price
FROM Product
ORDER BY Price DESC

68
Q

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?

A

WHERE

69
Q

This question requires that you evaluate the underlined text to determine if it is correct. Use [indexing] to create, remove, or change database objects.

A

Data Definition Language (DDL) statements

70
Q

You need to add rows to a database table. Which SQL keyword should you use?

A

JOIN

71
Q

This question requires that you evaluate the underlined text to determine if it is correct.
The [UNION] keyword combines the results of two queries and returns only rows that appear in both result sets.

A

INTERSECT

72
Q

This question requires that you evaluate the underlined text to determine if it is correct.
You combine data from three tables into one table. The new table includes redundancy to optimize read performance.
The data in the new table has been [denormalized].

A

No change is needed

73
Q

This question requires that you evaluate the underlined text to determine if it is correct.
[Truncate] is a database term used to describe the process of applying a backup to a damaged or corrupt database.

A

Restore

74
Q

QUESTION 72
This question requires that you evaluate the underlined text to determine if it is correct. Use the [ALLOW SELECT] command to give a user permission to read the data in a table.

A

GRANT SELECT

75
Q

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?

A

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

76
Q

Ports [20 and 21] are the default ports to secure a SQL Server.

A

1433 and 1434

77
Q

You have the following SQL query:

SELECT * FROM dbo.ProAthlete
WHERE Salary > 500000

The query takes too much time to return data.
You need to improve the performance of the query.
Which item should you add to the Salary column?

A

Foreign key

78
Q

You have a table that contains information about all students in your school.
You use the [INSERT] SQL keyword to change a student’s given name in the table.

A

UPDATE

MTA 98-364 (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions