Download CBSE Class 12 Informatics Practices Oracle SQL Notes in PDF format. All Revision notes for Class 12 Informatics Practices have been designed as per the latest syllabus and updated chapters given in your textbook for Informatics Practices in Class 12. Our teachers have designed these concept notes for the benefit of Class 12 students. You should use these chapter wise notes for revision on daily basis. These study notes can also be used for learning each chapter and its important and difficult topics or revision just before your exams to help you get better scores in upcoming examinations, You can also use Printable notes for Class 12 Informatics Practices for faster revision of difficult topics and get higher rank. After reading these notes also refer to MCQ questions for Class 12 Informatics Practices given on studiestoday
Revision Notes for Class 12 Informatics Practices Oracle SQL
Class 12 Informatics Practices students should refer to the following concepts and notes for Oracle SQL in Class 12. These exam notes for Class 12 Informatics Practices will be very useful for upcoming class tests and examinations and help you to score good marks
Oracle SQL Notes Class 12 Informatics Practices
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
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
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.