(Grade X) Chapter 2 : Database Management System-Exercise
Exercises- Page number 92
1. Select the correct answer from the given alternatives:
a.
Which of the following is not the DBMS software?
i.
MySQL
ii.
Ms Access
iii.
Ms Excel
iv.
Oracle
b. Which of the following is referred to as data redundancy?
i.
Records stored in ascending or descending order
ii.
Extracting selected records from the table.
iii.
Data displayed in printed form.
iv.
Duplication or repetition of data.
c. Which of the following is referred to as string data type in My SQL?
i
varchar
ii.
int
iii.
float
iv.
double
d. Which component of table gives the detail information about person place, things or any object?
i.
field
ii.
record
iii.
attribute
iv.
column
e. Which of the following defines a field or attribute which uniquely identifies each record in a table?
i.
foreign key
ii.
secondary key
iii.
primary key
iv.
non key
f. In which of the following relationship each record in one table is related with multiple records in another table?
i. one
to one
ii
one to many
iii.
many to some
iv.
some to many
g.Which of the following is an example of DDL?
i.SELECT
ii.
DELETE
iii.
UPDATE
iv.
ALTER
h. A from the table named 'Employees'? Which SQL query is used to display the records whose name starts with
i.
SELECT FROM Employees WHERE name LIKE '%A';
ii.
SELECT FROM Employees WHERE name LIKE 'A%;
iii.
SELECT FROM Employees WHERE name = "%A';
iv.
SELECT FROM Employees WHERE name = 'A%';
i. Which SQL query is used to modify the existing records in the database?
i.
Truncate
ii.
Alter
iii.
Update
iv.
Modify
j. Each column in a table is known as unit of information. which gives the smallest
i.
Attribute
ii.
Record
iii.
Tuple
iv.
Primary
k. Which SQL query is used to delete the record of Employees whose Age is greater than 58 from table named "Employees" ?
i.
DELETE FROM Employees WHERE Age>58;
ii.
DROP FROM Employees WHERE Age>58;
iii.
ERASE FROM Employees WHERE Age>58;
iv.
ALTER FROM Employees WHERE Age>58;
l. Which is the correct query to update the record from table name Student with fields Id, Roll, Name?
i.
UPDATE Student WHERE Id=1 SET Roll=150;
ii.
UPDATE Student SET Roll-150 WHERE id=1;
iii.
UPDATE Student SET roll=150 WHILE id=1;
iv.
UPDATE Student Roll-150 WHERE id=1;
m. Which SQL query is used to display the records whose name ends wi from the table named 'Employees'?
i.SELECT
FROM Employees WHERE name LIKE '%A';
ii
SELECT FROM Employees WHERE name LIKE 'A%;
iii.
SELECT FROM Employees WHERE name = "%A',
iv.SELECT
FROM Employees WHERE name = 'A%'; IV.
n. Which of the following statements are used in DDL?
i. Create,
Alter and Drop
ii.
Insert, Update and Delete
iii.
Create, Insert and Select
iv.
Delete, Alter and Drop
o. Which SQL query is used to display the records having second letter Name as A from the table named 'Employees'?
i.SELECT
FROM Employees WHERE Name LIKE 'A'
ii.
SELECT FROM Employees WHERE Name = 'A%';
iii.
SELECT FROM Employees WHERE Name LIKE '%_A';
iv.SELECT
* FROM Employees WHERE name LIKE 'A%';
2.
Write Short Answer Questions for the following:
a. Define
data and information.
Data: Data are the raw facts and figure which are
isolated and uninterrupted generally represented by letter and numbers. Data
undergoes processing. E.g. 16, John, 10
Information: The collection of meaningful result obtained
after processing raw data. They are generated after processing.
E.g.
John is 16 years old and he reads in class 10.
b. What is database? Write it's any two examples.
The
systematic and scientific collection of data that can access quickly whenever
required is called database. It is the organized way of collecting and
keeping data and information.
E.g.
Dictionary, telephone directory, marks ledger, attendance register.
c.What is DBMS? Write any two examples of DBMS?
DBMS
stands for Database Management System which is the collection of software that
is used to manage database i.e. storing, manipulating and retrieving data
systematically and scientifically. It is a software used to store, process
and access data and information whenever required.
Example- i. MsAccess ii.Oracle iii. Foxpro iv.MsSql
d.Define
field and record with examples.
Each column in a table is known as field
which gives the smallest unit of information. The field in the database is
topic name or heading under which similar kind of information is stored. Field
is also known as attributes.
•
In
the following database table of students, Symbol number, Name, Class and
address is field.
|
Symbol
number |
Name |
Class |
Address |
|
4786 |
Sworup Shrestha |
11 |
Kathmandu |
|
3256 |
Nabiska Shakya |
10 |
Lalitpur |
Each row in a table is known as record
which gives the detail information about person, place or any object. Records
are also known as tuples. In the above database table of students 4786,
Sworup, 10, Kathmandu, 3256, Nabiska Shakya, 10, Lalitpur is record.
e.What
is primary key? Write its importance.
A
primary key is a column (or field) that uniquely identifies each row in a
table. No two rows can have the same primary key value, and it cannot be empty.
For example, a student ID.
Importance:
· No duplication
· Ensures data integrity
f.
What is DDL? Write its an two examples.
DDL
is used to define and modify the structure of a database, such as creating,
altering, or deleting tables. DDL includes commands like CREATE, ALTER, and
DROP that affect the database schema.
Example:
CREATE
TABLE students (
id INT,
name VARCHAR(50)
);
g.What
is SQL? Write its features.
SQL
commands are the fundamental building blocks for communicating with a database
management system (DBMS).
Features:
· Easy to use
· Supports data retrieval
· Works with RDBMS
h.
What is data redundancy? How can data redundancy be controlled in RDBMS
Repetition of data in a database is known as data
redundancy.
Control:
· Normalization
· Proper database design
3.Write
Long Answer Questions for the following:
a.What
is DBMS? Explain the advantages of DBMS.
•
DBMS
stands for Database Management System which is the collection of software that
is used to manage database i.e. storing, manipulating and retrieving data
systematically and scientifically.
•
It
is a software used to store, process and access data and information whenever
required.
•
Example- i. MsAccess
ii.Oracle
iii. Foxpro
iv.MsSql
v.
MySql vi. Dbase
vii. Delphi
viii.Sybase
ix. Foxbase
x. IBM DB2
Advantages of DBMS
1.
Prevents
data redundancy
DBMS avoids
storing the same data multiple times. It organizes data properly so duplication
is reduced. This makes data more consistent and saves storage space.
2.
Faster
operation of data
DBMS allows
quick access, updating, and retrieval of data. It uses efficient methods to
process queries faster. This improves overall system performance.
3.
Data
security and privacy
DBMS protects
data from unauthorized access. Only allowed users can view or change the data.
4.
Recovery
and backup
DBMS can recover
data if there is a system failure. It regularly keeps backups of important
data. This ensures that data is not lost permanently.
5.
Data
integrity
DBMS ensures
that data remains accurate and consistent. It prevents incorrect or invalid
data entry. Rules and constraints help maintain data quality.
b.Differentiate between DBMS and RDBMS.
|
DBMS |
RDBMS |
|
Data
elements need to access individually. |
Multiple data elements can be
accessed at the same time. |
|
It deals
with small quantity of data. |
It deals with large amount of
data. |
|
DBMS
does not support distributed database. |
RDBMS supports distributed
database. |
|
It deals
with small quantity of data. |
It deals with large amount of
data. |
|
No
relationship between data. |
Data is stored in the form of
tables which are related to each other. |
c.What
is data type? Explain the different data types of MySQL with examples.
A data type
is a classification that specifies what kind of value can be stored in a field
or variable, such as numbers, text, date, or decimal values. It determines the
type of data a database column can hold and what operations can be performed on
it.
1. Numeric Data
Types
Used for storing numbers.
|
Data types |
Description |
|
INT |
Whole numbers. |
|
DECIMAL(p,s) |
Exact numbers
with decimal points. p is the total number of digits, s
is the number of digits after the decimal |
|
FLOAT |
Approximate
numbers with decimals (less precise but can store very
large or small numbers) |
|
SMALLINT |
Smaller range of whole numbers |
|
BIGINT |
Very large whole numbers |
2. Character
(Text) Data Types
Used for storing letters, words, or
text.
|
Data types |
Description |
|
CHAR(n) |
Fixed-length
text. Always stores exactly n characters. |
|
VARCHAR(n) |
Variable-length
text up to n characters. More flexible. |
|
TEXT |
Long blocks of text. Used for storing
large paragraphs. |
3. Date and Time
Data Types
Used for storing dates and times.
|
Data types |
Description |
|
DATE |
Stores only
the date (e.g., 2047-07-06) |
|
TIME |
Stores only
the time (e.g., 14:30:00) |
|
DATETIME |
Stores both
date and time together. |
|
TIMESTAMP |
Similar to
DATETIME, often used to record events automatically |
4. Boolean Data
Type
Used for storing true/false values.
|
Data types |
Description |
|
BOOLEAN |
Stores either
TRUE or FALSE |
d.
Explain the different types of keys with examples.
Types
of keys
1. Primary Key
A
primary key is a column (or field) that uniquely identifies each row in a
table. No two rows can have the same primary key value, and it cannot be empty.
For example, a student ID.
2. Foreign Key
A
foreign key is a column that links one table to another table. It refers to the
primary key of another table. This helps create relationships between tables.
3. Composite Key
A
composite key is made of two or more columns used together to uniquely identify
a row. It is used when a single column is not enough to ensure uniqueness. For
example, a combination of student ID and course ID.
e.What
is relationship? Explain the different types of relationship.
Database relationships are fundamental
concepts in relational database design. They allow data to be stored
efficiently, reduce redundancy, improve data integrity, and enable accurate
retrieval of information. In a database, a relationship is a logical connection
between two or more tables, typically based on a common field such as a key.
These relationships make it possible to link data stored in separate tables and
access them together in a meaningful and organized way. There are three types
of relationship in database they are:
1. One-to-one ( 1 : 1 )
2. One-to-many ( 1 : M )
3. Many-to-many ( M : M )
Types of
Relationship
1. One-to-one relationship: In a
one-to-one relationship, each record in one table is connected to only one
record in another table. This type of relationship is not very common, but it
is useful when we want to separate closely related information into two
different tables.
Example: A person can have only one
passport, and each passport is issued to only one person.
2. One-to-many
relationship:
In a one-to-many
relationship, a single record in one table is linked to multiple records in another
table. However, each record in the second table is connected to only one record
in the first table.
This is the most
common type of relationship in databases. It is useful when one item is related
to several items of another type.
Example: A
teacher may teach many classes, but each class is taught by only one teacher.
1.
Many-to-many relationship:
In a
many-to-many relationship, multiple records in one table can be related to
multiple records in another table. This type of relationship requires a third
table, called a junction table, to link the records from both tables.
Example: A
student can enroll in many courses, and each course can have many students.
DML
is used to manipulate the data stored in the database, such as inserting,
updating, or deleting records. DML includes commands like INSERT, UPDATE, and
DELETE that work on the data inside the tables.
DML
is used to work with data inside tables. Common DML Commands are as follows:
· SELECT
· INSERT
· UPDATE
· DELETE
Example:
INSERT
INTO students VALUES (1, 'Ram');
Activitiy
Lab
Activity 1
1.
Write the SQL statement to perform the following task.
a.
Create a table named Students with following fields and insert the following
five records with Roll_number as Primary key.
|
Roll_number |
Name |
Class |
Address |
Percentage |
|
450 |
Bipin |
10 |
Kathmandu |
86 |
|
325 |
Udaya |
9 |
Bhaktapur |
54 |
|
780 |
Rabina |
11 |
Pokhara |
90 |
|
452 |
Swostika |
10 |
Pokhara |
65 |
|
100 |
Bijaya |
9 |
Surkhet |
45 |
Roll_number INT PRIMARY KEY,
Name VARCHAR(50),
Class INT,
Address VARCHAR(50),
Percentage INT
);
INSERT INTO Students VALUES
(450, 'Bipin', 10, 'Kathmandu', 86),
(325, 'Udaya', 9, 'Bhaktapur', 54),
(780, 'Rabina', 11, 'Pokhara', 90),
(452, 'Swostika', 10, 'Pokhara', 65),
(100, 'Bijaya', 9, 'Surkhet', 45);
b. Add the field named Rank in the above table and insert the records.
ALTER TABLE Students ADD Rank INT;
UPDATE Students SET Rank = 1 WHERE
Roll_number = 450;
UPDATE Students SET Rank = 2 WHERE
Roll_number = 325;
UPDATE Students SET Rank = 3 WHERE
Roll_number = 780;
UPDATE Students SET Rank = 4 WHERE
Roll_number = 452;
UPDATE Students SET Rank = 5 WHERE
Roll_number = 100;
c. Display Name, Class and Address of
the students from the above table.
SELECT Name, Class, Address FROM Students;
d. Display all the records of students
whose Address is "Pokhara"
SELECT * FROM Students
WHERE Address = 'Pokhara';
e. Display Roll number, Name and
Percentage from the above table.
SELECT Roll_number, Name, Percentage FROM Students;
f. Increase the percentage of all the students by 5.
UPDATE Students
SET Percentage = Percentage + 5;
g. Update the Name and Class of Student
into Navya and 12 having roll number 450.
UPDATE Students
SET Name = 'Navya', Class = 12
WHERE Roll_number = 450;
h. Display the record of students whose
Name ends with 'a'
SELECT * FROM Students
WHERE Name LIKE '%a';
i. Display the record in ascending
order according to Percentage.
SELECT * FROM Students
ORDER BY Percentage ASC;
j. Delete the record of students named
Bipin.
DELETE FROM Students
WHERE Name = 'Bipin';
k. Display the record of students who
studies either in class 9 or 10.
SELECT * FROM Students
WHERE Class IN (9,10);
l. Display the record of students whose
address is Pokhara and studies in class 10.
SELECT * FROM Students
WHERE Address = 'Pokhara' AND Class =
10;
m. Display the record of students who
does not live in Kathmandu.
SELECT * FROM Students
WHERE Address <> 'Kathmandu';
n. Display Name, Class and Address of
students whose Percentage is greater than 85
SELECT Name, Class, Address
FROM Students
WHERE Percentage > 85;
o. Delete the record of student named
Bijaya.
DELETE FROM Students
WHERE Name = 'Bijaya';
Lab
Activity 2
1. Write the SQL statement to perform the following task.
a. Create a
table named Employee with following fields and insert the following five
records with Emp_Id as primary key.
|
Emp_Id |
Name |
Post |
Salary |
|
1 |
Shyam |
Manager |
78000 |
|
2 |
Hari |
Officer |
50000 |
|
3 |
Ramesh |
DBA |
80000 |
|
4 |
Gita |
Manager |
65000 |
|
5 |
Swonika |
Engineer |
95000 |
CREATE
TABLE Employee (
Emp_Id INT PRIMARY KEY,
Name VARCHAR(50),
Post VARCHAR(50),
Salary INT
);
INSERT INTO
Employee VALUES
(1,
'Shyam', 'Manager', 78000),
(2, 'Hari',
'Officer', 50000),
(3,
'Ramesh', 'DBA', 80000),
(4, 'Gita',
'Manager', 65000),
(5,
'Swonika', 'Engineer', 95000);
b. Display
all the records of employees whose post is Manager.
SELECT *
FROM Employee
WHERE Post
= 'Manager';
c. Display
Name and Post of employees whose Salary is greater than 70000.
SELECT
Name, Post
FROM
Employee
WHERE
Salary > 70000;
d. Display
all the record of employee whose Emp Id is 5.
SELECT *
FROM Employee
WHERE
Emp_Id = 5;
e. Increase
the Salary of employees whose post is Manager by 10%.
UPDATE
Employee
SET Salary
= Salary + (Salary * 10 / 100)
WHERE Post
= 'Manager';
f. Display
Name and Post of employees.
SELECT
Name, Post
FROM
Employee;
g. Delete
the record of employee whose post is DBA.
DELETE FROM
Employee
WHERE Post
= 'DBA';
h. Display
all the record of employees whose post is Officer.
SELECT *
FROM Employee
WHERE Post
= 'Officer';
i. Display
all the records whose Name starts with 'S'.
SELECT *
FROM Employee
WHERE Name
LIKE 'S%';
j. Display
the records in descending order by Salary.
SELECT *
FROM Employee
ORDER BY
Salary DESC;
k. Display
all the records whose Post is either Manager or Engineer.
SELECT *
FROM Employee
WHERE Post
IN ('Manager', 'Engineer');