SQL

SQL Datatypes:

Every constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values. SQL and PL/SQL provides many predefined datatypes that can be referenced from any of the PL/SQL blocks.

Various Data Types Supported in Oracle are listed below:

Character Datatypes:

Char (size):      Character data type defines a fixed length string of characters. Size is a constant number and all the string length will be occupied (Space padded) even if a string containing length less than size is assigned. Maximum size of Char data type is 2000 bytes.

Nchar (size):     nchar data type defines a fixed length string of NLS characters. This character is similar to char except for the NLS characters are assigned to this data type.

Varchar2 (size): varchar2 data type defines a variable length string of characters. Size is a constant number and defines the maximum length of characters that can be stored. This data type is not space padded. Maximum size of varchar2 data type is 4000 bytes.

Long: This datatype supports variable length strings that are backward compatible. Maximum size of this datatype is 2GB.

Raw: Raw datatype is generally used for binary strings that can have variable length. Maximum size of this data type is same as char data type (2000 bytes).

Long Raw: This data type combines the features of raw and long datatypes to support variable length binary strings that are backward compatible. Maximum size of this data type is 2GB.

 Numeric Datatypes:

Number(p,s): This data type supports all numbers with ‘p-s’ digits before decimals and ‘s’ digits after decimal. ‘p’ stands for precision ranging from 1 to 38 and ‘s’ stands for scale ranging from -84 to 127.

Numeric(p,s), float, decimal(p,s), dec(p,s), integer, int, smallint, real and double precision are other numeric data types that are nolonger available or used since oracle9i

Date/Time Datatypes:

Date: It supports any date between Jan 1, 4712 BC till Dec 31, 9999 AD.                         

Timestamp (p): Time stamp datatype includes year, month, day, hour, minute and seconds with a fractional seconds precision specified by ‘p’.  ‘p’ can range anywhere between 0 and 9 and has a default value of 6.

Timestamp (p) with time zone: This is same as timestamp(p) data type except for it includes time zone displacement value also.

Timestamp (p) with local time zone: The time zone specified in this data type is the time zone of the session.

Interval year(y) to month: This data type supports variables of time periods stored in years and months where y being the number of digits in the year with a default 2 value.

Interval day(d) to second (p): Variables which are time periods that have days, hours, minutes and seconds are supported by this data type. ‘d’ is a day precision which ranges from 0 to 9 with a default value of 2. ‘p’ is fractional seconds precision that ranges between 0 to 9 with a default value of 6.

Large Object (LOB) Datatypes:

Few LOB types with their sizes (10g) in the parenthesis are listed below for your reference:

Bfile (2³²-1) stores file locators, blob (4GB) stores unstructured data, clob (4GB) stores single or multi byte character data, nclob (4GB) stores Unicode data.

Rowid Datatypes:

Rowid, urowid (size) are the datatypes that supports a format that has data block information, row information and the data file information. Every record of every data file in a data base will be assigned with a physical address which is nothing but rowid.  ‘urowid’ is a universal rowid with an optional size of the data base records.

SQL Statement Categories:

SQL statements are basically categorized into four major statements (DDL, DML, DCL and TCL). Individual categories are described in brief below.

Data Definition Language (DDL) statements are used to create and modify the schema or structure of database objects in a database.

§ CREATE:         Creates objects in the database (For ex: tables, views, procedures, functions, etc.,)

§ ALTER:            Alters the structure of objects in the database

§ DROP:             Deletes objects from the database

§ TRUNCATE :  Removes all records from a table,

o   Note: All the spaces allocated for the records will as well be removed

§ COMMENT:     Adds comments to the data dictionary

§  RENAME:         Renames an object

Data Manipulation Language (DML) statements are used to retrieve and or manage data within the schema objects, of a database.

§ SELECT:   Retrieves data from the a database

§ INSERT:    Inserts data into a table

§ UPDATE:  Updates existing data within a table

§ DELETE:   Deletes records from a table according to the specified condition.

o   Note: The space for the records remain

§ MERGE:     UPSERT operation (insert or update)

§ CALL:         Call a PL/SQL or Java subprogram

§ EXPLAIN PLAN:  Explain access path to data

§ LOCK TABLE:      Control concurrency

Data Control Language (DCL) statements are used to create roles, permissions, and referential integrity. They are used as well to control access to database by securing it.

§ GRANT :    Gives user's access privileges to database

§ REVOKE:  Withdraws access privileges that are given with the GRANT command

Transactional Control Language (TCL) statements are used to manage different transactions occurring within a database.

§ COMMIT:         Saves work done

§ SAVEPOINT:  Identifies a point in a transaction to which you can later roll back

§ ROLLBACK:   Restore database to original since the last COMMIT

§ SET TRANSACTION: Changes transaction options like isolation level and what rollback segment to use

  

 

Data Definition Statements:

Create Table:

For creating and/or defining a table we use the create table statement, selecting valid data types for every column in the table definition. We may or include constraints during the table creation as needed.

Syntax for creating Table:

CREATE TABLE table_name

(column_name1 datatype unique/not null/default constraint,

column_name2 datatype unique/not null/default constraint,

…,

CONSTRAINT PK_constraint_name primary key(column_name)

CONSTRAINT FK_constraint_name FOREIGN KEY (column_name)

REFERENCES PK_table_name (PK_column_name from PK_table )

Example:

Table with Primary Key:

CREATE TABLE xxdn_clients

(

   client_id             NUMBER NOT NULL,

   client_number    CHAR (10) NOT NULL,

   client_name       VARCHAR2 (25) NOT NULL,

   phone_number   CHAR (10) UNIQUE,

   Vendor               VARCHAR2 (25),

   Start_date           DATE,

   End_date            DATE,

CONSTRAINT client_id_pk PRIMARY KEY(client_id));

Table with Primary and Foreign Key:

CREATE TABLE xxdn_client_locations

(

   client_location_id   NUMBER NOT NULL,

   client_id                  NUMBER NOT NULL,

   Address1                 VARCHAR2 (50),

   address2                  VARCHAR2 (25),

   city                          VARCHAR2 (25) NOT NULL,

   state                        VARCHAR2 (25),

   zip                          VARCHAR2 (10),

   country                   VARCHAR2 (25) NOT NULL,

   phone_number        NUMBER (10),

   CONSTRAINT client_loc_id_pk PRIMARY KEY (client_location_id),

   CONSTRAINT client_fk FOREIGN KEY      (client_id)

       REFERENCES xxdn_clients (client_id));

Drop Table:

The Drop Table statement is used to delete complete  table definition as well as data from the database.

Syntax:

DROP TABLE table name;

Alter Table:

The ALTER TABLE statement is used to rename the existing table, or add, modify and drop columns of any existing table.

Renaming a table using Alter:

Syntax:

ALTER TABLE table_name

 RENAME TO new_table_name;

Example:

ALTER TABLE xxdn_client_locations

                                RENAME TO xxdn_client_sites;

Adding column to a table using Alter:

Syntax:

ALTER TABLE table_name

                        ADD column_name datatype;

Example:

ALTER TABLE xxdn_clients

                                ADD client_short_name  varchar2(10);

Adding multiple columns to an existing table using Alter:

Syntax:

ALTER TABLE table_name

ADD ( column_1 datatype,

            column_2 datatype,

                                    ... 

                                   column_n datatype );

Example:

ALTER TABLE xxdn_client_sites

ADD ( location_name varchar2(50),

                                           county varchar2(25) );

Modifying column(s) in a table using Alter:

Syntax:

ALTER TABLE table_name

MODIFY ( column_1 datatype

                   column_2 datatype,

                                           ... 

                                         column_n datatype);

Example:

ALTER TABLE xxdn_client_sites

MODIFY (Address1                 VARCHAR2 (100),

                                                city                          VARCHAR2 (25),

                                                state                        VARCHAR2 (15) );

Drop column(s) in a table using Alter:

Syntax:

ALTER TABLE table_name

                                    DROP COLUMN column_name;

Example:

ALTER TABLE xxdn_client_sites

 DROP COLUMN county;

Rename column(s) in a table using Alter:

Syntax:

ALTER TABLE table_name

 RENAME COLUMN old_name to new_name;

Example:

ALTER TABLE xxdn_client_sites

 RENAME COLUMN location_name to location;

Add constraint(s) in a table using Alter:

Syntax:

ALTER TABLE Table Name

                          add CONSTRAINT PK_Name PRIMARY KEY (column name);

Example:

ALTER TABLE xxdn_client_sites

 Add CONSTRAINT location_name_pk to PRIMARY KEY(location);

Comment:

After you create any SQL object (table, view, synonym, procedure, etc,), you can supply a comment or information about it for future information. The information can span anything from the purpose of the object to anything special about it. Comments can also be given to individual columns of a table or view. Maximum length of the column is 2000 characters and 500 characters for sequences.

Syntax:

·          COMMENT ON TABLE schema.table name is ‘ur comment’;

·          COMMENT ON COLUMN schema.table name.column name is ‘ur comment’;

Examples:

·           COMMENT ON TABLE xxdn. xxdn_client_sites is ‘Deepthi client site information’;

·           COMMENT ON COLUMN xxdn. xxdn_client_sites.Location is ‘Client location information’;

To get the comment you can use a select statement as follows

SELECT long_comment

FROM systables

WHERE name = table name;

Data Manipulation Statements:

Insert:

Data into the table can be inserted using Insert statement. It is important that the if you are not passing values into all columns that you specify column names into which the data needs to be inserted. The constraint and data types needs to be taken care of before inserting data. If the data type is of character or varchar2 type pass values in single quotes. Number can be passed in single quotes or without quotes. If the data type is  date then proper format is required (DD-MON-YYYY). You can always refer SYSDATE for today’s date.

Syntax:

INSERT INTO table name

(column name1, column name2…column name-n)

VALUES (value1, value2… value-n);

Examples:

INSERT   INTO XXDN_CLIENTS

 (CLIENT_ID, CLIENT_NUMER, CLIENT_NAME, PHONE_NUMBER, VENDOR, START_DATE, END_DATE)

VALUES (10001,'200704','NYCHA','2127325864','Hunter Green', sysdate-1000, sysdate-700);

Inserting data without specifying column names is shown below. This can be done without errors if values for every single column are specified.

INSERT INTO XXDN_CLIENTS 

VALUES (10004,'200703','NYCHA','2127325865','Hunter Green',sysdate-1001,sysdate-701)

You can also insert multiple records at the same time following the below syntax:

Syntax:

INSERT ALL

INTO table name (column name1, column name2…column name-n) VALUES (value1, value2… value-n)

INTO table name (column name1, column name2…column name-n) VALUES (value1, value2… value-n)

Select * from dual ;

Examples:

INSERT ALL

      INTO XXDN_CLIENTS (CLIENT_ID, CLIENT_NUMER, CLIENT_NAME, PHONE_NUMBER, VENDOR, START_DATE, END_DATE) VALUES (10002,'200801','STRYKER','2016487523','GL Associates', sysdate-710, sysdate-365)

   INTO XXDN_CLIENTS (CLIENT_ID, CLIENT_NUMER, CLIENT_NAME, PHONE_NUMBER, VENDOR, START_DATE, END_DATE) VALUES (10003,'200903','TYCO','6098062276','Star Point', sysdate-300, sysdate+120)

SELECT * FROM dual;

Insertion into a table can also be done using a select statement.

Syntax:

INSERT INTO Table Name

(column name1, column name2…column name-n)

Select statement that has n-columns;

Insert using select will be discussed with example in later sessions.

Update:

Data that already exists can be modified using update statement. Update statement can be effectively used to update single or multiple columns and single or multiple records based on the conditions.

Syntax:

                                    UPDATE Table Name

                                    SET column name1 = ‘value’, column  name2 = ‘Value’

                                    Where ‘conditions’

If you don’t specify any condition all the records on the table will be updated with the column values specified.

Examples:

UPDATE xxdn_clients

SET client_name = 'NYC Housing Authority',phone_number = '2016500999'

WHERE client_id = '10004';

UPDATE xxdn_clients

SET client_name = 'NYC Housing Authority'

WHERE client_name like 'NY%';

Delete:

Using the delete statement you can either delete records that meet specific requirements (conditions) or delete all records in a table. Data deletion will be session specific unless until you follow the delete statement with commit.

Syntax:

                                    DELETE FROM Table Name

                                    Where ‘conditions’

If you don’t specify any condition all the records from the table will be deleted.

Examples:

Delete from xxdn_clients

WHERE client_id = '10004';

Truncate:

It is import that sometimes we wish to get rid of all the data (for every session) in a table. For this we may use DROP table statement and recreate it later. But if we think only data needs to be removed and not the table structure we can use truncate statement. Delete statement works in the same way as truncate except for it is session specific and the spaces for the records after deletion will not be removed unless until we commit it. So we can say “Truncate = Delete + Commit”

Syntax:

                                    TRUNCATE TABLE Table Name;

Examples:

TRUNCATE TABLE xxdn_clients;

   

SQL Constraints

Constraints are used to limit the type of data that can go into a table. Constraints can be specified during the creation of a table using CREATE TABLE statement, or after the table is created using ALTER TABLE statement.

Various Constraints available in SQL are:

·         NOT NULL

·         DEFAULT

·         CHECK

·         UNIQUE

·         PRIMARY KEY

·         FOREIGN KEY

SQL NOT NULL Constraint:

In general, a column can hold NULL but if you wish not to allow NULL value for a column, a constraint for not allowing the NULL values needs to be specified.

 Example: ·           

·         Customer_Number NUMBER NOT NULL 

You will not be able to insert Null value into customer number column.

DEFAULT Constraint:

             Default Constraint will provide a default value to a column if the insert statement does not provide a specific value to the column.

Example:

·         ·            Country VARCHAR2 (10)   Default ‘USA’

             If you are not inserting any country the value will be defaulted to ‘USA’.

CHECK Constraint:

                     The Check constraint ensures that all values in a column satisfy certain conditions. Once Check constraint is defined, the database will only insert or update values only if the check constraint is met. This constraint is used to ensure the data quality.

Example:  

·            Marks number CHECK (<=100) 

·            Age number CHECK(>18)

Upon insertion of a driver’s age according to the state law we can enforce a check condition to error out if age is less than 18.

UNIQUE Constraint:

                        The UNIQUE constraint ensures that all values in a column are distinct or unique. In other words the column that has unique constraint on it will not possess any duplicates.

Example:

·         SSN     Number    UNIQUE

·         TAX_PAYER_ID    Number   UNIQUE

·         REGISTRATION_NUMBER CHAR(10) UNIQUE

                       The column that is UNIQUE restricts to insert duplicate values on to it. Also it is important to note that multiple unique constraints may exist on a single table.

Primary Key Constraint:

                         A primary key constraint can be used to uniquely identify each row in a table. The primary key can be a part of the actual record (Supplier Number) or an artificial field which does not have any significance for the business user (Supplier Id). A primary can be a single column or a group of columns in a table. If more than one column is used as primary key then we call it a composite primary key.

Example:           

We may have a supplier table with supplier_id as primary key and a product table with product_id as the primary key. The constraint can be defined as shown below.

·        PRODUCT_ ID   NUMBER,
PRODUCT_NUMBER   CHAR(10),       
CONSTRAINT    XX_CUSTOMER  PRIMARY KEY (PRODUCT_ID)

·        SUPPLIER_ ID   NUMBER PRIMARY KEY,
SUPPLIER_NUMBER   CHAR(10),

Let us assume that the client needs a table that uniquely identifies the products that can be ordered from a supplier (product-supplier table). In that scenario it is required that instead of identifying the product_id alone uniquely and supplier_id alone uniquely, or else adding an additional column that identifies as PRD_SUPPLIER_ID as unique primary key we may define a single constraint as follows to identify both product_id and supplier_id as the composite primary key. An example of defining composite primary key is given below.

·        PRODUCT_ ID   NUMBER PRIMARY KEY,
SUPPLIER_ID   CHAR(10),       
CONSTRAINT    XX_SUPPLIER_PRD_ID   PRIMARY KEY (PRODUCT_ID,SUPPLIER_ID)

Also it is important to note that a column that is specified as a primary key will be always unique, but at the same time, a column that is unique may or may not be a primary key. 

Foreign Key Constraint:

A foreign key is a column that points to a primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. That means only values that are supposed to exist in the database are permitted.

                          Let us assume that we have employee table and purchase order table. Employee table has EMPLOYEE_ID as the primary key and purchase order table has PO_ID as the primary key. Now to identify the purchase orders placed by individual employee the PO table also has employee id. Then the foreign key constraint on employee table let us identify that we are entering the existing employee details when inserting records into the purchase order table.

Example:

·         PO_ ID   NUMBER PRIMARY KEY,

EMPLOYEE_ID NUMBER references EMPLOYEE_TABLE (EMPLOYEE_ID),      

PO_DATE              DATE,

EMPLOYEE_TABLE is another table that has EMPLOYEE_ID as primary key. Constraints for foreign key cannot be created unless until the tables with the referencing primary keys are created. The condition is true also while inserting values, as we cannot insert values into a foreign key table unless the primary key values are already defined.