Introduction to SQL * What is SQL: SQL environment, the three types of commands (DDL, DML, DCL) * SQL * Structured Query Language * High level declarative language for relational database * De facto standard in the database world * Is now an official standard too, accepted by * American National Standards Institute (ANSI) * International Organization for Standardization (ISO) * The first standard (1986): SQL/86 * Several subsequent versions * SQL-89, SQL-92, SQL-1999, SQL-2003 (XML), SQL-2006, SQL-2008; SQL-2011 * DDL * Define the database * Base tables: CREATE, ALTER and DROP a TABLE, VIEW or INDEX (you drop a table but not delete it) * Views - Virtual (or derived) tables: CREATE VIEW, ALTER VIEW, DROP VIEW * Notation: Angle brackets indicate mandatory * CREATE TABLE <table name>
(
<column name> <data type>
[DEFAULT<data value>]
[column constraint clause]
…
[table constraint clause]
);
* Each tables contains several attributes * Each attribute has a name and a data type and may be subject to some integrity constraints * Constraints can be defined for a table or attribute * Oracle doesn't permit some reserved keywords to be used as names of tables, columns, or constraints: ( order, number, type, comment, date, char, number, create, table, attribute, database ) * If you get an error message saying you have a bad (table, column or constraint) name, choose another name and try again. * Number * NUMBER(n): Integer and real values occupying up to n digits. * NUMBER(n, d): Real values occupying up to n digits with d digits after the decimal point. * Character (or Alphanumeric) * CHAR(n):