CS 350 Chapter 6

Boolean and logical operators:

- AND: joins two or more conditions and returns results only when all conditions are true - OR: joins two or more conditions and returns results when any condition are true - NOT: negates an expression

Standard functions:

- Mathematical: MIN, MAX, COUNT, SUM, ROUND, TRUNC, MOD - String: LOWER (changes all to lower case), UPPER (changes all to upper case), INITCAP (changes to only initial cap), CONCAT, SUBSTR (isolates certain character positions), COALESCE (findinf first not NULL value in a list of columns) - Date: NEXT_DAY, ADD_MONTHS, MONTHS_BETWEEN - Analytical: TOP (find the top n values in a set)

Pros of dynamic views:

- simplified query commands - help provide data security and confidentiality - improve programmer productivity - contain most current base table data - use little storage space - provide a customized view for a user - establish physical data independence

Cons of dynamic views:

- use processing time re-creating the view each time it is referenced - may or may not be directly updateable

Steps to follow when preparing to create a table

1. Identify the appropriate data type 2. Identify the columns that should accept null values 3. Identify columns that need to be unique 4. Identify all primary key-foreign key mates 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 7. Create the table and any desired indexes

Three types of SQL commands:

1. data definition language (DDL) 2. data manipulation definition (DML) 3. data control language (DCL)

Benefits of standardized relational language:

1. reducing training costs 2. productivity 3. application portability 4. application longevity 5. reduced dependence on a single vendor 6. cross-system communication

What were the original purposes of the SQL standard?

1. specify syntax and semantics 2. to define the data structures and basic operations for designing, accessing, maintaining, controlling, and protecting a SQL database 3. to provide a vehicle for portability 4. to specify both minimal and complete standards 5. to provide an initial standard

Basic syntax for creating a database:

CREATE SHEMA database_name; AUTHORIZATION owner_user id

ALTER TABLE table_name alter_table_action;

Command that alters a table to add new columns, alter existing columns, add or drop constraints

DELETE FROM Customer_T;

Command that deletes all rows of a table

DELETE FROM Customer_T WHERE CustomerState = 'HI';

Command that deletes rows that meet a certain criterion from the Customer table

DROP TABLE Customer_T;

Command that drops a table from a database schema

UPDATE Product_T SET ProductStandardPrice = 775 WHERE ProductID = 7;

Command that modifies a value in a record in a table

INSERT INTO CaCustomer_T SELECT * FROM Customer_T WHERE CustomerState = 'CA';

Command that populates a table by using a subset of another table with the same structure

CREATE INDEX Name_IDX ON Customer_T (CustomerName);

Command to create an alphabetical index on customer name in the Customer table

INSERT INTO Customer_T VALUES (xxx, 000, mm/dd/yyy, SS)

Command to insert a row of data into a table where a value will be inserted for every attribute

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

Command to insert a row of data into a table where some attributes are left null

DROP INDEX Name_IDX;

Command to remove the index on the customer name in the Customer table

Data definition language (DDL)

Commands used to define a database, including those for creating, altering, and dropping tables and establishing constraints

Materialized view

Copies or replicas of data, based on SQL queries created in the same manner as dynamic views. However, a materialized view exists as a table and thus care must be taken to keep it synchronized with its associated base tables

SELCT CustomerState, COUNT (CustomerState) FROM Customer_T GROUP BY CustomerState;

Count the number of customers with addresses in each state to which we ship

CREATE VIEW

Defines a logical table from one or more tables or views

CREATE TABLE

Defines a new table and its columns

SELECT ProductStandardPrice - PriceAvg AS Difference FROM Product_T, (SELECT AVG(ProductStandardPrice) AS PriceAvg FROM Product_T);

Display for each product the difference between its standard price and the overall average standard price of all products

Not equal to:

<> or !=

Relational DBMS

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

Catalog

A set of schemas that, when put together, constitute a description of a database

Scalar aggregate

A single value returned from a SQL query that includes an aggregate function

Schema

A structure that contains descriptions of objects created by a user, such as base tables, views, and constraints, as part of a database.

Virtual table

A table constructed automatically as needed by a DBMS. Virtual tables are not maintained as real data

Base table

A table in the relational data model containing the inserted raw data. Base tables correspond to the relations that are identified in the database's conceptual schema

Dynamic view

A virtual table that is created dynamically upon request by a user. A dynamic view is not a temporary table. Rather, its definition is stored in the system catalog, and the contents of the view are materialized as a result of a SQL query that uses the view. It differs from a materialized view, which may be stored on a disk and refreshed at intervals or when used, depending on the RDBMS

SELECT MIN (ProductDescription) FROM Product_T;

Alphabetically, what is the first product name in the Product Table?

SELECT CustomerState, COUNT (CustomerState) FROM Customer_T GROUP BY CustomerState HAVING COUNT (CustomerState) > 1;

Find only states with more than one customer

SELECT COUNT (*) FROM OrderLine_T WHERE OrderID = 1004;

How many different items were ordered on order number 100?

FROM

Identifies the tables, derived tables, or views from which columns can be chosen to appear in the result table and includes the tables, derived tables, or views needed to join tables to process the query

WHERE

Includes the conditions for row selection within the items in the FROM clause and the conditions between tables, derived tables, or views for joining

Data manipulation language (DML)

Known as the core commands of SQL. These commands are used to maintain and query a database, including those for updating, inserting, modifying, and querying data.

SELECT ProductStandardPrice, ProductDescription, ProductID FROM Product_T;

List the unit price, product name, and product ID for all products in the Product table

SELECT

Lists the columns from base tables, derived tables, or views to be projected into the table that will be the result of the command

Vector aggregate

Multiple values returned from a SQL query that includes an aggregate function

Data control language (DCL)

These commands help control the database, including those for administering privileges and committing (saving) data.

CREATE SCHEMA

Used to define the portion of a database that a particular user owns. Schemas are dependent on a catalog and contain schema objects

SELECT ProductID, ProductStandardPrice, ProductStandardPrice*1.1 AS Plus10Percet FROM Product_T;

What are the standard price and standard price if increased by 10%?

SELECT CUST.CustomerName AS Name, CUST.CustomerAddress FROM ownerid.Customer_T As Cust WHERE Name = 'Home Furnishings';

What is the address of the customer named Home Furnishings? Use an alias, Name, for the customer name

SELECT AVG (ProductStandardPrice) AS AveragePrice FROM Product_T;

What is the average standard price for all products in inventory?

SELECT ProductDescription, ProductStandardPrice FROM Product_T WHERE ProductStandardPrice < 275;

Which prodcuts have a standard price of less than $275?