SQL Tutorial for Beginners Part I




We human beings communicate with each other with the help of language. SQL is a 4TH Generation, non-procedural Database gateway Language standardized by ANSI. It is the language that a database understands and we will communicate to the database using SQL. SQL stands for Structured Query Language. It is used to create, manage, manipulate and query the database objects such as tables, views etc. Now lets start learning this language:

SQL Commands are divided into following categories:
1. Data Definition Language
• CREATE, ALTER, DROP
2. Data Manipulation Language
• INSERT, UPDATE, DELETE
3. Data Query Language
• SELECT
4. Transaction Control Language
• COMMIT, ROLLBACK
5. Data Control Language
• GRANT, REVOKE

DDL
Creating a Table
CREATE TABLE tablename
(columnname1 data_type (size) [constraints],
(columnname2 data_type (size) [constraints],
…)

Defining Oracle10g Database Tables
To create a table, we must specify
Table name, Column names their corresponding Column data types with the Column sizes

Names and Properties: Conventions
Series of rules Oracle Corporation established for naming all database objects
•From 1 to 30 characters
•Only alphanumeric characters, and special characters
($ , _, #)
•Must begin with a letter and can not contain blank spaces or hyphens

Data Types
Specifies kind of data that the column stores
Enables DBMS to use storage space more efficiently by internally storing different types of data in different ways
Basic Data types are: Character, Number, Date/time, Large object

Basic Built-in Data Types
Character: VARCHAR2, CHAR, NVARCHAR2/NCHAR [For storing Unicode Character set]
Numeric: NUMBER
DATE
Large Object: CLOB, BLOB, LONG, RAW, LONG RAW

Let us take an Example:
CREATE TABLE employee
(empno NUMBER(6),
ename VARCHAR2(30),
sex CHAR(1),
dob DATE);


Database Object(Table) Constraints
A rule that restricts the values that can be inserted into a column.
A mechanism used to protect the relationship between data within an Oracle table, or
the correspondence between data in two different tables.

Types of Constraints
Integrity constraints: define primary and foreign keys
Value constraints: define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL
Table constraint: restricts the data value with respect to all other values in the table
Column constraint: limits the value that can be placed in a specific column, irrespective of values that exist in other table records

Integrity Constraints
Define primary key columns
Specify foreign keys and their corresponding table and column references
Specify composite keys

Default Clause
Default: specifies a default value that is inserted automatically
Syntax:
CREATE TABLE tablename(column name datatype DEFAULT value);

Primary Key Constraints
•Column-Level: the constraint will be given along with the column definition. This is required when only one column is to be made as a primary key
•Table-level: this type is used when a compound primary key is to be created. i.e. super key

With an example
Column Level Primary Key Constraint:
CREATE TABLE employee1
(empno NUMBER(6) CONSTRAINT empno_pk PRIMARY KEY,
ename VARCHAR2(30),
sex CHAR(2),
dob DATE);

Table Level Primary Key Constraint:
CREATE TABLE employee2
(empno NUMBER(6),
ename VARCHAR2(30),
sex CHAR(2),
dob DATE,
CONSTRAINT emp_id_pk PRIMARY KEY(empno,ename));
i.e. a Composite Primary Key

Foreign Key Constraints
•Can only be defined after column is defined as a primary key in the parent table

With an Example:
Suppose we have a parent table that contains department info as follows:

CREATE TABLE dept
(deptno number(2) PRIMARY KEY,
dname varchar2(20) NOT NULL,
loc varchar2(10));

Column Level Foreign Key Constraints
CREATE TABLE employee1
(empno NUMBER(6),
ename VARCHAR2(30),
deptno number(2) CONSTRAINT deptno_fk REFERENCES dept(deptno),
sex CHAR(2),
dob DATE);

Table Level Foreign Key Constraints
CREATE TABLE employee2
(empno NUMBER(6),
ename VARCHAR2(30),
deptno number(2),
sex CHAR(2),
dob DATE,
CONSTRAINT deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno));

Types of Value Constraints
1. Check CONSTRAINT: restricts data to specific values
Syntax:
CREATE TABLE table name(Column name datatype, CHECK (condition);

With an Example: column level
Table Level CHECK Constraint
CREATE TABLE employee
(empno NUMBER(6) PRIMARY KEY,
ename VARCHAR2(30),
sex CHAR(2),
dob DATE,
CHECK (empno > 2999 OR dob >= ’01-JAN-1988’);

2. Not NULL: specifies that a column cannot be empty
Syntax:
CREATE TABLE tablename(column name datatype NOT NULL);

3. Unique: Specifies that a non-primary key column must have a unique value
Syntax:
CREATE TABLE table name(column name datatype CONSTRAINT term_term_desc_uk UNIQUE (term_desc));

With an Example:
CREATE TABLE employee
(empno NUMBER(6) PRIMARY KEY,
ename VARCHAR2(30) NOT NULL,
deptno number(2) REFERENCES dept(deptno),
sex CHAR(2) DEFAULT ‘M’,
email varchar2(20) CONSTRAINT em_email UNIQUE,
dob DATE,
CHECK (empno > 2999));

Create a Table from Another Table in database

Syntax:
CREATE TABLE
AS ( Select query);
Example:
CREATE TABLE employee2 AS(
SELECT * FROM employee1 WHERE empno> 5000);

Modifying and Deleting Database Tables
Modify existing database tables by Changing the name of a table
Adding new columns
Deleting columns that are no longer needed
Changing the data type or maximum size of an existing column
Unrestricted action: some specifications can always be modified
Restricted action: specifications modified only in certain situations

Deleting and Renaming Tables
To delete
Drop table tablename [CASCADE CONSTRAINTS]; *Use with caution
To delete foreign key constraints, add “cascade constraints”
Example:
DROP TABLE employee1;
To change the name of a table use RENAME
Syntax
Rename old_tablename to new_tablename;
Example:
RENAME employee TO EMP;

Adding Columns to Existing Tables
To add a column
Syntax
ALTER TABLE tablename ADD(column1 data_declaration [constraints], column2 data_declaration [constraints]…);
Example:
ALTER TABLE employee ADD (grade CHAR(2));

Modifying Existing Column Data Definitions
Can only change data type to compatible data type (i.e. varchar2 to char)
ALTER tablename MODIFY(columnname new_data_declaration);
Example:
Changing the column width of grade from 2 to 4:
ALTER TABLE employee MODIFY (grade CHAR(4));

Renaming a column
ALTER TABLE employee RENAME COLUMN email TO email_address;

Deleting a Column
Can be used to remove a column from a table
ALTER TABLE tablename DROP COLUMN columnname;

Examples:
ALTER TABLE employee DROP COLUMN grade;


Adding and Deleting Constraints
Add a constraint: ALTER TABLE tablename ADD CONSTRAINT constraint_name constraint_definition;
Remove a constraint: ALTER TABLE tablename DROP CONSTRAINT constraint_name;
Example:
ALTER TABLE employee ADD CONSTRAINT emp_ename UNIQUE (ename);
ALTER TABLE faculty DROP CONSTRAINT emp_email;

Enabling and Disabling Constraints
When modifying a database it can be useful to disable constraints. Constraints are enabled by default.

To disable a constraint: ALTER TABLE tablename DISABLE CONSTRAINT constraint_name;
To enable a constraint: ALTER TABLE tablename ENABLE CONSTRAINT constraint_name;

Example:
ALTER TABLE employee DISABLE CONSTRAINT sex_ck;
ALTER TABLE faculty ENABLE CONSTRAINT em_email;

Constraint Deferability
It is the concept of delaying constraint violation errors until the end of transaction e.g.
ALTER TABLE dept ADD(deptno number PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);

Deferred integrity constraints are those that are not enforced until the user attempts to commit the transaction.

Constraint Deferability
At the time of commit, if any data entered during the transaction violates an integrity constraint, Oracle will then rollback the entire transaction and signal an error to the user.
A constraint that is not deferrable will be checked immediately after every command.

•If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default.

SET CONTRAINTS {ALL | constraint [, ...] } {DEFERRED | IMMEDIATE}
Sets the constraint mode of the current transaction