Key Points - Oracle SQL Revision

What is SQL?

When a user wants to get some information from a database file, he can issue a query.

A query is a user–request to retrieve data or information with a certain condition.

SQL is a query language that allows user to specify the conditions. (instead of algorithms)

Types of SQL commands

Data Definition Language commands (DDL Command) : All the commands used to create, modify or delete physical structure of an object like Table.
For eg. Create, Alter , drop

Data Manipulation Language command (DML Command): All the commands used to modify contents of a table are comes under this category.
For eg. : Insert, delete, update commands

TCL Command : These commands are used to control Transaction of DML commands.
For eg. Commit, rollback

Concept of SQL

The user specifies a certain condition.

The program will go through all the records in the database file and select those records that satisfy the condition.(searching).

Statistical information of the data.

The result of the query will then be stored in form of a table.

Basic structure of an SQL query
General Structure
SELECT, ALL / DISTINCT, *,AS, FROM, WHERE

Comparison
Grouping
Display Order
Logical Operators
IN, BETWEEN, LIKE "% _"
GROUP BY, HAVING, COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
ORDER BY, ASC / DESC
AND, OR, NOT
TYPES OF SQL STATEMENTS: a) b) c) d)
DDL (Data Definition Language):- Create ,Alter,Drop.
DML (Data Manipulation Language):- Select,Delete,Insert,Update.
DCL (Data Control Language):- Grant,Revoke.
TCL (Transaction Control Language):- COMMIT,ROLLBACK,SAVEPOINT.
CONSTRAINT is a condition applicable on a field or group of fields.

Two types of constraint
Column Constraint :- apply only to individual column
Table Constraint
:- apply to groups of columns
Different constraint
Unique Constraint
Default constraint
Primary Key constraint
Check constraint
Applying Constraint
Example:
Create a student table with filed student id, student name, father’s name, age, class, adrress.
CREATE TABLE student (sid char(4) PRIMARY KEY, sname char(20) NOT NULL, fname char(20), age number(2) CHECK (age<20), class char(5) NOT NULL , address char(50));

SELECT COMMAND
Select command is a query that is given to produce certain specified information from the database table.
Select Statement is used as
SELECT <column name>,[,<column name>,……]
FROM <table name>;
Example:
Write a query to display the name and salary of the employee in emp table.
SELECT ename, sal
FROM emp;

Variations of select Command:
Selecting specific Rows……..WHERE clause
Syntax:
SELECT <column-name>[,<column-name>…….]
FROM <table name>
WHERE <condition>;
Example: Display the employee code, their name and their salary who are Manager.
SELECT empno,ename,sal
FROM emp
WHERE job=’MANAGER’;

Searching for NULL (IS NULL Command):
The null value in a column can be searched for in a table using IS NULL in the WHERE Clause
Syntax:
SELECT <column-name>[,<column-name>,……]
FROM <table-name>
WHERE <column-name> IS NULL;
Example
Display the employee code, name and their job whose Dept.No. is Null.
SELECT empno,empname,job
FROM emp
WHERE DeptNo IS NULL;

IS NOT NULL Command:
Example:
Display the name and job of those employee whose dept No is not Null
SELECT ename,job FROM emp
WHERE deptno IS NOT NULL;

Logical Operators
The logical operators OR, AND, NOT are used to connect search conditions in the WHERE clause.
The uses of logical operators are understand by these following examples

Display the name of manager whose salary is more than 5000
SELECT ename
FROM emp
WHERE job=’MANAGER’ and sal>5000;

Write a query on the customers table whose output will exclude all customers with rating<=100, unless they are located in Shimla.
SELECT *
FROM customers
WHERE rating>100 OR city=’Shimla’;
Sorting Result- ORDER BY Clause:
The resulting column can be sorted in ascending and descending order using the ORDER BY
Clause.
Syntax: SELECT <column-name>[,<column-name>…….]
FROM <table name>
WHERE <condition>
ORDER BY <column-name>
Example:

Display the list of employee in the descending order of employee code, who is manger
SELECT * FROM emp
WHERE job=’MANAGER’
ORDER BY ecode;

The INSERT Command:
The tuples are added to relation using INSERT command of SQL.
Syntax:
INSERT INTO <table-name>[<column list>]
VALUES (<value>,<value>,<value>,…..);
Example:
Enter a new record in student table
INSERT INTO student (sid,sname,fname,age,class,address);
VALUES(101,’Mohan’,’Pawan’,15,’8’,’Jaipur’); sid
1.1 sname
Mohan fname
Pawan age
1. class
8.address
Jaipur

The DELETE Command:
The delete command removes the tuples from the tables. This command remove the entire row from the table and not the individual field. So no filed argument is needed.
Syntax
DELETE FROM <table-name>
WHERE <condition>;
Example

Delete all the records of employee whose salary is less than 3000
DELETE FROM emp
WHERE sal<3000;

To delete all the record from the table:
DELET FROM<table-name>;

The UPDATE Command:
The UPDATE command is used to changes some values in existing rows. The UPDATE command specifies the rows to be changed using the WHERE clause, and new data using the SET keyword.
Example:

Update the salary of employee to 5000 whose employee code is 1011.
UPDATE emp
SET sal=5000
WHERE empno=1011;

The ALTER TABLE Command:
The ALTER command is used to change the definition of existing table. a)It can be used to add columns to a table.
Syntax (to add a column to a table):
ALTER TABLE <table-name> ADD <column-name>
<data type> <size>; b)To modify existing columns of a table:
Syntax:
ALTER TABLE <table-name>
MODIFY (Columnname newdatatype (newsize));
Example: o To modify column job of table emp to have new width of 30 character
ALTER TABLE emp
MODIFY (job char(30));

The DROP Command
The DROP command is used to drop the table from the database. For dropping a table all the tuples should be deleted first i.e the table should be empty.
Syntax:
DROP TABLE <table-name>
Example:
Drop the student table from the database
DROP TABLE student;