CIS 2210 - Database Management and Design
Review for Second Test
The following questions are provided to help you study for the second
test. Do not expect to see these exact questions on the test.
- In an SQL query, there are two general reasons to use a where clause.
Which reason applies to both single table queries and to multiple table
Which reason applies only to multiple table queries?
- What is the difference in the results of these two queries:
SELECT TITLE, PRICE
WHERE TITLE LIKE ('%of%') ;
SELECT TITLE, PRICE
WHERE TITLE LIKE ('% of %') ;
- Where should you place an ORDER BY clause in a query?
- How do you get an ORDER BY clause to display data from a field in
descending order? How about ascending order?
- Explain each line in the following query written for our bookstore
SELECT AUTHOR_FIRST, AUTHOR_LAST, TITLE
FROM author, wrote, book
WHERE author.AUTHOR_NUM = wrote.AUTHOR_NUM
AND wrote.BOOK_CODE = book.BOOK_CODE
AND book.TYPE = 'FIC'
ORDER BY AUTHOR_LAST;
- What is the purpose of an alias in a query? Is it necessary?
- An alias may be used in several clauses of a query. In which clause
of a query is an alias defined?
- When you run a query on fields that are not numeric, what happens
when you use numeric comparison operators, like < or <=?
- What is the product of two tables? Why should it be a sign of trouble
when you see this in your query results?
- Consider this query based on the bookstore database:
select wrote.book_code, title, wrote.author_num
from wrote, book
where wrote.author_num = '20'
order by title;
What is the apparent intent of this query?
Why will this query create a product result instead of the intended
Why will each book title that is generated be shown four times?
- A database must comply with security policies and standards of the
organization that uses it. What is the usual difference between a policy
and a standard? Give an illustrative example regarding operating system
- What is a data warehouse?
- How is a data warehouse different from a data mart? Where are you
likely to find them?
- In decision support systems, what is meant by granular vs. aggregate
- What is a star schema? What are its components? What should you find
in each kind of component?
- What makes the central table of a star schema so large? Why should
it be denormalized, compared to the surrounding tables?
- What is Big Data? What are the theee Vs used to describe it? Why
are they not given hard definitions?
- What is Hadoop, besides the name of a toy elephant? What are its two
major components? What are some of the odd features of its file system?
- What is meant by the phrase "NoSQL databases"? What are
some of the things that are odd about them, compared to relational databases?
- Who are the companies that use the column family databases BigTable