Information System Management Lab

PRACTICAL FILE
Submitted for partial fulfillment for the award of the Degree
of

BACHELORS OF BUSINESS ADMINISTRATION

Under the supervision of
Dr. Manju Pathania Biswas
(Associate Professor)
Submitted by: Amulay Singhal
Enrollment No: 25119101722
BBA M5C , Batch: 2022–2025

GITARATTAN INTERNATIONAL BUSINESS SCHOOL

(Affiliated to Guru Gobind Singh Indraprastha University)
MadhubanChowk, ROHINI, DELHI –110085

1
 INDEX

S.NO PARTICULARS PG.NO

1 INTRODUCTION OF SQL 3-5
2 TYPES OF DATA IN SQL 6-8
3 SQL DATA DEFINATION LANGUAGE AND DATA 8-15
MANIPULATION LANGUAGE COMMANDS
4 TABLE CREATION AND RECORD INSERTION 15-18
5 DATA MANIPULATION AND AGGREGATION 18-22
6 CONDITIONAL UPDATES AND DELETION 22-25
7 AGGREGATE FUNCTIONS 26-30
8 GROUP BY AND AGGREGATE FUNCTIONS 30-32
9 DML STATEMENTS USING AGGREGATE 32-36
FUNCTIONS
10 DDL AND DML STATEMENTS FOR STUDENT 37-46
TABLE
11 ER- MODELLING AND DIAGRAMS 46-59
a. BANKING SYSTEM
b. UNIVERSITY

2
 INFORMATION SYSTEM MANAGEMENT
LAB FILE
PAPER CODE: BBA-307

Q1. Introduction of SQL.

= SQL (Structured Query Language) is a standard programming language
specifically designed for managing, manipulating, and querying data in
relational databases. Developed in the early 1970s at IBM by Donald D.
Chamberlin and Raymond F. Boyce, SQL has since become the most widely
used language for interacting with relational databases, such as MySQL,
PostgreSQL, Microsoft SQL Server, and Oracle Database.
Key Aspects of SQL:
1. Data Definition Language (DDL): SQL allows you to define the
structure of a database using DDL commands such as:
o CREATE: Used to create new databases, tables, and other
database objects.
o ALTER: Modifies the structure of an existing database object,
such as a table.
o DROP: Deletes entire databases, tables, or other objects.
2. Data Manipulation Language (DML): This set of SQL commands
enables data manipulation within tables. Common DML operations
include:
o SELECT: Retrieves data from one or more tables. It’s one of the
most frequently used SQL commands, with the ability to filter, sort,
and aggregate data.
o INSERT: Adds new rows of data to a table.
o UPDATE: Modifies existing data within a table.
o DELETE: Removes rows of data from a table.

3
3. Data Control Language (DCL): DCL commands manage access
permissions and define privileges in a database. For example:
o GRANT: Grants specific privileges to users.
o REVOKE: Removes previously granted privileges.
4. Transaction Control Language (TCL): SQL also provides commands to
manage transactions (a series of related SQL operations). This is crucial
for maintaining data integrity and preventing errors during multi-step
operations. Common TCL commands include:
o COMMIT: Saves all changes made in a transaction to the
database.
o ROLLBACK: Undoes all changes made in the current transaction
if something goes wrong.
5. Relational Databases: SQL is specifically used for relational databases,
which store data in tables (rows and columns). These tables can be linked
or related to each other based on common data values, hence the term
"relational." SQL provides powerful mechanisms, such as JOINs, to
combine data from multiple related tables.
6. SQL Queries: SQL allows for complex querying of data. A query
typically starts with the SELECT statement, and users can add clauses
such as:
o WHERE: Filters rows based on specified conditions.
o GROUP BY: Groups rows that have the same values in specified
columns, often used with aggregate functions like COUNT, SUM,
and AVG.
o ORDER BY: Sorts the result set based on one or more columns.
7. Functions and Expressions: SQL includes built-in functions to perform
calculations, manipulate strings, work with dates, and aggregate data.
Examples include:
o Aggregate functions: COUNT(), SUM(), MAX(), MIN(), AVG().
o String functions: CONCAT(), UPPER(), LOWER(),
SUBSTRING().
o Date functions: NOW(), DATEDIFF(), DATEADD().
8. Normalization and Integrity: SQL plays a key role in enforcing
normalization, which organizes data to reduce redundancy and improve

4
 data integrity. Constraints such as primary keys, foreign keys, unique
keys, and checks ensure the accuracy and consistency of data.
SQL Standardization:
SQL has been standardized by various organizations, including the American
National Standards Institute (ANSI) and the International Organization for
Standardization (ISO). These standards ensure that SQL is consistent across
different platforms, though there are often vendor-specific extensions in
databases like Oracle or Microsoft SQL Server.
Advantages of SQL:
 Simplicity: SQL uses English-like commands, making it easy to learn
and use.
 Powerful Querying: SQL can handle both simple and highly complex
queries to retrieve specific data from large databases.
 Cross-Platform: SQL is supported by virtually all relational database
systems, providing flexibility in switching between databases.
 Data Integrity: SQL ensures data accuracy through constraints and
transaction controls.
 Scalability: SQL can efficiently manage both small and large amounts of
data.
Conclusion:
SQL remains the backbone of database management, enabling organizations to
store, retrieve, and analyze their data efficiently. Its wide adoption across
industries makes it an essential skill for developers, data analysts, and database
administrators.

5
Q2. Types of data type.
= In SQL, data types define the kind of values that can be stored in a column.
Each column in a database table must be assigned a specific data type, which
dictates how SQL will interpret and store that data. SQL supports a wide variety
of data types, typically categorized into several groups: numeric, string
(character), date/time, and others like binary and spatial types.

1. Numeric Data Types

These are used to store numbers, both integers and floating-point (decimal)
values. SQL provides various types of numeric data, based on the precision and
range required.
 INT / INTEGER:
o Stores whole numbers (integers) without decimal points.
o Commonly used for counting purposes (e.g., the number of items
in an order).
o Size varies depending on the DBMS, but typically it allows values
between -2,147,483,648 and 2,147,483,647.
 SMALLINT:
o Stores smaller integer values with a reduced range compared to
INT.
o Its range typically spans from -32,768 to 32,767.
 TINYINT:
o Stores very small integer values. Typically used for storing small-
range values like flags (0 or 1) or small counts.
o Range: 0 to 255 (unsigned) or -128 to 127 (signed).
 BIGINT:
o Used for storing very large integers, with a range typically between
-9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
o Suitable for storing large numbers such as global user IDs or
tracking high-volume data.

6
  DECIMAL / NUMERIC:
o Stores exact numeric values with a fixed decimal point.
o Ideal for storing precise financial data (e.g., currency values).
o Defined as DECIMAL(p, s) where p is the precision (total number
of digits) and s is the scale (digits after the decimal point).
 FLOAT:
o Stores approximate numeric values with floating decimal points.
o Suitable for scientific calculations where absolute precision isn't
critical.
o FLOAT is typically less precise than DECIMAL, as it
approximates values.
 REAL / DOUBLE:
o Similar to FLOAT, but with larger precision and storage capacity.
o Used when more accuracy is needed than a FLOAT can provide.

2. String (Character) Data Types

String data types are used to store text or character-based data.
 CHAR(n):
o Stores fixed-length strings of n characters.
o If the input string is shorter than the defined length, it will be
padded with spaces.
o For example, CHAR(10) will always store 10 characters, even if
the value entered is only 5 characters long.
 VARCHAR(n):
o Stores variable-length strings up to n characters.
o Unlike CHAR, it only uses the storage necessary for the entered
value, without padding.

7
 o Typically used for storing names, addresses, or other variable-
length textual data.

 TEXT / CLOB (Character Large Object):

o Stores large strings, typically much longer than what VARCHAR
can handle.
o This data type is used for storing large text data such as blog
content, descriptions, or documents.

Conclusion:
Choosing the right data type is crucial for ensuring data integrity, efficient
storage, and effective querying. Each data type in SQL is tailored for specific
kinds of data, and understanding them ensures that databases are optimized for
performance and accuracy. Whether handling numbers, strings, dates, or even
binary data, SQL offers a versatile range of types to manage a wide array of data
formats.

Q3. SQL DDL COMMANDS

= SQL Data Definition Language (DDL) commands are used to define and
manage the structure of database objects such as databases, tables, views,
indexes, and schemas. DDL commands mainly deal with the creation, alteration,
and deletion of these objects. These commands don't manipulate data itself but
rather the schema that holds the data.
Here are the major DDL commands explained in detail:
1. CREATE
The CREATE command is used to create new objects in a database, such as
databases, tables, indexes, views, or schemas. Each object type has its own
syntax.

8
Examples:
 Creating a Database:
CREATE DATABASE my_database;
This command creates a new database named my_database.

 Creating a Table:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
salary DECIMAL(10, 2))
This command creates a new table named employees with four columns:
employee_id, first_name, last_name, hire_date, and salary. The employee_id
column is set as the primary key.

 Creating an Index:
CREATE INDEX idx_last_name ON employees(last_name);
This creates an index on the last_name column of the employees table to
improve query performance when searching by last_name.

 Creating a View:
CREATE VIEW employee_salaries AS
SELECT first_name, last_name, salary
FROM employees;

9
A view named employee_salaries is created, which provides a simplified
interface for querying the first_name, last_name, and salary columns from the
employees table.
2. ALTER
The ALTER command is used to modify the structure of an existing database
object. Common uses of ALTER include adding or deleting columns in a table,
changing data types, or modifying constraints.
Examples:
 Adding a Column to a Table:
ALTER TABLE employees
ADD department VARCHAR(100);
This adds a new column called department to the employees table.
 Modifying a Column Data Type:
ALTER TABLE employees
MODIFY salary DECIMAL(12, 2);
This modifies the data type of the salary column to allow larger decimal values
(in this case, up to 12 digits, with 2 digits after the decimal).

 Dropping a Column from a Table:

ALTER TABLE employees
DROP COLUMN department;
This removes the department column from the employees table.
 Adding a Constraint:
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);
This adds a CHECK constraint to the salary column, ensuring that salary values
must be greater than 0.

10
3. DROP
The DROP command is used to delete objects from the database, such as tables,
databases, indexes, or views. Be careful when using DROP because it removes
the object entirely, including its data, and this action cannot be undone.
Examples:
 Dropping a Database:
DROP DATABASE my_database;
This command deletes the entire my_database and all its contents (tables, views,
etc.).
 Dropping a Table:
DROP TABLE employees;
This command deletes the employees table and all the data stored in it.
 Dropping an Index:
DROP INDEX idx_last_name ON employees;
This command deletes the index idx_last_name on the employees table.
 Dropping a View:
DROP VIEW employee_salaries;
This command deletes the view employee_salaries.

4. TRUNCATE
The TRUNCATE command is used to remove all rows from a table, while
keeping the table structure intact. Unlike DELETE, which can remove specific
rows and logs each deletion, TRUNCATE is faster because it doesn't log
individual row deletions. However, TRUNCATE cannot be rolled back in many
systems and is a more drastic operation.
Example:
TRUNCATE TABLE employees;
This command deletes all rows from the employees table, but keeps the table
and its structure available for future use.

11
5. RENAME
The RENAME command is used to rename database objects such as tables,
views, or columns. This is often necessary when adjusting to new naming
conventions or when a more meaningful name is needed.
Examples:
 Renaming a Table:
RENAME TABLE employees TO staff;
This command renames the table employees to staff.
 Renaming a Column: Some database systems, like MySQL, allow
renaming a column using ALTER:
ALTER TABLE employees RENAME COLUMN first_name TO given_name;
6. COMMENT
Some database systems support a COMMENT command to add metadata
comments to database objects, helping developers and database administrators
understand the structure and purpose of the objects.
Example:
COMMENT ON COLUMN employees.salary IS 'Salary in USD';
This command adds a comment to the salary column of the employees table,
specifying that the values represent salaries in USD.

Q4. SQL DML COMMANDS.

SQL Data Manipulation Language (DML) commands are used to manage and
manipulate data stored in database tables. Unlike Data Definition Language
(DDL), which focuses on the structure of the database, DML deals with
inserting, updating, deleting, and retrieving data from tables.
Here’s a detailed overview of the key DML commands:

1. INSERT
The INSERT command is used to add new rows of data into a table. You can
insert data into all columns or specific columns, depending on the need.

12
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example:
INSERT INTO employees (employee_id, first_name, last_name, hire_date,
salary)
VALUES (101, 'John', 'Doe', '2024-01-01', 50000);
This command inserts a new row into the employees table with values for the
employee_id, first_name, last_name, hire_date, and salary columns.
 INSERT INTO ... SELECT: This variation inserts rows based on data
retrieved from another table.
INSERT INTO employees_archive (employee_id, first_name, last_name,
salary)
SELECT employee_id, first_name, last_name, salary
FROM employees WHERE salary > 60000;

2. UPDATE
The UPDATE command is used to modify existing data in a table. It updates
one or more columns for one or more rows, depending on the condition
specified.

Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example:
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

13
This command updates the salary column for all employees in the Sales
department, giving them a 10% raise.

 Without WHERE: If the WHERE clause is omitted, all rows in the table
will be updated. Use it cautiously!
UPDATE employees
SET salary = 50000;
This command would set the salary of every employee in the table to 50,000,
which may not be desired unless intentionally resetting all values.

3. DELETE
The DELETE command removes rows from a table based on a given condition.
Unlike TRUNCATE (a DDL command), which removes all rows, DELETE
allows you to remove specific rows.
Syntax:
DELETE FROM table_name
WHERE condition;

Example:
DELETE FROM employees
WHERE employee_id = 101;
This command deletes the row where employee_id is 101 from the employees
table.
 Without WHERE: Similar to UPDATE, if the WHERE clause is
omitted, all rows in the table will be deleted. This would remove all data
from the table, but the table itself would remain.
DELETE FROM employees;
This command would delete all rows from the employees table, but the structure
of the table remains intact.

14
4. SELECT
The SELECT command is used to retrieve data from one or more tables. It
allows you to specify which columns to retrieve, filter rows based on
conditions, and even perform aggregations like counting, summing, or
averaging values.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
 Simple Select:
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Sales';
This retrieves the first_name, last_name, and salary of all employees in the
Sales department.

 Select All Columns:

SELECT * FROM employees;
This retrieves all columns from the employees table.
 Aggregate Functions:
SELECT COUNT(*), AVG(salary)
FROM employees
WHERE department = 'HR';
This counts the number of employees in the HR department and calculates their
average salary.

15
Q5. Write a query to create a table called Employees with the following
columns:
EmployeeID (INT, Primary Key)
FirstName (VARCHAR)
LastName (VARCHAR)
Age (INT)
Department (VARCHAR)
Salary (INT)

= To create a table called Employees with the specified columns, the SQL query
would look like this:

Explanation of the query:

16
  EmployeeID: An integer that serves as the primary key, ensuring each
employee has a unique ID.
 FirstName and LastName: Variable-length strings (VARCHAR) to store
the employee's first and last names, with a maximum length of 50
characters.
 Age: An integer to store the age of the employee.
 Department: A variable-length string to store the department name, also
up to 50 characters.
 Salary: An integer representing the employee's salary.

You can adjust the VARCHAR lengths based on the expected maximum length
of the data for each field.

Q6. Write a query and insert 10 records into the Employees table
= To insert 10 records into the Employees table, you can use the INSERT INTO
statement. Below is an example SQL query that inserts ten records into the
Employees table:

17
Explanation:
 The INSERT INTO Employees statement specifies the table where you
want to insert records.
 The columns are specified in parentheses: (EmployeeID, FirstName,
LastName, Age, Department, Salary).
 The VALUES keyword is followed by multiple sets of values, each
enclosed in parentheses and separated by commas.
This command will insert all ten records into the Employees table in one go. If
there are still issues, please ensure that the table has been created and that there
are no conflicts with existing data, especially with the EmployeeID primary key.

Q7. Write a query to select all columns from the employee table.

To select all columns from the Employees table, you can use the following SQL
query:
SELECT * FROM Employees;

18
19
Explanation:
 SELECT: This keyword is used to specify which columns you want to
retrieve.
 *: The asterisk (*) is a wildcard that means "all columns."
 FROM Employees: This specifies the table from which you want to
retrieve the data.
Running this query will return all the records and columns in the Employees
table.

Q8. Select the FirstName, LastName, and Salary of employees in the IT

department.

20
Explanation:
 SELECT FirstName, LastName, Salary: This part specifies the
columns you want to retrieve.
 FROM Employees: This indicates that you are querying data from the
Employees table.
 WHERE Department = 'IT': This condition filters the results to include
only those employees who work in the IT department.
Running this query will return the first name, last name, and salary of all
employees in the IT department.

Q9. Retrieve details of employees with a salary greater than 55,000, sorted by
Salary in descending order.

21
Explanation:
 SELECT *: This selects all columns from the Employees table.
 FROM Employees: This specifies the table from which to retrieve the
data.
 WHERE Salary > 55000: This condition filters the results to include
only employees with a salary greater than 55,000.
 ORDER BY Salary DESC: This sorts the results by the Salary column
in descending order, meaning the highest salaries will appear first.
Executing this query will give you a list of employees who earn more than
55,000, along with all their details, sorted from the highest salary to the lowest.

Q10. Write a query to update the salary of the employee with EmployeeID = 1
to 55,000

22
Explanation:
 UPDATE Employees: This specifies the table you want to update.
 SET Salary = 55000: This sets the Salary column to 55,000 for the
specified employee.
 WHERE EmployeeID = 1: This condition ensures that only the
employee with EmployeeID equal to 1 will be affected by this update.
Executing this query will change the salary of the employee with EmployeeID =
1 to 55,000.

Q11. Increase the salary of all employees in HR department by 10%.

23
Explanation:
 UPDATE Employees: This specifies the table you want to update.
 SET Salary = Salary * 1.10: This updates the Salary column by
multiplying the current salary by 1.10 (which represents a 10% increase).
 WHERE Department = 'HR': This condition ensures that only
employees in the HR department will have their salaries updated.
Executing this query will increase the salary of all employees in the HR
department by 10%.

Q12. Write a query to add a new column HireDate (DATE) to the employees
table.

24
Explanation:
 ALTER TABLE Employees: This specifies that you want to modify the
existing Employees table.
 ADD HireDate DATE: This part of the command adds a new column
called HireDate with the data type DATE.
Executing this query will successfully add the HireDate column to the
Employees table.

Q13. Write a query to delete the employee with employeeID=2

Explanation:
 DELETE FROM Employees: This specifies that you want to remove
records from the Employees table.
 WHERE EmployeeID = 2: This condition ensures that only the
employee with EmployeeID equal to 2 will be deleted.
Executing this query will permanently remove the record of the employee with
EmployeeID = 2 from the Employees table.
Q14. Delete all employees from the sales department.

25
Q15. Write a query to calculate the total salary of all employees.

26
Explanation:
 SELECT SUM(Salary): This part of the query calculates the sum of the
Salary column.
 AS TotalSalary: This gives a name (alias) to the resulting column for
clarity in the output.
 FROM Employees: This specifies the table from which to retrieve the
data.
Executing this query will return a single value representing the total salary of all
employees in the Employees table.

Q16. Write a query to find the average salary of employees in the IT

department.

Explanation:
 SELECT AVG(Salary): This part calculates the average of the Salary
column.
 AS AverageSalary: This provides an alias for the resulting column,
making it clear in the output.
 FROM Employees: This specifies the table from which to retrieve the
data.

27
  WHERE Department = 'IT': This condition filters the results to include
only employees in the IT department.
Executing this query will return the average salary of all employees working in
the IT department.

Q17. Write a query to count the number of employees in the HR department.

Explanation:
 SELECT COUNT(*): This counts the total number of rows that match
the specified condition.
 AS NumberOfEmployees: This gives a name (alias) to the resulting
count for clarity in the output.
 FROM Employees: This specifies the table from which to retrieve the
data.
 WHERE Department = 'HR': This condition filters the results to
include only employees in the HR department.
Executing this query will return a single value representing the number of
employees in the HR department.

Q18. Write a query to find the highest and lowest salary in the employee table.

28
Q19. Write a query to group employees by Department and calculate the total
salary for each department.

Explanation:

29
  SELECT Department: This specifies that you want to retrieve the
department names.
 SUM(Salary): This calculates the total salary for each department.
 AS TotalSalary: This gives a name (alias) to the resulting total salary
column.
 FROM Employees: This specifies the table from which to retrieve the
data.
 GROUP BY Department: This groups the results by the Department
column, so that the SUM(Salary) calculation is applied to each
department separately.
Executing this query will return a list of departments along with the total salary
for each department.

Q20. Write a query to group employees by Age and calculate the average salary
for each age group.

Explanation:

30
  SELECT Age: This specifies that you want to retrieve the age of the
employees.
 AVG(Salary): This calculates the average salary for each age group.
 AS AverageSalary: This provides an alias for the resulting average salary
column.
 FROM Employees: This indicates the table from which to retrieve the
data.
 GROUP BY Age: This groups the results by the Age column, so that the
AVG(Salary) calculation is applied to each age group.
Executing this query will return a list of ages along with the average salary for
each age group.

Q21. Write a query to count the number of employees in each department.

Explanation:

31
  SELECT Department: This retrieves the names of the departments.
 COUNT(*): This counts the total number of employees in each
department.
 AS NumberOfEmployees: This provides an alias for the resulting count
of employees for clarity in the output.
 FROM Employees: This specifies the table from which to retrieve the
data.
 GROUP BY Department: This groups the results by the Department
column, allowing the COUNT(*) function to count employees in each
department separately.
Executing this query will return a list of departments along with the number of
employees in each department.

Q22. Write a query to group employees by Department and calculate the

maximum salary in each department. Only include departments where the
maximum salary is above 60,000.
Example: SELECT Department, MAX(Salary) AS MaxSalary FROM
Employees
GROUP BY Department HAVING MAX(Salary) > 60000;

32
Explanation:
 SELECT Department: This retrieves the names of the departments.
 MAX(Salary): This calculates the maximum salary for each department.
 AS MaxSalary: This provides an alias for the resulting maximum salary
column.
 FROM Employees: This specifies the table from which to retrieve the
data.
 GROUP BY Department: This groups the results by the Department
column, allowing the MAX(Salary) function to compute the maximum
salary for each department.
 HAVING MAX(Salary) > 60000: This condition filters the results to
include only those departments where the maximum salary exceeds
60,000.
Executing this query will return a list of departments with their maximum
salaries, but only for those departments where the maximum salary is greater
than 60,000.

33
Q23. Write a query to select all employees and order them by LastName in
ascending order.
Example: SELECT * FROM Employees ORDER BY LastName ASC;

Explanation:
 SELECT *: This selects all columns from the Employees table.
 FROM Employees: This specifies the table from which to retrieve the
data.
 ORDER BY LastName ASC: This orders the results by the LastName
column in ascending order. The ASC keyword is optional since ascending
order is the default.
Executing this query will return all employees sorted by their last names
in alphabetical order.

34
Q24. Write a query to display employees from the IT department ordered by
their Salary in descending order.
Example: SELECT * FROM Employees WHERE Department = 'IT'
ORDER BY Salary DESC;

Explanation:
 SELECT *: This selects all columns from the Employees table.
 FROM Employees: This specifies the table from which to retrieve the
data.
 WHERE Department = 'IT': This condition filters the results to include
only employees in the IT department.
 ORDER BY Salary DESC: This orders the results by the Salary column
in descending order, so that the highest salaries appear first.
Executing this query will return all employees from the IT department,
sorted by their salary from highest to lowest.

35
Q25. Write an SQL query to create a table named Students with the following
structure:
StudentID (INT, Primary Key)
FirstName (VARCHAR)
LastName (VARCHAR)
Age (INT)
Gender (VARCHAR)
Grade (DECIMAL)
EnrollmentDate (DATE)

Explanation:
 CREATE TABLE Students: This command starts the creation of a new
table named Students.
 StudentID INT PRIMARY KEY: This defines the StudentID column as
an integer and sets it as the primary key, ensuring that each value in this
column is unique.
 FirstName VARCHAR(50): This defines the FirstName column as a
variable character string with a maximum length of 50 characters.

36
  LastName VARCHAR(50): This defines the LastName column similarly
to FirstName, allowing up to 50 characters.
 Age INT: This defines the Age column as an integer.
 Gender VARCHAR(10): This defines the Gender column as a variable
character string with a maximum length of 10 characters.
 Grade DECIMAL(5, 2): This defines the Grade column as a decimal
number that can have up to 5 digits in total, with 2 digits after the decimal
point.
 EnrollmentDate DATE: This defines the EnrollmentDate column as a
date type.
Executing this query will create the Students table with the specified structure.

Q26. Insert 10 records in the Student table.

Explanation:
Each INSERT INTO statement adds a new record to the Students table with
values for the specified columns:
 StudentID: Unique identifier for each student.

37
  FirstName: The first name of the student.
 LastName: The last name of the student.
 Age: The age of the student.
 Gender: The gender of the student.
 Grade: The student's grade as a decimal value.
 EnrollmentDate: The date the student enrolled, formatted as YYYY-
MM-DD.
Executing these commands will insert 10 records into the Students table.

Q27. Use various conditions to retrieve information from students table.

To retrieve information from the students2 table using various conditions, you
can use SQL queries with WHERE, ORDER BY, GROUP BY, and other
clauses. Here are some examples:
1. Retrieve Students by Age
SELECT * FROM students2 WHERE Age > 20;

This query retrieves all students older than 20.

2. Retrieve Students by Gender

SELECT * FROM students2 WHERE Gender = 'Female';

38
This query retrieves all female students.
3. Retrieve Students with a Specific Grade Range
SELECT * FROM students2 WHERE Grade BETWEEN 3.0 AND 4.0;

This query retrieves students whose grades are between 3.0 and 4.0.

4. Retrieve Students with a Grade Higher than a Specific Value

SELECT * FROM students2 WHERE Grade > 3.5;

39
This query retrieves students with grades higher than 3.5.

5. Retrieve Students Ordered by Enrollment Date

SELECT * FROM students2 ORDER BY EnrollmentDate ASC;

This query retrieves all students ordered by their enrollment date in ascending
order.

40
6. Count Students by Gender
SELECT Gender, COUNT(*) AS NumberOfStudents FROM students2 GROUP
BY Gender;

This query counts the number of students for each gender.

7. Retrieve Students with Specific First Name

SELECT * FROM students2 WHERE FirstName = 'John';

This query retrieves all students with the first name "John."

41
8. Retrieve Students with Specific Last Name
SELECT * FROM students2 WHERE LastName LIKE 'S%';

This query retrieves all students whose last name starts with "S."

Q28. Write a query to increase the grade of all female students by 5%.

Explanation
 UPDATE students2: Specifies the table you want to update.
 SET Grade = Grade * 1.05: Increases the current grade by 5%.
Multiplying by 1.05 effectively raises the grade by 5%.
 WHERE Gender = 'Female': Ensures that only female students are
affected by this update.

42
Q29. Write a query to delete the record of the student with student id=3

Explanation
 DELETE FROM students2: Specifies the table from which you want to
delete a record.
 WHERE StudentID = 3: Conditions the deletion to only affect the
record where StudentID is 3.
Run this command in your SQL environment, and it will remove the student
record with StudentID = 3

Q30. Write a query to add a new column Email (VARCHAR) to the Students
table.

43
Explanation
 ALTER TABLE students2: Specifies the table that you want to modify.
 ADD Email VARCHAR(255): Adds a new column named Email with a
data type of VARCHAR and a maximum length of 255 characters.
Run this command in your SQL environment to add the Email column to the
students2 table. Let me know if you need any further assistance!

Q31. Write a query to group students by Gender and calculate the average grade
for each gender.

44
Q32. Discuss ER Modelling.
= ER (Entity-Relationship) modeling is a conceptual framework used to
describe the structure of a database. It helps to visually represent the
relationships between data entities (objects) and their attributes in a database.
ER modeling is an essential part of the database design process and is
commonly used to develop relational databases.

Key Concepts in ER Modeling:

1. Entities:
o An entity is a real-world object or concept that can be distinctly
identified. Entities can be things like "Student," "Employee,"
"Product," or "Order."
o In ER diagrams, entities are usually represented by rectangles.
o Example: In a student database, Student and Course would be
entities.
2. Attributes:
o Attributes are the properties or characteristics of an entity. They
describe the entity and hold the data associated with it.

45
 o In ER diagrams, attributes are typically represented by ellipses
connected to the entity.
o Example: The Student entity may have attributes such as
StudentID, FirstName, LastName, Age, and Email.
3. Primary Key:
o A primary key is an attribute or set of attributes that uniquely
identifies each record in an entity. It ensures that no two records
have the same value for this attribute.
o Example: StudentID can be a primary key in the Student entity,
ensuring each student has a unique identifier.
4. Relationships:
o A relationship represents an association between two or more
entities. It shows how entities are related to one another.
o In ER diagrams, relationships are represented by diamonds, and
lines are used to connect entities to relationships.
o Example: A Student can be enrolled in many Courses, and each
Course can have many Students. This represents a many-to-many
relationship.
5. Cardinality:
o Cardinality defines the number of instances of one entity that can
be associated with instances of another entity in a relationship. The
most common cardinality types are:
 One-to-One (1:1): An entity in one set can be related to only
one entity in another set.
 One-to-Many : An entity in one set can be related to
multiple entities in another set.
 Many-to-Many: Entities in both sets can be related to
multiple entities in the other set.

46
 o Example: In a Student and Course relationship, one student can be
enrolled in many courses , and many students can enroll in one
course (N:1), making it a many-to-many relationship.
6. Weak Entity:
o A weak entity is an entity that cannot be uniquely identified by its
own attributes and relies on another entity (called the "owner" or
"strong" entity) for its identification. A weak entity typically has a
partial key and a total participation relationship with its owner
entity.
o Example: In a university system, Dependent could be a weak entity
if it's used to represent dependents of employees, where the
identification of a dependent relies on the Employee entity.
7. Relationship Degree:
o The degree of a relationship refers to the number of entities
involved in the relationship. The most common degrees are:
 Unary Relationship: A relationship involving only one
entity type (e.g., an employee supervises another employee).
 Binary Relationship: A relationship involving two entities
(e.g., Student and Course).
 Ternary Relationship: A relationship involving three
entities (e.g., a project that involves multiple Employees and
Departments).

ER Diagram (ERD):
An ER diagram is a graphical representation of an ER model that visualizes the
entities, attributes, relationships, and cardinalities. It is a key tool in database
design for planning and illustrating the structure of a database before
implementation.

47
Example of ER Modeling:
Let’s consider a university system as an example to illustrate an ER diagram:
 Entities:
o Student: Attributes include StudentID (Primary Key), FirstName,
LastName, Age, Email.
o Course: Attributes include CourseID (Primary Key), CourseName,
Credits.
o Instructor: Attributes include InstructorID (Primary Key), Name,
Department.
 Relationships:
o Enrollment: Represents the many-to-many relationship between
Student and Course.
o Teaches: Represents the one-to-many relationship between
Instructor and Course.

Steps in ER Modeling:
1. Identify Entities: Identify the main objects (entities) in the system.
2. Determine Relationships: Determine how these entities are related to
each other.
3. Define Attributes: Identify the key attributes for each entity and define
the primary key.
4. Specify Cardinality: Define the cardinality of relationships.
5. Draw the ER Diagram: Represent the entities, relationships, and
attributes visually.

48
Advantages of ER Modeling:
 Clear Structure: It provides a clear, high-level view of the data and
relationships.
 Simplicity: ER models are easy to understand and communicate, making
them suitable for non-technical stakeholders.
 Data Integrity: ER models help ensure data integrity by defining
relationships and constraints.
 Flexibility: ER diagrams can be easily modified during the design
process.

Conclusion:
ER modeling is a fundamental step in designing a database system, as it lays out
the blueprint for how data is structured and how entities interact with one
another. It forms the foundation for creating a well-organized, efficient, and
scalable relational database.

Q32. SYMBOLS OF ER MODEL-

ERD entity symbols

Entities are objects or concepts that represent important data. Entities are
typically nouns such as product, customer, location, or promotion. There are
three types of entities commonly used in entity relationship diagrams.

49
Entity Symbol Name Description

These shapes are independent from other

entities, and are often called parent
Strong entities, since they will often have weak
entity entities that depend on them. They will
also have a primary key, distinguishing
each occurrence of the entity.

Weak entities depend on some other

entity type. They don't have primary keys,
Weak entity
and have no meaning in the diagram
without their parent entity.

Associative entities relate the instances of

Associative several entity types. They also contain
entity attributes specific to the relationship
between those entity instances.

ERD relationship symbols

Within entity-relationship diagrams, relationships are used to document the
interaction between two entities. Relationships are usually verbs such as assign,
associate, or track and provide useful information that could not be discerned
with just the entity types.

50
Relationship Symbol Name Description

Relationships are associations between

Relationship
or among entities.

Weak Weak Relationships are connections

relationship between a weak entity and its owner.

ERD attribute symbols

ERD attributes are characteristics of the entity that help users to better
understand the database. Attributes are included to include details of the various
entities that are highlighted in a conceptual ER diagram.

51
Attribute Symbol Name Description

Attributes are characteristics of an

Attribute entity, a many-to-many relationship,
or a one-to-one relationship.

Multivalued Multivalued attributes are those that

attribute are can take on more than one value.

Derived attributes are attributes whose

Derived
value can be calculated from related
attribute
attribute values.

Relationships are associations

Relationship
between or among entities.

52
Conceptual Data Models establish a broad view of what should be included in
the model set. Conceptual ERDs can be used as the foundation for logical data
models. They may also be used to form commonality relationships between ER
models as a basis for data model integration. All of the symbols shown Above
are found in the Entity Relationship model.

Q34. ER Diagram of banking system.

DATE OF
BIRTH

NO.

CUSTOMER
AGE

53
ER diagram is known as Entity-Relationship diagram. It is used to analyze to
structure of the Database. It shows relationships between entities and their
attributes. An ER model provides a means of communication.
ER diagram of Bank has the following description :

 Bank have Customer.

 Banks are identified by a name, code, address of main office.
 Banks have branches.
 Branches are identified by a branch_no., branch_name, address.
 Customers are identified by name, cust-id, phone number, address.
 Customer can have one or more accounts.
 Accounts are identified by account_no., acc_type, balance.
 Customer can avail loans.
 Loans are identified by loan_id, loan_type and amount.
 Account and loans are related to bank’s branch.

This bank ER diagram illustrates key information about bank, including entities
such as branches, customers, accounts, and loans. It allows us to understand the
relationships between entities.
Entities and their Attributes are :

 Bank Entity : Attributes of Bank Entity are Bank Name, Code and
Address.
Code is Primary Key for Bank Entity.
 Customer Entity : Attributes of Customer Entity are Customer_id,
Name, Phone Number and Address.
Customer_id is Primary Key for Customer Entity.
 Branch Entity : Attributes of Branch Entity are Branch_id, Name and
Address.
Branch_id is Primary Key for Branch Entity.

54
  Account Entity : Attributes of Account Entity are Account_number,
Account_Type and Balance.
Account_number is Primary Key for Account Entity.
 Loan Entity : Attributes of Loan Entity are Loan_id, Loan_Type and
Amount.
Loan_id is Primary Key for Loan Entity.
Relationships are :

 Bank has Branches => 1 : N

One Bank can have many Branches but one Branch can not belong to
many Banks, so the relationship between Bank and Branch is one to
many relationship.

 Branch maintain Accounts => 1 : N

One Branch can have many Accounts but one Account can not belong to
many Branches, so the relationship between Branch and Account is one to
many relationship.

 Branch offer Loans => 1 : N

One Branch can have many Loans but one Loan can not belong to many
Branches, so the relationship between Branch and Loan is one to many
relationship.

 Account held by Customers => M : N

One Customer can have more than one Accounts and also One Account
can be held by one or more Customers, so the relationship between
Account and Customers is many to many relationship.

 Loan availed by Customer => M : N

(Assume loan can be jointly held by many Customers).
One Customer can have more than one Loans and also One Loan can be
availed by one or more Customers, so the relationship between Loan and
Customers is many to many relationship.

55
Q35.ER DIAGRAM OF UNIVERSITY.

This ER (Entity-Relationship) diagram represents a university database

structure with relationships between students, lecturers, courses, and subjects.
Here’s a breakdown of its components:
Entities and Attributes:
1. STUDENT
o Attributes:
 STUDENT_ID: Unique identifier for each student.
 STUDENT_NAME: Name of the student.
 AGE: Age of the student.
 HOBBY: Student's hobby.
 DOB: Date of birth.
 ADDRESS: Includes sub-attributes:

56
  STREET: Street name.
 CITY: City of residence.
 STATE: State of residence.
 PIN: Postal code.
2. COURSE
o Attributes:
 COURSE_ID: Unique identifier for each course.
 COURSE_NAME: Name of the course.
3. LECTURER
o Attributes:
 LECTURER_ID: Unique identifier for each lecturer.
 LECTURER_NAME: Name of the lecturer.
4. SUBJECTS
o Attributes:
 SUBJECT_ID: Unique identifier for each subject.
 SUBJECT_NAME: Name of the subject.

Relationships:
1. Attends (between STUDENT and COURSE)
o This is a many-to-many relationship, as each student can attend
multiple courses, and each course can have multiple students.

2. Takes (between STUDENT and SUBJECTS)

o This is a many-to-many relationship, indicating that students can
take multiple subjects, and each subject can be taken by multiple
students.

57
 3. Teaches (between LECTURER and STUDENT)
o This is a one-to-many relationship, meaning one lecturer can teach
multiple students, but each student is taught by only one lecturer in
this context.
4. Teaches (between LECTURER and SUBJECTS)
o This is a one-to-many relationship, where each lecturer can teach
multiple subjects, but each subject is taught by only one lecturer.
5. Has (between COURSE and SUBJECTS)
o This is a one-to-many relationship, indicating that each course
consists of multiple subjects, but each subject belongs to only one
course.
Summary
This ER diagram models a university’s structure by defining entities (students,
courses, lecturers, subjects) and their relationships. It allows the tracking of
which students attend specific courses, what subjects they take, and which
lecturers are assigned to teach them. This structure could be useful for managing
student enrolments, lecturer assignments, and course structures within the
university.

58