DBMS SQL Language Syntax


Introduction to the Oracle Database

An Oracle Database system is a relational database system which contains the process monitor and system monitor processes with storage. The oracle database can be accessed using common programming languages like C, C++, Java, C#, PHP and more. The data stored as rows and columns. The database system uses B+ tree. B+ tree is a special data structure allowing to efficiently store

Supported Environments

Oracle Database supports Microsoft Windows, Linux, Solaris (SPARC), HP-UX and AIX platforms. The Oracle has many different version from Express edition to enterprise editions. The Express edition can be used for learning purpose. The Oracle structured query language works same on all operating systems. Structured Query Language support can execute the database query to insert, delete and update the data

Oracle Database download location:
Oracle Database Express Edition 11g Release 2
Oracle Database 12c Release 1 (12.1.0.2.0)

Little History

Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories and invented Oracle Database in 1977. Oracle released 12c Release 1 as latest release.

Oracle version History: http://www.oracle.com/us/corporate/profit/p27anniv-timeline-151918.pdf

Develop using Oracle SQL

The oracle database can be accessed using SQL programming language. The developer can use SQL *plus or SQL Developer Integrated development environment to access the database. SQL*Plus basic Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers. The user can use other software integrated development to access the database.

Download the open source development environment from the following location
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
http://software.dell.com/products/toad-for-oracle/

SQL comments can be executed any IDE or SQL *plus.

Create database tables

The developer can use Database Configuration Assistant or create database comments to create the database. The database instance can be accessed using the SID or database instance with machine name and port number. The developer can also use existing database

http://www.fehily.com/books/createdb/createdb_oracle_11g_2.html

RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows. The following command unlock the HR user. The developer can login to SQL*Plus as the SYS user and execute the following command

alter user hr identified by hr account unlock;

The following query select all from employee HR Schema

select * from hr.employees;

The following query select limit the results

select * from hr.employees where ROWNUM <= 10;

The following query create SEQUENCE. A sequence is a database object that generates unique numbers used for primary key values.

CREATE SEQUENCE empid 
MINVALUE 1 START WITH 1
INCREMENT BY 1
CACHE 10;

The following query create employee table.

create table employee (
  id     number  primary key,
  empname   varchar2(50),
  empage  number
);

Primary key and Foreign Key

Primary key consists of one or more columns whose data contained uniquely identify each row in the table. A foreign key is a set of one or more columns in a table that refers to the primary key in another table.

Insert the data into employee table

INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Selva',11);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'James',22);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Josephine',55);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Art',77);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Lenna',35);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Donette',67);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Simona',72);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Mitsue',87);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Leota',90);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Sage',54);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Kris',92);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Minna',76
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Abel',45);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Kiley',34);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Graciela',32);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Cammy',1);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Mattie',35);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Meaghan',25);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Gladys',28);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Yuki',22);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Fletcher',76);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Bette',23);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Veronika',34);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Willard',6);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Maryann',4);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Alisha',2);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Allene',45);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Chanel',13);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Ezekiel',34);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Willow',98);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Bernardo',18);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Ammie',15);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Francine',67);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Ernie',56);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Albina',22);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Alishia',52);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Solange',12);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Jose',32);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Rozella',29);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Valentine',62);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Kati',56);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Youlanda',34);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Dyan',98);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Roxane',7);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Lavera',12);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Erick',45);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Fatima',34);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Jina',78);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Kanisha',34);
INSERT INTO employee (id,empname,empage) VALUES (empid.nextval,'Emerson',20);

Data definition language

The Data Definition language uses to Create, alter, and drop schema objects.

Create table

The following query create employee table.

create table Datadefinition  (
  tableid     number  primary key,
  tablename   varchar2(50)
);
Table DATADEFINITION created.

Rename table

rename table Datadefinition to DatadefinitionExample;

Alter table

ALTER TABLE DATADEFINITIONEXAMPLE ADD tableschema varchar2(100);
Table DATADEFINITIONEXAMPLE altered.

Drop table

drop table DATADEFINITIONEXAMPLE;
Table DATADEFINITIONEXAMPLE dropped.

Data Manipulation Language (DML)

Insert

INSERT INTO Datadefinition (tableid,tablename) VALUES (1,'Employee');
INSERT INTO Datadefinition (tableid,tablename) VALUES (2,'Departments');
INSERT INTO Datadefinition (tableid,tablename) VALUES (3,'Purchase');
INSERT INTO Datadefinition (tableid,tablename) VALUES (4,'Sales');
INSERT INTO Datadefinition (tableid,tablename) VALUES (5,'Items');
INSERT INTO Datadefinition (tableid,tablename) VALUES (6,'Delivery');

Select

Select all the details from Datadefinition table.

select * from Datadefinition ;
1	Employee
2	Departments
3	Purchase
4	Sales
5	Items
6	Delivery

Select current date from oracle database

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Date"  FROM DUAL;

Update

update Datadefinition set tablename='EmployeeInformation' where tableid=1;
1 row updated.
select * from Datadefinition ;
1	EmployeeInformation
2	Departments
3	Purchase
4	Sales
5	Items
6	Delivery

Delete

delete Datadefinition where  tableid =5;
1 row deleted.
select * from Datadefinition ;
1	EmployeeInformation
2	Departments
3	Purchase
4	Sales
6	Delivery

Joins

An SQL join clause combines records from two or more tables in a relational database.

Inner join or Equi join or simple join

select * from HR.employees
    inner join  HR.departments on
      employees.DEPARTMENT_ID=departments.DEPARTMENT_ID

Note: The HR Schema can be downloaded from the location http://www.oracle.com/technetwork/developer-tools/datamodeler/sample-models-scripts-224531.html

Outer join

The full outer join return all the rows from left table and right table

select * from HR.employees
    full outer join  HR.departments on
      employees.DEPARTMENT_ID=departments.DEPARTMENT_ID

Left outer join

select * from HR.employees
  LEFT OUTER JOIN  HR.departments on
   employees.DEPARTMENT_ID=departments.DEPARTMENT_ID

Right Outer Join

      
select * from HR.employees
  RIGHT OUTER JOIN  HR.departments on
   employees.DEPARTMENT_ID=departments.DEPARTMENT_ID

Cross join

SELECT   *
FROM     HR.employees
CROSS JOIN   HR.departments ;

Natural join

SELECT   *
FROM     HR.employees
NATURAL JOIN HR.departments ;



SQL Constraints

Primary and Foreign Key

Primary key Foreign Key
Uniquely identify a record in table. Set of one or more columns in a table that refers to the primary key in another table.
Only one primary key Can contain multiple foreign key.
Do not accept null values Can accept null values

Null and not null

The not null constraint do not allow null values to insert into the table.

desc hr.employees;

Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)

UNIQUE

CREATE TABLE Phones
(
pid int NOT NULL,
ModelName varchar(255) NOT NULL,
CompanyName varchar(255),
CONSTRAINT pid UNIQUE (pid,ModelName)
)

INSERT INTO Phones (pid,ModelName,CompanyName) VALUES (1,'IPhone 6','Apple');
INSERT INTO Phones (pid,ModelName,CompanyName) VALUES (1,'IPhone 6s','Apple');
INSERT INTO Phones (pid,ModelName,CompanyName) VALUES (2,'IPhone 6','Apple');

INSERT INTO Phones (pid,ModelName,CompanyName) VALUES (1,'IPhone 6','Apple');

Error starting at line : 153 in command -
INSERT INTO Phones (pid,ModelName,CompanyName) VALUES (1,'IPhone 6','Apple')
Error report -
SQL Error: ORA-00001: unique constraint (SYS.PID) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

DEFAULT

CREATE TABLE IPhoneModel
(
pid int NOT NULL,
ModelName varchar(255) NOT NULL,
CompanyName varchar(255) DEFAULT 'Apple'
)

INSERT INTO IPhoneModel (pid,ModelName,CompanyName) VALUES (1,'IPhone 6','Apple');
INSERT INTO IPhoneModel (pid,ModelName) VALUES (2,'IPhone 6s');

select * from IPhoneModel;

1	IPhone 6	Apple
2	IPhone 6s	Apple

Views

A view is a virtual table based on the result-set of an SQL statement.

CREATE OR REPLACE VIEW employee_contact AS
SELECT employee_id, first_name, last_name, phone_number, email
FROM HR.employees
WHERE salary >= 10000;

select * from employee_contact;
100	Steven	King	515.123.4567	SKING
101	Neena	Kochhar	515.123.4568	NKOCHHAR
102	Lex	De Haan	515.123.4569	LDEHAAN
108	Nancy	Greenberg	515.124.4569	NGREENBE
114	Den	Raphaely	515.127.4561	DRAPHEAL
145	John	Russell	011.44.1344.429268	JRUSSEL
146	Karen	Partners	011.44.1344.467268	KPARTNER
147	Alberto	Errazuriz	011.44.1344.429278	AERRAZUR
148	Gerald	Cambrault	011.44.1344.619268	GCAMBRAU
149	Eleni	Zlotkey	011.44.1344.429018	EZLOTKEY
150	Peter	Tucker	011.44.1344.129268	PTUCKER
156	Janette	King	011.44.1345.429268	JKING
162	Clara	Vishney	011.44.1346.129268	CVISHNEY
168	Lisa	Ozer	011.44.1343.929268	LOZER
169	Harrison	Bloom	011.44.1343.829268	HBLOOM
174	Ellen	Abel	011.44.1644.429267	EABEL
201	Michael	Hartstein	515.123.5555	MHARTSTE
204	Hermann	Baer	515.123.8888	HBAER
205	Shelley	Higgins	515.123.8080	SHIGGINS

DROP VIEW employee_contact;

View EMPLOYEE_CONTACT dropped.

Triggers

Stored Procedures

Functions

Missing!

We didn't cover tuning the SQL queries, Sub Queries, Clone the tables, SQL Injection, Wildcard Operations and transactions related areas.

You can always learn the topics from recommended books and useful links.