CS 204 Flashcards
A _______________ consists of tables that are linked together in some meaningful way.
relational database
________________ are created using primary and foreign keys.
Relationships
A _____________ is a minimal set of attributes whose values uniquely identify a record in a table.
primary key
A _______________ is an attribute whose values are the same as the primary key of another table.
foreign key
Relationships are characterized by their ________________: how many records in one table are related to how many records another table.
cardinality
What are the four types of cardinality?
one-to-one
one-to-many
many-to-one
many-to-many
__________________ are software programs that allow users to access and maintain databases.
Database applications
A database __________ is a blueprint of a relational database.
schema
What type of schema displays the hardware, servers, and connections that need to be set up to install the database?
physical schema
What type of schema is the layout and design of the data and tables in the database?
logical schema
What is it called to reduce the redundant data among the tables?
normalizing
How to put a table into 1NF?
Break up the columns with duplication into two or more pieces that reduce duplication.
1NF rules:
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
2NF rules:
Table is in 1NF
There are no partial dependencies of any column on the primary key
3NF rules:
The table should be in the 2NF
All non-primary fields are dependent on the primary field
Transitive dependencies are removed
What is a a set of SQL statements that work on the database as a whole, rather than an individual table or row?
Data Definition Language
Common DDL statements:
CREATE (generates a new table)
ALTER (alters table)
DROP (removes a table from the database)
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?
data manipulation language
What is a set of SQL statements that control privileges to the data in the database?
Data control language
Grant and revoke are elements of this language.
It’s used by database administrators.
___________ make pictures of tables.
Views
____________ create a sort order on a column
indexes.
M. Mm
An automatic operation to set to a certain event:
trigger
Data definition language (DDL) includes statements that modify the _____ of a database.
structure
The _____ command is used to completely remove a table from the database.
DROP
To build a new table from scratch, use the _____ command.
CREATE
The _____ command is used to modify columns in a table
ALTER
______________ 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
A ______________ is a field that directly identifies another table.
foreign key
What is a template in SQL?
schema
Database schemas depend on _____ and _____.
Database Type, Intended Use
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
______________ lets you put in any number of characters up to the specified limit.
VARCHAR
permits spaces of up to more than 4 billion while taking up four bytes per entry.
INT
permits blanks that fit up to 65,536 different values while only taking up two bytes per entry.
SMALLINT
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
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
__________ can hold a value 4 bytes in size, meaning it has 7 digits of precision.
Real data
_____________ can hold 8 bytes, or 15 places after the decimal point.
Float data
______________ is similar to float, except that it allows for much larger numbers.
Double data
What is the number of whole numbers and number of digits shown after the decimal point of a complex number.
Precision
True/false data type, binary, one of two answers only
Boolean
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
YYYY-MM-DD
year, month, and day. Make sure to use the full four year code.
DATE
provides a record of changes made to a specific record.
TIMESTAMP
__________________, 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
four BLOB types:
TINYBLOB - 256 bytes
BLOB
MEDIUMBLOB
LONGBLOB - 4 gigabytes
SQL doesn’t refer to the type as a BLOB, but with the statement ________________
varbinary(MAX)
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
Specifies the fields containing the target data
SELECT
Identifies the specific table or tables containing the fields specified in the SELECT clause
FROM
Identifies the criteria to be matched for inclusion in the results
WHERE *optional
Establishes the sorting requirements for retrieved data
ORDER BY *optional
Aggregate functions
SUM
AVG
GROUP BY
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
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
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
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
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
_________________ are simply sub-programs, which are commonly used and re-used throughout SQL database applications for processing or manipulating data.
SQL functions
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
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
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
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
_________ is a substitution function; that is, it is used to display one value if another value is NULL.
NVL
NVL is available only in _________, not in MySQL or SQL Server
oracle
NVL can be used with the following data types:
String or Numeric
In order to ensure a NULL inside a numeric field is replaced with a string, you use the _____ keyword
TO_CHAR
NVL is a function used for _____
substitution
correctly display 99999 for NULL Zip Codes in the Customer_Contact table?
SELECT NVL(ZipCode, 99999) FROM Customer_Contact;
The _________ statement is like a menu. Based on the value of a given field, certain instructions are carried out
CASE
In the CASE statement, we use ________ and ________ to determine when and where to branch; always use an _________ to catch any exceptions
WHEN, THEN, ELSE
The ELSE clause in a CASE statement is _____
highly recommended
Required parts of the CASE statement are:
WHEN, THEN, and END required, ELSE not required but recommended
________ 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
key DML commands in SQL
SELECT
INSERT
UPDATE
DELETE
What is created or destroyed using DML?
the data itself
The changes made by the DML statements are managed by a group of commands known as ______________
TCL transaction control language
The ____________ command is used to permanently save any transaction into the database.
COMMIT
INSERT INTO Lessons VALUES('LS004', 'Service Engineering');
COMMIT;
The ______________ command is used to temporarily save a transaction so that you can roll back to that point whenever necessary.
SAVEPOINT
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
Which command allows us to select a part of a string that starts from the beginning?
LEFT
LEFT is a useful statement when:
The text is limited in size
What function will return the length of the string?
LEN()
Which statement will show part of a string, starting from the right-most character?
RIGHT
Which statement will show a portion of a string, starting from a specific position?
SUBSTR()
If you want to get all records from two tables that are related, you can use a ___________
inner join
A ______________ returns ALL matching records in the left-hand table, plus the records that match in the middle (the inner join).
left outer join
A ______________ returns ALL matching records in the right-hand table, plus the records that match in the middle (the inner join).
right outer join
A _________________ returns results from both tables.
full outer join
A ____________ is similar to a full outer join, except that a _____________ returns all of the records into new rows.
union
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.
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
A _____ displays the merged data into new rows instead of columns.
union
An advanced SQL join that combines results from all tables is a(n) _____ join.
full outer
When do you perform a Join operation?
When you need to combine information from multiple tables
In which part of a query is a Join actually happening?
WHERE clause
How can you match tables to perform a Join?
Joins can be on primary-key-to-primary-key or primary-key-to-foreign-key
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
Do we always need all the information that is combined with a Join?
No, we can select the exact information that we require
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.
Which type of database supports SQL LEFT and RIGHT Joins?
Relational
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
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
What is the intent of a SQL JOIN?
To create a new table by combining rows of tables that contain exact data.
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
The ___________ clause in the statement ensures that you only get one row per value returned.
DISTINCT
A self-join can be used for what purpose?
Running totals
Which part of a self-join statement ensures that one record per criteria is returned?
DISTINCT
A self-join query compares data from one table to _____
the same table
A ____________ is a query which is defined within another SQL statement.
sub-query
Nested sub query
no correlation between query and sub query
Correlated sub query
correlation between query and sub query
Correlated sub-queries are normally executed ______________, depending on the number of rows returned by the main query.
many times
Which SQL commands can be used together with sub-queries in the same SQL statement?
SELECT
UPDATE
DELETE
INSERT
The correlated subquery will reference field(s) in the _________________
main query
It is also possible to replace correlated sub-queries with _______________ in most instances
table joins
The __________________ operators are used in an existence check in sub-query
EXISTS and NOT EXISTS
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
Creating a ________ in SQL allows the user to more easily find relevant information in large datasets.
view
A __________________ is a view in which the results are actually stored in a table or disk; they are often not real-time.
materialized view
a table that doesn’t store data and instead rebuilds it every time the database is queried.
virtual relational database
The three basic commands required to build a view are the following:
CREATE VIEW
SELECT
FROM
Using views to return a subset of a larger dataset can enhance _____ by hiding sensitive information.
security
A view ___________ how users work with data.
simplifies
The VIEW command creates a _____ table.
virtual
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
correct syntax for dropping/deleting the View Customer_View?
DROP VIEW Customer_View;
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
What item(s) does the DROP VIEW command delete?
The view only
Statement that tells the DROP VIEW command to work only if the view is present in the database?
IF EXISTS
A view is a _________.
virtual table or tables
An _________ is a list of sequence values that imply an order to the information in the database.
index
Which of the following SQL commands changes existing information?
UPDATE
When you use a specific element in the index, you can access what information?
All of the information in the row.
SQL is used for which of the following?
Finding, Adding and Removing Information
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.
What is the purpose of a UNIQUE clause in a CREATE INDEX statement?
Prevent duplicate values in a column
Which situation is the least optimal for an SQL index?
Table with over 30% NULL values in columns
When defined for a table field, this constraint ensures that the value entered cannot be NULL.
NOT NULL
When defined for a table field, this constraint ensures that all the values entered for this field must be unique.
UNIQUE
When defined for a table field, the __________ constraint ensures that the value entered is within the permissible range of values
CHECK
When defined for a table field, the _____________ constraint provides a default value for the field when no specific value is provided.
DEFAULT
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
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.
What command should be used to remove a temporary table when you are done with it?
DROP TEMPORARY TABLE
Which of the following is required when creating a temporary table in MySQL?
CREATE TEMPORARY TABLE
If you try to reference a table after the DROP TABLE command, what will happen?
Errors/program crash
What happens to the data in a temporary table after you use the DROP TABLE command?
It is deleted
Which is the correct syntax for dropping a temporary table?
DROP TABLE #orders_temp;
Examine the following code. What type of table is orders_temp?
DROP TABLE ##orders_temp;
global temporary
The _____ operator allows the tables of a database to be treated as objects in a set when performing a query.
SET
Which is the best analogy for JOIN in SQL?
a blending of data
Which is the best analogy for UNION in SQL?
Tacking extra names at the bottom of a list
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.
_______________________ can be used to simulate the effects of multi-table insertions.
database trigger
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
What is the most appropriate reason to use multi-table inserts in SQL?
Reduce redundant code
PIVOT is used to convert _____ into _____
rows; columns
The UNPIVOT command transforms _____ into _____
Columns; rows
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).
The purpose of the SQL PIVOT command is to?
convert a table’s information
A regular expression is a _______ used to define a ____________ which can then be applied to text.
string; search pattern
A single period (.) in a regular expression, outside of groups of other exceptions, represents what?
Any single character
What are regular expressions used to search?
Text
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.
Consider the following SQL. For best security, what type of user should Aaron Burr be?
GRANT ALL
ON tblUsers
TO ‘Aaron Burr’;
Database administrator
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
Which of the following would be the best option for preventing unauthorized access to the database?
creating stored procedures
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;
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
Adding validation to the _____ is a method of preventing SQL injection
queries
How is SQL Injection executed through Union query?
The attacker modifies the user field by using the sql code union to join multiple tables
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
Which of the following will execute an SQL Injection using always true?
By entering the code: 2=2
What is SQL Injection?
SQL queries that execute by editing the original queries
________________ 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
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.
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
What can you tell about data obtained through the method of inference?
Data can be intelligently analyzed to obtain individual results
Random noise would allow for _____
Data to be randomized using mathematical techniques
Vertical partition
the columns of tables are divided into multiple tables, so that all data does not reside in one single database table.
Horizontal partition
different rows of data would be placed into different tables based on some criteria
Perturbations protect data retrieved from data mining by _____.
Changing the database so the original data cannot be accessed.
Transparent encryption
encrypts stored data at the database level.
Column level encryption
allows for each column within a database table to have a unique decryption key.
Symmetric encryption
uses one private key to decrypt data retrieved from the database.
Asymmetric encryption
requires each authorized user to have a separate, unique and private key in order to decrypt data.
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.
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.
The correlated subquery will reference field(s) in the _______________
main query
Which statement will correctly insert a new record into the genre table?
INSERT INTO genre (genre)
VALUES (‘Rock’);
_____ limits who gains access to the database while _____ limits what a user can access within the database.
Access authentication, view defintion
Horizontal partition or vertical partition allocates data to different tables based on a certain criteria?
horizontal partition
the best option for preventing unauthorized access to the database?
creating stored procedures
PIVOT is used to convert _____ into _____
rows; columns
UNPIVOT is used to convert _____ into _____
columns; rows
Can a database table contain tuples zero tuples?
True, a database table can contain zero or more rows, or tuples
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
Do database aliases improve efficiency?
No, unrelated
What is a constraint?
A condition in a table or a column
Before removing the table from the database, DROP TABLE _____ the data.
deletes
The COMMIT command ____________.
is used to permanently save any transaction into the database
DML includes statements that modify the _____ of a database.
data
_____ is one method for advanced database security.
Statistical tables with query limitations
What are the two main categories of database indexes?
clustered and non clustered
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.
What is the SQL syntax to remove a constraint?
DROP
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.
Which of the following statements does NOT allow for subqueries?
SET does not allow for subqueries.
SELECT FROM WHERE allow for subqueries
The _________ operator will provide totals for all the dimensions mentioned in the SQL statement.
CUBE
What does the acronym DBA stand for in the context of information systems?
database administrator
When do you perform a Join operation?
When you need to combine information from multiple tables
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
Database schemas depend on _________ and _________.
Database type; intended use
ALTER TABLE Artists ADD PRIMARY KEY (artist_pk);
What does this statement do?
adds the primary key artist_pk to the Artists table
LEFT is a useful statement when:
the text is limited in size
What often sets apart SQL constraints in the code?
brackets
creates a unique index on the column sellerName? table = tblSeller, index = Seller
CREATE UNIQUE INDEX Seller ON tblSeller (sellerName);
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.
To drop/delete a row of data, use the _____ command.
DELETE FROM
MS-SQL command will check to see if the table exists before dropping?
DROP TABLE IF EXISTS customer
What is something a regular expression CANNOT be used for?
validating equation answers
Required parts of the CASE statements are:
WHEN, THEN, END
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
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.
What is data encryption?
the process of encoding messages so it can be only viewed by authorized individuals.
