Brainscape's Knowledge GenomeTM

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


See full index

CS 204 Database Programming > CS 204 > Flashcards

CS 204 Flashcards

1
Q

A _______________ consists of tables that are linked together in some meaningful way.

A

relational database

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

________________ are created using primary and foreign keys.

A

Relationships

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

A _____________ is a minimal set of attributes whose values uniquely identify a record in a table.

A

primary key

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

A _______________ is an attribute whose values are the same as the primary key of another table.

A

foreign key

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

Relationships are characterized by their ________________: how many records in one table are related to how many records another table.

A

cardinality

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

What are the four types of cardinality?

A

one-to-one
one-to-many
many-to-one
many-to-many

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

__________________ are software programs that allow users to access and maintain databases.

A

Database applications

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

A database __________ is a blueprint of a relational database.

A

schema

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

What type of schema displays the hardware, servers, and connections that need to be set up to install the database?

A

physical schema

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

What type of schema is the layout and design of the data and tables in the database?

A

logical schema

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

What is it called to reduce the redundant data among the tables?

A

normalizing

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

How to put a table into 1NF?

A

Break up the columns with duplication into two or more pieces that reduce duplication.

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

1NF rules:

A

Columns must have single values

Columns must have unique names

Values of a given attribute must be of the same data type

No two records (or rows) can be identical

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

2NF rules:

A

Table is in 1NF

There are no partial dependencies of any column on the primary key

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

3NF rules:

A

The table should be in the 2NF
All non-primary fields are dependent on the primary field
Transitive dependencies are removed

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

What is a a set of SQL statements that work on the database as a whole, rather than an individual table or row?

A

Data Definition Language

Common DDL statements:
CREATE (generates a new table)
ALTER (alters table)
DROP (removes a table from the database)

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

What is a set of SQL statements that act on the data within a table. Select, insert, update, and delete are elements of this language?

A

data manipulation language

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

What is a set of SQL statements that control privileges to the data in the database?

A

Data control language

Grant and revoke are elements of this language.

It’s used by database administrators.

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

___________ make pictures of tables.

A

Views

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

____________ create a sort order on a column

A

indexes.
M. Mm

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

An automatic operation to set to a certain event:

A

trigger

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

Data definition language (DDL) includes statements that modify the _____ of a database.

A

structure

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

The _____ command is used to completely remove a table from the database.

A

DROP

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

To build a new table from scratch, use the _____ command.

A

CREATE

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

The _____ command is used to modify columns in a table

A

ALTER

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

______________ allow database managers to pinpoint specific records within a database without fear of confusing those entries with similar data found elsewhere in a category.

A

Primary keys

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

A ______________ is a field that directly identifies another table.

A

foreign key

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

What is a template in SQL?

A

schema

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

Database schemas depend on _____ and _____.

A

Database Type, Intended Use

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

What data type lets you put in a specific amount of characters into a record entry, with no exceptions made for more or fewer characters than the number specified for in the parentheses?

A

CHAR

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

______________ lets you put in any number of characters up to the specified limit.

A

VARCHAR

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

permits spaces of up to more than 4 billion while taking up four bytes per entry.

A

INT

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

permits blanks that fit up to 65,536 different values while only taking up two bytes per entry.

A

SMALLINT

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

two data types in SQL that largely do the same thing in that they permit users to add data that includes decimal points.

A

NUMERIC and DECIMAL

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

After declaring each data type, however, you will have to enter values for (p,s), in which the ______ refers to the total number of digits in the number, while ______ refers to the number of spots after the decimal.

A

p,s

productPrice DECIMAL(6, 2),

1043.33

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

__________ can hold a value 4 bytes in size, meaning it has 7 digits of precision.

A

Real data

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

_____________ can hold 8 bytes, or 15 places after the decimal point.

A

Float data

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

______________ is similar to float, except that it allows for much larger numbers.

A

Double data

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

What is the number of whole numbers and number of digits shown after the decimal point of a complex number.

A

Precision

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

True/false data type, binary, one of two answers only

A

Boolean

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

What data type?

HH:MM:SS.[nnnnnnn].

H stands for hour, M stands for minute, S stands for second, and all those ns are for fractions of a second.

A

TIME

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

YYYY-MM-DD

year, month, and day. Make sure to use the full four year code.

A

DATE

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

provides a record of changes made to a specific record.

A

TIMESTAMP

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

__________________, is an SQL object data type, meaning it is a reference or pointer to an object. Typically a ________ is a file, image, video, or other large object

A

Binary Large Object, BLOB

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

four BLOB types:

A

TINYBLOB - 256 bytes
BLOB
MEDIUMBLOB
LONGBLOB - 4 gigabytes

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

SQL doesn’t refer to the type as a BLOB, but with the statement ________________

A

varbinary(MAX)

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

Sometimes it is useful to _____________ data in SQL to make sure that useful data is ending up in the proper column of each record.

A

constrain

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

Specifies the fields containing the target data

A

SELECT

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

Identifies the specific table or tables containing the fields specified in the SELECT clause

A

FROM

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

Identifies the criteria to be matched for inclusion in the results

A

WHERE *optional

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

Establishes the sorting requirements for retrieved data

A

ORDER BY *optional

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

Aggregate functions

A

SUM
AVG
GROUP BY

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

It is possible to provide column position numbers from the resultset in an ORDER BY clause of an SELECT statement instead of providing column names?

A

True

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

It is possible to provide a column name that is not a part of the resultset in an ORDER BY clause of a SELECT statement.

A

True

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

There is only one way to sort the data retrieved from a database using SQL. Which clause of the SELECT statement allows you to do that?

A

ORDER BY clause

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

It is ___________ to provide the ASC keyword as SQL assumes an ascending order sort to be the default order but you must specify the DESC keyword for those columns that you want to be sorted in a descending order.

A

optional

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

How many columns can be used to sort the data in an ORDER BY clause?

A

One or more columns, with each one in either ASC or DESC order

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

_________________ are simply sub-programs, which are commonly used and re-used throughout SQL database applications for processing or manipulating data.

A

SQL functions

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

Consider that your database tables have fields with extra spaces and padding in them. Which function should you use to ensure that these extras are not interfering with your queries?

A

TRIM

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

Which SQL function can be used when you want to get results on how many days have passed after the dates in the field, such as in a billing cycle?

A

DATEDIFF

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

In what scenario would you want to use the UPPER function in SQL?

A

The text values might be mixed-case, and you want them all in upper case

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

What do the 6 and 4 represent?

SELECT MID(userName, 6, 4) FROM users;

A

the 6 represents the number of characters from the beginning of the string to the starting point of the middle selection

4 represents the number of characters selected

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

_________ is a substitution function; that is, it is used to display one value if another value is NULL.

A

NVL

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

NVL is available only in _________, not in MySQL or SQL Server

A

oracle

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

NVL can be used with the following data types:

A

String or Numeric

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

In order to ensure a NULL inside a numeric field is replaced with a string, you use the _____ keyword

A

TO_CHAR

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

NVL is a function used for _____

A

substitution

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

correctly display 99999 for NULL Zip Codes in the Customer_Contact table?

A

SELECT NVL(ZipCode, 99999) FROM Customer_Contact;

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

The _________ statement is like a menu. Based on the value of a given field, certain instructions are carried out

A

CASE

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

In the CASE statement, we use ________ and ________ to determine when and where to branch; always use an _________ to catch any exceptions

A

WHEN, THEN, ELSE

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

The ELSE clause in a CASE statement is _____

A

highly recommended

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

Required parts of the CASE statement are:

A

WHEN, THEN, and END required, ELSE not required but recommended

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

________ function will give the totals of a field,

________ will give the average value,

_________ will count the number of occurrences and

_________________ will give the maximum and the minimum values of the given fields respectively.

A

SUM
AVG
COUNT
MAX and MIN

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

key DML commands in SQL

A

SELECT
INSERT
UPDATE
DELETE

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

What is created or destroyed using DML?

A

the data itself

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

The changes made by the DML statements are managed by a group of commands known as ______________

A

TCL transaction control language

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

The ____________ command is used to permanently save any transaction into the database.

A

COMMIT

INSERT INTO Lessons VALUES('LS004', 'Service Engineering');
COMMIT;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
78
Q

The ______________ command is used to temporarily save a transaction so that you can roll back to that point whenever necessary.

A

SAVEPOINT

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

The _______________ command restores the database to the last committed state. It is also used with the SAVEPOINT command to jump to a SAVEPOINT in a transaction.

A

ROLLBACK

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

Which command allows us to select a part of a string that starts from the beginning?

A

LEFT

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

LEFT is a useful statement when:

A

The text is limited in size

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

What function will return the length of the string?

A

LEN()

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

Which statement will show part of a string, starting from the right-most character?

A

RIGHT

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

Which statement will show a portion of a string, starting from a specific position?

A

SUBSTR()

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

If you want to get all records from two tables that are related, you can use a ___________

A

inner join

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

A ______________ returns ALL matching records in the left-hand table, plus the records that match in the middle (the inner join).

A

left outer join

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

A ______________ returns ALL matching records in the right-hand table, plus the records that match in the middle (the inner join).

A

right outer join

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

A _________________ returns results from both tables.

A

full outer join

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

A ____________ is similar to a full outer join, except that a _____________ returns all of the records into new rows.

A

union

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

Consider the following tables of data: Location and Purchases. We want to retrieve all purchases AND all locations, even if there were no purchases made at those locations. What type of SQL join is appropriate?

A

left-outer join

A left outer join will return not only purchases at all locations but locations that did not have a purchase.

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

If you need to display all records from both Purchase_Orders and Customers table and display them in columns, which type of SQL join is most appropriate?

A

full outer

92
Q

A _____ displays the merged data into new rows instead of columns.

A

union

93
Q

An advanced SQL join that combines results from all tables is a(n) _____ join.

A

full outer

94
Q

When do you perform a Join operation?

A

When you need to combine information from multiple tables

95
Q

In which part of a query is a Join actually happening?

A

WHERE clause

96
Q

How can you match tables to perform a Join?

A

Joins can be on primary-key-to-primary-key or primary-key-to-foreign-key

97
Q

What is the business purpose of performing an SQL Join?

A

To dig into data to actually make sense of information and answer to business questions

98
Q

Do we always need all the information that is combined with a Join?

A

No, we can select the exact information that we require

99
Q

What is the difference between LEFT and RIGHT joins?

A

A left join query will give the output of all rows that belong to the left table while matching only the rows on the right table that have the same values. Right joins accomplish exactly the same results, but in reverse action.

100
Q

Which type of database supports SQL LEFT and RIGHT Joins?

A

Relational

101
Q

Which of the following is not true of a SQL LEFT JOIN?

A

The query doesn’t need to indicate if it is a left or right join, the database will figure it out automatically

102
Q

an example of the structure the code would have for a LEFT join query

A

SELECT column_names

FROM right_table_name

LEFT JOIN left_table_name ON left_table_name.ID = right_table_name.ID

103
Q

What is the intent of a SQL JOIN?

A

To create a new table by combining rows of tables that contain exact data.

104
Q

A ____________ does what its name implies: it compares a table to itself. It’s not a true join, since you’re not truly connecting a table to itself.

A

self-join

105
Q

The ___________ clause in the statement ensures that you only get one row per value returned.

A

DISTINCT

106
Q

A self-join can be used for what purpose?

A

Running totals

107
Q

Which part of a self-join statement ensures that one record per criteria is returned?

A

DISTINCT

108
Q

A self-join query compares data from one table to _____

A

the same table

109
Q

A ____________ is a query which is defined within another SQL statement.

A

sub-query

110
Q

Nested sub query

A

no correlation between query and sub query

111
Q

Correlated sub query

A

correlation between query and sub query

112
Q

Correlated sub-queries are normally executed ______________, depending on the number of rows returned by the main query.

A

many times

113
Q

Which SQL commands can be used together with sub-queries in the same SQL statement?

A

SELECT
UPDATE
DELETE
INSERT

114
Q

The correlated subquery will reference field(s) in the _________________

A

main query

115
Q

It is also possible to replace correlated sub-queries with _______________ in most instances

A

table joins

116
Q

The __________________ operators are used in an existence check in sub-query

A

EXISTS and NOT EXISTS

117
Q

the EXISTS and NOT EXISTS operators are used to test for the existence of records in a sub-query and ________ be used together with an a SELECT statement.

A

MUST

118
Q

Creating a ________ in SQL allows the user to more easily find relevant information in large datasets.

A

view

119
Q

A __________________ is a view in which the results are actually stored in a table or disk; they are often not real-time.

A

materialized view

120
Q

a table that doesn’t store data and instead rebuilds it every time the database is queried.

A

virtual relational database

121
Q

The three basic commands required to build a view are the following:

A

CREATE VIEW
SELECT
FROM

122
Q

Using views to return a subset of a larger dataset can enhance _____ by hiding sensitive information.

A

security

123
Q

A view ___________ how users work with data.

A

simplifies

124
Q

The VIEW command creates a _____ table.

A

virtual

125
Q

When you use SQL view to query a database, you’re doing what?

A

Allowing the user to more easily find relevant information in large datasets

126
Q

correct syntax for dropping/deleting the View Customer_View?

A

DROP VIEW Customer_View;

127
Q

A view, Sales_View, has been dropped. A query still refers to this view. What will happen when the query is run?

A

The query will not run and display an error

128
Q

What item(s) does the DROP VIEW command delete?

A

The view only

129
Q

Statement that tells the DROP VIEW command to work only if the view is present in the database?

A

IF EXISTS

130
Q

A view is a _________.

A

virtual table or tables

131
Q

An _________ is a list of sequence values that imply an order to the information in the database.

A

index

132
Q

Which of the following SQL commands changes existing information?

A

UPDATE

133
Q

When you use a specific element in the index, you can access what information?

A

All of the information in the row.

134
Q

SQL is used for which of the following?

A

Finding, Adding and Removing Information

135
Q

Which of the following is a characteristic of an index in SQL?

A

Indexes can be traversed in either direction

stored separately from the information itself

contain the sequence number of the information.

136
Q

What is the purpose of a UNIQUE clause in a CREATE INDEX statement?

A

Prevent duplicate values in a column

137
Q

Which situation is the least optimal for an SQL index?

A

Table with over 30% NULL values in columns

138
Q

When defined for a table field, this constraint ensures that the value entered cannot be NULL.

A

NOT NULL

139
Q

When defined for a table field, this constraint ensures that all the values entered for this field must be unique.

A

UNIQUE

140
Q

When defined for a table field, the __________ constraint ensures that the value entered is within the permissible range of values

A

CHECK

141
Q

When defined for a table field, the _____________ constraint provides a default value for the field when no specific value is provided.

A

DEFAULT

142
Q

A___________________ is a table that is used only for a limited time and is deleted (dropped) when the user disconnects from the database.

A

temporary table

143
Q

Temporary tables ______________ be dropped upon disconnect

A

may not always

Although the table will eventually be dropped, if there are concurrent connections, it may not be dropped right away. It’s a good idea to drop the table in your code.

144
Q

What command should be used to remove a temporary table when you are done with it?

A

DROP TEMPORARY TABLE

145
Q

Which of the following is required when creating a temporary table in MySQL?

A

CREATE TEMPORARY TABLE

146
Q

If you try to reference a table after the DROP TABLE command, what will happen?

A

Errors/program crash

147
Q

What happens to the data in a temporary table after you use the DROP TABLE command?

A

It is deleted

148
Q

Which is the correct syntax for dropping a temporary table?

A

DROP TABLE #orders_temp;

149
Q

Examine the following code. What type of table is orders_temp?

DROP TABLE ##orders_temp;

A

global temporary

150
Q

The _____ operator allows the tables of a database to be treated as objects in a set when performing a query.

A

SET

151
Q

Which is the best analogy for JOIN in SQL?

A

a blending of data

152
Q

Which is the best analogy for UNION in SQL?

A

Tacking extra names at the bottom of a list

153
Q

Which would be the most appropriate reason to use a UNION vs. a JOIN?

A

Lots of duplicate data in the tables

UNION eliminates duplicates and is better to use if you don’t want to write all the extra code to do it.

154
Q

_______________________ can be used to simulate the effects of multi-table insertions.

A

database trigger

155
Q

Trigger SQL statement format:

A

CREATE TRIGGER trigger_Student

ON Student

AFTER INSERT

BEGIN

INSERT INTO Hostel( StudentID, FirstName, LastName, ContactNo )

SELECT INSERTED.StudentID,

INSERTED.FirstName,

INSERTED.LastName,

INSERTED.ContactNo FROM

INSERTED;

END

156
Q

What is the most appropriate reason to use multi-table inserts in SQL?

A

Reduce redundant code

157
Q

PIVOT is used to convert _____ into _____

A

rows; columns

158
Q

The UNPIVOT command transforms _____ into _____

A

Columns; rows

159
Q

When UNPIVOT is executed, it restores _____ of the information affected by PIVOT.

A

some

When UNPIVOT is executed, it restores some of the information affected by PIVOT. It cannot restore any information that was aggregated (summed or similar).

160
Q

The purpose of the SQL PIVOT command is to?

A

convert a table’s information

161
Q

A regular expression is a _______ used to define a ____________ which can then be applied to text.

A

string; search pattern

162
Q

A single period (.) in a regular expression, outside of groups of other exceptions, represents what?

A

Any single character

163
Q

What are regular expressions used to search?

A

Text

164
Q

Given the regular expression: a{5,}

How many a’s could be matched in a single instance?

A

Five or more

The numbers with brackets represent how many of the preceding character can be matched. A single number means there must be exactly that number. Two numbers represent a minimum and maximum, and a single number with a comma means there is a minimum but no maximum.

165
Q

Consider the following SQL. For best security, what type of user should Aaron Burr be?

GRANT ALL

ON tblUsers

TO ‘Aaron Burr’;

A

Database administrator

166
Q

Consider that you have a web site that connects to a sales database. Some of the SQL queries are embedded in the page. What security threat, if any, are you most likely to face?

A

There is a risk a hacker would use SQL injection

167
Q

Which of the following would be the best option for preventing unauthorized access to the database?

A

creating stored procedures

168
Q

You have just completed a project and need to take away the SELECT option for a testing team. Which SQL option will function the best?

A

DROP ROLE project_x;

169
Q

The database administrator is Edmond Dantes. What issues, if any, are present with the following SQL command?

REVOKE ALL

ON master_database

FROM ‘Edmond Dantes’;

A

Edmond Dantes will have NO access to the database

170
Q

Adding validation to the _____ is a method of preventing SQL injection

A

queries

171
Q

How is SQL Injection executed through Union query?

A

The attacker modifies the user field by using the sql code union to join multiple tables

172
Q

What are some of the different ways to secure the database from an SQL Injection?

A

The software code has to validate the sql query before executed

173
Q

Which of the following will execute an SQL Injection using always true?

A

By entering the code: 2=2

174
Q

What is SQL Injection?

A

SQL queries that execute by editing the original queries

175
Q

________________ with query limitations is one method of providing advanced database security. ________ can have limitations on the number of ‘where’ clauses so that criteria cannot be narrowed down, or in terms of inaccurate count of rows returned so that database inference methods are misled.

A

Statistical tables; Queries

176
Q

What is database inference?

A

Querying aggregate tables and analyzing data to identify individual results.

Database inference is a technique by which queries are created to select data from aggregate tables, and the data is analyzed to help identify individual data that the query may not have direct access to.

177
Q

What does it mean, when the database roles are such that, the sale’s roles have permissions that are cascaded from the accountant’s role?

A

The accountant role and the sales role may have the same permissions

178
Q

What can you tell about data obtained through the method of inference?

A

Data can be intelligently analyzed to obtain individual results

179
Q

Random noise would allow for _____

A

Data to be randomized using mathematical techniques

180
Q

Vertical partition

A

the columns of tables are divided into multiple tables, so that all data does not reside in one single database table.

181
Q

Horizontal partition

A

different rows of data would be placed into different tables based on some criteria

182
Q

Perturbations protect data retrieved from data mining by _____.

A

Changing the database so the original data cannot be accessed.

183
Q

Transparent encryption

A

encrypts stored data at the database level.

184
Q

Column level encryption

A

allows for each column within a database table to have a unique decryption key.

185
Q

Symmetric encryption

A

uses one private key to decrypt data retrieved from the database.

186
Q

Asymmetric encryption

A

requires each authorized user to have a separate, unique and private key in order to decrypt data.

187
Q

Application-level encryption

A

uses separate keys to encrypted data both at the application level and the database level. Plug-in APIs are used with this method to allow for long and short encryption keys depending on the level of security needed.

188
Q

Correlated sub-queries or nested sub-queries are always executed once?

A

correlated

Correlated sub-queries are normally executed many times, depending on the number of rows returned by the main query. The main query is executed first and if the main query has 1,000 rows, then the subquery has to be executed 1,000 times. This is the reason why correlated sub-queries should be analyzed carefully before use, especially if they act on very large SQL tables.

189
Q

The correlated subquery will reference field(s) in the _______________

A

main query

190
Q

Which statement will correctly insert a new record into the genre table?

A

INSERT INTO genre (genre)
VALUES (‘Rock’);

191
Q

_____ limits who gains access to the database while _____ limits what a user can access within the database.

A

Access authentication, view defintion

192
Q

Horizontal partition or vertical partition allocates data to different tables based on a certain criteria?

A

horizontal partition

193
Q

the best option for preventing unauthorized access to the database?

A

creating stored procedures

194
Q

PIVOT is used to convert _____ into _____

A

rows; columns

195
Q

UNPIVOT is used to convert _____ into _____

A

columns; rows

196
Q

Can a database table contain tuples zero tuples?

A

True, a database table can contain zero or more rows, or tuples

197
Q

This table has fields which are defined in French. As this is being used in an English-speaking environment, the field names and displays need to be changed to English. You are NOT allowed to change the database structure. How can this conversion be done?

A

Create a view which has an identical structure to the original table and use aliases for all field names defined in French

198
Q

Do database aliases improve efficiency?

A

No, unrelated

199
Q

What is a constraint?

A

A condition in a table or a column

200
Q

Before removing the table from the database, DROP TABLE _____ the data.

A

deletes

201
Q

The COMMIT command ____________.

A

is used to permanently save any transaction into the database

202
Q

DML includes statements that modify the _____ of a database.

A

data

203
Q

_____ is one method for advanced database security.

A

Statistical tables with query limitations

204
Q

What are the two main categories of database indexes?

A

clustered and non clustered

205
Q

How much of the time will the order implied by an index match the physical order of the information in a database?

A

It will match some of the time.

The order implied by an index will sometimes match the physical order of the information in a database. It does happen, but most of the time it won’t match.

206
Q

What is the SQL syntax to remove a constraint?

A

DROP

207
Q

What is something a regular expression CANNOT be used for?

A

validating equation answers

Since regular expressions can only look at text, it can’t actually analyze whether an equation is correct or not. It could validate the syntax of the equation, but not whether the answer was correct.

208
Q

Which of the following statements does NOT allow for subqueries?

A

SET does not allow for subqueries.

SELECT FROM WHERE allow for subqueries

209
Q

The _________ operator will provide totals for all the dimensions mentioned in the SQL statement.

A

CUBE

210
Q

What does the acronym DBA stand for in the context of information systems?

A

database administrator

211
Q

When do you perform a Join operation?

A

When you need to combine information from multiple tables

212
Q

Which of the following would be the best reason to use a right outer join between an Employee table and a Work_Unit table?

A

To display employees and work units that may or may not have employees

213
Q

Database schemas depend on _________ and _________.

A

Database type; intended use

214
Q

ALTER TABLE Artists ADD PRIMARY KEY (artist_pk);

What does this statement do?

A

adds the primary key artist_pk to the Artists table

215
Q

LEFT is a useful statement when:

A

the text is limited in size

216
Q

What often sets apart SQL constraints in the code?

A

brackets

217
Q

creates a unique index on the column sellerName? table = tblSeller, index = Seller

A

CREATE UNIQUE INDEX Seller ON tblSeller (sellerName);

218
Q

You have a table in a DBMS that satisfies 2NF. It can be considered in _____ if an attribute is related to another attribute through a second attribute.

A

3NF, A table is said to be in the 3NF if it is in 2NF and all non-primary fields are dependent on the primary field.

219
Q

To drop/delete a row of data, use the _____ command.

A

DELETE FROM

220
Q

MS-SQL command will check to see if the table exists before dropping?

A

DROP TABLE IF EXISTS customer

221
Q

What is something a regular expression CANNOT be used for?

A

validating equation answers

222
Q

Required parts of the CASE statements are:

A

WHEN, THEN, END

223
Q

A cross join is performed on two tables, Table A has 15 rows, Table B has 5. How many rows will the SQL query return?

A

75

224
Q

If you want to view a maximum value from a table, in which part of the SQL statement should the subquery be placed?

A

SELECT, The general use of a subquery within the SELECT clause is to return a single value: Sum, count, minimum/maximum values. Since a subquery within the SELECT can only return one value, this is a great place for an aggregate function like this.

225
Q

What is data encryption?

A

the process of encoding messages so it can be only viewed by authorized individuals.

226
Q
A

CS 204 Database Programming (2 decks)

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