Structured Query Language

This page contains the CBSE Computer Science with Python class 12 Unit-3 chapter 2 Structured Query Language. You can find the solutions for chapter 2 of CBSE class 12 Computer Science with Python Exercise. So is the case if you are looking for CBSE class 12 Computer Science with Python related topic Structured Query Language questions and answers for the Exercise.
Buy Class 12 CBSE Computer Science with Python Books NOW!
Exercise
Question 1
1. Mr. Rohan has created a table student with rollno., name, class and section. Now he is confused to set the primary key. So identify the primary key column.
Answer 1
rollno should be the primary key because it uniquely identifies each student record.
SQL
ALTER TABLE student
ADD PRIMARY KEY (rollno);
Question 2
2. Ms. Ravina is using a table customer with custno, name, address and phonenumber. She needs to display name of the customers, whose name start with letter ‘S’. She wrote the following command, which did not give the result.

Select * from customer where name="S%";

Help Ms. Ravina to run the query by removing the errors and write the correct query.

Answer 2
Correction Required: replace = with LIKE for pattern matching and used the pattern 'S%' to match names starting with S.
SQL
SELECT *
FROM customer
WHERE name LIKE 'S%';
Question 3
3. Write SQL query to add a column totalprice with data type numeric and size 10, 2 in a table product.
Answer 3
SQL
ALTER TABLE product
ADD totalprice NUMERIC(10,2);
Question 4
4. The name column of a table student is given below.
Name
Anu Sharma
Rohan Saini
Deepak Sing
Kannika Goal
Kunal Sharma
Based on the information, find the output of the following queries:
Select name from student where name like "%a";
Select name from student where name like "%h%";
Answer 4
SQL
SELECT name
FROM student
WHERE name LIKE "%a";
Result
Anu Sharma
Kunal Sharma
SQL
SELECT name
FROM student
WHERE name LIKE "%h%";
Result
Anu Sharma
Rohan Saini
Kunal Sharma
Question 5
5. A customer table is created with cno,cname, and address columns. Evaluate the following statement whether it is correct or not?

Delete cname from customer;
Answer 5
The given statement is incorrect because DELETE removes rows, not columns. The desired command to remove a column is:
ALTER TABLE customer
DROP COLUMN cname;
Question 6
6. Shopkeeper needs to change the first name of one of his customers in table customer. Which command should he use for the same?
Answer 6
He should use the UPDATE command to change existing customer data.
SQL
UPDATE customer
SET cname='NewName'
WHERE cno=101;
Question 7
7. Sonal needs to display name of teachers, who have “o” as the third character in their name. She wrote the following query.

Select name
From teacher
Where name ="$$o?";

But the query is not producing the result. Identify the problems.

Answer 7
Problems in given query: = is used instead of LIKE, and wildcard symbols are incorrect.
Corrected SQL:
SELECT name
FROM teacher
WHERE name LIKE '__o%';
Question 8
8. Pooja created a table bank in SQL. Later on, she found that there should have been another column in the table. Which command is used to add column to the table?
Answer 8
Use ALTER TABLE ... ADD to add a new column to an existing table without deleting existing data.
SQL
ALTER TABLE bank
ADD branch VARCHAR(20);
Question 9
9. Surpreeth wants to add two more records of customer in customer table. Which command is used to implement this?
Answer 9
Use INSERT INTO command to add new records.
SQL
INSERT INTO customer
VALUES (201,'Aman','Delhi','9999999999');
INSERT INTO customer
VALUES (202,'Sara','Jaipur','8888888888');
Question 10
10. Deepak wants to remove all rows from the table Bank. But he needs to maintain the structure of the table. Which command is used to implement the same?
Answer 10
To remove all rows but keep table structure, use either command below.
SQL
DELETE
FROM tableBank;
TRUNCATE TABLE tableBank;
Question 11
11. While creating table ‘customer”, Rahul forgot to add column price. Which command is used to add new column in the table. Write the command to implement the same.
Answer 11
Use ALTER TABLE ... ADD to insert a new column into an existing table structure.
SQL
ALTER TABLE customer
ADD price NUMERIC(10,2);
Question 12
12. Write the syntax of creating table command.
Answer 12
Use CREATE TABLE to define a new table by giving column names, their data types, and optional constraints like PRIMARY KEY or NOT NULL.
SQL
CREATE TABLE table_name (
	column1 datatype(size) [constraint],
	column2 datatype(size) [constraint]
);
Examples
CREATE TABLE student (
	adno INT PRIMARY KEY,
	sname VARCHAR(30) NOT NULL,
	marks INT
);

CREATE TABLE book (
	bookid INT,
	title VARCHAR(50),
	price NUMERIC(8,2)
);
Question 13
13. Write the syntax of dropping table command.
Answer 13
Use DROP TABLE to permanently remove a table along with all its data and structure from the database.
SQL
DROP TABLE table_name;
Question 14
14. What all are the clause possible in select statement.
Answer 14
Common clauses used in a SELECT statement are:
FROM: specifies the table(s) from which data is taken.
WHERE: filters rows based on a condition.
GROUP BY: makes groups of rows with same values.
HAVING: filters grouped records (used with GROUP BY).
ORDER BY: sorts the result in ascending or descending order.
Examples
SELECT name, marks FROM student WHERE marks > 80;

SELECT class, COUNT(*) FROM student GROUP BY class HAVING COUNT(*) > 5;

SELECT name FROM student ORDER BY name DESC;
Question 15
15. What is the default value of order by command.
Answer 15
Default ordering for ORDER BY is ascending (ASC).
SQL
SELECT *
FROM item
ORDER BY iname;
Question 16
16. Differentiate between delete and drop table command.
Answer 16
Basis
DELETE
DROP TABLE
Purpose
Removes row data from a table.
Removes the complete table from the database.
Table Structure
Table structure remains available for future use.
Table structure is also removed.
Data Removal
Can delete selected rows using WHERE or all rows without WHERE.
All data is removed because the table itself is deleted.
Condition Support
Supports WHERE clause.
Does not use WHERE clause.
Use Case
When data needs to be cleaned/updated but table is still required.
When table is no longer required.
SQL
DELETE
FROM item
WHERE itemno=101;
DROP TABLE item;
Question 17
17. Differentiate between update and alter table command.
Answer 17
Basis
UPDATE
ALTER TABLE
Purpose
Modifies existing values stored in rows.
Modifies the structure of an existing table.
Works On
Table data (records).
Table definition (columns/constraints).
WHERE Clause
Commonly used to update selected rows only.
Not used in normal structure-changing statements like ADD or DROP COLUMN.
Effect on Rows
Changes content of rows but keeps table design same.
Can add, remove, or modify columns; table design changes.
Use Case
When existing data values need correction or replacement.
When table structure needs improvement after creation.
SQL
UPDATE students
SET average=90
WHERE adno=501;
ALTER TABLE students
ADD grade CHAR(1);
Question 18
18. Differentiate between order by and group by command.
Answer 18
Basis
ORDER BY
GROUP BY
Purpose
Sorts the result set in ascending or descending order.
Groups rows with same values for summary calculations.
Result Type
Returns all selected rows in sorted order.
Returns one row per group (usually with aggregate functions).
Used With
Can be used with normal column display and also after grouping.
Commonly used with COUNT(), SUM(), AVG(), MIN(), MAX().
Effect on Data
Only changes display order, not grouping.
Combines similar rows into groups based on column values.
Use Case
When records must be shown alphabetically or numerically.
When category-wise totals or counts are required.
SQL
SELECT *
FROM students
ORDER BY name ASC;
SELECT scode, COUNT(*)
FROM students
GROUP BY scode;
Question 19
19. Define the following.

a)
Union
b)
Cartesian product
c)
Equi Join
d)
Non equi join
Answer 19
a)
Union: Combines results of two SELECT queries with same number and type of columns, and removes duplicate rows.
b)
Cartesian Product: Combines each row of first table with every row of second table, producing all possible row pairs.
c)
Equi Join: Joins two tables using equality condition (usually matching key columns), such as table1.id = table2.id.
d)
Non-Equi Join: Joins two tables using conditions other than equality, such as <, >, <=, or >=.
SQL
SELECT name
FROM students UNION SELECT name
FROM alumni;
SELECT *
FROM students, streams;
SELECT *
FROM students s, streams st
WHERE s.scode=st.scode;
SELECT *
FROM salary s, grade g
WHERE s.amount BETWEEN g.min_amt
AND g.max_amt;
Question 20
20. What is the use of wildcard?
Answer 20
Wildcards are used with LIKE for pattern matching. % matches multiple characters, and _ matches one character.
SQL
SELECT *
FROM students
WHERE name LIKE 'S%';
SELECT *
FROM students
WHERE name LIKE '_a%';
Question 21
21. Create the following table items.

Column name
Data type
Size
Constrain
Itemno
Number
3
Primary key
Iname
Varchar
15
Price
Number
10,2
Quantity
Number
3
Answer 21
SQL
CREATE TABLE items (
    Itemno NUMBER(3) PRIMARY KEY,
    Iname VARCHAR(15),
    Price NUMBER(10,2),
    Quantity NUMBER(3)
);
Question 22
22. Insert the following information:

Table: Item
Itemno
Iname
Price
Quantity
101
Soap
50
100
102
Powder
100
50
103
Face cream
150
25
104
Pen
50
200
105
Soap box
20
100
Answer 22
SQL
INSERT INTO item
VALUES (101,'Soap',50,100);
INSERT INTO item
VALUES (102,'Powder',100,50);
INSERT INTO item
VALUES (103,'Face cream',150,25);
INSERT INTO item
VALUES (104,'Pen',50,200);
INSERT INTO item
VALUES (105,'Soap box',20,100);
OR
INSERT INTO item (itemno, iname, price, quantity)
VALUES
    (101, 'Soap', 50, 100),
    (102, 'Powder', 100, 50),
    (103, 'Face cream', 150, 25),
    (104, 'Pen', 50, 200),
    (105, 'Soap box', 20, 100);
Question 23
23. Write queries based upon item table given in q. no 22.

a)
Display all items information.
b)
Display item name and price value.
c)
Display soap information.
d)
Display the item information whose name starts with letter ‘s’.
e)
Display a report with item number, item name and total price. (total price = price * quantity).
f)
Display item table information in ascending order based upon item name.
g)
Display item name and price in descending order based upon price.
h)
Display item name, whose price is in between 50 to 100.
i)
Add new column totalprice with number (10, 2).
j)
Increase price value by 100.
k)
Fill up totalprice = price * quantity.
l)
Remove powder information.
m)
Remove totalprice column.
n)
Remove whole item structure.
Answer 23
a) Display all items information.
SELECT *
FROM item;
b) Display item name and price value.
SELECT iname, price
FROM item;
c) Display soap information.
SELECT *
FROM item
WHERE iname='Soap';
d) Display the item information whose name starts with letter ‘s’.
SELECT *
FROM item
WHERE iname LIKE 's%';
e) Display a report with item number, item name and total price. (total price = price * quantity).
SELECT itemno, iname, price*quantity AS totalprice
FROM item;
f) Display item table information in ascending order based upon item name.
SELECT *
FROM item
ORDER BY iname ASC;
g) Display item name and price in descending order based upon price.
SELECT iname, price
FROM item
ORDER BY price DESC;
h) Display item name, whose price is in between 50 to 100.
SELECT iname
FROM item
WHERE price BETWEEN 50
AND 100;
i) Add new column totalprice with number (10, 2).
ALTER TABLE item
ADD totalprice NUMBER(10,2);
j) Increase price value by 100.
UPDATE item
SET price = price + 100;
k) Fill up totalprice = price * quantity.
UPDATE item
SET totalprice = price * quantity;
l) Remove powder information.
DELETE
FROM item
WHERE iname='Powder';
m) Remove totalprice column.
ALTER TABLE item
DROP COLUMN totalprice;
n) Remove whole item structure.
DROP TABLE item;
Question 24
24. Write outputs based upon item table given in q. no 22.

a)
select sum(price) from item;
b)
select avg(price) from item;
c)
select min(price) from item;
d)
select max(price) from item;
e)
select count(price) from item;
f)
select distinct price from item;
g)
select count(distinct price) from item;
h)
select iname,price*quantity from item;
Answer 24
a) select sum(price) from item;
SELECT SUM(price)
FROM item;
Result
370
b) select avg(price) from item;
SELECT AVG(price)
FROM item;
Result
74
c) select min(price) from item;
SELECT MIN(price)
FROM item;
Result
20
d) select max(price) from item;
SELECT MAX(price)
FROM item;
Result
150
e) select count(price) from item;
SELECT COUNT(price)
FROM item;
Result
5
f) select distinct price from item;
SELECT DISTINCT price
FROM item;
Result
50
100
150
20
g) select count(distinct price) from item;
SELECT COUNT(DISTINCT price)
FROM item;
Result
4
h) select iname,price*quantity from item;
SELECT iname, price*quantity
FROM item;
Result
Soap 5000
Powder 5000
Face cream 3750
Pen 10000
Soap box 2000
Question 25
25. In a database there are two tables – Brand and Item as shown below:

BRAND
ICODE
BNAME
100
SONY
200
HP
300
LG
400
SAMSUNG
ITEM
ICODE
INAME
PRICE
100
TELEVISION
25000
200
COMPUTER
30000
300
REFRIGERATOR
23000
400
CELL PHONE
40000
Write MYSQL queries for the following:
a)
To display Iname, price and corresponding Brand name (Bname) of those items, whose price is between 25000 and 30000 both values inclusive).
b)
To display ICode, Price and BName of the item, which has IName as “Television”.
c)
To increase the Prices of all items by Rs. 10%.
Answer 25
a) To display Iname, price and corresponding Brand name (Bname) of those items, whose price is between 25000 and 30000 both values inclusive.
SELECT i.iname, i.price, b.bname
FROM item i, brand b
WHERE i.icode = b.icode
AND i.price BETWEEN 25000
AND 30000;
b) To display ICode, Price and BName of the item, which has IName as “Television”.
SELECT i.icode, i.price, b.bname
FROM item i, brand b
WHERE i.icode = b.icode
AND i.iname = 'Television';
c) To increase the Prices of all items by Rs. 10%.
UPDATE item
SET price = price * 1.10;
Question 26
26. Create the following table Students.

Column name
Data type
Size
Constraints
Adno
Integer
3
Primary key
Name
Varchar
20
Average
Integer
3
Sex
Char
1
Scode
Integer
4
Answer 26
SQL
CREATE TABLE students (
    Adno INTEGER(3) PRIMARY KEY,
    Name VARCHAR(20),
    Average INTEGER(3),
    Sex CHAR(1),
    Scode INTEGER(4)
);
Question 27
27. Insert the following information:

Adno
Name
Average
Sex
Scode
501
R.Jain
98
M
111
545
Kavita
73
F
333
705
K.Rashika
85
F
111
754
Rahul Goel
60
M
444
892
Sahil Jain
78
M
333
935
Rohan Saini
85
M
222
955
Anjali
64
F
444
983
Sneha Aggarwal
80
F
222
Answer 27
SQL
INSERT INTO students
VALUES (501,'R.Jain',98,'M',111);
INSERT INTO students
VALUES (545,'Kavita',73,'F',333);
INSERT INTO students
VALUES (705,'K.Rashika',85,'F',111);
INSERT INTO students
VALUES (754,'Rahul Goel',60,'M',444);
INSERT INTO students
VALUES (892,'Sahil Jain',78,'M',333);
INSERT INTO students
VALUES (935,'Rohan Saini',85,'M',222);
INSERT INTO students
VALUES (955,'Anjali',64,'F',444);
INSERT INTO students
VALUES (983,'Sneha Aggarwal',80,'F',222);
OR
INSERT INTO students (adno, name, average, sex, scode)
VALUES
    (501,'R.Jain',98,'M',111),
    (545,'Kavita',73,'F',333),
    (705,'K.Rashika',85,'F',111),
    (754,'Rahul Goel',60,'M',444),
    (892,'Sahil Jain',78,'M',333),
    (935,'Rohan Saini',85,'M',222),
    (955,'Anjali',64,'F',444),
    (983,'Sneha Aggarwal',80,'F',222);
Question 28
28. Write queries based upon item table given in q. no 27.

(i)
Display all students’ information.
(ii)
Display Rohan Saini’s information.
(iii)
Display number of students in the table.
(iv)
Display number of students in each sex.
(v)
Display students’ information in ascending order using name.
(vi)
Display students’ information in descending order using average marks.
(vii)
Display students’ name starting with letter “K”.
(viii)
Display students’ information, whose name ends with “l”.
(ix)
Display a report with adno,name,average*5 as total marks from student table.
(x)
Display students’ information, whose average marks are in between 80 to 90.
(xi)
Display students’ info., who are getting average marks of more than 80 and scode 333.
(xii)
Display students’ name and average marks, whose scode is 222 and 333.
(xiii)
Display sum of average marks.
(xiv)
Display maximum average marks
(xv)
Display minimum average marks.
(xvi)
Display average value of average marks.
(xvii)
Display maximum, minimum and sum of average marks in each scode.
(xviii)
Display number of students in each scode.
Answer 28
(i) Display all students’ information.
SELECT *
FROM students;
(ii) Display Rohan Saini’s information.
SELECT *
FROM students
WHERE name='Rohan Saini';
(iii) Display number of students in the table.
SELECT COUNT(*)
FROM students;
(iv) Display number of students in each sex.
SELECT sex, COUNT(*)
FROM students
GROUP BY sex;
(v) Display students’ information in ascending order using name.
SELECT *
FROM students
ORDER BY name ASC;
(vi) Display students’ information in descending order using average marks.
SELECT *
FROM students
ORDER BY average DESC;
(vii) Display students’ name starting with letter “K”.
SELECT name
FROM students
WHERE name LIKE 'K%';
(viii) Display students’ information, whose name ends with “l”.
SELECT *
FROM students
WHERE name LIKE '%l';
(ix) Display a report with adno,name,average*5 as total marks from student table.
SELECT adno, name, average*5 AS total_marks
FROM students;
(x) Display students’ information, whose average marks are in between 80 to 90.
SELECT *
FROM students
WHERE average BETWEEN 80
AND 90;
(xi) Display students’ info., who are getting average marks of more than 80 and scode 333.
SELECT *
FROM students
WHERE average > 80
AND scode=333;
(xii) Display students’ name and average marks, whose scode is 222 and 333.
SELECT name, average
FROM students
WHERE scode IN (222,333);
(xiii) Display sum of average marks.
SELECT SUM(average)
FROM students;
(xiv) Display maximum average marks.
SELECT MAX(average)
FROM students;
(xv) Display minimum average marks.
SELECT MIN(average)
FROM students;
(xvi) Display average value of average marks.
SELECT AVG(average)
FROM students;
(xvii) Display maximum, minimum and sum of average marks in each scode.
SELECT scode, MAX(average), MIN(average), SUM(average)
FROM students
GROUP BY scode;
(xviii) Display number of students in each scode.
SELECT scode, COUNT(*)
FROM students
GROUP BY scode;
Question 29
29. Create the following table.

Column name
Data type
Size
Constraints
Scode
Integer
3
Primary key
Sname
Varchar
20
Place
Varchar
10
Answer 29
SQL
CREATE TABLE streams (
    Scode INTEGER(3) PRIMARY KEY,
    Sname VARCHAR(20),
    Place VARCHAR(10)
);
Question 30
30. Insert the following information.

Streams
Scode
Sname
Place
111
Science
SBlock
222
Commerce
CBlock
333
Humanity
HBlock
444
Art
ABlock
Answer 30
SQL
INSERT INTO streams
VALUES (111,'Science','SBlock');
INSERT INTO streams
VALUES (222,'Commerce','CBlock');
INSERT INTO streams
VALUES (333,'Humanity','HBlock');
INSERT INTO streams
VALUES (444,'Art','ABlock');
OR
INSERT INTO streams (scode, sname, place)
VALUES
    (111,'Science','SBlock'),
    (222,'Commerce','CBlock'),
    (333,'Humanity','HBlock'),
    (444,'Art','ABlock');
Question 31
31. Write queries based upon item table given in q. no 27& 30.

(i)
To display Adno, Name, Sex and Average from Student’s table and Stream name (Sname) and place from Stream table with respect to Scode.
(ii)
Add the following information into the student table.

999 Deepak Sharma 83 M 2222
(iii)
Display science stream students’ information.
Answer 31
(i) To display Adno, Name, Sex and Average from Student’s table and Stream name (Sname) and place from Stream table with respect to Scode.
SELECT st.adno, st.name, st.sex, st.average, sr.sname, sr.place
FROM students st, streams sr
WHERE st.scode = sr.scode;
(ii) Add the following information into the student table: 999 Deepak Sharma 83 M 2222
INSERT INTO students
VALUES (999,'Deepak Sharma',83,'M',2222);
(iii) Display science stream students’ information.
SELECT st.*
FROM students st, streams sr
WHERE st.scode = sr.scode
AND sr.sname = 'Science';
Question 32
32. Give the output of the following SQL queries.

(i)
Select sum(Average) From students Where sex=’M’;
(ii)
Select distinct (Scode) From students;
Answer 32
Based on records after Q31(ii) insertion:
(i) Select sum(Average) From students Where sex=’M’;
SELECT SUM(Average)
FROM students
WHERE sex='M';
Result
404
(ii) Select distinct (Scode) From students;
SELECT DISTINCT(Scode)
FROM students;
Result
111
333
444
222
2222
Question 33
33. Remove 111 scode information.
Answer 33
SQL
DELETE
FROM streams
WHERE scode = 111;
Question 34
34. Add new column state with varchar(10).
Answer 34
SQL
ALTER TABLE streams
ADD state VARCHAR(10);
Question 35
35. Increment 2 marks for 444 scode info.
Answer 35
SQL
UPDATE students
SET average = average + 2
WHERE scode = 444;
Question 36
36. Remove column state.
Answer 36
SQL
ALTER TABLE streams
DROP COLUMN state;
Question 37
37. Remove the whole table stream.
Answer 37
SQL
DROP TABLE streams;