Oracle Exercise SQL statement exercises

Table (i) Student (student table)

Property name	Data type	Can be empty	Meaning
Sno	VARCHAR2 (3)	Whether	Study number (main code)
Sname	VARCHAR2 (8)	Whether	Student Name
Ssex	VARCHAR2 (2)	Whether	Student Sex
Sbirthday	Date	Can	Student's date of birth
Class	VARCHAR2 (5)	Can	Students in the same class

CREATE TABLE Student

(

Sno VARCHAR2 (3) NOT NULL,

Sname VARCHAR2 (8) NOT NULL,

Ssex VARCHAR2 (2) NOT NULL,

Sbirthday Date,

Class VARCHAR2 (5)

)

;

Comment on column student. Sno

Is ' study number ';

Comment on column student. Sname

Is ' student name ';

Comment on column student. Ssex

Is ' student sex ';

Comment on column student. Sbirthday

Is ' student birth date ';

Comment on column student. Class

Is ' Student's class ';

Table (ii) Course (curriculum)

Property name	Data type	Can be empty	Meaning
Cno	VARCHAR2 (5)	Whether	Course Number (main code)
Cname	Varchar (10)	Whether	Course Name
Tno	VARCHAR2 (3)	Whether	Faculty Number (external code)

CREATE TABLE Course

(

Cno VARCHAR2 (5) NOT NULL,

Cname varchar (TEN) is not NULL,

Tno VARCHAR2 (3) NOT NULL

)

;

Comment on column course.cno

Is ' Course number ';

Comment on column course.cname

Is ' course name ';

Comment on column Course.tno

Is ' faculty number ';

ALTER TABLE Course

Add constraint Pk_course primary key (Cno);

ALTER TABLE Course

Add constraint Fk_tno foreign key (Tno)

References Teacher (Tno);

Table (iii) score (score table)

Property name	Data type	Can be empty	Meaning
Sno	VARCHAR2 (3)	Whether	School Number (external code)
Cno	VARCHAR2 (5)	Whether	Course number (outside code)
Degree	Number (4,1)	Can	Results
Main code: sno+ Cno

CREATE TABLE Score

(

Sno VARCHAR2 (3) NOT NULL,

Cno VARCHAR2 (5) NOT NULL,

Degree Number (4,1)

)

;

Comment on column Score.sno

Is ' study number ';

Comment on column score.cno

Is ' Course number ';

Comment on column Score.degree

is ' performance ';

ALTER TABLE Score

Add constraint Fk_sno foreign key (Sno)

References student (SNO);

ALTER TABLE Score

Add constraint Fk_cno foreign key (Cno)

References Course (Cno);

Table (iv) Teacher (Teacher's table)

Property name	Data type	Can be empty	Meaning
Tno	VARCHAR2 (3)	Whether	Faculty Number (main code)
Tname	VARCHAR2 (4)	Whether	Faculty Name
Tsex	VARCHAR2 (2)	Whether	Faculty Sex
Tbirthday	Date	Can	Faculty Birth date
Prof	VARCHAR2 (6)	Can	Title
Depart	Varchar (10)	Whether	Faculty Department

CREATE TABLE TEACHER

(

TNO VARCHAR2 (3) NOT NULL,

Tname VARCHAR2 (4) NOT NULL,

Tsex VARCHAR2 (2) NOT NULL,

Tbirthday DATE,

Prof VARCHAR2 (6),

Depart VARCHAR2 (TEN) NOT NULL

)

Comment on column Teacher.tno

Is ' faculty number ';

Comment on column teacher.tname

Is ' faculty name ';

Comment on column teacher.tsex

Is ' faculty gender ';

Comment on column Teacher.tbirthday

Is ' Faculty of birth ';

Comment on column teacher.prof

is ' title ';

Comment on column Teacher.depart

Is ' faculty Department ';

ALTER TABLE TEACHER

Add constraint Pk_tno primary key (Tno)

Table 1-2 data in the database

Table (i) Student

Sno	Sname	Ssex	Sbirthday	Class
108	Zenghua	Man	1977-09-01	95033
105	Kuanming	Man	1975-10-02	95031
107	Wang Li	Woman	1976-01-23	95033
101	Li June	Man	1976-02-20	95033
109	Wang fang	Woman	1975-02-10	95031
103	Contacts	Man	1974-06-03	95031

INSERT into Student values (' 108 ', ' Zeng Hwa ', ' Male ', to_date (' 1977-09-01 ', ' yyyy-mm-dd '), ' 95033 ');

INSERT into Student values (' 105 ', ' Kuanming ', ' Male ', to_date (' 1975-10-02 ', ' yyyy-mm-dd '), ' 95031 ');

INSERT into Student values (' 107 ', ' Wang Li ', ' Female ', to_date (' 1976-01-23 ', ' yyyy-mm-dd '), ' 95033 ');

INSERT into Student values (' 101 ', ' Li June ', ' Male ', to_date (' 1976-02-20 ', ' yyyy-mm-dd '), ' 95033 ');

INSERT into Student values (' 109 ', ' Wang Fang ', ' Female ', to_date (' 1975-02-10 ', ' yyyy-mm-dd '), ' 95031 ');

INSERT into Student values (' 103 ', ' contacts ', ' Male ', to_date (' 1974-06-03 ', ' yyyy-mm-dd '), ' 95031 ');

Table (ii) Course

Cno	Cname	Tno
3-105	Introduction to Computers	825
3-245	Operating system	804
6-166	Digital circuit	856
9-888	Advanced mathematics	831

INSERT into Course values (' 3-105 ', ' Introduction to Computer ', ' 825 ');

INSERT into Course values (' 3-245 ', ' operating system ', ' 804 ');

INSERT into Course values (' 6-166 ', ' digital circuit ', ' 856 ');

INSERT into Course values (' 9-888 ', ' Advanced math ', ' 831 ');

Table (iii) score

Sno	Cno	Degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

INSERT into score values (' 108 ', ' 6-166 ', ' 81 ');

Table (iv) Teacher

Tno	Tname	Tsex	Tbirthday	Prof	Depart
804	Sung	Man	1958-12-02	Associate professor	Computer Department
856	Zhang Xu	Man	1969-03-12	Lecturer	Department of Electronic Engineering
825	Wang ping	Woman	1972-05-05	Ta	Computer Department
831	Liu Bing	Woman	1977-08-14	Ta	Department of Electronic Engineering

INSERT into Teacher values (' 804 ', ' sung ', ' Male ', to_date (' 1958-12-02 ', ' yyyy-mm-dd '), ' associate Professor ', ' computer Department ');

INSERT into Teacher values (' 856 ', ' Zhang Xu ', ' Male ', to_date (' 1969-03-12 ', ' yyyy-mm-dd '), ' lecturer ', ' Electronic Engineering Department ');

INSERT into Teacher values (' 825 ', ' Wang Ping ', ' female ', to_date (' 1972-05-05 ', ' yyyy-mm-dd '), ' ta ', ' computer Department ');

INSERT into Teacher values (' 831 ', ' Liu Bing ', ' Female ', to_date (' 1977-08-14 ', ' yyyy-mm-dd '), ' ta ', ' Electronic Engineering Department ');

1. Query the sname, Ssex, and class columns of all records in the student table.

Select Sname,ssex,class from student;

2, the inquiry teacher all units namely does not duplicate depart column.

SELECT DISTINCT * from student;

3. Query all records of student table.

SELECT * from Student;

4. Query all records from 60 to 80 of the scores in the score table.

SELECT * from score where degree > degree < 80;

5. Check the record of 85, 86 or 88 in the score table.

SELECT * FROM score where degree = or degree = or degree = 88;

6, inquires the student table "95031" class or the sex is "the female" the classmate record.

SELECT * from Student where CLASS = 95031 or SSEX = ' female ';

7. Query the student table for all records in descending order of class.

SELECT * FROM student order BY class Desc;

8, in CNO Ascending, Degree descending query score all records of the table.

SELECT * FROM Score ORDER by CNO ASC, Degree desc;

9. Check the number of students in "95031" class.

Select Class,count (1) as quantity from student where class = ' 95031 ' Group by class;

10. Check the student number and course number of the highest score in the score table. (sub-query or sort)

Select Sno | | CNO as study number and course number from score order by degree Desc;

11. Check the average score of each course.

Select Cno,count (1) as number, AVG (degree) as average from score group by Cno;

12. Check the average score of the course with at least 5 students enrolled in the score table and begin with 3.

Select Cno,count (1) as number, AVG (degree) as average from score where Cno like ' 3% ' GROUP by Cno have Count (Cno) >5;

13, the query score is greater than 70, less than 90 of the SNO column.

SELECT * from score where degree between and 90;

14. Sname, CNO and degree columns for all students are queried.

Select S.sname,c.cno,c.degree from student S, score C where S.sno=c.sno;

15. Check the SNO, CNAME and degree columns of all students.

Select C.sno,q.cname,c.degree from student S, score c,course Q where S.sno=c.sno and q.cno=c.cno;

16. Check the sname, CNAME and degree columns of all students.

Select S.sname,q.cname,c.degree from student S, score c,course Q where S.sno=c.sno and q.cno=c.cno;

17. Check the average score of "95033" class students.

Select Class,avg (degree) as average from student S,score C where s.sno=c.sno and class = ' 95033 ' Group by class;

18. Assume that a grade table is created using the following command:

CREATE table Grade (Low number (3), UPP number (3), rank char (1))

Insert into grade values (90,100, ' A ');

Insert into grade values (80,89, ' B ');

Insert into grade values (70,79, ' C ');

Insert into grade values (60,69, ' D ');

Insert into grade values (0,59, ' E ');

The SNO, CNO and rank columns of all students are now queried.

Select S.sno,s.cno,g.rank from score s, Grade G where s.degree between G.low and G.upp;

Oracle Exercise SQL statement exercises