mysql - Need help writing SQL queries(joins, subqueries) -


given following schema, i'm supposed write queries questions.

my first query runs no result , second 1 gives subquery returns more 1 row error.

student (sid, sname, sex, birthdate, gpa) prof (pname, dname) course (cnum, dname, cname) section (cnum, secnum, pname) enroll (sid, cnum, secnum, grade)

  1. for each course, return number of sections (numsections), total number of students enrolled (numstudents), average grade (avggrade), , number of distinct professors taught course (numprofs). show courses in chemistry or computer science department. make sure show courses if have no students. not show course if there no professors teaching course.

  2. return students received higher grade course section average in @ least 2 courses. order number of courses higher average , show top 5.

sql query:

select c.cnum, c.cname, count(*) numsections, count(e.sid) numstudents,  avg(e.grade) avggrade, count(p.pname) numprofs course c      join section s on c.cnum = s.cnum     join enroll e on c.cnum = e.cnum     join prof p on s.pname = p.pname c.cname = 'chemistry' or c.cname = 'computer science' group c.cnum, c.cname;`   select s.sid, s.sname student s       left join enroll e on s.sid = e.sid e.grade > (select avg(grade)                 course c join enroll e2                 on c.cnum = e2.cnum                 group c.cnum                 limit 5);`

about second query...

your subquery getting more 1 row of data. use "<" need yo sure bringing 1 row , 1 column.

and if understand correctly, have show top 5 students order number of times better average of course.. realice learning example wont if give query..

you need select top 5 students, know have count() number of times grade greater avg() of each course have taken, @ point in subquery sould have list of students order number of times achive beeing above average.


-

Comments

Post a Comment

Popular posts from this blog

java - SSE Emitter : Manage timeouts and complete() -

i writing web app multiple listeners(evcentsource sse clients js) connected server . want store sse emitter per connected listener : can done in memory or other means assigning id each client able achieve far now question ; how send response/event specific client connected web app ? while doing sseemmiters stored either getting completed or timed out. how prevent ? how keep sseemmiter available open infinite time (till client closes) , send events selectively .
Read more

jquery - uncaught exception: DataTables Editor - remote hosting of code not allowed -

i'm trying implement crud operation using datatableseditor using datatables.but i'm getting error messages: 1)uncaught exception: datatables editor - remote hosting of code not allowed. please see http://editor.datatables.net details on how purchase editor license 2)typeerror: a.editor undefined 3) $.fn.datatable.editor not constructor" what reason? here configuration: <%@ page language="java" contenttype="text/html; charset=iso-8859-1" pageencoding="iso-8859-1"%> <!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1"> <title>insert title here</title> <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.datatables.min.css"> <link re...
Read more

java - How to resolve error - package com.squareup.okhttp3 doesn't exist? -

i trying convert json java object use of gson in maven, following youtube video guidance - https://www.youtube.com/watch?v=vqgghm9pwe0 , theres error occurs when in main class error - package com.squareup.okhttp3 doesn't exist. code below: java package com.codebeasty.json; import com.squareup.okhttp3.okhttpclient; public class main { private static okhttpclient client = new okhttpclient(); public static void main (string [] args) { } } i put in dependency in pom.xml: <dependencies> <dependency> <groupid>com.squareup.okhttp3</groupid> <artifactid>okhttp</artifactid> <version>3.4.2</version> </dependency> <dependency> <groupid>com.google.code.gson</groupid> <artifactid>gson</artifactid> <version>2.8.0</version> </dependency> </dependencies> i dont understand why doesn't recognize com.squareup. there may need download? have downl...
Read more