CBSE Class 12 Informatics Practices Oracle SQL Notes

ORACLE SQL REVISION TOUR

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.

TYPES OF SQL STATEMENTS:

a) DDL (Data Definition Language):- Create ,Alter,Drop.

b) DML (Data Manipulation Language):- Select,Delete,Insert,Update.

c) DCL (Data Control Language):- Grant,Revoke.

d) 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          • Primary Key constraint

•Default 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 ,[,,……]

FROM

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 [,…….]

FROM

WHERE ;

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 [,,……]

FROM

WHERE 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 [,…….]

         FROM

         WHERE

         ORDER BY

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[]

VALUES (,,,…..);

Example :

♦ Enter a new record in student table

INSERT INTO student (sid,sname,fname,age,class,address);

VALUES(101,’Mohan’,’Pawan’,15,’8’,’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

WHERE ;

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;

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 ADD

;

b)To modify existing columns of a table:

Syntax:

ALTER TABLE

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

Example :

♦  Drop the student table from the database

   DROP TABLE student;

Please click the link below to download pdf file for CBSE Class 12 Informatics Practices Oracle Sql Study Notes.