You are to use Oracle database system to create the following database: STUDENT
ID: 3715089 • Letter: Y
Question
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)
describe STUDENT
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)
ORA-00942: table or view does not exist
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)
drop STUDENT;
4- When you run the above SQL statement in Q3, what is the result/response from Oracle system if:
STUDENT table does not exist already? (0.5 marks)
If the described query executes in Oracle SQL
If table doesn't exist already ,it will prompt :
Table created.
5- STUDENT table exists? (0.5 marks)
If table is already present ,it will throw below error :
ORA-00955 name is already used by an existing object
Means Table Name is already in use in Oracle DBMS.
6- Now write the SQL statement to create a new table named STUDENT with the 4 columns described above. (5 marks)
7- What is the result/response from Oracle system if the STUDENT table created successfully? (0.5 marks)
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)
Answer from 5 to 10 please the rest are answered
Explanation / Answer
5 answer is already given, I started from 6, any doubts comments me.
6- Now write the SQL statement to create a new table named STUDENT with the 4 columns described above.
Answer
create table student(id varchar(9),name varchar(30),grade number(3), degree varchar(4));
7- What is the result/response from Oracle system if the STUDENT table created successfully?
Answer
Table created.
Now enter the following records into the STUDENT table:
insert into student values('CIT080001','TONY NG',100,'CSBP');
insert into student values('CIT059876','ALI MOHAMED',80,'ITBP');
insert into student values('CBA061234','STEVE ADAMS',90,'BABP');
insert into student values('CSC075555','BILL GATES',50, 'ECBP');
insert into student values('CEE042468','STEVE JOBS',60,'EEBP');
insert into student values('UAE000000','GREY MORE',0,'BABP');
After entering, write the SQL statement to display all records listed with the following new column title (match spelling):
ID as Std-ID
NAME as Std-Name
GRADE as Std-Grade
DEGREE as Program
Answer
select ID as "Std-ID",NAME as "Std-Name",GRADE as "Std-Grade",DEGREE as "Program" from student;
8- What is the SQL statement to display only the name of the student(s) with grade > 80?
Answer
select name from student where grade>80;
9- When you run the above SQL statement in Q8, what is the result/response from Oracle system?
Answer
NAME
------------------------------
TONY NG
STEVE ADAMS
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?
Answer
select ID from student order by degree desc;
ID
---------
CIT059876
CEE042468
CSC075555
CIT080001
UAE000000
CBA061234
6 rows selected.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.