1. Introduction to SQL
   SQL meets ideal database language requirements because it is a data definition language and has a data manipulation language, as well as it is fairly easy to learn. ANSI/ISO has described a standard SQL. Even so, there are many dialects of the SQL language.
2. Data Definition Commands
   
   
   1. The DataBase Model
      Model we will follow is simple but since this is notes not needed to be reproduced now.
   2. The Tables and Their Components
      again, we have details about the sample data not necessary to take notes on.
   3. Creating the Database and Table Structures
      To create a data base we use the following structure: CREATE SCHEMA AUTHORIZATION <creator>;
      example Jones is creator CREATE SCHEMA AUTHORIZATION JONES;
   4. Creating Table Structures
      With the creation of the database we need to create tables. We will need to determine the components of the tables and what type of data they are (date, number, string). To do this we would use the CREATE TABLE command, the format would look like this (note: we would have as many attributes as needed).
      CREATE TABLE <table name>(
      <attribute1 name and attribute1 characteristics,
      attribute2 name and attribute2 characteristics,
      attribute3 name and attribute3 characteristics,
      primary key designation,
      foreign key designation and foreign key requirements>);
      Though we use one line per a attribute, it is not necessary as long as commas are there. It makes it easier to read and debug if done this way. If a table is using a foreign key, the table that it refers to should be created first. Sample table creation

      
      CREATE  TABLE VENDOR(
      V_CODE            INTEGER             NOT NULL     UNIQUE,
      V_NAME           VARCHAR(35)    NOT NULL,
      V_CONTACT    VARCHAR(15)    NOT NULL,
      V_AREACODE CHAR(3)              NOT NULL,
      V_PHONE         CHAR(8)              NOT NULL,
      V_STATE         CHAR(2)              NOT NULL,
      V_ORDER        CHAR(1)              NOT NULL,
      PRIMARY KEY (V_CODE);
      

      
      Foregin keys would be created in another table and refered back to this one. Example: FOREIGN KEY (V_Code) REFERENCES VENDOR if this had been vendor table. NOT NULL means the item has to have a value to it, UNIQUE means it has to be a unique value in the table. Anything in () defines the values that are made. For example V_CODE is one character. It is not a good idea to give the column names (the attributes) anything that has a mathematical symbol in it. They can cause confusion.
   5. Using Domains
      Domains are permissable sets of values that can be in a row/column combination. We use the CREATE DOMAIN command to do this.
      CREATE DOMAIN <domain_name> AS DATA_TYPE
      [DEFAULT <default_value>]
      [CHECK (<condition>)]
      
      Example marital status:
      CREATE DOMAIN MARITAL_STATUS AS VARCHAR(8)
      CHECK (VALUE IN('Single','Divorced','Widowed'));
      
      This now creates a variable type that you could use.
      
      To remove this Domain later we: DROP DOMAIN <domain_name> [RESTRICT | CASCADE]
      CASCADE when used will change the type in the table that was used in creating the domain. RESTRICT will keep you from deleteing the DOMAIN untill no atributes are based on it. (NOTE: some RDBMSs do not support domains)
   6. SQL Integrity Constraints
      To maintain integrity, we use two commands ON DELETE RESTRICT which will not let us delete a row if it will effect another table (Vendor list for example - deleting it would make a product list have no vendor for an item). ON UPDATE CASCADE however forces changes to any other table that relies on it if changes are made to it.
3. Data Manipulation Commands
   Common SQL Commands:
   

   Command	Description
   INSERT	Lets you insert into a table, one row at a time. Used to make the initial data entries into a new tabel structure that already contains data.
   SELECT	List the table contents. Actually SELECT is a query command rather than a data management command. Nevertheless SELECT is introduced in this section because it lets you check the results of your data management efforts
   COMMIT	Lets you save your work to the disk
   UPDATE	Enables you to make changes in the data.
   DELETE	Enables you to delete one or more rows.
   ROLLBACK	Restoes the database table contents to their original conditions (Since last COMMIT)

   1. Data Entry
      INSERT INTO <table name> VALUES(attribute1 value, attribute 2 value, ... etc.);
      Example
      INSERT INTO VENDOR
      VALUES (21225, 'Bryson, Inc.','Smithson',615',223-3234','TN','Y');
      String and date values are between apostraphes (') except dates in Access (uses pound sign #) If necessary and no value has been provided put NULL in the place of the data that would go in (but only if NOT NULL is not in the table creation).
   2. Saving the Table Contents
      COMMIT <table name>; will save the changes to the table.
   3. Listing the Table Contents
      Use the SELECT command with the porper attributes to get the information that you need. To get all the results of a PRODUCT table you would use
      SELECT * FROM PRODUCT;
      You could also use individual column names as well to only display that information.
   4. Making a Correction
      To update a particular piece of information
      UPDATE PRODUCT SET (attribute = value [,attribut2=value2,attribute3 = value3]) WHERE (other attribute = value);
   5. Restoring the Table Contents
      ROLLBACK takes table changes back to what they were after last save.
   6. Deleting Table Rows
      DELETE FROM <table name> WHERE <attribute = value>;


4. Queries
   

   To Be Continued

   
   
   
   
   1. Partial Listing of Table Contents
   
   
   2. Logical Operators: And, Or, and Not
   
   
   3. Special Operators
   
   
   
   


5. Advanced Data Management Commands
   
   
   
   1. Changing a Column's Data Type
   
   
   2. Changing Attribute Characteristics
   
   
   3. Dropping a Column
   
   
   4. Entering Data into the New Column
   
   
   5. Arithmetic Operators and the Rule of Precedence
   
   
   6. Copying Parts of a Table
   
   
   7. Deleting a Table from the Database
   
   
   8. Primary and Foreign Key Designations
   
   
   
   


6. More Complex Queries and SQL Functions
   
   
   
   1. Ordering a Listing
   
   
   2. Listing Unique Values
   
   
   3. Aggregate Functions in SQL
   
   
   4. Grouping Data
   
   
   5. Virtual Tables: Creating a View
   
   
   6. SQL Indexes
   
   
   7. Joining Database Tables
   
   
   
   


7. Updated Views


8. Procedural SQL
   
   
   
   1. Triggers
   
   
   2. Stored Procedures
   
   
   3. PL/SQL Stored Functions
   
   
   
   


9. Converting an E-R Model into a Database Structure


10. General Rules Governing Relationships Among Tables