Open a new text file in Notepad or a similar text editing tool. Write a SQL comm
ID: 3637752 • Letter: O
Question
Open a new text file in Notepad or a similar text editing tool.
Write a SQL command to create a table called "STUDENT". Use the same fields you described in the previous assignment. Choose a suitable primary key.
Write a SQL command to create another table called COURSES. You need three fields course_id, course_name field, and description. Choose a suitable primary key.
Write a SQL command to create a third table called REGISTRATION. In this table we will record the student's registration information. You will need student_id, and course_id. Also add a date field to record the registration date. See if you can find a good primary key for this table. Maybe a compound key?
Insert two records in the STUDENT table, and four records in the COURSES table. For the third course inserted, do not insert any description information.
Write an UPDATE statement to UPDATE the description of the third course inserted to the actual description of the course.
Now write a DELETE statement to delete the second course inserted. You will have two students and three courses left.
In the REGISTRATION table, add two records for the first student showing that he/she has registered for the first two courses. Add two more records to show that the second student has registered for the first and the third courses.
Use a single SELECT command for each of the following situations to display the information requested:
The first and last names of the students that are registered in course #1
The first and last names of the students that are not registered in course #2
The first and last names of the students along with how many courses they have taken.
The course names along with how many students have registered for those courses
The name of courses with more that one students registered in them.
If you write the SQL statements without any problem, you are a genius! But if you are like most of us that has a problem, then you can do the following:
Read this article that explains GROUP BY and all the cool functions that come with it: http://www.techonthenet.com/sql/group_by.php
Explanation / Answer
Step1: CREATE TABLE STUDENT (student_id CHAR(9), firstname VARCHAR(20), lastname VARCHAR(20), age INTEGER, PRIMARY KEY (student_id) ); Step2: CREATE TABLE COURSES (course_id CHAR(9), course_name VARCHAR(20), description VARCHAR(50), PRIMARY KEY (course_id) ); Step3: CREATE TABLE REGISTRATION (student_id CHAR(9), course_id CHAR(9), reg_date DATE, PRIMARY KEY (student_id,course_id) ); Step4: INSERT INTO STUDENT values('S01','JOHN','SANDERS',14); Step5: INSERT INTO STUDENT values('S11','BRUCE','MILLER',13); Step6: INSERT INTO COURSES values('M101','MATH 101','Basic Math'); Step7: INSERT INTO COURSES values('M201','MATH 201','Advance Math'); Step8: INSERT INTO COURSES values('C101','COMP SCIENCE 101',NULL); Step9: INSERT INTO COURSES values('C201','COMP SCIENCE 201','Advance Computers course'); Step10: UPDATE COURSES set description='Basic Computers course' WHERE course_id='C101'; Step11: DELETE from COURSES WHERE course_id='M201'; Step12: INSERT INTO REGISTRATION VALUES('S01','M101','30-JAN-2012'); Step13: INSERT INTO REGISTRATION VALUES('S01','C101','30-JAN-2012'); Step14: INSERT INTO REGISTRATION VALUES('S11','M101','30-JAN-2012'); Step15: INSERT INTO REGISTRATION VALUES('S11','C201','30-JAN-2012'); Step16: SELECT firstname,lastname FROM STUDENT WHERE student_id in(SELECT student_id from REGISTRATION where course_id='M101'); Step17: SELECT firstname,lastname FROM STUDENT WHERE student_id not in(SELECT student_id from REGISTRATION where course_id='C101'); Step18: SELECT firstname,lastname,num_courses FROM STUDENT,(SELECT student_id,count(course_id) num_courses from REGISTRATION group by student_id) R WHERE STUDENT.student_id=R.student_id; Step19: SELECT course_name,num_students FROM COURSES,(SELECT course_id,count(student_id) num_students from REGISTRATION group by course_id having count(student_id)>1 ) R WHERE COURSES.course_id=R.course_id; ---EXECUTED--- * In steps 1,2 & 3 you missed a comma. * Insertions if done simultaneously, leads to error and hence sequential insertion of records is practiced. * In the 19th step, "num_students" must also be included in the primary select statement. * Step19 result contains only courses having more than one student, else if all the courses are to be displayed with the count of students, this may be replaced instead. SELECT course_name FROM COURSES,(SELECT course_id,count(student_id) num_students from REGISTRATION group by course_id ) R WHERE COURSES.course_id=R.course_id;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.