The subject is Database:

You are to use Oracle database system to create the following database:

STUDENT table contains 4 columns: ID, NAME, GRADE and DEGREE.

ID 9-character student ID (e.g. CIT081234).

NAME max. up to 30-character student name.

GRADE must be any of these: 0, 10, 20, 30, 40, 50, 60, 70, 80, 90 and 100.

(hint: max. 3 digits, 2 valid digits, last digit is always ZERO and scale is NOT zero)

DEGREE 4-character program name (e.g. CSBP).

Based on the above database, please answer the following questions in the space provided:

1. To check if a database table exists or not, one way is to display its structure. What is the SQL statement to display the structure of STUDENT table????(1 mark)

2. When you run the above SQL statement in Q1, if STUDENT table does not exist, what is the result/response from Oracle system???????(0.5 marks)

3. It is a common practice to delete an old existing database table before you create a new one with the same name. Please write the SQL statement to delete a table named STUDENT.??????????(1 mark)

4. When you run the above SQL statement in Q3, what is the result/response from Oracle system if:

a. STUDENT table does not exist already??????(0.5 marks)

b. STUDENT table exists????????(0.5 marks)

5. Now write the SQL statement to create a new table named STUDENT with the 4 columns described above.?????????(5 marks)

6. What is the result/response from Oracle system if the STUDENT table created successfully????????????(0.5 marks)

7. Now enter the following records into the STUDENT table:

ID NAME GRADE DEGREE

CIT080001 TONY NG 100 CSBP

CIT059876 ALI MOHAMED 80 ITBP

CBA061234 STEVE ADAMS 90 BABP

CSC075555 BILL GATES 50 ECBP

CEE042468 STEVE JOBS 60 EEBP

UAE000000 GREY MORE 0 BABP

After entering, write the SQL statement to display all records listed with the following new column title (match spelling):??????(5 marks)

ID as Std-ID

NAME as Std-Name

GRADE as Std-Grade

DEGREE as Program

8. What is the SQL statement to display only the name of the student(s) with grade > 80????????????(2 marks)

9. When you run the above SQL statement in Q8, what is the result/response from Oracle system???????????(1 mark)

10. What is the result/response from Oracle system when you run the SQL statement to display only the ID column with query on DEGREE by descending order??(3 marks)