Chapter 6 Flashcards
What is SQL?
Structure Query Language
What is the standard for relational database management systems (RDBMS)?
Stucture Query Language (SQL)
What is a relational database management systems (RDBMS)?
A database management system that manages data as a collection of tables in which all relationship are represented by common values in related tables.
What is the purpose of SQL Standard?
- 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)
What are the benefits of a standardized relational language?
- Reduced training costs
- Productivity
- Application portability
- Application longevity
- Reduced dependence on a single vendor
- Cross-system communication
What is a catalog?
- A set of schemas that constitute the description of a database
What is a Schema?
- The structure that contains descriptions of objects created by a user (base tables, views, constraints)
What is Data Definition Language (DDL)?
- Commands that define a database, including creating, altering, and dropping tables and establishing constraints
What is Data Manipulation Language (DML)?
- Commands that maintain and query a database
What is Data Control Language (DCL)?
- Commands that control a database, including administering privileges and committing data.
Simplified SQL environment example
How DDL, DML, and DCL affect the database development process. Visual
What are the major CREATE statements in Data Definition Language (DDL)?
- 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
SQL Data Types - Visual
What are the steps in table creation?
- Identify data types for attributes
- Identify columns that can and cannot be null
- Identify columns that must be unique (candidate keys)
- Identify primary key - foreign key mates
- Determine default values
- Identify constraints on columns (domain specifications)
- Create the table and associated indexes
General Syntax for CREATE TABLE in DDL
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?
Referential Integrity
What do ALTER TABLE statements allow you to do?
To change column specifications:
Example:
ALTER TABLE table_name alter_table_action;
What are Table Actions?
Example of adding a new column with a dafault value
ALTER TABLE CUSTOMER_T
ADD COLUMN CustomerType VARCHAR2 (10) DEFAULT “Commercial”;
What command statement allows you to remove tables from your schema?
DROP TABLE
Example:
DROP TABLE CUSTOMER_T
What does an insert statement do?
Adds one or more rows to a table
Example:
INSERT INTO Customer_t VALUES
(001, ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainsville’, ‘FL’,32601);
What is required when inserting a record that has some null attributes?
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);
How do you insert from another table?
Example:
INSERT INTO CaCustomer_T
SELECT * FROM Customer_T
WHERE CustomerState = ‘CA’;
What does a DELETE statement do?
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;
What does an UPDATE statement do?
Modified data in existing rows.
Example:
UPDATE Product_T
SET ProductStandardPrice = 775
WHERE ProductID = 7;
What does a MERGE statement do?
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:
What can be done to control processing/storage efficiency with Schemas?
- Choice of indexes
- File organizations for base tables
- File organizations for indexes
- Data clustering
- Statistics maintenance
Why create indexes?
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)
What is the SELECT statement used for?
Used for queries on single or multiple tables
What are the clauses of the SELECT statement?
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
SQL Statement processing order, visual
What is Alias?
An alternative column or table name
Example:
SELECT CUST.CUSTOMERNAME AS NAME, CUST.CUSTOMERADDRESS
FROM CUSTOMER_V CUST
WHERE NAME = ‘Home Furnishings’;
What does the COUNT aggregate function do?
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.
What are the boolean operations in the SELECT FUNCTION?
AND, OR, and NOT Operators for customizing conditions in the WHERE clause.
Example:
What does the LIKE operator allow you to do?
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.
What is the default processing order for boolean operators?
NOT, then AND, then OR
How do you override the normal procedence of Boolean operators?
With parentheses
example:
What does the ORDER BY clause do?
Sorts the results by specified order.
Example:
What can be used with aggregate functions?
- 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:
What does the HAVING clause do?
It’s used with GROUP BY, it’s like a WHERE clause, but it operates on groups (categories), not on individual rows.
Example:
What are views used for?
They provide users controlled access to tables.
What is a Base Table?
Table containing the raw data
What are the two types of views?
Dynamic View
Materialized View
What is Dynamic View?
- 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
What is materialized view?
- Copy or replication of data
- Data actually stored
- Must be refreshed periodically to match corresponding base tables
When does the CHECK_OPTION work?
Works only for updateable views and prevents updates that would create rows not included in the view.
What are the advantage of views?
- 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
What are the disadvantages of views?
- Use processing time each time view is referenced
- May or may not be directly updateable
