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.

1. 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 queries?
   Which reason applies only to multiple table queries?
   
   
2. What is the difference in the results of these two queries:
   SELECT TITLE, PRICE
   FROM book
   WHERE TITLE LIKE ('%of%') ;
   
   SELECT TITLE, PRICE
   FROM book
   WHERE TITLE LIKE ('% of %') ;
   
   
3. Where should you place an ORDER BY clause in a query?
   
   
4. How do you get an ORDER BY clause to display data from a field in descending order? How about ascending order?
   
   
5. Explain each line in the following query written for our bookstore database:
   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;
   
   
6. What is the purpose of an alias in a query? Is it necessary?
   
   
7. An alias may be used in several clauses of a query. In which clause of a query is an alias defined?
   
   
8. When you run a query on fields that are not numeric, what happens when you use numeric comparison operators, like < or <=?
   
   
9. What is the product of two tables? Why should it be a sign of trouble when you see this in your query results?
   
   
10. 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 result?
    Why will each book title that is generated be shown four times?
    
    
11. 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 patches.
    
    
12. What is a data warehouse?
    
    
13. How is a data warehouse different from a data mart? Where are you likely to find them?
    
    
14. In decision support systems, what is meant by granular vs. aggregate decisions?
    
    
15. What is a star schema? What are its components? What should you find in each kind of component?
    
    
16. What makes the central table of a star schema so large? Why should it be denormalized, compared to the surrounding tables?
    
    
17. What is Big Data? What are the theee Vs used to describe it? Why are they not given hard definitions?
    
    
18. 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?
    
    
19. What is meant by the phrase "NoSQL databases"? What are some of the things that are odd about them, compared to relational databases?
    
    
20. Who are the companies that use the column family databases BigTable and Cassandra?