S307 EXAM TWO

What are some tips for developing queries?

- gain familiarity with the data model, entity, and attributes that you are querying -think about the results you want from the query -decide what attributes you want the query to return -add those such attributes into the FROM clause -determine which columns in each table from the ERD will be used to make relationships and use them in the WHERE clause -once that works, you can add GROUP BY and HAVING

The SQL command ________ adds one or more new columns to a table.

ALTER TABLE

________ is a set of commands used to update and query a database.

DML

What does the following SQL statement do? DELETE FROM Customer_TWHERE state = 'HI';

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

The advantages of SQL-invoked routines are flexibility, efficiency, sharability, and applicability.

True

Indexes are created in most RDBMSs to:

provide rapid random and sequential access to base-table data.

What is the difference between a trigger and a routine?

A trigger waits on the database server for an event to happen once it is written and compiled. Triggers are typically tied to a table, as well as an action. A routine must be called from the database server. It can also access multiple tables to produce the results.

What does the following SQL command do? INSERT INTO Customer_T values (001,'John Smith','231 West St','Boston','MA','02115');

Adds a new record to the Customer_T table

What does the following SQL statement do? ALTER TABLE Customer_TADD (Type Varchar (2));

Alters the Customer_T table by adding a field called "Type"

User-defined functions can improve system performance because they will be processed as sets rather than individually, thus reducing system overhead.

False

The outer join syntax does not apply easily to a join condition of more than ________ tables.

two

Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by:

using the CASE key word in a statement

In order to update data in SQL, one must inform the DBMS which relation, columns, and rows are involved.

True

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));

an application time period table

Which of the following is a technique for optimizing the internal performance of the relational data model?

clustering data

The benefits of a standardized relational language include:

cross-system communication

One major advantage of the outer join is that:

information is not lost

Multiple values returned from an SQL query that includes an aggregate function are called:

vector aggregates

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 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

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 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).

The main concept of relational databases was published in 1970 by:

EF Codd

________ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows.

Exists

Combining a table with itself results in a faster query.

False

DCL is used to update the database with new records.

False

EXISTS takes a value of false if the subquery returns an intermediate result set.

False

In databases, null values are equivalent to zero.

False

Indexes generally slow down access speed in most RDMS.

False

One major disadvantage of the outer join is that information is easily lost.

False

SQL has been implemented only in the mainframe and midrange environments.

False

The CREATE SCHEMA DDL command is used to create a table.

False

The DELETE TABLE DDL command is used to remove a table from the database.

False

The ORDER BY clause is the first statement processed in an SQL command.

False

Which of the following questions is answered by the SQL statement? SELECT COUNT (Product_Description) FROM Product_T;

How many products have product descriptions in the Product Table?

Discuss some of the SQL:2011 and SQL:2016 enhancements and extensions to SQL.

In SQL:2008; several built-in functions were added such as ceiling, floor, SQRT, rank, dense_rank, rollup, cube, sample, and window. Three new data types (BIGINT, MULTISET, and XML) were added and the merge operation was added.

When is it better to use a subquery over using a join?

Joining is useful when data from several tables need to be retrieved. Also, when there is no nesting. For nested relationships, it is best to use a subquery.

A correlated subquery is executed once for each iteration through the outer loop.

True

A database table is defined using the data definition language (DDL).

True

A join in which the joining condition is based on equality between values in the common column is called an equi-join.

True

A materialized view is not persistent.

True

A natural join is the same as an equi-join, except that it is performed over matching columns that have been defined with the same name, and one of the duplicate columns is eliminated.

True

An SQL query that implements an outer join will return rows that do not have matching values in common columns.

True

Figuring out what attributes you want in your query before you write the query will help with query writing.

True

Some DBMS can handle graphic data types as well as text and numbers.

True

The FROM clause is the first statement processed in an SQL command.

True

The following queries produce the same results. SELECT DISTINCT Customer_Name, Customer_City FROM Customer, Salesman WHERE Customer.Salesman_ID = Salesman.Salesman_ID and Salesman.Lname = 'SMITH'; SELECT Customer_Name, Customer_City FROM Customer WHERE Customer.Salesman_ID = (SELECT Salesman_IDFROM Salesman WHERE Lname = 'SMITH');

True

DDL is typically used during which phase of the development process?

physical design

All of the following are part of the coding structure for triggers EXCEPT:

selection

An operation to join a table to itself is called a(n):

self-join

A type of query that is placed within a WHERE or HAVING clause of another query is called a:

subquery

What steps should be followed when preparing to create a table?

The steps to preparing to create a table include: 1. identify the appropriate data type, including length, precision, and scale, if required, for each attribute. 2. identify the columns that should not accept null values 3. identify the columns that need to be unique 4. identify all primary key - foreign key connections 5. determine values to be inserted in any columns for which a default value is desired 6. identify any columns for which domain specifications may be stated that are more constrained than those established by data type