Unit 3

Database Management
System

Learning Outcome – Create and edit tables using wizard

Assignment 11

Create any table in LO base using table wizard.

Solution
1. Click on Start  All Programs  Libre Office 4.1.7  Libre Office Base.

2. LO Base window will open with Database Wizard. Select or create the database to

work upon it. I have selected existing database db1. Click on Finish button.

1|Page
2|Page
3. Now click on Tables button from Left pane and chLOse Use Wizard to Create Table…
option from the tasks window.

4. The Table Wizard will open. Follow wizard steps to create a table.

3|Page
5. In first step of wizard select the Category either Business or Personal, Table from list of
sample tables, and fields from available fields. Click on Finish button.

6. Click on Next and select field types and all if you want to change it. Click on Next.

4|Page
7. Set a primary key for your table in this step. I have selected EmplyeeID as Primary key.

8. If you wish to change the table name then type new name for the table and click on
Insert Data immediately, and click on Finish.

9. Insert data.
5|Page
 Assignment 12
Learning Outcome – Retrieve data using query

Objective: Query creation using wizard

Task: Create table Marksheet (Using SQL Command) and perform the below given queries
using wizard and design view.

Field Name Data Type Size

Stud_No Integer 2
Name Text 15

RollNo Integer 3

Sub101 Decimal 3,2

Sub102 Decimal 3,2

Sub103 Decimal 3,2

1. In the Database file Add these Fields: (Total: Datatype- Number 3 digits, Percentage:
Datatype - Number 3 digits with 2 decimal places, Grade: Datatype- Char with 2 letters)

2. Insert more 3 records in MARKSHEET using SQL mode.

3. Display name, rollno, marks of 3 subjects, total and percentage using design view.

4. Display name, rollno, grades from the marksheet table using query wizard. 9

5. Display the maximum marks for Sub101 and minimum marks for Sub102 using design
view.

6. Display the rollno, name and percentage whose percentage are more than 70 using design
view.

7. Display all the record in ascending order of names using design view.
Solution

Create table command

create table “marksheet” (“Stud_no” tinyint primary key, “Name” varchar(15),

“RollNo” tinyint, “Sub101” decimal(5,2), “Sub102” decimal(5,2), “Sub103”

decimal(5,2)) Output
Queries

1. Add Columns

1. Add column Total  alter table "marksheet" add column "Total" tinyint
2. Add column Percentage  alter table "marksheet" add column "Percentage"
decimal(5,2)
3. Add column grade  alter table "marksheet" add column "Grade" char(2)

2. Insert records
1. insert into "marksheet" values (101, 'Sagar', 105, 27, 25, 28, 80 , 88 , 'B1')
3. Steps to perform a query using design view
1. Click Queries  Create Query in Design View….
2. The query design window will open.
3. Select the marksheet table and click on Add button.
4. Now select fields given in the question like name, rollno, marks,Sub101,
Sub102, Sub103, Total and Percentage.

8|Page
 5. Save the query.

4. Query Wizard Steps

1. Click on Queries  Use Wizard to Create Query…
2. Query Wizard opens.
3. Select the table marksheet and Select the fields given the question.

4. Click on finish.
Output

9|Page
5. Display maximum marks and minimum marks for the fields given in the question,

using design view.

1. Click Queries  Create Query in Design View….

2. The query design window will open.

3. Select the marksheet table and click on Add button.

4. Now select the field Sub101 and Sub102.

5. ChLOse the function maximum under Sub101 and Minimum under Sub102.

6. Save the query and check the result.

10 | P a g e
 6. Display the rollno, name and percentage whose percentage are more than 70 using

design view

1. Click Queries  Create Query in Design View….

2. The query design window will open.

3. Select the marksheet table and click on Add button.

4. Select columns given in the question i.e. Rollno, Name and Percentage.

5. Type >70 in the front of criteria under the percentage field.

6. Save the query and check the result.

Output

11 | P a g e
 7. Display all the record in ascending order of names using design view.
1. Click Queries  Create Query in Design View….
2. The query design window will open.
3. Select the marksheet table and click on Add button.
4. Select markesheet.* and name in the column list.
5. Select sort  ascending under name field.
6. Now click on Visible checkbox to hide the name column in result.

7. Save the query and check the result.

Output

12 | P a g e
Assignment 14

Learning Outcome – Create Forms and Reports using wizard

Objective: Creating form using wizard

Task: Create table a form using wizard by selecting all the fields for the table - Mark sheet

Solution
Steps to create a form using wizard
1. Click on Forms  Use Wizard to Create Form…

2. A Form Wizard appears.

3. Select the table from Tables or Queries then add all the fields.

4. Click on Next button. Ignore step 3 and step 4.

13 | P a g e
5. Select the first option i.e. Columnar – Labels Left then click on the next button.

6. Now set data entry step will be there. Ignore this step and click on Next. ChLOse the
styles for the form interface and click on next.

14 | P a g e
 7. Type new name for the form and click on work with the form. Click on Finish

Assignment 15
Objective: Creating report using wizard
Task: Create table a report using wizard by selecting all the fields for the table - Marksheet

Solution
1. Click on Reports  Use Wizard to Create report. The report wizard will open in the new
window.
2. Select the table and add fields.

15 | P a g e
3. Now change the label text that you want in the report. I have changed the label
Stud_no into Adm. No. Click on Next.

4. I have skipped groping and sorting options by click on Next button.

5. Now choose the layout. I have selected Outline-Elegant as Layout of Data,

Landscape Orientation, and Bubbles Layout of Headers and Footers. Click on Next.

16 | P a g e
6. Now type the title of the report and select dynamic report  Create report now option.

7. Now click on finish.

Output

17 | P a g e