作业3-第一周笔记+作业

作业3-第一周笔记+作业

概要

第一周笔记整理
2套题目
数据库作业

正文:

• StackEdit is accessible offline after the application has been loaded for the first time.
• Your local documents are not shared between different browsers or computers.
• Clearing your browser's data may delete all your local documents! Make sure your documents are synchronized withGoogle Drive or Dropbox (check out the Synchronization section).

第一周笔记整理

2套题目

第一套题目
SELECT
AVG(english)
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno;

SELECT b.math,a.Name,a.Address,a.Telno
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno
WHERE a.Studentno in (11,22,33,44,55);

SELECT a.Name,b.computer
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno
ORDER BY b.computer DESC;

SELECT a.Studentno,a.Name,(b.english+b.math+b.computer) zcj
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno
WHERE zcj>240
ORDER BY zcj DESC;

第二套题目
SELECT cout(*)
FROM Student
WHERE NAME='王%';

SELECT sid,AVG(score )
FROM SC
WHERE avd(score)>90；

SELECT id FROM(SELECT
a.id,
max(CASE WHEN c.name ='语文' THEN b.score END) chinese,
max(CASE WHEN c.name ='数学' THEN b.score END) math
FROM student a
JOIN sc b ON a.id=b.cid
GROUP BY a.id
HAVING chinese < math) biao

数据库作业

数据库作业
SELECT b.cname,a.id,a.sname
FROM t_student a JOIN t_class b ON a.cid=b.id JOIN t_grade c ON a.id=c.sid;

SELECT a.id,a.sname,c.chinese,c.english,c.math
FROM t_student a JOIN t_class b ON a.cid=b.id JOIN t_grade c ON a.id=c.sid;

SELECT a.id,a.sname,b.cname,c.chinese,c.english,c.math
FROM t_student a JOIN t_class b ON a.cid=b.id JOIN t_grade c ON a.id=c.sid;

SELECT b.title
FROM t_user a JOIN t_article b ON a.id=b.uid
JOIN t_user_comments c ON a.id=c.uid
WHERE a.id=1333502317;

SELECT b.title,a.gstatus
FROM t_article_user_status a JOIN t_article b ON a.aid=b.id
WHERE a.uid=1333502317 and a.gstatus=0;

SELECT b.title,a.cstatus
FROM t_article_user_status a JOIN t_article b ON a.aid=b.id
WHERE a.uid=1333502317 and a.cstatus=0;

SELECT
d.id,title
FROM
t_article d
JOIN(
SELECT
b.fid FROM t_user a JOIN t_user_comments b ON a.id=b.uid
WHERE a.id=1333502317
) biao ON biao.fid=d.id
GROUP BY fid;