4-5. List the junior level COSC courses (like COSC3xxx) and thename of the course. Use the Course table.
4-6. Using the COUNT feature, determine whether there are duplicatenames or student numbers in the Student table.
4-7. Assume all math courses start with MATH. How many math coursesare there in the Section table? From the COUNT of courses, does itappear there are any math courses in the Section table not in theCourse table? Again, using COUNTs, are there any math courses inthe Course table not in the Section table? Does it appear there areany courses at all in the Grade_report, Section, or Course tablesnot in the others? (We will study how to ask these questions in SQLin a later chapter). A query like the following would not work:
SELECT g.section_id
FROM Grade_report g, Section t
WHERE g.section_id <> t.section_id;
Explain why WHERE .. <> .. will not work to produce thedesired output.
4-8. Display dictionary views for the tables we have in theStudent-Course database (refer to the diagram in Appendix 3 for allthe table names). Use “All_tables†as the dictionary view. Do thequery as follows:
a. DESCRIBE the table with DESC All_tables;
b. Display the number of rows in All_tables. Use SELECT COUNT(*)FROM All_tables; When you are exploring the dictionary, it is not agood idea to simply SELECT * FROM whatever, where whatever is somedictionary view. Dictionary views are often long and wide --widebecause there are often many attributes and many of thoseattributes are not necessarily interesting.
c. Display the owner and table_name from All_tables where owner =‘your userid’.
CREATE THE TABLE FOR only for Student, GradeReport, and SectionQUESTION 4-9.
4-9. For all the tables in the Student-Course database--Student, Grade_report, Section, Room, Course, Prereq,Department_to_major, list the attributes, number of rows, number ofdistinct rows, and number of rows without nulls. As you gather theinformation, put the information in a tabular format, as shownbelow (Note, you may want to create this table in your wordprocessor as you gather the information.)
Table Attribute Rows Distinct Rows Rows without Nulls Studentstno
48
sname major
class Section section_id etc.
Also, there is probably no “one†query to give you thisinformation. You will have to fi nd this information using separatequeries, and then put the information together in a tabular format.Hint: You can use:
SELECT COUNT (*) FROM Student WHERE sname IS NULL
