⑴ sql面试题
这个肯定可以
SELECT t.Code, Student.Name, t.Subject, t.Score
FROM Student RIGHT JOIN
(SELECT Code, Subject, Score
FROM Exam t
WHERE NOT EXISTS (SELECT 1 FROM Exam WHERE Subject = t.Subject AND Score > t.Score)) t
ON t.Code = Student.Code
⑵ 一个sql面试题
select t1.name,t2.name from department t1,department t2 where t1.name<t2.name;
********
测试log：
[TEST@ORA1] SQL>select t1.name,t2.name from department t1,department t2 where t1.name<t2.name;
N N
- -
a b
a c
a d
b c
b d
---
以上，希望对你有所帮助。
⑶ JAVA和SQL面试题
(2)查询B列中长度大于2的记录，结果为
A B
1 abc
可能么？ 1对应a 而不是abc
--> 生成测试数据: @T
DECLARE @T TABLE (A INT,B VARCHAR(3))
INSERT INTO @T
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'abc' UNION ALL
SELECT 4,'c' UNION ALL
SELECT 5,'a' UNION ALL
SELECT 6,'b' UNION ALL
SELECT 7,'ba'
--SQL查询如下:
select min(A) as A,B from @T group by B having count(*) > 1
select * from @T where len(B) > 2
select * from @T where B like '%A%' and B like '%B%'
/*
A B
----------- ----
1 a
2 b
(2 行受影响)
A B
----------- ----
3 abc
(1 行受影响)
A B
----------- ----
3 abc
7 ba
(2 行受影响)
*/
⑷ SQL语句面试题
SELECT*,
(SELECTCOUNT(*)FROM(SELECTCOUNT(*),b.sidFROMscbLEFTJOINcoursecONb.Cid=c.CidGROUPBYb.sid,c.tid)owWHEREow.sid=a.sid)as'
选课数量',
(SELECTsum(Score)FROMScdWHEREa.sid=d.sid)as'总成绩'
FROMstudenta
;
SELECTd.sid,d.snamefromteachera
LEFTJOINcoursebona.tid=b.tid
LEFTJOINscconc.cid=b.cid
leftJOINstudentdONd.sid=c.sid
WHEREa.tname='叶萍';
SELECTsid,sname
FROMstudent
WHEREsidin(selecta.sid
FROMscAleftjoinscbona.sid=b.sid
WHEREa.cid=1andb.cid=2anda.score>b.score)
SELECTsc.Sid,sum(CASEWHENc.Cname='
数学'thensc.Scoreelse0end)数学,SUM(casewhenc.Cname='物理
'THENsc.ScoreELSE0END)物理,AVG(sc.Score)平均分
FROMsc
INNERJOINCourseconsc.Cid=c.Cid
WHEREc.Cnamein('数学','物理')
GROUPBYsc.Sid
ORDERBYAVG(sc.Score)DESC
insertintoSc(Sid,Cid,Score)values(003,3,85);
insertintoSc(Sid,Cid,Score)values(003,3,30);
首先这个不知道是你i写错了还是怎么一个人化学成绩有2个所以我在这里按照你这个上做的查询所以有一个人是选了单个课程
⑸ 几个面试中遇到的SQL题，大家帮帮忙
1，select * from pages where url='..' union all
select * from pages where title='...' union all
select * from pages where body='...'
2,select case when a>b then a when b<=c then c else b end from 表
3，A表建触发器
CREATE TRIGGER myt
ON 表A
FOR UPDATE
AS
If UPDATE(主键)
BEGIN
update b
set 同字段1=a.同字段1,同字段2=a.同字段2
from 表B b,inserted a
where b.主键=a.主键
END
⑹ 面试题目(sql)
1、忍不住想说一句，因为第一题中的字段类型是
【日期型】，而各种数据库操作日期型数据有不同的方法，没有一种共通的方法，所以脱离了数据库而言没有一种共通的sql。
2、select
ID,NAME,ADDRESS,PHONE,LOGDATE
from
T
where
ID
in(
select
ID
from
T
group
by
NAME
having
count(*)>1)
order
by
NAME;
3、delete
from
T
where
ID
not
in
(select
min(id)
from
T
group
by
name);
4、update
T
set
T.ADDRESS=(select
E.ADDRESS
from
E
where
E.NAME=T.NAME),
T.PHONE=(select
E.PHONE
from
E
where
E.NAME=T.NAME);
5、这个不同的数据库也有不同的处理方法，不能脱离数据库谈了。
如：SqlServer或者access可以使用
top
oracle可以使用
rownum
等
---
以上，希望对你有所帮助。
⑺ 经典的SQL面试题目，百度有答案，谁有更简洁的
select a.id,a.proid as pro1,b.proid as pro2 from (select * from tablename where proid =1) a,(select * from tablename where proid =2) b where a.id=b.id
⑻ sql语句 面试题
A.创建表格CODE省略
注明：学生表PK stu_id 课程表pk cos_id 分数表PK enrollment_id FK stu_id,cos_id
B.插入数据code省略
C.Query
select s.stu_id,stu_name,count(cos_id) from student s,enrollments e where s.stu_id = e.stu_id and e.grade>60 group by s.stu_id,stu_name;
select e.stu_id,s.stu_name,c.cos_name from student s,enrollments e,course c
where s.stu_id = e.stu_id
and e.cos_id = c.cos_id
and c.cos_name = 'CHINESE'
and s.stu_name like 'W%';
select stu_id,stu_name from (select e.stu_id,stu_name,cos_name from enrollments e,student s,course c
where s.stu_id = e.stu_id
and e.cos_id = c.cos_id
and c.cos_name IN ('CHINESE','MUSIC'))
group by stu_id,stu_name
having count(cos_name) = 2
select distinct e.cos_id,c.cos_name,count(e.stu_id) stu_count,count(e.stu_id)-NVL(A.FAIL,0) upscore,(count(e.stu_id)-NVL(A.FAIL,0))/count(e.stu_id) rate from
(select cos_id,count(stu_id) fail from enrollments where grade<60 group by cos_id) a,enrollments e,course c
where e.cos_id = a.cos_id(+)
and e.cos_id = c.cos_id
group by e.cos_id,NVL(a.fail,0),c.cos_name;
update student
set avg_grade =(select avg(grade) X from enrollments group by stu_id
having student.stu_id = enrollments.stu_id);
select stu_id,avg(grade) from
(select stu_id,cos_id,grade,row_number() over(partition by stu_id order by grade ) X from enrollments)
group by stu_id
having count(*)<=2
UNION
select A.stu_id,avg(A.grade)from
(select stu_id,cos_id,grade,row_number() over(partition by stu_id order by grade ) X from enrollments) A,
(select stu_id,count(*) c from
(select stu_id,cos_id,grade,row_number() over(partition by stu_id order by grade ) X from enrollments)
group by stu_id) B
where A.stu_id = B.stu_id
and A.x>1 and x<B.c
group by A.stu_id,b.c
_________________________________________________
环境：oracle 10g/TOAD 以上代码均通过测试，如有问题，请联系，谢谢
⑼ 求软件测试面试题中的sql题目
2.查询所有班级的名称，和所有版中女生人数和女生的平均分。 1. select * from student where score>80 and s_id in (select sid from student_,fuidoj
⑽ sql语句的面试题目
select (sum(id)+sum(no)) from table1