MySQL Please show me how to do the following query in MySQL: 1. The names of stu

Question

MySQL

Please show me how to do the following query in MySQL:

1. The names of students who performed lower than average in each course

Here is the table schema

CNAME 21 Computer Science 17 Chemist 15 PhysicS 12 Mathematics l 13 Mathematics III RESULT CNO CNO STUDENT SNO SNAME ADDRESS SEX SCORE 31 L. Gatlin Holcomb F 11 N. Park Whitney M 22 S. Conner: Holcomb F 24 D. Davissc E. Quad M 19 1. Blake Holcomb M 4 T. Smith Cambridg F SNO 2 2 2 2 2 2 31 14 2 63 4 Histo 3 Geography 2 Biology 72 1 N. J. Sloan Whitney F 14 D. E. Knutl Whitney M 6 J. Brown Whitney 2 M. Robert Cambridg M 14 33 U. Smith Cambridg F 14 37 2 13 13 13 13 97 72 11 NULL 14 61 52 63

Explanation / Answer

--Sql Server 2014 Express Edition
--Batches are separated by 'go'
create table course(cno int,cname char(20));
create table student(sno int,sname char(20),saddress char(20),sex char(20));
create table result(cno int,sno int ,score int);

INSERT INTO course (cno,cname) VALUES (21,'computer science');
INSERT INTO course(cno,cname) VALUES (17,'chemistry');
INSERT INTO course (cno,cname) VALUES (15,'physics');
INSERT INTO course (cno,cname) VALUES (12,'mathematics i');
INSERT INTO course(cno,cname) VALUES (13,'mathematicsiii');
INSERT INTO course (cno,cname) VALUES (4,'history');
INSERT INTO course (cno,cname) VALUES (3,'geography');
INSERT INTO course (cno,cname) VALUES (2,'biology');

INSERT INTO student (sno,sname,saddress,sex) VALUES (31,'L.Gatlin','holcomb','F');
INSERT INTO student (sno,sname,saddress,sex) VALUES (11,'L.N.Park','whitney','M');
INSERT INTO student (sno,sname,saddress,sex) VALUES (22,'S.Conner','holcomb','F');
INSERT INTO student (sno,sname,saddress,sex) VALUES (24,'D.Davissc','e.quad','M');
INSERT INTO student (sno,sname,saddress,sex) VALUES (19,'I.Blake','holcomb','M');
INSERT INTO student (sno,sname,saddress,sex) VALUES (4,'T.Smith','cambridge','F');
INSERT INTO student (sno,sname,saddress,sex) VALUES (1,'N.J.Sloar','whitney','F');
INSERT INTO student (sno,sname,saddress,sex) VALUES (14,'D.E.Knut','whitney','M');
INSERT INTO student (sno,sname,saddress,sex) VALUES (6,'J.Brown','whitney','M');
INSERT INTO student (sno,sname,saddress,sex) VALUES (2,'M.Robert','cambridge','M');
INSERT INTO student (sno,sname,saddress,sex) VALUES (33,'U.Simth','cambridge','F');
INSERT INTO result (cno,sno,score) VALUES (2,19,71);
INSERT INTO result (cno,sno,score) VALUES (2,31,82);
INSERT INTO result (cno,sno,score) VALUES (2,14,63);
INSERT INTO result (cno,sno,score) VALUES (2,2,69);
INSERT INTO result (cno,sno,score) VALUES (2,24,72);
INSERT INTO result (cno,sno,score) VALUES (2,33,94);
INSERT INTO result (cno,sno,score) VALUES (3,19,80);
INSERT INTO result (cno,sno,score) VALUES (3,14,58);
INSERT INTO result (cno,sno,score) VALUES (12,24,77);
INSERT INTO result (cno,sno,score) VALUES (12,4,82);

INSERT INTO result (cno,sno,score) VALUES (12,14,37);

INSERT INTO result (cno,sno,score) VALUES (12,2,78);

INSERT INTO result (cno,sno,score) VALUES (12,33,90);
INSERT INTO result (cno,sno,score) VALUES (13,33,78);
INSERT INTO result (cno,sno,score) VALUES (13,4,82);
INSERT INTO result (cno,sno,score) VALUES (13,24,78);
INSERT INTO result (cno,sno,score) VALUES (13,6,44);

INSERT INTO result (cno,sno,score) VALUES (15,33,97);
INSERT INTO result (cno,sno,score) VALUES (15,4,72);
INSERT INTO result (cno,sno,score) VALUES (15,11,NULL);
INSERT INTO result (cno,sno,score) VALUES (15,14,61);
INSERT INTO result (cno,sno,score) VALUES (15,6,52);
INSERT INTO result (cno,sno,score) VALUES (15,24,63);

SELECT SNAME from student,result where student.cid in (select cid as a,avg(score) as AVG group by cid) and score> AVG and a.cid=student.cid;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.