Practical File

Information System Management(BBA 307)

BBA-V

Guru Gobind Singh Indraprastha University, Delhi

Batch (2021-2024)

Submitted To Submitted By
Ms. Gargi Charterjee Name: Pujan Mehta
(Assistant Professor) Enroll No: 07313701721
Class: BBA-V(M2)

Institute of Information Technology & Management,

Approved By AICTE
Affiliated to Guru Gobind Singh Indraprastha University, Dwarka,New Delhi
New Delhi – 110058
Sr. No Lab Objective Topic/ Module Details with subtopics Date Signature
on which
delivered
To understand Define Data, Information, Database, DBMS and 17/08
L-1 Basics Concepts RDBMS.
To understand Give steps to Open Microsoft Access. Write steps to 17/08
Basics create a database. What are the various
L-2 terminology of elements/objects of a database? What are the various
MS ACCESS as ways to create tables in database?
Data Base.
To understand Features of SQL. 17/08
L-3 Basics features
of SQL.
To understand Give the steps to create table in design view and 17/08
Basics of datasheet view.
L-4
Creating Table
in MS ACCESS.
To understand Explain Field Name, Data Types, Field Size with its 17/08
L-5 Basics Terms Description.
related to Table.
To understand Give the steps to open an existing database. 17/08
L-6 the steps to open
Data Base.
To Understand Various data types in Database. Create a student 19/08
the construction database with a table STUDENT_DETAILS
process of containing the field name as
Table.
• ID
• FIRST NAME
• LAST NAME
• AGE
• ADDRESS

L-7 Insert 5 records using the design view in MS-

ACCESS.

Create another table using data sheet view

as STUDENT_COURSE with the field name as

• ID
• COURSE CODE
• COURSE NAME.

Enter 5 records.
To Understand Steps to create a database, save the database and
the construction open the database. Create a student database with
process of Data S1 as table containing the field names as SERIAL
L-8 Base and Table NO, ENROLLMENT NO, NAME, AGE and
with inserting
ADDRESS.
the records.
Insert at least 10 records using the design view in
MS ACCESS.
 To Understand Define data type and explain the various data types 19/08
the concept of available in MS-ACCESS. Create a databaseDB1
Data Types & with a table T1 with the following fields as:
construction
process of Data • ST_NO.
Base and Table • ST_ID
with inserting
L-9 the records. • ST_NAME
• ST_ADD
• ST_PHONE NO.
• ST_HOBBIES
In the design view and add at least 10 records in the
table.
To Understand Create a database named EMPLOYEE with the 19/08
the construction following fields as EMPLOYEE NAME,
process of Data EMPLOYEE CODE, EMPLOYEE
L-10 Base and Table DESIGNATION, EMPLOYEE DEPARTMENT,
with inserting DATE OF JOINING and YEAR OF
the Deleting
EXPERIENCE. Delete any two records as well as
records.
fields from the table to show the output.
To Understand Create an EMPOYEE database with the following 19/08
the construction fields as
process of Data • Emp_code
Base and Table • Emp_ssn
with inserting
the records and • Emp_name
implement some • DOB
queries on it. • Emp_design
• Emp_dept
• DOJ
• E_SAL
L-11 • Years of experience

For the above created database set the following

fields properties:
1. Employee names should be in upper case with
field size equal to10.
2. Set the default value of Emp_dept as HR.
3. Date of joining should be set to the current date
by default.
4. E_SAL should be in dollars.
Use Lookup Wizard.
To Understand Create an EMPOYEE database with the following 24/08
the construction fields as:
process of Data
Base and Table • Emp_code
with the use of • Emp_ssn
L-12 Input mask
wizard. • Emp_name
• Emp-dob
For the above created database set the input mask
for the field property for Emp_ssn and Emp_dob
using the input mask wizard.
 To Understand Create a table as STUDENT_DETAILS with the 24/08
the construction following fields
process of Data • S_id
Base and Table • S_name
with inserting
the records and • S_course
implement some • S_marks
queries on it. • S_dob
L-13 Make S_id as the primary key and apply the
following validations:
1. S_name should start with A and end with M.
2. S_course can have only BBA OR MBA as its
values.
3. S_marks, it can be NULL or should be > 50.
4. S_dob should be less than the current date.
Enter at least 10 entries in the table.

Create a local template of “FACULTY” , insert 5 26/08

records and perform the following queries on faculty
template table:

a. Insert five records using “new faculty” tab in

faculty template.
b. Faculty name should be in ascending order.
L-14
c. Department should be in descending order
d. Generate report for: All faculty category and
faculty by department category.

To Understand Create a database named as STUDENT 31/08/2021

the construction DATABASE with a table STUDENT_DETAILS
process of Data having fields as
Base and Table • St_id
with inserting
the records and • St_roll no.
understand the • St_name
steps of making • St-age
Composite keys. • St-dob
• St_course
• St-add
L-15 • St_contact

Add at least 10 records in the table. Answer the

following:
A. What is a primary key? Make a field stu_id as
the primary key in the table. Also write down the
steps for the same.
B. What is a composite key? In the table
STUDENT_DETAILS, make stu_roll
no and stu-dob
as a composite key also write the steps for the same.
 To Understand Create a company database with the following table 04/09/2021
the construction EMP_DETAILS with the fields as:
process of Data • EMP_ID
Base and Table • EMP_NAME
with
establishing • EMP_DESIGNATION
Relationship and And make EMP_ID as the primary key.
enforcing Create another table as DEPT_DETAILS with the
referential fields as:
integrity • DEPT_ID
Constraints. • DEPT_NAME
L-16 • DEPT_LOCATION
Also create a table as PROJECTS with fields as:
• P_ID
• P_NAME
Designate primary key in both the fields.
Establish a relationship between the table
EMP- DETAILS AND
DEPT_DETAILS AS 1:1, 1: M and M:
1. Also enforce referential integrity constraint on it
and
write down the steps for the same.
To Understand Explain in brief about the query object in MS- 07/09/2021
the construction ACCESS 2007. Write steps to execute queries
process of Table using the query wizard. Create an ACCOUNT table
with inserting with the following fields:
the records and
• Acc no
implementation
• Act type
steps using
• Do_opening
Query wizards.
• Transaction_type
• Balance
• Account H_name
L-17(a)
• Transaction ID
Enter 12 records in the table and perform the
following queries on it.
• Display the acc no in ascending order.
• Display the details of account where acc
no is 675.
• Display the details of where acc no is 675
and name is AJAY.
Display the acc no, acc type, account H_name and
balance where balance is greater than 5000.
 To Understand Create a table Student with following fields 09/09/2021
the construction • Enrolment_no
process of Table • S_name
with inserting • Course_id
the records and • Batch
implement some
• Semester
queries on it.
Execute the following queries after entering 10
L-17(b)
records in the table.
• Display the list of Students in batch ‘2017-2020’
• Display the Enrolment_no of fourth semester
students.
• Display the list of students of batch ‘2019-2022’
and course_is 17.
Display the number of students in course_id 44.
To Understand Create the following table CUSTOMER having the
the construction columns, data types.
process of Table Field Name Data Type
with inserting Customer_ID Number
the records and Customer_Name Text
implement some Designation Text
queries on it.
City Text
Country Text
Phone Number
L-18(a) Insert the data into table Customer. And apply the
queries:
• Display customer details having designation Sales
Representative.
• Retrieve customer who is living in Canada.
• Show details of the customer whose name is
Victoria.
Show detail of the customer who lives in Sweden.
Show detail of the customer who is owner.
To Understand Create a table named as STUDENT with the
the construction following fields as:
process of Table • S_no
with inserting • Name
the records and • City
implement some
• Age
queries on it.
• Marks
Execute the following queries after entering 10 records
in the table.
L-18(b) • Display all the details of the given table
• Display the names in alphabetical order
• Display the name along with the marks where
marks are greater than 60
• Display the name of students whose name
starts with letter ‘A’.
• Display the details of students who live in
‘Delhi’.
• Display the details of students who are older
than 10 years.
L-18(c) Create a table employee with the following fields: 30/9/2021
Emp_id
Emp_name
Contact no.
Gender
Salary
Emp_grade
Designation
Address
Execute the following queries on the table created:
1. Display the details of the employees
2. Display the employee id of employee whose
name is supriya.
3. Display the name of the employee whose
contact no. is 98454863011.
4. Display the details of the employee whose
address is D-29, IITM, JANAKPURI.
5. Display the name of the employees whose salary
is greater than 10,000.
6. Update the employee details, set EMP_GRADE
equal to C whose salary is less than 10000.
7. Delete the record of the employee whose gender
is male and designation is manager
8. Display the name, designation, and salary of all
the employees whose names begins with A.
9. Update the employee details; set designation to
assistant manger where designation is trainee.
10. Display the employee id, name, salary and
designation for the employees where
emp_grade is A.

To Understand Create a table EMPLOYEE either the following

the construction fields:
process of Table • Emp_id
with inserting • Emp_name
the records and • Dept_name
implement some • Salary
queries on it.
• Add
• Date of joining
Perform the following queries on it.
1. Delete the records of the employee whose
L-19(a)
Emp_id is 101
2. Delete the record of the employees whose
Dept_name is HR and salary >10000
3. Delete the record of the employee whose name
begins with S or salary >10000
4. Update the record of the employees, set
Dept_name = IT where Dept_name is
PRODUCTION.
Update the record of the employee, set salary to
15000 where Dept_name is IT.
 To Understand 5. Create a table named as
the construction STUDENT_MARKS with the following
process of Table fields as:
with inserting 6. S_no
the records and 7. Name
implement some 8. Marks
queries on it.
9. Execute the following queries after
entering 10 records in the table.
10. Display the S_no of those students
whose names starts with L and ends with A
L-19(b) or marks less than 70
11. Display the name and S_no of those
students who have scored marks equal to 95
12. Display the names and marks of all
those students who have scored marks
between 75 and 90.
13.
 To Understand Create a table named STUDENT_DETAILS with
the construction the following fields:
process of Table • R_no
with inserting • Name
the records and
Construction of • Course name
Form using • Attendance
Design View. • Dob
• Add
L-20 Course will have values either economics or dbms.
Enter 5 records. Create a form for the abovetable in
design view. Add text box and the following
buttons to it:
➢ Go to first record
➢ Go to last record
➢ Go to previous record
➢ Go to next record
Add a label student form to it.
To Understand Discuss All DDL Commands with its syntax.
about Basics of
L-21
DDL
commands.
To Understand Write SQL Queries using Data Definition Commands
about the DDL like CREATE, DROP, TRUNCATE, RENAME,
command ALTER.
L-22
implementation. Create Table named Student with Fields Student id,
Name, Age, Course, Fees.
And apply above mentioned commands on the table.
To Understand Write SQL Queries using Data Definition Commands
about the DDL Create Table named Employee with Fields
command • EMP_ID
implementation • EMP_NAME
L-23 using SQL • EMP_DESIGNATION
Queries.
• EMP_DEPARTMENT
• EMP_CONTACT
And make EMP_ID as the primary key.
.
To Understand Write SQL Queries using Data Definition Commands
about the DDL Create Table named Employee with Fields
command • EMP_ID
implementation • EMP_NAME
L-24 using SQL • EMP_DESIGNATION
Queries.
And make EMP_ID as the primary key. Apply
TRUNCATE, RENAME and ALTER commands.

To Understand Discuss All DML Commands with its syntax.

about Basics of
L-25
DML
commands.
 To Understand Write SQL Queries using Data Manipulation Commands
about the DML like INSERT, UPDATE, DELETE and SELECT.
command Insert record into Table named Student with Fields
implementation Student id,
L-26 using SQL Name,
Queries. Age,
Course,
Fees.
And apply above mentioned commands on the table.
To Understand Write SQL Queries using Data Manipulation Commands
about the DML Insert record into Table named Employee with Fields
command • EMP_ID
implementation • EMP_NAME
using SQL • EMP_DESIGNATION
L-27 Queries.
• EMP_DEPARTMENT
• EMP_CONTACT
And make EMP_ID as the primary key.
And apply above mentioned commands on the table.

To Understand Define All Aggregate function with its work defined in

about Basics of SQL.
Aggregate
L-28 Functions in
SQL.

To Understand Write SQL Queries using Data Manipulation Commands

about the DML Insert record into Table named Employee with Fields
command • EMP_ID
implementation • EMP_NAME
using SQL • EMP_DESIGNATION
Queries.
• EMP_AGE
• EMP_DEPARTMENT
• EMP_SALARY
L-29 And make EMP_ID as the primary key.
1. Write a query to find the salary of a person where
age is <= 26 and salary >= 25000 from Employee
Table.
2. Write a query to find the name of Employee whose
name is like “Ku%”.
3. Write a query to find the customer details using
“IN” and “Between” operator where age can be 25 or
27.
 To Understand Write SQL Queries using SQL Commands
about the DML Create a Table Product with Fields
command • PRO_ID
implementation • PRO_NAME
and Use of • PRO_PRICE
Aggregate
And make PRO_ID as the primary key.
Function using
L-30 SQL Queries.
Insert 10 Records into the Table.

1. Write a query to find the Name of Product where

PRO_PRICE>= 500 from PRODUCT Table.
2. Write a query to implement Aggregate Functions on
field PRICE”.
3. Write a query to COUNT the number of Records in
the Product Table.
To Understand Create an ER Diagram for COLLEGE Management
about the basic system.
L-31
implementation
of ER Diagram.
To Understand Create an ER Diagram for Airline Reservation System.
about the basic
L-32
implementation
of ER Diagram.
To Understand Create an ER Diagram for Employee Management
about the basic System
L-33
implementation
of ER Diagram.
To Understand Create an ER Diagram for Library Management
about the basic system
L-34
implementation
of ER Diagram.
 LAB ASSIGNMENT-1

Q. Define data, information, database, DBMS, RDBMS.

Ans. Data:
Data is information, such as figures and facts, that is used for decision-making or analysis.
Information that can be processed by a computer is known as computer data. Data can be found in
a variety of formats, including text or numbers written on paper, bits and bytes kept in electronic
memory, and facts retained in an individual's memory. Ever since computers were invented,
computer information that is transferred or stored has been referred to as data. Since the data is
kept in binary (zero or one) on the computer, it can be digitally processed, created, saved, and
stored. This makes it possible to transfer data between computers using a variety of media devices
or new connections

Information:
An information collection is beneficial to all parties involved because it aids in decision-making
for the individuals involved. Information can be found in a variety of formats, such as text, photos,
videos, and audio files. It is available via several platforms, including social media, the Internet,
and books. Information is essentially a representation of reality that is used to share knowledge
about the world we live in. Depending on its objective and target audience, it can be objective,
subjective, or even fictional and take on a variety of shapes.

Data that has been transformed into a more comprehensible or helpful form is called information.
It is a collection of data that has been arranged so that people can use it directly, as information
aids in decision-making. By putting together disparate data points into a meaningful format, the
information is produced. For instance, the information on the report card or sheet is the grades that
the students have received and their roll numbers. Pay stubs, schedules, reports, worksheets, bar
charts, invoices, account returns and so forth are examples of additional information formats.

Database:
An ordered collection of data that is simple to manage and access is called a database. To facilitate
finding pertinent information, data can be indexed, arranged into tables, rows, and columns, and
more. Database handlers design a database so that all users can access the data with just one set of
software. The primary function of a database is to manage, retrieve, and store a vast amount of
information. These days, databases are used to manage a large number of dynamic websites that
can be found on the Internet. Consider a model that verifies whether hotel rooms are available.
This is an illustration of a database-driven dynamic website. There are many databases available
like MySQL, Sybase, Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc. Modern
databases are managed by the database management system (DBMS). SQL or Structured Query
Language is used to operate on the data

Pujan Mehta 1 07313701721

 stored in a database. SQL depends on relational algebra and tuple relational calculus. A cylindrical
structure is used to display the image of a database.

DBMS:
The system software used to create and manage databases is called a database management system
(DBMS). End users can create, protect, read, update, and delete data in a database with the help of
a DBMS. The most common kind of data management platform, the database management system
(DBMS) functions essentially as a conduit between users or application programmers and
databases, keeping data easily accessible and consistently organized. Data is managed by the
database management system (DBMS); its logical structure is specified by the database schema;
and data access, locking, and modification are made possible by the database engine. In addition
to providing concurrency, security, and data integrity, these three fundamental components also
support standard data administration practices. Numerous common database administration tasks,
such as change management, security, backup and recovery, and performance monitoring and
tuning, are supported by the DBMS.

RDBMS:
Relational Database Management System is referred to as RDBMS. All contemporary database
systems, including MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access, are built
on top of RDBMS. A database management system (DBMS) that is built on the relational model,
first presented by E. F. Codd in 1970, is known as a relational database management system
(RDBMS). Because cloud-based relational databases such as Cloud SQL, Cloud Spanner, and
Alloy DB provide managed services for infrastructure support, patching, capacity management,
provisioning, and database maintenance, they have grown in popularity. The main benefit of the
relational database model is that it provides an intuitive way to represent data and allows easy
access to related data points. As a result, relational databases are most used by organizations that
need to manage large amounts of structured data, from tracking inventory to processing
transactional data to applicationlogging.

Pujan Mehta 2 07313701721

 LAB ASSIGNMENT-2
Q. Give steps to open Microsoft Access. Write steps to create a database. What are the various
elements or objects of a database? What are the various ways to create tables in database?

Steps are as follows:

1. Go to windows and search for Access 2013

2. Open Access 2013 and click on blank document and rename the database.

3. A new database will be created.

Pujan Mehta 3 07313701721

 4. Go to create tab and we can view four elements- Table, Query, Forms and Report.

Elements of a database:

Tables:

To store your data, you create one table for each type of information that you track. Types of information
might include customer information, products, and order details. To bring the data from multiple tables
together in a query, form, or report, you define relationships between the tables.

Query:

A query can help you find and retrieve the data that meets conditions that you specify — including data
from multiple tables. You can also use a query to update or delete multiple records at the same time and
to perform predefined or custom calculations on your data.

Forms:

You can use a form to easily view, enter, and change data one row at a time. You can also use a form to
perform other actions, such as sending data to another application. Forms typically contain controls that
are linked to underlying fields in tables. When you open a form, Access retrieves the data from one or
more of those tables, and then displays the data in the layout that you chose when you created the form.
You can create a form by using one of the Form commands on the Ribbon, the Form Wizard, or create a
form yourself in Design view.

Report:

You can use a report to quickly analyze your data or to present it a certain way in print or in other
formats. For example, you may send a colleague a report that groups data and calculates totals. Or, you
may create a report with address data formatted for printing mailing labels.

Pujan Mehta 4 07313701721

 Various ways to create tables in database are:
Steps are as follows:

In the database view, a database can be created by filling out the fields with headings and then the
relevant data can be filled under the headings in the cells.
1. Start by clicking Short Text in the new field heading.

2. Rename the field. Then Save the table and rename.

Pujan Mehta 5 07313701721

 3. A Database can also be created and edited using Design View.

4. Rename the ID column and by right clicking toggle off the primary key.

Pujan Mehta 6 07313701721

 5. Under Data Type, select Number for Roll No. field.

6. Save the database. Now under Datasheet View

Pujan Mehta 7 07313701721

 LAB ASSIGNMENT-3

Q. Define and write the features of SQL.

SQL:

Programming languages such as structured query language (SQL) are used to store and handle
data in relational databases. Data is stored in tabular form in relational databases, where
different data attributes and the relationships between the data values are represented by rows
and columns. Information from the database can be stored, updated, removed, searched for, and
retrieved using SQL statements. SQL can also be used to enhance and maintain database
performance. Based on the relational data model, SQL was created in the 1970s. The structured
English query language (SEQUEL) was its original moniker. Later, the word was abbreviated to
SQL.

1. DDL:
A computer language called a data definition language (DDL) is used to create and alter the
structure of database objects within a database. Views, schemas, tables, indexes, and other
database objects are among them. In some contexts, this term is also referred to as "data
description language" because it describes the records and fields found in database tables. A
computer language called a data definition language (DDL) is used to create and alter the
structure of database objects within a database. Views, schemas, tables, indexes, and other
database objects are among them. In some contexts, this term is also referred to as "data
description language" because it describes the fields and records found in database tables.
Commands under DDL are as follows:

CREATE: The CREATE TABLE is a DDL statement which is used to create tables in the
database. The table gets created as soon as the CREATE TABLE script is executed and is ready
to hold the data onwards. The user must have the CREATE TABLE system privilege to create
the table in its own schema.
Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype);

Example: CREATE TABLE customers (id INT, name VARCHAR (50));

ALTER TABLE: ALTER SQL command is a DDL (Data Definition Language) statement.
ALTER is used to update the structure of the table in the database (like add, delete, modify the
attributes of the tables in the database).
Syntax: ALTER TABLE table_name ADD column_name datatype;
Example: ALTER TABLE customers ADD email VARCHAR(100);

Pujan Mehta 8 07313701721

 DROP: DROP is a Data Definition Language (DDL) command which removes the named
elements of the schema like relations, domains or constraints and you can also remove an entire
schema using DROP command.

Syntax: DROP TABLE table_name;

Example: DROP TABLE customers;
TRUNCATE: TRUNCATE is a DDL (Data Definition Language) command and is used to
delete all the rows or tuples from a table. Unlike the DELETE command, the TRUNCATE
command does not contain a WHERE clause. In the TRUNCATE command, the transaction log
for each deleted data page is not recorded.

Syntax: TRUNCATE TABLE table_name;

Example: TRUNCATE TABLE customers;
RENAME: RENAME command is used to change the name of the table or a database object.

Syntax: ALTER TABLE old_table_name RENAME TO new_table_name;

Example: ALTER TABLE employees RENAME TO staff;

2. DQL:
Data query language (DQL) is part of the base grouping of SQL sub-languages. These sub-
languages are mainly categorized into four categories: a data query language (DQL), a data
definition language (DDL), a data control language (DCL), and a data manipulation language
(DML).

SELECT: The SELECT command is used to query or retrieve data from a table in the database.
It is used to retrieve a subset of records from one or more tables.

Syntax: SELECT column1, column2, ...

FROM table_name
WHERE condition;

Example: SELECT first_name, last_name

FROM employees
WHERE hire_date > '2022-01-01';

Pujan Mehta 9 07313701721

 3. DML:
DML is an abbreviation for Data Manipulation Language. Represents a collection of
programming languages explicitly used to make changes to the database, such as: CRUD
operations to create, read, update and delete data. Using INSERT, SELECT, UPDATE, and
DELETE commands.

INSERT: INSERT is another most important data manipulation command in Structured Query
Language, which allows users to insert data in database tables.

Syntax: INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example: INSERT INTO employees (employee_id, first_name, last_name, hire_date)

VALUES (1, 'John', 'Doe', '2023-01-15');
UPDATE: UPDATE is another most important data manipulation command in Structured Query
Language, which allows users to update or modify the existing data in database tables. Here,
'UPDATE', 'SET', and 'WHERE' the SQL keywords, and ‘Table_name’ are is the name of the
table whose values you want to update.

Syntax: UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

Example: UPDATE employees

SET hire_date = '2023-02-01'
WHERE employee_id = 1;
DELETE: Delete is a DML or Data Manipulation Command that is used to delete particular
records from a table. It deletes the complete row from the table and produces the number of
deleted rows as output.

Syntax: DELETE FROM table_name WHERE condition;

Example: DELETE FROM customers WHERE id = 3;

Pujan Mehta 10 07313701721

 LAB ASSIGNMENT-4
Q. Write steps to create a table in design view and datasheet view.
Steps are as follows:
1. After opening a new document click on the Design View under View tab.

2. Rename and save the table.

Pujan Mehta 11 07313701721

 3. Remove the Primary Key from field name and choose Data Types for field after
removing Primary key from S_No

4. Then fields with respective Data Type and then save the table.

Pujan Mehta 12 07313701721

 LAB ASSIGNMENT-5

Q. What do you mean by field name, data type, field size & description?

Field Name:

A field name is a fundamental component of a database table, serving as a label or identifier for
specific data within that table. Field names are crucial for uniquely identifying and referencing
individual data elements. For instance, in a table containing information about customers, you
might have field names like "Customer_ID," "FirstName," and "Last_Name." These names help
you understand and manipulate the data stored in the table.

Data Type:
Data type refers to the nature or kind of data that can be stored in a particular field within a
database. Different data types are available to cater to various types of information. Common
data types include Text, Number, Date/Time, Boolean, and more. The data type determines how
the data is stored, validated, and processed. For example, a "Date of Birth" field would typically
have a data type of "Date/Time" to ensure it only accepts valid dates.

Field Size:
Field size specifies the maximum allowable length or size of data that can be stored in a
particular field within a database table. It ensures that data entered into the field conforms to a
predetermined limit. For example, a "Product Description" field might have a field size of 255
characters, meaning that product descriptions cannot exceed 255 characters in length. Field size
helps manage data integrity by preventing overly long or incorrect entries that could disrupt the
database's structure.

Description:
Description is an optional property that allows you to provide additional information or context
about a field within a table. This description can be used to explain the purpose or usage of a
particular field to database users or other developers. It serves as documentation within the
database, making it easier for individuals who work with the data to understand its significance.
Descriptions can be particularly helpful in large and complex databases, providing clarity and
aiding in data management and collaboration.

Pujan Mehta 13 07313701721

 LAB ASSIGNMENT-6
Q. Write steps to open-hand existing database.
Steps are as follows:
1. Open MS Access

2. Under Recent tabs, click on any existing database.

Pujan Mehta 14 07313701721

 LAB ASSIGNMENT-7
Ques. Create a student database with a table named as- student_details. Also, the database
should contain the following fields: -
1) ID
2) First name
3) Last name
4) Age
5) Address
Insert file records using the datasheet view. Create another table student_course using the
design view with the following fields: -
1) ID
2) Course code
3) Course name

Steps are as follows:

1. Open a blank database and name it as Student_database. Insert the fields and fill the data.

Pujan Mehta 15 07313701721

 2. Go to Create tab and click on table. Then name the new table as Student_course.

3. Go to Design View, add fields and select respective Data Type and then save the table and
then fill in the data in Datasheet View.

Pujan Mehta 16 07313701721

 LAB ASSIGNMENT-8
Q. Create a student_database with S1 as the table name containing the following fields:
1. Enrl_No
2. Name
3. Age
4. DOB
5. Phone_No
6. Address
Insert at least 10 records using Datasheet view.
Steps are as follows:
1. Create a student_database and then go to Design view after saving the table as S1.

2. Add the fields remove primary key from Enrolment No and change the Data Type of DOB to
Date/Time.

Pujan Mehta 17 07313701721

 3. For Phone_No select Number in Data Type and in the bottom under Default Value select
Double and also in case of adding longer Address, one can select Long Text.

4. Now under Datasheet View, add the respective data.

Pujan Mehta 18 07313701721

 LAB ASSIGNMENT-9
Q. Create a database DB1 with a table T1 with the following fields:
1. St_No
2. St_ID
3. St_Name
4. St_Address
5. St_PhoneNo
6. St_Hobbies
In the design view, create 10 records.

Steps are as follows :

1. Create a blank database named as DB1. Create a table named as T1.

Pujan Mehta 19 07313701721

 2. Go to Design View and start by removing primary key and changing Data type of St_No. from Auto
number to Number followed by changing Data Type of St_Id and St_PhoneNo to Number. Then, Change
Data Type of St_Address to Long text for adding more characters.

3. In the Datasheet View add the data in their respective fields.

Pujan Mehta 20 07313701721

 LAB ASSIGNMENT-10
Q. Create a database named ‘Employee’ and create a table named “Employee_Data” in
datasheet view with the following data
1. Employee_id
2. Employee_name
3. SSN_number
4. Employee_designation
5. Employee_department
6. Date_of_joining
7. Salary
8. Years_of_experience
Insert 7 records and delete any 2 records. Also delete 1 field that is SSN_number.

Steps:
1. Create a blank database named as ‘Employee’. Then under Design View change the Data Type
wherever required after saving the table as ‘Employee_Data’.
2. Save it and go to Datasheet View and add 7 records. Start deleting a record by left clicking on the
extreme left cell of the respective field that needs to be deleted.

Pujan Mehta 21 07313701721

 3. Save and go to design view, right click on the field named “SSN_No”, and select “Delete
rows” to remove the field.

4. Save and check the output in the Datasheet View.

Pujan Mehta 22 07313701721

 LAB ASSIGNMENT-11
Q. Create an employee database with the following fields:
1) Emp_Code
2) Emp_SSN
3) Emp_Name
4) Emp_DOB
5) Emp_Desig
6) Emp_Dept

7) Emp_DOJ

8) Emp_Salary

9) Emp_YOE

Enter 10 records. For the above created database, set the following field properties:

1. Employee name should be in uppercase with field size equals to 10.

2. Set the default value of Emp_Dept as HR.
3. Date of joining should be set to the current date by default
4. Emp_Salary should be in Euro
5. Use General wizard

Steps:
1. Create a database named Employee and fill resepective fields and data.

Pujan Mehta 23 07313701721

 2. Click on Emp_name field. Under general wizard, make field size= “10” and format =
“>”

3. Click on Emp_dept and Under general wizard, make Default Value “HR”

4. Click on the data type for the field “Emp_DOJ”. Under general wizard, make default
value = “date ()”

Pujan Mehta 24 07313701721

 5. Click on the data type for the field “EMP_SALARY”. Under general wizard, make format
“currency” and select EURO

6. Save the changes and then enter the data in the datasheet view.

Pujan Mehta 25 07313701721

 LAB ASSIGNMENT-12
Q. Create an employee database
EMP_CODE
EMP_SSN
EMP_NAME
EMP_DOB
For the above created database set the input mask for the field property for EMP_SSN and
EMP_DOB using the input mask wizard

Steps are as follows:

Go to Microsoft access, create a database named “EMPLOYEE”
1. Go to design view and create the table named “Employee_Data” having required fields
under field name with suitable data type. Click on the data type for the field “EMP_SSN”.
Under general wizard, set input mask wizard for EMP_SSN and choose Social Security
Number option, select Next and then Finish.

Pujan Mehta 26 07313701721

 2. Click on the data type for the field “EMP_DOB”. Under general wizard, set input mask
wizard for EMP_DOB, choose between the options.

3. Enter the data in the table and check if the previous commands are executed properly.

Pujan Mehta 27 07313701721

 LAB ASSIGNMENT-13
Q. Create a table “Student_Name” with the fields given below stating 5 records:
• S_id
• S_name
• S_course
• S_marks
• S_dob
Where “s_id” is the primary key and apply the following validations.
1. S_name should start with A and end with M
2. S_course can have only BBA or MBA
3. S_marks can be null or should be greater than 50
4. S_dob should be less than current date

Steps are as folows:

1. In design view for S_name go to validation rule in the field property and write – like “A*M”.

2. Now for S_course write “BBA” or “MBA” under Validation Rule.

Pujan Mehta 28 07313701721

 3. Now for S_marks write NULL or >50.

4. Now for S_DOB write <Date().

5. In case you try to add same S_ID for a different row, an error will pop-up as Primary key
should have unique values.

Pujan Mehta 29 07313701721

 6. Add the data.

7. Error will appear in case of a rule-break. Name should start with A and end with M according
to the Validation Rule set.

8. Similarly, an error will appear in case:

➢ S_Course other than BBA or MBA
➢ S_marks cannot be NULL or >50.
➢ DOB should be less than current date

Pujan Mehta 30 07313701721

 9. Adding the data keeping in mind all the validation rules that are set, no error message will
appear.

Pujan Mehta 31 07313701721

 LAB ASSIGNMENT-14
Q. Create a local template of “FACULTY”, insert 5 records and perform the following
queries on faculty template table:
• Insert 5 records using “new faculty” tab in faculty template.
• Faculty name should be in ascending order.
• Department should be in descending order.
• Generate report for: All faculty category and faculty by department category.

Steps are as follows:

1. Open MS Access and select Faculty template.

2. Enter the records using “New faculty” and arrange faculty name in ascending order

Pujan Mehta 32 07313701721

3. Arrange faculty department in descending order

4. To find reports generated by all faculty, in reports field select “All faculty”

Pujan Mehta 33 07313701721

 5. To find reports generated by a department, in reports field select “faculty by department”

Pujan Mehta 34 07313701721

 LAB ASSIGNMENT-15
Q. Create a Database named Student with a table Student_details with the following fields:
1. Stu_id
2. Stu_roll
3. Stu_DOB
4. Stu_name
5. Stu_age
6. Stu_course
7. Stu_address
8. Stu_contact
Add at least 7 records in the table.
Answer the following:
1. What is a primary key? Make a field Stu_id in the table as the primary key. Also write
the steps for the same.
2. What is a composite key? In the table make Stu_roll and Stu_DOB as the composite key
and write the steps for the same.
Primary key:
In Microsoft Access, a primary key is a fundamental concept in database design and management.
It serves several important purposes and comes with certain features and characteristics:
1. Uniqueness: The primary key must contain unique values within the table. This means that no
two rows in the table can have the same value for the primary key column(s). This uniqueness
ensures that each record in the table can be uniquely identified.

2. Indexing: A primary key is automatically indexed by Microsoft Access. This indexing helps
improve the performance of database queries, as it allows for faster data retrieval when
searching for specific records based on the primary key.

3. Non-Null: The primary key columns must have non-null (i.e., not empty) values for every
record. This ensures that there are no gaps or missing values in the primary key, as every record
must have a unique identifier.

4. Relational Integrity: Primary keys are used to establish relationships between tables in a
relational database. They serve as a reference point for creating foreign keys in other tables,
which, in turn, helps maintain referential integrity, ensuring that data remains consistent and
accurate across related tables.

5. AutoNumber Data Type: In many cases, an AutoNumber data type is used for primary key
columns. An AutoNumber column automatically generates a unique numeric value for each
new record added to the table, simplifying the process of ensuring uniqueness.

Pujan Mehta 35 07313701721

 Steps are as follows:
1. To make Stu_ID as the primary key, right click on the row selector of the field in the design
view.

Composite key:
In Microsoft Access, a composite key is a key that consists of two or more columns in a database
table. Unlike a single-column primary key, which is made up of a single field, a composite key is
created by combining multiple fields to create a unique identifier for each record in the table. Here
are the key features and characteristics of a composite key in MS Access:
1. Uniqueness: Just like a single-column primary key, a composite key must ensure the
uniqueness of each combination of values across the selected columns. This means that no two
records in the table can have the same values in all of the composite key columns.
2. Data Integrity: Composite keys help enforce data integrity by preventing the insertion of
duplicate records based on the combination of values in the key columns.
3. Relationships: Composite keys are often used in relational databases to establish relationships
between tables. They can serve as foreign keys in related tables, allowing for the creation of
complex and meaningful relationships.
4. Querying: Composite keys can be used in queries and joins to link related tables together. They
are especially useful when the relationship between tables is based on multiple fields.
5. Flexibility: Composite keys provide flexibility in designing databases where a single column
cannot uniquely identify a record. They are particularly useful in situations where natural keys
(existing data attributes) are not sufficient for ensuring uniqueness.

Pujan Mehta 36 07313701721

 Steps:
1. Select both the fields i.e., Stu_roll and Stu_DOB in the design view and click on the primary
key from the ribbon.

Add 7 records in the fields:

Pujan Mehta 37 07313701721

 LAB ASSIGNMENT-16
Q. Create a Company database with the following fields:
1. Emp_ID- Emp_Name, Emp_Desig
2. Dept_Details- Dept_Name, Dept_Location
3. Projects- Project_ID, Project_Name
Designate primary key in all tables. Establish a relationship between the table Emp_Details
& Dept_Details as 1:1, 1:M & M:1. Also, establish a relationship between the table
Emp_Details & projects as 1:1.
Write down the steps for the same.

Steps are as follows:

1. Create three different tables with the above-mentioned fields under Design view, make sure
to designate primary key to Emp_ID, Dept_ID & Project_Id.

2. Then go to Design tab, Drag Emp_ID and drop it at Dept_ID. Next, inside the pop-up box in
the Related Table drop down select Dept_ID to establish a 1:1 relationship.

3. Select Enforce Referential Integrity and then click on Ok.

Pujan Mehta 38 07313701721

 4. Again, drag Emp_ID and drop it at Projects table. Inside the pop-up box choose Project_ID.

5. To create 1:M and M:1, drag Emp_ID and drop it at Dept_Details table, then under Dept_Details drop
down select Dept_Location. Select Enforce Referential Integrity and then click on Ok.

6. The output is ready.

Pujan Mehta 39 07313701721

 LAB ASSIGNMENT-17(A)
Q. Explain in brief about query object in MS Access. Write steps to execute query using
query wizard. Create an Acc_details table with the following fields: -
• Acc_no
• Acc_type
• Acc_Date_of_opening
• Acc_balance
• Acc_name
• Acc_transactionID
Enter 10 records and perform following queries:-
1. Display Acc_no in ascending order
2. Display the details of account where Acc_no is 675 & name is Ajay
3. Display the details where Acc_no, Acc_type, Acc_name where Acc_balance > 5000.

Steps are as follows:

1. Add the fields in Design View with the respective Data Types.

Pujan Mehta 40 07313701721

 2. Save the table and under Database View add the data then select the entire data. Go to Create
tab and select Query Wizard → Simple Query Wizard then click Next → Next → Finish.

3. The Query will be created. Click on Acc_No, then Go to Sort drop down menu and select Ascending.

4. Under Criteria for [Acc_No] field write “=675” and under [Acc_Name] field write =”Ajay”. Then
under Design tab Run the quer

Pujan Mehta 41 07313701721

 5. Again, click on the Acc_details_Query table and under [Acc_balance] field write “>5000” then Run.

6. We can see the results with the mentioned criteria in the output.

Pujan Mehta 42 07313701721

 LAB ASSIGNMENT-17(B)
Q. Create a table “Student” with the following fields: -
1. Enroll_No

2. S_name
3. Course_id
4. Batch

5. Semester

Execute the following queries after entering 10 records in the table: -

a) Display the list of students in batch 2017-20
b) Display the list Enrol_No of 4th Semester students
c) Display the list of students of Batch 2017-20 and Course_id =17
d) Display the no. of students in Course_id =444

Steps are as follows:

1. Add the fields in Design View with the respective Data Types. Save the table.
➢ Go to Datasheet View and add the data.

Pujan Mehta 43 07313701721

 1st Query
2. Save the table and under Database View add the data then select the entire data.
➢ Go to Create tab and select Query Wizard → Simple Query Wizard.
➢ Click on Next → Next → Finish.

3. Select the table and go to View Tab.

➢ Select SQL view

4. Under the SQL View write the following syntax:

➢ SELECT* (Which will include the entire table data.)
➢ FROM Student i.e table’s name
➢ WHERE Batch = “2017-20”;

Pujan Mehta 44 07313701721

 5. Go to Design tab → Run.
➢ Output will be presented.

6. To get the output where only the S_name is visible from the Batch 2017-20:
➢ Again, select the table and go to Query Wizard → Simple Query →Next →Next→Finish.
➢ SQL View
➢ Write SELECT S_name
➢ FROM Student
➢ WHERE Batch = “2017-20”;

7. Go to Design tab → Run.

➢ Output will be presented.

Pujan Mehta 45 07313701721

 2nd Query
8. Under the SQL View write the following syntax:
➢ SELECT Enroll_No, Batch
➢ FROM Student
➢ WHERE Semester = “4”;

9. Go to Design tab → Run.

➢ Output will be presented.

3rd query
10. Under the SQL View write the following syntax:
➢ SELECT Enroll_No, Batch
➢ FROM Student
➢ WHERE Batch = “2017-20” and Course_id = “17

11. Go to Design tab → Run.

➢ Output will be presented.

Pujan Mehta 46 07313701721

 4th query
12. Under the SQL View write the following syntax:
➢ SELECT Enroll_No, Batch
➢ FROM Student
➢
➢ WHERE Course_id = “444”;

13. Go to Design tab → Run.

➢ Output will be presented.

Pujan Mehta 47 07313701721

 LAB ASSIGNMENT-18(A)
Q. Create a table Customer having the following fields and Data types:
Field Name Data type
Customer_ID Number
Customer_name Short text
Designation Short text
City Short text
Country Short text
Phone Number

Insert the data into the table Customer and apply the queries.
i Display customer details having designation Sales representative.
ii Retrieve customers who is living in Canada.
iii Show details of the customer whose name is Victoria.
iv Show details of the customer who lives in Sweden.
v Show details of the customer who is owner.

Steps are as follows: -

Create a Database named customer & the table named customer

Pujan Mehta 48 07313701721

 1. Display customer details having designation Sales representative by performing following
queries:
➢ SELECT*
➢ FROM Customer
➢ WHERE Designation= “Sales Representative”;

2. To retrieve customers who is living in Canada.

➢ SELECT*
➢ FROM Customer
➢ WHERE Country= “Canada”;

Pujan Mehta 49 07313701721

 3. To show details of the customer whose name is Victoria.
➢ SELECT*
➢ FROM Customer
➢ Where Customer_name= “Victoria”;

4. To Show details of the customer who is owner.

➢ SELECT*
➢ FROM Customer
➢ Where Designation= “Owner”;

Pujan Mehta 50 07313701721

 5. Output can be seen.

Pujan Mehta 51 07313701721

 LAB ASSIGNMENT-18(B)

Ques. Create a table named as STUDENT with the following fields:

1. Serial_no
2. Name
3. City
4. Age
5. Marks
Execute the following queries after entering 10 records in the table.
i Display all the details of the given table
ii Display the names in alphabetical order
iii Display the marks along with the names where the marks are greater than 60
iv Display the name of the students whose name starts with the letter "A"
v Display the details of students who lives in "Delhi"
vi Display the details of students who are older than 10 years

Steps are as follows: -

Create a Database named as Student & a table also named as Student. Then start performing
queries in SQL view.

Pujan Mehta 52 07313701721

 1. To Display all the details of the given table perform the queries:
➢ SELECT *
➢ FROM STUDENT;

2. To display the names in alphabetical order, perform the queries:

➢ SELECT *
➢ FROM STUDENT
➢ ORDER BY STUDENT.[Name];

Pujan Mehta 53 07313701721

 3. To display the marks along with the names where the marks are greater than 60, perform
the queries:
➢ SELECT *
➢ FROM STUDENT
➢ WHERE Marks >60;

4. To Display the name of the students whose name starts with the letter "A"
➢ SELECT *
➢ FROM STUDENT
➢ WHERE Name like "A*";

Pujan Mehta 54 07313701721

 5. To display the details of students who lives in "Delhi", perform the queries:
➢ SELECT *
➢ FROM STUDENT
➢ WHERE City="Delhi";

Pujan Mehta 55 07313701721

 6. To display the details of students who are older than 10 years, perform the queries:
➢ SELECT *
➢ FROM STUDENT
➢ WHERE Age>10;

Pujan Mehta 56 07313701721

 LAB ASSIGNMENT-18(C)
Q. Create a table Employee with the following fields:
Emp_ID, Emp_name, Contact_no, Gender, Salary, Emp_grade, Designation, Address
Execute the following queries on the table created:

1. Display the details of the employees

2. Display the employee ID whose name is Supriya
3. Display the name of the employee whose contact number is 9845486301
4. Display the details of the employee whose address is D-29, IITM Janakpuri
5. Display the name of the employee whose salary is >10000
6. Update the employee details, set the emp_grade equal to C whose salary is less than
10000
7. Delete the record of the employees whose gender is male and designation is manager
8. Display the name, designation and salary of all the employees whose name starts
with A
9. Update the employee details, set designation to assistant manager where designation
is trainee.
10. Display the employee id, name, salary and designation for the employee where grade
is A
Steps are as follows:
1. Go to Design Tab, add the field names and Data Type for each entry. Save the table and in
the Datasheet View add the data.

Pujan Mehta 57 07313701721

 2. Under Create tab go to Query Design and create a query.
➢ Add the table
➢ Select the fields that needs to be shown.
➢ Go to View tab and select SQL view.

1st query
3. Under Query1 write:
➢ SELECT Emp_ID
➢ FROM Employee
➢ WHERE Emp_name = “Supriya”;

4. Go to Design tab → Run → The output will appear

Pujan Mehta 58 07313701721

 2nd query
5. Again make a Query and under SQL View write:
➢ SELECT Emp_name
➢ FROM Employee
➢ WHERE Contact_no = “9845486301”;

6. Go to Design tab → Run → The output will appear

3rd query
7. Again make a Query and under SQL View write:
➢ SELECT*
➢ FROM Employee
➢ WHERE Address = “D-29 IITM”;

Pujan Mehta 59 07313701721

 8. Go to Design tab → Run → The output will appear

4th query
9. Again make a Query and under SQL View write:
➢ SELECT Emp_name
➢ FROM Employee
➢ WHERE Salary >10000;

Pujan Mehta 60 07313701721

 10. Go to Design tab → Run → The output will appear

5th query
11. Again make a Query and under SQL View write:
➢ UPDATE Employee
➢ SET Emp_grade = “C”
➢ WHERE Salary <10000;

12. Go to Design tab → Run → The updated output will appear.

Pujan Mehta 61 07313701721

 LAB ASSIGNMENT-19(A)

Q. Create a table EMPLOYEE with the following fields:

• Emp_id
• Emp_name
• Dept_name
• Salary
• Add
• Date of joining

Perform the following queries on it.

1. Delete the records of the employee whose Emp_id is 101
2. Delete the record of the employees whose Dept_name is HR and salary >10000
3. Delete the record of the employee whose name begins with S or salary >10000
4. Update the record of the employees, set Dept_name = IT where Dept_name is PRODUCTION.
5. Update the record of the employee, set salary to 15000 where Dept_name is IT.

Solution:

• Step 1: First create a Table Name Employee and enter the relevant entries.

• Step 2: For Query 1, “Delete the records of the Employee whose Emp_id is 101”.

• Step 3: On running the query, the output will be -

• Step 4: For Query 2, “Delete the record of the employees whose Dept_name is HR and salary
>10000”.

Pujan Mehta 62 07313701721

 • Step 5: On running the query, the output will be -

• Step 6: For Query 2, “Delete the record of the employee whose name begins with S or salary
>10000”.

• Step 7: On running the query, the output will be -

• Step 8: For Query 2, “Update the record of the employees, set Dept_name = IT where Dept_name
is PRODUCTION”.

• Step 9: On running the query, the output will be -

Pujan Mehta 63 07313701721

 • Step 8: For Query 2, “Update the record of the employee, set salary to 15000 where Dept_name is
IT”.

• Step 9: On running the query, the output will be -

Pujan Mehta 64 07313701721

 LAB ASSIGNMENT 19(B)

Q. Create a table student Marks with the following fields

1. S_NO
2. Name
3. Marks
Enter 10 records in the table and execute the following queries
i Display S No. of those students whose name start with L and ends with A and Marks
<17.
ii Display the name and S.NO of those students who have scored marks-95.
iii Display the names and marks of all the students who have scored marks between 75
and 90

Steps are as follows: -

1. Create a Database named Employee & the table named Employee

Pujan Mehta 65 07313701721

 2. Display S No. of those students whose name start with L and ends with A and Marks <17.

3. Display the name and S.NO of those students who have scored marks-95.

Pujan Mehta 66 07313701721

 4. Display the names and marks of all the students who have scored marks between 75 and 90

Pujan Mehta 67 07313701721

 LAB ASSIGNMENT-20

Create a Table named STUDENT_DETAILS with the following fields:

1. R_NO
2. Name
3. Course_Name
4. Attendance
5. DOB
6. Address

The course will have values either ISM or FMI. Enter 5 records. Create a form for the above
table in design view. Add text box and the following buttons to it:
➢ Go to the first record.
➢ Go to the last record.
➢ Go to previous record.
➢ Go to next record Add a label student form to it.

A-The steps are as follows:

1. Create a Table named STUDENT_DETAILS and enter the relevant records.

2. Then run a Query design to run the query.

Pujan Mehta 68 07313701721

 3. Write a query “Like FMI or ISM”.

4. This will show you the details of all the students having the Course_Name FMI or ISM.

5. Next is to create a Form to add Buttons.

6. Then go to the form layout design and click on the button option.

7. Click on the form and then you will get the window of command Button Wizard. Click on the
Go to first record option and click next.

Pujan Mehta 69 07313701721

 8. Select Go to First option and then click on Next Button

9. Finally click on the finish to add the button.

10. See, the Go to First Record button has been added.

11. Similarly, you can add other buttons as well.

Pujan Mehta 70 07313701721

 LAB ASSIGNMENT- 21

Q- Discuss all the DML command with their syntax

A- DML resembles simple English language and enhances efficient user interaction with the
system. The functional capability of DML is organized in manipulation commands like SELECT,
UPDATE, INSERT INTO and DELETE FROM, as described below:
• SELECT: This command is used to retrieve rows from a table. The syntax is SELECT
[column name(s)] from [table name] where [conditions]. SELECT is the most widely used
DML command in SQL.
• UPDATE: This command modifies data of one or more records. An update command
syntax is UPDATE [table name] SET [column name = value] where [condition].
• INSERT: This command adds one or more records to a database table. The insert command
syntax is INSERT INTO [table name] [column(s)] VALUES [value(s)].
• DELETE: This command removes one or more records from a table according to specified
conditions. Delete command syntax is DELETE FROM [table name] where [condition].
The structured query language (SQL) commands deal with the manipulation of data present in the
database that belongs to the DML or Data Manipulation Language. This includes most of the SQL
statements.
There are four main types of DML in the Database Management System (DBMS) which are as
follows −
SELECT − Retrieve data from the database.
INSERT − Insert data into a table.
UPDATE − Update existing data within a table.
DELETE − Delete records from a database table.

Syntax of SELECT DML command

SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM Name_of_table;
Here, column_Name_1, column_Name_2, ….., column_Name_N are the names of those
columns whose data we want to retrieve from the table.
If we want to retrieve the data from all the columns of the table, we have to use the following
SELECT command:
SELECT * FROM table_name;

Pujan Mehta 71 07313701721

 Examples of SELECT Command
Example 1: This example shows all the values of every column from the table.
SELECT * FROM Student;
This SQL statement displays the following values of the student table:

Student_ID Student_Name Student_Marks

BCA1001 Abhay 85

BCA1002 Anuj 75

BCA1003 Bheem 60

BCA1004 Ram 79

BCA1005 Sumit 80

Example 2: This example shows all the values of a specific column from the table.
SELECT Emp_Id, Emp_Salary FROM Employee;
This SELECT statement displays all the values of Emp_Salary and Emp_Id column
of Employee table:

Emp_Id Emp_Salary

201 25000

202 45000

203 30000

204 29000

205 40000

Pujan Mehta 72 07313701721

 Syntax of INSERT Command
INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , column_Name3 , ...... colu
mn_NameN ) VALUES (value_1, value_2, value_3, ......value_N ) ;
Examples of INSERT Command
Example 1: This example describes how to insert the record in the database table.
Let's take the following student table, which consists of only 2 records of the student.

Stu_Id Stu_Name Stu_Marks Stu_Age

101 Ramesh 92 20

201 Jatin 83 19

Suppose, you want to insert a new record into the student table. For this, you have to write the
following DML INSERT command:
INSERT INTO Student (Stu_id, Stu_Name, Stu_Marks, Stu_Age) VALUES (104, Anmol, 89,
19);

Syntax of UPDATE Command

UPDATE Table_name SET [column_name1= value_1, ......., column_nameN = value_N] WHE
RE CONDITION;
Here, 'UPDATE', 'SET', and 'WHERE' are the SQL keywords, and 'Table_name' is the name of
the table whose values you want to update.
Examples of the UPDATE command
Example 1: This example describes how to update the value of a single field.
Let's take a Product table consisting of the following records:

Product_Id Product_Name Product_Price Product_Quantity

P101 Chips 20 20

P102 Chocolates 60 40

Pujan Mehta 73 07313701721

 P103 Maggi 75 5

P201 Biscuits 80 20

P203 Namkeen 40 50

Suppose, you want to update the Product_Price of the product whose Product_Id is P102. To
do this, you have to write the following DML UPDATE command:
UPDATE Product SET Product_Price = 80 WHERE Product_Id = 'P1

Syntax of DELETE Command

DELETE FROM Table_Name WHERE condition;
Examples of DELETE Command
Example 1: This example describes how to delete a single record from the table.
Let's take a Product table consisting of the following records:

Product_Id Product_Name Product_Price Product_Quantity

P101 Chips 20 20

P102 Chocolates 60 40

P103 Maggi 75 5

P201 Biscuits 80 20

P203 Namkeen 40 50

Suppose, you want to delete that product from the Product table whose Product_Id is P203.
To do this, you have to write the following DML DELETE command:
DELETE FROM Product WHERE Product_Id = 'P202' ;

Pujan Mehta 74 07313701721

 LAB ASSIGNMENT-22

Q. Queries using Data Manipulation Commands like INSERT, UPDATE, DELETE and
DDL Commands Like CREATE, ALTER and DROP.
Insert record into Table named Student with Fields

• Student_ID
• Name
• Age
• Course Fees.
And appl y above mentioned commands on the table.
SOLUTION: Create a Database and go to SQL view and write the below mention
Query to create the Table.

Click on Run to see the results.

Write Insert Query to enter the records.

Similarly, insert other records as well and click on the run.

Write Alter Query to add the Column to the table.

• Clickon Run tosee theresults.

Pujan Mehta 75 07313701721

 • Write Alter Query to Drop the Column to the table.

• Click on Run to see the results.

• Write the Update Query to update the fees of the student where course= “BBA”

• Click on run to see the results.

• Delete a Record where Student Id=104.

• Click on run to get the results.

• Use DROP command to Delete the table.

Pujan Mehta 76 07313701721

 LAB ASSIGNMENT-23

Question: Write SQL Queries using Data Definition Commands Create Table named Employee
with Fields
• EMP_ID
• EMP_NAME
• EMP_DESIGNATION
• EMP_DEPARTMENT
• EMP_CONTACT
And make EMP_ID as the primary key.

Answer:

SQL Query

CREATE TABLE Employee (EMP_ID NUMBER PRIMARY KEY, EMP_NAME TEXT,

EMP_DESIGNATION TEXT, EMP_DEPARTMENT TEXT, EMP_CONTACT TEXT)

Steps:

Step 1: Open MS-Access and click on ‘Create’ on ribbon

Pujan Mehta 77 07313701721

 Step 2: Then choose option ‘Query Design’ in Queries column.

Step 3: Right Click on ‘Query1’ and choose option ‘SQL View’.

Step 4: In SQL View, type Query for creating table and for making EMP_ID a primary key,
write PRIMARY KEY in Query after Data type of EMP_ID.

Step 5: Then click on ‘Run’ in ‘Results’ column of ‘Query Tools’.

Pujan Mehta 78 07313701721

 LAB ASSIGNMENT-24

Question: Write SQL Queries using Data Definition Commands. Create Table named Employee
with Fields
• EMP_ID
• EMP_NAME
• EMP_DESIGNATION
And make EMP_ID as the primary key. Apply TRUNCATE, RENAME and ALTER commands.

Answer:

SQL Queries
1. CREATE TABLE Employee (EMP_ID NUMBER PRIMARY KEY, EMP_NAME
TEXT, EMP_DESIGNATION TEXT)

1. TRUNCATE TABLE Employee

1. ALTER TABLE Employee RENAME EMP_DESIGNATION TO EMP_POST

1. ALTER TABLE Employee ADD COLUMN EMP_ADDRESS TEXT

Pujan Mehta 79 07313701721

Steps:

Step 1: Open MS-Access and click on ‘Create’ on ribbon.

Step 2: Then choose option ‘Query Design’ in Queries column.

Step 3: Right Click on ‘Query1’ and choose option ‘SQL View’.

Pujan Mehta 80 07313701721

Step 4: In SQL View, type Queries one by one.

Step 5: Then click on ‘Run’ in ‘Results’ column of ‘Query Tools’.

Pujan Mehta 81 07313701721

 LAB ASSIGNMENT-25
Q-Discuss all the DML commands with their syntax.
A-DML stands for "Data Manipulation Language." It is a subset of SQL (Structured Query
Language) used for interacting with and manipulating data stored in a relational database
management system (RDBMS). DML statements are responsible for performing operations on the
data within a database, such as retrieving, inserting, updating, and deleting data.
DML Commands are-

1. INSERT

Definition: The INSERT INTO command in SQL is used to insert new records (rows) into a database
table. It has the following syntax:

Syntax: INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

Example: INSERT INTO employees (employee_id, first_name, last_name, hire_date)

VALUES (1, 'John', 'Doe', '2023-10-22');

2. UPDATE

Definition: the "UPDATE" command is used in SQL (Structured Query Language) to modify existing
records in a database table. It allows you to change the values of one or more columns in one or more
rows of a table based on a specified condition.

Syntax: UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

Example: UPDATE Employees

SET Salary = 60000

Pujan Mehta 82 07313701721

WHERE FirstName = 'Jane' AND LastName = 'Smith';

3. DELETE
Definition: The DELETE statement in SQL is used to remove one or more rows from a table based on
a specified condition. It is a Data Manipulation Language (DML) command and is used to modify data
in a database.
Syntax: DELETE FROM table_name
WHERE condition;
Example: DELETE FROM Employees
WHERE EmployeeID = 2;

Pujan Mehta 83 07313701721

 PRACTICAL-26
Q- Write SQL Queries using Data Manipulation Commands like INSERT, UPDATE, DELETE
and SELECT. Insert record into Table named Student with Fields
• Student id,
• Name,
• Age,
• Course,
• Fees.
And apply above mentioned commands on the table.
Solution:
TABLE

1. INSERT INTO STUDENT VALUES (106, "F", 16, "BCA", 150000)

Pujan Mehta 84 07313701721

2. UPDATE STUDENT SET NAME="G" WHERE NAME="F"

3. DELETE FROM STUDENT WHERE COURSE="BCA"

4. SELECT * FROM STUDENT WHERE NAME="A"

Pujan Mehta 85 07313701721

STEPS:
1. Create a table named as STUDENT and insert five records.
2. Go to create tab and choose QUERY DESIGN option.

3. Right click and choose SQL view option.

4. Type queries one by one.

5. In query tools, click on RUN option to see the output.

Pujan Mehta 86 07313701721

 PRACTICAL-27

Question: Write SQL Queries using Data Manipulation Commands. Insert record into Table
named Employee with Fields:
• EMP_ID
• EMP_NAME
• EMP_DESIGNATION
• EMP_DEPARTMENT
• EMP_CONTACT
And make EMP_ID as the primary key.
And apply above mentioned commands on the table.

Answer:

Employee Table with Fields

SQL Queries:

1. INSERT INTO Employee VALUES (101, "A", “CEO”, "MGMT", 9863258747)

Pujan Mehta 87 07313701721

 2. UPDATE Employee SET EMP_NAME="D" WHERE EMP_NAME="B"

3. DELETE FROM Employee WHERE EMP_NAME="C"

4. SELECT * FROM Employee WHERE EMP_NAME="A"

Pujan Mehta 88 07313701721

 PRACTICAL-28

Q. Define all the Aggregate Functions with their work defined in the SQL.
Solution: Aggregate functions are used to perform calculations on sets of values and return a single
value as a result. These functions operate on a group of rows and produce a single value for that
group. Here are some common aggregate functions in SQL and their brief descriptions:
1. COUNT FUNCTION: COUNT () is used to count the number of rows in a result set that meets a
specified condition. It can be used with the asterisk (*) to count all rows or with a specific column
to count non-null values in that column.

Syntax: SELECT COUNT (*) FROM employees

2. SUM FUNCTION: SUM () calculates the sum of all values in a numeric column.

Syntax: SELECT SUM (salary) FROM employees

3. AVG FUNCTION: AVG () computes the average (mean) of values in a numeric column.

Syntax: SELECT AVG (salary) FROM employees

4. MAX FUNCTION: MAX () returns the maximum value from a column.

Syntax: SELECT MAX (salary) FROM employees

5. STDDEV FUNCTION: STDEV () is used to calculate the standard deviation of a set of numeric
values within a specified column. The standard deviation is a statistical measure of the amount of
variation or dispersion in a dataset. It provides insight into how spread out the values are and how
much they deviate from the mean (average).

Syntax: SELECT STDDEV (salary) FROM employees

6. VARIANCE FUNCTION: The variance is a statistical measure that quantifies how data points in
a dataset differ from the mean (average) value. It is calculated as the average of the squared
differences between each data point and the mean.

Syntax: SELECT VARIANCE (salary) FROM employees

Pujan Mehta 89 07313701721

 PRACTICAL-29
Q- Write SQL Queries using Data Manipulation Commands and Insert record into Table
named Employee with Fields.

• EMP_ID
• EMP_NAME
• EMP_DESIGNATION
• EMP_AGE
• EMP_DEPARTMENT
• EMP_SALARY
And make EMP_ID as the primary key.

1. Write a query to find the salary of a person where age is <= 26 and salary >= 25000 from
Employee Table.
2. Write a query to find the name of Employee whose name is like “Ku%”.
3. Write a query to find the customer details using “IN” and “Between” operator where age
can be 25 or 27.
Solution:

• Step 1: First create a Table Name Student_Marks and enter the relevant entries.

Pujan Mehta 90 07313701721

 • Step2: Then Go to Create and Click Query Wizard.

• Select “Simple Query Wizard” and Press OK.

• Click on Double Angular Brackets to transfer fields, Then Click on Next.

• Then select on open the query to open the information tab, then click on finish.

Pujan Mehta 91 07313701721

 • Then finally, go to the SQL View.

• Then finally you will see this window on your screen.

• Step 3: For Query 1, “to find the salary of a person where age is <= 26 and salary >= 25000”,
write the query as shown below and then click on “RUN”.

• Step 4: On running the query, the output will display the age of employees whose <= 26 and
salary >= 25000” .

• Step 5: For Query 2, “to find the name of Employee whose name is like Ku%”, write the query
as shown below and then click on “RUN”.

Pujan Mehta 92 07313701721

 • Step 6: On running the query, the output will the name of Employee whose name is like Ku%”.

• Step 7: For Query 3, “to find the customer details using “IN” and “Between” operator where
age can be 25 or 27”. write the query as shown below and then click on “RUN”.

• Step 8: On running the query, the output will the details of employee whose age can be 25 or
27.

Pujan Mehta 93 07313701721

 PRACTICAL-30
Q. Write SQL Queries using SQL Commands Create a Table Product with Fields
• PRO_ID
• PRO_NAME
• PRO_PRICE
And make PRO_ID as the primary key. Insert 7 Records into the Table.

1. Write a query to find the Name of Product where PRO_PRICE>= 500 from PRODUCT
Table.
2. Write a query to implement Aggregate Functions on field PRICE”.
3. Write a query to COUNT the number of Records in the Product Table.
SOLUTION: -
• Create a table with fields mentioned above in question, using SQL commands.

• Click on run and you can see that the table is created.

• Write the mentioned query to insert the records.

• Click on run and you can see that the record has been entered.

Pujan Mehta 94 07313701721

 • Similarly, add other records as well by using Insert Command.

• Go to design view and make Pro_ID Primary Key.

• Write the mentioned query to get the names of those products whose price>=500.

• Click on run and you can see the results.

• Write the below mentioned query to Sum the Price of the products.

Pujan Mehta 95 07313701721

 • Click on run and you can see the results.

• Write the below mentioned query to count the no. of records.

• Click on run and you can see the results.

Pujan Mehta 96 07313701721

 PRACTICAL-31

Q. Create an ER diagram for a College Management System.

Ques: Create an ER diagram for college management system.

Creating a complete ER (Entity-Relationship) diagram for a college management system involves
various entities and their relationships.
STEPS:
Create tables with the entity names and enter fields and records.

Pujan Mehta 97 07313701721

In the database tools tab, go to relations column.

In the dialogue box, add tables one by one.

Then in the relationship tools, choose edit relationships option.

Pujan Mehta 98 07313701721

In the dialogue box click on Create new.

A new dialogue box will open, in that box enter the table names and column names, between which we
want to establish relationships and click on ok.

Below is a simplified example that includes essential entities in a college management system:
Entities:
1. Student:
• Attributes: Student_ID (Primary Key), Name, Contact, DOB.

2. Course:
• Attributes: Course_ID (Primary Key), Course Name.

3. Faculty:
• Attributes: Faculty_ID (Primary Key), Name, Subject.

4. Department:
• Attributes: Department_ID (Primary Key), Department Name.

Pujan Mehta 99 07313701721

 ❖ Relationships and Cardinality

1. Student to Faculty Relationship (M to m):

• Cardinality Notation: Many Students (M) can be Taught by (M) Faculty.

• Explanation: A student can have multiple faculty advisors (for different courses or
academic matters), and a faculty member can advise multiple students.

2. Student to Course Relationship (M to 1):

• Cardinality Notation: Many Students (M) can enrol in Single (1) Course.
• Explanation: A student can enrol in single course, but a course can have multiple students
enrolled in it.

3. Faculty to Department Relationship (M to 1):

• Cardinality Notation: Many Faculty members (M) can be associated with (1) Department.
• Explanation: Many faculty members can belong to a single department, but each faculty
member is associated with at most one department.

Pujan Mehta 100 07313701721

 PRACTICAL-32
Q. Create an ER diagram for an Airline Reservation System.

The EntityRelationship (ER) diagram for an airline reservation system is a visual representation
of the data model that describes the structure of the system and the relationships between different
entities. Here's a brief explanation of the key elements:

1. Entities:

• Passenger Entity: Represents individuals who use the reservation system. It contains
attributes like PassengerID (unique identifier), Name, and ContactNumber.
• Flight Entity: Represents a scheduled flight. It has attributes like FlightID (unique
identifier), Airline, Departure Time, Arrival Time, Desto
• Airport Entity- It represents where flight land.
• Booking Office- Represents Place where the ticket for the fight has been booked.

Pujan Mehta 101 07313701721

 2. Attributes:

• Passenger Attributes: PassengerID, Name, Contact, Age..

• Flight Attributes: Flight_no, Airline, Departure_Time, Arrival_Time, Destination.
• Booking Office- Office_Id, Booking_No, Office_Name.
• Airport- Name, Code, City.

3. Relationships and Cardinalities:

• Passenger-Office Relationship (Many to 1): A passenger can have multiple

reservations, but each reservation is associated with one passenger.
• Booking Office-Flight Relationship (Many to 1): Each Flight can have multiple or
many booking offices.
• Flight-Airport Relationship (Many to 1) - Many Flights can land on a single airport.

4. Primary keys

• Primary Keys: Unique identifiers for each entity (e.g., PassengerID, Office_ID,
Flight_no, Booking_No, Booking_no., Code).

Pujan Mehta 102 07313701721

 PRACTICAL-33
Q. Create an ER diagram for an Employee Management System.

Above is an Entity-Relationship Diagram (ERD) for an Employee Management System with four
entities: Employee, Department, Role, and Project.
1. Employee Entity (Attributes):
• EmployeeID (Primary Key)
• Name
• Contact
• Address
2. Department Entity (Attributes):
• Department_ID (Primary Key)
• Department_Name

3. Roles Entity (Attributes):

• Role_Code (Primary Key)

• Role_Type

4. Project Entity (Attributes):

• Project ID (Primary Key)

• Start Date
• End Date

Pujan Mehta 103 07313701721

 ❖ Relationships And Cardinality
1. Employee - Department Relationship:
One-to-Many relationship from Department to Employee.
Each department can have many employees, but each employee belongs to only one department.
Cardinality: (1:M) - One department to Many employees.

2. Employee - Role Relationship:

Many-to-Many relationship from Role to Employee.
Each role can be assigned to many employees and each employee can have multiple roles..
Cardinality: (M:M) - Many employees to Many role.

3. Employee - Project Relationship:

Many-to-Many relationship between Employee and Project.
An employee can be assigned to multiple projects, and a project can have multiple employees.
Introduce a junction table (e.g., Employee Project Assignment) to represent this relationship.

Cardinality: (M:M) - Many employees to Many projects.

4. Department - Project Relationship:

One-to-Many relationship from Department to Project.
Each department can be associated with many projects, but each project belongs to only
one department.
Cardinality: (1:M) - One department to Many projects.

Pujan Mehta 10 07313701721

104
 PRACTICAL-34
Q-Create an ER Diagram for Library Management system

Pujan Mehta 11 07313701721

 Above is an Entity-Relationship Diagram (ERD) for a Library Management System withfour
entities:
1. Member Entity (Attributes):
• MemberID (Primary Key)
• Name
2. Login Entity (Attributes):
• Login_ID (Primary Key)
• Login_Password

3. Roles Entity (Attributes):

• Book_ID(Primary Key)
• Book_Name
• Book_Author

Pujan Mehta 12 07313701721