CLASS 10 ICT [402] QUERIES

QUERIES
Database Languages having two type:
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
DDL Statements:
• Create: Using this statement, a database or set of tables can be created.
• Alternate: This statement is used to change the table’s structure.
• Drop: This statement is used to remove database objects from the system.
DML statements:
• SELECT: The statement “SELECT” is used to get data from the database.
• INSERT: The statement “INSERT” is used to add a new record to the database.
• DELETE: The database can be cleaned out by using the statement DELETE.
• UPDATE: This statement is used to modify the database’s information.
Select Statement
Select statement is most important for retrieving and for displaying data from a database.
Syntax of Select Statement is –
SELECT * FROM <TABLENAME>;
The SELECT statement has many optional clauses:
• WHERE specifies which rows to retrieve.
• ORDER BY specifies an order in which to return the rows.
Table Name – product

Product_No Product_Name Price Quantity

25 Soap 40 80

31 Powder 80 30

45 Shampoo 250 25

52 Soap Box 120 100

Question 1 – Write a Query to display all record from the table;
Answer: Select * from product;
Question 2 – Write a Query to display product name from the table;
Answer: Select Product_Name from product;
Question 3 – Write a Query to display Product_Name and Price from the table;
Answer: Select Product_Name, Price from product;
Page 1 of 12
CLASS 10 ICT [402] QUERIES

Question 4 – Write a Query to find the total no of quantity available in table;

Answer: Select sum(quantity) from product;
Question 5- Display the total amount of each item. The amount must be calculated as
the price multiplied by quantity for each item.
Answer: Select Product_No, Product_Name, Price * Quantity from product;
Question 6- Write a Query to display the product whose price is less than 90
Answer: Select * from product where price < 90;
Question 7- Write a Query to find the total amount of the Shampoo product;
Answer: Select Price*Quantity from product where Product_Name = ‘Shampoo’;
Question 8- Write a Query to display the data whose quantity is equal to 80.
Answer: Select * from product where quantity = 80;
Question 9- Write a Query to display a list of Products whose Price between 40 to 120.
Answer: Select * from product where Price >= 40 and Price <= 120;
Question 10- Write a Query to display the list of Product_Name in alphabetical order.
Answer: Select * from product order by Product_Name ASC;
Question 11- Write a Query to display the list of Price in descending order.
Answer: Select * from product order by Price DESC;
UPDATE statement
Update statement is used to update existing records present in database. The updated
statement will apply using WHERE clause.
Syntax of Update Statement –
UPDATE <table name> SET = value [, column_name = value ...] [WHERE ];
Question 12 – Write a Query to update the price of Shampoo in the product table.
Answer: Update product Set Price = 300 where Price = 250;
Question 13 – Write a Query to update the Quantity of Powder in the product table.
Answer: Update product Set Quantity = 50 where Product_Name = ‘Powder’;

Page 2 of 12
CLASS 10 ICT [402] QUERIES

IMPORTANT SQL COMMANDS

Q1. What do you mean by query in Open Office Base?
Ans. A query is a request to collect specific information from a table or combination of
tables.
Q2. In how many ways you can create query in Open Office Base?
Ans. We can create query in Open Office Base by three ways which are :
1. Create query in Design view
2. Create query using Wizard
3. Create query in SQL view
Q3. Name the Query language which is used in Base?
Ans. SQL
Q4. Which command is used to retrieve data from the table?
Ans. Select command is used to retrieve data from the table.
Q5. Name two categories of SQL Commands.
Ans. Two categories of SQL Commands are :
1. DDL
2. DML
Q6. Differentiate between DDL and DML Commands.
Ans.

DDL DML

It stands for Data Definition Language It stands for Data Manipulation Language

Example : Create, Alter, Drop Example : Insert, Update, Delete

Q7. Identify the DML Commands from the following :

1. Create
2. Alter
3. Insert
4. Delete
Ans. Insert and Delete are DML Commands

Page 3 of 12
CLASS 10 ICT [402] QUERIES

Q8. _____ is the most commonly used Data Manipulation Language(DML) command.
Ans. Select
Q9. Name two clauses which can be used with Select Command.
Ans. Two clauses which can be used with Select Command are :
1. Where Clause
2. Order By Clause
Q10. Write the Select command to display all the records of table “book”.
Ans. Select * from book;
Q11. Write the shortcut to execute query in “Create query in SQL view” of Base.
Ans. F5
Q12. What is the purpose of Where clause in Select Command?
Ans. Where Clause is used to retrieve specific record from the table.
Q13. What is the purpose of Order by clause in Select Command?
Ans. Order by clause is used to arrange the records in ascending or descending order.
Q14. Write the queries for the following table : Emp

Emp_id Ename Salary

1 Suman 20000

2 Sanjay 32000

3 Ravi 30000

a. Display the salary of all the employees after incrementing by Rs 1000.

Ans. Select Salary +1000 from Emp;
b. Display the Employee id and salary of all the employees after decreasing by Rs 500.
Ans. Select Emp_id, Salary – 500 from Emp;
c. Display the Name and salary of all the employees after incrementing it as thrice the
amount of present salary.
Ans. Select Ename, Salary * 3 from Emp;
d. Display the Employee id, Name and salary of all the employees after decrementing it as
half the amount of present salary.
Ans. Select Emp_id, Ename, Salary/2 from Emp;

Page 4 of 12
CLASS 10 ICT [402] QUERIES

e. Display the Employee id and Name of all the employees.

Ans. Select Emp_id, Ename from Emp;
Q15. Write the queries for the following table : Student

Admno Name Class House

1001 Sonam 9 Blue

1002 Ravi 10 Yellow

1003 Poonam 10 Green

a. Display the entire table

Ans. Select * from Student
b. Display the list of students whose house color is blue.
Ans. Select * from Student where House = “Blue”
c. Display the admission number of students whose house color is green.
Ans. Select Admno from Student where House = “Green”
d. To view records in ascending order of Admission Number.
Ans. Select * from Student order by Admno ;
e. Display the records of Class 10 Students.
Ans. Select * from students where Class = 10;
f. Display the class of ‘Ravi’
Ans. Select Class from Student where Name = ‘Ravi’
g. Insert the given record : 1004, “Aman”, 11, “Blue”
Ans. Insert into Student values( 1004, “Aman”, 11, “Blue”)
Q16. Which command is used for the following task in database?
1. To insert a new record
2. To modify the existing data.
3. To delete a record
4. To display record
Ans.
1. Insert

Page 5 of 12
CLASS 10 ICT [402] QUERIES

2. Update
3. Delete
4. Select
Q17. Write the queries for the following table : Item

Itemno Iname Price Qty

12 Pen 10 17

13 Eraser 5 15

14 Notebook 15 20

a. Write a query to insert a new record of following details

15, “Pencil”, 20, 10
Ans. Insert into Item values(15, “Pencil”, 20, 10)
b. Write a query to display detail of items whose quantity is more than 10.
Ans. Select * from Item where Qty > 10
c. Write a query to change the quantity of Item number 13 to 25.
Ans. Update Item set Qty = 25 where Itemno = 13
d. Display the total amount of each item. The amount must be calculated as the price
multiplied by quantity for each item
Ans. Select Price * Qty from Item.
e. Display the name of item whose price is 10.
Ans. Select Iname from Item where price = 10
f. Display all the records in descending order of price.
Ans. Select * from Item order by Price desc.
g. Identify the Primary key from table Item.
Ans. Itemno
h. Write the suitable data type of field “Iname”.
Ans. Char or Varchar
i. Write a query to increase the price of all items by Rs2.
Ans. Update Item set Price = Price + 2;
Page 6 of 12
CLASS 10 ICT [402] QUERIES

j. Write a query to decrease the price of all items by Rs2 whose price is less than 20.
Ans. Update Item set Price = Price – 2 where Price < 20;
Q18. By default, data is arranged in _________ order using ORDER BY clause.
Ans. Ascending Order
Q19. Which clause is used for the following:
a. To display specific record.
b. To display records in a particular order.
Ans.
1. Where Clause
2. Order by Clause
Q20. Consider the following table: STUDENT [C.B.S.E. – 2021]

ADMNO NAME GRADE DOB MARKS HOUSE GENDER

1001 RUPAL 9 10/04/2006 76 GREEN M

1002 RASHMI 9 08/12/2005 87 RED F

1003 ARNAV 10 25/05/2004 81 GREEN M

1004 SUMONA 9 23/08/2005 68 RED F

1005 ARUN 9 16/07/2005 72 GREEN M

1006 TIA 9 22/09/2005 91 BLUE F

1007 ROSHAN 10 26/08/2004 89 BLUE M

a) To Display the details of all students of Green House.

Ans. Select * from STUDENT where HOUSE = “GREEN”;
b) To increase the marks by 5 whose ADMNO is 1005.
Ans. Update STUDENT set MARKS = MARKS + 5 where ADMNO = 1005;
c) To display the details of all students whose MARKS are less than 80.
Ans. Select * from STUDENT where MARKS < 80;
d) Display the list of all students in descending order of MARKS.
Ans. Select * from STUDENT order by MARKS desc;

Page 7 of 12
CLASS 10 ICT [402] QUERIES

Q21. Identify any two Column name/Attribute and their data types from a given table:
PLAYER. [C.B.S.E.]

PID PNAME RUNS GENDER DOB

P101 SACHIN 13000 M 10/04/2001

P102 KAPIL 7000 M 12/02/1998

P103 SAURABH 12000 M 13/04/2001

P104 VIRAT 12500 M 17/03/2005

Ans. Column Name and it’s data types are:

Column Name Data type

PID Char

PNAME VARCHAR

Q22. Consider the following table: EMPLOYEE [C.B.S.E.]

EMPID NAME SALARY DOJ COMM DEPT GENDER

1001 ROHAN 7000 10/04/2006 300 SALES M

1002 RISHU 12000 08/12/2005 100 FINANCE F

1003 DEVANSH 9000 25/05/2004 500 SALES M

1004 SUMAN 10000 23/08/2005 300 MARKETING F

1005 ARYAN 11000 16/07/2005 500 SALES M

1006 TAMANNA 15000 22/09/2005 200 FINANCE F

1007 ROHIT 8000 26/08/2004 900 SALES M

Page 8 of 12
CLASS 10 ICT [402] QUERIES

Write SQL Commands :

a) To display the details of all employees of SALES Department.
Ans. Select * from EMPLOYEE where DEPT = “SALES”;
b) To increase the SALARY by 1000 whose EMPID is 1007.
Ans. Update EMPLOYEE set SALARY = SALARY + 1000 where EMPID = 1007;
c) To display the details of all employees whose SALARY is more than 10000.
Ans. Select * from EMPLOYEE where SALARY > 10000;
d) To display the list of all employees in descending order of SALARY.
Ans. Select * from EMPLOYEE order by SALARY desc;
Q23. Consider the following table: Sales [Sample Paper-2022]

Sale_Id Prod_Name Price Discount

1101 Laptop 65000 2500

1103 Pen Tab 29500 1000

1105 Desktop 50000 1550

1106 Printer 12000 2000

1. How many fields and records are there in Sales table?

2. Write SQL commands for the following:

i. Display Sales ID and price of all products whose discount is more than 1000.
ii. Display the details alphabetically by product name.
iii. Display product name and sales price after deducting the discount from the price.
Note: Sales price can be calculated as (price-discount)
Ans. 1. There are 4 fields and 4 records in table: Book
Ans2 i.) Select Sale_Id, Price from Sales where Discount > 1000;
ii) Select * from Sales order by Prod_Name;
iii) Select Prod_Name, Price- Discount from Sales;

Page 9 of 12
CLASS 10 ICT [402] QUERIES

Q24. Consider the following table: Teachers

NUMBE AG DATEOFJOI SALAR GENDE

NAME SUBJECT
R E N Y R

COMPUTE
1 JUGAL 34 10/01/2019 12000 M
R

2 PRATIGYA 31 SCIENCE 24/03/2019 20000 F

3 SANDEEP 32 MATHS 12/12/2019 30000 M

4 SANGEETA 35 SCIENCE 01/07/2020 40000 F

5 SATTI 42 MATHS 05/09/2020 25000 M

6 SHYAM 50 SCIENCE 27/06/2021 30000 M

7 SHIV OM 44 COMPUTER 25/02/2021 21000 M

SHALAKH
8 33 MATHS 31/07/2020 20000 F
A

Write SQL commands:

a. To show all the information about IT teachers.
b. To list the details of all the teachers who are getting salary between 20000 to 35000.
c. To display the subject of all the teachers whose age is more than 40 years.
d. To display the list of names of all the teachers in alphabetical order.
Ans. a. Select * from Teachers where subject = “COMPUTER”;
b. Select * from teachers where Salary >= 20000 and Salary <= 35000;
c. Select Subject from Teachers where Age > 40;
d. Select * from Teachers order by Name; [given in marking scheme of CBSE]
OR
d. Select Name from Teachers order by Name;

Page 10 of 12
CLASS 10 ICT [402] QUERIES

Q25. Consider the following table “Datesheet” and write the queries for the following:
[CBSE Sample Paper – 2023]

Sub_Code Sub_Name DateofExam Days

E001 English 11-03-2021 Monday

H002 Hindi 15-03-2021 Friday

S003 Social Sci 18-03-2021 Monday

S004 Science 21-03-2021 Thursday

i. Write a SQL command to display the records in ascending order by date of exam.
ii. Write a query to display the above date sheet.
iii. Write a query to display the subject name and date of the exam held on ‘Monday’
iv. Write a SQL command to display the date of exam and Sub_Name of Science subject.
Ans. a. Select * from Datesheet order by DateofExam;
b. Select * from Datesheet;
c. Select Sub_Name, DateofExam from Datesheet where Days= ‘Monday’;
d. Select DateofExam, Sub_Name from Datesheet where Sub_Name = ‘Science’;

Page 11 of 12
CLASS 10 ICT [402] QUERIES

Q26. Consider the following table “School” and write the queries for the following:

Roll_no Student_Name DateofBirth Class

R12 Aman 11-03-2012 10

R43 Sumit 15-03-2015 9

R23 Dhriti 18-03-2010 10

R1 Parth 21-03-2010 9

1. Write a query to display all records in alphabetical order of Name.

2. Write a query to change the class of “Dhriti” from 10 to 7.
3. Write a query to display the above table.
4. Write a query to display Roll number and Name of Class 10 students.
5. Display detail of all students of class 9.
6. Insert a new record with value: ‘R24’, ‘Kamal’, ’15-05-2010′, 8
7. Display detail of all students whose class is greater than 9.
Ans. 1. Select * from School order by Student_Name;
2. Update School set Class = 7 where Student_Name = ‘Dhriti’;
3. Select * from School;
4. Select Roll_no, Student_Name from School where Class = 10
5. Select * from School where Class = 9;
6. Insert into School values(‘R24’, ‘Kamal’, ’15-05-2010′, 8);
7. Select * from School where Class > 9;

Page 12 of 12