6.4. Query Development with Derived Structures
In this section, we discuss how derived structures such as views and temporary tables can be used in query development.
To illustrate this process, we will list from our standard database, Student_course, the name, student number, and department name of students who are freshman or sophomores and computer science majors.
In Step 1, we will develop a query, and in Step 2, we will show how to use this query with derived structures. In Step 2, Option 1 shows how the query can be turned into a view, Option 2 shows how the query can be turned into an inline view, and Option 3 shows how the query can be used to create a temporary table.
6.4.1. Step 1: Develop a Query Step by Step
The first step is to see which columns we need and in which tables these columns are found. We need student names (sname) and numbers (stno), which are found in the Student table. Department names (dname) are found in the Department_to_major table. To find the department names that correspond to the student majors, we have to combine the Student and Department_to_major tables. To combine these two tables, we will join the tables where major from the Student table joins with the dcode from the Department_to_major table as follows (because the statements eventually will be filtered by class, we include class in the result set):
SELECT s.sname, s.stno, d.dname, s.class
FROM Student s, Department_to_major d
WHERE s.major = d.dcode
After you type the query and run it, you will get the following 45 rows of output:
sname stno dname class
-------------------- ------ -------------------- ------
Lineas 2 English 1
Mary 3 Computer Science 4
Zelda 5 Computer Science NULL
Ken 6 Political Science NULL
Mario 7 Mathematics NULL
Brenda 8 Computer Science 2
Romona 9 English NULL
Richard 10 English 1
Kelly 13 Mathematics 4
Lujack 14 Computer Science 1
Reva 15 Mathematics 2
Elainie 17 Computer Science 1
Harley 19 Political Science 2
Donald 20 Accounting 4
Chris 24 Accounting 4
Jake 31 Computer Science 4
Lynette 34 Political Science 1
Susan 49 English 3
Monica 62 Mathematics 3
Bill 70 Political Science NULL
Hillary 121 Computer Science 1
Phoebe 122 English 3
Holly 123 Political Science 4
Sadie 125 Mathematics 2
Jessica 126 Political Science 2
Steve 127 English 1
Brad 128 Computer Science 1
Cedric 129 English 2
Alan 130 Computer Science 2
Rachel 131 English 3
George 132 Political Science 1
Jerry 142 Computer Science 4
Cramer 143 English 3
Fraiser 144 Political Science 1
Harrison 145 Accounting 4
Francis 146 Accounting 4
Smithly 147 English 2
Sebastian 148 Accounting 2
Losmith 151 Chemistry 3
Genevieve 153 NULL NULL
Lindsay 155 NULL 1
Stephanie 157 Mathematics NULL
Gus 160 Art 3
Benny 161 Chemistry 4
Jake 191 Mathematics 2
(45 row(s) affected)
To find all the freshmen and sophomores (class 1 and 2) from the Student table, add AND (s.class = 1 or s.class = 2) to the end of the previous query, as follows:
SELECT s.sname, s.stno, d.dname, s.class
FROM Student s, Department_to_major d
WHERE s.major = d.dcode
AND (s.class = 1 or s.class = 2)
Running this query produces the following 21 rows of output:
sname stno dname class
-------------------- ------ -------------------- ------
Lineas 2 English 1
Brenda 8 Computer Science 2
Richard 10 English 1
Lujack 14 Computer Science 1
Reva 15 Mathematics 2
Elainie 17 Computer Science 1
Harley 19 Political Science 2
Lynette 34 Political Science 1
Hillary 121 Computer Science 1
Sadie 125 Mathematics 2
Jessica 126 Political Science 2
Steve 127 English 1
Brad 128 Computer Science 1
Cedric 129 English 2
Alan 130 Computer Science 2
George 132 Political Science 1
Fraiser 144 Political Science 1
Smithly 147 English 2
Sebastian 148 Accounting 2
Lindsay 155 NULL 1
Jake 191 Mathematics 2
(21 row(s) affected)
Now that we have the department names of all the freshmen and sophomores, we need to find the computer science majors from this group, so we add AND s.major = 'COSC' to the previous query as follows:
SELECT s.sname, s.stno, d.dname, s.class
FROM Student s, Department_to_major d
WHERE s.major = d.dcode
AND (s.class = 1 or s.class = 2)
AND s.major = 'COSC'
This query produces the following output (six rows), which finally gives us the student name, student number, and department name of students who are freshman or sophomores and computer science majors:
sname stno dname class
-------------------- ------ -------------------- ------
Brenda 8 Computer Science 2
Lujack 14 Computer Science 1
Elainie 17 Computer Science 1
Hillary 121 Computer Science 1
Brad 128 Computer Science 1
Alan 130 Computer Science 2
(6 row(s) affected)
Note that in each case where we add more filtering in the WHERE clause, the number of rows declines. If the number of rows does not decline, that could represent a problem.
6.4.2. Step 2: Using a Derived Structure
This step shows how the previous query (developed in Step 1) can be turned into a view (Option 1), inline view (Option 2), or temporary table (Option 3). Each one of these derived structures will produce the same end results, so as you develop your own queries, you may use whichever derived structure you become most comfortable with and/or is most appropriate.
 | Derived structures are also very useful when you wish to use nested functions. |
|
6.4.2.1. Option 1: Turning your query into a view
To create a view (called stu_view) using the previous example query, type:
CREATE VIEW stu_view AS
SELECT s.sname, s.stno, d.dname, s.class
FROM Student s, Department_to_major d
WHERE s.major = d.dcode
AND (s.class = 1 or s.class = 2)
AND s.major = 'COSC'
You can now SELECT from the view by typing:
SELECT *
FROM stu_view
WHERE sname LIKE 'BR%'
This query produces the following output, which includes all the names in the view stu_view that start with "Br":
sname stno dname class
-------------------- ------ -------------------- ------
Brenda 8 Computer Science 2
Brad 128 Computer Science 1
(2 row(s) affected)
Remember that the view always reflects the database as it is, and a view takes up no extra storage in the database, because no data is stored in a view.
6.4.2.2. Option 2: Using an inline view
You can also place a query in the FROM clause of a SELECT statement and thereby create what is called an inline view. An inline view exists only during the execution of a query. The main purpose of an inline view is to simplify the development of a one-time query. In a typical development scenario, a person would probably devise a SELECT statement, test it, examine the result, wrap it in parentheses, and continue with the development by using the inline view.
Follow these general steps to develop an inline view:
Develop a query:
SELECT column1, column2, ...
FROM TableName
WHERE ...
Wrap the results into parentheses and make it into an inline view:
SELECT *
FROM (SELECT column1, column2, ... FROM TableName WHERE ...)
Display the columns in the inline view:
SELECT v.column1, v.column2, ...
FROM (SELECT column1, column2, ... FROM TableName WHERE ...) v
You could then proceed to make the previous query an inline view and add more complexity as needed. The beauty of creating a query in steps is that you can examine each step using counts and TOP qualifiers to see whether you're heading in the right direction.
Let's look at an example of an inline view for our sample problem. In this example, we create the same view as previously inline -- that is, we create the view on the fly, give it an alias of v, and use it just as we would use a stored table or view, as follows:
SELECT v.sname, v.dname, v.class
FROM (SELECT s.sname, s.stno, d.dname, s.class
FROM Student AS s, Department_to_major AS d
WHERE s.major = d.dcode
AND (s.class = 1 or s.class = 2)
AND s.major = 'COSC') AS v
This query produces the following six rows of output:
sname dname class
-------------------- -------------------- ------
Brenda Computer Science 2
Lujack Computer Science 1
Elainie Computer Science 1
Hillary Computer Science 1
Brad Computer Science 1
Alan Computer Science 2
(6 row(s) affected)
In the final result set of the outer query, the column names reference the names used in the inline view result set.
6.4.2.3. Option 3: Using a global temporary table
To create a global temporary table (called ##Temp2) using the query developed in Step 1, type:
SELECT s.sname, s.stno, d.dname, s.class INTO ##Temp2
FROM Student s, Department_to_major d
WHERE s.major = d.dcode
AND (s.class = 1 or s.class = 2)
AND s.major = 'COSC'
Once you run or execute your query, you have created a temporary table called ##Temp2.
Now if you type:
SELECT *
FROM ##Temp2
You should get the following six rows of output, which should be exactly the same as you received in the other options:
sname stno dname class
-------------------- ------ -------------------- ------
Brenda 8 Computer Science 2
Lujack 14 Computer Science 1
Elainie 17 Computer Science 1
Hillary 121 Computer Science 1
Brad 128 Computer Science 1
Alan 130 Computer Science 2
(6 row(s) affected)
In all the examples of views and temporary tables, the SQL programmer weighs programming effort (individual and team), storage costs, and query efficiency to choose which structure is appropriate.
 |
