What is PL/SQL?

      PL/SQL is a database-oriented programming language that extends Oracle SQL with procedural capabilities.

SQL is divided into the following

  1. Data Definition Language (DDL-create, alter,drop,truncate,rename)
  2. Data Manipulation Language (DML-insert, update, delete)
  3. Data Retrieval Language (DRL-select)
  4. Transaction Control Language (TCL-commit,rollback,savepoint)
  5. Data Control Language (DCL-grant,revoke)

SQL Queries for Practice

CONSTRAINTS

      Constraints are always attached to a column not a table.

We can add constraints in three ways.

  • Column level -- along with the column definition
  • Table level -- after the table definition
  • Alter level -- using alter command

      While adding constraints you need not specify the name but the type only, oracle will internally name the constraint.If you want to give a name to the constraint, you have to use the constraint clause.

Constraints are categorized as follows.

Domain Integrity Constraints:

  • Not null
  • Check

Entity Integrity Constraints:

  • Unique
  • Primary Key

Referential Integrity Constraints:

  • Foreign Key

FUNCTIONS

ALIASES

Create with SELECT:

We can create a table using existing table [along with data].

Syntax:  Create table new_table_name [col1, col2, col3 ... coln] as select * from old_table_name;

Example:
      SQL> create table Graduates1 as select * from Graduates;
Creating table with your own column names.
      SQL> create table Graduates2(sno, sname, smarks) as select * from Graduates;

Creating table with specified columns.
      SQL> create table Graduates3 as select no,name from Graduates;

Creating table with out table data.
      SQL> create table Graduates2(sno, sname, smarks) as select * from Graduates where 1 = 2;
In the above where clause give any condition which does not satisfy.

Insert with SELECT:

Using this we can insert existing table data to a another table in a single trip. But the table structure should be same.

Syntax:  Insert into table1 select * from table2;

Example:
      SQL> insert into Graduates1 select * from Graduates;
Inserting data into specified columns
      SQL> insert into Graduates1(no, name) select no, name from Graduates;

Column Aliases:

Syntax:  Select orginal_col alias_name from table_name;

Example:
      SQL> select no sno from Graduates; (or)
      SQL> select no “sno” from Graduates;

Table Aliases:

If you are using table aliases you can use dot method to the columns.

Syntax:  Select alias_name.col1, alias_name.col2alias_name.coln from table_name alias_name;

Example:
      SQL> select s.no, s.name from Graduates s;

MERGES

      You can use merge command to perform insert and update in a single command.

Example:
SQL> Merge into Graduates1 s1
       Using (select *From Graduates2) s2
       On(s1.no=s2.no)
       When matched then
       Update set marks = s2.marks
       When not matched then
       Insert (s1.no,s1.name,s1.marks)
       Values(s2.no,s2.name,s2.marks);

      In the above the two tables are with the same structure but we can merge different structured tables also but the datatype of the columns should match.
      Assume that Graduates1 has columns like no,name,marks and Graduates2 has columns like no, name, hno, city.

SQL> Merge into Graduates1 s1
       Using (select *From Graduates2) s2
       On(s1.no=s2.no)
       When matched then
       Update set marks = s2.hno
       When not matched then
       Insert (s1.no,s1.name,s1.marks)
       Values(s2.no,s2.name,s2.hno);