Database Management System (DBMS) and SQL with complete practical-based questions and solutions.

1. Choose the correct option:

a) Which of the following is used to uniquely identify each row in a table?

i) Foreign Key
ii) Primary Key ✅
iii) Attribute
iv) Column

Answer: ii) Primary Key

b) In a table, the vertical arrangement of data is called a:

i) Field Name
ii) Row
iii) Column ✅
iv) Record

Answer: iii) Column

c) Which one is an example of open-source database software?

i) Oracle
ii) MySQL ✅
iii) MS Access
iv) MS Excel

Answer: ii) MySQL

d) What type of SQL command is used to insert new records into a table?

i) CREATE
ii) INSERT ✅
iii) UPDATE
iv) SELECT

Answer: ii) INSERT

e) Which clause is used in SQL to filter records based on a specific condition?

i) ORDER BY
ii) WHERE ✅
iii) SET
iv) VALUES

Answer: ii) WHERE

f) Which command is used to permanently delete a table from a database?

i) DELETE
ii) DROP ✅
iii) REMOVE
iv) ERASE

Answer: ii) DROP

g) Which command is used to retrieve data from a table in MySQL?

i) SELECT ✅
ii) INSERT
iii) DELETE
iv) UPDATE

Answer: i) SELECT

h) Which of the following is a DML command?

i) CREATE
ii) SELECT ✅
iii) DROP
iv) ALTER

Answer: ii) SELECT

i) The command that changes the structure of an existing table is:

i) CREATE
ii) ALTER ✅
iii) INSERT
iv) SELECT

Answer: ii) ALTER

j) Which of these data types is suitable to store textual information of variable length?

i) INT
ii) FLOAT
iii) VARCHAR ✅
iv) DATE

Answer: iii) VARCHAR

k) Which of these is a correct relational operator in SQL?

The OCR text in the source has lost the actual relational-operator options and shows “ADD, PUT, LINK”, which are not valid relational operators.

The standard relational operators include:

i) =
ii) >
iii) <
iv) >=
v) <=
vi) <>

Answer: The question's original correct option cannot be identified from the supplied OCR text.

l) Which clause is used to sort the result in ascending or descending order?

i) ORDER BY ✅
ii) SORT
iii) GROUP
iv) WHERE

Answer: i) ORDER BY

m) Which operator is used to select values between two ranges in SQL?

i) LIKE
ii) IN
iii) BETWEEN ✅
iv) IS

Answer: iii) BETWEEN

n) A command to modify existing data within a table is:

i) INSERT
ii) UPDATE ✅
iii) DELETE
iv) CREATE

Answer: ii) UPDATE

o) Which type of key is used to link one table with another table's primary key?

i) Candidate Key
ii) Foreign Key ✅
iii) Primary Key
iv) Composite Key

Answer: ii) Foreign Key

2. Write the Technical Terms for the Following

a. A software used to create, manage, and use databases.
Ans: DBMS (Database Management System)

b. An organized collection of related data stored in a computer system.
Ans: Database

c. Raw facts or figures that have no clear meaning until arranged.
Ans: Data

d. Organized and meaningful data obtained after processing.
Ans: Information

f. A column in one table that connects to the primary key of another table.

Ans: Foreign Key

g. A command in MySQL used to add new data into a table.
Ans: INSERT

h. A DDL command used to make a new table.
Ans: CREATE

i. A DML command used to modify existing records in a table.
Ans: UPDATE

j. A clause used to filter records based on a given condition.
Ans: WHERE

k. A clause used to sort records in ascending or descending order.
Ans: ORDER BY

l. A set of symbols or keywords used to perform operations in SQL.
Ans: SQL Operators

m. A command used to retrieve and display data from one or more tables.
Ans: SELECT

n. A DDL command used to permanently delete a table or database.
Ans: DROP

o. A command given to a database to perform a task like add, search, or delete.
Ans: Query

Descriptive Questions

1. Answer the following questions briefly:

a. What is the main difference between data and information?

Answer:
Data is a collection of raw facts and figures that may not have a clear meaning. Information is processed and organized data that has a meaningful result.

Example:

Data: 80, 75, 90
Information: The average mark of a student is 81.67.

b. Define DBMS with one clear example.

Answer:
DBMS (Database Management System) is software used to create, store, manage, update, and retrieve data from a database.

Example: MySQL is a DBMS.

c. What is RDBMS?

Answer:
RDBMS (Relational Database Management System) is a database management system that stores data in tables consisting of rows and columns and establishes relationships between tables.

Example: MySQL, Oracle, and Microsoft SQL Server.

d. Why is a primary key important in a database table?

Answer:
A primary key uniquely identifies each record in a table. It prevents duplicate values and helps identify and retrieve a particular record easily.

e. Write the use of the FOREIGN KEY in relational databases.

Answer:
A FOREIGN KEY is used to establish a relationship between two tables. It refers to the primary key of another table and helps maintain data integrity.

f. What is the purpose of the CREATE command in MySQL?

Answer:
The CREATE command is a DDL command used to create database objects such as databases and tables.

Example:

CREATE DATABASE School;

g. Name any two DDL commands.

Answer:
Two DDL commands are:

CREATE
DROP

Other examples include ALTER and TRUNCATE.

h. Why do we use the SELECT command in MySQL?

Answer:
The SELECT command is used to retrieve and display data from one or more tables in a database.

Example:

SELECT * FROM students;

i. List any two examples of clauses used with SQL statements.

Answer:
Two examples are:

WHERE
ORDER BY

j. What is the function of the ORDER BY clause in MySQL?

Answer:
The ORDER BY clause is used to arrange the result of a query in ascending or descending order.

Example:

SELECT * FROM students
ORDER BY name ASC;

k. Which command is used to remove specific records from a table? Write its syntax.

Answer:
The DELETE command is used to remove specific records from a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM students
WHERE ID = 5;

l. Write any two differences between DDL and DML commands.

Answer:

DDL	DML
DDL is used to define or modify the structure of database objects.	DML is used to manipulate data in tables.
Examples: `CREATE`, `ALTER`	Examples: `INSERT`, `UPDATE`

m. Write any two reasons why we use a database management system (DBMS) in computing.

Answer:
Two reasons are:

It helps to store and manage large amounts of data efficiently.
It makes it easy to retrieve, update, and organize data.

n. What do you mean by redundancy in a database system?

Answer:
Data redundancy means unnecessary or repeated duplication of the same data in a database.

Example: Storing the same student's address repeatedly in several records.

o. Why should the WHERE clause be used carefully with UPDATE and DELETE commands?

Answer:
The WHERE clause specifies which records should be affected. If it is omitted or written incorrectly, all records may be updated or deleted.

Example:

DELETE FROM students;

This can delete all records from the table.

p. What is relationship in MySQL?

Answer:
A relationship is a connection between two or more tables through common fields, usually using primary and foreign keys.

2. Answer the following questions comprehensively:

a. Explain the differences between a database and a DBMS with examples.

Answer:

A database is an organized collection of related data stored electronically. A DBMS is software that is used to create, manage, store, retrieve, and manipulate that data.

Database	DBMS
It is a collection of organized data.	It is software used to manage the data.
It stores the actual data.	It provides tools to create, modify, and retrieve data.
Example: Student database containing student records.	Example: MySQL, Oracle, MS Access.

Example:
A school may have a database containing student names, addresses, classes, and marks. MySQL can be used as the DBMS to manage that database.

b. What is a primary key? List any four features or functions of a primary key in a table.

Answer:

A primary key is a field or combination of fields that uniquely identifies each record in a table.

Features/functions of a primary key:

It uniquely identifies each record.
It does not allow duplicate values.
It cannot contain NULL values.
It helps establish relationships between tables through foreign keys.

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Class INT
);

Here, StudentID uniquely identifies each student.

c. What is the purpose of the FOREIGN KEY in MySQL? Explain with a suitable example.

Answer:

A FOREIGN KEY is a field in one table that refers to the primary key of another table. It is used to establish a relationship between two tables and maintain referential integrity.

Example:

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

Here:

DeptID is the primary key of the Department table.
DeptID in the Employee table is a foreign key.
It connects the Employee table with the Department table.

d. Write the differences between DDL and DML commands with any two examples each.

Answer:

DDL (Data Definition Language) is used to define and modify the structure of database objects.

Examples: CREATE, ALTER

DML (Data Manipulation Language) is used to insert, modify, and delete data in database tables.

Examples: INSERT, UPDATE

DDL	DML
Defines database structure.	Manipulates data.
Works mainly with database objects such as tables.	Works mainly with records.
Examples: `CREATE`, `ALTER`	Examples: `INSERT`, `UPDATE`

e. Describe the function of the following SQL commands: CREATE, DROP, INSERT, and SELECT.

Answer:

1. CREATE
Used to create a database or database object such as a table.

CREATE TABLE Students (
    ID INT,
    Name VARCHAR(50)
);

2. DROP
Used to permanently remove a database object such as a table or database.

DROP TABLE Students;

3. INSERT
Used to add new records to a table.

INSERT INTO Students
VALUES (1, 'Ram');

4. SELECT
Used to retrieve and display data from a table.

SELECT * FROM Students;

f. What are clauses in SQL? Explain the uses of WHERE, ORDER BY, and GROUP BY clauses.

Answer:

A clause is a part of an SQL statement that specifies a particular condition or operation.

1. WHERE
It is used to filter records according to a condition.

SELECT * FROM Students
WHERE Class = 10;

2. ORDER BY
It is used to sort records in ascending or descending order.

SELECT * FROM Students
ORDER BY Name ASC;

3. GROUP BY
It is used to group records having similar values.

SELECT Class, COUNT(*)
FROM Students
GROUP BY Class;

g. Define operators in SQL. List any four relational operators with examples.

Answer:

Operators are symbols or keywords used to perform operations or comparisons in SQL.

Four relational operators are:

Operator	Meaning	Example
`=`	Equal to	`WHERE Age = 15`
`>`	Greater than	`WHERE Marks > 80`
`<`	Less than	`WHERE Marks < 40`
`>=`	Greater than or equal to	`WHERE Marks >= 50`

Other relational operators include <= and <>.

h. What is a query in MySQL? Explain how queries help in managing database records.

Answer:

A query is a command or request given to a database to perform a particular task, such as retrieving, inserting, updating, or deleting data.

Queries help users to:

Retrieve required records.
Add new records.
Modify existing records.
Delete unwanted records.
Filter and sort data.

Example:

SELECT * FROM Students
WHERE Marks > 80;

This query displays students who scored more than 80 marks.

i. Explain with examples how the UPDATE and DELETE commands work with the WHERE clause.

Answer:

The UPDATE command modifies existing records. The WHERE clause specifies which records should be modified.

Example:

UPDATE Students
SET Marks = 90
WHERE ID = 3;

This changes the marks of the student whose ID is 3.

The DELETE command removes records from a table.

Example:

DELETE FROM Students
WHERE ID = 5;

This deletes the record whose ID is 5.

The WHERE clause is important because it prevents unintended changes to all records.

j. Write the importance of using a DBMS in computing. Mention any four advantages of a DBMS.

Answer:

A DBMS is important because it provides an organized and efficient way to store, manage, and retrieve data.

Four advantages are:

Easy data management: It helps users store and manage large amounts of data.
Easy data retrieval: Required information can be retrieved quickly using queries.
Reduced data redundancy: It helps reduce unnecessary duplication of data.
Data security: It can control access to database information.

k. What are SQL constraints? State their importance.

Answer:

SQL constraints are rules applied to table columns to control the type of data that can be stored.

Common constraints include:

PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
CHECK
DEFAULT

Importance:

They maintain data accuracy.
They prevent invalid data from being entered.
They maintain data integrity.
They help establish relationships between tables.

l. What do you mean by a report in MySQL? How does it help users?

Answer:

A report is an organized presentation of database information in a meaningful and readable form.

Reports help users to:

Present information clearly.
Analyze database records.
Prepare summaries.
Support decision-making.
Print or share important information.

m. Define a composite key. Explain when it is used in a database.

Answer:

A composite key is a key made up of two or more fields that together uniquely identify a record in a table.

It is used when a single field cannot uniquely identify a record.

Example:

Suppose a table records students enrolled in different courses:

StudentID    CourseID
101          C01
101          C02
102          C01

Here, StudentID alone is not unique and CourseID alone is not unique. But the combination of StudentID + CourseID can uniquely identify each enrollment.

n. What do you mean by a relationship in DBMS? Explain its types.

Answer:

A relationship in a DBMS is a connection between two or more tables based on related fields.

The main types of relationships are:

1. One-to-One (1:1)
One record in one table is related to only one record in another table.

Example: One person has one passport.

2. One-to-Many (1:M)
One record in one table can be related to many records in another table.

Example: One department can have many employees.

3. Many-to-Many (M:M)
Many records in one table can be related to many records in another table.

Example: Many students can enroll in many courses.

A many-to-many relationship is generally implemented using an intermediate/junction table.

For Knowledge Enhancement

Case Study and Application-Based Questions

1. A college wants to create a database system to manage student records, course details, and exam results. The database must store student ID, name, address, course ID, and marks obtained. The management also wants to ensure no two students have the same student ID, and each course has a unique course ID.

i) Design suitable table structures for this case.

Answer:

A suitable database can be designed using three tables:

1. Student Table

Field Name	Data Type	Key
StudentID	INT	Primary Key
Name	VARCHAR(50)	—
Address	VARCHAR(100)	—

2. Course Table

Field Name	Data Type	Key
CourseID	INT	Primary Key
CourseName	VARCHAR(50)	—

3. ExamResult Table

Field Name	Data Type	Key
StudentID	INT	Foreign Key
CourseID	INT	Foreign Key
MarksObtained	INT	—

The ExamResult table connects students with their courses and stores the marks obtained.

ii) Identify primary keys and foreign keys.

Answer:

Primary Keys:

StudentID → Primary key of the Student table.
CourseID → Primary key of the Course table.

Foreign Keys:

StudentID in the ExamResult table → Foreign key referencing StudentID in the Student table.
CourseID in the ExamResult table → Foreign key referencing CourseID in the Course table.

iii) Mention why these keys are necessary in this situation.

Answer:

These keys are necessary for the following reasons:

Primary Key ensures uniqueness:
StudentID ensures that no two students have the same ID.
Course identification:
CourseID uniquely identifies each course.
Establishes relationships:
Foreign keys connect the Student, Course, and ExamResult tables.
Maintains data integrity:
Foreign keys ensure that an exam result is associated with a valid student and a valid course.
Easy data retrieval:
The keys make it easier to retrieve a student's course and exam-result information.

Case Study and Application-Based Questions — Answers

2. Employee and Department Management System

Question:

A company is managing its employee and department details in two different tables, Employee and Department. The management wants to link the two tables so that every employee is assigned to a valid department.

i) Illustrate how you would create a relationship between these two tables using a FOREIGN KEY.

Answer:

First, create the Department table with DeptID as the primary key:

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

Then, create the Employee table and use DeptID as a foreign key:

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

Here, DeptID in the Employee table is a FOREIGN KEY that references DeptID in the Department table.

ii) Also, list two reasons why this relationship is important for maintaining data accuracy.

Answer:

It ensures that an employee can only be assigned to a valid department.
It maintains referential integrity and prevents invalid department IDs from being entered.

3. School Book Database

Question:

A school database contains a table called Book with fields: BookID, Title, Author, Price, and Category. The librarian wants to:

Display all books with price between 500 and 1000.
Increase the price of books in the 'Reference' category by 200.
Remove all books whose price is less than 200.

i) Write SQL queries for each of these three tasks.

Answer:

1. Display all books with price between 500 and 1000:

SELECT *
FROM Book
WHERE Price BETWEEN 500 AND 1000;

2. Increase the price of books in the Reference category by 200:

UPDATE Book
SET Price = Price + 200
WHERE Category = 'Reference';

3. Remove all books whose price is less than 200:

DELETE FROM Book
WHERE Price < 200;

4. Store Product Database

Question:

A store database has a Product table with columns: ProductID, ProductName, Category, Price, and Stock. The store manager wants to identify products with low stock (less than 20) in the 'Electronics' and 'Furniture' categories and delete them from the table.

i) Write the suitable DELETE query for this task.

Answer:

DELETE FROM Product
WHERE Stock < 20
AND Category IN ('Electronics', 'Furniture');

ii) Explain why the WHERE clause is important here.

Answer:

The WHERE clause is important because it specifies which records should be deleted.

In this case, only products that:

have stock less than 20, and
belong to either Electronics or Furniture

will be deleted.

Without the WHERE clause, the DELETE command could remove all records from the Product table.

5. Hospital Database

Question:

A hospital database is planned to store details of patients, doctors, and appointments. Each patient must have a unique PatientID, and each appointment record should be linked to a valid doctor and patient.

i) Design suitable table structures for this case.

Answer:

A suitable database can contain three tables.

1. Patient Table

CREATE TABLE Patient (
    PatientID VARCHAR(10) PRIMARY KEY,
    PatientName VARCHAR(50),
    Address VARCHAR(100),
    Phone VARCHAR(15)
);

2. Doctor Table

CREATE TABLE Doctor (
    DoctorID INT PRIMARY KEY,
    DoctorName VARCHAR(50),
    Specialization VARCHAR(50)
);

3. Appointment Table

CREATE TABLE Appointment (
    AppointmentID INT PRIMARY KEY,
    PatientID VARCHAR(10),
    DoctorID INT,
    AppointmentDate DATE,
    FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)
);

ii) Mention which fields should be primary keys and which should be foreign keys.

Answer:

Primary Keys:

PatientID → Patient table
DoctorID → Doctor table
AppointmentID → Appointment table

Foreign Keys:

PatientID in the Appointment table → references PatientID in the Patient table.
DoctorID in the Appointment table → references DoctorID in the Doctor table.

iii) Write a SELECT query to display the appointment details for a patient whose PatientID is `'P005'`.

Answer:

SELECT *
FROM Appointment
WHERE PatientID = 'P005';

This query displays all appointment records associated with patient P005.

6. Hotel Room Database

Question:

A hotel database has a table named Room with fields: RoomID, RoomType, PricePerNight, and Status. The hotel manager wants to:

Display details of all available rooms whose price per night is less than 3000.
Update the status of rooms of type 'Deluxe' to 'Booked'.

i) Write SQL queries for these operations.

Answer:

1. Display all available rooms whose price per night is less than 3000:

SELECT *
FROM Room
WHERE Status = 'Available'
AND PricePerNight < 3000;

2. Update the status of Deluxe rooms to Booked:

UPDATE Room
SET Status = 'Booked'
WHERE RoomType = 'Deluxe';

Case Study & Practical-Based Questions — Complete Answers

Below are the questions cleaned up and answered with proper SQL queries. I have retained the organization and tasks from the uploaded Smart Computer Science Book–10 material.

6. Hotel Management System

Question:

A hotel database has a table named Room with fields RoomID, RoomType, PricePerNight, and Status. The hotel manager wants to:

Display details of all available rooms whose price per night is less than 3000.
Update the status of rooms of type Deluxe to Booked.
Delete the records of rooms whose price per night is more than 5000.

i) Write appropriate SQL queries for each of these three operations.

Answer:

1. Display available rooms costing less than 3000:

SELECT *
FROM Room
WHERE Status = 'Available'
AND PricePerNight < 3000;

2. Update Deluxe rooms to Booked:

UPDATE Room
SET Status = 'Booked'
WHERE RoomType = 'Deluxe';

3. Delete rooms whose price is more than 5000:

DELETE FROM Room
WHERE PricePerNight > 5000;

7. Mobile Store Database

Question:

A mobile store database contains a table called Mobile with fields MobileID, Brand, Model, Price, and Stock. The store owner wants to:

List all mobiles from the brand Samsung with stock more than 20.
Increase the price of all Apple brand mobiles by 5000.
Delete records of mobiles whose stock is zero.

i) Write the SQL queries for these three tasks.

Answer:

1. List Samsung mobiles with stock more than 20:

SELECT *
FROM Mobile
WHERE Brand = 'Samsung'
AND Stock > 20;

2. Increase the price of Apple mobiles by 5000:

UPDATE Mobile
SET Price = Price + 5000
WHERE Brand = 'Apple';

3. Delete mobiles whose stock is zero:

DELETE FROM Mobile
WHERE Stock = 0;

Practical-Based Questions

Set-1: Product Inventory System

A. Create Database

a. Create a database named `InventoryDB`.

Answer:

CREATE DATABASE InventoryDB;

To use the database:

USE InventoryDB;

B. Create Table

b. Create a table `Product` with the following fields:

ProductID
ProductName
Category
Price
Stock

Answer:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Category VARCHAR(30),
    Price DECIMAL(10,2),
    Stock INT
);

C. Insert Records

c. Insert at least 5 suitable records into the table.

Answer:

INSERT INTO Product
(ProductID, ProductName, Category, Price, Stock)
VALUES
(1, 'Frying Pan', 'Kitchen', 1200, 25),
(2, 'Pressure Cooker', 'Kitchen', 2500, 30),
(3, 'Table Fan', 'Electronics', 3500, 15),
(4, 'Printer', 'Electronics', 6500, 22),
(5, 'Office Chair', 'Furniture', 4500, 35);

D. Practice the DML Questions

1. List out all the columns and rows from the table.

SELECT *
FROM Product;

2. Show products with price greater than 1500.

SELECT *
FROM Product
WHERE Price > 1500;

3. Display products from `Kitchen` or `Electronics`.

SELECT *
FROM Product
WHERE Category IN ('Kitchen', 'Electronics');

4. Show products with stock between 20 and 40.

SELECT *
FROM Product
WHERE Stock BETWEEN 20 AND 40;

5. Display products whose name starts with `P`.

SELECT *
FROM Product
WHERE ProductName LIKE 'P%';

6. List products with price less than or equal to 2000 and stock greater than 20.

SELECT *
FROM Product
WHERE Price <= 2000
AND Stock > 20;

7. Display products where price is not between 500 and 2000.

SELECT *
FROM Product
WHERE Price NOT BETWEEN 500 AND 2000;

8. Show products sorted by price in descending order.

SELECT *
FROM Product
ORDER BY Price DESC;

9. Increase price by 500 for products priced below 1500.

UPDATE Product
SET Price = Price + 500
WHERE Price < 1500;

10. Increase stock by 10 for `Electronics` products.

UPDATE Product
SET Stock = Stock + 10
WHERE Category = 'Electronics';

11. Change the category of `Frying Pan` to `Cookware`.

UPDATE Product
SET Category = 'Cookware'
WHERE ProductName = 'Frying Pan';

12. Decrease stock by 5 for products priced above 2000.

UPDATE Product
SET Stock = Stock - 5
WHERE Price > 2000;

13. Increase price by 200 for products with stock between 20 and 30.

UPDATE Product
SET Price = Price + 200
WHERE Stock BETWEEN 20 AND 30;

14. Delete products with stock less than 20.

DELETE FROM Product
WHERE Stock < 20;

15. Remove products from the `Kitchen` category.

DELETE FROM Product
WHERE Category = 'Kitchen';

16. Delete products priced above 7000.

DELETE FROM Product
WHERE Price > 7000;

17. Remove products whose name ends with `Fan`.

DELETE FROM Product
WHERE ProductName LIKE '%Fan';

18. Delete products where stock is not between 20 and 35.

DELETE FROM Product
WHERE Stock NOT BETWEEN 20 AND 35;

These Product Inventory tasks correspond to the practical set in the source.

Set-2: Students Marks Management

A. Create Database and Table

i) Create a database named `student_db`.

CREATE DATABASE student_db;

USE student_db;

ii) Create a table named `students` with the fields `ID`, `name`, `class`, `math`, `computer`, and `science`.

CREATE TABLE students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Class INT,
    Math INT,
    Computer INT,
    Science INT
);

iii) Insert at least 10 students' records into the `students` table.

INSERT INTO students
(ID, Name, Class, Math, Computer, Science)
VALUES
(1, 'Ram', 10, 85, 90, 88),
(2, 'Sita', 9, 78, 82, 80),
(3, 'Hari', 10, 92, 88, 91),
(4, 'Gita', 8, 65, 70, 68),
(5, 'Anil', 10, 55, 75, 60),
(6, 'Sunita', 9, 88, 94, 90),
(7, 'Asha', 8, 72, 80, 75),
(8, 'Bikash', 10, 38, 65, 45),
(9, 'Rita', 9, 81, 86, 84),
(10, 'Mina', 8, 35, 42, 40);

B. SELECT Queries

i) Display all records from the students table.

SELECT *
FROM students;

ii) Display students who belong to class 10.

SELECT *
FROM students
WHERE Class = 10;

iii) Display students who scored more than 80 marks in Math.

SELECT *
FROM students
WHERE Math > 80;

iv) Display students whose name starts with the letter `A`.

SELECT *
FROM students
WHERE Name LIKE 'A%';

v) Display all students ordered by Computer marks in descending order.

SELECT *
FROM students
ORDER BY Computer DESC;

C. UPDATE Queries

i) Update the Math marks of the student whose ID is 3.

UPDATE students
SET Math = 95
WHERE ID = 3;

ii) Increase Science marks by 5 for all students of class 9.

UPDATE students
SET Science = Science + 5
WHERE Class = 9;

iii) Update Computer marks to 90 for the student named Ram.

UPDATE students
SET Computer = 90
WHERE Name = 'Ram';

iv) Update Math marks for students who scored below 40.

UPDATE students
SET Math = Math + 5
WHERE Math < 40;

v) Change the class of the student whose name is Sita.

UPDATE students
SET Class = 10
WHERE Name = 'Sita';

D. DELETE Queries

i) Delete the record of the student whose ID is 5.

DELETE FROM students
WHERE ID = 5;

ii) Delete records of students belonging to class 8.

DELETE FROM students
WHERE Class = 8;

iii) Delete students who scored less than 35 marks in Math.

DELETE FROM students
WHERE Math < 35;

iv) Delete the student whose name is Hari.

DELETE FROM students
WHERE Name = 'Hari';

v) Delete students who scored below 40 marks in Computer.

DELETE FROM students
WHERE Computer < 40;

The source contains these Students Marks Management tasks, although OCR has interleaved some numbering and fragments.

Set-3: Student Information Management System

A. Database and Table Creation

i) Create a database named `student_info_db`.

The source text appears as student info db; using underscores is clearer and avoids spaces in the database name.

CREATE DATABASE student_info_db;

USE student_info_db;

ii) Create a table named `students_info` with the fields:

ID
Name
Date_of_Birth
Gender
Class
Section
Address
Phone

CREATE TABLE students_info (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Date_of_Birth DATE,
    Gender VARCHAR(10),
    Class INT,
    Section VARCHAR(5),
    Address VARCHAR(100),
    Phone VARCHAR(15)
);

iii) Insert at least 15 students' records.

INSERT INTO students_info
(ID, Name, Date_of_Birth, Gender, Class, Section, Address, Phone)
VALUES
(1, 'Aarav', '2010-01-15', 'Male', 10, 'A', 'Kathmandu', '9800000001'),
(2, 'Sita', '2010-03-20', 'Female', 10, 'A', 'Lalitpur', '9800000002'),
(3, 'Ram', '2011-05-12', 'Male', 9, 'B', 'Pokhara', '9800000003'),
(4, 'Anita', '2011-07-18', 'Female', 9, 'A', 'Kathmandu', '9800000004'),
(5, 'Ramesh', '2010-09-10', 'Male', 10, 'B', 'Bhaktapur', '9800000005'),
(6, 'Sunita', '2012-02-14', 'Female', 8, 'C', 'Lalitpur', '9800000006'),
(7, 'Hari', '2012-04-25', 'Male', 8, 'A', 'Pokhara', '9800000007'),
(8, 'Asha', '2011-06-30', 'Female', 9, 'C', 'Kathmandu', '9800000008'),
(9, 'Bikash', '2010-08-22', 'Male', 10, 'A', 'Lalitpur', '9800000009'),
(10, 'Sarita', '2012-10-11', 'Female', 7, 'B', 'Pokhara', '9800000010'),
(11, 'Suman', '2011-11-05', 'Male', 9, 'A', 'Kathmandu', '9800000011'),
(12, 'Hema', '2013-01-19', 'Female', 7, 'C', 'Bhaktapur', '9800000012'),
(13, 'Rita', '2010-12-08', 'Female', 10, 'B', 'Kathmandu', '9800000013'),
(14, 'Kiran', '2012-03-17', 'Male', 8, 'C', 'Lalitpur', '9800000014'),
(15, 'Sabin', '2011-09-27', 'Male', 9, 'B', 'Pokhara', '9800000015');

B. SELECT Queries

i) Display all records from the `students_info` table.

SELECT *
FROM students_info;

ii) Display students who belong to class 10 AND section `A`.

SELECT *
FROM students_info
WHERE Class = 10
AND Section = 'A';

iii) Display students who belong to class 9 OR class 10.

SELECT *
FROM students_info
WHERE Class = 9
OR Class = 10;

iv) Display students who are NOT in class 8.

SELECT *
FROM students_info
WHERE Class <> 8;

v) Display students whose name starts with the letter `S` using LIKE.

SELECT *
FROM students_info
WHERE Name LIKE 'S%';

vi) Display students whose address contains the word `Kathmandu` using LIKE.

SELECT *
FROM students_info
WHERE Address LIKE '%Kathmandu%';

vii) Display students whose ID is BETWEEN 5 AND 12.

SELECT *
FROM students_info
WHERE ID BETWEEN 5 AND 12;

viii) Display students whose date of birth is between two given dates.

SELECT *
FROM students_info
WHERE Date_of_Birth BETWEEN '2010-01-01' AND '2011-12-31';

ix) Display students ordered by name in ascending order.

SELECT *
FROM students_info
ORDER BY Name ASC;

x) Display the number of students in each class using GROUP BY.

SELECT Class, COUNT(*) AS NumberOfStudents
FROM students_info
GROUP BY Class;

xi) Display classes having more than 3 students using HAVING.

SELECT Class, COUNT(*) AS NumberOfStudents
FROM students_info
GROUP BY Class
HAVING COUNT(*) > 3;

xii) Display the average age of students in each class using GROUP BY and HAVING.

SELECT Class,
       AVG(TIMESTAMPDIFF(YEAR, Date_of_Birth, CURDATE())) AS AverageAge
FROM students_info
GROUP BY Class
HAVING AVG(TIMESTAMPDIFF(YEAR, Date_of_Birth, CURDATE())) > 0;

C. UPDATE Queries

i) Update the phone number of a student whose ID is 5.

UPDATE students_info
SET Phone = '9811111111'
WHERE ID = 5;

ii) Update the address of students who belong to class 10 AND section `B`.

UPDATE students_info
SET Address = 'Kathmandu'
WHERE Class = 10
AND Section = 'B';

iii) Update the section of students who belong to class 9 OR class 8.

UPDATE students_info
SET Section = 'A'
WHERE Class = 9
OR Class = 8;

iv) Update the class of students who are NOT in class 10.

UPDATE students_info
SET Class = 10
WHERE Class <> 10;

v) Update the phone number of students whose name starts with `R` using LIKE.

UPDATE students_info
SET Phone = '9822222222'
WHERE Name LIKE 'R%';

vi) Update the address of students whose ID is BETWEEN 6 AND 10.

UPDATE students_info
SET Address = 'Kathmandu'
WHERE ID BETWEEN 6 AND 10;

vii) Update the section of students whose address contains `Lalitpur`.

UPDATE students_info
SET Section = 'B'
WHERE Address LIKE '%Lalitpur%';

viii) Update the class of students who belong to class 8 AND section `C`.

UPDATE students_info
SET Class = 9
WHERE Class = 8
AND Section = 'C';

D. DELETE Queries

i) Delete the record of the student whose ID is 12.

DELETE FROM students_info
WHERE ID = 12;

ii) Delete students who belong to class 8 AND section `C`.

DELETE FROM students_info
WHERE Class = 8
AND Section = 'C';

iii) Delete students who belong to class 7 OR class 6.

DELETE FROM students_info
WHERE Class = 7
OR Class = 6;

iv) Delete students who are NOT in class 10.

DELETE FROM students_info
WHERE Class <> 10;

v) Delete students whose name starts with `H` using LIKE.

DELETE FROM students_info
WHERE Name LIKE 'H%';

vi) Delete students whose ID is BETWEEN 1 AND 3.

DELETE FROM students_info
WHERE ID BETWEEN 1 AND 3;

vii) Delete students whose address contains `Pokhara`.

DELETE FROM students_info
WHERE Address LIKE '%Pokhara%';

viii) Delete classes having less than 2 students using GROUP BY and HAVING.

A direct DELETE ... GROUP BY ... HAVING is not the appropriate MySQL form. We can first identify the classes and then delete students belonging to those classes:

DELETE FROM students_info
WHERE Class IN (
    SELECT Class
    FROM (
        SELECT Class
        FROM students_info
        GROUP BY Class
        HAVING COUNT(*) < 2
    ) AS Temp
);

The source's Set-3 section contains these SELECT, UPDATE, and DELETE tasks.

Set-4: Library Management System

A. Database and Table Creation

i) Create a database named `library_db`.

CREATE DATABASE library_db;

USE library_db;

ii) Create a table named `books` with the fields:

book_id
title
author
category
published_year
price
quantity

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(50),
    category VARCHAR(30),
    published_year INT,
    price DECIMAL(10,2),
    quantity INT
);

iii) Insert at least 15 books' records into the `books` table.

INSERT INTO books
(book_id, title, author, category, published_year, price, quantity)
VALUES
(1, 'Basic Mathematics', 'Biseswor', 'Math', 2018, 450, 10),
(2, 'Science Today', 'Ramesh', 'Science', 2019, 400, 8),
(3, 'Advanced Physics', 'Hari', 'Science', 2020, 1200, 5),
(4, 'English Grammar', 'Sita', 'Education', 2017, 350, 12),
(5, 'Reference Book', 'Anil', 'Reference', 2021, 800, 6),
(6, 'Nepali Stories', 'Gita', 'Fiction', 2016, 500, 9),
(7, 'Computer Science', 'Biseswor', 'Science', 2019, 900, 7),
(8, 'Algebra Basics', 'Ram', 'Math', 2015, 600, 10),
(9, 'World History', 'Mina', 'History', 2014, 750, 4),
(10, 'Comics World', 'Unknown', 'Comics', 1999, 250, 0),
(11, 'Animal Stories', 'Suman', 'Fiction', 2018, 300, 15),
(12, 'Physics Fundamentals', 'Hari', 'Science', 2020, 1100, 5),
(13, 'Zoology Guide', 'Rita', 'Science', 2022, 650, 6),
(14, 'Math Practice', 'Anil', 'Math', 2017, 450, 8),
(15, 'Fiction Collection', 'Sita', 'Fiction', 2019, 850, 3);

B. SELECT Queries

i) Display all records from the `books` table.

SELECT *
FROM books;

ii) Display books whose category is `Science` AND price is less than 500.

SELECT *
FROM books
WHERE category = 'Science'
AND price < 500;

iii) Display books whose title starts with `A` using LIKE.

SELECT *
FROM books
WHERE title LIKE 'A%';

iv) Display the number of books available in each category using GROUP BY.

SELECT category, COUNT(*) AS NumberOfBooks
FROM books
GROUP BY category;

v) Display books whose price is BETWEEN 200 and 800.

SELECT *
FROM books
WHERE price BETWEEN 200 AND 800;

vi) Display books whose author is `Biseswor` OR category is `Fiction`.

SELECT *
FROM books
WHERE author = 'Biseswor'
OR category = 'Fiction';

vii) Display books whose title does NOT contain `Math`.

SELECT *
FROM books
WHERE title NOT LIKE '%Math%';

viii) Display books ordered by published year in descending order.

SELECT *
FROM books
ORDER BY published_year DESC;

ix) Display categories having more than 3 books using HAVING.

SELECT category, COUNT(*) AS NumberOfBooks
FROM books
GROUP BY category
HAVING COUNT(*) > 3;

C. UPDATE Queries

i) Update the quantity of a book whose `book_id = 5`.

UPDATE books
SET quantity = 10
WHERE book_id = 5;

ii) Update the price of books whose category is `Science` AND published year is less than 2020.

UPDATE books
SET price = price + 100
WHERE category = 'Science'
AND published_year < 2020;

iii) Update the title of a book whose `book_id = 8`.

UPDATE books
SET title = 'Advanced Algebra'
WHERE book_id = 8;

iv) Update the author of books whose title contains `Physics` using LIKE.

UPDATE books
SET author = 'Biseswor'
WHERE title LIKE '%Physics%';

v) Update the quantity of books whose category is `Fiction`.

UPDATE books
SET quantity = quantity + 5
WHERE category = 'Fiction';

vi) Update the price of books whose published year is BETWEEN 2015 and 2020.

UPDATE books
SET price = price + 100
WHERE published_year BETWEEN 2015 AND 2020;

vii) Update the category of books whose title starts with `A`.

UPDATE books
SET category = 'Education'
WHERE title LIKE 'A%';

viii) Update the author of books whose price is greater than 1000.

UPDATE books
SET author = 'Biseswor'
WHERE price > 1000;

ix) Update the published year of books whose category is `Math` AND price is less than 500.

UPDATE books
SET published_year = 2023
WHERE category = 'Math'
AND price < 500;

D. DELETE Queries

i) Delete the book whose `book_id = 10`.

DELETE FROM books
WHERE book_id = 10;

ii) Delete books whose category is `Comics` OR quantity is 0.

DELETE FROM books
WHERE category = 'Comics'
OR quantity = 0;

iii) Delete books whose title does NOT contain `Science`.

DELETE FROM books
WHERE title NOT LIKE '%Science%';

iv) Delete books whose price is BETWEEN 100 and 300.

DELETE FROM books
WHERE price BETWEEN 100 AND 300;

v) Delete books whose published year is less than 2000.

DELETE FROM books
WHERE published_year < 2000;

vi) Delete books whose category is `Fiction` AND price is greater than 800.

DELETE FROM books
WHERE category = 'Fiction'
AND price > 800;

vii) Delete books whose title starts with `Z` using LIKE.

DELETE FROM books
WHERE title LIKE 'Z%';

viii) Delete books whose author is `Unknown`.

DELETE FROM books
WHERE author = 'Unknown';

ix) Delete books whose quantity is 1.

DELETE FROM books
WHERE quantity = 1;

The Library Management System tasks are contained in the uploaded source, including the SELECT, UPDATE, and DELETE exercises.

Set-5: Employee Management System

A. Database and Table Creation

i) Create a database named `employee_db`.

Answer:

CREATE DATABASE employee_db;

USE employee_db;

ii) Create a table named `employees` with the fields:

emp_id
name
department
designation
salary
join_date
experience

Answer:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    designation VARCHAR(50),
    salary DECIMAL(10,2),
    join_date DATE,
    experience INT
);

CLASS 10 COMPUTER SCIENCE | SQL PRACTICAL QUESTIONS & SOLUTIONS

1. Choose the correct option:

2. Write the Technical Terms for the Following

Descriptive Questions

1. Answer the following questions briefly:

a. What is the main difference between data and information?

b. Define DBMS with one clear example.

c. What is RDBMS?

d. Why is a primary key important in a database table?

e. Write the use of the FOREIGN KEY in relational databases.

f. What is the purpose of the CREATE command in MySQL?

g. Name any two DDL commands.

h. Why do we use the SELECT command in MySQL?

i. List any two examples of clauses used with SQL statements.

j. What is the function of the ORDER BY clause in MySQL?

k. Which command is used to remove specific records from a table? Write its syntax.

l. Write any two differences between DDL and DML commands.

m. Write any two reasons why we use a database management system (DBMS) in computing.

n. What do you mean by redundancy in a database system?

o. Why should the WHERE clause be used carefully with UPDATE and DELETE commands?

p. What is relationship in MySQL?

2. Answer the following questions comprehensively:

a. Explain the differences between a database and a DBMS with examples.

b. What is a primary key? List any four features or functions of a primary key in a table.

c. What is the purpose of the FOREIGN KEY in MySQL? Explain with a suitable example.

d. Write the differences between DDL and DML commands with any two examples each.

e. Describe the function of the following SQL commands: CREATE, DROP, INSERT, and SELECT.

f. What are clauses in SQL? Explain the uses of WHERE, ORDER BY, and GROUP BY clauses.

g. Define operators in SQL. List any four relational operators with examples.

h. What is a query in MySQL? Explain how queries help in managing database records.

i. Explain with examples how the UPDATE and DELETE commands work with the WHERE clause.

j. Write the importance of using a DBMS in computing. Mention any four advantages of a DBMS.

k. What are SQL constraints? State their importance.

l. What do you mean by a report in MySQL? How does it help users?

m. Define a composite key. Explain when it is used in a database.

n. What do you mean by a relationship in DBMS? Explain its types.

For Knowledge Enhancement

Case Study and Application-Based Questions

i) Design suitable table structures for this case.

1. Student Table

2. Course Table

3. ExamResult Table

ii) Identify primary keys and foreign keys.

iii) Mention why these keys are necessary in this situation.

Case Study and Application-Based Questions — Answers

2. Employee and Department Management System

Question:

i) Illustrate how you would create a relationship between these two tables using a FOREIGN KEY.

ii) Also, list two reasons why this relationship is important for maintaining data accuracy.

3. School Book Database

Question:

i) Write SQL queries for each of these three tasks.

4. Store Product Database

Question:

i) Write the suitable DELETE query for this task.

ii) Explain why the WHERE clause is important here.

5. Hospital Database

Question:

i) Design suitable table structures for this case.

1. Patient Table

2. Doctor Table

3. Appointment Table

ii) Mention which fields should be primary keys and which should be foreign keys.

iii) Write a SELECT query to display the appointment details for a patient whose PatientID is 'P005'.

6. Hotel Room Database

Question:

i) Write SQL queries for these operations.

Case Study & Practical-Based Questions — Complete Answers

6. Hotel Management System

Question:

i) Write appropriate SQL queries for each of these three operations.

7. Mobile Store Database

Question:

i) Write the SQL queries for these three tasks.

Practical-Based Questions

Set-1: Product Inventory System

A. Create Database

a. Create a database named InventoryDB.

B. Create Table

b. Create a table Product with the following fields:

iii) Write a SELECT query to display the appointment details for a patient whose PatientID is `'P005'`.

a. Create a database named `InventoryDB`.

b. Create a table `Product` with the following fields:

3. Display products from `Kitchen` or `Electronics`.

5. Display products whose name starts with `P`.

10. Increase stock by 10 for `Electronics` products.

11. Change the category of `Frying Pan` to `Cookware`.

15. Remove products from the `Kitchen` category.

17. Remove products whose name ends with `Fan`.

i) Create a database named `student_db`.

ii) Create a table named `students` with the fields `ID`, `name`, `class`, `math`, `computer`, and `science`.

iii) Insert at least 10 students' records into the `students` table.

iv) Display students whose name starts with the letter `A`.

i) Create a database named `student_info_db`.

ii) Create a table named `students_info` with the fields:

i) Display all records from the `students_info` table.

ii) Display students who belong to class 10 AND section `A`.

v) Display students whose name starts with the letter `S` using LIKE.

vi) Display students whose address contains the word `Kathmandu` using LIKE.

ii) Update the address of students who belong to class 10 AND section `B`.

v) Update the phone number of students whose name starts with `R` using LIKE.