Relational DBMS (OLD) - Oracle Sql Revision Tour

 CBSE Class 12 Informatics Practices

Revision Notes
CHAPTER-10
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). SQL stands for Structured Query Language. It is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. 

What can SQL do ? 

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

Types of SQL commands
1. Data Definition Language commands (DDL Command): These SQL commands are used to create, modify, and drop the structure of database objects like table, view, procedure, indexes etc. In this category we have CREATE, ALTER, DROP and TRUNCATE commands.
2. Data Manipulation Language command (DML Command): These SQL commands are used to store, modify, and delete data from database tables. In this category we have INSERT, UPDATE, and DELETE commands.
3. TCL Command : These SQL commands are used to handle changes which affect the data in database. Basically we use these commands with in the transaction or to make a stable point during changes in database at which we can rollback the database state if required. In this category we have SAVEPOINT, ROLLBACK and COMMIT commands.

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

IN, BETWEEN, LIKE "% _"

Grouping

GROUP BY, HAVING, COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )

Display Order

ORDER BY, ASC / DESC

Logical Operators

AND, OR, NOT

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.

Some of The Most Important SQL Commands

  • SELECT - extracts data from a database
  • UPDATE - updates data in a database
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index

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 j ob= ’ 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,empnamejob FROM emp
WHERE DeptNo IS NULL;

TS 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 j ob= ’ 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

Sname

Fname

Age

Class

Address

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 <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:
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;