Section 2.12. Exercises

Unless specified otherwise, use the Student_course database to answer the following questions.

In writing out all the following queries, use table and column aliases wherever you feel that it would improve the readability of your output. Follow the conventions for writing SQL statements. Also, for future reference, you may want to get into the practice of saving your queries by question number. For example, save the query you write for Question 2-2a as query2-2a. Print the query and your results.

Refer to Appendix A for a complete listing of all tables (and their columns) available in the Student_course database.

The Student_course database used in this book has the following tables: Student, Dependent, Course, Section, Prereq (for prerequisite), Grade_report, Department_to_major, and Room.
1. Display the data from each of these tables by using the simple form of the SELECT * statement.
2. Display the first five rows from each of these tables.
3. Display the student name and student number of all students who are juniors (hint: class = 3).
4. Display the student names and numbers (from question 2) in descending order by name.
5. Display the course name and number of all courses that are three credit hours.
6. Display all the course names and course numbers (from question 3) in ascending order by course name.
Display the building number, room number, and room capacity of all rooms in descending order by room capacity. Use appropriate column aliases to make your output more readable.
Display the course number, instructor, and building number of all courses that were offered in the Fall semester of 1998. Use appropriate column aliases to make your output more readable.
List the student number of all students who have grades of C or D.
List the offering_dept of all courses that are more than three credit hours.
Display the student name of all students who have a major of COSC.
Find the capacity of room 120 in Bldg 36.
Display a list of all student names ordered by major.
Display a list of all student names ordered by major, and by class within major. Use appropriate table and column aliases.
Count the number of departments in the Department_to_major table.
Count the number of buildings in the Room table.
What output will the following query produce?
```
SELECT COUNT(class)
FROM Student
WHERE class IS NULL
```
Why do you get this output?
Use the BETWEEN operator to list all the sophomores, juniors, and seniors from the Student table.
Use the NOT BETWEEN operator to list all the sophomores and juniors from the Student table.
Create synonyms for each of the tables available in the Student_course database. View your synonyms in the Object Explorer.