Create a Students table which has the following columns: student id, student name, email address, gender, status, GPA, age. The student id can be an integer between 100 and 999. The name is limited to 10 characters. The email address is limited to 25 characters. Gender is a single letter, either m or f. Status is two characters from the set {Fr, So, Ju, Se}. GPA is a real number with one digit to the right of the decimal place between 0 and 4 inclusive. Age is an integer between 15 and 30, inclusive. The number of clubs is an integer greater than or equal to zero. The GPA and age should have default values of zero. No null values are allowed for the any of the columns. The student number is the primary key. Email is an alternate key. There are no foreign keys.
Create a Clubs table which has the following columns, club id, club name, meeting room, number of club members, and contact email. The club id is a string of six characters; all club ids begin with the letter C followed by digits. The club name is limited to 10 characters. The meeting room is a three digit integer. The number of club members is an integer. The email address is limited to 25 characters. There are no default values. No null values are allowed except for the email and number of members columns. The club id is the primary key. The email column is a foreign key reference to the email column in Students.
The club members tables has the following columns: the club id, the student id, and the date joined. The club id has the same type as the club id in the Clubs table. The student id column has the same type as the student id in the Students table. The date joined column is a date. No null values are allowed in any column. The primary key is all three columns. The club id column is a foreign key reference to the club id column in Clubs table. The student id column is a foreign key reference to the student id column in the Students table.
Write the insert statement to populate the students table. The data will be provided by the instructor.
Write the insert statement to populate the clubs table. The data will be provided by the instructor.
Write the insert statement to populate the clubs table. The data will be provided by the instructor.
Write the update statements needed to set the number of members column to the correct value for each club. Use a select query to count the number of members the club has from the club members table and assign this value to the number of members column. There should be an update statement for each club.
Write a select query that produces a list of student names.
Write a select query that produces a list of student names and their GPAs.
Write a query which lists the name of the student to contact for each club along with the club name
Write a select query that produces a list of student names and their GPAs for students having a GPA below 2.0.
Write a select query that lists the names of clubs which have members who are freshman. Use natural joins on the student, club members However, the Clubs table has email and cid columns; you only want to join Clubs using the cid column. The club names should be listed only once. Order the club names alphbetically.
Create a view which uses the query in 11. Write a select query to display the view.
Create a view which shows only clubs with members. Write a select query to display the view.
Write a select query that returns the average number of members in the clubs.
Write a select query which lists a student’s name only if that student is in the Debate club. Use the hint from problem 11.
Write a select query which shows the highest GPA in each the the student statuses (‘Fr’, ‘So’, ‘Ju’, ‘Se’).
Modify the query above to show a class’s highest GPA only if it is greater the 3.5.
Write a query which lists the student names who are members of none of the clubs.
Use the query from 19. in the where part of a delete statement to delete students from the student table who are not members of any club.