UNIT - 3 Chapter 11 - Queries in Base PDF

INFORMATION TECHNOLOGY – 402 CLASS X UNIT – 3 DATABASE MANAGEMENT SYSTEM USING LIBRE OFFICE BASE NOTES Chapter 11 - Queries in Base Introduction: A databa...

INFORMATION TECHNOLOGY – 402 CLASS X UNIT – 3 DATABASE MANAGEMENT SYSTEM USING LIBRE OFFICE BASE NOTES Chapter 11 - Queries in Base Introduction: A database is used to store data in an organized manner so that it can be retrieved easily and accurately. To search for certain records and retrieve data, we have to give correct specifications/conditions to DBMS. Such specifications are given to the database in the form of queries. What is a query? A query is a sort of question asked to the database. A query is used to retrieve and display data from one or more tables in a database. A search criteria is given to the DBMS to view the desired information. The result of the query is displayed in tabular form with fields and records. LibreOffice Base allows us to create a query and even save it as an object in a database. A query can be run multiple times. Creating a Query: A query can be created in three ways. (i) Using a Wizard (ii) In Design View (iii) In SQL view 1|Pa ge INFORMATION TECHNOLOGY – 402 CLASS X Creating a Query Using a Wizard: (i) Click on Use Wizard to Create Query (ii) Select Fields for your query from the tables in your database (iii) Specify the sorting order (iv) Give alias name (v) Give Name for the Query – By default, the name of the query is Query_Events. (vi) By default, Display Query option will be selected. Click and select the Modify Query radio button if the query must be edited in the Design view. What is the use of Alias? (i) Alias is the column header name which will be displayed when we run the query. (ii) By default, the field names will be displayed as column headers. (iii) Mostly the field names are not user friendly, so a readable alias name is chosen to be displayed in the query output. (iv) For example,an Employee table might have E_No but writing it’s Alias as Employee_Number is more understandable. Creating a Query Using Design view: (i) Select create query in design view (ii) Add tables and Select Fields for your query. (iii) Give field name, alias name and table name with any criteria as needed (iv) Fields can also be sorted as needed. (v) Run Query (F5). Working with Numerical data: We can display numerical data using certain mathematical functions to find the count, sum, minimum, maximum, average, etc of data values. 1. Click Create Query in Design View 2. Open the Query Design Window 2|Pa ge INFORMATION TECHNOLOGY – 402 CLASS X 3. Select Group option from the drop-down list in the field. This option forms the groups for each of the data values in the field. 4. Under the column which has numerical value, select the respective function from the drop-down list. 5. Run Query (F5). Answers to questions at the back of the chapter Chapter 11 – Queries in Base A. Multiple choice questions 1. Which of the following is referred to asking questions from the database? (a) Report (b) Table (c) Query (d) Database 2. Which of the following are the ways to design a query? (a) Wizard (b) Design View (c) SQL (d) All of the above 3. Which is a flexible way to create a query? (a) Wizard (b) Design View (c) Both (a) and (b) (d) Neither (a) nor (b) 4. Into how many parts is the query design window divided? (a) One (b) Two (c) Three (d) Four 5. Which of the following is NOT true about queries? (a) It can be created using multiple tables (b) Multiple queries can be created in a database (c) A query can run multiple times (d) A query once created cannot be edited 6. Which of the following is the shortcut key to run the query? (a) F3 (b) F4 (c) F5 (d) F6 7. Which of the following functions can be performed on numerical data while designing a query? (a) Sum (b) Minimum (c) Maximum (d) All of the above 3|Pa ge INFORMATION TECHNOLOGY – 402 CLASS X 8. In a Query Design wizard, which of the following buttons is clicked to move a field from ‘Available fields’ list box to ‘Fields in the query‘list box? (a) > (b) < 9 (c) ∨ (d) ∧ 9. Which of the following relational operators can be applied to set the criterion while designing a query in LibreOffice Base? (a) > (b) = (c) != (d) All of these 10. Which of the following dialog box is present when the Query Design window is opened for the first time to design a query? (a) Add Table (b) Add Query (c) Add Table or Query (d) None of the above 11. Which of the following step is not performed if there is no numerical data to be worked upon in a query? (a) Selection of fields (b) Giving Aliases (c) Summarizing (d) Selection of tables B. State whether the following statements are True or False 1. You can run a query only once. FALSE 2. A query cannot be created from multiple tables. FALSE 3. The shortcut key to run a query is F5.TRUE 4. LibreOffice Base provides us with two ways to create a query. FALSE 5. A query with numerical data cannot be saved. FALSE 6. By default the query result is not sorted. TRUE 7. A query can be used to display the average value of a numerical field. TRUE 8. While designing a query, the criterion can be set on only one field. FALSE 9. Alias is an alternative name for a field in a query. TRUE 10. In query Design window, the visible check box is selected by default. TRUE 11. A query once created using a wizard can only be edited in the Design view. TRUE 4|Pa ge INFORMATION TECHNOLOGY – 402 CLASS X C. Fill in the blanks 1. A QUERY is a sort of question asked from a database. 2. The result of the query is displayed in TABLE form with field names in columns 3. A query can be created in THREE ways. 4. The Query Design window is divided into TWO sections. 5. The shortcut key to run the query is F5. 6. The conditions to filter the records are set in the Criterion row. 7. When a table is selected in a Query wizard, the corresponding fields are displayed in the AVAILABLE FIELDS list box. 8. The result of the query can be displayed in ASCENDING or DESCENDING order of any particular field of the table. 9. At the most Three search conditions can be given in the query wizard. 10. The last step of the Query wizard displays the entire OVERVIEW of the query. 11. The Design view is a more flexible method to create a query. 12. To edit any query, right click on the Query icon of the query that has to be edited. 13. In the FIELD row of the Query Design grid, we can type the column heading that will be displayed instead of field name when we run the query. D. Answer the given questions 1. Define a query? What is the need of creating a query in a database? ANS: 1. A query is used to retrieve and display data from one or more tables in a database. 2. A specific search criteria is given to the DBMS to view the desired information. 3. Using a query, we can retrieve and display data from one or more tables in a database. The result is given in the form of a table. 5|Pa ge INFORMATION TECHNOLOGY – 402 CLASS X Need for Creating a query: As the number of records increase, finding desired information is difficult. By using queries, data retrieval can be done without going through each record and result can be seen in desired form 2. Rearrange the steps given below so as to create a query using a wizard. Give Alias (5) Select the fields (2) Set the criterion (4) Set the sorting order (3) Give table name (1) 3. What all information is seen in the overview (last step) of the Query wizard? Ans: Name of the query – Default name is Query_Events. Type a new name if desired. The action to be performed after the wizard finishes – By default Display Query option is selected. Click and select the Modify Query radio button Complete detail of the query – This contains a summary about the query that has been created. 9. What is the use of Alias row in the Design grid of the Query Design window? Ans: (i) Alias is the column header name which will be displayed when we run the query. (ii) It is used to display meaningful names in the output. More user friendly. (iii) Alias are alternative names for field names. 6|Pa ge INFORMATION TECHNOLOGY – 402 CLASS X 10. Name any four mathematical functions that can be applied to numerical data in a query. Ans: Average, minimum, maximum, sum, count 11. Name the three ways of creating a query in LibreOffice Base? Ans: A query can be created in three ways. (i) Using a Wizard (ii) In Design View (iii) In SQL view 7|Pa ge