Practice for DDL and DML Commands

Objective

This assignment will help you practice and master:

DDL (Data Definition Language)

• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME

DML (Data Manipulation Language)

• INSERT
• UPDATE
• DELETE
• MERGE

You will also practice constraints, keys, and real-world data operations.

---

Part 1: Database Design Scenario

Create a College Management System Database with the following tables:

1. Students
2. Courses
3. Faculty
4. Enrollments

---

Part 2: DDL Practice

Task 1: Create Tables

Create the following tables with appropriate data types.

STUDENTS

Column	Type	Constraint
student_id	NUMBER	Primary Key
first_name	VARCHAR2(30)	NOT NULL
last_name	VARCHAR2(30)	NOT NULL
email	VARCHAR2(50)	UNIQUE
phone	VARCHAR2(15)
admission_date	DATE

---

COURSES

Column	Type	Constraint
course_id	NUMBER	Primary Key
course_name	VARCHAR2(50)	NOT NULL
credits	NUMBER
department	VARCHAR2(50)

---

FACULTY

Column	Type	Constraint
faculty_id	NUMBER	Primary Key
faculty_name	VARCHAR2(50)
department	VARCHAR2(50)
salary	NUMBER

---

ENROLLMENTS

Column	Type	Constraint
enrollment_id	NUMBER	Primary Key
student_id	NUMBER	Foreign Key
course_id	NUMBER	Foreign Key
enrollment_date	DATE

---

Task 2: Add Constraints (ALTER)

Perform the following:

1. Add FOREIGN KEY from student_id in enrollments referencing students.
2. Add FOREIGN KEY from course_id referencing courses.
3. Add CHECK constraint for credits (between 1 and 5).
4. Add DEFAULT value for admission_date.

---

Task 3: Modify Table Structure

Perform the following operations:

1. Add a column gender to students.
2. Add column hire_date to faculty.
3. Modify salary column to allow larger values.
4. Rename column phone to contact_number.
5. Drop column gender.

---

Task 4: Table Management

Practice the following commands:

1. Rename table faculty to professors.
2. Truncate table enrollments.
3. Drop table professors.

---

Part 3: DML Practice

Task 5: Insert Data

Insert at least 5 records in each table.

Example:

Students
Courses
Faculty
Enrollments

Use both:

1. Single row insert
2. Multiple row insert
3. Insert using subquery

---

Task 6: Update Records

Perform the following updates:

1. Change student phone number.
2. Increase faculty salary by 10%.
3. Change course credits for a specific course.
4. Update department name.

---

Task 7: Delete Records

Practice different delete scenarios.

1. Delete a specific student.
2. Delete courses with less than 2 credits.
3. Delete enrollments of a specific course.
4. Delete all records from a table without dropping the structure.

---

Task 8: MERGE Statement

Create a new table:

new_students

Insert some records in it.

Now use MERGE to:

• Update existing students
• Insert new students

---

Part 4: Advanced DML Practice

Task 9: Insert Using Subquery

Insert records into enrollments using a subquery from students and courses.

---

Task 10: Update Using Subquery

Update student admission date using a subquery condition.

---

Task 11: Delete Using Subquery

Delete enrollments where the course belongs to a specific department.

---

Part 5: Practice Queries (Real World Tasks)

Perform the following:

1. Add a NOT NULL constraint to course_name.
2. Add a UNIQUE constraint to faculty email.
3. Create a table backup_students using:

CREATE TABLE backup_students AS SELECT * FROM students;

4. Insert records into backup_students from students.
5. Drop backup_students table.

---

Part 6: Challenge Tasks (For Mastery)

Try the following advanced exercises.

1. Create a table using CREATE TABLE AS SELECT.
2. Add multiple constraints in a single ALTER command.
3. Write a MERGE statement that:
   • updates matching rows
   • inserts non-matching rows
4. Write a script to:
   • create tables
   • insert data
   • update records
   • delete records

---

Expected Learning Outcomes

After completing this assignment, you should be able to:

✔ Create and modify tables
✔ Apply constraints correctly
✔ Insert, update, and delete data
✔ Use MERGE operations
✔ Modify table structure
✔ Manage tables in Oracle SQL