CBSE Class 12 Computer Science Database And SQL Concepts

Data :- Raw facts and figures which are useful to an organization. We cannot take decisions on the basis of data.

Information:-Well processed data is called information. We can take decisions on the basis of information

Field: Set of characters that represents specific data element.

Record: Collection of fields is called a record. A record can have fields of different data types.

File: Collection of similar types of records is called a file.

Table: Collection of rows and columns that contains useful data/information is called a table. A table generally refers to the passive entity which is kept in secondarystorage device.

Relation: Relation (collection of rows and columns) generally refers to an active entity on which we can perform various operations.

Database: Collection of logically related data along with its description is termed as database.

Tuple: A row in a relation is called a tuple.

Attribute: A column in a relation is called an attribute. It is also termed as field or data item.

Degree: Number of attributes in a relation is called degree of a relation.

Cardinality: Number of tuples in a relation is called cardinality of a relation.

Primary Key: Primary key is a key that can uniquely identifies the records/tuples in a relation. This key can never be duplicated and NULL.

Foreign Key: Foreign Key is a key that is defined as a primary key in some other relation. This key is used to enforce referential integrity in RDBMS.

Candidate Key: Set of all attributes which can serve as a primary key in a relation.

Alternate Key: All the candidate keys other than the primary keys of a relation are alternate keys for a relation.

DBA: Data Base Administrator is a person (manager) that is responsible for defining the data base schema, setting security features in database, ensuring proper functioning of the data bases etc. Data types of SQL Just like any other programming language, the facility of defining data of various types is available in

SQL also. Following are the most common data types of SQL.

       1) NUMBER

       2) CHAR

       3) VARCHAR / VARCHAR2

       4) DATE

       5) LONG

       6) RAW/LONG RAW

Constraints:

Constraints are the conditions that can be enforced on the attributes of a relation. The constraints come in play when ever we try to insert, delete or update a record in a relation.They are used to ensure integrity of a relation, hence named as integrity constraints.

1. NOT NULL

2. UNIQUE

3. PRIMARY KEY

4. FOREIGN KEY

5. CHECK

6. DEFAULT

i. Not Null constraint : It ensures that the column cannot contain a NULL value.

ii. Unique constraint : A candidate key is a combination of one or more columns, the value of which uniquely identifies each row of a table.

iii. Primary Key : It ensures two things : (i) Unique identification of each row in the table.

(ii)No column that is part of the Primary Key constraint can contain a NULL value.

iv. Foreign Key : The foreign key designates a column or combination of columns as a foreign key and establishes its relationship with a primary key in different table.
Create table Fee

(RollNo number(2) Foreign key (Rollno) references Student (Rollno),

Name varchar2(20) Not null, Amount

number(4), Fee_Date date);

v. Check Constraint : Sometimes we may require that values in some of the columns of our table are to be within a certain range or they must satisfy
cceorntdaiintions.

Example:

Create table Employee

(EmpNo number(4) Primary Key,

Name varchar2(20) Not Null,

Salary number(6,2) check (salary > 0),

DeptNo number(3)
);
Data Manipulation in SQL

DML Commands are as under:

SELECT - Used for making queries

INSERT - Used for adding new row or record into table

UPDATE- used for modification in existing data in a table

DELETE – used for deletion of records.

INSERT Statement

To insert a new tuple into a table is to use the insert statement

insert into <table> [(<column i, . . . , column j>)] values (<value i, . . . , value j>);

INSERT INTO student VALUES(101,'Rohan','XI',400,'Jammu');

While inserting the record it should be checked that the values passed are of same data types as the one

which is specified for that particular column.

For inserting a row interactively (from keyboard) & operator can be used.

e.g INSERT INTO student VALUES(&Roll_no’,’&Name’,’&Class’,’&Marks’,’&City’);

In the above command the values for all the columns are read from keyboard and inserted into the table student.

2 marks Practice questions

Question 1. What is relation? What is the difference between a tuple and an attribute?
Answer: A relation refers to a table in a relational database, consisting of rows and columns, which represents an active entity on which various operations can be performed.
Difference between tuple and attribute:
- Tuple: A row in a relation representing a single record or entity instance.
- Attribute: A column in a relation representing a specific property or characteristic of the entity.
In simple words: A relation is a table where rows are individual records (tuples) and columns are the data fields (attributes).

Exam Tip: Remember that relation is synonymous with a table, tuple with a row, and attribute with a column.

Question 2. Define the following terminologies used in Relational Algebra: (i) selection (ii) projection (iii) union (iv) Cartesian product
Answer:
(i) Selection: Yields a subset of rows from a relation that satisfy a specified condition, denoted by \(\sigma\).
(ii) Projection: Yields a subset of specified columns from a relation, denoted by \(\pi\).
(iii) Union: Combines two union-compatible relations into a single relation containing all tuples from both, eliminating duplicates.
(iv) Cartesian product: Combines two relations such that the resultant relation has columns equal to the sum of degrees and rows equal to the product of cardinalities of the two relations.
In simple words: Selection filters rows, projection picks columns, union merges tables, and Cartesian product multiplies all rows across tables.

Exam Tip: Clearly state the symbols (\(\sigma\) for selection, \(\pi\) for projection) when defining relational algebra operations.

Question 3. What are DDL and DML?
Answer:
- DDL (Data Definition Language): Contains commands used to create and modify database structures, tables, indexes, and views (e.g., CREATE TABLE, ALTER TABLE).
- DML (Data Manipulation Language): Contains commands used to manipulate database objects and query information (e.g., SELECT, INSERT, UPDATE, DELETE).
In simple words: DDL manages the structure of the database, while DML manages the data stored inside it.

Exam Tip: Always give examples of commands for each category to secure full marks.

Question 4. Differentiate between primary key and candidate key in a relation?
Answer:
- Primary Key: A specific key chosen from the candidate keys to uniquely identify each record in a relation. It cannot contain NULL values or duplicates.
- Candidate Key: A set of one or more attributes that can uniquely identify tuples within a relation. Every table can have multiple candidate keys, but only one primary key.
In simple words: Candidate keys are all possible unique identifiers, and the primary key is the one chosen out of them for the table.

Exam Tip: Emphasize that every primary key is a candidate key, but not every candidate key is a primary key.

Question 5. What do you understand by the terms Cardinality and Degree of a relation in relational database?
Answer:
- Cardinality: The total number of tuples (rows) present in a relation.
- Degree: The total number of attributes (columns) present in a relation.
In simple words: Degree counts the columns, and cardinality counts the rows.

Question 6. Differentiate between DDL and DML. Mention the 2 commands for each category.
Answer:
- DDL deals with database structure definition, whereas DML deals with data retrieval and modification.
- DDL Commands: CREATE TABLE, DROP TABLE
- DML Commands: SELECT, INSERT
In simple words: DDL builds the database skeleton, while DML handles the data inside it.

Exam Tip: List exact, valid SQL commands to avoid losing marks.

Database and SQL : 6 marks questions

Question 1. Write SQL Command for (a) to (d) and output of (e) based on table GRADUATE.
(a) List the names of those students who have obtained DIV I sorted by NAME.
(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year assuming that the STIPEND is paid every month.
(c) To count the number of students who are either PHYSICS or COMPUTER SC graduates.
(d) To insert a new row in the GRADUATE table: 11, "KAJOL", 300, "computer sc", 75, 1
(e) Give the output of following sql statement based on table GRADUATE:
(i) Select MIN(AVERAGE) from GRADUATE where SUBJECT="PHYSICS";
(ii) Select SUM(STIPEND) from GRADUATE WHERE div=2;
(iii) Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;
(iv) Select COUNT(distinct SUBJECT) from GRADUATE;
Answer:
(a) SELECT NAME FROM GRADUATE WHERE DIV = 'I' ORDER BY NAME;
(b) SELECT NAME, STIPEND, SUBJECT, STIPEND*12 FROM GRADUATE;
(c) SELECT SUBJECT, COUNT(*) FROM GRADUATE GROUP BY SUBJECT HAVING SUBJECT='PHYSICS' OR SUBJECT='COMPUTER SC';
(d) INSERT INTO GRADUATE VALUES(11, 'KAJOL', 300, 'COMPUTER SC', 75, 1);
(e) Output:
(i) 63
(ii) 800
(iii) 475
(iv) 4
In simple words: These queries demonstrate filtering, sorting, arithmetic calculations, grouping, aggregate functions, and record insertion.

Exam Tip: Ensure string literals use single quotes and match exact column names specified in the table schema.

Question 2. Consider the following tables Sender and Recipient. Write SQL commands for the statements (i) to (iv) and give the outputs for SQL queries (v) to (viii).
(i) To display the names of all Senders from Mumbai
(ii) To display the RecIC, Sendername, SenderAddress, RecName, RecAddress for every Recipient.
(iii) To display Recipient details in ascending order of RecName
(iv) To display number of Recipients from each city
(v) SELECT DISTINCT SenderCity from Sender;
(vi) SELECT A.SenderName, B.RecName From Sender A, Recipient B Where A.SenderID = B.SenderID AND B.RecCity = 'Mumbai';
(vii) SELECT RecName, RecAddress From Recipient Where RecCity NOT IN ('Mumbai', 'Kolkata');
(viii) SELECT RecID, RecName FROM Recipient Where SenderID = 'MU02' or SenderID = 'ND50';
Answer:
Commands (i) to (iv):
(i) SELECT sendername FROM Sender WHERE sendercity = 'Mumbai';
(ii) SELECT R.RecIC, S.Sendername, S.SenderAddress, R.RecName, R.RecAddress FROM Sender S, Recipient R WHERE S.SenderID = R.SenderID;
(iii) SELECT * FROM Recipient ORDER BY RecName;
(iv) SELECT RecCity, COUNT(*) FROM Recipient GROUP BY RecCity;
Outputs (v) to (viii):
(v) SenderCity
Mumbai
New Delhi
(vi) A.SenderName | B.RecName
R Jain | H Singh
S Jha | P K Swamy
(vii) RecName | RecAddress
S Mahajan | 116, A Vihar
S Tripathi | 13, BID, Mayur Vihar
(viii) RecID | RecName
ND08 | S Mahajan
ND48 | S Tripathi
In simple words: These problems cover basic table queries, table joins, sorting, grouping with aggregate functions, and condition filtering.

Exam Tip: For JOIN queries, always qualify column names with table aliases to prevent ambiguity errors.

Question 3. Write SQL command for (a) to (f) on the basis of the table SPORTS.
(a) Display the names of the students who have grade 'C' in either Game1 or Game2 or both.
(b) Display the number of students getting grade 'A' in Cricket.
(c) Display the names of the students who have same game for both Game1 and Game2.
(d) Display the games taken up by the students, whose name starts with 'A'.
(e) Add a new column named 'Marks'.
(f) Assign a value 200 for Marks for all those who are getting grade 'B' or grade 'A' in both Game1 and Game2.
Answer:
(a) SELECT Name FROM SPORTS WHERE Grade = 'C' OR Grade2 = 'C';
(b) SELECT Count(*) FROM SPORTS WHERE Game1 = 'Cricket' AND Grade = 'A';
(c) SELECT Name FROM SPORTS WHERE Game1 = Game2;
(d) SELECT Game1, Game2 FROM SPORTS WHERE Name LIKE 'A%';
(e) ALTER TABLE SPORTS ADD (Marks INT(4));
(f) UPDATE SPORTS SET Marks = 200 WHERE Grade IN ('A', 'B') AND Grade2 IN ('A', 'B');
In simple words: These queries test pattern matching, conditional operators, table modification using ALTER, and data updates using UPDATE.

Exam Tip: Pay close attention to wildcard operators like '%' for pattern matching with LIKE.

Question 4. Consider the following tables Stationary and Consumer. Write SQL commands for the statement (i) to (iv) and output for SQL queries (v) to (viii).
(i) To display the details of those consumers whose Address is Delhi.
(ii) To display the details of Stationary whose Price is in the range of 8 to 15. (Both Value included)
(iii) To display the ConsumerName, Address from Table Consumer, and Company and Price from table Stationary, with their corresponding matching S_ID.
(iv) To increase the Price of all stationary by 2.
(v) SELECT DISTINCT Address FROM Consumer;
(vi) SELECT Company, MAX(Price), MIN(Price), COUNT(*) FROM Stationary GROUP BY Company;
(vii) SELECT Consumer.ConsumerName, Stationary.StationaryName, Stationary.Price FROM Stationary, Consumer WHERE Consumer.S_ID = Stationary.S_ID;
(viii) SELECT StationaryName, Price*3 FROM Stationary;
Answer:
Commands (i) to (iv):
(i) SELECT * FROM Consumer WHERE Address = 'Delhi';
(ii) SELECT * FROM Stationary WHERE Price BETWEEN 8 AND 15;
(iii) SELECT Consumer.ConsumerName, Consumer.Address, Stationary.Company, Stationary.Price FROM Consumer, Stationary WHERE Consumer.S_ID = Stationary.S_ID;
(iv) UPDATE Stationary SET Price = Price + 2;
Outputs (v) to (viii):
(v) Address
Delhi
Mumbai
Bangalore
(vi) Company | MAX(Price) | MIN(Price) | COUNT(*)
ABC | 15 | 10 | 2
XYZ | 7 | 6 | 2
CAM | 5 | 5 | 1
(vii) ConsumerName | StationaryName | Price
Good Learner | Pencil | 5
Write Well | Gel Pen | 15
Topper | Dot Pen | 10
Write & Draw | Pencil | 6
Motivation | Pencil | 5
(viii) StationaryName | Price*3
Dot Pen | 30
Pencil | 18
Eraser | 21
Pencil | 15
Gel Pen | 45
In simple words: This exercises table joins, aggregate grouping functions, arithmetic expressions in projections, and conditional updates.

Exam Tip: When using BETWEEN, both lower and upper boundary values are inclusive.

Question 5. Consider the following tables GARMENT and FABRIC. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).
(i) To display GCODE and DESCRIPTION of each dress in descending order of GCODE.
(ii) To display the details of all the GARMENTs, which have READYDATE in between 08-DEC-07 and 16-JUN-08 (inclusive of both the dates).
(iii) To display the average PRICE of all the GARMENTs, which are made up of FABRIC with FCODE as F03.
(iv) To display FABRIC wise highest and lowest price of GARMENTs from GARMENT table. (Display FCODE of each GARMENT along with highest and lowest price)
(v) SELECT SUM(PRICE) FROM GARMENT WHERE FCODE = 'F01';
(vi) SELECT DESCRIPTION, TYPE FROM GARMENT, FABRIC WHERE GARMENT.FCODE = FABRIC.FCODE AND GARMENT.PRICE >= 1260;
(vii) SELECT MAX(FCODE) FROM FABRIC;
(viii) SELECT COUNT(DISTINCT PRICE) FROM FABRIC;
Answer:
Commands (i) to (iv):
(i) SELECT GCODE, DESCRIPTION FROM GARMENT ORDER BY GCODE DESC;
(ii) SELECT * FROM GARMENT WHERE READYDATE BETWEEN '08-DEC-07' AND '16-JUN-08';
(iii) SELECT AVG(PRICE) FROM GARMENT WHERE FCODE = 'F03';
(iv) SELECT FCODE, MAX(PRICE), MIN(PRICE) FROM GARMENT GROUP BY FCODE;
Outputs (v) to (viii):
(v) SUM(PRICE) = 2600
(vi) DESCRIPTION | TYPE
FORMAL SHIRT | TERELENE
INFORMAL SHIRT | COTTON
INFORMAL PANT | COTTON
FORMAL PANT | TERELENE
(vii) MAX(FCODE) = F04
(viii) COUNT(DISTINCT PRICE) = 0 (Note: evaluated on FABRIC table which does not contain PRICE column, hence returns error or 0 depending on SQL dialect; standard execution evaluates to empty or column reference error if strictly scoped, but functionally distinct prices in GARMENT is 7).
In simple words: These queries test aggregate functions like SUM, AVG, MAX, MIN, grouping data, and date-range filtering.

Exam Tip: Pay close attention when grouping data; every column in the SELECT clause must be either an aggregate function or listed in the GROUP BY clause.

Question 6. Consider the following WORKERS and DESIG. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (vi)
(i) To display the content of workers table in ascending order of first name.
(ii) To display the FIRSTNAME, CITY and TOTAL SALARY of all Clerks from the tables workers and design, where TOTAL SALARY = SALARY + BENEFITS.
(iii) To display the minimum SALARY among Managers and Clerks from the table DESIG.
(iv) Increase the BENEFITS of all Salesmen by 10% in table DESIG.
(v) SELECT FIRSTNAME, SALARY FROM WORKERS, DESIG WHERE DESIGNATION = 'Manager' AND WORKERS.W_ID = DESIG.W_ID;
(vi) SELECT DESIGNATION, SUM(SALARY) FROM DESIG GROUP BY DESIGNATION HAVING COUNT(*) >= 2;
Answer:
Commands (i) to (iv):
(i) SELECT * FROM WORKERS ORDER BY FIRSTNAME ASC;
(ii) SELECT WORKERS.FIRSTNAME, WORKERS.CITY, (DESIG.SALARY + DESIG.BENEFITS) AS TOTAL_SALARY FROM WORKERS, DESIG WHERE WORKERS.W_ID = DESIG.W_ID AND DESIG.DESIGNATION = 'Clerk';
(iii) SELECT MIN(SALARY) FROM DESIG WHERE DESIGNATION IN ('Manager', 'Clerk');
(iv) UPDATE DESIG SET BENEFITS = BENEFITS * 1.10 WHERE DESIGNATION = 'Salesman';
Outputs (v) to (vi):
(v) FIRSTNAME | SALARY
Sam | 75000
Manila | 70000
George | 75000
(vi) DESIGNATION | SUM(SALARY)
Manager | 220000
Clerk | 135000
Salesman | 60000
In simple words: This final set covers sorting, calculated columns, aggregate filters with HAVING, and conditional updates with percentage increments.

Exam Tip: Use the HAVING clause when filtering results after applying group-by aggregate functions.