SQL Practical File for CBSE Class 12 Computer Science

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

  1. DDL: The SQL DDL Provides commands for – defining relation, deleting relation, creating indexes and modifying.
  2. I-DML: It includes programs to insert, delete or modify Tuples.
  3. E-DML: It is a form of SQL designed for general purpose programming such as Pascal etc.
  4. View Definitions: It includes commands for defining views.
  5. Integrity- It includes that we you integrity checking.
  6. Authorization – It includes commands for specifying excess rights to relations and views.
  7. 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

  1. 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

 

  1. 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

  1. To show all the details from table employee

SELECT * from employee;

  1. To show specific column data from table employee

 SELECT EMPNO, EMPNAME, SAL from employee;

  1. To show those records where salary range from 5000 to 10000

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

  1. To show records whose name is BLAKE

SELECT * from employee whore EMPNAME = “BLAKE”;

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

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

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

SELECT * from employee where EMPNAME like “A”;

  1. To show records in the alphabetical order of EMPNAME

SELECT * from employee order by EMPNAME;

  1. To Show the records by avoiding duplicate EMPNAME.

 SELECT DISTINCT EMPNAME from employee;

 

OTHER SQL COMMANDS

 

  1. 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.”

  1. To list the names from books of Text Type

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

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

SELECT Book Name, Price FROM Books ORDER BY Price;

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

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

  1. 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;

  1. 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.

Leave a Comment

Your email address will not be published.