50 SQL statements for a project (Oracle version)

Just learned the basics of Oracle, so I want to find some SQL problems to practice practiced hand, so I chose this 50sql statement, because the found version is not Oracle, so try to use Oracle to achieve.

This blog post is mainly to record the 50 questions in the understanding of the time, the answer is similar to the Internet. Now send the two-day just done.

4 Tables of data
Student table
sql> Select *from Student;

SID SNAME SAGE SSEX
---------- -------------------- ---------- ----------
A 20 female
b 20 Female
C 20 Female
d 20 Male
E 20 Male
F 21 Female
G 23 Male
H 19 Female

8 rows have been selected.

Teacher Table
Sql> Select *from Teacher;

TID Tname
---------- --------------------
A
to B
Geneva C

Course Table
Sql> Select *from Course;

CID CNAME TID
---------- -------------------- ----------
A 01
02 B
C 03

SC table
sql> Select *from SC;

SID CID Score
---------- ---------- ----------
01 01 39
01 02 89
01 03 69
02 01 74
02 02 65
02 03 89
03 01 68
03 02 87
03 03 75
04 01 57
04 02 73

SID CID Score
---------- ---------- ----------
04 03 97
05 01 84
05 02 84
05 03 31
06 01 82
06 02 71
06 03 91
07 01 39
07 03 82

20 rows have been selected.

Created under the WCB user

1. Information and course scores for students with "01" courses higher than "02"

This problem is difficult to be difficult to ensure that under the same SID, how to query C1 score >C2 score Records

I can't think of it.
Finally, it's the solution.

1) This query is for cases where both 01 and 02 courses exist
Sql> Select A.*,b.score Course 1 score, c.score Course 2 score from student A,SC B,SC C
where A.sid=b.sid and A.sid=c.sid and b.cid= ' c.cid= ' and b.score>c.score;

SID SNAME SAGE SSEX Course 1 score Course 2 score
---------- -------------------- ---------- ---------- ----------- -----------
20 B 74 65
F 21 Female 82 71

Fine seconds, in the query field, creates two fields of the same table, which solves the problem of how to compare C1 with C2, which I started with.
Select *from SC where (select Score from SC where cid= ' ") > (select Score from SC where cid= ' 02 ') result in an error, saying that a single-row subquery returns
Return multiple rows of two fields of the same table, so that you can compare the same table data line by row, for example, the sentence B.score>c.score

Then with A.sid=b.sid and a.sid=c.sid it can be determined that C1 and C2 are from the same SID, because in fact, you can also write an equivalence relationship, such as B.sid=c.sid just do not
necessary, because it writes like this, equivalence relation is so b.sid=a.sid=c.sid namely B.sid=c.sid pull.

and b.cid= ' c.cid= ' 02 ' is equivalent to two query statements select *from SC where cid= ' ' select *from sc where cid= ' 02 ' then followed by a sentence
B.score>c.score

2) for cases where there is a "01" course and a "02" course and there is a "01" course but there may not be a "02" course (shown as null if not present) (no longer explained when the same content exists)

Sql> Select A.*,b.score Course 01 score, c.score course 02 score from student A
Left join SC B on a.sid=b.sid and b.cid= ' 01 '
Left joins SC C on A.sid=c.sid and c.cid= ' 02 '
where B.SCORE>NVL (c.score,0);

SID SNAME SAGE SSEX Course 01 score Course 02 Score
---------- -------------------- ---------- ---------- ------------ ------------
20 B 74 65
F 21 Female 82 71

Note I was thinking about these connections (such as inner joins, outer joins, from behind with a subquery) what is the relationship to the condition, the data that we are connecting to, and then filtering the eligible data from here, is this
Like this? It should be, as the above example, I table 1 combined with table 2, table 3 Check out the data, and then to the conditions to judge, if you encounter the field value is empty, then to 0, and then determine whether greater than, and connect it,
is not a general query, but it also added a condition inside, filtered out some of the data

！！！！！！！！！！
NOTE 2: The solution to this problem is to find out a student's two course results, if the result is empty, then converted to 0
Converted to 0, can be used NVL
Find two results, create 2 similar types (i.e. SC) fields, with left connection, A.sid = b.sid and A.sid = C.sid because it is a one-track, so in the check, it will first take
A.sid go to compare with the data of Table B, find one, and then take A.sid to compare with the C table data, find a, because it is to take the same a.sid to compare, so can guarantee that the same student's two courses into
B.cid= ' 01 ' and c.cid= ' 02 ' are limited to that course

If according to the above, the course number 02 does not exist, then the second left the condition of the query is not set, then, will not show any data of C, but it is still in, just empty, to where, when the hair
Now C is empty, it turns to 0, that is, although the left connection, right connection Ah, these, even if not meet the conditions, it will exist, but the value is empty only.

！！！！！！！！！！！！！
Ok, the above note is one of my personal understanding, now say 2) This example, there is a question, why should we use LEFT JOIN to connect? The advantage of using a left connection is that when there is a condition that is not set, the left side
field can be displayed as usual, such as the field Sid 1 of its CID does not have 02 records, but I can still display the SID is 1 of this record, why, because maybe it has a CID 01 records, which
Kind of words, because is to compare the CID to 1>cid for 2 of the data, so that, no CID for 2, we can as 0, that is, although SID is 1 of this record, it does not have CID for 2 records, but it
There's a CID for 1 records, all of which meet our criteria, we should show it.

That's the benefit of connecting the left and right connections, and not letting go of any qualifying record.

In order to verify my above sentence, I inserted a record, no CID is 02, but there is CID 01, SID is 07 of the record

1 Select A.*,b.score Course 01 score, c.score course 02 score from student A
2 LEFT join SC B on a.sid=b.sid and b.cid= ' 01 '
3 Left joins SC C on A.sid=c.sid and c.cid= ' 02 '
B.SCORE>NVL (c.score,0)
Sql>/

SID SNAME SAGE SSEX Course 01 score Course 02 Score
---------- -------------------- ---------- ---------- ------------ ------------
20 B 74 65
F 21 Female 82 71
G 23 Male 39

Summarize
What do you learn from this problem?
1) is the question of how comparisons should be made between other field values that are associated with different values of fields in the same table (such as CID=01 's score compared to CID=02 's score)
2) is to really understand the left to connect these connections, how to explain the most easy to understand? Is that ok? Table 1 and Table 2, I used to connect, can get two tables meet the conditions of that part of the data, but
At the same time, the left table or the right table does not meet the criteria of the data can also be obtained, when it is necessary to compare the value of two tables, some data meet the criteria, but its value is more special (for example, it is
The value is null, but can be used as 0), no connection, it is easy to be filtered out, with the connection, it is not afraid to be screened out, but can be converted to the special value of a comparable value, so as to obtain the most accurate results

2) The student number and the student's name and average score of the students with average scores greater than or equal to 60 points
Sql> Select A.sid,a.sname,cast (avg (B.score) as Decimal (18,2)) Avg_score
2 from student A,sc B where a.sid=b.sid
3 GROUP BY A.sid,a.sname
4 Having CAST (AVG (B.score) as Decimal (18,2)) >=60
5 order by A.sid;

SID SNAME Avg_score
---------- -------------------- ----------
A 65.67
76 b
C 76.67
D 75.67
E 66.33
F 81.33
G 60.5

7 rows have been selected.

Note: Cast is used to convert data types, and decimal is a data type, similar to number

What this problem learns is the role of having

Having is in the group after the filter, that is, you can add a condition, this condition, note that you can use the grouping function, for example, this sentence has cast (avg (B.score as
Decimal (18,2)) >=60
We can not use the grouping function to judge in where, then why is it possible?

Since the data obtained after the packet query is A.SID,A.SNAME,AVG (score), then AVG (score) is already a normal field, which can be used for querying. Could it be seen as a place
The idea of the function is to transform it into a normal field before judging

3) Query the number of teacher Li

Sql> Select COUNT (tname) from teacher where tname like ' a% ';

COUNT (Tname)
------------
3

4) Inquire about the students of a teacher class
1 SELECT distinct a.* from student a,sc b,course C,teacher t
2 where A.sid=b.sid and B.cid=c.cid and C.tid=t.tid and T.tname= ' a '
* * ORDER BY A.sid
Sql>/

SID SNAME SAGE SSEX
---------- -------------------- ---------- ----------
b 20 Female
C 20 Female
d 20 Male
E 20 Male
F 21 Female
G 23 Male

6 rows have been selected.

5) Students who have never been to a teacher class
Sql>select student.* from student where Sid isn't in
(SELECT distinct a.sid from student a,sc b,course C,teacher t
where A.sid=b.sid and B.cid=c.cid and C.tid=t.tid and T.tname= ' a ')
Order by Sid;

SID SNAME SAGE SSEX
---------- -------------------- ---------- ----------
A 20 female
H 19 Female

This problem learns not in

6) Inquire about the students who have been numbered "01" and who have also studied the courses numbered "02" (To do this experiment I understand that, when filtering data, it is a filter, each one must conform to
These conditions behind the where)
Sql>select a.* from student a,sc B,SC C where A.sid=b.sid and A.sid=c.sid and b.cid= ' ' and c.cid= ' 01 '

SID SNAME SAGE SSEX
---------- -------------------- ---------- ----------
b 20 Female
C 20 Female
d 20 Male
E 20 Male
F 21 Female

6) The information of the students who have learned the number "01" and have not learned the course numbered "02".
Sql> Select student.* from Student, sc where student.sid = sc. Sid and SC. Cid = ' student.sid ' and ' not ' in (Select
Sc_2.sid from SC sc_2 where Sc_2.sid = sc. Sid and sc_2.cid = ' a ') ' ORDER by Student.sid;

SID SNAME SAGE SSEX
---------- -------------------- ---------- ----------
G 23 Male

This problem, learned is that a change of thinking, the request is not learned the number of the course of 02 students, I was doing this problem, the idea did not turn around, thinking how to add conditions in the SC table, so that both meet
Learn the number of 01, and did not learn 02, and then still did not solve it, read the answer, only to know that the original can be so, to learn the ID of 02 of the class to find out, and then as long as the ID is not in these IDs, is not
Learning 02, ah, sometimes, really change the thinking, really very simple, but I will encounter these problems next time? Can I turn around?

7) Check the ID of the students who have not studied all the courses
That's how I realized it.

Sql>select A.sid from student A,sc B where a.sid=b.sid
2 GROUP BY A.sid
3 have count (b.cid) < (select count (CID) from course)

Sid
----------
07