S307 Exam 2 Practice Questions

What three clauses are contained in most SQL retrieval statements?

1. SELECT (required) 2. FROM (required) 3. WHERE (necessary when only certain table rows are to be retrieved or multiple tables are to be joined)

What is a derived table? When is it used? Can you describe any situations where you would have to use it over a subquery in the WHERE clause?

A derived table can be created by placing a subquery within the FROM clause. The derived table is simply a temporary table in memory which can be accessed just like a table or view. Sometimes, these are referred to as inline views. A derived table is useful for situations where you need to create aggregate values (such as a sum) and then use these in another query. A derived table would be used instead of a subquery in cases where you need to display results from multiple tables. If the results need to come from a subselect, this is not possible, so we would have to use a derived table

What are some of the advantages and disadvantages to an SQL standard?

Advantages 1. reduced training costs 2. productivity 3. application portability 4. application longevity 5. reduced dependence on a single vendor 6. cross system communication Disadvantages 1. can stifle creativity and innovation 2. one standard is never enough to meet all needs 3. may be the offspring of compromises among many parties 4. may be difficult to change so fixing deficiencies may take considerable effort

A type of query that is placed within a WHERE or HAVING clause of another query is called a: a. master query. b. subquery. c. superquery. d. multi-query.

NOT a. master query

The role that is responsible for developing overall policies and procedures for database security is: a. Data Administration. b. Database Administration. c. Database Development. d. Accounting.

NOT b. Database Administration

By default, Hadoop replicates data ___ times (that is, it creates __ copies of the data): a. 3 b. 5 c. 4 d. 6

a. 3

This is an example of a document database: a. Couchbase b. SQL Server c. MySQL d. Oracle

a. Couchbase

You might expect to see instructions like "SELECT * FROM rx;" in which of the following Hadoop abstraction? a. Hive b. YARN c. Spark d. Pig

a. Hive

Which of the following questions is answered by the SQL statement? SELECT COUNT (Product_Description) FROM Product_T; a. How many products have product descriptions in the Product Table? b. How many different columns named "Product_Description" are there in table Product_T? c. How many characters are in the field name "Product_Description"? d. How many products are in the table Product_T?

a. How many products have product descriptions in the Product Table?

The following is true about "embedding" a relationship in a document database: a. It is terrific for read performance b. It is flexible c. Data integrity is part of the schema but reinforced through code. d. More work is done during reads in comparison to linking

a. It is terrific for read performance

All of the following are well-accepted characteristics of transactions EXCEPT: a. Persistence. b. Atomicity. c. Consistency. d. Durability.

a. Persistence

The W3C standard for Web privacy is called: a. Platform for Privacy Preferences. b. Freedom of Web Information Act. c. the Magna Carta. d. the Web privacy act.

a. Platform for Privacy Preferences

HDFS is: a. Primary storage system for Hadoop b. A programming model for Hadoop c. Written in Python d. The way Hadoop manages resources.

a. Primary storage system for Hadoop

A base table is the underlying table that is used to create views. a. True b. False

a. TRUE

A database table is defined using the data definition language (DDL). a. True b. False

a. TRUE

A materialized view is not persistent. a. True b. False

a. TRUE

The content of dynamic views is generated when they are referenced. a. True b. False

a. TRUE

The following queries produce the same results. SELECT DISTINCT Customer_Name, Customer_CityFROM Customer, SalesmanWHERE Customer.Salesman_ID = Salesman.Salesman_IDand Salesman.Lname = 'SMITH'; SELECT Customer_Name, Customer_CityFROM CustomerWHERE Customer.Salesman_ID =(SELECT Salesman_IDFROM SalesmanWHERE Lname = 'SMITH'); a. True b. False

a. TRUE

The presentation logic component of a client/server system is responsible for formatting and presenting data on the user's screen. a. True b. False

a. TRUE

The views are created by executing a CREATE VIEW SQL command. a. True b. False

a. TRUE

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. a. True b. False

a. TRUE

The client/server architectures that have evolved can be distinguished by the distribution of ________ across clients and servers. a. application logic components b. memory c. query logic components d. files

a. application logic components

A(n) ________ is a set of application routines that programs use to direct the performance of procedures by the computer's operating system. a. MOM b. API c. LAN d. RPC

b. API

Which of the following is a type of network security? a. Guidelines for frequency of password changes b. Authentication of the client workstation c. Password naming conventions d. Random password guessing

b. Authentication of the client workstation

Regarding risks related to Big Data, consider the following two statements: 1. Loss of privacy is a major risk related to Big Data. 2. Accumulation of power among "data rich" companies is a major risk related to Big Data. a. Neither statement 1 or 2 is correct b. Both statements 1 and 2 are correct c. Statement 1 is correct but statement 2 is not correct d. Statement 2 is correct but statement 1 is not correct

b. Both statements 1 and 2 are correct

A function has only input parameters but can return multiple values. a. True b. False

b. FALSE

A routine is a named set of SQL statements that are considered when a data modification occurs. a. True b. False

b. FALSE

A subquery in which processing the inner query depends on data from the outer query is called a codependent query. a. True b. False

b. FALSE

Combining a table with itself results in a faster query. a. True b. False

b. FALSE

DCL is used to update the database with new records. a. True b. False

b. FALSE

Geolocation logic is the application logic component responsible for data storage and retrieval. a. True b. False

b. FALSE

IF-THEN-ELSE logical processing cannot be accomplished within an SQL statement. a. True b. False

b. FALSE

It is better not to have a result set identified before writing GROUP BY and HAVING clauses for a query. a. True b. False

b. FALSE

Subqueries can only be used in the WHERE clause. a. True b. False

b. FALSE

The HAVING clause and the WHERE clause perform the same operation. a. True b. False

b. FALSE

The comparison operators = and != are used to establish a range of values. a. True b. False

b. FALSE

The following INSERT command would work fine:INSERT INTO budget values 121,222,111; a. True b. False

b. FALSE

The following code is an example of a Subquery.SELECT Customer_T.CustomerID, Order_T.CustomerID,CustomerName, OrderIDFROM Customer_T, Order_TWHERE Customer_T.CustomerID = Order_T. CustomerID; a. True b. False

b. FALSE

Which of the following finds all groups meeting stated conditions? a. SELECT b. HAVING c. FIND d. WHERE

b. HAVING

Which of the following will produce the minimum of all standard prices? a. SELECT Standard_Price FROM Product_V WHERE Standard_Price = min; b. SELECT MIN(Standard_Price) FROM Product_V; c. SELECT MIN(Standard_Price) FROM Product_V WHERE Standard_Price = min(Standard_Price); d. SELECT Standard_Price FROM min(Product_V);

b. SELECT MIN(Standard_Price) FROM Product_V;

Which of the following is not one of the three steps (aka phases) of Map Reduce? a. Map b. Transpose c. Shuffle (Sort) d. Reduce

b. Transpose

The ________ is the structure that contains descriptions of objects such as tables and views created by users. a. catalog b. schema c. master view d. SQL

b. schema

All of the following are part of the coding structure for triggers EXCEPT: a. event. b. selection. c. action. d. condition.

b. selection

An operation to join a table to itself is called a(n): a. sufficient-join. b. self-join. c. inner join. d. outer join.

b. self-join

What will be returned when the following SQL statement is executed? SELECT driver_no, count(*) as num_deliveriesFROM deliveriesWHERE state = 'MA'GROUP BY driver_no; a. An error message will be generated b. A count of all of the deliveries made to state = 'MA' by all drivers c. A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state d. A listing of all drivers who made deliveries to state = 'MA', sorted by driver number

c. A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state

MapReduce is: a. An abstraction of Hadoop b. The way Hadoop store files c. A programming model in Hadoop d. The manager of resources in Hadoop

c. A programming model in Hadoop

To eliminate duplicate rows in a query, the ________ qualifier is used in the SQL Select command. a. CHECK b. SPECIFIC c. DISTINCT d. ALTER

c. DISTINCT

YARN is: a. An abstraction of Hadoop b. A programming model for Hadoop c. The data operating system (resource manager) for Hadoop d. The technology Hadoop uses to store files

c. The data operating system (resource manager) for Hadoop

Most of the world's data belongs to this category: a. Imaginary b. Structured c. Unstructured d. Semi-structured

c. Unstructured

In order for a transaction to be consistent: a. it must run the same way all the time. b. it must run using the same amount of memory. c. any database constraints that must be true before the transaction must also be true after the transaction. d. it must tell the truth.

c. any database constraints that must be true before the transaction must also be true after the transaction.

Any create command may be reversed by using a(n) ________ command. a. undo b. unpack c. drop d. delete

c. drop

________ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows. a. Extents b. In c. Exists d. Having

c. exists

A type of join between three tables is called a(n): a. unnatural join. b. pinned join. c. ternary join. d. self-join.

c. ternary join

How the database results are read into memory is determined by: a. the operating system. b. None of these. c. the database driver. d. the program.

c. the database driver

An optimistic approach to concurrency control is called: a. deadlock resolution. b. denormalization. c. versioning. d. HappyControl.

c. versioning

The SQL command ________ adds one or more new columns to a table. a. CREATE VIEW b. CREATE RELATIONSHIP c. CREATE TABLE d. ALTER TABLE

d. ALTER TABLE

What does the following SQL command do? INSERT INTO Customer_T values (001,'John Smith','231 West St','Boston','MA','02115'); a. Deletes the Customer_T table b. Creates the Customer_T table c. Updates the Customer_T table d. Adds a new record to the Customer_T table

d. Adds a new record to the Customer_T table

________ is the process of assigning pieces of application code to clients or servers. a. Code distribution b. Modularizing programs c. Program breakup d. Application partitioning

d. Application partitioning

The equivalent of SQL tables in MongoDB are: a. Document b. Index c. Databases d. Collections

d. Collections

Which Java method retrieves the next row of a cursor? a. first() b. load() c. get() d. next()

d. next()

The following code is an example of a: SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState,CustomerPostalCodeFROM Customer_TWHERE Customer_T.CustomerID =(SELECT Order_T.CustomerIDFROM Order_TWHERE OrderID = 1008); a. join. b. correlated subquery. c. FULL OUTER JOIN. d. subquery.

d. subquery

A named set of SQL statements that are considered when a data modification occurs are called: a. treatments. b. trapdoors. c. stored procedures. d. triggers.

d. triggers

________ is/are any of several classes of software that allow an application to interoperate with other software without requiring the user to understand all software involved. a. Interface managers b. User interface enhancers c. Middleware d. MPP

c. Middleware

Which of the following is NOT a common distribution logic for two-tiered server environments? a. Fat client b. Thin client c. Tall client d. Distributed

c. Tall client

Which of the following is not a basic step to accessing a database from an application: a. query the database. b. register database driver. c. open a connection. d. define physical storage.

d. define physical storage

When a user creates a virtual table it is called a(n): a. materialized view. b. inline view. c. virtual table. d. dynamic view.

d. dynamic view

Traditional (relational) databases (like SQL Server) are designed around the tenets of ACID. ACID means that: a. Only one change can be written to a data field at a time, so there are no conflicting transactions made. b. The database is as fast as possible all the time. c. All data is available to all users. d. Restructions in access are randomly implemented to improve security.

a. Only one change can be written to a data field at a time, so there are no conflicting transactions made.

An INSERT command does not need to have the fields listed. a. True b. False

a. TRUE

Client/server describes a networked computing model that distributes processes between computers that request services and computers that provide services. a. True b. False

a. TRUE

Establishing IF-THEN-ELSE logical processing within an SQL statement can now be accomplished by using the CASE keyword in a statement. a. True b. False

a. TRUE

The FROM clause is the first statement processed in an SQL command. a. true b. false

a. TRUE

What result set will the following query return? SELECT Item_No, descriptionFROM itemWHERE weight > 100 and weight < 200; a. The Item_No and description for all items weighing between 101 and 199 b. The Item_No and description for all items weighing less than 100 c. The Item_No for all items weighing between 101 and 199 d. The Item_No for all items weighing more than 200

a. The Item_No and description for all items weighing between 101 and 199

What result will the following SQL statement produce? SELECT Avg(Standard_Price) as average from Product_V; a. The average Standard_Price of all products in Product_V b. The mode of all prices c. The average of all products in Product_V d. The average price of all products

a. The average Standard_Price of all products in Product_V

All of the following are additional methods of Web security EXCEPT: a. blacklist bad users. b. restrict access to the Web server. c. restrict the number of users on the Web server. d. remove unneeded programs.

a. blacklist bad users

The actions that must be taken to ensure data integrity is maintained during multiple simultaneous transactions are called ________ actions. a. concurrency control b. transaction authorization c. logging d. multiple management

a. concurrency control

The benefits of a standardized relational language include: a. cross-system communication. b. application turnover. c. increased training costs. d. confusing the SQL programmers.

a. cross-system communication

A client PC that is responsible for processing presentation logic, extensive application and business rules logic, as well as many DBMS functions is called a: a. fat client. b. file server. c. database server. d. file processor.

a. fat client

the language of business is value

and the currency is relationships

Specifying the attribute names in the SELECT statement will make it easier to find errors in queries and also correct for problems that may occur in the base system. a. True b. False

b. FALSE

What result set is returned from the following query? SELECT Customer_Name, telephoneFROM customersWHERE city in ('Boston','New York','Denver'); a. The Customer_Name of all customers living in Boston, New York or Denver b. The Customer_Name and telephone of all customers living in either Boston, New York or Denver c. The Customer_Name and telephone of all customers d. The Customer_Name and telephone of all customers living in Boston and New York and Denver

b. The Customer_Name and telephone of all customers living in either Boston, New York or Denver

The reason that a developer can insert documents with different formats in the same collection in MongoDB is because: a. The DBA can define multiple schemas in advance b. There is no rigid schema defined a priori for a collection c. An equivalency process moves data into the appropriate fields in the original schema d. For every new document a new set of columns is added to the original schema

b. There is no rigid schema defined a priori for a collection

The fact that data can take multiple formats, including structured, semi-structured, and unstructured refers to the following "V" of Big Data: a. Velocity b. Variety c. Volume d. Vividness

b. Variety

A computer that provides database storage and access in a client/server environment is called a(n): a. file server. b. database server. c. info server. d. cloud data store.

b. database server

One major advantage of the outer join is that: a. information's data type changes. b. information is not lost. c. information is easily accessible. d. the query is easier to write.

b. information is not lost

The extent of the database resource that is included with each lock is called the level of: a. TIO. b. lock granularity. c. impact. d. management.

b. lock granularity

All of the following are advantages of stored procedures EXCEPT: a. performance improves for compiled SQL statements. b. portability. c. network traffic deceases. d. thinner client.

b. portability

The role responsible for administering database security is: a. Developers. b. Security. c. Database Administration. d. Data Administration.

c. Database Administration

A document database schema design is based on: a. Static, uniform scalar data b. Rectangles (tables) c. Flexible, rich shapes d. Low-level, physical representation.

c. Flexible, rich shapes

Security measures for dynamic Web pages are different from static HTML pages because: a. static Web pages contain more sensitive data. b. HTML is more complex than dynamic Web pages. c. the connection requires full access to the database for dynamic pages. d. dynamic Web pages are built "on the fly."

c. the connection requires full access to the database for dynamic pages.

What does the following SQL statement do? DELETE FROM Customer_TWHERE state = 'HI'; a. Deletes all records from the Customer_T table b. Doesn't delete anything because of a syntax error c. Removes the Customer_T table from the database d. Deletes all records from Customer_t where the state is equal to HI

d. Deletes all records from Customer_t where the state is equal to HI

Hadoop is good for: a. Interactive use b. Intensive calculations with little data c. Processing lots of small files d. Managing high volume of data

d. Managing high volume of data

MongoDB and other document databases are particular good for Agile development because: a. Document databases should not be used in Agile development b. The database keeps automatically updating the old schema as the application is developed. c. The application is constrained so that there is only acceptable data early on. d. They can easily evolve over time as the application changes since there is no rigid structure

d. They can easily evolve over time as the application changes since there is no rigid structure

The following is a true statement about how organizations should see the emergence of different types of databases: a. They should wait until a model emerges victorious b. The should remain focused only on traditional RDBMS, since the other types of databases are likely fads. c. The should bet all their chips in NoSQL databases d. They should consider a portfolio composed by different database technologies

d. They should consider a portfolio composed by different database technologies

The following is NOT a correct statement about Hadoop: a. Open source software project b. Reliable through replication c. Optimized for parallelism d. Written in Python

d. Written in Python

The following code would include:SELECT Customer_T.CustomerID,CustomerName, OrderIDFROM Customer_T LEFT OUTER JOIN Order_T ONCustomer_T.CustomerID = Order_T.CustomerID; a. all rows of the Order_T Table regardless of matches with the Customer_T Table. b. only rows that match both Customer_T and Order_T Tables. c. only rows that don't match both Customer_T and Order_T Tables. d. all rows of the Customer_T Table regardless of matches with the Order_T Table.

d. all rows of the Customer_T Table regardless of matches with the Order_T Table.