Practical File

Of
Information System Management

Submitted in Partial Fulfilment for the Award of the

Degree of Bachelor of Business Administration 2022-2025

Under the Guidance of: Ms. Rashi Aggarwal Submitted By: Saksham Sood
Designation: Assistant Professor Enrolment No.: 07661101722

Maharaja Agrasen Institute of Management Studies

Affiliated to Guru Gobind Singh Indraprastha University, Delhi
PSP Area, Plot No. 1, Sector 22, Rohini, Delhi 110086, India
Assignment 1
1. Explain the syntax of create command.

To create a table, there are 4 requisites that has to be there. These are –

Table name, Column name, Data type, Data size

2. Create a table EMP with the following fields:

Field Name Field Type Size
EMPNO Number 4
ENAME Text 10
JOB Text 10
HIREDATE Date
SAL Number 4

3. Describe EMP table.

4. Insert the following data in the EMP table:
EMPNO ENAME JOB HIREDATE SAL
7369 SMITH CLERK 17-DEC-80 800
7499 ALLEN SALESMAN 20-FEB-81 1600
7521 WARD SALESMAN 22-FEB-81 1250
7566 JONES MANAGER 02-APR-81 2975
7654 MARTIN SALESMAN 28-SEP-81 1250
7698 BLAKE MANAGER 01-MAY-81 2850
7782 CLARK MANAGER 09-JUN-81 2450
7788 SCOTT ANALYST 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 5000
7844 TURNER SALESMAN 08-SEP-81 1500
7876 ADAMS CLERK 23-MAY-87 1100
7900 JAMES CLERK 03-DEC-81 950
7902 FORD ANALYST 03-DEC-81 3000
7934 MILLER CLERK 23-JAN-82 1300
5. Display the data entered in EMP table.
 Assignment 2

1. Create a table DEPT with the following fields:

Field Name Field Type Size

DEPTNO NUMBER 2
DNAME TEXT 10
LOC TEXT 10

2. Describe the DEPT table.

3. Explain two ways of inserting the values in the table.

4. Insert the following data in the DEPT table:

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SOLUTION:

5. Display the data entered in DEPT table.

Assignment 3
Q4: Display the data entered in PROJECT table.
Assignment 4
Q1: Display record of employee where Empno is 7934.

Q2: Display records of employee where job is Manager.

Q3: Display employee name and hiredate of employee whose salary is 600

Q4: Display all fields from emp no 7876 to 7934.

Q5: Display all records where ename starts with le er J.

Q6: Display all records where salary is either 1300 or 1600.

Q7: Display all records of employees who are salesman.

Q8: Display employee name and salary where hiredate is 03-Dec-81.

Q9: Display employee name and job where ename ends with S.

Q10: Select all records where employee name starts with S and salary is 800.
Q11: Select all employee whose salary is between 1300 and 1600.

Q12: Select all employee who are NOT analyst.

Q13: Display names of employee whose first character is A.

Q14: Display names of employee whose second character is A.

Q15: Display names of employee whose first character is A and last character is S.
Q16: Display employee name and empno who is clerk and salary 1300

Q17: Select all employee that have ‘LL’ or ‘TT’ in any posi on.
Q18: Display employee name that have at least 5 characters in length.
Assignment 5

1. Create Course table with given constraints

Insert following Values in course table
2. Create Faculty Table with given constraint
Insert following values in Faculty Table Data
3. Create student table having following structure and constraints
Assignment 6
DML- Select Query

1. Display the salary of Dr. Parul

2. Display the names of faculty who are either teaching HR or Marketing.

3. Change the HoD name from Dr. Umesh to Dr. Nisha Singh.

4. Display the percentage requirement in each course.

5. Display the course name which are being held in floor 3 or floor 8.

6. Update the fees of BBA course to 90000.

7. Who is the HoD of Eco(H).

8. Display the percentage of BBA and B.COM(H).

9. Now the BJMC classes will be held in Floor 5. Update and display the same.
10. Display all faculty who have specialization in ‘IT’.

11. Display the name of faculty who are drawing a salary of more than 75000.
12. Display the name, specialization, couse id and salary of those faculty members
whose name starts with S and teaching in course id 101.

13. Display specialization in Course ID 101.

14. Display the faculty members whose name ends with letter A.
15. Display faculty who are teaching in course ID 101.

16. Delete the entry of Enrol no. 1001.

17. The new coordinator of students ranging from Roll no 1002-1005 is Ms. Kamna.
Change the same in the table.
18. The name of Student having enrol. no 1016 has been written as ‘RISHAB’ instead of
‘RISHOBH. Change the same in the table.
19. Add one more department BCA in Department table. the course id for the same is
106.
20. Dr. Parul has been shifted from BBA department to BCA department. update the
same in the table.
Assignment 7

1. Select all columns in student table.

2. Select all columns in faculty table with a salary over 20000.

3. Display all columns in faculty table where salary is between 60000 and 80000.
4. Select EnrolNO, SNAME, and DOB for for each student whose class coordinator is Dr. Parul.

5. Select all columns in student table whose birthdate >=9th June, 2003.
6. Select enrolno, name and class coordinators of those students whose class coordinator is
either Dr. Sarita or Dr. Parul.

7. Select fname, course_id and specialization of those faculty whose salary>70000 and
specialization is IT.
8. Select fname from faculty table where fname doesn’t start with letter P.

9. Retrieve the list of all honours courses from course table.

10. Display the name of courses which start with letter B.

11. Which honours course has fees more than 81000.

12. What is the percentage requirement of BBA Course?

13. Which Courses are held in floor 3 and 8?

14. Display the faculty name who are specialized in IT or Finance and drawing a salary of more
than 50000.

15. Display the names of students whose name begins with ‘M’
16. Display the names of students whose name ends with ‘N”.

17. Display the names of students whose name starts with ‘A” or ends with ‘N’.
18. Display the names of students whose name contains ‘AA’ and Class coordinator is Dr.
Deepak.

19. Display the names of students whose name contains ‘I’ twice.
Assignment 8
Subquery

1. Display faculty who are teaching in course ID 101.

2. Delete the records from student table where Enrol_no is 1016.

3. Change student name from SONIA KAPOOR to SONYA KAPOOR whose Enrol_no is
1006.

4. Type the following SQL statement and note the output.

a. SELECT * FROM STUDENT WHERE SNAME LIKE 'G_' ;

b. SELECT * FROM STUDENT WHERE SNAME='G';
c.

d. SELECT * FROM STUDENT WHERE SNAME LIKE 'G%';

 e. SELECT * WHERE Student WHERE StdName like '%G%' ;

5. Display all the cities other than Delhi in student table.

6.
7. Get the names of faculty teaching in BBA Department.

8. Get the number of students in the department named ‘BBA’.

Assignment 9
Subquery

1. Get the number of the student whose name comes first among all students (i.e. first
in alphabetical order).

2. Retrieve all student names whose city is ‘Delhi’ and the last 4 digits of their tel. no.
are 4341. Retrieve the names in alphabetical order.
3. Find the highest salary of any instructor.

4. Find all instructors earning the highest salary (there may be more than one with the
same salary).
5. Display all faculty who are studying either in BBA or B.COM(H).

6. Display student names who are being taught by IT faculty.

7. Display total salary of faculty.

8. Display students under the supervision of Dr. Parul.

9. Display the fees paid by student “VIKAS SINGH”.

10. Add one column credits in course table with default value 24.
11. Display students name whose HOD is Dr. Vijay.

12. Find the minimum salary of faculty.

Assignment 10
Text Functions

Based on Student, Faculty & Course Table

1. Concatenate Sname, Phone_no in one column.

2. Write a query to append ‘@maims.ac.in’ to sname field.

3. Display details of students where the length of name is greater than 6 characters.

4. Display details of students where name is in lower case.

5. Display details of students where name is in upper case.

6. Extract first three characters of city of each student.

7. Write a query to update the portion of the phone_number, within the phone
number the substring '124' will be replaced by '999'.

8. Write a query to display leading zeros before salary of faculty.

9. Display course which has minimum no. of characters in it.

10. Extract last three characters of city of each student.

11. Display query in the form: Amit was born on 1-Jan-2002.

12. Display S_ID, SName, and birthmonth from student table.

13. Extract last 4 characters of phone number.

14. Write a query to display the first word from those student names which contains

more than three words.

Assignment 11
Mathematical Functions
Q1: Find the Absolute value of the following Numbers
 -2.34
 1.222
 -234.45
 -98.00
 23.000

Q2: Solve the Following queries using SQL AVERAGE FUNCTION

 Find the average of the fees from course table.

  Find the average salary of all the faculty according to the course code

 Display total fees to be paid by the student after three years.

Q3: Solve the following queries using COUNT Function

 Find the total no of students who are born after May 2002.
  Find the total no of students from student table who are taught by Ms. Kamna.

 Find the total no of students who are born in Jan.

Q4: Solve the following queries using Max and Min Function
 Find the minimum fees value from course table.
 Find the Maximum fees value from course table.

 Find the name of the course having Minimum fees.

  Find C_ID and name from course where fees is maximum.

Q5: BASIC MATHEMATICAL FUNCTION

 Find the square root of 64

 Find the square root of 81.
 Find the square root of 144.
 Divide 345/5
 Divide 567/4
 Divide 456/2
 Calculate 2 3
 Calculate 3 3
 TRUNCATE (234.567,2)
 TRUNCATE (456.900,1)
 TRUNCATE(567.90,2)
 ADD 23,45,78;
 SUB 76,23
 MULT 23,56
Assignment 12

Explain all the date and me func ons.

1. SYSDATE

2. CURRENT_DATE
3. EXTRACT

4. LAST DAY

5. MONTHS BETWEEN
6. ADD MONTHS
Assignment 13
Q1: Create the following Student table as per shown Below

Q2: A er crea on of the Table, Insert the following Data

Q-3

1. Calculate Total as Sum of PDCS+ME+FA+ITM+HRM.

2. Display the student records in ascending order of name star ng from A-Z
3. Display the student records of all the female students in descending order ranging from Z-A
4. Display the Result of the students in Highest Total marks to the lowest Total Marks.

5. Display the Students records of all the male students ranging in Highest to lowest marks in PDCS.
6. Display the student records of all the female students ranging in lowest to highest in ME.

7. Display the students records in ascending order of ITM Marks.

Assignment 14
1.CREATE A FOLLOWING TABLE WHICH WILL CONTAIN THE DETAIL OF EMPLOYEES WORKING IN AN
ORGANISATION AND NAME OF THE TABLE SHALL BE EMPLOYEE

2. Add one more column in table department having datatype as varchar and not null constraint.
3. INSERT THE FOLLOWING DATA IN THE TABLE

4. ALTER THE ABOVE TABLE BY ADDING THE NEW COLUMN AS SALARY

5. a) Update the salary of all the employees to 25000 working in HR department as assistant .

b) Update the salary of all the employees to 35000 working in HR department as manager
c) Update the salary of all the employees to 100000 working in finance department as director.

d) Update the salary of all the employees to 28000 working in finance department as assistant.

e) Update the salary of all the employees to 26000 working in sales department as assistant.

f) Update the salary of all the employees to 200000 working in sales departmet as ceo.

g) Update the salary of all the employees to 40000 working in sales department as manager.
h) Update the salary of all the employees to 45000 working in admin department as manager.

6. Change the size of name to Varchar(30).

7. Rename the table as Emplotee_Details.

8. Add two more columns Address and Phone No.

9. Drop column Address

10. Drop Table

Assignment 15
1. Create a table Survey having columns:

Column Name Data type Allow Nulls

FullName Varchar

Gender Varchar

BirthDate Date

Favourite Food Varchar

Daily Allowance Number

DailySpent Number

2. Display total daily allowance from survey.

3. Display total daily allowance of each gender.

4. Display sum of daily allowance based on favorite food.

5. Count the number of males and females in table.

6. Count the number of males and females that like a par cular food.

7. calculate the average daily allowance of males and females.

Assignment 16
Q1: Display the name of course in which fees is maximum.

Q2: Display the name of course in which fees is minimum.

Q3: Display the average salary of faculty.

Q4: Display the name and salary of faculty who earns maximum salary in IT specialization.

Q5: Display names of students who have paid 70000 as fees.

Q6: Display names of students course wise.

Q7: Display names of students class coordinator wise.
Q8: Display names of students who were born in September.
Q9: Display faculty name course wise.

Q10: Display faculty name specialization wise.

Q11: Display count of students taught by each faculty.

Q12: Display names and address of student whose name’s second character is ‘U’.
Assignment 17
1. Write a query to list the number of jobs available in the employees table

2. Write a query to get the total salaries payable to employees.

3. Write a query to get the minimum salary from employees table.

4. Write a query to get the maximum salary of an employee working as a Programmer.

5. Write a query to get the average salary and number of employees working the department
90.

6. Write a query to get the highest, lowest, sum, and average salary of all employees.
7. Write a query to get the number of employees with the same job.

8. Write a query to get the difference between the highest and lowest salaries.

9. Write a query to find the manager ID and the salary of the lowest-paid employee for that
manager.

10. Write a query to get the department ID and the total salary payable in each department.
 11. Write a query to get the average salary for each job ID excluding programmer.

12. Write a query to get the total salary, maximum, minimum, average salary of employees for
department ID 90 only.

13. Write a query to get the job ID and maximum salary of the employees where salary is greater
than or equal to $4000.

14. Write a query to get the average salary for all departments employing more than 10 employees.
Assignment 18
1. Create a table Faculty having columns:
Column Name Data type Example
F_Id Varchar
Name Varchar
Designation Varchar Asst, Associate, Professor
Office Varchar A101….
Yrs of Experience Number

2. Create a table Student having columns:

Column Name Data type Example
S_Id Varchar
Name Varchar
Age Number
Course Varchar
Semester Number
GPA Number
F_ID Varchar
3. Display age, name, id, GPA of student who is studying in 2 sem of BBA.
nd

Q4: For each student who has a GPA <2, Display their GPA, age, id, name and ID of their faculty.

Q5: For each student who is studying in B.COM(H), list their ID, name, age, “Expected age as a Senior”(their
age three years from now).
Q6: List all faculty (Id, and name) who reside in Building ‘B’ (office number starts with the letter
‘B’).

Q7: For each F_ID, list F_ID, average GPA and count of semester.
Q8: For each course, list the course, avg age, min age and max age for that major. column
headings should be ‘avg age’ ‘youngest’ ‘oldest’.

Q9: For each student who is in semester 4, list ID, name, GPA, Faculty Id, faculty name and
designation.

Q10: For each sem, list sem and the number of students under 25 years old, include the average
gpa of these students.
Q11: For each faculty, list id, name, designation, and list of their students (stID , stName and gpa)
who have gpa below 3.0.

Q12: List the Faculty number, of any faculty who has more than one student with a GPA above 3.
Include a count of these students.

Q13: For the faculty member named ‘Bharti’ (list name, number and designation) and all their
students (number and name and age) who are not seniors. (sort the result by student names).
Q14: List the ID, name and GPA, for all students who have a GPA higher than average GPA.

Q15: List the ID, name, and age, for all students who are in course with more than 5 members.
Q16: Retrieve the name of each faculty and number of students studying under each faculty which
has at least 2 students.
Assignment 19
Identify all tables, column names, primary key

Table 1 – Student

Primary Key – Student ID

Multivalues Attribute – Hobby

Derived Attribute – Age

Composite Attribute - Address

Column Name- Student ID, DOB, Age, Hobby, Student Name, Address, Course ID.

Table 2 – Course

Primary Key – Course ID

Column Name – Course ID, Course Name

Table 3 – Subjects

Primary Key – Subject ID

Column Name- Subject ID, Subject Name, Lecturer ID

Table 4 – Lecturer

Primary Key – Lecturer ID

Column Name – Lecturer ID, Lecturer Name, Course ID

Assignment 20

Q 1 E-R Diagram

Components:

1. En ty:

 Student

2. A ributes:

 Roll_No (Primary Key)

 Name

 Sex

Q 2 Composite

The provided diagram is a Composite E-R Diagram for the Student en ty. Here's the breakdown:

1. En es:

 Student:
 o A ributes:

 Roll_No: Primary Key.

 Name

 Address

3. Composite A ributes:

Name and Address are composite a ributes because they consist of mul ple sub-parts that provide
addi onal detail about the main a ribute.

 Name:

o First_Name

o Last_Name

 Address:

o House_no

o Street

o City

Q 3 Mul _Valued

A Mul valued A ribute in an E-R diagram represents an a ribute that can have mul ple values for a
single en ty. It is visually represented by two concentric ovals around the a ribute name.

 En ty: Student

o A ributes:

 Roll_No: Primary Key.

 City

 Mobile_No: A Mul valued A ribute.

Why Mobile_no is Mul valued:

 A student can have mul ple mobile numbers (e.g., personal and home), so Mobile_no is
presented as a mul valued a ribute.

Q 4 Rela onship

 En ty: Employee

o A ributes:

 Emp_No: Primary Key.

 Emp_Name

 Salary

o Department

 Dept_Id: Primary Key

 Dept_Name

 Rela onship
Works in(between Employee and Department):
o A ributes:
 Since
Assignment 21

1. En es
 Student: A ributes are:
o Sid (Student ID)
o Name
o Program
 Course Offerings: A ributes are:
o Courseno (Course Number)
o Secno (Sec on Number)
o Year
o Semester
o Room
o Time
  Exam: A ributes are:
o Eid (Exam ID)

o Name
o Place
o Time
2. Rela onship
 Takes (between Student and Course Offerings):
o A ributes:
 Marks
Assignment 22
Draw ER diagram of Library and iden fy all tables, column names, primary key if any:

Table 1 – Books

Primary Key – Book ID

Column Name – Title, Book ID, Price, Author, Available

Table 2 – Publisher

Primary Key – Publisher ID

Column Name – Publisher ID, Address, Name

Table 3 – Member

Primary Key – Member Id

Column Name – Member ID, Expiry date, Name, Address, Membership date, Member Type

Table 4 – Borrowed By

Primary Key – Member Id

Column Name – Due date, Return Date, Issue

Assignment-23
Q1. Create two tables as Customer and Orders. Insert following entries in the table.
Q2. Return all rows from the customers and orders tables where there is matching customer_id value
in both the customers and orders tables.

Q3. Return all rows from the customers table and only those rows from the orders table where the
joined fields are equal.

Q4. Return all rows from the orders table and only those rows from the customers table where the
joined fields are equal.
Q5. Return all rows from the orders table and all rows from the customers table.
Assignment 24
Joins

1. Create table customer, salesman and order with following entries:

2. Find the salesperson and customer who belongs to same city. Return Salesman, cust_name and
city.

3. Find those orders where order amount exists between 500 and 2000. Return ord_no, purch_amt,
cust_name, city.
4. Find the salesperson(s) and the customer(s) he handles. Return Customer Name, city, Salesman,
commission.

5. Find those salespersons who received a commission from the company more than 12%. Return
Customer Name, customer city, Salesman, commission.

6. Find those salespersons do not live in the same city where their customers live and received a
commission from the company more than 12%.
7. Find the details of an order. Return ord_no, ord_date, purch_amt, Customer Name, grade,
Salesman, commission.

8. Find those customers whose grade less than 300. Return cust_name, customer city, grade,
Salesman, salesman city. The result should be ordered by ascending customer_id.