Assignment
1. Write a query that retrieves student.last_name, student.first_name and total number of enrollments from the student and enrollment tables. Your query should also include students that do not have any enrollments.
a. Write this query without using a join operator.
b. Write this query using the join operator style
2. Write a query that retrieves student.last_name, student.first_name, grade_type.description and average numeric_grade for all average numeric grades above 90.
a. Write this query without using a join operator.
b. Write this query using the join operator style
3. What are views and what are the advantages of using views.
4. Create a view that displays student.last_name, student.first_name, courses.descriptions, instructor.last_name, andinstructor. first_name.
5. Discuss the types of constraints that may be placed on view
6. What are Oracle sequences and how are they most commonly used.
7. What is a sub query and what are the types of sub queries. Your discussion should include the differences between the types of sub-queries.
8. Write a simple sub query that retrieves student last_name, student first_name for students that do not have any enrollments.
9. Write a correlated sub query that retrieves all instructors that have taught classes.
10. Where can sub-queries be placed in a SQL Select statement.
11. What are the ACID properties of a transaction.
12. Describe the three most common concurrent transaction execution problems. Explain how concurrency control can be used to avoid those problems.
13. What tables are good candidates for indexes and what type of indexes would you place on those tables.
14. Write a query using a set operator that list all first_names that students and instructors have in common.
15. What is a data warehouse and what type of data model is used in data marts to improve performance.