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?