PL/SQL

Introduction

This SQL Guide is written as a basic introduction to Structured Query Language (SQL).

RDBMS and SQL

Structured Query Language (SQL) is a standard language for communication with a relational database management system (RDBMS). SQL provides commands which are used to create, modify, and access the database. Databases contain one or more tables of data with relationships between multiple tables defined in the database. Each table is an object.

SQL Commands

• Select - Used to retrieve selected data. Example:

select "columnname1", "columnname2" from "tablebasename" [where "condition"];

A "*" may be used to select all columns. The where clause is optional and only one column name must be specified.

• Insert
• Update
• Delete
• Create
• Drop

Insert Command

This valment is used to insert a row of data in a table. All inserted values are enclosed       using single quote strings. The syntax of this command is:

insert into tablename

(column1name,column2name...columnxname)

values (value1,value2...valuex); 

Example

insert into Abcd

(name, val, indi, zipcode)

values ('Type', 'Apple', '89', '46501');

Update Command

This command is used to make changes to records in tables. Syntax:

update tablename

set columnname = newvalue [,columnxname = newvaluex...]

where columnname OPERATOR value [and|or columnnamex OPERATOR valuex];

The OPERATOR is one of the conditions listed on the Select Command page.

OPERATORs include:

• =   Equal
• <   Less than
• >   Greater than
• <= Less than or equal
• >= Greater than or equal
• <> Not equal
• LIKE - Allows the wildcard operator, %, to be used to select items that are a partial match. An example is:

select city, val from towntable where val like 'bcd%'

This allows selection of all towns in vals that begin with the word "north" allowing vals like North Dakota and North Carolina to be selected.

Example

update Abcd set indi = indi+1 where name = 'Type' and val = 'Apple';

Delete Command

This command is used to delete rows or records in a table. The syntax of this command is:

delete from "tablename"

where columnname OPERATOR value [and|or 

columnnamex OPERATOR valuex]; 

Note : without the where clause, all records in the table will be deleted.

Example

delete from Abcd where name = 'Type' and val = 'Apple';

Create Command

This command is used to create a table. The syntax of this command is:

create table tablename

(column1name datatype [constraint],

column2name datatype [constraint], 

column3name datatype [constraint]);

Keywords specifying Items that may be created

• table

Data Types

• char(size) - Fixed length string of characters of the set size. The size of the string is limited to 255 characters.
• date
• number(maxsize) - Number with a maximum number of digits specified by "maxsize".
• number(maxdigits,maxright) - A decimal number with a manimum number of "maxdigits" with "a maximum number of digits to the right of the decimal, "maxright".
• varchar(maxsize) - A character string with variable lingth limited to "maxsize".

Constraints

Constraints are rules for the column.. Possible values include:

• not null - The column values must have a value and cannot be null.
• primary key - Each record is uniquely identified by this column.
• unique - No two values may be the same in the column

Example

create table Abcd (name varchar(20),val varchar(20),indi number(8),

zipcode number(5) unique);

 Drop Command

     Used to remove an entire table from the database. Syntax:

  drop table <tablename>;

Example

     drop table Abcd;

Q : What is a primary key?

Ans: A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

Note:

In Oracle, a primary key can not contain more than 32 columns.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a primary key using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n));

For example:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.

We could also create a primary key with more than one field as in the example below:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name));

Using an ALTER TABLE statement

The syntax for creating a primary key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

For example:

ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

Drop a Primary Key

The syntax for dropping a primary key is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

In this example, we're dropping a primary key on the supplier table called supplier_pk.

Disable a Primary Key

The syntax for disabling a primary key is:

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
disable CONSTRAINT supplier_pk;

In this example, we're disabling a primary key on the supplier table called supplier_pk.

Enable a Primary Key

The syntax for enabling a primary key is:

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
enable CONSTRAINT supplier_pk;

In this example, we're enabling a primary key on the supplier table called suppli

Q : What is a foreign key?

Ans: A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
  FOREIGN KEY (column1, column2, ... column_n)
  REFERENCES parent_table (column1, column2, ... column_n));

For example:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));

CREATE TABLE products
(	product_id	numeric(10)	not null,
	supplier_id	numeric(10)	not null,
	CONSTRAINT fk_supplier
	FOREIGN KEY (supplier_id)
	REFERENCES supplier(supplier_id)_;

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
(	product_id	numeric(10)	not null,
	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	CONSTRAINT fk_supplier_comp
	FOREIGN KEY (supplier_id, supplier_name)
	REFERENCES supplier(supplier_id, supplier_name)
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

Using an ALTER TABLE statement

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name
  FOREIGN KEY (column1, column2, ... column_n)
  REFERENCES parent_table (column1, column2, ... column_n);

For example:

ALTER TABLE products
add CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);

In this example, we've created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

ALTER TABLE products
add CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);