Assignment

Using the DPC.University Data Set:

Download the table, to download the table click on "Slow download (FREE):

This lab incorporates the DPC University dataset that is used throughout this module. Your instructor will provide you with the DDL (CREATE TABLE) and DML (INSERT INTO) text files necessary to load the database.

1. Write an SQL Statement that shows the first three letters of the students last name (see Chapter 6) and current major. Do not include students that are majoring in anything beginning with an A.

2. Write an SQL statement that shows the students first and last name (combined into one column, and with an appropriate alias) that use Locker Location B.

3. Write a statement that shows information (SELECT *) about students and lockers. Include all rows from both the STUDENT and LOCKER tables, regardless. In your answer, describe the type of JOIN you decided to use, and why.

4. Write an aggregate function that counts the number of students that have not selected a major.

5. Write an SQL statement that adds up the amount of cash awards located in the AWARD table. Make the output in currency format.

6. As a follow-up to question 5, write an SQL statement that shows the total amount of money paid out. The coding for this question is not difficult. However, you need to think through where the numbers that needed to be added are located. Make the output in currency format.

7. This question is about student major and activity risk. Show the average risk and major--- and group this information by major. Only include groups with an average risk of at least 2.

Attachment:- SQL-Assignment.rar