Practical Lab File

On
Information Systems Management lab

Submitted for the partial fulfilment of the degree of the

“Bachelor of Business Administration”

(2023-2026)

Management Education and Research Institute

Affiliated To Guru Gobind Singh Indraprastha University
Sector 16-C, Dwarka, New Delhi

Supervised By Submitted By
Mr. Pranay Gupta Name- Lakshay Tyagi
Internal Supervisor Roll No: 02015101723

MERI

1
 TABLE OF CONTENTS

S.No. Title Page no. Signature

1. Practical 1 – Basic questions of SQL 3,4

2. Practical 2 – Create Database and tables 5,6

3. Practical 3 – Alter the tables 7,8

4. Practical 4 – Create new database loan 8,20

5. Practical 5 – select data with conditions from tables 11-13

2
 PRACTICAL 1

1. What is Database?

An organized electronic collection of data stored in tables (rows and columns).

2. What is Database Management Systems?

Application software used to define, manipulate, retrieve, and manage data in a database. (E.g.,
MySQL, Oracle).

3. What is SQL?

Structured Query Language, used to communicate with and manage data in relational databases.

4. What are the main types of SQL Commands?

DDL (Data Definition Language): Defines the structure (e.g., CREATE, ALTER).

DML (Data Manipulation Language): Manages data within tables (e.g., INSERT, UPDATE,
DELETE).

DQL (Data Query Language): Retrieves data (e.g., SELECT).

DCL (Data Control Language): Manages permissions (e.g., GRANT, REVOKE).

TCL (Transaction Control Language): Manages transactions (e.g., COMMIT, ROLLBACK).

5. What are features of SQL?

A: It is easy to learn (English-like), can create and modify tables, and is used to add, update, delete,
and retrieve data.

3
6. What is the difference between char and varchar2?

• CHAR: Fixed-length; always takes up the maximum declared space.

• VARCHAR2: Variable-length; only takes up space equal to the actual data stored.

4
 PRACTICAL 2

1. Create a database named BooksDB.

create database Booksdb;

use booksdb;

2. Create a table Books with the following fields:

Column name Data type Key

BookID INT Primary key
Title Varchar(20)
Author Varchar(25)
Price Decimal(9,2)
Publisher Varchar(34)
Year Date

Create table Books(book_ID int primary key,

Title varchar(20),
Author varchar(20),
Price decimal(9,2),
Publisher varchar(34),
Year date);

3. write a query to create another table Doctor.

Column name DataType Key

DoctorID INT Primary key
Name Varchar(15)
Specialization Varchar(26)
Phone BIGINT
Fees Decimal(8,3)

5
Create table Doctor(Doctor_ID int primary key,
Name varchar(15),
Specialization varchar(26),
Phone BigInt,
Fees decimal(8,3) );

4. write a command to view structure of tables ‘Books’.

Exec sp_help Books;

5. Write a command to remove structure and data of table ‘patient’

Drop table Book;

6. Write a command to delete database BooksDB in SQL Server.

Drop Database BooksDB;

6
 Practical 3

Create previous table books

1. Alter the Books table to add a new column Genre.

Alter table Books add Genre varchar(24);

2. Alter the Doctor table to add a new column Address.

Alter table doctor add address varchar(45);

3. Modify the Books table to increase the size of the Title column.

Alter table Books alter column title varchar(38);

4. Rename table name books to newbook.

Rename table books to newbook;

5. rename column name genre to music.

Alter table newbook rename column genre to music

6. Drop the column Genre from the Books table.

Alter table Books Drop column genre; ( it doest not execute as name of column changed )

7
7. Drop the column Address from the Doctor Table.

alter table Doctor drop column address;

8
 Practical 4

1) Create Database LoanDB.

2) Write a query to create new table Loan

Create table loan(loan_ID int primary key,

name varchar(45),

amount decimal(10,2),

rate decimal(5,2),

city varchar(30),

email varchar(100));

3.) Write a query to Insert 10 rows in Loan table

INSERT INTO LOAN (loan_id, name, amount, rate, city, email) VALUES

(101,'Ravi Kumar', 250000.00, 7.50, 'Delhi', 'ravi.kumar@gmail.com'),

(102,'Priya Sharma', 150000.00, 8.20, 'Mumbai', 'priya.sharma@yahoo.com'),

(103,'Amit Singh', 300000.00, 6.75, 'Bangalore', 'amit.singh@gmail.com'),

(104,Null, 200000.00, 7.00, 'Chennai', 'neha.gupta@gmail.com');

4) write a query to add new row without mention column name

INSERTINTOLOAN VALUES(105,'ajay mehta',200000.00,2.6,'Pune','ajay.mehta@gmail.com');

9
10
 Practical 5

1) write a Query to show all the rows /records

select * from Loan;

2) Write a query to show Name, Amount

select Name, amount from Loan;

3) write a query to display the name of student who live in delhi and Mumbai

select name from loan where city =’Delhi’ or city =’Mumbai’;

11
4) write a query to show the balance Ravi Kumar

select amount from Loan where name=’Ravi Kumar’;

5) write a Query to show Name of the students whose loan amount > 20000

select Name from Loan where amount>20000;

6) write a query to show the name of the student whose name start with "p"

SELECT name

FROM students

WHERE name LIKE 'P%';

12
8) write a query to show account number name and balance of the user whose amount
between 10000 and 20000;

SELECT account_number, name, balance

FROM accounts

WHERE balance BETWEEN 10000 AND 20000;

13
14