In SQL, specifically running on Oracle, write the following query: \"How many st

Question

In SQL, specifically running on Oracle, write the following query: "How many students have more than one major?"

Note: This must be done with a nested query. Creation of temporary tables to solve the problem is not permitted. It must be completed in a single query statment. You may not create any tables nor can you alter the structure of the database. DO NOT insert any tables or modify any tables. The solution must be one statement. The only thing you can do is to create a single SQL query statement that uses the existing structure defined in the schema.

Based on the information provided, the correct output for the query should be 2.

Database Schema

Pertinent Table Information

I am only posting the Major, Student, and Enroll table since the other tables are not relevant for this specific problem.

MAJOR TABLE

STUDENT TABLE

ENROLL TABLE

dept(dname, numphds) course(cno, cname, dname) prof (pname, dname) section(cno, dname, sectno, pname) student(sid, sname, sex, age, gpa) major(sid, dname) enroll(sid, cno, dname, sectno, grade)

Explanation / Answer

As you have same SID in all the tables, so there's No Need of even the other two tables, you can solve this query with MAJOR table only. It took me so long to solve this query because you've not given the table script. Though i've solved it as per my best knowledge.

SELECT COUNT(COUNT(sid)) FROM major GROUP BY sid HAVING COUNT(*)>=2;

This query will calculate the count of all the 'SID' which are repeated for more than once. As a single particular 'SID' is for single particular Student, so there's no neccessity of taking names from the Student table.

And I've used this COUNT twice with the select statement because the inner COUNT will calculate all the 'SID' which are having more than one department and it will display separate count for each same 'SID' used and the outer COUNT will count the number of times the same 'SID' used in the table..

For further explanation, if i've used COUNT only once for the above given question than the output would be like this:

2

2

Because there are only two 'SID' which are in repeated order. So I've used the outer COUNT to count the repeated values..

2 ---- (1.)

2 ----- (2.)

= 2

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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