Page 1 of 8

Computer Science
Class XII
DATA BASE

DBMS: Data Base Management System is a collection of interrelated data and a set of program to access those
data. This collection of data is called the database.

Relation:A relation is a two dimensional table.

Table: A database table is a collection of rows and columns which describes the basic storage structure of
RDBMS.

Attributes: The columns of the table are called attribute.

Tuples: The rows of the table are called tuples.

Degree: The number of attributes in a relation is called degree of the relation.

Cardinality: The number of tuples (rows) in a relation is called its cardinality.

Primary Key: Primary Key is a set of one or more attributes that uniquely identifies each row in a table. The
Primary Key column can not contain null value.

Candidate Key: A candidate key is the one that is capable of becoming primary key.

Alternate Key: A candidate key that is not primary key is called alternate key.

Foreign Key: A non key attribute is called foreign key if it is a primary key of another table.

DDL: DDL is the part of SQL. It stands for Data Definition Language. It provides statements for creation,
modification and deletion of the database. For example CREATE, DROP, ALTER etc.

DML: DML is the part of SQL. It stands for Data Manipulation Language. It provides statements for
manipulating the database. For example SELECT, INSERT etc.

Creating Table:
Command: CREATE TABLE
Syntax: CREATE TABLE table-name(
column-name data-type<COLUMN CONSTRAINT>,
....
....
PRIMARY KEY(column-name,......));
Example: CREATE TABLE employee(
ecode integer NOT NULL,
enamevarchar(20) NOT NULL,
sex char(1),
grade char(2) DEFAULT ‘E1’,
gross decimal,
deptcode char(4),
CHECK(gross>2000),
PRIMARY KEY(ecode),
FOREIGN KEY(deptcode) REFERENCES department(deptcode));

Describing Table:
Command: DESC
 Page 2 of 8

Syntax: DESC table-name; or DESCRIBE table-name;

Example: DESC employee; or DESCRIBE employee;

Inserting Data into Table:

Command: INSERT INTO
Syntax: INSERT INTO table-name(column-name1, column-name2,.....)
VALUES(value,value,......);
Example: INSERT INTO employee
VALUES(123,’Raju’,’M’,’E3’,10000.00,’acco’);

Altering Table:
Command: ALTER TABLE
Syntax for remove column: ALTER TABLE table-name DROP COLUMN column–name;
Example: ALTE TABLE employee DROP COLUMN grade;
Syntax for add column: ALTER TABLE table-name ADD COLUMN column-name datatype(length);
Example: ALTER TABLE employee ADD COLUMN grade char(2);
Syntax for changing an existing column:
ALTER TABLE table-name CHANGE [old column-name] [new column-name] datatype;
Example: ALTER TABLE table-name CHANGE grade score char(2);
Syntax for resize a column: ALTER TABLE table-name MODIFY column-name datatype(length);
Example: ALTER TABLE employee MODIFY enamevarchar(25);

Change the value in the table:

Command: UPDATE
Syntax: UPDATE table-name SET column-name=value1 [WHERE column-name=value];
Example: UPDATE employee SET gross=gross+500 WHERE grade=’E3’;

Deleting rows from a table:

Command: DELETE FROM
Syntax: DELETE FROM table-name [WHERE condition];
Example: DELETE FROM employee;
DELETE FROM employee WHERE grade=’E2’;

Remove a table:
Command: DROP TABLE
Syntax: DROP TABLE table-name;
Example: DROP TABLE employee;

Display the contents of all the columns of a table:

Command: SELECT *
Syntax: SELECT * FROM table-name;
Example: SELECT * FROM employee;

Selecting specific columns:

Syntax: SELECT column1, column2,... FROM table-name;
Example: SELECT ename, grade, sex FROM employee;

Use search condition to specify the rows you want to retrieve from the table:
Command: WHERE
Syntax: SELECT * FROM table-name WHERE condition;
Example: SELECT * FROM employee WHERE grade=’A’;

We can use two types of operators in the condition part:

1. Relational Operators: The mathematical operators which are used to perform certain type of comparison
between two variables are called relational operators. =, >, <, >=, <= are examples of relational operators.
2. Boolean or Logical Operators: Boolean operators are the ones which are either true or false. They are used to
combine one or more conditions. AND, OR, NOT are examples of Boolean operators.
 Page 3 of 8

Remove duplicate rows in the output:

Command: DISTINCT
Syntax: SELECT DISTINCT column-name FROM table-name;
Example: SELECT DISTINCT ename FROM employee;

Retrieve information from the table where the values of a column belong to a specified range:
Command: BETWEEN
Syntax: SELECT * FROM table-name WHERE column-name BETWEEN value1 AND value2;
Example: SELECT * FROM employee WHERE ecode BETWEEN 100 AND 123;

Pattern Matching:
Command: LIKE
Syntax: SELECT * FROM table-name WHERE column-name LIKE condition-based-on-pattern;
Example: SELECT * FROM employee WHERE grade LIKE ‘E%’;

N.B: Percent (%) means zero or one or more characters.

Underscore (_) means exactly one character.

Retrieve information from the table where the values of a column belong to a specified list:
Command: IN
Syntax: SELECT * FROM table-name WHERE column-name in (value 1, value 2,...., value n);
Example: SELECT * FROM employee WHERE grade IN (‘E1’,’E3’,’E5’);
SELECT * FROM employee WHERE grade NOT IN (‘E1’,’E3’,’E5’);

Aggregate Functions:
1. Calculate the average of a specified column:
Name of the function: AVG
Syntax: SELECT AVG(column-name) FROM table-name;
Example: SELECT AVG(gross) FROM employee;

2. Find the maximum value of the column:

Name of the function: MAX
Syntax: SELECT MAX(column-name) FROM table-name;
Example: SELECT MAX(gross) FROM employee;

3. Find the minimum value of the column:

Name of the function: MIN
Syntax: SELECT MIN(column-name) FROM table-name;
Example: SELECT MIN(gross) FROM employee;

4. Add the values of a column:

Name of the function: SUM
Syntax: SELECT SUM(column-name) FROM table-name;
Example: SELECT SUM(gross) FROM employee;

5. Count the number of values in a given column or number of rows in a table:

Name of the function: COUNT
Syntax: SELECT COUNT(column-name) FROM table-name; or
SELECT COUNT(*) FROM table-name;
Example: SELECT COUNT(ecode) FROM employee;
SELECT COUNT(*) FROM employee;

Sorting Results:
Command: ORDER BY
Syntax: SELECT column-name1 [,column-name2,...] FROM table-name [WHERE <condition>]
ORDER BY column-name [ASC/DESC];
 Page 4 of 8

Example: SELECT * FROM employee;

SELECT ename, gross FROM employee WHERE gross>5000
ORDERBY ename DESC;

Group the rows in the result table by columns that have the same values, so that each group is reduced to a
single row:
Command: GROUP BY
Syntax: SELECT column-name1, column-name2,.... FROM table-name
GROUP BY column-name;
Example: SELECT grade, AVG(gross) FROM employee GROUP BY grade;

Apply condition to restrict grouped rows that appear in the result table:
Command: HAVING
Syntax: SELECT column-name1, column-name2, .... FROM table-name
GROUP BY column-name HAVING column-name CONDITION value;
Example: SELECT grade, AVG(gross) FROM employee GROUP BY grade
HAVING AVG(gross)>5000;

Check whether a column value is NULL or NOT:

Command: IS NULL
Syntax: SELECT * FROM table-name WHERE column-name IS NULL;
Example: SELECT * FROM employee WHERE deptcode IS NULL;
Command: IS NOT NULL
Syntax: SELECT * FROM table-name WHERE column-name IS NOT NULL;
Example: SELECT * FROM employee WHERE deptcode IS NOT NULL;

Column Alias:
Command: AS
Syntax: SELECT column-name AS alias-name FROM table-name;
Example: SELECT ename AS EmployeeName FROM employee;

Previous Year Questions:

Q1. Explain the concept of candidate key with the help of appropriate example. 2
Ans: A candidate key is the one that is capable of becoming primary key.

AdmissionNo RollNo Name Class

1245 1 Ajay Kumar XII
1248 2 Bijay Kumar XI
Here. AdmissionNo and RollNo both can identify records uniquely, so both of them are candidate
for primary key. So we can say they are candidate key.

Q2. Give a suitable example of a table with sample data and illustrate Primary and Alternate Keys in it. 2
Ans: Primary Key is a set of one or more attributes that uniquely identifies each row in a table. The
Primary Key column can not contain null value.
A candidate key that is not primary key is called alternate key.
AdmissionNo RollNo Name Class
1245 1 Ajay Kumar XII
1248 2 Bijay Kumar XI
Here. AdmissionNo and RollNo both can identify records uniquely, so both of them are candidate
for primary key. But AdmissionNo is chosen as Primary Key. So in this case RollNo will be
Alternate Key.
 Page 5 of 8

Q3. What do you understand by Union & Cartesian Product operations in relational algebra? 2
Ans: Cartesian Product (binary operator): It operates on two relations and isdenoted by X. For example
Cartesian product of two relations R1 and R2 isrepresented by R = R1 X R2. The degree of R is
equal to sum of degrees ofR1 and R2. The cardinality of R is product of cardinality of R1 and
cardinalityof R2.
Example:

Union (binary operator): It operates on two relations and is indicated by U.For example,
R=R1 U R2 represents union operation between two relationsR1 and R2. The degree of R is
equal to degree of R1. The cardinality of R issum of cardinality of R1 and cardinality of R2.
Following have to be considered for the operation R1 U R2.
• Degree of R1 = Degree of R2
• jth attribute of R1 and jth attribute of R2 must have a common domain.
 Page 6 of 8

Q4. What do you understand by Primary Key? Give a suitable example of Primary Key from a table
containing some meaningful data.
Ans: Primary Key is a set of one or more attributes that uniquely identifies each row in a table. The
Primary Key column can not contain null value.
Example:
AdmissionNo RollNo Name Class
1245 1 Ajay Kumar XII
1248 2 Bijay Kumar XI
Here we can chose AdmissionNo as Primary Key because it uniquely identifies each row in the
table.

Q5. Differentiate between primary key and alternate key. 2

Ans: Try yourself.

Q6. What do you understand by the terms Primary Key and Degree of a relation inrelational database?
Ans: Try yourself.

Q7. What do you understand by normalization? What is First Normal Form? 2

Ans: Normalization of datacan be considered a process (1) minimizing redundancy and (2)
minimizing the insertion, deletion, and updateanomalies.

First Normal Form states thatthe domain of an attribute must include only atomic (simple,
indivisible) values andthat the value of any attribute in a tuple must be a single value from the
domain ofthat attribute.

Q8. What is the difference between degree and cardinality of a table? What is the degree and cardinality
of the following table? 2

Ans: Try yourself.

Q9. What do you understand by selection and projection operation in relational algebra? 2
Ans: A selection is unary operation. One can consider the SELECT operation to be afilter that
keeps only those tuples that satisfy a qualifying condition.In general, the SELECT operation
is denoted by
σ<selection condition>(R)
where the symbol σ (sigma) is used to denote the SELECT operator and the
selectioncondition is a Boolean expression (condition) specified on the attributes ofrelation R.
For example, to select the EMPLOYEE tuples whose department is4, or those whose salary is
greater than $30,000, we can individually specify each ofthese two conditions with a
SELECT operation as follows:
σDno=4(EMPLOYEE)
σSalary>30000(EMPLOYEE)

The SELECT operation chooses some of the rowsfrom the table while discarding other rows.
The PROJECT operation, on the otherhand, selects certain columns from the table and
discards the other columns.The general form of the PROJECToperation is
π<attribute list>(R)
where π (pi) is the symbol used to represent the PROJECT operation, and <attributelist> is
the desired sub list of attributes from the attributes of relation R.For example, to list each
employee’sfirst and last name and salary, we can use the PROJECT operation as follows:
πLname, Fname, Salary(EMPLOYEE)
 Page 7 of 8

Q10. Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned in (h1) to (h4)
parts on the basis of ITEMS and TRADERS.

(a) To display the details of all the items in ascending order of item names (i.e. INAME).
(b) To display item name and price of all those items, whose price is in the range of 10000 and
22000 (both values inclusive).
(c) To display the number of items, which are traded by each trader. The expected output of the
query should be
T01 2
T02 2
T03 1
(d) To display the Price, item name (i.e. INAME) and quantity (i.e. QTY) of those items which have
quantity more than 150.
(e) To display the names of those traders, who are either from DELHI or form MUMBAI.
(f) To display the name of the companies and the name of the items in descending order of company
names.
(h1) SELECT MAX(PRICE), MIN(PRICE) FROM ITEMS;
(h2) SELECT PRICE*QTY AMOUNT FROM ITEMS WHERE CODE=1004;
(h3) SELECT DISTINCT TCODE FROM ITEMS;
(h4) SELECT INAME, TNAME FROM ITEMS I, TRADERS T WHERE I.TCODE=T.TCODE
AND QTY<100;

Ans: (a) SELECT * FROM ITEMS ORDER BY INAME;

(b) SELECT INAME, PRICE FROM ITEMS WHERE PRICE BETWEEN 10000 AND
22000;
(c) SELECT TCODE, COUNT(TCODE) FROM ITEMS GROUP BY TCODE;
(d) SELECT PRICE, INAME, QTY FROM ITEMS WHERE QTY>150;
(e) SELECT TNAME FROM TRADERS WHERE CITY=’DELHI’ OR CITY=’MUMBAI’;
(f) SELECT COMPANY, INAME FROM ITEMS ORDER BY COMPANY DESC;
(h1) MAX(PRICE) MIN(PRICE)
38000 1200

(h2) AMOUNT
1075000

(h3) TCODE
T01
T02
T03
 Page 8 of 8

(h4) INAME TNAME

CAR GPS SYSTEM ELECTRONIC SALES
LED SCREEN 40 DISP HOUSE INC

Q11.