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 standard 12. These exam notes for Grade 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.;
Books recommended by teachers
More Study Material