Lab Experience 19 - Modified
Structured Query Language
Objectives
· Work with basic SQL Select queries on a single database table
· Develop a column computed from existing columns
· Use aggregate function to get summary data from database table
· Make SQL queries with multiple, related tables of a database
Background
In Section 13.3 of the textbook, you are introduced to database management systems and the standard query language, Structured Query Language (SQL), which is commonly used to construct queries to extract data from such a database. In this lab session, you will create and test SQL queries with a database containing data related to members of our class.
Since most lab installations are equipped with database systems, we will work with an actual database system rather than a simulation. The examples shown here are based on the Microsoft Access (Office 2007) package. Access provides several tools for generating queries for the database. However, in this lab, we focus on working directly with SQL.
For this lab you will be provided with an Access database called CS101.mdb. On your H drive, make a folder called SQL. Now go to the Handouts folder within the course folder and copy the database to the SQL folder you just created. To begin, you should open this database by double clicking on it. If the Tables are not listed in the left pane, use the drop down list to select Tables as in figure 1.1. You should now have a window similar to that of Figure 19.2.
Notice that there are six tables listed – Students, Majors, Interests, Faculty, StudentMajor and StudentInterest. The Student table contains the basic student ID (primary key), name, birth date, class year, hometown and faculty advisor for the students of our class. The Majors table has an ID number for the major, the name of the major, the department offering the major and the ID for the chair of the department. The Interests table contains information on interests of class members – sports teams, organizations, political figures, hobbies, etc. It contains an ID number, a name of the interest, the major category of the interests and possibly a web address of a special site related to the interest. The Faculty table contains an ID number, name, phone extension and email address. The records of the StudentInterest table consist of a student ID and an interest ID indicating that the given student has the given interest. In a similar way, the StudentMajor table relates students to majors.
Figure 19. 1 Selecting Tables tab Figure 19.2 CS101 Database Tables
Exercise 19.1. The Students table – the data
Now open the Students table by either dragging it into the work area or double clicking on it. You should now see the data currently in the Students table as in Figure 19.3. Scan the data to become familiar with the kind of data being stored. In this view you would be able to add, delete or modify data in the table.
Figure 19.3 The Students Table
Exercise 19.2. The Students table – the fields and data types
You should now click on the Design View icon in the upper left corner. You should now see the various fields of the table together with their data types. In this view you would be able to add, delete or modify columns for the table. Notice that the first and last names are stored in separate fields. This enables us to order query results or reports alphabetically by last name and to choose to present names in either order. It is also common practice to store birth dates rather than ages since ages change once a year and thus cause unnecessary data maintenance. Ages can be computed from the birth dates when needed.
Now, close the Students table and select the Queries option from the drop down list on the left. For each of the queries you create in this lab, you should work with the SQL view in Access. To get to that view, click the Create tab at the top. Then in the Other group, select Query Design. Now close the Show Table dialog box. Now click on the SQL View icon in the upper left corner.
Exercise 19.3. First query – selecting columns from table
In the Query View window, edit the SELECT statement shown to read
SELECT StudentID, FirstName, LastName, City, State, BirthDate
FROM Students;
Recall that the SELECT clause specifies the columns that we want from the table(s), and the FROM clause specifies the table(s) to be used. Now Run icon in the upper left corner. The results should appear similar to Figure 19.4.
Figure 19.4 Selected columns from Students table
Exercise 19.4. Getting parts of a Date/Time value
With Access, we are able to extract the various parts of a date/time field when needed. To see this, go back to SQL view and edit the Select statement to read
SELECT StudentID, FirstName, LastName, City, State, DATEPART("yyyy",BirthDate) As Year
FROM Students;
The DATEPART function is used to extract parts from the field. The “yyyy” indicates that we want only the year part (“d” day and “m” for month). The clause “As Year” indicates that we want the resulting column to have “Year” as the heading. Test the results by running the query.
Exercise 19.5. Computing the Students’ ages
To compute the age of a student, we would subtract the birthdate from the current date. Various implementations of SQL provide slightly different methods for obtaining the current date. With Access, this is given by the Date function. Now go back into SQL view and change the SQL statement to
SELECT FirstName, LastName, City, State, DATE()-BirthDate AS Age
FROM Students;
and then run the query. As you see, there is a problem with this query – the result is giving the number of days the student has been alive rather than the number of years. To correct this, change the computation to be
(Date() – BirthDate)/365
and run again. We’re getting closer, but we probably prefer to have the age expressed as an integer. In Access we can get the desired result by editing the computation to
Int((Date() – BirthDate)/365)
Make this change and test again. You should get results similar to Figure 19.5. Check your own age to see if it is correct.
Figure 19.5 Computed Age column
Exercise 19.6. Choosing rows from the table
Recall that the WHERE clause in an SQL statement specifies conditions that must be met in order for a row to appear in the result of the query. Now return to SQL View and add the clause
WHERE State = “TX”
after the FROM clause. When you run the query now, you should only get rows for the students from Texas. If you are not from Texas, change the query to find students from your state.
Exercise 19.7. A query with two tables
If you have not done so, you should scan the fields and data of the other tables now. The information here is separated into the various tables to avoid considerable redundancy. For example, the information about a particular major should not appear with every student having that major since this would waste a lot of storage space, and, more importantly, would create a nightmare in terms of maintaining the data. For example, a slight change such as the chair of the department of the major would require making the change in the records of all students having that major. If we tried to store the interests of the student inside the student record, we would not know how many fields to allot for these interests. The result would likely be that we would allot many more than needed, most of which would remain empty for most students. You will see how the relational model allows us to bring together related data from different tables. Suppose we want a query to show students’ names together with the names of their primary advisors. Note that this data comes from two tables, Students and Faculty. The key is that these are related tables. They are related via the AdvisorID field in the Student table containing the FacultyID number of the Advisor in Faculty table (AdvisorID is foreign key in Student table). To see how this works, back in the query SQL View, enter the statement
SELECT S.LastName AS StudentLastName,S.FirstName AS StudentFirstName,
F.LastName AS AdvisorLastName, F.FirstName AS AdvisorFirstName
FROM Students S, Faculty F
WHERE AdvisorID = FacultyID;
and run the query. Note that we must qualify the field name with the table name where both tables have a field with the given name. In this example, the WHERE clause is being used to “join” the two tables, combining rows from the two tables that agree in the AdvisorID and FacultyID fields of the two tables.
Exercise 19.8. Ordering the rows of a query result
The ORDER BY clause of an SQL query allows us to have the result sorted by the values of one or more fields. For the query of Exercise 19.7, suppose we would like to have the results show all of the students with a given advisor grouped together. In SQL View, add the clause
ORDER BY F.LastName, F.FirstName
after the WHERE clause and run the query again. To see this result with the students with a given advisor listed in alphabetical order, change the clause to
ORDER BY F.LastName, F.FirstName, S.LastName, S.FirstName
Exercise 19.9. Combining three tables
Suppose now that we would like to see student names along with the names and categories of interests that the student has. In order to link a student with an interest, we must use the intermediate table StudentInterest. This table uses the StudentID field to link to a student and the InterestID field to link to an interest. In SQL View change the statement to
SELECT FirstName, LastName, InterestName, Category
FROM Students S, Interests I, StudentInterest SI
WHERE S.StudentID = SI.StudentID AND SI.InterestID = I.InterestID
ORDER BY LastName, FirstName;
The result should be as shown in Figure 19.6.
Figure 19.6 The three table query
SQL Lab Exercises
For each of the following exercises, you should create and test the query using the SQL mode in Access. Make a text document, using any text editor (Wordpad), into which you will copy all of your queries for printing. When the query is working correctly, copy the SQL for the query (select text, copy, paste) into your text document and give the exercise number. Also, save the query in Access with a name of Query followed by a dash, followed by the exercise number, for example Query-19-11 (You will need to use dashes rather than periods). When you have completed the entire set of exercises, print and turn in the text document.
Exercise 19.10. Students, cities and states
Create a query that lists the student names, their home city and home state. The list should be organized by state (alphabetical), city within state (alphabetical), and by name within city (alphabetical). Once this is working, modify the query so that it lists just the students from your home state. Once this working, modify the query so that it includes students from Texas as well as the students from your home state. If you are from Texas, include students from Virginia. Now modify the query so that only those students from the given states who are taking the course this term are included. This list should be ordered by state, city and then name. This is the version to save and turn in.
Exercise 19.11. Majors and Chairpersons
Create a query that gives an alphabetical list of the various majors together with the name of the department that offers the given major. When this is working, modify the query to include the name of the chairperson for the department offering the major. This is the version to save and turn in.
Exercise 19.12. Interests and Categories
Create a query that produces a list of all of the interests together with their category. The list should be organized by category and by interest within category. Now restrict this to just the category of Instrument. This is the version to save and turn in.
Exercise 19.13. Dave Matthews.
Create a query that lists the names and classyear of all students who indicated an interest in the Dave Matthews Band. Modify the query to include only students who are taking the course this term. This is the version to save and turn in.
Exercise 19.14. Major and Student Name
Create a query that returns only two columns – called Major and StudentName – ordered by Major, LastName, and finally FirstName. You will need to concatenate FirstName and LastName to display them in one column (use FirstName + " " + LastName AS StudentName). You may also include other characters in your concatenation by simply adding them (using +), within quotes ("any characters"), to your query.
Turn in database: When you have finished the entire lab, turn in a printout of your query document and copy your database with the saved queries to your Returns folder.