GURU GOBIND SINGH INDRAPRASTHA UNIVERSITY

MAHARAJA SURAJMAL INSTITUTE

Information System Management Lab

Subject Code-BCOM-307
 

SUBMITTED TO- SUBMITTED BY-

DR. ANAMIKA RANA NAME:SHUBHAM SEHGAL
ENROLLMENT NO:01721288819

B.COM(H) VTH SEM

Page 1 of 119
 INDEX
S.NO ASSIGNMENT DAT SIGNATUR
. E E
1. What do you mean by ‘Data’, ‘Information’, ‘DBMS, 19-
RDBMS and ‘Database’? 08-
2021
2. Write steps to Open Microsoft Access. Write steps to 19-
create a database. What are the various elements/objects 08-
of a database? What are the various ways to create tables 2021
in database?
3. What are the features of SQL? 26-
08-
2021
4. Write steps to create table in design view and datasheet 26-
view. 08-
2021
5. What do you mean by Field Name, Data type, Field size 27-
and Description? 08-
2021
6. Write steps to open an existing database. 27-
08-
2021
7. What are the various data types in Database (One-line 31-
description of each with example)? Create a student 08-
database with a table STUDENT_DETAILS containing 2021
the field name as:
• ID
• FIRST NAME
• LAST NAME
• AGE
• ADDRESS
Insert 5 records using the design view in MS-ACCESS.
Create another table using datasheet view as
STUDENT_COURSE with the field name as:
• ID
• COURSE CODE
• COURSE NAME
8. Write steps to create a database, save the database and 31-
open the database. Create a student database with S1 as 08-
table containing the field names as SERIAL NO, 2021
ENROLLMENT NO, NAME, AGE and ADDRESS.
Insert at least 10 records using the design view in MS
ACCESS.
9. Define data type and explain the various data types 31-
available in MS-ACCESS. Create a database DB1 with a 08-
table T1 with the following fields as: 2021
• ST_NO.
• ST_ID
• ST_NAME

Page 2 of 119
 • ST_ADD
• ST_PHONE NO.
• ST_HOBBIES
In the design view and add at least 10 records in the table.
10. Create a database named EMPLOYEE with the following 03-
fields as EMPLOYEE NAME, EMPLOYEE CODE, 09-
EMPLOYEE DESIGNATION, EMPLOYEE 2021
DEPARTMENT, DATE OF JOINING and YEAR OF
EXPERIENCE. Insert 10 records in table and perform the
following operations:
• Delete any two records
• ADD new field EMP_GRADE
• Rename the field EMPLOYEE DESIGNAMTIOON to
EMP_DESIGN 
• Remove field EMP_GRADE
11. Create an EMPOYEE database with the following fields 03-
as: 09-
• Emp_code 2021
• Emp_ssn
• Emp_name
• DOB
• Emp_design
• Emp_dept
• DOJ
• E_SAL
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 Euro.
5. Set a validation rule for Emp_code (Code must be less
than 50)
Use Lookup Wizard
12. Create an EMPOYEE database with the following fields 07-
as: 09-
• Emp_code 2021
• Emp_ssn
• Emp_name
• Emp-dob
• Emp_Phone
For the above created database set the input mask for the
field property for Emp_ssn(2 letters and 3 digits) and
Emp_dob(mm-dd-yyyy) using the input mask wizard and
emp_phone (10 digits only)
13. Create a database named as STUDENT DATABASE. 09-
With a table STUDENT_DETAILS with the given fields 09-

Page 3 of 119
 as: 2021
• St_roll no.
• St_name
• St_age
• St_dob
• St_course
• St_add
• 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 st_dob as a
composite key also write the steps for the same.
14. Create a table as STUDENT_DETAILS with the 10-
following fields: 09-
• S_roll 2021
• S_name
• S_course
• S_marks
• S_dob
Make S_roll as the primary key.
Enter at least 10 entries in the table.
Apply following validations rules:
1. Calculate age under age column.
2. S_name should start with A and end with M.
3. S_course can have only BBA OR MBA as its values.
4. S_marks, it can be NULL or should be > 50.
5. S_dob should be less than the current date.
6. S_marks must be less than 100.
7. Length of S_name cannot exceed 15 characters.
8. Input mask for dob must be dd/mm/yyyy.
9. S_course must allow only 3 upper characters.
10. Marks allowed only numbers of 3 digits.
15. Create a company database with the following table 16-
EMP_DETAILS with the fields as: 09-
• EMP_ID 2021
• EMP_NAME
• EMP_DESIGNATION
And make EMP_ID as the primary key.
Create another table as DEPT_DETAILS with the fields
as:
•DEPT_ID
• DEPT_NAME
• DEPT_LOCATION.
Also create a table as PROJECTS with fields as:
• P_ID
• P_NAME
Designate primary key in both the fields.

Page 4 of 119
 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.
16. Explain in brief about the query object in MS-ACCESS. 23-
Write steps to execute queries using the query wizard. 09-
Create an ACCOUNT table with the following fields: 2021
• Acc_no (Validation of 10 digits only)
• Act_type(Saving/Current)
• Date_of_opening (date should not exceed current date)
• Balance (allow only digits)
• Acct_Holder_name(Allow only alphabets for maximum
size 20)
• Transaction_ID (Max. length =7 with first 3 characters
and 4 digits allowed) 
Enter 12 records in the table and perform the following
queries on it.
1. Display the acc no in ascending order.
2. Display all records with coloumns Acc_no, Act_type
and Balance.
3. Display the details of account where acc no is 675.
4. Display all records where balance is greater than 5000.
5. Display all records having act_type=Savings.
6. Display records whose account holder name start with
A.
7. Display the details of where acc no is 675 and name is
ARUN.
8. Display the acc no, acc type, account H_name and
balance where balance is greater than 5000.
9. Display Records open after 1-Jan-21.
10. Display records of current account with balance less
than 5000.
17. Create a table named as STUDENT with the following 30-
fields as: 09-
• S_no 2021
• Name
• City
• Age
Execute the following queries after entering 10 records in
the table.
• 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 the
letter ‘A’.
• Display the details of students who live in ‘Delhi’.
• Display the details of students who are older than 10
years.
18. Create a table named as STUDENT_MARKS with the 01-

Page 5 of 119
 following fields as: 10-
• S_no (101 to 110) 2021
• Name
• Marks
Execute the following queries after entering 10 records in
the table.
1. Display the S_no of those students whose names starts
with L and ends with A or marks less than 70
2. Display the name and S_no of those students who have
scored marks equal to 95
3. Display the names and marks of all those students who
have scored marks between 75 and 90.
4. Display students whose S_no is 103,107,110.
5. Display records having second character is A in their
name field.
6. Display records end with N in Name field.
7. Display records ending with EN in name field.
8. Display Records except 102,105,108.
9. Sort table in ascending order of marks.
10. Display records having ANA anywhere in their name.
19. Create a table Student with following fields 12-
• Enrolment_no 10-
• S_name 2021
• Course_id
• Batch
• Semester
Execute the following queries after entering 10 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 ‘2017-2020’ and
course_is 17.
• Display the number of students in course_id 44.
20. Create table course with following fields 12-
• Course_id 10-
• Course_name 2021
• Duration
Execute the following queries after entering 10 records in
the table.
• Create a relationship of Course table with Student table
which is created in previous exercise.
• Display all students enrolled in BBA course.
• Display name of students enrolled in 2-year course.
• Display details of students of BBA course of batch
‘2017-2020’.
21. Create the following table CUSTOMER having the 21-
columns, data types. 10-
  Field Name                                    Data Type 2021
  Customer_ID                                 Number
  Customer_Name                            Text

Page 6 of 119
   Designation                                    Text
  City                                                Text
  Country                                          Text
  Phone                                             Number
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.
22. Create a table EMPLOYEE either the following fields: 21-
• Emp_id 10-
• Emp_name 2021
• 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.
23. Create a table employee with the following fields: 22-
• Emp_id, 10-
• Emp_name, 2021
• 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
MSI, JANAKPURI.
5. Display the name of the employees whose salary is
greater than 10,000.
6. Update the employee details, set EMP_GRADE equal

Page 7 of 119
 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 manager where designation is trainee.
10. Display the employee id, name, salary and designation
for the employees where emp_grade is A.
24-28 28-
10-
2021

Enter 10 records as given in Assignment uploaded on

google classroom.
1. Retrieve a list of MANAGERS.
2. Find out salary of both MILLER and SMITH.
3. Find out the names and salaries of all employees
earning more than 1000 per month.
4. Display the names and salaries of all employees
except JAMES
5. List the name and salary of employees who can earn
more than 1500 and are in department 10 or 30. Label
the columns Employee and Monthly Salary
respectively.
6. List the name and salary for all employees whose
salary is not in the range of 1500 and 2850.
7. Display the name, job, and salary of all the employees
whose job is CLERK or ANALYST and their salary
is not equal to 1000, 3000, or 5000.
8. Display the name, salary and commission for all
employees whose commission amount is greater than
their salary increased by 10%.
9. Display the name of all employees who have two Ls
in their name and are in department 30 or their
manager is 7782.
10. Find out the details of employees whose names begin
with ‘S’.
11. Find out the names of all employees that have ‘A’
anywhere in their name.
12. Find out the names of all employees that have ‘L’ as
their third character in their name.
13. Find out the names of the employees whose name
begin with ‘A’ or ‘M’.

Page 8 of 119
 14. List all the employees whose commission is NULL.
15. List employee number, employee name, total salary
(i.e. salary + commission). (Note: Manipulate the
NULL values accordingly.)
16. List the name of the employee and designation of the
employee, who does not report to anybody.
17. List employee name and yearly salary and arrange the
output on the basis of yearly salary in descending
order.
18. Retrieve the names of departments in ascending order
and their employees in descending order.
19. Select the name, job, salary, and department number
of all employees except SALESMAN from
department number 30.
20. List different jobs with no duplicates.
21. Count the total number of employees.
22. Print the total employees and average salary of each
department.
23. Select the minimum and maximum salary from EMP
table.
24. List the minimum and maximum salaries of each
department.
25. List all departments in which more than 3 employees
are working.
29-30 Create table customer with fields: CID, Lname, Fname, 11-
city, Pin, State and Insert 10 records.  11-
Perform the following query on table:  2021
1. Display the contents
2. Display CID and Fname 
3. Display Fname, Lname and state
4. Display all records where state is Delhi
5. Display fname, lname concatenated with state 
6. Display the records whose pin is not entered
7. Display the states to which the customer belongs
8. Select records of Delhi having name Rajiv 
9. Display records Delhi or Karnataka 
10. Display details of Customer with CID=98 
11. Display details of Customer except CID=98 
12. Use Alias Name to display CID 
13. Retrieve all rows where CID is between 98 and 100 
14. Display those rows where state name begins with ‘D’
15. Retrieve all rows where first name contains the word
‘RAJ’ • 
16. Retrieve all rows where name field contains the word
‘RAJESH’ 
17. Retrieve all rows where city is Delhi, Karnataka or
Bangalore
18. Rename the table customer to cost 
19. Delete all those rows of customers who stay in

Page 9 of 119
 Bangalore
20. Delete those customers who do not have Pin Code
21. Rename column city to address 
22. Delete the customers who do not belong to
Bangalore.

Page 10 of 119
 ASSIGNMENT-1

Q1.What do you mean by ‘Data’, ‘Information’, ‘DBMS, ‘RDBMS’ and ‘Database’?

1. Data
Data can be described as unprocessed facts and figures. Plain collected data as raw facts
cannot help in decision-making. However, data is the raw material that is organized,
structured, and interpreted to create useful information systems.
Data are facts obtained by observation or research and which are recorded. Frequently they
are called raw or basic data and are often records of the day-to-day transactions of the
organisation.
For example- the date, amount and other details of an invoice or cheque, payroll details of
pay, National Insurance and tax for a person, the output for a machine or shift, the number of
vehicles passing a road monitoring point and so on.
Data is defined as 'groups of non-random symbols in the form of text, images, voice
representing quantities, action and objects'.
Data are derived from both external and internal sources and whilst most external data are in
readily usable and concrete forms — for example, bank statements, purchase invoices —
internal activities require appropriate measuring and recording systems to be developed and
maintained so that facts are recorded.
The most popular data collection techniques include −
● Surveys − A questionnaires is prepared to collect the data from the field.
● Secondary data sources or archival data: Data is collected through old records,
magazines, company website etc.
● Objective measures or tests − An experimental test is conducted on the subject and the
data is collected.
● Interviews − Data is collected by the system analyst by following a rigid procedure
and collecting the answers to a set of pre-conceived questions through personal
interviews.

2. Information
Information is the product of data processing. Information is interrelated data.
Information is equivalent to finished goods produced after processing the raw
material. The information has a value in decision making. Information brings clarity
and creates an intelligent human response in the mind.Information is stimuli that have
meaning in some context for its receiver. When information is entered into and stored

Page 11 of 119
 in a computer, it is generally referred to as data. After processing (such as formatting
and printing), output data can again be perceived as information

Characteristics of Good Information-

1. Understandable
2. Relevant
3. Complete
4. Available
5. Reliable
6. Concise
7. Timely
8. Cost-effective

3. Database
In computing, a database is an organized collection of data stored and accessed
electronically from a computer system. Where databases are more complex they are
often developed using formal design and modeling techniques.(DB) are organized,
they have a structure, and all the data they store it fits into that structure. More
specifically, a database is an electronic system that allows data to be stored, easily
accessed, manipulated and updated.

4. DBMS
Database Management System (DBMS) is a software for storing and retrieving users'
data while considering appropriate security measures. It consists of a group of
programs which manipulate the database. The DBMS accepts the request for data from
an application and instructs the operating system to provide the specific data. In large
systems, a DBMS helps users and other third-party software to store and retrieve data.

DBMS allows users to create their own databases as per their requirement. The term
“DBMS” includes the user of the database and other application programs. It provides
an interface between the data and the software application.

Characteristics and properties of Database Management System:

● Provides security and removes redundancy

● Self-describing nature of a database system
● Insulation between programs and data abstraction
● Support of multiple views of the data
● Sharing of data and multiuser transaction processing
● Database Management Software allows entities and relations among them to form
tables.
● It follows the ACID concept ( Atomicity, Consistency, Isolation, and Durability).

Page 12 of 119
 ● DBMS supports multi-user environment that allows users to access and manipulate
data in parallel.

Some popular DBMS system:

● MySQL
● Microsoft Access
● Oracle
● PostgreSQL
● dBASE
● FoxPro
● SQLite
● IBM DB2
● LibreOffice Base
● MariaDB
● Microsoft SQL Server etc.

Application of DBMS

Sector Use of DBMS

Banking For customer information, account

activities, payments, deposits, loans, etc.

Airlines For reservations and schedule information.

Universities For student information, course

registrations, colleges, and grades.

Telecommunication It helps to keep call records, monthly bills,

maintaining balances, etc.

Finance For storing information about stock, sales,

and purchases of financial instruments like
stocks and bonds.

Sales Use for storing customer, product & sales

Page 13 of 119
 information.

Manufacturing It is used for the management of supply

chain and for tracking production of items.
Inventories status in warehouses.

HR Management For information about employees, salaries,

payroll, deduction, generation of pay
checks, etc.

5. RDBMS

A relational database is a digital database based on the relational model of data, as

proposed by E. F. Codd in 1970.A software system used to maintain relational
databases is a relational database management system (RDBMS). Many relational
database systems have an option of using the SQL (Structured Query Language) for
querying and maintaining the database. RDBMS is the basis for SQL, and for all
modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and
Microsoft Access.

Page 14 of 119
Difference between DBMS & RDBMS

No. DBMS RDBMS

1) DBMS applications store data as RDBMS applications store data in a tabular

file. form.

2) In DBMS, data is generally stored In RDBMS, the tables have an identifier

in either a hierarchical form or a called primary key and the data values are
navigational form. stored in the form of tables.

3) Normalization is not present in Normalization is present in RDBMS.

DBMS.

4) DBMS does not apply any security RDBMS defines the integrity constraint for
with regards to data manipulation. the purpose of ACID (Atomocity,
Consistency, Isolation and Durability)
property.

5) DBMS uses file system to store in RDBMS, data values are stored in the
data, so there will be no relation form of tables, so a relationship between
between the tables. these data values will be stored in the form
of a table as well.

Page 15 of 119
 6) DBMS has to provide some RDBMS system supports a tabular structure
uniform methods to access the of the data and a relationship between them
stored information. to access the stored information.

7) DBMS does not support RDBMS supports distributed database.

distributed database.

8) DBMS is meant to be for small RDBMS is designed to handle large amount

organization and deal with small of data. it supports multiple users.
data. it supports single user.

9) Examples of DBMS are file Example of RDBMS are mysql, postgre, sql
systems, xml etc. server, oracle etc.

ASSINGMENT-2

Q1.Write steps to Open Microsoft Access. Write steps to create a database. What are
the various elements/objects of a database? What are the various ways to create tables
in a database?

Page 16 of 119
Steps to open Ms-Access

STEP 1-Click on Windows

STEP2- Click on Ms-Access

Steps to create a database

STEP1- Open Ms-Access and select blank database

STEP2- Name the file and choose the path

Page 17 of 119
STEP3- Select Create

Elements/Objects of a Database

1. Tables
A table is the primary unit of physical storage for data in a database. When a user accesses
the database, a table is usually referenced for the desired data. Multiple tables might comprise
a database, therefore a relationship might exist between tables. Because tables store data, a
table requires physical storage on the host computer for the database. Tables might also have
constraints attached to them, which control the data allowed to be entered into the table. An
entity from the business model is eventually converted into a database table.

Page 18 of 119
When you create a new table, Access asks you to define fields which is also known as
column headings.

★ Each field must have a unique name, and data type.

★ Tables contain fields or columns that store different kinds of data, such as a name or
an address, and records or rows that collect all the information about a particular
instance of the subject, such as all the information about a customer or employee etc.
★ You can define a primary key, one or more fields that have a unique value for each
record, and one or more indexes on each table to help retrieve your data more quickly.

Types of Tables

➢ Data Tables store most of the data found in a database.

➢ Join Tables are tables used to create a relationship between two tables that would
otherwise be unrelated.
➢ Subset Tables contain a subset of data from a data table.
➢ Validation Tables often referred to as code tables, are used to validate data
entered into other database tables.

A database table is similar in appearance to a spreadsheet, in that data is stored in rows and
columns.

ROWS

A row is a series of data placed out horizontally in a table . It is a horizontal arrangement of

the objects, words, numbers, and data. In Row, data objects are arranged face-to-face with

Page 19 of 119
lying next to each other on the straight line. In DBMS, rows are known as records that
contain fields.In the database, the information like name, gender, names, are placed in the
rows.

COLUMNS

Columns are mostly placed one after another in the continuous sequence. In a table, columns
are mostly separated from each other by lines, which help to enhance readability and
attractiveness. In DBMS, columns are called fields which contain the collection of
characters.The column contains information about someone who you are mentioning in the
rows.

2.Data types

A data type is a classification of data which tells the compiler or interpreter how the
programmer intends to use the data. Most programming languages support various types of
data, including integer, real, character or string, and BooleanData types are the building
blocks of databases. A field’s data type not only influences other important characteristics of
that field, such as field size, but also how the field is used throughout the database, such as in
objects, calculations, expressions, and so forth. Using the right data type is a key to success.

Data Type Meaning

Short text (formerly known as “Text”) Alphanumeric data (names, titles,phone

number etc.)

Long Text (formerly known as “Memo”) Large amounts of alphanumeric data:

sentences and paragraphs

Page 20 of 119
Number Numeric data used in mathematical
calculations.
Range- -2^31 to 2^31-1

Large Number Numeric data. with greater range for

calculation than the Number data type
Range- -2^63 to 2^63-1

Date/Time Dates and times.

Min Value- 100-01-01 00:00:00
Max Value- 9999-12-31 23:59:59:.999

Date/Time Extended Dates and times.

Min Value- 0001-01-01 00:00:00
Max Value-9999-12-31 23:59:59.9999999

Currency Monetary data, stored with 4 decimal places

of precision.

Auto Number Unique value generated by Access for each

new record.

Yes/No Boolean (true/false) data; Access stores the

numeric value zero (0) for false, and -1 for
true.

OLE Object Pictures, graphs, or other ActiveX objects

from another Windows-based application.

Hyperlink A link address to a document or file on the

Internet, on an intranet, on a local area
network (LAN), or on your local computer

Attachment You can attach files such as pictures,

documents, spreadsheets, or charts; each
Attachment field can contain an unlimited
number of attachments per record, up to the
storage limit of the size of a database file.

Calculated You can create an expression that uses data

from one or more fields. You can designate
different result data types from the
expression.

Lookup wizard he Lookup Wizard entry in the Data Type

column in Design view is not actually a data
type. When you choose this entry, a wizard
starts to help you define either a simple or
complex lookup field. A simple lookup field
uses the contents of another table or a value
list to validate the contents of a single value

Page 21 of 119
 per row. A complex lookup field allows you
to store multiple values of the same data
type in each row.

3.Forms

Form is an object in a desktop database designed primarily for data input or display or for
control of application execution. Forms allow you to create a user interface in which you can
enter and edit your data. Forms often contain command buttons and other controls that
perform various tasks. You use forms to customize the presentation of data that your
application extracts from queries or tables.

❖ Forms are used for entering, modifying, and viewing records.

❖ The reason forms are used so often is that they are an easy way to guide people
toward entering data correctly.
❖ When you enter information into a form in Access, the data goes exactly where the
database designer wants it to go in one or more related tables.

4.Queries

Page 22 of 119
An object that provides a custom view of data from one or more tables. Queries are a way of
searching for and compiling data from one or more tables.

➢ Running a query is like asking a detailed question of your database.

➢ When you build a query in Access, you are defining specific search conditions to find
exactly the data you want.
➢ In Access, you can use the graphical query by example facility or you can write
Structured Query Language (SQL) statements to create your queries.
➢ You can define queries to Select, Update, Insert, or Delete data.
➢ You can also define queries that create new tables from data in one or more existing
table

5. Report

➔ Report is an object in desktop databases designed for formatting, calculating, printing,

and summarizing selected data.
➔ You can view a report on your screen before you print it.
➔ If forms are for input purposes, then reports are for output.
➔ Anything you plan to print deserves a report, whether it is a list of names and
addresses, a financial summary for a period, or a set of mailing labels.
➔ Reports are useful because they allow you to present components of your database in
an easy-to-read format.
➔ You can even customize a report's appearance to make it visually appealing.
➔ Access offers you the ability to create a report from any table or query.

6.Database Schema

Page 23 of 119
The database schema is its structure described in a formal language supported by the database
management system (DBMS). The term "schema" refers to the organization of data as a
blueprint of how the database is constructed (divided into database tables in the case of
relational databases). The formal definition of a database schema is a set of formulas
(sentences) called integrity constraints imposed on a database.[citation needed] These integrity
constraints ensure compatibility between parts of the schema.

7.Keys

The integrity of the information stored in a database is controlled by keys. A key is a column
value in a table that is used to either uniquely identify a row of data in a table, or establish a
relationship with another table. A key is normally correlated with one column in table,
although it might be associated with multiple columns.

Page 24 of 119
8.Relationships

Most databases are divided into many tables, most of which are related to one another. In
most modern databases, such as the relational database, relationships are established through
the use of primary and foreign keys. The purpose of separating data into tables and
establishing table relationships is to reduce data redundancy. The process of reducing data
redundancy in a relational database is called normalization

There are three types of relationships that can exist between two entities.

I. One-to-One Relationship
Such a relationship exists when each record of one table is related to only one record of the
other table.

For example, If there are two entities ‘Person’ (Id, Name, Age, Address)and
‘Passport’(Passport_id, Passport_no). So, each person can have only one passport and each
passport belongs to only one person.

Such a relationship is not very common. However, such a relationship is used for security
purposes. In the above example, we can easily store the passport id in the ‘Person’ table only.

Page 25 of 119
But, we make another table for the ‘Passport’ because Passport number may be sensitive data
and it should be hidden from certain users. So, by making a separate table we provide extra
security that only certain database users can see it.

II. One-to-Many or Many-to-One Relationship

Such a relationship exists when each record of one table can be related to one or more than
one record of the other table. This relationship is the most common relationship found. A
one-to-many relationship can also be said as a many-to-one relationship depending upon the
way we view it.

For example, If there are two entity type ‘Customer’ and ‘Account’ then each ‘Customer’
can have more than one ‘Account’ but each ‘Account’ is held by only one ‘Customer’. In this
example, we can say that each Customer is associated with many Account. So, it is a one-to-
many relationship. But, if we see it the other way i.e many Account is associated with one
Customer then we can say that it is a many-to-one relationship.

III. Many-to-Many Relationship

Such a relationship exists when each record of the first table can be related to one or more
than one record of the second table and a single record of the second table can be related to
one or more than one record of the first table. A many-to-many relationship can be seen as a
two one-to-many relationship which is linked by a 'linking table' or 'associate table'. The
linking table links two tables by having fields which are the primary key of the other two
tables. We can understand this with the following example.

Page 26 of 119
Example: If there are two entity type ‘Customer’ and ‘Product’ then each customer can buy
more than one product and a product can be bought by many different customers.

Now, to understand the concept of the linking table here, we can have the ‘Order’ entity as a
linking table which links the ‘Customer’ and ‘Product’ entity. We can break this many-to-
many relationship in two one-to-many relationships. First, each ‘Customer’ can have many
‘Order’ whereas each ‘Order’ is related to only one ‘Customer’. Second, each ‘Order’ is
related only one Product wheres there can many orders for the same Product.

In the above concept of linking can be understood with the help of taking into consideration
all the attributes of the entities 'Customer', 'Order' and 'Product'. We can see that the primary
key of both 'Customer' and 'Product' entity are included in the linking table i.e 'Order' table.
These key act as foreign keys while referring to the respective table from the 'Order' table.

Page 27 of 119
9.Macros

Macros in Access can be thought of as a simplified programming language which you can
use to add functionality to your database. For example, you can attach a macro to a command
button on a form so that the macro runs whenever the button is clicked. Macros contain
actions that perform tasks, such as opening a report, running a query, or closing the database.
Most database operations that you do manually can be automated by using macros, so they
can be great time-saving devices

10.Modules

Modules, like macros, are objects you can use to add functionality to your database. Whereas
you create macros in Access by choosing from a list of macro actions, you write modules in
the Visual Basic for Applications (VBA) programming language. A module is a collection of
declarations, statements, and procedures that are stored together as a unit. A module can be
either a class module or a standard module. Class modules are attached to forms or reports,
and usually contain procedures that are specific to the form or report they're attached to.
Standard modules contain general procedures that aren't associated with any other object.
Standard modules are listed under Modules in the Navigation Pane, whereas class modules
are not.

Various Ways to Create Tables in Database

There are two ways to create a Table:

1. Design view
2. Datasheet view

Design View

Page 28 of 119
In Design View, the fields are listed vertically. They are listed above and below each other as
opposed to side-by-side.

In Design View, we can see the data type listed next to each field.

The way Design View works is, when you click on a field (in the top frame), the bottom
frame displays the properties for that field. We can then change these properties as required.

Datesheet View

Datasheet View displays the table as a grid. The fields are displayed as columns, and the
records are displayed as rows. The field names are listed as the column headers.

ASSIGNMENT-3

Page 29 of 119
Q1.What are the features of SQL?

SQL stands for Structured Query Language. It is used for storing and managing data in
relational database management system (RDMS). It is a standard language for Relational
Database System. It enables a user to create, read, update and delete relational databases and
tables. All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL
as their standard database language. SQL allows users to query the database in a number of
ways, using English-like statements.

Characteristics of SQL

All these features of SQL makes it a robust and primary language for many organizations to

manages their large databases.

Page 30 of 119
 01. High Performance

SQL provide high performance programming capability for highly transactional, heavy
workload and high usage database system. SQL programming gives various ways to
describe the data more analytically.

02. High Availability

SQL is compatible with databases like MS Access, Microsoft SQL Server, MySQL, Oracle

Database, SAP HANA, SAP Adaptive Server, etc. All of these relational database management

systems support SQL and it is easy to create an application extension for procedural programming

and various other functions which is additional features thus converting SQL into a powerful tool

03. Scalability and Flexibility

SQL provide Scalability and Flexibility. It is very easy to create new tables and previously created

or not used tables can be dropped or deleted in a database.

04. Robust Transactional Support

With SQL programming can handle large records and manage numerous transactions.

05. High Security

It is very easy to provide permissions on tables, procedures, and views hence SQL give security to

your data.

06. Comprehensive Application Development

SQL is used by many programmers to program apps to access a database. No matter what is the size

of organization, SQL works for every small or large organization.

Page 31 of 119
 07. Management Ease

SQL is used in almost every relational database management system. “Select“, “Create”, “Insert”,

“Drop”, “Update”, and “Delete” are the standard and common SQL commands that helps us to

manage large amount of data from a database very quickly and efficiently.

08. Open Source

SQL is an open-source programming language for building relational database management system

09. Client server execution and remote database access

Client server technology maintains a many to one relationship of clients(many) and server(one). We

have commands in SQL that control how a client application can access the database over a

network.

10. Embedded SQL

SQL provides the feature of embedding host languages such as C, COBOL, Java for query from their

language at runtime

11. Advanced SQ

The current features include OOP ones like recursive queries, decision supporting queries and

also query supporting areas like data mining, spatial data and XML(Xtensible Markup

Language).

ASSIGNMENT-4

Q1.Write steps to create table in design view and datasheet view.

You can switch between design and datasheet view by clicking View in Home Tab

Page 32 of 119
Steps to Create Table in Design View
★ Click th Creat tab
★ Click the Table Design Button.A blank table will appear in Design View.
★ Enter the Fields and Field Properties
Once the table has been configured, be sure to save it. In any case, you will be prompted to
save it when you try to switch to Datasheet View or close the table.

Steps to Create Table in Datasheet View

● Click th Creat tab
● Click the Table Button
● Add a Field
● Name the Field
● Save the Table

Page 33 of 119
Page 34 of 119
 ASSIGNMENT-5

Q1.What do you mean by Field Name, Data type, Field size and Description?

Field Name
Field names are the names you give to the columns in a table. The names should indicate
what data is contained in each column. The names should indicate what data is contained in
each column Field names in the same table must be unique
A field name should be descriptive enough to identify the purpose of the field, without being
overly long to prevent excessive typing. Enter the field name by placing the pointer in the
first row of the Table Design window under the Field Name column.

In order to ensure a valid field name, a field name:

➢ Cannot exceed 64 characters,
➢ Cannot include periods (.), exclamation points (!), accent grave (`), or brackets ([]),
➢ Cannot include spaces,
➢ Cannot include low-order ASCII characters,
➢ Cannot start with a blank space.

Data type

A data type is a classification of data which tells the compiler or interpreter how the
programmer intends to use the data. Most programming languages support various types of
data, including integer, real, character or string, and BooleanData types are the building
blocks of databases. A field’s data type not only influences other important characteristics of
that field, such as field size, but also how the field is used throughout the database, such as in
objects, calculations, expressions, and so forth. Using the right data type is a key to success.

Data Type Meaning

Short text (formerly known as “Text”) Alphanumeric data (names, titles,phone

number etc.)

Long Text (formerly known as “Memo”) Large amounts of alphanumeric data:

sentences and paragraphs

Number Numeric data used in mathematical

calculations.
Range- -2^31 to 2^31-1

Page 35 of 119
Large Number Numeric data. with greater range for
calculation than the Number data type
Range- -2^63 to 2^63-1

Date/Time Dates and times.

Min Value- 100-01-01 00:00:00
Max Value- 9999-12-31 23:59:59:.999

Date/Time Extended Dates and times.

Min Value- 0001-01-01 00:00:00
Max Value-9999-12-31 23:59:59.9999999

Currency Monetary data, stored with 4 decimal places

of precision.

Auto Number Unique value generated by Access for each

new record.

Yes/No Boolean (true/false) data; Access stores the

numeric value zero (0) for false, and -1 for
true.

OLE Object Pictures, graphs, or other ActiveX objects

from another Windows-based application.

Hyperlink A link address to a document or file on the

Internet, on an intranet, on a local area
network (LAN), or on your local computer

Attachment You can attach files such as pictures,

documents, spreadsheets, or charts; each
Attachment field can contain an unlimited
number of attachments per record, up to the
storage limit of the size of a database file.

Calculated You can create an expression that uses data

from one or more fields. You can designate
different result data types from the
expression.

Lookup wizard he Lookup Wizard entry in the Data Type

column in Design view is not actually a data
type. When you choose this entry, a wizard
starts to help you define either a simple or
complex lookup field. A simple lookup field
uses the contents of another table or a value
list to validate the contents of a single value
per row. A complex lookup field allows you
to store multiple values of the same data
type in each row.

Page 36 of 119
 Field size
The Field Size property determines the maximum size of information that can be stored in a
text or number field. For example, if you set the size of a text field to 2, you could enter
“MN” but not “Minnesota.”

Number Field Sizes

Byte Integers from 0 to 255. Very small – not usually a

good choice.

Integer Integers from -32,768 to 32,767. Works for most numbers

without decimals.

Long Integer Integers from -2,147,483,648 to Works for most numbers

2,147,483,647. without decimals.

Single Positive or negative numbers up to 38 Good for large numbers

zeroes and 7 decimal places. with decimals.

Page 37 of 119
 Double Positive or negative numbers up to For really big numbers
308 zeroes and 15 decimal places. with decimals.

Replication ID Long unique codes. Used when you want to

merge copies of a database.

Decimal Positive or negative numbers up to 28 Good for numbers with a

zeroes and 28 decimal places. lot of decimals.

Description

Descriptions provide extra instructions to users about a field, while captions allow you to
create “nicknames” for fields, making their names more meaningful to users.Descriptions
make your database fields easier to fill out and use by providing users with onscreen
instructions and help. Whenever a user selects a field, anything you type in that field’s
Description box will appear in the status bar.
In Design View, click the field’s Description box, and type the description.

When you return to Datasheet View, and click anywhere in that field, you will see the
Description appear in the status bar.

Page 38 of 119
ASSIGNMENT-6

Page 39 of 119
Q1.Write steps to open an existing database?

➔ Open an Access database from Windows Explorer

In Windows Explorer, navigate to the drive or folder containing the Access database file you
want to open and double-click the database.

➔ Open a database from within Access

1. On the getting started page of Access, Click Open Other Files.
2. On the Open area of the Backstage view, click Browse.
3. Click a shortcut in the Open dialog box, or in the Look in box, click the drive or
folder that contains the database that you want.

4. In the folder list, browse to the folder that contains the database.

Page 40 of 119
 ➔ Open an Access database from Taskbar

1. Right click the logo of Ms-Access on taskbar

2. Select the files from Recents

ASSINGMENT-7

Q.What are the various data types in the Database (One line description of

Page 41 of 119
each with example).
A)Create a student database with a table STUDENT_DETAILS
containing the field name as
• ID
• FIRST NAME
• LAST NAME
• AGE
• ADDRESS
Insert 5 records using the design view in MS-ACCESS.
B)Create another table using datasheet view as STUDENT_COURSE with the field
name
as
• ID
• COURSE CODE
• COURSE NAME

DATA TYPE

A data type is a classification of data which tells the compiler or interpreter how the
programmer intends to use the data. Most programming languages support various types of
data, including integer, real, character or string, and BooleanData types are the building
blocks of databases. A field’s data type not only influences other important characteristics of
that field, such as field size, but also how the field is used throughout the database, such as in
objects, calculations, expressions, and so forth. Using the right data type is a key to success.

Data Type Meaning Example

Short text (formerly known Alphanumeric data (names,

as “Text”) titles,phone number etc.)

Long Text (formerly known Large amounts of

as “Memo”) alphanumeric data:
sentences and paragraphs

Number Numeric data used in

mathematical calculations.
Range- -2^31 to 2^31-1

Large Number Numeric data. with greater

range for calculation than
the Number data type
Range- -2^63 to 2^63-1

Date/Time Dates and times.

Min Value- 100-01-01

Page 42 of 119
 00:00:00
Max Value- 9999-12-31
23:59:59:.999

Date/Time Extended Dates and times.

Min Value- 0001-01-01
00:00:00
Max Value-9999-12-31
23:59:59.9999999

Currency Monetary data, stored with 4

decimal places of precision.

Auto Number Unique value generated by

Access for each new record.

Yes/No Boolean (true/false) data;

Access stores the numeric
value zero (0) for false, and -
1 for true.

OLE Object Pictures, graphs, or other

ActiveX objects from
another Windows-based
application.

Hyperlink A link address to a document

or file on the Internet, on an
intranet, on a local area
network (LAN), or on your
local computer

Attachment You can attach files such as

pictures, documents,
spreadsheets, or charts; each
Attachment field can contain
an unlimited number of
attachments per record, up to
the storage limit of the size
of a database file.

Calculated You can create an

expression that uses data
from one or more fields.
You can designate different
result data types from the
expression.

Lookup wizard he Lookup Wizard entry in

the Data Type column in
Design view is not actually a

Page 43 of 119
 data type. When you choose
this entry, a wizard starts to
help you define either a
simple or complex lookup
field. A simple lookup field
uses the contents of another
table or a value list to
validate the contents of a
single value per row. A
complex lookup field allows
you to store multiple values
of the same data type in each
row.

A)Student database with a table STUDENT_DETAILS containing the field name as

• ID
• FIRST NAME
• LAST NAME
• AGE
• ADDRESS

Page 44 of 119
B)Table using datasheet view as STUDENT_COURSE with the field name as
• ID
• COURSE CODE
• COURSE NAME

Page 45 of 119
 ASSINGMENT-8

Q. Write steps to create a database, save the database and open the
database. Create a student database with S1 as table containing the field names as
SERIAL NO, ENROLLMENT NO, NAME, AGE and ADDRESS. Insert at least 10
records using the design view in MS ACCESS.

Steps to create Database

1. Open MS-Access.
2. Select blank database.
3. Enter file name and then click create.

Save the database

1. Open the database or database object.
2. On the File tab, click Save.
3. Or simply just press “Ctrl+S”.

Open the database

1. Choose File | Open.
2. Click to select the filename of the database you want to open.
3. Click Open. Access opens the database.

Student database with S1 as table containing the field names as Serial no., Enrolment no.,
Name, Age and Address.

Page 46 of 119
 ASSINGMENT-9

Q.Define data type and explain the various data types available in MS-ACCESS. Create
a database DB1 with a table T1 with the following fields as:
● S_No.
● ST_ID
● ST_NAME
● ST_ADD
● ST_PHONE NO.
● ST_HOBBIES
In the design view and add at least 10 records in the table.

DATA TYPE

A data type is a classification of data which tells the compiler or interpreter how the
programmer intends to use the data. Most programming languages support various types of
data, including integer, real, character or string, and BooleanData types are the building
blocks of databases. A field’s data type not only influences other important characteristics of
that field, such as field size, but also how the field is used throughout the database, such as in
objects, calculations, expressions, and so forth. Using the right data type is a key to success.

Data Type Meaning

Short text (formerly known as “Text”) Alphanumeric data (names, titles,phone

number etc.)

Long Text (formerly known as “Memo”) Large amounts of alphanumeric data:

sentences and paragraphs

Number Numeric data used in mathematical

calculations.
Range- -2^31 to 2^31-1

Large Number Numeric data. with greater range for

calculation than the Number data type
Range- -2^63 to 2^63-1

Date/Time Dates and times.

Min Value- 100-01-01 00:00:00
Max Value- 9999-12-31 23:59:59:.999

Date/Time Extended Dates and times.

Min Value- 0001-01-01 00:00:00
Max Value-9999-12-31 23:59:59.9999999

Currency Monetary data, stored with 4 decimal places

Page 47 of 119
 of precision.

Auto Number Unique value generated by Access for each

new record.

Yes/No Boolean (true/false) data; Access stores the

numeric value zero (0) for false, and -1 for
true.

OLE Object Pictures, graphs, or other ActiveX objects

from another Windows-based application.

Hyperlink A link address to a document or file on the

Internet, on an intranet, on a local area
network (LAN), or on your local computer

Attachment You can attach files such as pictures,

documents, spreadsheets, or charts; each
Attachment field can contain an unlimited
number of attachments per record, up to the
storage limit of the size of a database file.

Calculated You can create an expression that uses data

from one or more fields. You can designate
different result data types from the
expression.

Lookup wizard he Lookup Wizard entry in the Data Type

column in Design view is not actually a data
type. When you choose this entry, a wizard
starts to help you define either a simple or
complex lookup field. A simple lookup field
uses the contents of another table or a value
list to validate the contents of a single value
per row. A complex lookup field allows you
to store multiple values of the same data
type in each row.

Page 48 of 119
Database D1 with table T1

10 Records-

Page 49 of 119
 ASSIGNMENT-10
Q. Create a database named EMPLOYEE with the following fields as
EMPLOYEE NAME, EMPLOYEE CODE, EMPLOYEE DESIGNATION,
EMPLOYEE DEPARTMENT, DATE OF JOINING and YEAR OF
EXPERIENCE.
Insert 10 records in table and perform the following operations:

• Delete any two records.

Page 50 of 119
• ADD new field EMP_GRADE

• Rename the field EMPLOYEE DESIGNATION to EMP_DESIGN

• Remove field EMP_GRADE

Page 51 of 119
 ASSIGNMENT-11
Q. Create an EMPLOYEE database with the following fields as

o Emp_code
o Emp_ssn
o Emp_name
o DOB
o Emp_design
o Emp_dept
o DOJ
o E_SAL
o 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

Page 52 of 119
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 Euro.

Page 53 of 119
5. Set a validation rule for Emp_code (Code must be less than 50)
Use Lookup Wizard.

Page 54 of 119
 ASSIGNMENT-12

Ques 12. Create an EMPLOYEE database with the following fields as:
➢ Emp_code
➢ Emp_ssn
➢ Emp_name
➢ Emp-dob
➢ Emp_Phone
For the above created database set the input mask for the field property for
Emp_ssn(2 letters and 3 digits) and Emp_dob(mm-dd-yyyy) using the input mask
wizard and emp_phone (10 digits only)

● Input mask for the field property for Emp_ssn(2 letters and 3 digits)

● Emp_dob(mm-dd-yyyy)

Page 55 of 119
● emp_phone (10 digits only)

Page 56 of 119
 ASSINGMENT-13

Q.Create a database named as STUDENT DATABASE. With a table

STUDENT_DETAILS with the given fields as

➢ St_ID
➢ St_name
➢ St_age
➢ St_dob
➢ St_course
➢ St_add
➢ St_contact
➢ St_phone no.
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_id
no and st_dob as a composite key also write the steps for the same.

Primary Key
A primary key is a column or a set of columns in a table whose values uniquely identify a
row in the table. A relational database is designed to enforce the uniqueness of primary keys
by allowing only one row with a given primary key value in a table.

Steps to make column primary key-

Open Design view > Select the field (Std_ID) > Table Design > Tools > Select Primary Key

Page 57 of 119
Composite Key
A composite key is made by the combination of two or more columns in a table
that can be used to uniquely identify each row in the table when the columns are
combined uniqueness of a row is guaranteed, but when it is taken individually it
does not guarantee uniqueness, or it can also be understood as a primary key
made by the combination of two or more attributes to uniquely identify every
row in a table.A composite key can also be made by the combination of more
than one candidate key.A composite key cannot be null

Steps to make column Composite key-

Open Design View > Use the mouse to click both the columns (St_ID, St_DOB)
you want to use for the composite key > Table Design > Tools > Select Primary Key

Page 58 of 119
 ASSINGMENT-14

Q.Create a table as STUDENT_DETAILS with the following fields

➢ S_roll
➢ S_name
➢ S_course
➢ S_marks
➢ S_dob
Make S_roll as the primary key.
Enter at least 10 entries in the table.
Apply following validations rules:
1. S_name should start with A and end with M.

Use Validation rule- Like “A*M”

2. S_course can have only BBA OR MBA as its values.

Page 59 of 119
Use Validation rule- “BBA or MBA”

3. S_marks, it can be NULL or should be > 50.

Use Validation rule- Is Null or >50

Page 60 of 119
4. S_dob should be less than the current date.

Use Validation rule- <Date()

5. S_marks must be less than 100.

Use Validation rule- <100

Page 61 of 119
6. Length of S_name can not exceed 15 characters.

Filed Size should be 15

7. Input mask for dob must be dd/mm/yyyy.

8. S_course must allowed only 3 upper characters.

Page 62 of 119
Use Input Mask >LLL
10. Marks allowed only numbvers of 3 digits.

Use Input Mask >999

Page 63 of 119
 ASSINGMENT-15
Q.Create a company database with the following table
EMP_DETAILS with the fields as:
EMP_ID
EMP_NAME
EMP_DESIGNATION
And make EMP_ID as the primary key.

Create another table as DEPT_DETAILS with the fields as:

DEPT_ID
DEPT_NAME
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.

Page 64 of 119
• 1:1

• 1:M

• M:1

Page 65 of 119
 ASSIGNMENT-16

Q.Explain in brief about the query object in MS-ACCESS. Write steps to

execute queries using the query wizard. Create an ACCOUNT table with the following
fields:
• Acc_no (Validation of 10 digits only)
• Act_type(Saving/Current)
• Date_of_opening (date should not exceed current date)
• Balance (allow only digits)
• Acct_Holder_name(Allow only alphabets for maximum size 20 )
• Transaction_ID (Max. length =7 with first 3 characters and 4 digits allowed)
Enter 12 records in the table and perform the following queries on it.

1. Display the acc no in ascending order.

Page 66 of 119
2. Display all records with coloumns Acc_no, Act_type and Balance.

3. Display the details of account where acc no is 675.

4. Display all records where balance is greater than 5000.

Page 67 of 119
5. Display all records having act_type=Savings.

6. Display records whose account holder name start with A.

7. Display the details of where acc no is 675 and name is ARUN.

Page 68 of 119
8. Display the acc no, acc type, account H_name and balance where balance is
greater than 5000.

9. Display Records open after 1-Jan-21.

10. Display records of current account with balance less than 5000.

Page 69 of 119
Page 70 of 119
 ASSIGNMENT-17
Q.Create a table named as STUDENT with the following fields as:
• S_no
• Name
• City
• Age
Execute the following queries after entering 10 records in the table.
• Display all the details of the given table

• Display the names in alphabetical order

Page 71 of 119
• Display the name along with the marks where marks are greater than 60

• Display the name of students whose name starts with the letter ‘A’.

Page 72 of 119
• Display the details of students who live in ‘Delhi’.

• Display the details of students who are older than 10 years.

Page 73 of 119
 ASSIGNMENT-18

Q.Create a table named as STUDENT_MARKS with the following fields as:

• S_no (101 to 110)
• Name
• Marks

Execute the following queries after entering 10 records in the table.

1. Display the S_no of those students whose names starts with L and ends with A or
marks less than 70

2. Display the name and S_no of those students who have scored marks equal to 95

Page 74 of 119
3. Display the names and marks of all those students who have scored marks between
75 and 90.

4. Display students whose S_no is 103,107,110.

5. Display records having second character is A in their name field.

Page 75 of 119
6. Display records end with N in Name field.

7. Display records ending with EN in name field.

8. Display Records except 102,105,108.

Page 76 of 119
9. Sort table in ascending order of marks.

10. Display records having ANA anywhere in their name.

ASSIGNMENT-19
Page 77 of 119
Q.Create a table Student with following fields
• Enrolment_no
• S_name
• Course_id
• Batch
• Semester
Execute the following queries after entering 10 records in the table.

• Display the list of Students in batch ‘2017-2020’

• Display the Enrolment_no of fourth semester students.

Page 78 of 119
• Display the list of students of batch ‘2017-2020’ and course_is 17.

• Display the Enrolment number of students in course_id 44.

Page 79 of 119
 ASSIGNMENT-20

Q.Create table course with following fields

• Course_id
• Course_name
• Duration

Execute the following queries after entering 10 records in the table.

• Create a relationship of Course table with Student table which is created in

previous exercise.

• Display all students enrolled in BBA course.

Page 80 of 119
• Display name of students enrolled in 2 year course.

• Display details of students of BBA course of batch ‘2017-2020’.

Page 81 of 119
Page 82 of 119
 Assignment – 21

Q. Create the following table CUSTOMER having the columns, data types.
  Field Name                                    Data Type
  Customer_ID                                 Number
  Customer_Name                            Text
  Designation                                    Text
  City                                                Text
  Country                                          Text
  Phone                                             Number
Insert the data into table Customer. And apply the queries:

Page 83 of 119
• 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.

Page 84 of 119
• Show detail of the customer who is owner.

Page 85 of 119
 Assignment- 22

Q. Create a table EMPLOYEE with the following fields:

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

Page 86 of 119
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.

Page 87 of 119
3. Delete the record of the employee whose name begins with S or salary >10000.

Page 88 of 119
4. Update the record of the employees, set Dept_name = IT where Dept_name is
PRODUCTION.

Page 89 of 119
Page 90 of 119
5. Update the record of the employee, set salary to 15000 where Dept_name is IT.

Page 91 of 119
 Assignment - 23

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:

Page 92 of 119
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.

Page 93 of 119
4. Display the details of the employee whose address is MSI, JANAKPURI.

5. Display the name of the employees whose salary is greater than 10,000.

Page 94 of 119
6. Update the employee details, set Emp_grade equal to C whose salary is less than
10000.

Page 95 of 119
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.

Page 96 of 119
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 employees where
Emp_grade is A.

Page 97 of 119
 Assignment: 24 – 28
Q.

Enter records as given in Assignment uploaded on google classroom.

Page 98 of 119
1. Retrieve a list of MANAGERS.

2. Find out salary of both MILLER and SMITH.

3. Find out the names and salaries of all employees earning more than 1000 per

month.

Page 99 of 119
4. Display the names and salaries of all employees except JAMES.

5. List the name and salary of employees who can earn more than 1500 and are in
department 10 or 30. Label the columns Employee and Monthly Salary respectively.

Page 100 of 119

6. List the name and salary for all employees whose salary is not in the range of 1500
and 2850.

7. Display the name, job, and salary of all the employees whose job is CLERK or
ANALYST and their salary is not equal to 1000, 3000, or 5000.

Page 101 of 119

8. Display the name, salary and commission for all employees whose commission
amount is greater than their salary increased by 10%.

9. Display the name of all employees who have two Ls in their name and are in
department 30 or their manager is 7782.

10. Find out the details of employees whose names begin with ‘S’.

Page 102 of 119

11. Find out the names of all employees that have ‘A’ anywhere in their name.

12. Find out the names of all employees that have ‘L’ as their third character in their
name.

13. Find out the names of the employees whose name begin with ‘A’ or ‘M’.

Page 103 of 119

14. List all the employees whose commission is NULL.

15. List employee number, employee name, total salary (i.e. salary + commission).
(Note: Manipulate the NULL values accordingly.)

Page 104 of 119

16. List the name of the employee and designation of the employee, who does not report
to anybody.

17. List employee name and yearly salary and arrange the output on the basis of yearly
salary in descending order.

Page 105 of 119

18. Retrieve the names of departments in ascending order and their employees in
descending order.

19. Select the name, job, salary, and department number of all employees except
SALESMAN from department number 30.

Page 106 of 119

20. List different jobs with no duplicates.

21. Count the total number of employees.

22. Print the total employees and average salary of each department.

Page 107 of 119

23. Select the minimum and maximum salary from EMP table.

24. List the minimum and maximum salaries of each department.

Page 108 of 119

25. List all departments in which more than 3 employees are working.

Assignment: 29 – 30

Q. Create table customer with fields: CID, Lname, Fname, City, Pin, State and insert 11
records. 
Perform the following queries on the table: 

Page 109 of 119

1. Display the contents.

2. Display CID and Fname. 

Page 110 of 119

3. Display Fname, Lname and State.

4. Display all records where state is Delhi.

Page 111 of 119

5. Display Fname, Lname concatenated with State.

6. Display the records whose pin is not entered.

7. Display the states to which the customer belongs.

Page 112 of 119

8. Select records of Delhi having name Rajiv.

9. Display records of Delhi or Karnataka. 

Page 113 of 119

10. Display details of Customer with CID=98. 

11. Display details of Customer except CID=98 

12. Use Alias Name to display CID.

Page 114 of 119

13. Retrieve all rows where CID is between 98 and 100.

14. Display those rows where state name begins with ‘D’.

15. Retrieve all rows where first name contains the word ‘RAJ’.

Page 115 of 119

16. Retrieve all rows where name field contains the word ‘RAJESH’.

17. Retrieve all rows where city is Delhi or Bangalore.

18. Rename the table customer to cost.

Page 116 of 119

19. Delete all those rows of customers who stay in Bangalore.

20. Delete those customers who do not have Pin Code.

Page 117 of 119

21. Rename column city to Address.

Page 118 of 119

22. Delete the customers who do not belong to Bangalore.

Page 119 of 119