Chapter 6

Question 1

What is SQL?

Answer 1

Structure Query Language

Question 2

What is the standard for relational database management systems (RDBMS)?

Answer 2

Stucture Query Language (SQL)

Question 3

What is a relational database management systems (RDBMS)?

Answer 3

A database management system that manages data as a collection of tables in which all relationship are represented by common values in related tables.

Question 4

What is the purpose of SQL Standard?

Answer 4

• Specify syntax/semantics for data definition and manipulation
• Define data structures and basic operations
• Enable portability of database definition and application modules
• Specify minimal (level 1) and complete (level 2) standards
• Allow for later growth/enhancement to standard (referential integrity, transcation management, user-defined functions, extended join operations, national character sets)

Question 5

What are the benefits of a standardized relational language?

Answer 5

• Reduced training costs
• Productivity
• Application portability
• Application longevity
• Reduced dependence on a single vendor
• Cross-system communication

Question 6

What is a catalog?

Answer 6

• A set of schemas that constitute the description of a database

Question 7

What is a Schema?

Answer 7

• The structure that contains descriptions of objects created by a user (base tables, views, constraints)

Question 8

What is Data Definition Language (DDL)?

Answer 8

• Commands that define a database, including creating, altering, and dropping tables and establishing constraints

Question 9

What is Data Manipulation Language (DML)?

Answer 9

• Commands that maintain and query a database

Question 10

What is Data Control Language (DCL)?

Answer 10

• Commands that control a database, including administering privileges and committing data.

Question 11

Simplified SQL environment example

Answer 11

Question 12

How DDL, DML, and DCL affect the database development process. Visual

Answer 12

Question 13

What are the major CREATE statements in Data Definition Language (DDL)?

Answer 13

• CREATE SCHEMA - defines a portion of the database owned by a particular user
• CREATE TABLE - defines a new table and its columns
• CREATE VIEW - defines a logical table from one or more tables or views

*Other CREATE statements*

CHARACTER SET, COLLATION, TRANSLATION, ASSERTION, DOMAIN

Question 14

SQL Data Types - Visual

Answer 14

Question 15

What are the steps in table creation?

Answer 15

1. Identify data types for attributes
2. Identify columns that can and cannot be null
3. Identify columns that must be unique (candidate keys)
4. Identify primary key - foreign key mates
5. Determine default values
6. Identify constraints on columns (domain specifications)
7. Create the table and associated indexes

Question 16

General Syntax for CREATE TABLE in DDL

Answer 16

Question 17

What is a constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships?

Answer 17

Referential Integrity

Question 18

What do ALTER TABLE statements allow you to do?

Answer 18

To change column specifications:

Example:

ALTER TABLE table_name alter_table_action;

Question 19

What are Table Actions?

Answer 19

Question 20

Example of adding a new column with a dafault value

Answer 20

ALTER TABLE CUSTOMER_T

ADD COLUMN CustomerType VARCHAR2 (10) DEFAULT “Commercial”;

Question 21

What command statement allows you to remove tables from your schema?

Answer 21

DROP TABLE

Example:

DROP TABLE CUSTOMER_T

Question 22

What does an insert statement do?

Answer 22

Adds one or more rows to a table

Example:

INSERT INTO Customer_t VALUES

(001, ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainsville’, ‘FL’,32601);

Question 23

What is required when inserting a record that has some null attributes?

Answer 23

It requires identifying the fields that actually get data.

Example:

INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8);

Question 24

How do you insert from another table?

Answer 24

Example:

INSERT INTO CaCustomer_T

SELECT * FROM Customer_T

WHERE CustomerState = ‘CA’;

Question 25

What does a DELETE statement do?

Answer 25

Removes rows from a table Examples: To Delete certain rows: DELETE FROM CUSTOMER\_T WHERE CUSTOMERSTATE = 'HI'; To Delete all rows DELETE FROM CUSTOMER\_T;

Question 26

What does an UPDATE statement do?

Answer 26

Modified data in existing rows. Example: UPDATE Product\_T SET ProductStandardPrice = 775 WHERE ProductID = 7;

Question 27

What does a MERGE statement do?

Answer 27

Makes it easier to update a table... allows combination of Insert and Update in one statement. Useful for updating master tables with new data Example:

Question 28

What can be done to control processing/storage efficiency with Schemas?

Answer 28

- Choice of indexes - File organizations for base tables - File organizations for indexes - Data clustering - Statistics maintenance

Question 29

Why create indexes?

Answer 29

To speed up random/sequential access to base table data Example: CREATE INDEX NAME\_IDX ON CUSTOMER\_T(CUSTOMERNAME) (This makes an index for the CUSTOMERNAME field of the CUSTOMER\_T table)

Question 30

What is the SELECT statement used for?

Answer 30

Used for queries on single or multiple tables

Question 31

What are the clauses of the SELECT statement?

Answer 31

SELECT - List the columns (and expressions) to be returned from the query FROM - Indicate the table(s) or view(s) from which data will be obtained WHERE - Indicate the conditions under which a row will be included in the result GROUP BY - Indicate categorization of results HAVING - Indicate the conditions under which a category (group) will be included ORDER BY - Sorts the result according to specfied criteria

Question 32

SQL Statement processing order, visual

Answer 32

Question 33

What is Alias?

Answer 33

An alternative column or table name Example: SELECT **CUST**.CUSTOMERNAME AS **NAME,** CUST.CUSTOMERADDRESS FROM CUSTOMER\_V **CUST** WHERE **NAME** = 'Home Furnishings';

Question 34

What does the COUNT aggregate function do?

Answer 34

Used to find totals Example: SELECT COUNT(\*) FROM ORDERLINE\_T WHERE ORDERID = 1004; \*Note: With aggregate functions you can't have single-valued columns included in the SELECT clause, unless they are included in the GROUP BY clause.

Question 35

What are the boolean operations in the SELECT FUNCTION?

Answer 35

AND, OR, and NOT Operators for customizing conditions in the WHERE clause. Example:

Question 36

What does the LIKE operator allow you to do?

Answer 36

Allows you to compare strings using wildcards. For example, the % wildcard in '%Desk' indicates that all strings that have any number of character preceding the word "Desk" will be allowed.

Question 37

What is the default processing order for boolean operators?

Answer 37

NOT, then AND, then OR

Question 38

How do you override the normal procedence of Boolean operators?

Answer 38

With parentheses example:

Question 39

What does the ORDER BY clause do?

Answer 39

Sorts the results by specified order. Example:

Question 40

What can be used with aggregate functions?

Answer 40

- Scalar aggregate - single value returned from SQL query with aggregate function - Vector aggregate - multiple values returned from SQL query with aggregate function (via GROUP BY) Example:

Question 41

What does the HAVING clause do?

Answer 41

It's used with GROUP BY, it's like a WHERE clause, but it operates on groups (categories), not on individual rows. Example:

Question 42

What are views used for?

Answer 42

They provide users controlled access to tables.

Question 43

What is a Base Table?

Answer 43

Table containing the raw data

Question 44

What are the two types of views?

Answer 44

Dynamic View Materialized View

Question 45

What is Dynamic View?

Answer 45

- A "virtual table" created dynamically upon request by a user - No data actually stored; instead data from base table made available to user - Based on SQL SELECT statement on base tables or other views

Question 46

What is materialized view?

Answer 46

- Copy or replication of data - Data actually stored - Must be refreshed periodically to match corresponding base tables

Question 47

When does the CHECK\_OPTION work?

Answer 47

Works only for updateable views and prevents updates that would create rows not included in the view.

Question 48

What are the advantage of views?

Answer 48

- Simplify query commands - Assist with data security (but don't rely on views for security, there are more important security measures) - Enhance programming productivity - Contain most current base table data - Use little storage space - Provide customized view for user - Establish physical data independence

Question 49

What are the disadvantages of views?

Answer 49

- Use processing time each time view is referenced - May or may not be directly updateable