Home » SQL & PL/SQL » SQL & PL/SQL » Write an SQL query to report the students (student_id, student_name) being “quiet” in ALL exams. A “
Write an SQL query to report the students (student_id, student_name) being “quiet” in ALL exams. A “ [message #684826] Fri, 03 September 2021 08:50 Go to next message
vritik
Messages: 1
Registered: September 2021
Junior Member
-- A "quite" student is the one who took at least one exam and didn't score
neither the high score nor the low score.

-- Write an SQL query to report the students (student_id, student_name)
being "quiet" in ALL exams.

-- Don't return the student who has never taken any exam. Return the result
table ordered by student_id.

-- The query result format is in the following example.

-- Student table:
-- +-------------+---------------+
-- | student_id | student_name |
-- +-------------+---------------+
-- | 1 | Daniel |
-- | 2 | Jade |
-- | 3 | Stella |
-- | 4 | Jonathan |
-- | 5 | Will |
-- +-------------+---------------+

-- Exam table:
-- +------------+--------------+-----------+
-- | exam_id | student_id | score |
-- +------------+--------------+-----------+
-- | 10 | 1 | 70 |
-- | 10 | 2 | 80 |
-- | 10 | 3 | 90 |
-- | 20 | 1 | 80 |
-- | 30 | 1 | 70 |
-- | 30 | 3 | 80 |
-- | 30 | 4 | 90 |
-- | 40 | 1 | 60 |
-- | 40 | 2 | 70 |
-- | 40 | 4 | 80 |
-- +------------+--------------+-----------+

-- Result table:
-- +-------------+---------------+
-- | student_id | student_name |
-- +-------------+---------------+
-- | 2 | Jade |
-- +-------------+---------------+

Re: Write an SQL query to report the students (student_id, student_name) being “quiet” in ALL exams. A “ [message #684827 is a reply to message #684826] Fri, 03 September 2021 11:54 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

we don't do homework but we can help you to do so, post what you already tried, what are your thoughts about your query, why it does not work...

Note that you have to define what are high and low scores. Do you mean the highest and lowest scores in the "exam" table? Is this overall or by exam (exam_id)?
In the same way, you have to be very clear and specific, deterministic in your specification: "report the students being "quiet" in ALL exams" does "ALL exams" refer to "ALL exams" shown in "exam" table or "ALL exams" the student has taken (obviously, it is the later but the specification MUST not depend on interpretation, in any way)?

In the end, you need:
1) the lowest score (in the meaning you want, see above)
2) the highest score
3) the count of exams taken by each student
4) the count of exams quietly scored by each student that is with a score different from (1) and (2)
*) return those with (3) = (4)
For all this, you only need the "exam" table, you will join (*) with the "student" table to get the final result with the name(s).

Previous Topic: Error ocurred calling a Web Service using UTL_HTTP
Next Topic: query to display employee whose code is 03 is not inserted
Goto Forum:
  


Current Time: Thu Mar 28 07:55:39 CDT 2024