⑴ 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