Database Creation and Queries for Student-Society
26 Questions
0 Views

Database Creation and Queries for Student-Society

Created by
@HeroicNarrative8754

Podcast Beta

Play an AI-generated podcast conversation about this lesson

Questions and Answers

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?

  • 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?

  • 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?

    <p>The enrollment data of students in all societies.</p> Signup and view all the answers

    Which task involves adding a new column with a default value in the student table?

    <p>Add mobile numbers with a default value of 9999999999.</p> Signup and view all the answers

    What information is being requested about students in society 'NSS'?

    <p>The name of the youngest student.</p> Signup and view all the answers

    Which type of information can be retrieved from the student's database schema?

    <p>Details of students enrolled in a specific course.</p> Signup and view all the answers

    Which roll number format is being queried?

    <p>Starts with 'X' or 'Z' and ends with '9'.</p> Signup and view all the answers

    For which of the following courses can student details be retrieved?

    <p>Computer Science or Chemistry.</p> Signup and view all the answers

    What is the maximum number of students mentioned for enrollment in societies?

    <p>No limit specified.</p> Signup and view all the answers

    What is the objective of Query 10?

    <p>Find the name of the most popular society based on enrolled students.</p> Signup and view all the answers

    Which of the following queries specifically addresses the enrollment status of students?

    <p>Query 12.</p> Signup and view all the answers

    What does Query 11 require?

    <p>Finding the names of two least popular societies based on enrolled students.</p> Signup and view all the answers

    Which query would require user input to function properly?

    <p>Query 6.</p> Signup and view all the answers

    What type of task is specified in the assignment posted on 6 Sept related to society records?

    <p>Insert at least 5 records in each table.</p> Signup and view all the answers

    What is being queried in Query 14?

    <p>Society names in which the maximum number of students are enrolled.</p> Signup and view all the answers

    What is the due date for the assignment related to Query 10-14?

    <p>29 Sept.</p> Signup and view all the answers

    What additional task needs to be performed in Query 7?

    <p>Update the mentor name in the society table.</p> Signup and view all the answers

    What data type is used for Roll No in both the STUDENT and ENROLLMENT tables?

    <p>Char(6)</p> Signup and view all the answers

    Which field in the SOCIETY table specifies the total number of available seats?

    <p>TotalSeats</p> Signup and view all the answers

    Which foreign keys are present in the ENROLLMENT table?

    <p>Roll No and SocID</p> Signup and view all the answers

    What is the maximum length of the StudentName field in the STUDENT table?

    <p>20 characters</p> Signup and view all the answers

    Which of the following accurately describes the SocNameRef field in the SOCIETY table?

    <p>It is a string with a maximum length of 20 characters.</p> Signup and view all the answers

    What is the primary purpose of the DateOfEnrollment field in the ENROLLMENT table?

    <p>To track the date a student enrolls in a society.</p> 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?

    <p>It stores only the date without time.</p> Signup and view all the answers

    Which field type would you expect in a field named 'MentorName' based on its description?

    <p>Varchar(15)</p> 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.

    Quiz Team

    Related Documents

    GE-DBMS 2024.pdf

    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.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser