README
DSC-11 Database Management Systems
Welcome to the collection of practical assignments for the Database Management Systems course.
📅 List of Practical
1) Retrieve names of students enrolled in any society.
2) Retrieve all society names.
3) Retrieve student's names starting with the letter ‘A’.
4) Retrieve student's details studying in courses ‘computer science’ or ‘chemistry’.
5) Retrieve student's names whose roll number either starts with ‘X’ or ‘Z’ and ends with ‘9’.
6) Find society details with more than N TotalSeats where N is to be input by the user.
7) Update society table for the mentor name of a specific society.
8) Find society names in which more than five students have enrolled.
9) Find the name of the youngest student enrolled in society ‘NSS’.
10) Find the name of the most popular society (on the basis of enrolled students).
11) Find the name of two least popular societies (on the basis of enrolled students).
12) Find the students names who are not enrolled in any society.
13) Find the students names enrolled in at least two societies.
14) Find society names in which maximum students are enrolled.
15) Find names of all students who have enrolled in any society and society names in which at least one student has enrolled.
16) Find names of students who are enrolled in any of the three societies ‘Debating’, ‘Dancing’, and ‘Sashakt’.
17) Find society names such that its mentor has a name with ‘Gupta’ in it.
18) Find the society names in which the number of enrolled students is only 10% of its capacity.
19) Display the vacant seats for each society.
20) Increment Total Seats of each society by 10%.
21) Add the enrollment fees paid (‘yes’/’No’) field in the enrollment table.
22) Update date of enrollment of society id ‘s1’ to ‘2018-01-15’, ‘s2’ to the current date, and ‘s3’ to ‘2018-01-02’.
23) Create a view to keep track of society names with the total number of students enrolled in it.
24) Find student names enrolled in all the societies.
25) Count the number of societies with more than 5 students enrolled in it.
26) Add column Mobile number in student table with default value ‘9999999999’.
27) Find the total number of students whose age is > 20 years.
28) Find names of students who were born in 2001 and are enrolled in at least one society.
29) Count all societies whose name starts with ‘S’ and ends with ‘t’ and at least 5 students are enrolled in the society.
30) Display society name, mentor name, total capacity, total enrolled, and unfilled seats.
🛠️ Database Administration Commands
- Create user
- Create role
- Grant privileges to a role
- Revoke privileges from a role
- Create index
📊 Database Schema
STUDENT
| Roll No | StudentName | Course | DOB |
|---|---|---|---|
| Char(6) | Varchar(20) | Varchar(10) | Date |
SOCIETY
| SocID | SocName | MentorName | TotalSeats |
|---|---|---|---|
| Char(6) | Varchar(20) | Varchar(15) | Unsigned int |
ENROLLMENT
| Roll No | SID | DateOfEnrollment |
|---|---|---|
| Char(6) | Char(6) | Date |
NOTE: RollNo (ENROLLMENT) and SID (ENROLLMENT) are foreign keys referencing STUDENT and SOCIETY tables respectively.
<p align="right">
<i>Developed with ❤️ by <a href="https://github.com/16ratneshkumar">16ratneshkumar</a></i>
</p>