Database Management Systems Lab (CSE-212-F)
Experiment Solutions
Experiment 1: Create tables and specify the Questionnaires in SQL
To create tables in SQL, the `CREATE TABLE` command is used. Consider designing a database for storing
questionnaire responses.
```sql
CREATE TABLE Questionnaire (
QID INT PRIMARY KEY,
QuestionText VARCHAR(255) NOT NULL,
OptionA VARCHAR(100),
OptionB VARCHAR(100),
OptionC VARCHAR(100),
OptionD VARCHAR(100),
CorrectOption CHAR(1)
);
```
This table includes a question ID, the question itself, four options (A to D), and the correct answer. After
creating the table, data can be inserted using:
```sql
INSERT INTO Questionnaire VALUES
(1, 'What is the capital of India?', 'Delhi', 'Mumbai', 'Kolkata', 'Chennai', 'A');
```
This structure helps store and retrieve questions efficiently for quiz or test applications.
Experiment 2: To Manipulate the Operations on the Table
Manipulation involves operations like **INSERT**, **UPDATE**, **DELETE**, and **SELECT**:
- **Insert Data**:
```sql
INSERT INTO Questionnaire VALUES (2, 'Which language is used for DBMS?', 'C', 'Java', 'SQL', 'Python',
'C');
```
- **Update Records**:
```sql
UPDATE Questionnaire SET CorrectOption = 'C' WHERE QID = 2;
```
- **Delete Records**:
```sql
DELETE FROM Questionnaire WHERE QID = 2;
```
- **Select Records**:
```sql
SELECT * FROM Questionnaire;
```
These commands form the core of **Data Manipulation Language (DML)**.
Experiment 3: To Implement the Restrictions on the Table
SQL provides constraints to enforce rules. Common restrictions include:
- **NOT NULL**
- **UNIQUE**
- **PRIMARY KEY**
- **FOREIGN KEY**
- **CHECK**
Example:
```sql
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18),
Email VARCHAR(100) UNIQUE
);
```
This enforces that each student must have a unique roll number and email, a non-null name, and an age of at
least 18.
Experiment 4: To Implement the Structure of the Table
This involves table creation and altering structures. Use `CREATE TABLE` and `ALTER TABLE` commands.
- Create:
```sql
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Credits INT
);
```
- Alter:
```sql
ALTER TABLE Course ADD Instructor VARCHAR(100);
ALTER TABLE Course MODIFY Credits DECIMAL(3,1);
```
This helps adapt tables to evolving requirements.
Experiment 5: To Implement the Concept of Joins
Joins combine data from multiple tables. Types:
- **INNER JOIN**
- **LEFT JOIN**
- **RIGHT JOIN**
- **FULL OUTER JOIN**
Example:
```sql
SELECT s.Name, c.CourseName
FROM Student s
INNER JOIN Enrollment e ON s.RollNo = e.RollNo
INNER JOIN Course c ON e.CourseID = c.CourseID;
```
This fetches students along with the courses they are enrolled in.
Experiment 6: To Implement the Concept of Grouping of Data
Grouping is done using `GROUP BY`, often with aggregate functions like `COUNT()`, `AVG()`, `SUM()`.
Example:
```sql
SELECT CourseID, COUNT(*) AS TotalStudents
FROM Enrollment
GROUP BY CourseID;
```
This counts how many students are enrolled in each course.
Experiment 7: To Implement the Concept of Sub Queries
A **subquery** is a query inside another query. It can be used in `SELECT`, `WHERE`, or `FROM`.
Example:
```sql
SELECT Name FROM Student
WHERE RollNo IN (
SELECT RollNo FROM Enrollment WHERE CourseID = 101
);
```
This retrieves students enrolled in Course 101.
Experiment 8: To Implement the Concept of Indexes and Views
- **Indexes** speed up search:
```sql
CREATE INDEX idx_name ON Student(Name);
```
- **Views** provide a virtual table:
```sql
CREATE VIEW StudentCourses AS
SELECT s.Name, c.CourseName
FROM Student s
JOIN Enrollment e ON s.RollNo = e.RollNo
JOIN Course c ON e.CourseID = c.CourseID;
```
Views help simplify complex queries.
Experiment 9: To Implement the Concept of Forms and Reports
Forms and reports are GUI and output features, usually implemented in front-end tools (like MS Access or
web apps), but in SQL we can use **queries** for reports.
Example (report of students and their courses):
```sql
SELECT s.Name, c.CourseName
FROM Student s
JOIN Enrollment e ON s.RollNo = e.RollNo
JOIN Course c ON e.CourseID = c.CourseID;
```
Forms allow user input; reports display formatted outputs, often generated in applications that interface with
SQL.
