當前位置:首頁 » 編程語言 » 筆試題sql語句
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

筆試題sql語句

發布時間: 2022-08-13 20:51:30

sql筆試題尋求答案

select count(*) ,case Fwage > 5000 then 1 else 0 end as type1, case fage > 32 then 1 else 0 end as type2 from emploreeinfo group by type1,type2

有個問題啊 就是你的month是什麼形式的 年月日 還是數字形式 我按數字形式給你寫一個啊 如果是年-月的話 回頭再給你寫
我假設是同一年了 如果不是可以在left join 的on後面改條件
select s1.month,sum(s1.consume) as 當月消費, sum(s2.consume) as 上月消費, sum(s3.consume) as 下月消費, from student s1 left join student s2 on s1.month=s2.month+1 left join student s3 on s1.month=s3.month-1 group by s1.month

❷ sql語句 面試題

A.創建表格CODE省略

註明:學生表PK stu_id 課程表pk cos_id 分數表PK enrollment_id FK stu_id,cos_id

B.插入數據code省略

C.Query

  1. 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;

  2. 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%';

  3. 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

  4. 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;

  5. update student

    set avg_grade =(select avg(grade) X from enrollments group by stu_id

    having student.stu_id = enrollments.stu_id);

  6. 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查詢語句面試題

5.1
select a.username,b.deptname from users a,dept b where a.dept_id=b.id;

5.2
update users set dept_id='9' where dept_id='2';

5.3
select a.deptname,b.count_id from dept a,(select dept_id,count(id) as count_id from users group by dept_id having count(id)>1) b where a.id=b.dept_id;

5.4
select a.deptname,b.count_man,c.count_woman from dept a,(select dept_id,count(sex) as count_man from users where sex='男' group by dept_id) b,(select dept_id,count(sex) as count_woman from users where sex='女' group by dept_id) c where a.id=b.dept_id and a.id=c.dept_id;

5.5
添加歷史記錄表
create table history(
id number(8), -- 記錄編號
dept_id varchar2(5), -- 部門ID
user_id varchar2(5), -- 用戶ID
change_date date -- 變動日期
);

❹ 某公司SQL 筆試題尋找答案

第一道題是查看學生的姓名,選修的課程和對應的學分吧 select '姓名'=(select name from students_t where id=student_course_t.student_id ), '選修課程'=(select name from course_t where id=student_course_t.course_id ), '學分'=(select credit from course_t where id=student_course_t.course_id ) from student_course_t order by credit desc 只能給你第一題的了。後面的如果不自己打一次容易出現錯誤,用嵌套子查詢加相關查詢可以做到, oracle語法的話最後要加分號;

❺ 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查詢語句

DECLARE @Course varchar(20), --課程
@ETime datetime(20), --考試日期
@PResult decimal(5,2) --及格分數線
SET @Course = '英語'
SET @ETime = '2008-08-08'
SET @PResult = 60

SELECT C.Cid AS 班級編號,COUNT(S.Sid) AS 總人數,
COUNT(R.Sid) AS 實考人數,MAX(R.Results) AS 最高分,
MIN(R.Results) AS 最低分,
SUM(CASE WHEN R.Results<@PResult THEN 1 ELSE 0 END) AS 不及格人數,
SUM(CASE WHEN R.Results>=@PResult THEN 1 ELSE 0 END)/COUNT(R.SId) AS 及格率
FROM T_Class C INNER JOIN T_Student S ON C.CIid = S.Cid
INNER JOIN T_Results R ON S.Sid = R.Sid
WHERE R.Course = @Course
AND R.ETime = @ETime
GROUP BY C.Cid

❼ SQL語句分組查詢筆試題

select match_date,
sum(case when match_result ='win' then 1 else 0 end) as amount_win,
sum(case when match_result='lose' then 1 else 0 end) as amount_lose

from t_match
group by match_date