您好,登錄后才能下訂單哦!
小編給大家分享一下SQL數(shù)據(jù)庫(kù)的案例分析,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
/* Navicat SQLite Data Transfer Source Server : school Source Server Version : 30808 Source Host : :0 Target Server Type : SQLite Target Server Version : 30808 File Encoding : 65001 Date: 2021-12-23 16:06:04 */ PRAGMA foreign_keys = OFF; -- ---------------------------- -- Table structure for Course -- ---------------------------- DROP TABLE IF EXISTS "main"."Course"; CREATE TABLE Course( courseid integer primary key autoincrement, courseme varchar(32), teacherid int ); -- ---------------------------- -- Records of Course -- ---------------------------- INSERT INTO "main"."Course" VALUES (3001, '語(yǔ)文', 1001); INSERT INTO "main"."Course" VALUES (3002, '數(shù)學(xué)', 1002); -- ---------------------------- -- Table structure for Mark -- ---------------------------- DROP TABLE IF EXISTS "main"."Mark"; CREATE TABLE Mark( userid integer, courseid integer not null, score int default 0 ); -- ---------------------------- -- Records of Mark -- ---------------------------- INSERT INTO "main"."Mark" VALUES (2001, 3001, 89); INSERT INTO "main"."Mark" VALUES (2001, 3002, 90); INSERT INTO "main"."Mark" VALUES (2002, 3001, 66); INSERT INTO "main"."Mark" VALUES (2003, 3002, 85); -- ---------------------------- -- Table structure for sqlite_sequence -- ---------------------------- DROP TABLE IF EXISTS "main"."sqlite_sequence"; CREATE TABLE sqlite_sequence(name,seq); -- ---------------------------- -- Records of sqlite_sequence -- ---------------------------- INSERT INTO "main"."sqlite_sequence" VALUES ('Teacher', 1002); INSERT INTO "main"."sqlite_sequence" VALUES ('Student', 2002); INSERT INTO "main"."sqlite_sequence" VALUES ('Course', 3002); -- ---------------------------- -- Table structure for Student -- ---------------------------- DROP TABLE IF EXISTS "main"."Student"; CREATE TABLE Student( userid integer primary key autoincrement, username varchar(32), userage int, usersex varchar(32) ); -- ---------------------------- -- Records of Student -- ---------------------------- INSERT INTO "main"."Student" VALUES (2001, '小明', 18, '男'); INSERT INTO "main"."Student" VALUES (2002, '小紅', 18, '女'); -- ---------------------------- -- Table structure for Teacher -- ---------------------------- DROP TABLE IF EXISTS "main"."Teacher"; CREATE TABLE Teacher( teacherid integer primary key autoincrement, teachername varchar(32) ); -- ---------------------------- -- Records of Teacher -- ---------------------------- INSERT INTO "main"."Teacher" VALUES (1001, '張三'); INSERT INTO "main"."Teacher" VALUES (1002, '李四');
1、查詢“語(yǔ)文”課程比“數(shù)學(xué)”課程成績(jī)低的所有學(xué)生的學(xué)號(hào)
select a.userid from (select userid,score from Mark where courseid ='3001')a, (select userid,score from Mark where courseid ='3002')b where a.userid = b.userid and a.score<b.score;
2、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)
select userid,avg(score) from Mark group by userid having avg(score)>60;
3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)
select s.userid ,s.username ,count_courseid as 選課數(shù), sum_score as 總成績(jī) from Student s left join (select userid,count(courseid ) as count_courseid,sum(score) as sum_score from Mark group by userid )sc on s.userid = sc.userid;
4、查詢姓‘李'的老師的個(gè)數(shù):
select count(teachername ) from Teacher where teachername like '張%';
5、檢索語(yǔ)文課程分?jǐn)?shù)小于60,按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào):
select userid ,score from Mark where courseid ='3001' and score<60 order by score desc;
6、查詢學(xué)/沒學(xué)過(guò)”張三”老師講授的任一門課程的學(xué)生姓名
select username from Student where userid in ( select userid from Mark,Course,Teacher where Course.teacherid = Teacher.teacherid and Mark.courseid = Course.courseid and Teacher.teachername ='張三' );
7、查詢?nèi)繉W(xué)生選修的課程和課程號(hào)和課程名:
select courseid ,courseme from Course where courseid in (select courseid from Mark group by courseid);
8、檢索選修兩門課程的學(xué)生學(xué)號(hào):
select userid from Mark group by userid having count(8) == 2;
9、查詢各個(gè)課程及相應(yīng)的選修人數(shù)
select courseid ,count(*) from Course group by courseid ;
10、查詢選修“張三”老師所授課程的學(xué)生中,成績(jī)最高的學(xué)生姓名及其成績(jī)
select Student.username ,Mark.score from Mark left join Student on Mark.userid = Student.userid left join Course on Mark.courseid = Course.courseid left join Teacher on Course.teacherid = Teacher.teacherid where Teacher.teachername = '張三' and Mark.score = ( select max(score) from Mark sc_1 where Mark.courseid = sc_1.courseid);
11、求選了課程的學(xué)生人數(shù):
select count(2) from (select distinct userid from Mark)a;
12、查詢課程編號(hào)為“語(yǔ)文”且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名
select Mark.userid,Student.username from Mark left join Student on Mark.userid = Student.userid where Mark.courseid = '3001' and Mark.score>80;
13、查詢每門課程的平均成績(jī),結(jié)果按平均成績(jī)升序排序,平均成績(jī)相同時(shí),按課程號(hào)降序排列
select courseid ,avg(score) from Mark group by courseid order by avg(score),courseid desc;
14、查詢課程名稱為“數(shù)學(xué)”,且分?jǐn)?shù)高于85的學(xué)生名字和分?jǐn)?shù):
select c.courseme ,Student.userid ,Student.username ,Mark.score from Course c left join Mark on Mark.courseid = c.courseid LEFT JOIN Student on Student.userid = Mark.userid where c.courseme = '數(shù)學(xué)' and Mark.score>85;
以上是“SQL數(shù)據(jù)庫(kù)的案例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對(duì)大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。