This exam requires you to execute queries against the STUDENT database schema that you already should have already created. For all answers that are narratives please provide citations to the sources that you are using to answer the questions.

1. Write a query that retrieves the course description, section number, total number of enrollments for each section.
a. Write this query without using a join operator. (5 point)

b. Write this query using the join operator style (5 point)

2. Write a query that retrieves instructor.last_name, instructor.first_name, and the number of sections each instructor is teaching.
a. Write this query without using a join operator. (5 point)
b. Write this query using the join operator style (5 point)

3. What are views and what are the advantages of using views. (5 Points)

6. What are Oracle sequences and how are they most commonly used. (5 points)

7. What is a sub query and what are the types of sub queries. Your discussion should include the differences between the types of subqueries.(5 points)

8. Write a simple sub query that retrieves student last_name, student first_name for students that do not have any enrollments (5 points).

9. Write a correlated sub query that retrieves all instructors that have taught classes. (10 points)
10. Where can sub-queries be placed in a SQL Select statement. (5 point)

11. What are the ACID properties of a transaction? (10 points)
12. Describe the three most common concurrent transaction execution problems. Explain how concurrency control can be used to avoid those problems. (5 points)

13. What tables are good candidates for indexes and what type of indexes would you place on those tables. (5 points)

14. Write a query using a set operator that list all first_names that students and instructors have in common. (5 point)
15. What is a data warehouse and what type of data model is used in data marts to improve performance. (10 points)

COMMENT ON TABLE GRADE_TYPE_WEIGHT IS ‘Information on how the final grade for a particular section is computed. For example, the midterm constitutes 50%, the quiz 10% and the final examination 40% of the final grade.’
/

COMMENT ON COLUMN GRADE_TYPE_WEIGHT.SECTION_ID IS ‘The unique section ID for a section.’
/

COMMENT ON COLUMN GRADE_TYPE_WEIGHT.GRADE_TYPE_CODE IS ‘The code which identifies a category of grade.’
/

COMMENT ON COLUMN GRADE_TYPE_WEIGHT.NUMBER_PER_SECTION IS ‘How many of these grade types can be used in this section. That is, there may be 3 quizzes.’
/

COMMENT ON COLUMN GRADE_TYPE_WEIGHT.PERCENT_OF_FINAL_GRADE IS ‘The percentage this category of grade contributes to the final grade.’
/

COMMENT ON COLUMN GRADE_TYPE_WEIGHT.DROP_LOWEST IS ‘Is the lowest grade in this type removed when determining the final grade? (Y/N)’
/

SELECT ‘Count of COURSE Table:’, COUNT(*)
FROM course
GROUP BY ‘Count of COURSE Table:’;
SELECT ‘Count of ENROLLMENT Table:’, COUNT(*)
FROM enrollment
GROUP BY ‘Count of ENROLLMENT Table:’;
SELECT ‘Count of GRADE Table:’, COUNT(*)
FROM GRADE
GROUP BY ‘Count of GRADE Table:’;
SELECT ‘Count of GRADE_CONVERSION Table:’, COUNT(*)
FROM GRADE_CONVERSION
GROUP BY ‘Count of GRADE_CONVERSION Table:’;
SELECT ‘Count of GRADE_TYPE Table:’, COUNT(*)
FROM GRADE_TYPE
GROUP BY ‘Count of GRADE_TYPE Table:’;
SELECT ‘Count of GRADE_TYPE_WEIGHT Table:’, COUNT(*)
FROM GRADE_TYPE_WEIGHT
GROUP BY ‘Count of GRADE_TYPE Table:’;
SELECT ‘Count of INSTRUCTOR Table:’, COUNT(*)
FROM INSTRUCTOR
GROUP BY ‘Count of INSTRUCTOR Table:’;
SELECT ‘Count of SECTION Table:’, COUNT(*)
FROM SECTION
GROUP BY ‘Count of SECTION Table:’;
SELECT ‘Count of STUDENT Table:’, COUNT(*)
FROM STUDENT
GROUP BY ‘Count of STUDENT Table:’;
SELECT ‘Count of ZIPCODE Table:’, COUNT(*)
FROM ZIPCODE
GROUP BY ‘Count of ZIPCODE Table:’;
SET PAGES 24
SET ECHO ON

Get a 5 % discount on an order above $ 150
Use the following coupon code : 2018DISC

Research and assignments can be a hard nut to crack.
Leave it to us, it will be in safe hands, the experts!
Just sit back and relax as we do the research and you can be attending to other urgent or pressing matters.

Our Commitment

We commit to only produce 100% original work. All the papers are written from scratch and content customized as per your instructions.