SQL Practical File for CBSE Class 12 Computer Science

STRUCTURED QUERY LANGUAGE

It is a simple query language used to accessing, handling and managing data in relational databases. It enables to create and operate on rational databases, which are sets of related information stored in tables.

PROCESSING CAPABILITIES OF SQL

DDL: The SQL DDL Provides commands for – defining relation, deleting relation, creating indexes and modifying.
I-DML: It includes programs to insert, delete or modify Tuples.
E-DML: It is a form of SQL designed for general purpose programming such as Pascal etc.
View Definitions: It includes commands for defining views.
Integrity- It includes that we you integrity checking.
Authorization – It includes commands for specifying excess rights to relations and views.
Transaction Control: It includes commands for specify the beginning and ending of transactions.

DATA DEFINATION LANGUAGE

DATA DICTIONARY: It is a file that contain “metadata” ie, data about data.

DDL provides a set of definitions to specify the storage structure and access methods used by the database system.

DATA MANIPULATION LANGUAGE

DML is language that enables users to access or manipulate data as organised by the appropriate data method.

COMMANDS IN SQL

Create Table Command: It is used to create a new column name and its data type.

CREATE TABLE EMPLOYEE

(EMPNO INTEGER PRIMARY KEY,

EMPNAME CHAR(20) NOT NULL,

JOB CHAR (25) NOT NULL,

MGR INTEGER,

HIREDATE DATE,

SAL DOUBLE CHECK (SAL>5000),

COMM REAL,

DEPTNO INTEGER);

INSERT INTO – It is used to add records.

Example –

INSERT INTO employee value (1008, “XYZ”, “CLERK”, 850, {23/12/2013}, 7500, 10, 5)

TABLE EMPLOYEE

EmpNo	Emp Name	Job	Mgr	HireDate	Sal	Comm	Dept No
7839	King	President		07-Nov-81	5000		10
7698	Blake	Manager	7839	01-May-81	2850		30
7782	Clark	Manager	7839	09-Jun-81	2450		10
7566	Jones	Manager	7839	02-April-81	2975		20
7654	Martin	Salesman	9698	02-April-81	1250	1400	30
7499	Allen	Salesman	7698	20-Feb-81	1600	300	30
7844	Turner	Salesman	7698	08-Sep-81	1500	0	30
7900	James	Clerk	7698	03-Dec-81	950		30
7521	Ward	Salesman	7698	02-Feb-81	1250	500	30

The SELECT Command: This is used to display or print the contents of the table on the screen. Clauses available with SELECT statements are:

a) WHERE

b) ORDER BY

SQL COMMANDS

To show all the details from table employee

SELECT * from employee;

To show specific column data from table employee

SELECT EMPNO, EMPNAME, SAL from employee;

To show those records where salary range from 5000 to 10000

SELECT * from employee where SAL >=5000 and SAL <= 10000;

To show records whose name is BLAKE

SELECT * from employee whore EMPNAME = “BLAKE”;

To show records of those whose name does not start with “A”.

SELECT * from employee where EMPNAME not like “A%”;

To show those records whose name starts with “A”.

SELECT * from employee where EMPNAME like “A”;

To show records in the alphabetical order of EMPNAME

SELECT * from employee order by EMPNAME;

To Show the records by avoiding duplicate EMPNAME.

SELECT DISTINCT EMPNAME from employee;

OTHER SQL COMMANDS

To show Book name, Author name and Price of books of first publ. publishers.

SELECT Book NAME, Author NAME, Price FROM Books WHERE Publishers = “first Publ.”

To list the names from books of Text Type

SELECT Book Name FROM Books WHERE BOOKS Type = “Text”;

To display the names and price from books in ascending order of their price.

SELECT Book Name, Price FROM Books ORDER BY Price;

To increase the price of all book of EPB Publishers by 50.

UPDATE Books SET Price = Price +50 WHERE Publisher = “EPB”

To display BOOK ID, BOOK NAME for all books which have been issued.

SELECT BOOKS.BOOK ID, BOOK NAME FROM BOOKISSUED

WHERE BOOKS.BOOK. ID = ISSUED. BOOKID;

To insert new row in the table Issued having data “F0003”, 1.

INSERT INTO Issued VALUES (“F0003”, 1);

In above command two different tables are used , Table: BOOKS and Table: ISSUED with different data.