Chapter Six Flashcards
(99 cards)
1
Q
What is the purpose of join tables in SQL?
A
To retrieve data from one or more tables
2
Q
What does a SELECT query return?
A
A table
3
Q
What is a cross join?
A
A join that returns all combinations of rows from the joined tables
4
Q
How do you perform a cross join in SQL?
A
SELECT * from DEMO_INSTRUCTOR CROSS JOIN DEMO_COURSE
5
Q
What is the requirement for join compatibility between two tables?
A
There are no specific requirements on the number of rows, columns or matching columns
6
Q
Can you cross join a table with itself?
A
Yes
7
Q
What is the purpose of table name aliasing in the FROM clause?
A
To write more readable queries
8
Q
What are subqueries in SQL?
A
Queries nested inside another query
9
Q
What is a correlated subquery?
A
A subquery that references a table (alias) from the outer query
10
Q
What is a derived table?
A
A subquery used in the FROM clause
11
Q
Why should one use subqueries?
A
For performance reasons and clarity of logic
12
Q
What is the compatibility criteria for using UNION in SQL?
A
The number of columns and their data types must match
13
Q
What is the significance of knowing how many result rows to expect in a join query?
A
It helps in understanding the output of the query
14
Q
What can the operand for a FROM clause be?
A
A SELECT query
15
Q
What are the two types of subqueries mentioned?
A
- Outer Queries
- Inner Queries
16
Q
What is a non-equi join?
A
A join that does not require equality between columns
17
Q
True or False: A SELECT statement can return data from multiple tables.
A
True
18
Q
What is Middleware?
A
Software that allows an application to interoperate with other software without requiring the user to understand and code low-level operations.
Middleware facilitates communication and data management for distributed applications.
19
Q
What does API stand for?
A
Application Program Interface.
It consists of routines that an application uses to direct the performance of procedures by the computer’s operating system.
20
Q
List the common database APIs.
A
- ODBC
- ADO.NET
- JDBC
21
Q
What are the steps for using databases via Middleware APIs?
A
- Identify and register a database driver
- Open a connection to a database
- Execute a query against the database
- Process the results of the query
- Repeat steps 3-4 as necessary
- Close the connection to the database
22
Q
What is a fat client?
A
A client PC that processes presentation logic, extensive application and business rules logic, and many DBMS functions.
Fat clients often require more resources and capabilities than thin clients.
23
Q
What is a thin client?
A
An application where the client PC primarily provides the user interfaces and some application processing, usually with no or limited local data storage.
24
Q
What are the advantages of three-tier architectures?
A
- Scalability
- Technological flexibility
- Long-term cost reduction
- Better match of systems to business needs
- Improved customer service
- Competitive advantage
- Reduced risk
25
What does the term 'transaction integrity' refer to?
The notion that transactions must be atomic, consistent, isolated, and durable (ACID).
26
Define Atomic in the context of transaction integrity.
A transaction cannot be subdivided.
27
What does Consistent mean in the context of transaction integrity?
Constraints don’t change from before the transaction to after the transaction.
28
What does Isolated mean in the context of transaction integrity?
Database changes are not revealed to users until after the transaction has completed.
29
What does Durable mean in the context of transaction integrity?
Database changes are permanent.
30
What are some threats to data security?
* Human error
* Software failure
* Hardware failure
* Theft and fraud
* Loss of privacy or confidentiality
* Loss of data integrity
* Loss of availability
31
What is the W3C Web Privacy Standard?
A standard that addresses who collects data, what data is collected and for what purpose, who data is shared with, user control over data access, dispute resolution, data retention policies, and access to these policies.
32
What are the advantages of stored procedures?
* Performance improves for compiled SQL statements
* Reduced network traffic
* Improved security
* Improved data integrity
* Thinner clients
33
What are the disadvantages of stored procedures?
* Programming takes more time
* Proprietary, so algorithms are not portable
34
What is a deadlock?
An impasse that results when two or more transactions have locked common resources, and each waits for the other to unlock their resources.
35
What is optimistic concurrency control?
An approach that assumes simultaneous updates will be infrequent, allowing each transaction to attempt an update as it wishes.
36
What is serializability in concurrency control?
The practice of finishing one transaction before starting another.
37
What is a shared lock?
A lock that allows read access but no update permitted, used to prevent another user from placing an exclusive lock on the record.
38
What is an exclusive lock?
A lock that prohibits any access, used when preparing to update.
39
What are the two phases of the two-phase locking protocol?
* Growing phase
* Shrinking phase
40
What is versioning in the context of concurrency control?
An optimistic approach where each transaction can attempt an update, creating a new version of a record instead of replacing the old one.
41
What is the lost update problem?
A situation where simultaneous access causes updates to cancel each other.
## Footnote
This can lead to inconsistent read problems.
42
What is the purpose of using connection pooling?
To manage database connections efficiently as maintaining an open connection is resource-intensive.
43
What is the main function of a database server?
To host the DBMS (Database Management System).
44
What is the role of a web server?
To receive and respond to browser requests using HTTP protocol.
45
What is the function of an application server?
To provide software building blocks for creating dynamic websites.
46
What is the purpose of user authentication in client-server application security?
To verify the identity of users accessing the application.
47
How can dynamic pages be secured?
Through user authentication, session security, SSL for encryption, and restricting the number of users and open ports.
48
When EXISTS or NOT EXISTS is used in a subquery, the select list of the subquery will usually just select all columns as a placeholder because it doesn't matter which columns are returned.
True
49
The advantages of SQL-invoked routines are flexibility, efficiency, sharability, and applicability.
True
50
EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows.
undefined
1
false
* true *
51
User-defined functions can improve system performance because they will be processed as sets rather than individually, thus reducing system overhead.
False
52
The ________ operator is used to combine the output from multiple queries into a single result table.
*UNION*
DIVIDE
COLLATE
INTERSECT
53
Constraints are a special case of triggers.
False
54
A dynamic query is created by the user.
True
55
The most commonly used form of join operation is the:
equi-join.
union join.
* natural join. *
outer join
56
SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query.
union
grouping
* subquery *
joining
57
What would the following view contain for values?
Create view CustomerOrders as
Select CustID, Count(*) as TotOrders, Sum(ordertotal) as Value
From customer inner join sale on customer.customer_id = sale.customer_id;
A listing of the customer ID as well as the total orders
An error message
* A listing of the customer ID as well as the total number of orders and the total amount spent by the customer*
A listing of all customers in the customer table
58
The MERGE command:
allows one to combine the INSERT and DELETE operations.
is always a single table operation.
* allows one to combine the INSERT and UPDATE operations. *
joins 2 tables together.
59
The outer join syntax does not apply easily to a join condition of more than ________ tables.
five
three
four
* two *
60
A join operation:
causes two disparate tables to be combined into a single table or view.
is used to combine indexing operations.
* causes two tables with a common domain to be combined into a single table or view.*
brings together data from two different fields.
61
All of the following are guidelines for better query design EXCEPT:
understand how indexes are used in query processing.
* use a lot of self-joins. *
write simple queries.
retrieve only the data that you need.
62
SQL allows one to calculate linear regressions, moving averages, and correlations without moving the data outside of the database.
True
63
A ________ is a temporary table used in the FROM clause of an SQL query.
view table
trigger
* derived table *
correlated subquery
64
In which of the following situations would one have to use an outer join in order to obtain the desired results?
A report is desired that lists all customers who placed an order.
A report is desired that lists all customers and the total of their orders.
There is never a situation that requires only an outer join.
* A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero).*
65
An SQL query that implements an outer join will return rows that do not have matching values in common columns.
True
66
If the DBA wishes to describe all tables in the database, which data dictionary view should be accessed in Oracle?
*DBA_TABLES *
DBA_TABLE_LABEL
DBA_TAB_PRIVS
DBA_TAB_COMMENTS
67
One major disadvantage of the outer join is that information is easily lost.
False
68
The following code is an example of a correlated subquery.
SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState,
CustomerPostalCode
FROM Customer_T
WHERE Customer_T.CustomerID =
(SELECT Order_T.CustomerID
FROM Order_T
WHERE OrderID = 1008);
False
69
An SQL query that implements an outer join will return rows that do not have matching values in common columns.
True
70
One major disadvantage of the outer join is that information is easily lost.
False
71
An operation to join a table to itself is called a(n):
outer join.
inner join.
sufficient-join.
* self-join.*
72
The following query will execute without errors.
SELECT Customer.Customer_Name, Salesman.Sales_Quota
FROM Customer
WHERE Customer.Salesman_ID =
(SELECT Salesman_ID
WHERE Lname = 'SMITH');
False
73
A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n):
inner join.
multivariate join.
equi-join.
* natural join. *
74
A join in which the joining condition is based on equality between values in the common columns is called a(n):
natural join.
equi-join.
* both equi-join. and natural join. *
unilateral join.
75
EXISTS takes a value of false if the subquery returns an intermediate result set.
False
76
The UNION clause is used to:
find all rows that are in one table, but not the other.
find all rows that do not match in two tables.
* combine the output from multiple queries into a single result table.*
join two tables together to form one table.
77
________ use the result of the inner query to determine the processing of the outer query.
Inner subqueries
Correlated subqueries
Outer subqueries
* Subqueries *
78
SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query.
union
joining
grouping
* subquery *
79
Triggers have three parts: the event, the condition, and the action.
True
80
A new set of analytical functions added in SQL:2008 is referred to as:
MOLAP functions.
* OLAP functions. *
OLAF functions.
average functions.
81
An equi-join is a join in which one of the duplicate columns is eliminated in the result table.
False
82
All of the following are part of the coding structure for triggers EXCEPT:
event.
action.
condition.
* selection. *
83
There is a special operation in SQL to join a table to itself.
False
84
Establishing IF-THEN-ELSE logical processing within an SQL statement can now be accomplished by using the CASE keyword in a statement.
True
85
The following code would include:
SELECT Customer_T.CustomerID,CustomerName, OrderID
FROM Customer_T RIGHT OUTER JOIN Order_T ON
Customer_T.CustomerID = Order_T.CustomerID;
*all rows of the Order_T Table regardless of matches with the Customer_T Table.*
only rows that match both Customer_T and Order_T Tables.
all rows of the Customer_T Table regardless of matches with the Order_T Table.
only rows that don't match both Customer_T and Order_T Tables.
86
The following statement is an example of:
CREATE TABLE Customer_t (
CustNmbr number(11,0),
CreditLimit number(6,2),
CustStart date,
CustEnd date,
PERIOD for Custperiod(CustStart,CustEnd));
a materialized view.
* an application time period table. *
a system-versioned table.
a dynamic view.
87
The UNION clause is used to combine the output from multiple queries into a single result table.
True
88
A type of join between three tables is called a(n):
*ternary join. *
unnatural join.
pinned join.
self-join.
89
An equi-join is a join in which one of the duplicate columns is eliminated in the result table.
False
90
The ________ DBA view shows information about all users of the database in Oracle.
USERS
DBA_VIEWS
* DBA_USERS *
DBA_INDEXES
91
Figuring out what attributes you want in your query before you write the query will help with query writing.
True
92
The joining condition of an equi-join is based upon an equality.
True
93
The following code is an example of a:
SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState,
CustomerPostalCode
FROM Customer_T
WHERE Customer_T.CustomerID =
(SELECT Order_T.CustomerID
FROM Order_T
WHERE OrderID = 1008);
* subquery. *
correlated subquery.
FULL OUTER JOIN.
join.
94
In order for two queries to be UNION-compatible, they must:
both return at least one row.
both return exactly one row.
both have the same number of lines in their SQL statements.
* both output compatible data types for each column and return the same number of rows.*
95
EXISTS takes a value of false if the subquery returns an intermediate result set.
False
96
A join in which rows that do not have matching values in common columns are still included in the result table is called a(n):
equi-join.
union join.
natural join.
* outer join. *
97
It is better not to have a result set identified before writing GROUP BY and HAVING clauses for a query.
False
98
Combining a table with itself results in a faster query.
False
99
A join in which the joining condition is based on equality between values in the common column is called an equi-join.
True
