LAB on DBMS – SQL Assignment

Course: MCA-415 - LAB on DBMS Student Name: Ansari Intesab Aarif Roll No: 01 Course: F.Y.M.C.A

Contents

1. Question 1 – Creating database tables and using data types
2. Question 2 – CREATE, ALTER, DROP table
3. Question 3 – Data manipulation using INSERT, UPDATE, DELETE
4. Question 4 – Implementing constraints
5. Question 5 – Retrieving data using SELECT and clauses
6. Question 6 – Aggregate functions
7. Question 7 – String functions
8. Question 8 – Date and time functions
9. Question 9 – UNION, INTERSECTION, SET DIFFERENCE
10. Question 10 – Nested queries and JOINs
11. Question 11 – Operations on views
12. Question 12 – Procedures
13. Question 13 – Triggers
14. Question 14 – Cursors
15. Question 15 – Database connectivity with front-end tools
16. Question 16 – Data reports
17. Question 17 – ER model and normalization

Question 1 – Creating Database Tables and Using Data Types

Problem Statement

Create database tables using appropriate SQL data types for a simple college database. Define tables like STUDENT, COURSE, and ENROLLMENT with suitable columns and constraints.

Schema / Tables Used (Preview)

Table: STUDENT

Table: COURSE

Table: ENROLLMENT

SQL Queries

CREATE TABLE STUDENT (
	student_id   INT,
	student_name VARCHAR(50),
	gender       CHAR(1),
	dob          DATE,
	city         VARCHAR(30),
	mobile_no    VARCHAR(15)
);
 
CREATE TABLE COURSE (
	course_id   INT,
	course_name VARCHAR(50),
	credits     INT
);
 
CREATE TABLE ENROLLMENT (
	enroll_id   INT,
	student_id  INT,
	course_id   INT,
	enroll_date DATE
);

Sample Input Data (Preview Table)

Sample STUDENT Data

Sample COURSE Data

Output / Result (After Creating Tables)

There is no row output for CREATE TABLE, but the DBMS usually shows a message like:

Execution Message Preview

Explanation

In this question I defined basic tables for a college system using proper SQL data types like INT, VARCHAR, and DATE. The STUDENT table stores personal details of students, COURSE stores course information, and ENROLLMENT will be used to connect students with the courses they select.

Question 2 – CREATE, ALTER and DROP Table

Problem Statement

Demonstrate how to create a new table, modify the structure of an existing table using ALTER, and finally remove a table using DROP.

Schema / Tables Used (Preview)

For this question, I am using a simple DEPARTMENT table to show CREATE, ALTER and DROP operations.

Table: DEPARTMENT (Initial Definition)

SQL Queries

-- CREATE TABLE example
CREATE TABLE DEPARTMENT (
	dept_id   INT,
	dept_name VARCHAR(40)
);
 
ALTER TABLE DEPARTMENT
ADD location VARCHAR(30);
 
ALTER TABLE DEPARTMENT
MODIFY dept_name VARCHAR(60);
 
ALTER TABLE DEPARTMENT
DROP COLUMN location;
 
DROP TABLE DEPARTMENT;

Execution Preview Table

Explanation

In this question I first created a simple DEPARTMENT table with two columns. Then I used ALTER TABLE to add a new column and to change the size of an existing column. Finally, I showed how to drop a column and how to completely remove the table from the database using DROP TABLE.

Question 3 – Data Manipulation (INSERT, UPDATE, DELETE)

Problem Statement

Perform basic data manipulation operations (INSERT, UPDATE, DELETE) on the STUDENT table.

Schema / Tables Used (Preview)

We will reuse the STUDENT table defined in Question 1.

Table: STUDENT

Initial Data (Before Operations)

SQL Queries

-- INSERT new student
INSERT INTO STUDENT (student_id, student_name, gender, dob, city, mobile_no)
VALUES (3, 'Sneha Sharma', 'F', '2002-03-21', 'Nashik', '9123456780');
 
-- UPDATE a student city
UPDATE STUDENT
SET city = 'Pune'
WHERE student_id = 2;
 
-- DELETE a student record
DELETE FROM STUDENT
WHERE student_id = 1;

Data After INSERT

Data After UPDATE

Data After DELETE

Explanation

In this question I used INSERT to add a new student record, UPDATE to change the city of an existing student, and DELETE to remove one record from the table. By comparing the tables before and after each operation, we can clearly see how DML statements change the stored data.

Question 4 – Implementing Constraints

Problem Statement

Implement and demonstrate different types of constraints in SQL like NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and DEFAULT on the college database tables.

Schema / Tables Used (Preview)

Here I am using STUDENT, COURSE and ENROLLMENT tables with proper constraints.

Table: STUDENT (with constraints)

CREATE TABLE STUDENT (
	student_id   INT           PRIMARY KEY,
	student_name VARCHAR(50)   NOT NULL,
	gender       CHAR(1)       CHECK (gender IN ('M','F')),
	dob          DATE,
	city         VARCHAR(30)   DEFAULT 'Dhule',
	mobile_no    VARCHAR(15)   UNIQUE
);

Table: COURSE (with constraints)

CREATE TABLE COURSE (
	course_id    INT          PRIMARY KEY,
	course_name  VARCHAR(50)  NOT NULL,
	credits      INT          CHECK (credits > 0)
);

Table: ENROLLMENT (with constraints)

CREATE TABLE ENROLLMENT (
	enroll_id   INT  PRIMARY KEY,
	student_id  INT  NOT NULL,
	course_id   INT  NOT NULL,
	enroll_date DATE DEFAULT CURRENT_DATE,
	CONSTRAINT fk_student
		FOREIGN KEY (student_id) REFERENCES STUDENT(student_id),
	CONSTRAINT fk_course
		FOREIGN KEY (course_id) REFERENCES COURSE(course_id)
);

Sample INSERT Statements (Valid and Invalid)

INSERT INTO STUDENT (student_id, student_name, gender, dob, city, mobile_no)
VALUES (1, 'Ansari Intesab', 'M', '2001-01-01', 'Dhule', '8983245824');
 
INSERT INTO STUDENT (student_id, student_name, gender, dob, mobile_no)
VALUES (2, 'Rahul Patil', 'M', '2001-05-12', '9876543210');
 
INSERT INTO STUDENT (student_id, student_name, gender, dob, city, mobile_no)
VALUES (3, 'Sneha Sharma', 'X', '2002-03-21', 'Nashik', '9123456780');
 
INSERT INTO STUDENT (student_id, student_name, gender, dob, city, mobile_no)
VALUES (4, 'Amit Joshi', 'M', '2001-08-10', 'Pune', '8983245824');
 
INSERT INTO COURSE (course_id, course_name, credits)
VALUES (101, 'DBMS', 4);
 
INSERT INTO COURSE (course_id, course_name, credits)
VALUES (102, 'OS', 0);
 
INSERT INTO ENROLLMENT (enroll_id, student_id, course_id)
VALUES (1, 1, 101);
 
INSERT INTO ENROLLMENT (enroll_id, student_id, course_id)
VALUES (2, 5, 101);

Constraint Violation Preview Table

Explanation

In this question I tried to show how constraints protect data in a table. PRIMARY KEY makes each row unique, NOT NULL avoids missing important values, FOREIGN KEY connects child table with parent table, UNIQUE avoids duplicate values, CHECK controls allowed values like gender and credits, and DEFAULT fills a value automatically when we do not pass it.

Question 5 – Retrieving Data Using SELECT and Clauses

Problem Statement

Retrieve data from tables using different clauses like simple SELECT, WHERE, ORDER BY, DISTINCT and GROUP BY.

Schema / Tables Used (Preview)

I am using the same STUDENT and COURSE tables and some sample data.

Sample STUDENT Data

Sample COURSE Data

SQL Queries and Outputs

SELECT * FROM STUDENT;

Output Preview

SELECT student_id, student_name, city
FROM STUDENT
WHERE city = 'Dhule';

Output Preview

SELECT student_id, student_name, city
FROM STUDENT
ORDER BY student_name ASC;

Output Preview (ordered by name)

SELECT DISTINCT city
FROM STUDENT;

Output Preview

SELECT city, COUNT(*) AS total_students
FROM STUDENT
GROUP BY city;

Output Preview

Explanation

Here I used SELECT with different clauses to filter and arrange data. WHERE selects specific rows, ORDER BY changes the order, DISTINCT removes duplicate values and GROUP BY is used to get total students per city.

Question 6 – Aggregate Functions

Problem Statement

Use aggregate functions like AVG, COUNT, MAX, MIN and SUM on a numeric column and show the results.

Schema / Tables Used (Preview)

For this question I am using a simple RESULT table which stores marks of students in one subject.

Table: RESULT

Sample RESULT Data

SQL Queries

SELECT AVG(marks) AS avg_marks
FROM RESULT
WHERE course_id = 101;
 
SELECT COUNT(*) AS total_students
FROM RESULT
WHERE course_id = 101;
 
SELECT MAX(marks) AS max_marks,
	   MIN(marks) AS min_marks,
	   SUM(marks) AS total_marks
FROM RESULT
WHERE course_id = 101;

Output / Result Preview

Average Marks

Total Students

Max, Min, Sum

Explanation

Aggregate functions help to get summary of data. Here I calculated average, total count, highest marks, lowest marks and total marks of all students for DBMS course.

Question 7 – String Functions

Problem Statement

Use different string functions on character type columns like name, city and email.

Schema / Tables Used (Preview)

For this question I am using a simple CUSTOMER table.

Table: CUSTOMER

Sample CUSTOMER Data

SQL Queries

SELECT cust_id, UPPER(cust_name) AS upper_name
FROM CUSTOMER;
 
SELECT cust_id, LOWER(cust_name) AS lower_name
FROM CUSTOMER;
 
SELECT cust_id, cust_name, LENGTH(cust_name) AS name_length
FROM CUSTOMER;
 
SELECT cust_id, email,
	   SUBSTR(email, 1, 5) AS email_prefix
FROM CUSTOMER;
 
SELECT cust_id,
	   cust_name || ' - ' || city AS name_with_city
FROM CUSTOMER;

Output / Result Preview

UPPER(cust_name)

LENGTH(cust_name)

SUBSTR(email, 1, 5)

Explanation

String functions are used to work with character data. I practiced UPPER, LOWER, LENGTH, SUBSTR and concatenation. These are very common when we have to clean or format names and emails.

Question 8 – Date and Time Functions

Problem Statement

Use different date and time functions on date columns.

Schema / Tables Used (Preview)

For this question I am using an ATTENDANCE table.

Table: ATTENDANCE

Sample ATTENDANCE Data

SQL Queries

SELECT att_id, student_id, att_date,
	   EXTRACT(YEAR  FROM att_date) AS year_part,
	   EXTRACT(MONTH FROM att_date) AS month_part,
	   EXTRACT(DAY   FROM att_date) AS day_part
FROM ATTENDANCE;
 
SELECT CURRENT_DATE AS today_date;
 
SELECT student_id,
	   MIN(att_date) AS first_day,
	   MAX(att_date) AS last_day,
	   (MAX(att_date) - MIN(att_date)) AS days_difference
FROM ATTENDANCE
GROUP BY student_id;

Output / Result Preview

Extracted Parts from Date

Explanation

In this question I practiced how to pick different parts from a date and how to use current date. I also calculated difference between first and last attendance date for each student.

Question 9 – UNION, INTERSECTION, SET DIFFERENCE

Problem Statement

Use set operations like UNION, INTERSECT and MINUS (set difference) on compatible queries.

Schema / Tables Used (Preview)

I am using two simple tables for students participating in different activities.

Table: CRICKET_STUDENTS

Table: FOOTBALL_STUDENTS

Sample Data

SQL Queries

SELECT student_id, student_name FROM CRICKET_STUDENTS
UNION
SELECT student_id, student_name FROM FOOTBALL_STUDENTS;
 
SELECT student_id, student_name FROM CRICKET_STUDENTS
INTERSECT
SELECT student_id, student_name FROM FOOTBALL_STUDENTS;
 
SELECT student_id, student_name FROM CRICKET_STUDENTS
MINUS
SELECT student_id, student_name FROM FOOTBALL_STUDENTS;

Output / Result Preview

UNION Result – students in at least one game

INTERSECT Result – students in both games

MINUS Result – students playing only cricket

Explanation

Set operations work on result sets like mathematical sets. UNION combines, INTERSECT gives common rows and MINUS gives records present in first query but not in second query.

Question 10 – Nested Queries & JOINs

Problem Statement

Use nested queries (subqueries) and different types of JOINs between tables.

Schema / Tables Used (Preview)

I am using DEPARTMENT and EMPLOYEE tables.

Table: DEPARTMENT

Table: EMPLOYEE

Sample Data

SQL Queries

SELECT e.emp_id, e.emp_name, d.dept_name, e.salary
FROM EMPLOYEE e
JOIN DEPARTMENT d ON e.dept_id = d.dept_id;
 
SELECT e.emp_id, e.emp_name, d.dept_name, e.salary
FROM EMPLOYEE e
LEFT JOIN DEPARTMENT d ON e.dept_id = d.dept_id;
 
SELECT emp_id, emp_name, salary
FROM EMPLOYEE
WHERE salary > (
	SELECT AVG(salary) FROM EMPLOYEE
);

Output / Result Preview

INNER JOIN Result

LEFT JOIN Result (includes Temporary Emp)

Explanation

In this question I practiced joins and subqueries. INNER JOIN shows only matching records, LEFT JOIN keeps all employees even if they have no department, and subquery helps to compare each salary with the average salary.

Question 11 – Operations on Views

Problem Statement

Create a view on existing tables, perform SELECT from view and then drop the view.

Schema / Tables Used (Preview)

Using STUDENT and COURSE tables and the ENROLLMENT table from previous questions.

SQL Queries

CREATE VIEW STUDENT_COURSE_VIEW AS
SELECT s.student_id, s.student_name,
	   c.course_name, e.enroll_date
FROM STUDENT s
JOIN ENROLLMENT e ON s.student_id = e.student_id
JOIN COURSE c ON e.course_id = c.course_id;
 
SELECT * FROM STUDENT_COURSE_VIEW;
 
DROP VIEW STUDENT_COURSE_VIEW;

Output / Result Preview

STUDENT_COURSE_VIEW Data (example)

Explanation

A view is like a stored SELECT query. It makes complex joins easier to use. We can select from the view like from a normal table.

Question 12 – Procedures

Problem Statement

Write a simple stored procedure to display student details of a particular city.

SQL Code (Example in PL/SQL style)

CREATE OR REPLACE PROCEDURE show_students_by_city(p_city IN VARCHAR2) AS
BEGIN
	SELECT student_id, student_name, city
	FROM STUDENT
	WHERE city = p_city;
END;
/
 
EXEC show_students_by_city('Dhule');

Output / Result Preview

Explanation

Procedure is a stored block of code which can be called again and again. Here I passed city as parameter and procedure shows all students from that city.

Question 13 – Triggers

Problem Statement

Create a trigger which stores old values in a log table whenever we delete a student record.

Schema / Tables Used (Preview)

Base table: STUDENT
Log table: STUDENT_LOG

CREATE TABLE STUDENT_LOG (
	log_id      INT,
	student_id  INT,
	student_name VARCHAR(50),
	action_date DATE
);
 
CREATE OR REPLACE TRIGGER trg_student_delete
BEFORE DELETE ON STUDENT
FOR EACH ROW
BEGIN
	INSERT INTO STUDENT_LOG (log_id, student_id, student_name, action_date)
	VALUES (1, :OLD.student_id, :OLD.student_name, SYSDATE);
END;
/

Example Operation

DELETE FROM STUDENT
WHERE student_id = 2;

Output / Result Preview

STUDENT_LOG Data

Explanation

Trigger automatically fires when a particular DML operation happens. In this example, whenever I delete a student, the trigger copies old values into log table for future record.

Question 14 – Cursors

Problem Statement

Use an explicit cursor to display all student names one by one.

SQL Code (PL/SQL style)

DECLARE
	CURSOR c_students IS
		SELECT student_id, student_name FROM STUDENT;
 
	v_id   STUDENT.student_id%TYPE;
	v_name STUDENT.student_name%TYPE;
BEGIN
	OPEN c_students;
	LOOP
		FETCH c_students INTO v_id, v_name;
		EXIT WHEN c_students%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ' Name: ' || v_name);
	END LOOP;
	CLOSE c_students;
END;
/

Output / Result Preview

On console we will see lines like:

ID: 1 Name: Ansari Intesab
ID: 2 Name: Rahul Patil
...

Explanation

Cursor allows us to process query result row by row. Here I opened the cursor on STUDENT table and printed each student’s id and name.

Question 15 – Database Connectivity with Front-End Tools

Conceptual Explanation

For database connectivity we can use front-end tools like VB.NET, C#.NET or Java. In our syllabus mostly we show simple example using Java and JDBC.

Basic steps are:

1. Load the database driver in program.
2. Create a connection using connection string (URL, username, password).
3. Create Statement or PreparedStatement object.
4. Execute SQL queries (SELECT, INSERT, UPDATE, DELETE).
5. Read data from ResultSet and show on form or console.
6. Close connection.

This is only conceptual in my assignment; actual coding part depends on the language used in lab.

Question 16 – Data Reports

Conceptual Explanation

Data reports are prepared using tools like Crystal Reports or built-in reporting tools in .NET / Java frameworks. From database we fetch data and then design report layout (title, column headings, totals etc.).

Example: Student Marks Report

Columns in Report

• Student Name
• Course Name
• Marks
• Result (Pass/Fail)

Sample Report Output

In real tools we can export this report to PDF or print it directly.

Question 17 – ER Model and Normalization

Problem Statement

Design an ER model for a small college system and convert it into a set of normalized tables.

Identified Entities

For my simple college database I considered the following main entities:

• STUDENT – stores student related information.
• COURSE – stores course related information.
• DEPARTMENT – stores department details.
• FACULTY – stores faculty/teacher information.
• ENROLLMENT – represents which student takes which course.
• RESULT – stores marks of students in different courses.

Attributes & Keys (Main Tables)

STUDENT

• student_id (PK)
• student_name
• gender
• dob
• city
• mobile_no

COURSE

• course_id (PK)
• course_name
• credits
• dept_id (FK to DEPARTMENT)

DEPARTMENT

• dept_id (PK)
• dept_name

FACULTY

• faculty_id (PK)
• faculty_name
• dept_id (FK to DEPARTMENT)

ENROLLMENT

• enroll_id (PK)
• student_id (FK to STUDENT)
• course_id (FK to COURSE)
• enroll_date

RESULT

• result_id (PK)
• student_id (FK to STUDENT)
• course_id (FK to COURSE)
• marks

Relationships in Words

• One DEPARTMENT has many COURSEs.
• One DEPARTMENT has many FACULTY members.
• One COURSE can be taught by one or more FACULTY (can be modeled with extra table if needed).
• One STUDENT can enroll for many COURSEs.
• One COURSE can have many STUDENTs. So STUDENT and COURSE are in a many‑to‑many relationship, which is resolved using the ENROLLMENT table.
• One RESULT record belongs to one STUDENT and one COURSE.

ER Description in Simple Form

If I describe my ER diagram in sentences:

• STUDENT (student_id, student_name, gender, dob, city, mobile_no)
• COURSE (course_id, course_name, credits, dept_id)
• DEPARTMENT (dept_id, dept_name)
• FACULTY (faculty_id, faculty_name, dept_id)
• ENROLLMENT (enroll_id, student_id, course_id, enroll_date)
• RESULT (result_id, student_id, course_id, marks)

STUDENT connects to COURSE through ENROLLMENT. COURSE and DEPARTMENT are connected using dept_id. Faculty also belongs to a department.

Normalization Steps (1NF, 2NF, 3NF)

Unnormalized Form (UNF) Example

Suppose initially all data is written in one big table like this:

Here multiple courses and marks are stored in a single row and repeating groups are present.

First Normal Form (1NF)

Rules: remove repeating groups, keep atomic values only.

After converting to 1NF we separate each course into a new row:

Still there is redundancy (student_name repeated, dept_name repeated etc.).

Second Normal Form (2NF)

Rules: table should be in 1NF and there should be no partial dependency on part of composite key.

To reach 2NF we split the data into separate tables:

• STUDENT(student_id, student_name, gender, dob, city, mobile_no)
• COURSE(course_id, course_name, credits, dept_id)
• DEPARTMENT(dept_id, dept_name)
• RESULT(result_id, student_id, course_id, marks)

Now non‑key attributes depend fully on their respective primary key.

Third Normal Form (3NF)

Rules: table should be in 2NF and there should be no transitive dependency (non‑key attribute depending on another non‑key attribute).

In my design above, dept_name depends only on dept_id in DEPARTMENT table, not on course_id or student_id. Course details are in COURSE table and student details are in STUDENT table. So tables satisfy 3NF.

Final Normalized Tables (Summary)

STUDENT

COURSE

DEPARTMENT

ENROLLMENT

RESULT

Short Conclusion

In this question I designed a small college database using ER concepts and then converted it into normalized tables up to 3NF. This reduces redundancy and makes the design easier to maintain.

How I Prepared This Assignment

In this assignment I used simple examples related to a college database system. I designed tables in a way that I can reuse the same schema for different questions like DDL, DML, constraints, joins, aggregate functions, views, procedures, triggers and cursors. I tried to keep the SQL queries and explanation in simple language matching my understanding as an MCA first semester student.

Using GitHub Copilot to Complete This File

I am using GitHub Copilot in VS Code to help me write and format the SQL queries and tables faster. I still read every query and explanation and change the wording in my own simple language so that it matches my understanding.

Steps I Follow with Copilot

1. I open this file assignment.md and the original questions file data.md side by side.

2. For each question, I select the empty section or template and then open Copilot Chat.

3. I ask Copilot in my own words, for example:

   “Use Question 1 from data.md and complete the Question 1 section in assignment.md. Use simple SQL examples for a college database, and keep the explanation in simple MCA 1st sem student language.”

4. Copilot suggests SQL code blocks and markdown tables. I check them and edit any part that looks too formal or not like my style.

5. I repeat the same process for all 17 questions.

When choosing the model inside GitHub Copilot, I prefer the latest GPT-based model (like GPT-5.1 Preview) because it formats markdown tables and SQL queries very nicely.