IS380 Final

The ON UPDATE ________option allows changes to a parent table to pass onto a child table or tables.

CASCADE

Which of the following counts ONLY rows that contain a value?

COUNT

The command for creating a database is:

CREATE SCHEMA

SQL command that defines a logical table from one or more tables

CREATE VIEW

The SQL command ________ defines a logical table from one or more tables or views.

CREATE VIEW

What does the following SQL statement do? Update Product_T Set Unit_Price = 775 Where Product_ID = 7

Changes the unit price of Product 7 to 775

Subqueries can only be used in the WHERE clause.

False

The following query totals sales in state= 'MA' for each salesperson. SELECT salesperson_id, SUM(sales) FROM salesperson GROUP BY salesperson_id HAVING state = 'MA';

False

What are types of table constraints?

Foriegn Key, Primary Key, Alternate Key(UNIQUE)

What does DISTINCT do?

Gives no duplicates

What are the uses of SQL views?

Hide columns and rows Display results of computations Hide complicated SQL syntax Layer built-in functions Provide level of isolation between table data and users view of data Assign different processing permissions to different views of the same table Assign different triggers to different views of the same table

You can use the _________ operator to join two tables together.

JOIN

Adding the DISTINCT keyword to a query eliminates duplicates

True

If multiple boolean operators are used in an SQL statement, NOT is evaluated first, then AND, then OR

True

In order to find out what customers have not placed an order for a particular item, one might use the NOT qualifier along with the IN qualifier.

True

NoSQL supports schema on read.

True

The ALTER TABLE command is used to change a table definition.

True

The WHERE clause is always processed before the GROUP BY clause when both occur in a SELECT statement.

True

The asterisk (*) wildcard designator can be used to select all fields from a table, as well as in WHERE clauses when an exact match is not possible

True

The following two SQL statements will produce the same results. SELECT last_name, first_name FROM customer WHERE credit_limit > 99 AND credit_limit < 10001; SELECT last_name, first_name FROM customer WHERE credit_limit BETWEEN 100 AND 10000;

True

When a GROUP BY clause is included in an SQL statement, only those columns with a single value for each group can be included

True

The ______ clause is used to combine the output from multiple queries into a single result table.

UNION

The 5 V's of Big Data

Volume, Velocity, Variety, Veracity, Value

The ___________ clause specifies which rows are to be listed in the query results

WHERE

What clause would IN appear in?

WHERE

Hadoop

an open source program supported by the Apache Foundation that manages thousands of computers and that implements MapReduce

ORDER BY will display rows in a ______________.

in a particular order

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

natural join

We use BETWEEN and NOT BETWEEN to include/exclude a range of ___________ values.

numerical

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

outer join

Data that are never physically altered once they are added to the store are called ________ data.

periodic

Indexes are created in most RDBMS to provide rapid ________ and ________ access to base-table data

random; sequential;

Data that are detailed, current, and intended to be the single, authoritative source of all decision support applications are called ________ data.

reconciled

NULL AND NOT NULL determine whether or not data values are _______________ in that column

required

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

self-join

Enterprise data warehouse

A centralized, integrated data warehouse that is the control point and single source of all data made available to end users for decision support applications.

Hadoop Distributed File System

A highly distributed, fault-tolerant file storage system designed to manage large amounts of data at high speeds.

NoSQL

A new generation of database management systems that is not based on the traditional relational database model.

What is a correlated subquery?

A query inside another query, where the inner query refers to something in the outer query.

The ________ Boolean operator joins two or more conditions and returns results only when all conditions are true

AND

The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T LEFT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;

All of the rows available in the Customer_T table regardless of matches with the Order_T table.

The ___________ clause specifies which tables are to be used in the query.

FROM

A data mart is a(n):

A data warehouse that is limited in scope

Operational Data Store

An integrated, subject-oriented, continuously updateable, current-valued (with recent history), enterprise-wide, detailed database designed to serve operational users as they do decision support processing

5 Aggregate Functions in SQL:

count, sum, avg, max, min

Another name for a star schema is a _________________.

dimensional model

Reconciled data

Detailed, current data intended to be the single, authoritative source for all decision support applications.

Facts and this other type of table are the main components of a star schema.

Dimension tables

What clause would DISTINCT appear within?

SELECT

We use LIKE and NOT LIKE to compare ______________.

strings

The query that is enclosed in parentheses at the bottom of another query is a ___________.

subquery

Every key used to join the fact table with a dimension table should be a ________ key.

surrogate key

The Tablename.Fieldname is used to specify which ___________ each ___________ is linked to.

table, column

What does the exists/not exists function do?

tests whether or not there are any values returned to the subquery

Aggregation

the process of transforming data from a detailed level to a summary level

DROP Table statement does what?

to remove, delete

A data warehouse derives its data from:

various operational data sources

What are the wildcard characters in SQL?

* means select all (To help identify character string patterns:) __ represents a single, unspecified character in a string % represents any sequence of unspecified characters

Logical data mart

A data mart created by a relational view of a data warehouse.

A dependent data mart:

A data mart filled exclusively from an enterprise data warehouse and its reconciled data.

Dependent data mart

A data mart filled exclusively from an enterprise data warehouse and its reconciled data.

What is the purpose of the following SQL querie? ALTER TABLE Customer_T ADD (ADDRESS char(2))

Alters the customer table to add a new column called Address which will possess a char data type.

Real-time data warehouse

An enterprise data warehouse that accepts near-real-time feeds of transactional data from the systems of record, analyzes warehouse data, and in near-real-time relays business rules to the data warehouse and systems of record so that immediate action can be taken in response to business events

__________ refers to databases where the amount of data makes it unfeasible to use standard relational Database Management Systems.

Big Data

These commands are used to manage and maintain the security of a database by determining who can use it and how.

DCL (Data Control Language)

This set of commands is used as part of the physical design process to create the different components of the database before any data is inserted into them.

DDL (Data Definition Language)

This set of commands is used to update the database and read data from it.

DML (Data Manipulation Language)

Transient data

Data in which changes to existing records are written over previous records, thus destroying the previous data content.

Derived data

Data that have been selected, formatted, and aggregated for end-user decision support applications.

A class of database technology used to store textual and other unstructured data is called:

NoSQL

Big data is special in that it:

Does not require strictly pre-defined data models to be analyzed.

COUNT(*) tallies only those rows that contain a value, while COUNT counts all rows

False

The following two SQL statements will produce different results. SELECT last_name, first_name FROM customer WHERE state = 'MA' OR state = 'NY' OR state = 'NJ' OR state = 'NH' OR state = 'CT'; SELECT last_name, first_name FROM customer WHERE state IN ('MA','NY','NJ','NH','CT');

False

Rows can be grouped together by common values using the ___________ clause.

GROUP BY

The ______ refers to the level of detail presented in the fact table. It is determined by the intersection of all components of the keys connected to it (for example, a fact table that identifies sales per hour, per customer, per product, per store).

Grain

The ____________ clause acts like the WHERE clause, but for GROUP BY.

HAVING

The following clause is used to find all groups that match some specified conditions:

HAVING

The first thing we need to identify when creating a new table in SQL is:

Identify the attributes that we want to use as well as each of their characteristics.

Conformed dimension

One or more dimension tables associated with two or more fact tables for which the dimension tables have the same business meaning and primary key with each fact table.

The DDL set of commands is used during the _______ phase of the database development process.

Physical Design

Four NoSQL classifications

Key-Value stores Document stores Wide-column stores Graph-oriented database

The ___________ clause specifies which columns are to be listed in query results.

SELECT

Order of clauses/operators:

SELECT FROM WHERE GROUPBY HAVING ORDER BY

Which of the following will produce the minimum of all standard prices?

SELECT MIN(standard_price) FROM Product_V;

In SQL the ________ and ________ clauses are required, while the ________ clause is needed when only certain rows are to be retrieved or multiple tables are to be joined.

SELECT; FROM; WHERE;

The _______________ is used to construct tables, define columns and column constraints, and create relationships.

SQL CREATE TABLE statement

Star schemas can be normalized with dimensions spreading out. This schema type is called a:

Snowflake schema

What is a stored procedure?

Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters.

What result will the following SQL statement produce? SELECT AVG(standard_price) AS average FROM Product_V;

The average Standard_Price of all products in Product_V

Data warehouses are considered to be time variant because:

There is a time dimension that allows the study of trends and changes.

A named set of SQL statements that are considered when a data modification occurs are called:

Triggers

A transaction is the complete set of closely related update commands that must all be done, or none of them done, for the database to remain valid.

True

Hadoop is a good way to take a big problem and allow many computers to work on it simultaneously.

True

The content of dynamic views is generated when they are referenced

True

The following SQL statement is an example of a correlated subquery. SELECT first_name, last_name, total_sales FROM salesman s1 WHERE total_sales > all (SELECT total_sales from salesman s2 WHERE s1.salesman_id != s2.salesman_id);

True

The following queries produce the same results: SELECT 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');

True

The following query totals sales for each salesperson. SELECT salesperson_id, SUM(sales) FROM salesperson GROUP BY salesperson_id;

True

Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? INSERT INTO store values ('234 Park Street')

You must specify the fields to insert if you are only inserting some of the fields.

What is the ALTER TABLE statement?

a SQL DDL statement that is used to change the structure of an existing table. It can be used to add, remove or change columns. It also can be used to add or remove constraints

Going from a summary view to progressively lower levels of detail is called data:

drill down

The use of a set of graphical tools that provides users with multidimensional views of their data is called:

on-line analytical processing (OLAP).