Oracle Simple Query Exercise (with database and answers)

Keep in mind that when inserting a table, the session will be given to an English session, otherwise the date item will get an error when inserting STUDENT and TEACHER tables
(Solution: Baidu Chat Modify ORACLE Session)

Establish STUDENT table
CREATE TABLE STUDENT
(SNO VARCHAR2(10) NOT NULL,
SNAME VARCHAR2(10) NOT NULL,
SSEX VARCHAR2(10) NOT NULL,
SBIRTHDAY DATE,
CLASS NUMBER NOT NULL);
Establish COURSE table
CREATE TABLE COURSE
(CNO VARCHAR2(10) NOT NULL,
CNAME VARCHAR2(20) NOT NULL,
TNO VARCHAR2(20) NOT NULL);
Establishing SCORE tables
CREATE TABLE SCORE
(SNO VARCHAR2(10) NOT NULL,
CNO VARCHAR2(10) NOT NULL,
DEGREE NUMBER NOT NULL);
Establish TEACHER table
CREATE TABLE TEACHER
(TNO VARCHAR2(10) NOT NULL,
TNAME VARCHAR2(10) NOT NULL,
TSEX VARCHAR2(10) NOT NULL,
TBIRTHDAY DATE NOT NULL,
PROF VARCHAR2(10),
DEPART VARCHAR2(20) NOT NULL);
Insert STUDENT data
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'Hua Zeng' ,'male' ,to_date('1977-09-01','yyyy-mm-dd'),95033);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'Correct Ming' ,'male' ,to_date('1975-10-02','yyyy-mm-dd'),95031);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'Wang Li' ,'female' ,to_date('1976-01-23','yyyy-mm-dd'),95033);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'Li Jun' ,'male' ,to_date('1976-02-20','yyyy-mm-dd'),95033);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'Wang Fang' ,'female' ,to_date('1975-02-10','yyyy-mm-dd'),95031);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'Lu Jun' ,'male' ,to_date('1974-06-03','yyyy-mm-dd'),95031);

commit;
Insert COURSE data
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'Introduction to Computer Science',825);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'operating system' ,804);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'Data circuit' ,856);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'Advanced mathematics' ,100);

commit;
Insert SCORE data
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

commit;
Insert TEACHER data
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'Li Cheng','male',to_date('1958-12-02','yyyy-mm-dd'),'associate professor','Computer Department');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'Zhang Xu','male',to_date('1969-03-12','yyyy-mm-dd'),'lecturer','Department of Electronic Engineering');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'Wang Ping','female',to_date('1972-05-05','yyyy-mm-dd'),'Assistant','Computer Department');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'Liu Bing','female',to_date('1977-08-14','yyyy-mm-dd'),'Assistant','Department of Electronic Engineering');

commit;

Exercise Title

- 1. Query Sname, Ssex, and lass columns for all records in the Student table.

select sname ,ssex ,class	
	from student;

- 2. Query all the units owned by the teacher, that is, the Duplicate Depart column.

select distinct depart
	from teacher;

- 3. Query all records of the Student table.

select * from student;

- 4. Query all records in the Score table with scores between 60 and 80.

select * 
	from score
where degree between 60 and 80; 

- 5. Query the Score table for records with a score of 85, 86 or 88.

select *
	from score
	where degree=85 or degree=85 or degree=88;

- 6. Query the Student table for the records of "95031" class or classmates of "female" gender.

select *
	from student
	where class='95301' or ssex='female';

- 7. Query all records of the Student table in descending order of Class.

select *
	from student
	order by class desc;

- 8. Query all records of the Score table in ascending or descending order of Cno.

select *
	from score
	order by cno asc,degree desc;	

- 9. Query the number of students in class 95031.

select count(*)
	from student
	where class ='95031';

- 10. Query the student number and course number with the highest score in the Score table.

select sno,cno
	from score
	where degree = (
		select max(degree) 
		from score);  //Virtual Table//Single Value

- 11. Query the average score of Course''3-105''.

select avg(degree) 
	from score
	where cno='3-105';

- 12. Query the average score of at least five students in the Score table for courses starting with three.

select avg(degree)
from score
where cno = (select cno 
from score
group by cno
having count(cno)>=5 
) and cno like '3%';

- 13. Sno columns with query minimum score greater than 70 and query maximum score less than 90.

select sno
	from score
	group by sno
	having min(degree)>70 and max(degree)<90;

- 14. Query the Sname, Cno, and Degree columns for all students.

select sname , s.cno ,s.degree
	from student , score s
	where student.sno = s.sno;

- 15. Query the Sno, Cname, and Degree columns for all students.

select sno , cname , degree
	from course , score
	where course.cno = score.cno;

- 16. Query the Sname, Cname, and Degree columns for all students.

select sname , cname ,degree
	from student,course,score
	where student.sno = score.sno and score.cno=course.cno;

- 17. Query the average score of the selected courses in class 95033.

select avg(degree)
	from score,student
	where class = '95033' and score.sno=student.sno;

- 18. Assume that a grade table is created using the following commands:
- Now query the Sno, Cno, and rank columns of all the classmates.

create table grade
(low number,upp number,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');
commit;

select sno,cno,rank
from score,grade
where score.degree 
between grade.low 
and grade.upp;

- 19. Query the records of all students who took the course "3-105" and got higher scores than those of students "109".

select p.sno
from (select degree ,sno 
from score 
where cno='3-105'and sno!='109') p
where p.degree>(select degree 
from score 
where cno='3-105' and sno ='109');

- 20. Query for all records with higher grades than those with student number "109" and course number "3-105".

select sno,cno
from score
where degree>(select degree 
from score 
where sno='109' and cno='3-105')

Sno, Sname, and Sbirthday columns for all students born in the same year with a query and study number of 108.

select sno,sname,sbirthday
from student
where to_char(sbirthday,'yyyy')=(
select to_char(sbirthday,'yyyy') 
from student 
where sno='108');

- 22. Query the students'achievements of "Zhang Xu" teachers.

select sname,degree
from student ,score
where cno=(
select cno
from course
where tno = (
select tno
from teacher
where tname='Zhang Xu')) and student.sno = score.sno;

- 23. Query the records of all students in classes 95033 and 95531.

select *
from student
where class='95033' or class='95031';

- 24. Query for course Cno with 85 or more marks.

select distinct cno
from score
where degree>'85';

- 25. Query the result sheet of the "Computer Department" teachers'courses.

select sname,degree
from score,student,(select cno 
from course s ,(select tno
from teacher
where depart='Computer Department') p
where s.tno=p.tno) f
where student.sno=score.sno and score.cno = f.cno;

- 27. Query the Cno, Sno and Degree of the students whose elective number is "3-105" and their scores are at least higher than those of the students whose elective number is "3-245", and press

Degree Sort high to low.
select cno,sno,degree
from score
where degree>(select min(degree)
from score
where cno='3-245') and cno='3-105';
order by degree desc;

- 28. Query for Cno, Sno and Degree of students whose elective number is "3-105" and their scores are higher than those of students whose elective number is "3-245".

select cno,p.sno,degree
from score s, (select s1.sno,s1.degree as d1,s2.degree as d2
from score s1,score s2
where s1.cno='3-105' 
and s2.cno='3-245' 
and s1.sno=s2.sno) p
where p.d1>p.d2 and p.sno=s.sno;

- 29. Query name s, sex, and birthday for all teachers and classmates.

select name,sex,birthday
from (select sname as name,ssex as sex ,sbirthday as birthday
from student) union all (select tname as name,tsex as sex ,tbirthday as birthday
from teacher) 

- 30. Query the name s, sex and birthday of all "female" teachers and "female" classmates.

select name,sex,birthday
from (select name,sex,birthday
from (select tname as name ,tsex as sex ,tbirthday as birthday
from teacher) union all (select sname as name ,ssex as sex ,sbirthday as birthday
from student))
where sex='female';

- 31. Query the result sheet of the students whose grades are lower than the average of the course.

select degree,a.cno,sno
from score s,(select avg(degree) as avgdegree,cno
from score
group by cno) a
where s.degree<a.avgdegree and s.cno=a.cno; 

- 32. Query Tname and Depart of all teachers.

select tname , depart
from teacher
where tno in(select tno
from course)

- 33. Query Tname and Depart of all unscheduled teachers.

select tname , depart
from teacher
where tno not in(select tno
from course)

- 34. Query the class numbers of at least two boys.

select class
from student
where ssex='male'
group by class
having count(*)>=2;

- 35. Query the records of the students whose surname is not Wang in the Student table.

select sname
from student
where sname not like 'king%';

- 36. Query each student's name and age in the Student table.

select sname ,trunc(months_between(sysdate,sbirthday)/12)
from student;

- 37, query the maximum and minimum Sbirthday date values in the Student table.

select max(sbirthday),min(sbirthday)
from student;

- 38. Query all records in the Student table in order of shift number and age.

select *
from student,(select sno,trunc(months_between(sysdate,sbirthday)/12) as age
from student) a
where a.sno=student.sno
order by class desc , a.age desc;

- 39. Query "male" teachers and their courses.

select cname
from course c1,(
	select cno 
	from teacher,course 
	where teacher.tno = course.tno and teacher.tsex='male') c2
where c1.cno = c2.cno;

select cname
from course
where tno in(select tno
from teacher 
where tsex='male')

- 40. Query the No, Cno, and Degree columns of the highest score students.

select sno,cno,degree 
from score 
where degree=(select max(degree)
from score)

- 41. Query the results table of all "male" students taking the "Introduction to Computer" course

select sname degree 
from student,score ,(
select sno
from student
where ssex='male') p
where cno = 
(select cno
from course
where cname='Introduction to Computer Science') 
and score.sno = p.sno 
and student.sno=score.sno;

Tags: Session Oracle less

Posted on Mon, 09 Sep 2019 19:21:11 -0700 by prakash911