Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7a1e662

Browse files
committed
SQL经典笔试题目
1 parent2cf01ed commit7a1e662

File tree

1 file changed

+135
-33
lines changed

1 file changed

+135
-33
lines changed

‎docs/database/SQL经典笔试题目.md

Lines changed: 135 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -88,75 +88,177 @@ insert into SC values('07' , '02' , 89);
8888
insert into SCvalues('07' ,'03' ,98);
8989
```
9090

91+
##2.SQL题目(1~10)
9192

93+
###2.1 第一题
9294

93-
- 查询所有同学的学号、姓名、选课数、总成绩；
95+
>查询“01”课程比“02”课程成绩高的所有学生的学号；
9496
9597
```sql
96-
98+
selectt1.sid
99+
from
100+
(select sid,scorefrom scwhere cid='01')as t1
101+
join
102+
(select sid,scorefrom scwhere cid='02')as t2
103+
ont1.sid=t2.sidandt1.score>t2.score
97104
```
98105

106+
###2.2 第二题
99107

100-
101-
- 查询平均成绩大于60分的同学的学号和平均成绩；
108+
>查询平均成绩大于60分的同学的学号和平均成绩；
102109
103110
```sql
104111
select sid,avg(score)as avgscore
105112
from sc
106-
group byscore
113+
group bysid
107114
having avgscore>60
108115
```
109116

110-
- 查询“01”课程比“02”课程成绩高的所有学生的学号；
117+
###2.3 第三题
118+
119+
>查询所有同学的学号、姓名、选课数、总成绩;
111120
112121
```sql
113-
SELECTt1.sid
114-
FROM
115-
(select sid,score
116-
from sc
117-
where cid='01')as t1
118-
JOIN
119-
(select sid,score
120-
from sc
121-
where cid='02')as t2
122-
ONt1.sid=t2.sidandt1.score>t2.score
122+
selectt1.sid ,t1.sname ,t2.cnt ,t2.total_score
123+
from
124+
(select sid,snamefrom studentgroup by sid)as t1
125+
join
126+
(select sid,count(*)as cnt ,sum(score)as total_scorefrom scgroup by sid )as t2
127+
ont1.sid=t2.sid
123128
```
124129

125-
- 查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
126-
- 查询姓“李”的老师的个数；
130+
###2.4 第四题
131+
132+
>查询姓“李”的老师的个数;
127133
128134
```sql
129-
select*from teacherwhere tnamelike"李%"
135+
selectcount(*)as count
136+
from teacher
137+
where tnamelike"李%"
130138
```
131139

132-
- 查询没学过“张三”老师课的同学的学号、姓名；
140+
###2.5 第五题
141+
142+
>查询没学过“张三”老师课的同学的学号、姓名；
133143
134144
```sql
135145
select distinct sid,sname
136146
from student
137147
where sid notin (
138-
select sidfrom sc
139-
join courseas consc.cid=c.cid
140-
join teacheras tonc.tid=t.tid
141-
wheret.tname='张三'
148+
select sid
149+
from sc s
150+
left join courseas cons.cid=c.cid
151+
left join teacheras tonc.tid=t.tid
152+
where tname='张三'
153+
)
154+
```
155+
156+
###2.6 第六题
157+
158+
>查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；**（类比题目1）**
159+
160+
```sql
161+
select sid ,sname
162+
from student
163+
where sidin
164+
(
165+
select distinctt1.sid
166+
from
167+
(select sid,scorefrom scwhere cid='01')as t1
168+
join
169+
(select sid,scorefrom scwhere cid='02')as t2
170+
ont1.sid=t2.sid
142171
)
143172
```
144173

145-
- 查询学过“张三”老师所教的课的同学的学号、姓名；
174+
###2.7 第七题
175+
176+
>查询学过“张三”老师所教的课的同学的学号、姓名；**（类比题目5）**
146177
147178
```sql
148179
select distinct sid,sname
149180
from student
150181
where sidin (
151-
select sidfrom sc
152-
join courseas consc.cid=c.cid
153-
join teacheras tonc.tid=t.tid
154-
wheret.tname='张三'
182+
select sid
183+
from sc s
184+
left join courseas cons.cid=c.cid
185+
left join teacheras tonc.tid=t.tid
186+
where tname='张三'
187+
)
188+
```
189+
190+
###2.8 第八题
191+
192+
>查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；**（类比题目1和题目6）**
193+
194+
```sql
195+
select sid ,sname
196+
from student
197+
where sidin
198+
(
199+
select distinctt1.sid
200+
from
201+
(select sid,scorefrom scwhere cid='01')as t1
202+
join
203+
(select sid,scorefrom scwhere cid='02')as t2
204+
ont1.sid=t2.sidandt1.score<t2.score
155205
)
156206
```
157207

158-
- 查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名;
159-
- 查询**所有课程**成绩小于60分的同学的学号、姓名；
160-
- 查询没有学全所有课的同学的学号、姓名；
161-
-
208+
###2.9 第九题
209+
210+
>查询**所有课程**成绩小于60分的同学的学号、姓名；
211+
212+
```sql
213+
select distinct sid ,sname
214+
from student
215+
where sidin (
216+
select sidfrom scwhere score<60
217+
)
218+
```
219+
220+
###2.10 第十题
221+
222+
>查询没有学全所有课的同学的学号、姓名；
223+
224+
```sql
225+
selects.sid ,s.sname
226+
from studentas s
227+
left join sc
228+
onsc.sid=s.sid
229+
group bys.sid
230+
havingcount(sc.cid )<3
231+
```
232+
233+
##3.SQL题目(11~20)
234+
235+
###3.1 第十一道
236+
237+
>查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
238+
239+
```sql
240+
241+
```
242+
243+
###3.2 第十二道
244+
245+
>查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名;
246+
247+
###3.3 第十三道
248+
249+
###3.4 第十四道
250+
251+
###3.5 第十五道
252+
253+
###3.6 第十六道
254+
255+
###3.7 第十七道
256+
257+
###3.8 第十八道
258+
259+
###3.9 第十九道
260+
261+
###3.10 第二十道
262+
263+
162264

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp