1. Draw the E-R diagram for the following scenario.

IT industry is developing several projects on various domains such as banking, education

2. What do you mean by an integrity constraint? Explain any two with example.
Ans. An integrity constraint is a rule designed to ensure the accuracy, consistency, and
reliability of data stored in a database.
• Entity Integrity: This constraint ensures that every table has a primary key and that it
cannot contain null values. For example, in a “Books” table, the “Book_ID” column is the
primary key and must be unique and non-null for each book.
• Referential Integrity: This rule ensures a foreign key value either matches a primary key in
another table or is null. For instance, an “Orders” table might use “Customer_ID” as a
foreign key, which must exist in the “Customers” table.
3. Explain any three aggregate functions in SQL with proper example.
Ans. Aggregate functions compute values across multiple rows in a table.
• COUNT(): Returns the number of rows.
Example: SELECT COUNT(*) FROM Employee; finds the total number of employees.
• SUM(): Calculates the total for a numeric column.
Example: SELECT SUM(Salary) FROM Employee; finds the total salary paid.
• AVG(): Determines the average value of a numeric column.
Example: SELECT AVG(Salary) FROM Employee; computes the average salary.
4. Write syntax for CREATE TABLE statement and UPDATE statement.
Ans. • CREATE TABLE: CREATE TABLE table_name (column1 datatype, column2 datatype, …);
Example: CREATE TABLE Student (ID int, Name varchar(255));
• UPDATE: UPDATE table_name SET column1 = value1 WHERE condition;
Example: UPDATE Student SET Name = ‘Amit’ WHERE ID = 101;
 5. Consider the following relations:
Country (con-code, name, capital)
Population (pop-code, population)
Country and population are related with one-to-one relationship. Create a RDB and solve
the following queries in SQL.
• Give the name and population of country whose capital is “Delhi”.
• ii)List the name of all the countries whose population is greater than 250000.
• Delete the country whose capital is “Tokyo”.
Ans. • Table creation: CREATE TABLE Country (con_code INT PRIMARY KEY, name
VARCHAR(50), capital VARCHAR(50));
CREATE TABLE Population (pop_code INT PRIMARY KEY, population BIGINT);

SQL Queries:
i) SELECT [Link], [Link] FROM Country JOIN Population ON
Country.con_code = Population.pop_code WHERE [Link] = ‘Delhi’;
ii) SELECT name FROM Country JOIN Population ON Country.con_code =
Population.pop_code WHERE [Link] > 250000;
iii) DELETE FROM Country WHERE capital = ‘Tokyo’;
6. Explain DBMS structure with neat diagram.
Ans. • Query Processor: Includes DDL interpreter, DML compiler, and query optimizer for
converting user queries and optimizing execution.
• Storage Manager: Handles storage, retrieval, and integrity of data using modules like
authorization manager and buffer manager.
• Disk Storage: Stores the actual data.
7. What is DDL and DML? Explain with example.
Ans. • DDL is used to define and manage the structure of database objects like tables,
schemas, indexes, and views.
Example: CREATE TABLE Students(Student_ID INT, Name VARCHAR(100), Age INT);
• DML is used to manipulate the data stored in database tables.
Example: INSERT INTO Students(Student_ID, Name, Age)VALUES (1, ‘Amit’, 20);

8. State and explain different types of relationships that can exist in an entity set in an
E-R model.
Ans. • One-to-One: One entity in set A relates to one entity in set B.
E.g., each employee with one set of contact info.
• One-to-Many: One entity in set A relates to multiple entities in set B.
E.g., department with multiple employees.
• Many-to-Many: Each entity in A relates to multiple entities in B and vice versa.
E.g., students enrolled in multiple courses; a course with multiple students.
9. Explain four clauses with the help of examples.
Ans. • WHERE: Used to filter records based on specific conditions.
Example: SELECT * FROM Student WHERE age > 18;
• GROUP BY: Used to group rows that have the same values in specified columns, often with
aggregate functions.
Example: SELECT class, COUNT(*) FROM Student GROUP BY class;
• HAVING: Used to filter groups created by GROUP BY, similar to WHERE but for aggregated
data.
Example: SELECT class, COUNT(*) FROM Student GROUP BY class HAVING COUNT(*) > 5;
• ORDER BY: Used to sort the result set in ascending or descending order.
Example: SELECT name, age FROM Student ORDER BY age DESC;
10. What is Join? Explain their types.
Ans. In DBMS (Database Management System), a JOIN is used to combine rows from two or
more tables based on a related column between them. It helps retrieve meaningful data
spread across multiple tables.
Types of JOINs in DBMS :
• INNER JOIN : Returns only the matching rows from both tables.
- Example : SELECT [Link], [Link] FROM Employees INNER JOIN
Departments ON [Link] = [Link];
• LEFT JOIN (or LEFT OUTER JOIN) : Returns all rows from the left table and matching rows
from the right table. Non-matching rows from the right table are shown as NULL.
- Example : SELECT [Link], [Link] FROM Employees LEFT JOIN
Departments ON [Link] = [Link];
• RIGHT JOIN (or RIGHT OUTER JOIN) : Returns all rows from the right table and matching
rows from the left table. Non-matching rows from the left table are shown as NULL.
- Example : SELECT [Link], [Link] FROM Employees RIGHT JOIN
Departments ON [Link] = [Link];
• FULL JOIN (or FULL OUTER JOIN) : Returns all rows when there is a match in either left or
right table. Non-matching rows are filled with NULLs.
- Example: SELECT [Link], [Link] FROM Employees FULL
OUTER JOIN Departments ON [Link] = [Link];