Scribd
Upload
13 views3 pages

SQL Assignment 4

The document outlines an SQL assignment focused on creating and managing a college database, including tables for Students, Courses, Enrollments, and Faculty. It includes tasks for Data Definition Language (DDL) to create tables, Data Manipulation Language (DML) for inserting, updating, and deleting records, Data Control Language (DCL) for user permissions, and Data Transaction Language (DTL) for managing transactions. Specific SQL commands and scenarios are provided for each category to guide the assignment completion.

Uploaded by

shreyask045
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views3 pages

SQL Assignment 4

The document outlines an SQL assignment focused on creating and managing a college database, including tables for Students, Courses, Enrollments, and Faculty. It includes tasks for Data Definition Language (DDL) to create tables, Data Manipulation Language (DML) for inserting, updating, and deleting records, Data Control Language (DCL) for user permissions, and Data Transaction Language (DTL) for managing transactions. Specific SQL commands and scenarios are provided for each category to guide the assignment completion.

Uploaded by

shreyask045
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Assignment 4

SQL Assignment: College Database

Focus: DDL, DML, DCL, DTL only

Tables:

- Students

- Courses

- Enrollments

- Faculty

DDL - Create Tables

Q1. Create a table Students with the following columns:

- student_id

- name

- department

- admission_year

Q2. Create a table Courses with the following columns:

- course_id

- course_name

- department

- credits

Q3. Create a table Enrollments with the following columns:

- enrollment_id

- student_id
- course_id

- semester

Q4. Create a table Faculty with the following columns:

- faculty_id

- name

- department

- subject_handled

DML - Insert, Update, Delete

Q5. Insert 5 records into each of the 4 tables using basic INSERT statements.

Q6. Update the department of a student.

Q7. Delete a course from the Courses table.

Q8. Update a subject handled by a faculty member.

Q9. Insert a new enrollment record for a student in a course.

DCL - Grant, Revoke

Q10. Create a user college_user and grant them INSERT and UPDATE permissions on the

Students and Enrollments tables.

Q11. Revoke UPDATE permission from college_user for the Enrollments table.

DTL - Transactions

Q12. Begin a transaction to simulate this:

- Insert a new student.

- Insert a new enrollment for that student.

- Commit the transaction.


Q13. Begin a transaction:

- Insert a course.

- Delete a faculty member.

- Rollback the transaction.

Q14. Use a SAVEPOINT:

- Insert a student.

- Create a SAVEPOINT after this step.

- Insert a course.

- Rollback to the savepoint.

- Commit the transaction.

Q15. Test what happens if you ROLLBACK after a COMMIT. Write down the observation as a

comment in your SQL file.

You might also like