Listing 3 shows this technique in action. This code
joins the MovieReview table with the Movie table to get the movie names and the MovieReview table
with the Genre table to get the genre names. The
Stars column in the MovieReview table provides
the movie ratings, which are averaged for each
movie. The average ratings are displayed as
asterisks rather than numbers. The Count(*)
function is used to count how many reviews
each movie has; these counts are assigned to
a column named Reviews. After grouping the
results by movie name, then genre, the code
stores the result set in the MovieReviewSummary
table.
You can find the code in Listing 3 in the CodeTo
Create&PopulateMovieReviewSummaryTable
.sql file, which is part of 99448.zip. After you
execute this code in the query window, you can
run the query
SELECT *
FROM MovieReviewSummary
ORDER BY Stars, Reviews
to get the result set in Figure 2.
If you were to look at the structure of the new
table to see how SQL Server defines the columns,
you’d see that the Movie and Genre columns
are the same type and length in the new table as
they were in the source tables. Because the Stars
and Reviews columns were computed within the
SELECT statement and not directly pulled from
physical tables, their type and length matches the
result of the corresponding expressions.
Knowing How to Create
Tables Isn’t Knowledge
Reserved Just for DBAs
Creating tables doesn’t have to be the exclusive
domain of DBAs. Application developers,
report writers, business analysts, or anyone else
who stores data in a relational database will
benefit from knowing how to create their own
tables. Being able to write the table creation
scripts when requesting new tables goes a long
way in convincing the DBA to do your bidding.
End of Article
Prev. page
1
2
[3]
next page -->
