Podcast
Questions and Answers
Which query would help to display the vacant seats for each society?
Which query would help to display the vacant seats for each society?
What modification needs to be made to the enrollment table?
What modification needs to be made to the enrollment table?
Which query would be used to find the names of students who are enrolled in specific societies?
Which query would be used to find the names of students who are enrolled in specific societies?
What information is needed to count societies with more than 5 students enrolled?
What information is needed to count societies with more than 5 students enrolled?
Signup and view all the answers
Which task involves adding a new column with a default value in the student table?
Which task involves adding a new column with a default value in the student table?
Signup and view all the answers
What information is being requested about students in society 'NSS'?
What information is being requested about students in society 'NSS'?
Signup and view all the answers
Which type of information can be retrieved from the student's database schema?
Which type of information can be retrieved from the student's database schema?
Signup and view all the answers
Which roll number format is being queried?
Which roll number format is being queried?
Signup and view all the answers
For which of the following courses can student details be retrieved?
For which of the following courses can student details be retrieved?
Signup and view all the answers
What is the maximum number of students mentioned for enrollment in societies?
What is the maximum number of students mentioned for enrollment in societies?
Signup and view all the answers
What is the objective of Query 10?
What is the objective of Query 10?
Signup and view all the answers
Which of the following queries specifically addresses the enrollment status of students?
Which of the following queries specifically addresses the enrollment status of students?
Signup and view all the answers
What does Query 11 require?
What does Query 11 require?
Signup and view all the answers
Which query would require user input to function properly?
Which query would require user input to function properly?
Signup and view all the answers
What type of task is specified in the assignment posted on 6 Sept related to society records?
What type of task is specified in the assignment posted on 6 Sept related to society records?
Signup and view all the answers
What is being queried in Query 14?
What is being queried in Query 14?
Signup and view all the answers
What is the due date for the assignment related to Query 10-14?
What is the due date for the assignment related to Query 10-14?
Signup and view all the answers
What additional task needs to be performed in Query 7?
What additional task needs to be performed in Query 7?
Signup and view all the answers
What data type is used for Roll No in both the STUDENT and ENROLLMENT tables?
What data type is used for Roll No in both the STUDENT and ENROLLMENT tables?
Signup and view all the answers
Which field in the SOCIETY table specifies the total number of available seats?
Which field in the SOCIETY table specifies the total number of available seats?
Signup and view all the answers
Which foreign keys are present in the ENROLLMENT table?
Which foreign keys are present in the ENROLLMENT table?
Signup and view all the answers
What is the maximum length of the StudentName field in the STUDENT table?
What is the maximum length of the StudentName field in the STUDENT table?
Signup and view all the answers
Which of the following accurately describes the SocNameRef field in the SOCIETY table?
Which of the following accurately describes the SocNameRef field in the SOCIETY table?
Signup and view all the answers
What is the primary purpose of the DateOfEnrollment field in the ENROLLMENT table?
What is the primary purpose of the DateOfEnrollment field in the ENROLLMENT table?
Signup and view all the answers
Which of the following is a characteristic of the DATA type used for the DOB field in the STUDENT table?
Which of the following is a characteristic of the DATA type used for the DOB field in the STUDENT table?
Signup and view all the answers
Which field type would you expect in a field named 'MentorName' based on its description?
Which field type would you expect in a field named 'MentorName' based on its description?
Signup and view all the answers
Study Notes
Database Creation
-
Create a Student-Society database schema for a college
-
Database has three tables: Student, Society, and Enrollment
-
Tables contain the following fields:
- Student: Roll No, StudentName, Course, DOB
- Society: SocID, SocName, MentorName, TotalSeats
- Enrollment: Roll No, SID, DateOfEnrollment
-
Rollno and SID are foreign keys
Queries
- Query 1: Retrieve names of students enrolled in any society
- Query 2: Retrieve all society names
- Query 3: Retrieve student's names starting with the letter 'A'.
- Query 4: Retrieve student's details studying in course 'Computer Science' or 'Chemistry'
- Query 5: Retrieve student's names who roll number starts with 'X' or 'Z' and ends with '9'
- Query 6: Find society details with more than N TotalSeats where N is user input
- Query 7: Update the society table for the mentor name of a specific society
- Query 8: Find society names in which more than five students have enrolled
- Query 9: Find the name of the youngest student enrolled in society 'NSS'
- Query 10: Find the name of the most popular society (on the basis of enrolled students)
- Query 11: Find the name of two least popular societies (on the basis of enrolled students)
- Query 12: Find the students names who are not enrolled in any society
- Query 13: Find the students names enrolled in at least two societies
- Query 14: Find society names in which maximum students are enrolled
- Query 15(a): Find names of all students who have enrolled in any society.
- Query 15(b): Find the society names in which at least one student has enrolled.
- Query 16: Find names of students who are enrolled in any of the three societies 'Debating', 'Dancing' and ‘Sashakt’.
- Query 17: Find society names such that its mentor has a name with 'Gupta' in it.
- Query 18: Find the society names in which the number of enrolled students is only 10% of its capacity.
- Query 19: Display the vacant seats for each society
- Query 20: Increment Total Seats of each society by 10%.
- Query 21: Add the enrollment fees paid ('yes'/'No') field in the enrollment table
- Query 22: Update date of enrollment of society id 'sl' to 2018-01-15', 's2' to the current date and 's3' to “2018-01-02'
- Query 23: Create a view to keep track of society names with the total number of students enrolled in it
- Query 24: Find student names enrolled in all the societies
- Query 25: Count the number of societies with more than 5 students enrolled in it
- Query 26: Add column Mobile number in student table with default value 9999999999’
Assignments & Deadlines
-
Assignment 1: Create the Student-Society database and insert at least five records into each table
- Due: September 15th, 12:01 PM
-
Assignment 2: Complete Queries 1-5
- Due: September 15th, 12:01 PM
-
Assignment 3: Complete Queries 6-9
- Due: September 15th, 11:59 PM
-
Assignment 4: Complete Queries 10-14
- Due: September 29th, 11:59 PM
-
Assignment 5: Complete Queries 15-18
- Due: October 13th, 11:59 PM
-
Assignment 6: Complete Queries 19-26
- Due: October 17th, 11:59 PM
References
- Elmasri, R., Navathe, B.S.F. Fundamentals of Database Systems (6th Edition), Pearson Education, 2011
- Murach, J. Murach's MySQL, Murach, 2013
- Guidelines for assignments
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Related Documents
Description
This quiz covers the creation of a Student-Society database schema and various queries related to student enrollment. You'll need to understand table structure, foreign keys, and how to retrieve or manipulate data effectively in a database. Put your SQL skills to the test with scenarios including student details and society information.
