Lab 5

Unless specified otherwise, use the Student_course database to answer the following questions. Also, use appropriate column headings when displaying your output.

1. Display the COUNT of tuples (rows) in each of the tables Grade_report, Student, and Section. How many rows would you expect in the Cartesian product of all three tables? Display the COUNT (not the resulting rows) of the Cartesian product of all three and verify your result (use SELECT COUNT(*) ...).

2. Display the COUNT of section-ids from the Section table. Display the COUNT of DISTINCT section-ids from the Grade_report table. What does this information tell you? (Hint: section_id is the primary key of the Section table.)

3. Write, execute, and print a query to list student names and grades (just two attributes) using the table alias feature. Restrict the list to students that have either As or Bs in courses with ACCT prefixes only.

   Here's how to complete this problem:

   1. Get the statement to work as a COUNT of a join of the three tables, Student, Grade_report, Section. Use table aliases in the join condition. Note that a join of n tables requires (n - 1) join conditions, so here you have to have two join conditions: one to join the Student and Grade_report tables, and one to join the Grade_report and Section tables. Note the number of rows that you get (expect no more rows than is in the Grade_report table). Why do you get this result?

   2. Modify the query and put the Accounting condition in the WHERE clause. Note the number of rows in the resultit should be a good bit less than in question 3a.

   3. Again, modify the query and add the grade constraints. The number of rows should decrease again. Note that if you have WHERE x and y or z, parentheses are optional, but then the criteria will be interpreted according to precedence rules.

   The reason that we want you to "start small" and add conditions is that it gives you a check on what you ought to get and it allows you to output less nonsense. Your minimal starting point should be a count of the join with appropriate join conditions.

4. Using the Student table, answer the following questions:

   1. How many students have names like Smith?

   2. How many have names that contain the letter sequence Smith?

   3. How many student names end in LD?

   4. How many student names start with S?

   5. How many student names do not have "i" as the second letter?

   6. Would SELECT * FROM Student WHERE sname LIKE 'Smith%' find someone whose name is:

      1. LA SMITH

      2. SMITH-JONES

      3. SMITH JR.

      4. SMITH, JR

5. Using the Course table, answer the following questions:

   1. List the junior-level COSC courses (LIKE COSC3xxx) and the name of the courses.

   2. List all the courses except the junior-level COSC courses (use NOT LIKE).

6. Using the COUNT feature, determine whether there are duplicate names or student numbers in the Student table.

7. Assume that all math courses start with MATH. How many math courses are there in the Section table? From the count of courses, does it appear that there any math courses in the Section table that are not in the Course table? Again, using COUNTs, are there any math courses in the Course table that are not in the Section table? Does it appear that there are any courses at all that are in the Grade_report, Section, or Course tables that are not in the others? (We will study how to ask these questions in SQL in a later chapter.) Note that 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 the desired output.

8. For every table in the Student_course database, we would like to compile the following information: attributes, number of rows, number of distinct rows, and rows without nulls. Find this information using different queries and compile the information in a table as shown here:

   Table	Attribute	Rows	Distinct Rows	Rows without Nulls
   Student	Stno	48	48	48
   	Sname	48	47	48
   	Major	48	8
   	Class	etc.	etc.	etc.
   Section	Section_id	etc.	etc.	etc.

   
   

   The other tables in the Student_course database are Grade_report, Dependent, Section, Room, Course, Prereq, and Department_to_major.

   Hint: You can use the following query:

       SELECT COUNT(*)
      FROM Student
      WHERE sname IS NULL
   

   
   

9. Find the count, sum, average, minimum, and maximum capacity of rooms in the database. Format the output using the STR function.

   1. Where there is a null value for the capacity, assume the capacity to be 40, and find the average room size again.

10. Using the Student table, display the first 10 rows with an appended initial. For the appended initial, choose the halfway letter of the name, so that if a name is Evans, the initial is A (half of the length +1). If the name is Conway, the initial is W (again, (half of the length +1)). You do not need to round up or down, just use (LEN(Name)/2)+1 as the starting place to create the initial. Use appropriate column aliases. Your result should look like this (actual names may vary depending on the current database):

        PERSON#   NAMES
       --------- ------------------------
       1         Lineas, E.
       2         Mary, R.
       3         Brenda, N.
       4         Richard, H.
       5         Kelly, L.
       6         Lujack, A.
       7         Reva, V.
       8         Elainie, I.
       9         Harley, L.
       10        Donald, A.
    

    
    

    1. Display the preceding output in all capital letters.

11. Find the names of the bottom 50 percent of the students, ordered by grade.

    1. Find the names of the top 25 percent of the seniors, ordered by grade.

    2. Now use the WITH TIES option with part (b). Is there any difference in the output?

12. Count the number of courses taught by each instructor.

    1. Count the number of distinct courses taught by each instructor.

13. Count the number of classes each student is taking.

14. Display all the names that are less than five characters long from the Student table.

15. List all the students with student numbers in the 140s range.

16. Find all the students (the student names should be listed only once) who received As and Bs.

17. Would you call TOP an aggregate function? Why or why not?

18. Add an asterisk (*) to the names of all juniors and seniors who received at least one A. (This question will take a few steps, and you will have to approach this problem in a step-by-step manner.)

19. In this chapter, we used a table called Employee. Add a birthdate column and an employment_date column to the Employee table. Insert values into both the columns.

    1. Display the current ages of all the employees.

    2. Find the youngest employee.

    3. Find the oldest employee.

    4. Find the youngest employee at the time of employment.

    5. Find the oldest employee at the time of employment.

    6. Add five years to the current ages of all employees. Will any of the employees be over 65 in five years?

    7. List the birth months and names of all employees.