CS 204 Flashcards
(226 cards)
1
Q
A _______________ consists of tables that are linked together in some meaningful way.
A
relational database
2
Q
________________ are created using primary and foreign keys.
A
Relationships
3
Q
A _____________ is a minimal set of attributes whose values uniquely identify a record in a table.
A
primary key
4
Q
A _______________ is an attribute whose values are the same as the primary key of another table.
A
foreign key
5
Q
Relationships are characterized by their ________________: how many records in one table are related to how many records another table.
A
cardinality
6
Q
What are the four types of cardinality?
A
one-to-one
one-to-many
many-to-one
many-to-many
7
Q
__________________ are software programs that allow users to access and maintain databases.
A
Database applications
8
Q
A database __________ is a blueprint of a relational database.
A
schema
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
10
Q
What type of schema is the layout and design of the data and tables in the database?
A
logical schema
11
Q
What is it called to reduce the redundant data among the tables?
A
normalizing
12
Q
How to put a table into 1NF?
A
Break up the columns with duplication into two or more pieces that reduce duplication.
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
14
Q
2NF rules:
A
Table is in 1NF
There are no partial dependencies of any column on the primary key
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
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)
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
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.
19
Q
___________ make pictures of tables.
A
Views
20
Q
____________ create a sort order on a column
A
indexes.
M. Mm
21
Q
An automatic operation to set to a certain event:
A
trigger
22
Q
Data definition language (DDL) includes statements that modify the _____ of a database.
A
structure
23
Q
The _____ command is used to completely remove a table from the database.
A
DROP
24
Q
To build a new table from scratch, use the _____ command.
A
CREATE
25
The _____ command is used to modify columns in a table
ALTER
26
______________ allow database managers to pinpoint specific records within a database without fear of confusing those entries with similar data found elsewhere in a category.
Primary keys
27
A ______________ is a field that directly identifies another table.
foreign key
28
What is a template in SQL?
schema
29
Database schemas depend on _____ and _____.
Database Type, Intended Use
30
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?
CHAR
31
______________ lets you put in any number of characters up to the specified limit.
VARCHAR
32
permits spaces of up to more than 4 billion while taking up four bytes per entry.
INT
33
permits blanks that fit up to 65,536 different values while only taking up two bytes per entry.
SMALLINT
34
two data types in SQL that largely do the same thing in that they permit users to add data that includes decimal points.
NUMERIC and DECIMAL
35
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.
p,s
productPrice DECIMAL(6, 2),
1043.33
36
__________ can hold a value 4 bytes in size, meaning it has 7 digits of precision.
Real data
37
_____________ can hold 8 bytes, or 15 places after the decimal point.
Float data
38
______________ is similar to float, except that it allows for much larger numbers.
Double data
39
What is the number of whole numbers and number of digits shown after the decimal point of a complex number.
Precision
40
True/false data type, binary, one of two answers only
Boolean
41
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.
TIME
42
YYYY-MM-DD
year, month, and day. Make sure to use the full four year code.
DATE
43
provides a record of changes made to a specific record.
TIMESTAMP
44
__________________, 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
Binary Large Object, BLOB
45
four BLOB types:
TINYBLOB - 256 bytes
BLOB
MEDIUMBLOB
LONGBLOB - 4 gigabytes
46
SQL doesn't refer to the type as a BLOB, but with the statement ________________
varbinary(MAX)
47
Sometimes it is useful to _____________ data in SQL to make sure that useful data is ending up in the proper column of each record.
constrain
48
Specifies the fields containing the target data
SELECT
49
Identifies the specific table or tables containing the fields specified in the SELECT clause
FROM
50
Identifies the criteria to be matched for inclusion in the results
WHERE *optional
51
Establishes the sorting requirements for retrieved data
ORDER BY *optional
52
Aggregate functions
SUM
AVG
GROUP BY
53
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?
True
54
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.
True
55
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?
ORDER BY clause
56
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.
optional
57
How many columns can be used to sort the data in an ORDER BY clause?
One or more columns, with each one in either ASC or DESC order
58
_________________ are simply sub-programs, which are commonly used and re-used throughout SQL database applications for processing or manipulating data.
SQL functions
59
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?
TRIM
60
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?
DATEDIFF
61
In what scenario would you want to use the UPPER function in SQL?
The text values might be mixed-case, and you want them all in upper case
62
What do the 6 and 4 represent?
SELECT MID(userName, 6, 4) FROM users;
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
63
_________ is a substitution function; that is, it is used to display one value if another value is NULL.
NVL
64
NVL is available only in _________, not in MySQL or SQL Server
oracle
65
NVL can be used with the following data types:
String or Numeric
66
In order to ensure a NULL inside a numeric field is replaced with a string, you use the _____ keyword
TO_CHAR
67
NVL is a function used for _____
substitution
68
correctly display 99999 for NULL Zip Codes in the Customer_Contact table?
SELECT NVL(ZipCode, 99999) FROM Customer_Contact;
69
The _________ statement is like a menu. Based on the value of a given field, certain instructions are carried out
CASE
70
In the CASE statement, we use ________ and ________ to determine when and where to branch; always use an _________ to catch any exceptions
WHEN, THEN, ELSE
71
The ELSE clause in a CASE statement is _____
highly recommended
72
Required parts of the CASE statement are:
WHEN, THEN, and END required, ELSE not required but recommended
73
________ 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.
SUM
AVG
COUNT
MAX and MIN
74
key DML commands in SQL
SELECT
INSERT
UPDATE
DELETE
75
What is created or destroyed using DML?
the data itself
76
The changes made by the DML statements are managed by a group of commands known as ______________
TCL transaction control language
77
The ____________ command is used to permanently save any transaction into the database.
COMMIT
INSERT INTO Lessons VALUES('LS004', 'Service Engineering');
COMMIT;
78
The ______________ command is used to temporarily save a transaction so that you can roll back to that point whenever necessary.
SAVEPOINT
79
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.
ROLLBACK
80
Which command allows us to select a part of a string that starts from the beginning?
LEFT
81
LEFT is a useful statement when:
The text is limited in size
82
What function will return the length of the string?
LEN()
83
Which statement will show part of a string, starting from the right-most character?
RIGHT
84
Which statement will show a portion of a string, starting from a specific position?
SUBSTR()
85
If you want to get all records from two tables that are related, you can use a ___________
inner join
86
A ______________ returns ALL matching records in the left-hand table, plus the records that match in the middle (the inner join).
left outer join
87
A ______________ returns ALL matching records in the right-hand table, plus the records that match in the middle (the inner join).
right outer join
88
A _________________ returns results from both tables.
full outer join
89
A ____________ is similar to a full outer join, except that a _____________ returns all of the records into new rows.
union
90
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?
left-outer join
A left outer join will return not only purchases at all locations but locations that did not have a purchase.
91
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?
full outer
92
A _____ displays the merged data into new rows instead of columns.
union
93
An advanced SQL join that combines results from all tables is a(n) _____ join.
full outer
94
When do you perform a Join operation?
When you need to combine information from multiple tables
95
In which part of a query is a Join actually happening?
WHERE clause
96
How can you match tables to perform a Join?
Joins can be on primary-key-to-primary-key or primary-key-to-foreign-key
97
What is the business purpose of performing an SQL Join?
To dig into data to actually make sense of information and answer to business questions
98
Do we always need all the information that is combined with a Join?
No, we can select the exact information that we require
99
What is the difference between LEFT and RIGHT joins?
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
Which type of database supports SQL LEFT and RIGHT Joins?
Relational
101
Which of the following is not true of a SQL LEFT JOIN?
The query doesn't need to indicate if it is a left or right join, the database will figure it out automatically
102
an example of the structure the code would have for a LEFT join query
SELECT column_names
FROM right_table_name
LEFT JOIN left_table_name ON left_table_name.ID = right_table_name.ID
103
What is the intent of a SQL JOIN?
To create a new table by combining rows of tables that contain exact data.
104
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.
self-join
105
The ___________ clause in the statement ensures that you only get one row per value returned.
DISTINCT
106
A self-join can be used for what purpose?
Running totals
107
Which part of a self-join statement ensures that one record per criteria is returned?
DISTINCT
108
A self-join query compares data from one table to _____
the same table
109
A ____________ is a query which is defined within another SQL statement.
sub-query
110
Nested sub query
no correlation between query and sub query
111
Correlated sub query
correlation between query and sub query
112
Correlated sub-queries are normally executed ______________, depending on the number of rows returned by the main query.
many times
113
Which SQL commands can be used together with sub-queries in the same SQL statement?
SELECT
UPDATE
DELETE
INSERT
114
The correlated subquery will reference field(s) in the _________________
main query
115
It is also possible to replace correlated sub-queries with _______________ in most instances
table joins
116
The __________________ operators are used in an existence check in sub-query
EXISTS and NOT EXISTS
117
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.
MUST
118
Creating a ________ in SQL allows the user to more easily find relevant information in large datasets.
view
119
A __________________ is a view in which the results are actually stored in a table or disk; they are often not real-time.
materialized view
120
a table that doesn't store data and instead rebuilds it every time the database is queried.
virtual relational database
121
The three basic commands required to build a view are the following:
CREATE VIEW
SELECT
FROM
122
Using views to return a subset of a larger dataset can enhance _____ by hiding sensitive information.
security
123
A view ___________ how users work with data.
simplifies
124
The VIEW command creates a _____ table.
virtual
125
When you use SQL view to query a database, you're doing what?
Allowing the user to more easily find relevant information in large datasets
126
correct syntax for dropping/deleting the View Customer_View?
DROP VIEW Customer_View;
127
A view, Sales_View, has been dropped. A query still refers to this view. What will happen when the query is run?
The query will not run and display an error
128
What item(s) does the DROP VIEW command delete?
The view only
129
Statement that tells the DROP VIEW command to work only if the view is present in the database?
IF EXISTS
130
A view is a _________.
virtual table or tables
131
An _________ is a list of sequence values that imply an order to the information in the database.
index
132
Which of the following SQL commands changes existing information?
UPDATE
133
When you use a specific element in the index, you can access what information?
All of the information in the row.
134
SQL is used for which of the following?
Finding, Adding and Removing Information
135
Which of the following is a characteristic of an index in SQL?
Indexes can be traversed in either direction
stored separately from the information itself
contain the sequence number of the information.
136
What is the purpose of a UNIQUE clause in a CREATE INDEX statement?
Prevent duplicate values in a column
137
Which situation is the least optimal for an SQL index?
Table with over 30% NULL values in columns
138
When defined for a table field, this constraint ensures that the value entered cannot be NULL.
NOT NULL
139
When defined for a table field, this constraint ensures that all the values entered for this field must be unique.
UNIQUE
140
When defined for a table field, the __________ constraint ensures that the value entered is within the permissible range of values
CHECK
141
When defined for a table field, the _____________ constraint provides a default value for the field when no specific value is provided.
DEFAULT
142
A___________________ is a table that is used only for a limited time and is deleted (dropped) when the user disconnects from the database.
temporary table
143
Temporary tables ______________ be dropped upon disconnect
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
What command should be used to remove a temporary table when you are done with it?
DROP TEMPORARY TABLE
145
Which of the following is required when creating a temporary table in MySQL?
CREATE TEMPORARY TABLE
146
If you try to reference a table after the DROP TABLE command, what will happen?
Errors/program crash
147
What happens to the data in a temporary table after you use the DROP TABLE command?
It is deleted
148
Which is the correct syntax for dropping a temporary table?
DROP TABLE #orders_temp;
149
Examine the following code. What type of table is orders_temp?
DROP TABLE ##orders_temp;
global temporary
150
The _____ operator allows the tables of a database to be treated as objects in a set when performing a query.
SET
151
Which is the best analogy for JOIN in SQL?
a blending of data
152
Which is the best analogy for UNION in SQL?
Tacking extra names at the bottom of a list
153
Which would be the most appropriate reason to use a UNION vs. a JOIN?
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
_______________________ can be used to simulate the effects of multi-table insertions.
database trigger
155
Trigger SQL statement format:
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
What is the most appropriate reason to use multi-table inserts in SQL?
Reduce redundant code
157
PIVOT is used to convert _____ into _____
rows; columns
158
The UNPIVOT command transforms _____ into _____
Columns; rows
159
When UNPIVOT is executed, it restores _____ of the information affected by PIVOT.
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
The purpose of the SQL PIVOT command is to?
convert a table's information
161
A regular expression is a _______ used to define a ____________ which can then be applied to text.
string; search pattern
162
A single period (.) in a regular expression, outside of groups of other exceptions, represents what?
Any single character
163
What are regular expressions used to search?
Text
164
Given the regular expression: a{5,}
How many a's could be matched in a single instance?
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
Consider the following SQL. For best security, what type of user should Aaron Burr be?
GRANT ALL
ON tblUsers
TO 'Aaron Burr';
Database administrator
166
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?
There is a risk a hacker would use SQL injection
167
Which of the following would be the best option for preventing unauthorized access to the database?
creating stored procedures
168
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?
DROP ROLE project_x;
169
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';
Edmond Dantes will have NO access to the database
170
Adding validation to the _____ is a method of preventing SQL injection
queries
171
How is SQL Injection executed through Union query?
The attacker modifies the user field by using the sql code union to join multiple tables
172
What are some of the different ways to secure the database from an SQL Injection?
The software code has to validate the sql query before executed
173
Which of the following will execute an SQL Injection using always true?
By entering the code: 2=2
174
What is SQL Injection?
SQL queries that execute by editing the original queries
175
________________ 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.
Statistical tables; Queries
176
What is database inference?
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
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?
The accountant role and the sales role may have the same permissions
178
What can you tell about data obtained through the method of inference?
Data can be intelligently analyzed to obtain individual results
179
Random noise would allow for _____
Data to be randomized using mathematical techniques
180
Vertical partition
the columns of tables are divided into multiple tables, so that all data does not reside in one single database table.
181
Horizontal partition
different rows of data would be placed into different tables based on some criteria
182
Perturbations protect data retrieved from data mining by _____.
Changing the database so the original data cannot be accessed.
183
Transparent encryption
encrypts stored data at the database level.
184
Column level encryption
allows for each column within a database table to have a unique decryption key.
185
Symmetric encryption
uses one private key to decrypt data retrieved from the database.
186
Asymmetric encryption
requires each authorized user to have a separate, unique and private key in order to decrypt data.
187
Application-level encryption
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
Correlated sub-queries or nested sub-queries are always executed once?
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
The correlated subquery will reference field(s) in the _______________
main query
190
Which statement will correctly insert a new record into the genre table?
INSERT INTO genre (genre)
VALUES ('Rock');
191
_____ limits who gains access to the database while _____ limits what a user can access within the database.
Access authentication, view defintion
192
Horizontal partition or vertical partition allocates data to different tables based on a certain criteria?
horizontal partition
193
the best option for preventing unauthorized access to the database?
creating stored procedures
194
PIVOT is used to convert _____ into _____
rows; columns
195
UNPIVOT is used to convert _____ into _____
columns; rows
196
Can a database table contain tuples zero tuples?
True, a database table can contain zero or more rows, or tuples
197
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?
Create a view which has an identical structure to the original table and use aliases for all field names defined in French
198
Do database aliases improve efficiency?
No, unrelated
199
What is a constraint?
A condition in a table or a column
200
Before removing the table from the database, DROP TABLE _____ the data.
deletes
201
The COMMIT command ____________.
is used to permanently save any transaction into the database
202
DML includes statements that modify the _____ of a database.
data
203
_____ is one method for advanced database security.
Statistical tables with query limitations
204
What are the two main categories of database indexes?
clustered and non clustered
205
How much of the time will the order implied by an index match the physical order of the information in a database?
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
What is the SQL syntax to remove a constraint?
DROP
207
What is something a regular expression CANNOT be used for?
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
Which of the following statements does NOT allow for subqueries?
SET does not allow for subqueries.
SELECT FROM WHERE allow for subqueries
209
The _________ operator will provide totals for all the dimensions mentioned in the SQL statement.
CUBE
210
What does the acronym DBA stand for in the context of information systems?
database administrator
211
When do you perform a Join operation?
When you need to combine information from multiple tables
212
Which of the following would be the best reason to use a right outer join between an Employee table and a Work_Unit table?
To display employees and work units that may or may not have employees
213
Database schemas depend on _________ and _________.
Database type; intended use
214
ALTER TABLE Artists ADD PRIMARY KEY (artist_pk);
What does this statement do?
adds the primary key artist_pk to the Artists table
215
LEFT is a useful statement when:
the text is limited in size
216
What often sets apart SQL constraints in the code?
brackets
217
creates a unique index on the column sellerName? table = tblSeller, index = Seller
CREATE UNIQUE INDEX Seller ON tblSeller (sellerName);
218
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.
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
To drop/delete a row of data, use the _____ command.
DELETE FROM
220
MS-SQL command will check to see if the table exists before dropping?
DROP TABLE IF EXISTS customer
221
What is something a regular expression CANNOT be used for?
validating equation answers
222
Required parts of the CASE statements are:
WHEN, THEN, END
223
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?
75
224
If you want to view a maximum value from a table, in which part of the SQL statement should the subquery be placed?
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
What is data encryption?
the process of encoding messages so it can be only viewed by authorized individuals.
226
