Database Systems You are to use Oracle database system to create the following d
ID: 3712794 • Letter: D
Question
Database Systems 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? b. STUDENT table exists? 0.5 marks) [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) Now enter the following records into the STUDENT table: NAME IONY NG ALI MOHAMED 7. GRADE DEGREE ID CITO80001 CITO59876 CBA061234 CSC075555 CEE042468 UAE000000 80 90 50 60 CSBP ITBP BABP ???? EEBP BABP STEVE JOBS GREY MORE 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 $td-Name GRADE as Sid-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 mark)Explanation / Answer
1. Ans: describe STUDENT; or desc STUDENT(sometime works in Other DBS to)
If you give the above statement oracle sql,it displays the structure of the table
2.Ans: If a table doesn't exist in Oracle SQL,it gives below error:
ORA-00942: table or view does not exist
3. drop STUDENT;
drop deletes the table structure and delete deletes the data in student table.
4. a)If the described query executes in Oracle SQL
If table doesn't exist already ,it will prompt :
Table created.
b)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.
5.Ans:
CREATE TABLE STUDENT(
ID VARCHAR(10),
NAME VARCHAR(30) ,
GRADE INT CONSTRAINT chk_grade CHECK (GRADE IN (0,10,20,30,40,50,60,70,80,90,100)),
DEGREE VARCHAR(4)
);
//restricts the values to (0,10,20,30,40,50,60,70,80,90,100)
6.Ans: Table created successfully
Oracle responds the above message if a table is created.
7.)ANS:
Execute below statements:
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 ('UAE0001000', 'GREY MORE', 0, 'BABP')
select ID as Std-ID,
Name as Std-Name,
GRADE as Std-Grade,
DEGREE as Program from STUDENT //displays student table with the alias names
8. select name from STUDENT where GRADE >80
Theh above statement displays the student names whose rades are >80
9. It displays the Student names whose grades are greater than 80
10. select ID from STUDENT order by DEGREE desc
the above statement displays IDs by descending order of degree.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.