Session 4 : Retrieve Data using Query
In order to describe the data structure and to modify the data in the database, queries are used
as instructions. A query enables the joining and filtering of data from various tables.
Database Languages having two type:
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
DDL Statements:
● Create.
● Alternate.
● Drop.
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.
Database Query
Query is a computer languages. In order to describe the data structure and to modify the data
in the database, queries are used as instructions. Query can extract particular data from a
database. We can filter and join data from various tables with the help of a query. By using the
criteria you supply query will filter the data.
Select Statement
A select query is a language in a database that displays data in Datasheet view. Data from
tables is displayed by a query rather than being stored by it. A query may display data from one
or more tables, from other queries, or from both of these sources simultaneously.
The SELECT statement has many optional clauses:
● WHERE specifies the condition over rows to retrieve.
● ORDER BY specifies an order in which to return the rows.
Syntax of Select Statement is –
SELECT * FROM <TABLENAME>;
Query related to Simple Select Statement –
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
Database Management System Class 10 Notes
Question – Write a Query to display all record from the table;
Select * from product;
Output –
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 250 25
52 Soap Box 120 100
Database Management System Class 10 Notes
Question – Write a Query to display product name from the table;
Select Product_Name from product;
Output –
Product_Name
Soap
Powder
Shampoo
Soap Box
Database Management System Class 10 Notes
Question – Write a Query to display Product_Name and Price from the table;
Select Product_Name, Price from product;
Output –
Product_Name Price
Soap 40
Powder 80
Shampoo 300
Soap Box 120
Database Management System Class 10 Notes
Query related to Select Statement with Mathematical function –
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
Database Management System Class 10 Notes
Question – Write a Query to find the total no of quantity available in table;
Select sum(quantity) from product;
Output – 235
Question – Display the total amount of each item. The amount must be calculated as
the price multiplied by quantity for each item.
Select Product_No, Product_Name, Price * Quantity from product;
Output –
Product_No Product_Name Price*Quantity
25 Soap 3200
31 Powder 2400
45 Shampoo 6250
52 Soap Box 12000
Database Management System Class 10 Notes
Question – Write a query to find the average price of the total product;
Select avg(Quantity) from product;
Output – 58.75
Query related to Select Statement with Where clause
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 – Write a Query to display the product whose price is less than 90
Select * from product where price < 90;
Output –
Product_No Product_Nam Price Quantity
25 Soap 40 80
31 Powder 80 30
Database Management System Class 10 Notes
Question – Write a Query to find the total amount of the Shampoo product;
Select Price*Quantity from product where Product_Name = ‘Shampoo’;
Output – 6250
Or
Select Product_No, Product_Name, Price*Quantity from product where Product_Name =
'Shampoo';
Output –
Product_No Product_Nam Price*Quantity
45 Shampoo 6250
Question – Write a Query to display the data whose quantity is equal to 80.
Select * from product where quantity = 80;
Output –
Product_No Product_Nam Price Quantity
25 Soap 40 80
Database Management System Class 10 Notes
Question – Write a Query to display a list of Products whose Price between 40 to 120.
Select * from product where Price >= 40 and Price <= 120;
Output –
Product_No Product_Nam Price Quantity
25 Soap 40 80
31 Powder 80 30
52 Soap Box 120 100
Query related to Select Statement with Order by
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 – Write a Query to display the list of Product_Name in alphabetical order.
Select * from product order by Product_Name ASC;
Or
Select * from product order by Product_Name;
Output –
Product_No Product_Nam Price Quantity
31 Powder 80 30
45 Shampoo 250 25
25 Soap 40 80
52 Soap Box 120 100
Database Management System Class 10 Notes
Question – Write a Query to display the list of Price in ascending order.
Select * from product order by Price ASC;
Or
Select * from product order by Price;
Output –
Product_No Product_Nam Price Quantity
25 Soap 40 80
31 Powder 80 30
52 Soap Box 120 100
45 Shampoo 250 25
Question – Write a Query to display the list of Price in descending order.
Select * from product order by Price DESC;
Output –
Product_No Product_Nam Price Quantity
45 Shampoo 250 25
52 Soap Box 120 100
31 Powder 80 30
25 Soap 40 80
UPDATE statement
To edit or update already-existing records in a table, use the UPDATE statement. Using the
WHERE clause, you can either define a specific subset of entries to edit or use it to update
everything at once.
Syntax of Update Statement –
UPDATE <table name> SET = value [, column_name = value ...] [WHERE ];
Table Name – product
duct_No Product_Name Price Quantity
Soap 40 80
Powder 80 30
Shampoo 250 25
Soap Box 120 100
Database Management System Class 10 Notes
Question – Write a Query to update the price of Shampoo in the product table.
Update product Set Price = 300 where Price = 250;
Output –
duct_No Product_Name Price Quantity
Soap 40 80
Powder 80 30
Shampoo 300 25
Soap Box 120 100
Question – Write a Query to update the Quantity of Powder in the product table.
Update product Set Quantity = 50 where Product_Name = ‘Powder’;
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 50
45 Shampoo 250 25
52 Soap Box 120 100
Output –
Database Management System Class 10 Notes
Create Table
To create a new table in the database you can use Create Table Command.
Syntax of Create Table –
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype);
Question – Write a Query to create the following table in the database;
Table Name – product
Field DataType
Product_No Integer
Product_Name Varchar(20)
Price Integer
Quantity Integer
Create table product ( Product_No Int, Product_Name Varchar(20), Price Int, Quantity
Int);
Output –
Product_No Product_Name Price Quantity
Database Management System Class 10 Notes
Insert Table
Insert statement is primarily used to add a single or more rows to the target table.
Syntax of Insert Table –
INSERT INTO table_name (column1, column2, column3, ...) VALUES
(value1, value2, value3, ...);
Or
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 250 25
52 Soap Box 120 100
Table Name – product
Question – Write a Query to add a new row with the following details
(72, “Hair Conditioner‟, 350, 60)
Insert into product (Product_No, Product_Name, Price, Quantity) values(72, “Hair
Conditioner‟, 350, 60);
Or
Insert into product values(72, “Hair Conditioner‟, 350, 60);
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 300 25
52 Soap Box 120 100
72 Hair Conditioner 350 60
Output –
Database Management System Class 10 Notes
Session 5 : Create Forms and Reports using Wizard
Forms
The Form allows you greater control over your outcomes. The form allows you to customize
various parts of the form’s design and then generates a form based on your instructions.
Steps To Create Form Using Wizard
● Step 1 : Click Use Wizard to Create Form
● Step 2 : Select the selective Fields using Arrow button
● Step 3 : Click Next
● Step 4 : Add Subform if you need to insert
● Step 5 : Click Next
● Step 6 : Arrange selected field in a form
● Step 7 : Click Next
● Step 8 : Click Finish
Reports
A report helps in the presentation of data in a summarized format. It is utilized to create a clear
format for the entire work product. In the database, you can build reports.
Steps To Create Report Using Wizard
● Step 1 : Click on Use Wizard to Create Report
● Step 2 : Select the selective Fields using Arrow button
● Step 3 : Click Next
● Step 4 : Redefine the label of the fields in the reports or else you can set the default
name
● Step 5 : You can define grouping for the fields of the table
● Step 6 : Click on Next
● Step 7 : Click Finish
https://cbseskilleducation.com/database-management-system-class-10-mcq/
