Database Management Systems – Perform Operations on tables

[1] Mrinal wants to insert records into the OO base tables. Suggest the way to insert records in the table.
Ans.INSERT

[2] Raj is using OO base where he is typing contents directly into the table and modifying the data. In which view he is
working?
Ans.: Datasheet View

[3] What kind of operations are supported by the datasheet view?

Ans.: Datasheet view supports the following operations:
1. Insert data
2. Update existing data
3. Delete data
[4] Samyak wants to delete the entire row. Suggest how to delete a record from OO base.
Ans.: To delete entire row follow these steps:
1. Select the entire row.
2. Right click on selected row.
3. Choose Delete option.
[5] Write any two DML commands.
Ans.: Insert, Update and Delete

[6] Write SQL command to insert the following records in the table – customer
“C1008″,”Shiv”,4500,”2022/03/22″
Ans.: insert into customer values (‘C1008′,’Shiv’,4500,’2022/03/22′);

[7] Write SQL command to modify the records from the customer table
Change Amount to 5500 for the orders given on 22/03/2022.
Ans.: update customer set amount=5500 where orderdate=’2022/03/22′;

[8] Write SQL command to delete the records given after 22/03/2022.
Ans.: delete from customer where orderdate>=”2022/03/22″;

[9] Satyam wants to set the value for the serial number which is going to be incremented itself when a new row will be
inserted. Which option he should use for this purpose? Suggest the name of the properties.
Ans.: AutoValue is used to auto incremented value in the new row in OO base.

[10] The length field property set to __________ by default.

Ans.: 10

[11] Nirmal wants to insert a fixed value in the column stream as Science. Which field property will be useful for him?
Ans.: Default

[12] Which field properties won’t allow a cell to be blank or empty?

Ans.: Entry Required

[13] Define sorting.

Ans.: Sorting refers to rearrange data in ascending or descending order by a particular field.

[14] How to sort data of emp table by ename?

Ans.: To sort the data of emp table by ename, follow these steps:
1. Select the column: ename
2. Click on sort button.

[15] Define referential integrity.

Ans.: Referential integrity is used to maintain accuracy and consistency of data in a relationship.

[16] In OO Base data can be linked between two more tables. To link data what is required?
Ans.: The data can be linked between two or more tables using primary key and foreign key.

[17] Define relationship.

Ans.: Relationship refers to an association or connection between two or more tables.

[18] Write step to create a relationship between two tables in OO Base.

Ans.: To create relationship between two tables Click on Tools – Relationship option.

[19] Kajal wants to know the three types of relationships. Suggest their name to her.
Ans.: The 3 types of relationships are:
1. One to One
2. One to Many or Many to One
3. Many to Many

[20] Satvik wants to create a relationship. He knows this task can be done from two menus. Suggest the menus to him.
Ans. Ans.: The two menus are:
1. Insert
2. Tools
[21] How to remove the relationship?
Ans.: To delete relationship follow the given steps:
1. Select the relationship.
2. Right click and choose delete option.

2 marks questions Unit 3 Database Management Systems – Perform Operations on tables

[1] How to insert data in OO Base tables? Explain in detail with steps.
Ans.: To insert data in OO Base tables follow these steps:
1. Select the table from the database objects window.
2. Double click on the table in which you want to insert data.
3. The table will open in datasheet view.
4. Now type the required data to the end of the table.

[2] What do you mean by field properties? How to set the field properties?
Ans.: A field property is a particular characteristics of a field can be applied to the field to change the structure of a table.
To set field properties follow these steps:
1. Select the table
2. Right Click
3. Select the option edit
4. The Table design window appear where the different field properties are available.

[3] Differentiate between Auto Value and Default Value.

Ans.: AutoValue and Default Value both are field properties. Autovalue set to yes if field needs to get the auto
incremented value, otherwise no. Default value set to a fixed value when user don’t provide any value while entering the
values the fixed value will be taken into the field.

[4] Write a few advantages of referential integrity.

Ans.: The advantages of referential integrity are as following:
1. It saves the time as there is no need to enter the same data in separate table.
2. It reduces data-entry errors.
3. Summarize data from related tables.
4. Delete and modify the matching records from a primary key table deleted or modified.

[5] Explain how to create a relationship using the insert menu from the relation design window.
Ans.: To create a relationship using the insert menu from the relation design window follow these steps:
1. Click on Tools – Relationship
2. Now click on Insert – new Relationship
3. Select the appropriate options for the relationship from the window.
4. Click on OK.

[6] Enlist the options available under update and delete options in the relations window.
Ans.: The options under update and delete options are as follows:
1. No Action
2. Update Cascade
3. Set null
4. Set Default

[7] Explain all types of relationships supported by OO base.

Ans.: OO base support 3 types of relationships:
1. One to One
2. One to Many or Many to One
3. Many to Many
1. One to One: In this relationship both tables must have primary key columns. Example: In the given tables emp and
dept, empno and deptno is primary key in dept table. So in relationship these two fields are used for one to one
relationship.

2. One to many or Many to one: In this kind of relationship one of the table must have primary key column. It means that
one column of primary key table is associated with all the columns of associated table. As above example, emp and dept
tables, empno is primary key for the emp table.

3. Many to many relationship: In this kind of relationship no table has primary key column. It means that all the columns of
primary key table are associated with all the columns of associated table.
If you are looking for more questions follow this link:

Table operations in OO Base

Referential Integrity

1 mark questions retrieve data using the query

[1] Define query.
Ans.: Query is used to collect specific information from the pool of data. A query helps to join information from different
tables and filter that information.

[2] Define filtering.

Ans.: Filtering means that the query uses criteria provided to hide some data and present only what user want to retrieve.

[3] What are the ways to perform queries supported by OO base?

Ans.: There are three ways to perform queries:
1. Query using wizard
2. Use wizard to create query
3. SQL command

[4] Write the full form: SQL

Ans.: SQL stands for Structured Query Language

[5] Ronit is a learning Database. Where he notices people pronounced SQL with a different word but he doesn’t
remember what it was? Suggest your answer for the same.
Ans.: Sequel

[6] Trishala is learning SQL Queries. She wants to know what a query will do in SQL, Support her by writing the answer.
Ans.: Queries are used to:
1. Define structure of database
2. Manipulate data
3. Retrieve Data
4. Control transactions
[7] Which command is mostly used to retrieve data?
Ans.: Select

[8] What do you mean by clause in SQL command?

Ans.: The various parts of SQL queries are known as clauses.

[9] What are the clauses used with the SELECT command?
Ans.: With select following clauses can be used:
1. From
2. Where
3. Order by
4. Group by

[10] Write SQL command to retrieve all data from a table?

Ans.: select * from student; (Student is tablename)

[11] Hiteshi wants to execute the query from SQL view. Which key hee need to press in order to get the result?
Ans.: F5

[12] Ishika is writing a query, where she needs to display only practical marks from total marks and theory marks from
exam_results table. Write a query to get practical marks from total marks and theory marks. The column names are total
for total marks and theory for theory.
Ans.: Ishika will write this command:
select total – theory “Practical” from exam_results

[13] Write the use of where clause with select query.

Ans.: The where clause is used to filter the records containing the same type of values.
[14] Write SQL query to display students of commerce stream from exam_results table.
Ans.: select * from exam_results where stream =’commerce’

[15] Write SQL query to get the result in ascending order of total marks scored by students.
Ans.: select * from exam_results order by total asc;

[16] Priya wants to sort the records of students in the descending order of marks scored by students. She knows the
command but forgets only the keyword which is used to get the result in descending order with an order by clause. Write
that keyword.
Ans.: desc

[17] Manisha is working as a team leader. She wants to update the salary of her department employees. She wants to
open SQL command window for the update command. She forgets how to open SQL command window to update the
records. Help her by suggesting the answer.
Ans.: Tools – SQL

[18] Write a command to update the centre city of students from Baroda to Ahmedabad from the table candidates.
Ans.: update candidates set centre=’Ahmedabad’ where centre=’Baroda’

[19] Write a query to display marks between 91 to 100 from exam_results.

Ans.: select * from exam_results where marks>=91 and marks<=100
OR
select * from exam_result where marks between 91 and 100

[20] The SQL Command window has which button to run the query?
”Show_Answer”

2 marks questions retrieving data using query unit 3 DBMS class 10

[1] Write steps to create a query using the wizard.
Ans.: To create a query using wizard follow these steps:
1. Select queries option from the main database window.
2. Select and click on Use Wizard to Create Query…
3. Now select fields and table for the query.
4. Select sorting order, search conditions, aliases and overview respectively and click on Finish button.
[2] Nilesh is working in Krishna Automobiles. His manager told him to perform a query to return records for his
Ahmedabad branch. He is going to use the option to create queries using design. Write the steps.

Ans.: Follow these steps to create a query using design view:

1. Click on queries button from the main database window.
2. Choose the option Create query in Design view.
3. Now add desired tables for the queries.
4. Now select fields and criteria then save the query.

[3] Explain the parts of the query design window in SQL view.
Ans.: The query design window in SQL view has following parts:
1. Output Window: It will display the output after executing the query.
2. SQL Window: This part accepts the SQL commands.

[4] Enlist some of the operators used in SQL command in where clause.
Ans.: Where clause in SQL requires various operators. These operators are:
1. Relational Operators: (=,!=,<,>,<=,>=)
2. Logical Operators: (and, or, not)
3. Pattern matching: like
4. Null Value handling: with is and is not
5. Range Searching Operator: Between…and
6. Membership operator: in, not in

[5] Explain the update command with syntax and example.

Ans.: Update command is used to modify the records in a table. It is one of the DML commands. The syntax for update
command is as following:
update set = value, [columnname=value,…] where ;
Example: update emp set comm = sal * 0.1 where comm is null;

4 marks questions retrieving data using queries Unit 3 Database Management System
[1] Observe the following table (examresult) structure and determine the appropriate data type and constraints for the
table as per the given specification then write a query to create a table:

Field name Data Type Description Constraint Description

rollno Store 4 digit unique numbers Unique and Not empty
sname Store name of 20 characters long Not empty
marks Store marks with 2 decimal places Default 0
grade Store grades with two characters
Ans.:
1. Rollno – int(4) – Primary Key
2. Sname – Varchar(20) – Not Null
3. Marks – Decimal(5,2) – default 0
4. Grade – Varchar(20)
Create table command:
create table examresult
(rollno int(4) primary key,
sname varchar(20) not null,
marks Decimal(5,2) default 0,
grade varchar(20));
[2] Aman is working in a multinational company. He wants to maintain records of employees in the database and create a
database to keep a track of all the records. Help him to write the following commands:
i) Create a table emp with the following structure:
Field Data Type Constraint
empno int(4) Primary Key
ename varchar(20) Not Null
designation varchar(20)
Salary decimal(7,2)
doj date
deptno int(2)
ii) Write SQL command to insert a record of your choice
iii) Write SQL command to add new column comm with int datatype.
Ans.:
i)
create table emp
(empno int(4) primary key,
ename varchar(20) not null,
designation varchar(20),
salary decimal(7,2),
doj date,
deptno int(2));
ii) insert into emp values
(1324, ‘Divyesh’, ‘President’, 25000, ‘2018/02/04’, 20);
iii) alter table emp add column comm int(5);
[3] Observe the following table student and write answers for the below-given queries:

rollno name stream percentage class

1201 Krish Commerce 85.79 12D
1202 Jay Science89.71 12B
1203 Nadiya Commerce 92.74 12C
1204 Archi Science72.84 12A
1205 Adnan Commerce 84.86 12D
1206 Mahek Science62.11 12B
i) Display the student’s details of the commerce stream.
ii) Display records of class 12D and 12B students
iii) Change the percentage of Mahek to 64.15.
iv) Delete records of those who secured less than 60%.

Ans.:
i) select * from student where stream=’commerce’;
ii) select * from student where class in(’12B’,’12D’)
or
select * from student where class =’12B’ or class=’12D’;
iii) update percentage=64.15 where name=’Mahek’;
iv) delete from student where percentage<60;

[4] Consider the following table – candidate and write queries given below:

Sno name fees subject marks status

1 Preet 350 PCM 68 pending
2 Vansh 450 PCB 52 pending
3 Dhwanil350 PCM 89 admitted
4 Kirsha 450 PCM 75 pending
5 Diya 350 PCB 72 admitted
i) Display the candidates for PCM subject
ii) Display names of candidates whose admission is pending in ascending order of name.
iii) Display the records of PCB candidates whose marks are less than 70.
iv) Display the name, fees and subject of candidates whose fees are more than 400.
Ans.:
i) select name from candidate where subject=’PCM’;
ii) select name from candidate where status=’pending’ oder by name;
iii) select * from candidate where subject =’PCB’ and marks < 70;
iv) select name,fees, subject from candidate where fees > 400;
[6] Consider the following table teacher and write the given queries below:

Teacher_ID Name Age Subject DOJ Salary Gender

101 Kamlesh Mali 55 Physics 1991/04/01 75000 M
102 Praveen Singh 48 Maths 1998/06/01 65000 M
103 Hemisha Patel 45 Gujarati 2000/02/07 55000 F
104 Alauddin Sayed 41 Computer Science 2008/04/02 52000 M
105 Dipti Agrawal 38 Maths 2012/04/01 48000 F
106 Krunali Prajapati 37 Computer Science 2016/04/01 44000 F
i) Display the information of Maths and Computer Science subject teachers
ii) Display the information of teachers who are getting salaries in the range of 45000 to 75000
iii) Display the information of teachers whose age are more than 45 years
iv) Display the name of teachers along with a report on how many years remain for their retirement

Ans.:
i) select * from teacher where subject = ‘Maths’ or Subject = ‘Computer Science’;
OR
select * from teacher where subject in( ‘Maths’, ‘Computer Science’);
ii) select * from teacher where salary between 45000 and 75000;
OR
select * from teacher where salary >=45000 and salary<=75000;
iii) select * from teacher where age>45;
iv) select name, 60-age “Year of retirement” from teacher;

[7] Observe the table store and write queries given below:

itemno itemname supplierqty rate city state

1001 Chocolate Cadbury India 150 40.00 Pune Maharashtra
1002 Bread Amul 500 30.00 Anand Gujarat
1003 Cookies Dark Fantasy 250 35.00 Haridwar Uttarakhand
1004 Biscuits Parle 700 25.00 Jaipur Rajasthan
1005 Namkeen Gopal 500 45.00 Rajkot Gujarat
1006 Sev Bikaji 600 32.00 Bikaner Rajasthan
1007 Chocolate Amul 500 42.00 Anand Gujarat
i) Display the records in the descending order of rate
ii) Display the itemname, supplier from Gujarat and Rajasthan.
iii) Delete a column city
iv) Display the information of items whose price is more than 30 and the supplier is Amul.
Ans.:
i) select * from store order by rate desc;
ii) select itemname, supplier from store where state in (‘Gujarat’,’Rajasthan’);
iii) alter table store drop column city;
iv) select * from store where rate > 30 and supplier = ‘Amul’;
[8] Observe the following table team and write queries given below:

Name Department jobid salary post

MaheshHouse Keeping H45 28000 Peon
Nitigna Admin A11 32000 Receptionist
Nikita Admin A02 38000 Secretary
Ankit HR HR01 50000 HR Head
Yogesh Management M003 42000 Manager
Vikas HR HR03 34000 HR Manager
Dinesh HR HR02 39000 HR HOD
i) Display the information whose name ends with sh.
ii) Identify the primary key and justify your answer.
iii) Display name, department, and salary of an HR department.
iv) Display the information with a 5% of increment in salary for admin department team members.

Ans.:
i) select * from team where name like ‘%sh’;
ii) The jobid field can be a primary key as it contains unique records. Jobid for each team member is unique.
iii) select name, department, salary from team where department =’HR’;
iv) select salary+(salary*0.5) from team where department=’Admin’;

1 marks questions – CREATE FORMS AND REPORTS USING WIZARD

[1] Ayushi is learning OO Base. She wants to provide an interface to users that allows them to view, enter and change
data directly in database tables. Suggest the database object fulfils her need.
Ans.: Forms

[2] Write one feature of a base form.

Ans. : base forms provide a systamatic way of storing information into database.

[3] Jayshree has opened OO Base window. She wants to create a new form. Help her to select the option to create a
form.
Ans.: Jayshree need to click on Forms option under Database section in OO base window.

[4] Vanshika is looking for ways to create a form provided by OO base. Write the same for her.
Ans.: There are two ways to creare a form in OO base.
1. Create form in design view
2. Using wizard to create a form

[5] The option – “Use wizard to create form” is available under which group?
Ans.: The option use wizard to create a form is available under Tasks group.

[6] In which step does the form wizard allow you to select the field to display the field on the form?
Ans.: The Field Selection step allows you to select the field to display the field on form.

[7] Users can select specific fields or all fields for a form in the OO base. Which buttons are present to do so?
Ans.: To select a specific fields for a form > button is present and to select all fields >> button is present.

[8] What is a subform?

Ans.: A subform is a special kind of form that allows to insert the contents of table or query into separate form.

[9] In which step you can place the fields or contents to the right place on a form in form wizard?
Ans.: Arrange Controls

[10] The steps that let users set up the data entry model. Mention the step of the database form wizard.
Ans.: Set data entry

[11] Nilesh wants to set background and text colours on a form through a wizard. Which step helps him fulfil his task?
Ans.: Apply Styles
[12] Write any two features of a report.
Ans.:
1. Report helps to display the data in a summarized manner.
2. It is used to generate overall work outcome in a clear form.
[13] Aniket wants to create a report in OO Base. He is not familiar with OO base. Help him to initiate the process.
Ans.: Aniket can click on Reports option under Database section in OO base window.

[14] There is only one way to create a report in OO Base. Which it is?
Ans.The only way to create a report in OO base is Use wizard to create report.

[15] What is the significance of labelling fields in report wizard?

Ans.: Labelling fields step allows to redefined the label of fields in the reports or set the default the name.

[16] Which step of the report wizard allows to filter and display the fields according to specific fields frequencies?
Ans.: Grouping

[17] Nitin wants to set the orientation of OO Base through the wizard. Which step will help him to fulfil his task?
Ans.: Choose Layout

[18] Which types of reports can be generated through the wizard in OO Base?
Ans.: There are 2 kinds of reports:
1. Static
2. Dynamic

2 Marks questions Create forms and reports using wizards

[1] What are the uses of forms?

Ans.: Forms are used to display the records. Some forms can be used for data entry model. Some forms used to modify
the content driectly from the table.

[2] What are the steps involved in creating a form using a wizard?
Ans.: To create a form using wizard follow these steps:
1. Field Section
2. Set up a sub form
3. Arrange Controls
4. Set Data Entry
5. Apply Styles
6. Set Name

[3] What are the options available under arrange controls?

Ans.: The arrange controls has following options:
1. Label Placement
2. Arrangement of Item on the main form as DataSheet, Columnar Labels Left, Columnar Labels top, In Blocks – Labels
Above
3. Arrangement of the subform

[4] Distinguish between forms and reports.

Ans.:
Forms provides an interface to users to view, add, modify in both table and queries.
Reports are presented forms used to present formatted summaries or lists of the data from one or more tables or queries.

[5] Manan is working on the form. He is going to create a form to add new records only. Enlist all the options available for
data entry in the form wizard. Write the option which helps Manan to fulfil his need.
Ans.: The form wizard in OO base provides following options for data entry:
1. The form is to be used for entering new data only. Existing data will not be displayed.
2. The form is to display data
a. Do not allow modification of existing data
b. Do now allow deletion of existing data
c. Do not allow addition of new data
Option 1 will help Manan to fulfil his need.

[6] What are the styles available for designing a form? Write any four styles.
Ans.: There are various styles available in form wizard for designing a form. Few of them are:
1. Beige
2. Violet
3. Bright Blue
4. Dark

[7] Priya wants to add a border on the form using the wizard. Help her by suggesting the border options available for the
form.
Ans.: There are three options for border in the form wizard:
a) No Border
b) 3D Look
c) Flat

[8] Shital has almost finished all the steps and she wants to continue with the form. What are the options available for her
after setting up the name of the form?
Ans.: There are two options after setting up the name of form in wizard:
a) Work with form
b) Modify the form

[9] How to add a new record through form?

Ans.: Follow these steps to add record through form:
1. Create a new form or Open an existing form
2. Find the button Add Record
3. Enter the desired details in the form
4. Save record.

[10] Ritesh wants to delete a record using the form. Help him to finish this task.
Ans.: Follow these steps to delete records using form:
1. Create a new form or Open an existing form
2. Navigate the form on which record needs to be deleted
3. Find the delete record button
4. Press the delete record

[11] How do modify the records using forms?

Ans.: Follow these steps to modify the record:
1. Create or open a form
2. Navigate the form on which records needs to be modify
3. Enter new details
4. Click on Save

[12] Kunjan wants to display data in a summarized manner from the queries and tables. Which database object will fulfil
his requirements. Define the object which is used to do this task.
Ans.: Kunjan can use reports to display data in a summarized manner from the queries and tables.

Reports refers to a database object which is used to generate the overall work outcome in a clear format.
[13] What are the main steps required to create a report through the wizard in the oo base?
Ans.: To create a report follow these steps:
1. Click on report section under database in OO Base database window.
2. Click on Use wizard to create report option under the tasks.
3. Follow the wizard steps as directed
4. Save the report

[14] Rashmi wants to create a report. She wants to customize the labels in the report. Suggest how she can fulfil the task?
Ans.: After selecting the fields in wizard the next step is labelling fields. This step allows to customize the label. It accepts
the default label as field names. Type the label text to customize the label. Repeat the procedure for all fields and move
towards the next step.

[15] Zeel is working in a domestic company and she wants to display state-wise records in the report. Write steps so she
can do this task easily.
Ans.: In report wizard, after customizing the label, next step allows to grouping the records. Select field to display the
records. Here Zeel needs to add the state field for grouping. And move further.

[16] Explain sorting options present in Create Report wizard in OO Base.

Ans.: The sorting option in create report wizard in OO base used to sort the records and place them on the report
accordingly. Use can sort 4 fields in ascending or descending order. The option Then By provides multiple field selection
for sorting.

[17] Explain the options provided in the step choose the layout in OO base to create a report wizard?
Ans.: The choose layout step of OO Base create report wizard has following options:
1. Layout of data: This option allows to select a pre-defined layout for data in the report.
2. Layout of header and footer: It sets the header and footer of the report.
3. Orientation: It can be either portrait or landscape. User can choose as per his choice.

[18] Explain the options supported in the step create report the final step of creating report wizard.
Ans.: The final step of OO base report wizard has following options:
1. Title of report: Type the desired of the report.
2. Select the option to choose kind of report required by user.
3. User can select statics or dynamic report.
4. Finally used can modify or create the report