Class 10 IT Retrieve Data using Query in Libre Office Base Notes

 , , ,

Retrieve Data using Query

In order to describe the data structure and to modify the data in the database (Open office and Libre Office), 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: Using this statement, a database or set of tables can be created.
  • Alter: 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.

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 which 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
29 Mouse 140 60
51 Keyboard 700 50
15 Monitor 3500 15
42 CPU 12000 100

Question : Write a Query to display all record from the table.


Solution : Select * from product;

Output:

Product_No Product_Name Price Quantity
29 Mouse 140 60
51 Keyboard 700 50
15 Monitor 3500 15
42 CPU 12000 100

Question : Write a Query to display product name from the table.

Solution : Select Product_Name from product;

Output:

Product_Name
Soap
Powder
Shampoo
Soap Box

Question : Write a Query to display Product_Name and Price from the table.

Solution : Select Product_Name, Price from product;

Output:

Product_NamePrice
Soap40
Powder80
Shampoo300
Soap Box120

Query related to Select Statement with Mathematical function:


Table Name : Product

Product_No Product_Name Price Quantity
29 Mouse 140 60
51 Keyboard 700 50
15 Monitor 3500 15
42 CPU 12000 100

Question : Write a Query to find the total no of quantity available in table.

Solution : Select sum(quantity) from product;

Output:

sum(quantity)
225

Question : Display the total amount of each item. The amount must be calculated as the price multiplied by quantity for each item.

Solution : Select Product_No, Product_Name, Price * Quantity from product;

Output:

Product_NoProduct_NamePrice*Quantity
29Mouse8400
51Keyboard35000
15Monitor52500
42CPU120000

Question : Write a query to find the average price of the total product;

Solution : Select avg(price) from product;

Output:

avg(price)
4085

Query related to Select Statement with Where clause.


Table Name : Product

Product_No Product_Name Price Quantity
29 Mouse 140 60
51 Keyboard 700 50
15 Monitor 3500 15
42 CPU 12000 100

Question : Write a Query to display the product whose price is less than 90.

Solution : Select * from product where price > 750;

Output:

Product_NoProduct_NamPriceQuantity
15 Monitor 3500 15
42 CPU 12000 100

Question : Write a Query to find the total amount of the Mouse product;

Solution : Select Price*Quantity from product where Product_Name = ‘Mouse’;

Output:

Price*Quantity
8400

Question : Write a Query to display the data whose quantity is equal to 50.

Solution : Select * from product where quantity = 50;

Output:

Product_NoProduct_NamPriceQuantity
51 Keyboard 700 50

Question : Write a Query to display a list of Products whose Price between 150 to 850.

Solution : Select * from product where Price >= 150 and Price <= 850;

Output:

Product_NoProduct_NamPriceQuantity
51 Keyboard 700 50

Query related to Select Statement with Order by:

Table Name : Product

Product_No Product_Name Price Quantity
29 Mouse 140 60
51 Keyboard 700 50
15 Monitor 3500 15
42 CPU 12000 100

Question : Write a Query to display the list of Product_Name in alphabetical order/ascending order.

Solution : Select * from product order by Product_Name ASC;

Or

Solution : Select * from product order by Product_Name;

Output:

Product_NoProduct_NamPriceQuantity
42 CPU 12000 100
51 Keyboard 700 50
15 Monitor 3500 15
29 Mouse 140 60

Note : For Descending order use Desc. Example: Select * from product order by Product_Name Desc; This query will show Product_Name in Descending order

Question – Write a Query to display the list of Price in descending order.
Select * from product order by Price DESC;

Output – 

Product_NoProduct_NamPriceQuantity
45Shampoo25025
52Soap Box120100
31Powder8030
25Soap4080

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 

Product_NoProduct_NamePriceQuantity
25Soap4080
31Powder8030
45Shampoo25025
52Soap Box120100
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 – 

Product_NoProduct_NamePriceQuantity
25Soap4080
31Powder8030
45Shampoo30025
52Soap Box120100

Question – Write a Query to update the Quantity of Powder in the product table.

Update product Set Quantity = 50 where Product_Name = ‘Powder’;

Output – 

Product_NoProduct_NamePriceQuantity
25Soap4080
31Powder8050
45Shampoo25025
52Soap Box120100
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

FieldDataType
Product_NoInteger
Product_NameVarchar(20)
PriceInteger
QuantityInteger
Database Management System Class 10 Notes
Create table product ( Product_No Int, Product_Name Varchar(20), Price Int, Quantity Int);

Output – 

Product_NoProduct_NamePriceQuantity
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, ...);

Table Name – product 

Product_NoProduct_NamePriceQuantity
25Soap4080
31Powder8030
45Shampoo25025
52Soap Box120100

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);

Output –  

Product_NoProduct_NamePriceQuantity
25Soap4080
31Powder8030
45Shampoo30025
52Soap Box120100
72Hair Conditioner35060