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?
- Increment total seats of each society by 10%.
- Display the number of enrolled students in relation to total capacity. (correct)
- Count the number of societies with more than 5 students enrolled.
- Create a view to track society names with total enrolled students.
What modification needs to be made to the enrollment table?
What modification needs to be made to the enrollment table?
- Update date of enrollment for specific society IDs. (correct)
- Add a column for last semester grades.
- Change the field type of student names to varchar.
- Create a new table for society mentors.
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?
- Increment the total seats of each society.
- Find names of students who have paid the enrollment fees.
- Find names of students enrolled in 'Debating', 'Dancing', and 'Sashakt'. (correct)
- Find society names such that the mentor has 'Gupta' in their name.
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?
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?
What information is being requested about students in society 'NSS'?
What information is being requested about students in society 'NSS'?
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?
Which roll number format is being queried?
Which roll number format is being queried?
For which of the following courses can student details be retrieved?
For which of the following courses can student details be retrieved?
What is the maximum number of students mentioned for enrollment in societies?
What is the maximum number of students mentioned for enrollment in societies?
What is the objective of Query 10?
What is the objective of Query 10?
Which of the following queries specifically addresses the enrollment status of students?
Which of the following queries specifically addresses the enrollment status of students?
What does Query 11 require?
What does Query 11 require?
Which query would require user input to function properly?
Which query would require user input to function properly?
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?
What is being queried in Query 14?
What is being queried in Query 14?
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?
What additional task needs to be performed in Query 7?
What additional task needs to be performed in Query 7?
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?
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?
Which foreign keys are present in the ENROLLMENT table?
Which foreign keys are present in the ENROLLMENT table?
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?
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?
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?
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?
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?
Flashcards are hidden until you start studying
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.
