Oracle Exam Questions Jobs

Create a new Student information sheet (student) that requires:
1. The fields are as follows: study number (SID), name, Gender (sex), age, address.
2. Add a constraint for the field: The primary key, the name is non-empty, the gender is the check constraint, the age is the check constraint, the address is the default constraint.
3. Create a sequence insertion number (SID) column, with a recommended initial value of 1001, and an increment of 1.
4. Insert the record.

CREATE TABLE student (SID int, name char (1), Sex char (2), age int, address VARCHAR2 (20));

ALTER TABLE student modify SID primary Key;

ALTER TABLE student modify name not NULL;

ALTER TABLE student modify sex check (sex= ' m ' or sex= ' W ');

ALTER TABLE student Modify age check (age between and 100);

ALTER TABLE student Modify address VARCHAR2 ($) Default ' 20 ';

Create sequence Student_sid start with 1001 increment by 1;

INSERT into student (sid,name,sex,age,address) VALUES (student_sid.nextval, ' 1 ', ' w ', +, ' China ');

INSERT into student values (Student_sid.nextval, 2, ' m ', +, ' China '); INSERT into student values (Student_sid.nextval, 3, ' m ', +, ' China ');

Commit

Create a new curriculum (course) that requires:
1. The fields are as follows: Course number (CID), course name (subject).
2. Add a constraint to the field, respectively: the course number is the primary key, the course name is non-empty
3. Create sequence Insert Course Number column (CID), the recommended initial value is from 1, increment is 1.
4. Insert the record.

CREATE TABLE course (CID int, subject char (20));

ALTER TABLE course modify CID primary key;

ALTER TABLE course modify subject NOT NULL;

Create sequence course_cid start with 1 increment by 1;

Insert into course values (course_cid.nextval, ' Yuwen ', ' Shuxue ', ' Yingyu ');

Commit

To create a new student exam results table (grade), ask:
1. The fields are as follows: Score number (GID), study number (SID), course number (CID), Test score (score).
2. Add a constraint to the field: The score number is the key, the study number is the foreign key, the course number is the foreign key, the test result is non-empty.
3. Create a sequence to insert the score number column (GID), the recommended initial value is from 101, the increment is 1.
4. Insert the record.

CREATE table grade (GID int, sid int, Cid int, score int);

ALTER TABLE grade modify GID primary key;

ALTER TABLE grade ADD constraint fk_grade_sid foreign key (SID) references student (SID);

ALTER TABLE grade ADD constraint fk_grade_cid foreign key (CID) references student (CID);

Create sequence Grade_gid start with 101 increment by 1;

Insert into grade values (grade_gid.nextval,1001,1,50);

Insert into grade values (grade_gid.nextval,1001,2,90);

Insert into grade values (grade_gid.nextval,1001,3,80);

Insert into grade values (grade_gid.nextval,1002,1,50);

Insert into grade values (GRADE_GID.NEXTVAL,1002,2,70);

Insert into grade values (grade_gid.nextval,1002,3,80);

Insert into grade values (grade_gid.nextval,1003,1,50);

Insert into grade values (grade_gid.nextval,1003,2,59);

Insert into grade values (GRADE_GID.NEXTVAL,1003,3,70);

SELECT * from grade;

Commit

For the above three tables, the following requirements are completed:
1. Group BY course number and find the average score for each group (average of each course)
2. Group BY course number and find the average score for each group of passing persons (grade >= 60) (average number of passing persons per course)
3. Ask for an average of all courses for each student
4. The average score for each student (exam result) Pass Course
5. Average score for each student in each internal test (average of each course per learner)
6. Check the average score of the students who have done the exam (find out if the student's course number (CID) appears in the group more than once) (each course for each student
More than once in the course number)
7. Use multi-table connection query to find out student number, name, exam number, subject and score
8. Create a view that contains the student's number, name, exam number, subject, score, these fields

1. Select Cid,avg (Score) from the grade group by CID;

2. Select Cid,avg (Score) from the grade where score>=60 Group by CID;

3. Select SID, Avg (score) from grade group by SID;

4. Select Sid,avg (score) from grade where score>=60 the group by SID;

5. Select Cid,sid,avg (Score) from the grade group by sid,cid the order by SID;

6. Select Sid,avg (score) from grade where SID in (select SID from Grade GROUP by SID,CID have Count (CID) >1) GROUP by S Id

Insert a retake record: INSERT into grade values (GRADE_GID.NEXTVAL,1003,2,70);

7.Select S.sid, S.name,g.gid,c.cid,g.score
form student S,course C,grade g
where S.sid=g.sid
and C.cid=g.cid;

8. Create or replace view score as
Select S.sid, S.name, G.gid, C.cid, G.score
From student S, course C, Grade G
where S.sid=g.sid and c.cid=g.cid;

Oracle Exam Questions Jobs