SQL Queries?

urahonky

I hate to make yet another thread but I'm in (again) some dire need of help. I am currently working with SQL and I have no idea how to do this SQL query stuff.

We had a midterm in which SQL query's were 50% of the grade, and I got a 52% on it because I have no idea what's going on. The instructor did them on the board but I can't understand a word the idiot says so I wrote down what was written but it doesn't help that I don't know how/why you can do stuff.

This is the database I've been given and the relations:

STUDENT(SNAME, SNO)
L Tree, S1
D Pond, S2
M Lake, S3
J Bond, S4
S Bark, S5
L Leaf, S6

COURSE(CNAME, CNO)
Comp 1, C1
Math 2, C2
Phys 3, C3
Comp 3, C4

RESULT(SNO, CNO, MARK)
S1, C1, 77
S1, C2, 80
S2, C2, 93
S2, C3, 88
S2, C4, 91
S3, C2, 74
S3, C3, 89
S4, C2, 75
S4, C3, 85
S4, C4, 55
S5, C1, 50
S5, C4, 61
S6, C2, 77
S6, C3, 88
S6, C4, 54

Okay. So I typed it up in SQL and I got it setup in my database. Now I'm supposed to use the SELECT FROM WHERE stuff to get the answers.

One of the questions is asking: Find the names of the courses that 'D Pond' failed or 'J Bond' passed.

At this point I'm sure I need to get a nested query which is what I can't do. I know how to find information from one area but I am in the dark on how to do nested ones. I know it's like:

SELECT c.CNAME
FROM COURSE as c, STUDENT as s, RESULT as r
WHERE (SELECT *
FROM *)

You know? But I can't logically think on how to solve this problem. (I could probably do SELECT CNAME FROM COURSE but I like to make aliases).

I am not sure how to do an or statement in SQL. Any help is appreciated. Again, sorry for the 3rd thread of these in the past few weeks. I can't ask the instructor for help because I honestly can't understand a word the man says.

e: fixed the crucial word

solsovly

Excellent website for basics: http://www.w3schools.com/sql/default.asp

I'll give you some hints.

1) How are the tables related? Where are the joins?
2) What is the filtering condition for the query?

Edit: You don't need a nested query. If you are really confused start with just one table.

Here is how to get the passing marks without the Person name or Course Name.

select RESULT.SNO,
RESULT.CNO,
RESULT.MARK
from RESULT
where RESULT.MARK >= 60

Infidel

You're actually wanting to do some joins on those tables, not just the crossproduct that you are now and looking with a WHERE clause for the "joints."

SELECT * FROM STUDENT s INNER JOIN COURSE c ON (s.sno = c.sno) WHERE ____ is an example of a join. You went over joins and such in class right?

From there you would look for the appropriate conditions in the WHERE clause.

urahonky

Oh God, that's a good site. Thank you solsovly.

Infidel, this is what I've got and it gave me the answer:

mysql> select c.CNAME
    -> from student as s, result as r, course as c
    -> where (s.SNAME='D Pond' and r.MARK<60
    -> and s.SNO=r.SNO and c.CNO=r.CNO)OR(
    -> s.SNAME='J Bond' and r.MARK>60
    -> and s.SNO=r.SNO and c.CNO=r.CNO);
+--------+
| CNAME  |
+--------+
| Math 2 |
| Phys 3 |
+--------+
2 rows in set (0.00 sec)

I think I joined them without actually using join, right?

Dehumanized

Err, isn't that question kind of a trick?

Given this data in your table:


D Pond, S2
J Bond, S4

S2, C2, 93
S2, C3, 88
S2, C4, 91

S4, C2, 75
S4, C3, 85
S4, C4, 55

S2 (Pond) didn't fail any classes, so an inclusive and question looking at "which classes did Pond fail and another student pass" (it doesn't really matter who the other student is) would result in 0 rows, because Pond didn't fail any of his classes. The teacher is almost certainly looking for the process, but if you've got a query that doesn't result in 0 rows it's probably incorrect.


EDIT: If you change the "OR" in your statement to an "AND" it should correctly result in 0 rows. Like this:
select c.CNAME
-> from student as s, result as r, course as c
-> where (s.SNAME='D Pond' and r.MARK<60
-> and s.SNO=r.SNO and c.CNO=r.CNO)AND(
-> s.SNAME='J Bond' and r.MARK>60
-> and s.SNO=r.SNO and c.CNO=r.CNO);

With an "OR" check there it's returning rows if Pond failed, or if Bond passed. Since Bond passed C2 and C3, but not C4 those two classes are in the result set.

Aetheri

Joins are just selections on cartesian products, so yes, that is a join. But it doesn't look like the right answer to me... if you want the courses where D Pond failed and J Bond passed, you should get an empty set- From the looks of that table, D Pond passed all his classes.

edit: by which I mean selections in relational algebra, not SQL

urahonky

But J Bond passed 2 classes, did he not?

S4, C2, 75
S4, C3, 85

Passing is any mark > 60

urahonky

Seriously? I am sorry guys. I used the word "and" when I meant "or". Worst time in the world to do that.... Ugh. I need some sleep.

Dehumanized

In which case, your answer is correct.

urahonky

Phew. I was so confused... I was reading my paper over and over again... then I decided to read what I posted and I found out. My bad.

urahonky

Okay I have a new one:

Find the names of students who failed some courses that 'J Bond' passed.

So this was my query:

mysql> select s.SNAME
    -> from student as s, course as c, result as r
    -> where (r.MARK<60 and s.SNO=r.SNO and c.CNO=r.CNO)and(
    -> s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and 

c.CNO=r.CNO);
Empty set (0.00 sec)

Which, by looking at the numbers, seems correct. But the next query asks

Find the names of students who passed all the courses that 'J Bond' passed.

Which means it should be all the students, so I typed:

mysql> select s.SNAME
    -> from student as s, course as c, result as r
    -> where (r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO)and(
    -> s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and 

c.CNO=r.CNO);

Which returns J Bond twice.

I guess I need to learn how to do the join command?


e:

mysql> select distinct s.SNAME
    -> from student as s, course as c, result as r
    -> where (r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO)or(
    -> s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO);
+--------+
| SNAME  |
+--------+
| L Tree |
| D Pond |
| M Lake |
| J Bond |
| S Bark |
| L Leaf |
+--------+
6 rows in set (0.00 sec)

That looks right but that could be because the answer is all the students who took C2 and C3... I don't think it's right.

Infidel

You get the right answer yes but you're doing it by building the cartesian product (every row of a table is joined with every row of another table which is then joined with every row from another table.)

This is slow and depending on your prof may be wrong if he's expecting you to actually perform some joins.

"and s.SNO=r.SNO and c.CNO=r.CNO" in the WHERE is after the fact, you want to avoid records that don't make sense in the first place.

Infidel

Your logic is also not going to work because you're looking at the same record when testing the WHERE clause.

-> where (r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO)or(
-> s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO);

If you ignore the key matching which should be in the join instead, you're looking at this:

r.MARK > 60 or (s.SNAME = 'J Bond' and r.MARK > 60)

Note how the second part of that (after the or) is trivial. Name isn't even mattering.

Think you have the right answer still?

Aetheri

You should learn how to use the join command, but I don't think that's the issue here. Think about the conditionals you're using in those where clauses, and you'll find something is quite wrong there...

Hint:
where (r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO)
and
(s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and
c.CNO=r.CNO)

is checking every row of the database for... what exactly?

edit: Man, I need to type faster.

urahonky

Is this an instance in which I would have to use a nested query? Because I see what you guys are saying. Right now I'm just getting marks > 60. Nothing more.

So what I'd need to do is get the marks >60 that J Bond is in as well right? Would that be a union?

Infidel

A union would combine two sets of results. How would you phrase it in that way? Probably not a straight union.

You might want to break it down. What courses did J Bond pass? Then, who passed those courses?

urahonky

So something like:

select s.SNAME
from student s, result r, course c
where (select s.SNAME
from student s, result r, course c
where s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO)and(
select s.SNAME
from student s, result r, course c
where r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO);

?

(also how do you use join if I have to join 3 tables together? I tried

student join result on student.SNO=result.SNO, course join result on course.CNO=result.CNO

but that obviously won't work since result is used twice.)

Aetheri

urahonky wrote: »

So something like:

select s.SNAME
from student s, result r, course c
where (select s.SNAME
from student s, result r, course c
where s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO)and(
select s.SNAME
from student s, result r, course c
where r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO);

?

(also how do you use join if I have to join 3 tables together? I tried

student join result on student.SNO=result.SNO, course join result on course.CNO=result.CNO

but that obviously won't work since result is used twice.)

I don't even know what you're trying to do with that first query. A table is not a condition, so you can't just AND them together (in general, if you have one row in each result table then it does work, but I don't think that's what you were trying to do). If you're using mysql, I might suggest looking at the official documentation for subqueries. Second, that query

student join result on student.SNO=result.SNO, course join result on course.CNO=result.CNO

will work if you write it as

student join result as r1 on student.SNO=r1.SNO, course join result as r2 on course.CNO=r2.CNO

although I don't know why you'd want to do that. Do you want something like this?

student join result on student.SNO=result.SNO join course on course.CNO=result.CNO

(obviously you'd need a SELECT whatever there, but you get the idea)

edit: Huh. I didn't even know you could do that.

Aumni

Think about breaking it down like infidel suggested. These components may lead to subqueries.

Infidel wrote: »

A union would combine two sets of results. How would you phrase it in that way? Probably not a straight union.

You might want to break it down. What courses did J Bond pass? Then, who passed those courses?

urahonky wrote: »

So something like:

select s.SNAME
from student s, result r, course c
where (select s.SNAME
from student s, result r, course c
where s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO)and(
select s.SNAME
from student s, result r, course c
where r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO);

You're going crazy with subqueries here though - step back and think of what you need to do again. That first subquery is getting closer to where you need to be at. But that second subquery isn't necessary, you can do that where clause at the current level you're at now.

Don't flip to the end of the chapter for the answer (my suggestion which may not even be right!), you'll cheapen the victory!

SELECT s.Name, r.SNO
FROM results r LEFT JOIN Student s ON (r.SNO = s.SNO)
Where r.mark > 60 and r.CNO IN (Select DISTINCT r.CNO FROM Results r WHERE r.SNO = "S4" AND r.Mark > 60)

Infidel

Aumni, we're supposed to let him attempt to get there first!

Aumni

Infidel wrote: »

Aumni, we're supposed to let him attempt to get there first!

Whoops!:oops:

Haha! How about that ninja edit?

urahonky

Aetheri wrote: »

urahonky wrote: »

So something like:

select s.SNAME
from student s, result r, course c
where (select s.SNAME
from student s, result r, course c
where s.SNAME='J Bond' and r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO)and(
select s.SNAME
from student s, result r, course c
where r.MARK>60 and s.SNO=r.SNO and c.CNO=r.CNO);

?

(also how do you use join if I have to join 3 tables together? I tried

student join result on student.SNO=result.SNO, course join result on course.CNO=result.CNO

but that obviously won't work since result is used twice.)

I don't even know what you're trying to do with that first query. A table is not a condition, so you can't just AND them together (in general, if you have one row in each result table then it does work, but I don't think that's what you were trying to do). If you're using mysql, I might suggest looking at the official documentation for subqueries. Second, that query

student join result on student.SNO=result.SNO, course join result on course.CNO=result.CNO

will work if you write it as

student join result as r1 on student.SNO=r1.SNO, course join result as r2 on course.CNO=r2.CNO

although I don't know why you'd want to do that. Do you want something like this?

student join result on student.SNO=result.SNO join course on course.CNO=result.CNO

(obviously you'd need a SELECT whatever there, but you get the idea)

edit: Huh. I didn't even know you could do that.

Oh wow, why didn't I think of that? You just join them together right in one line. Okay let me see if I can get this answer now...

urahonky

So in order to get the courses that J Bond passed I have to do:

select CNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where SNAME="J Bond" and MARK>60;

Now I have to use that result and find the students that passed the same classes right?

e:

mysql> select SNAME
    -> from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
    -> where CNAME in (
    -> select CNAME
    -> from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
    -> where SNAME='J Bond' and MARK>60);
+--------+
| SNAME  |
+--------+
| L Tree |
| D Pond |
| D Pond |
| M Lake |
| M Lake |
| J Bond |
| J Bond |
| L Leaf |
| L Leaf |
+--------+
9 rows in set (0.00 sec)

But J Bond is included in that set.

Aumni

urahonky wrote: »

So in order to get the courses that J Bond passed I have to do:

select CNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where SNAME="J Bond" and MARK>60;

Now I have to use that result and find the students that passed the same classes right?

While I'm not familiar with multiple joins (they don't work in access) like that, the logic there should work.

Make sure your fields are reference to the right table : Student.SNAME = "J BOND" and Result.MARK > 60.

Infidel

The first query grabs the courses that J Bond passed and you're getting that part right. The courses are C2 and C3. If you grab all records that have that course, you get your nine rows.

S1, C2, 80
S2, C2, 93
S2, C3, 88
S3, C2, 74
S3, C3, 89
S4, C2, 75
S4, C3, 85
S6, C3, 88

You're looking for courses that people have failed. So what would you have to do to eliminate some of these results?

urahonky

So question 2 and 3 are almost the same, except 2 is "students that failed" and 3 is "students that passed"

Would that still include J Bond? I mean it's obvious he passed if I use that in the query.

Infidel

urahonky wrote: »

So question 2 and 3 are almost the same, except 2 is "students that failed" and 3 is "students that passed"

Would that still include J Bond? I mean it's obvious he passed if I use that in the query.

In both cases, you could check to make sure you're selecting SNAME <> 'J Bond' in your main query right? It's a little redundant in the one case because any class that J Bond passed will not show him in the result for classes failed of course.

The only thing that changes is what your MARK cutoff is.

Aetheri

urahonky wrote: »

So question 2 and 3 are almost the same, except 2 is "students that failed" and 3 is "students that passed"

Would that still include J Bond? I mean it's obvious he passed if I use that in the query.

It's not quite the same. For query two, you need students that failed SOME courses that J Bond passed (that is, they failed AT LEAST ONE class that J Bond passed). For the third query, you want students that passed ALL courses that J Bond passed. If you just replace the mark comparison in query two with a different mark comparison for query three, it won't work.

And yeah, the third query probably should return J Bond unless the problem states otherwise. Did J Bond pass all the classes that J Bond passed?

urahonky

Okay trimming Query 3 down...

select distinct SNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where CNAME in (
select CNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where SNAME='J Bond' and MARK>60);

+--------+
| SNAME  |
+--------+
| L Tree |
| D Pond |
| M Lake |
| J Bond |
| L Leaf |
+--------+
5 rows in set (0.00 sec)

That looks good according to what I found out manually. Query 2, like Aetheri said, says failed SOME classes that J Bond passed. Which is going to make that one much more difficult.

Aetheri

urahonky wrote: »

Okay trimming Query 3 down...

select distinct SNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where CNAME in (
select CNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where SNAME='J Bond' and MARK>60);

+--------+
| SNAME  |
+--------+
| L Tree |
| D Pond |
| M Lake |
| J Bond |
| L Leaf |
+--------+
5 rows in set (0.00 sec)

That looks good according to what I found out manually. Query 2, like Aetheri said, says failed SOME classes that J Bond passed. Which is going to make that one much more difficult.

This is still not right, and it's somewhat disconcerting that you got it wrong even when you did the query by hand (L Tree shouldn't be there).

urahonky

Why shouldn't L Tree be there? J Bond passed C2 and C3... Oohhhh. I get it now. I was reading it as "whatever classes J Bond passed, all students that passed one of the classes"

So I have to find the classes that students passed C2 AND C3, not one or another.

Lewisham

You need to be very careful about your reading of questions when you do this stuff. You're doing logical set mathematics, you can't afford to misread ANDs with ORs.

urahonky

Well I got Query 2, which is the one that says:

Find the names of students who failed some courses that 'J Bond' Passed.

select distinct SNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where MARK<60 and CNAME in (
select CNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where SNAME='J Bond' and MARK>60);

Empty set (0.00 sec)

Now how would I do the Q3? If I have the classes that J Bond passed... And I have to find the students that pass all courses that he passed.
I tried using EXISTS but I still get L Tree because he has one class (C2) and he did pass it.

urahonky

Lewisham wrote: »

You need to be very careful about your reading of questions when you do this stuff. You're doing logical set mathematics, you can't afford to misread ANDs with ORs.

Yeah I know.... Which is the main reason I completely bombed most of the questions on the final in Discrete Math.

Aumni

urahonky wrote: »

Well I got Query 2, which is the one that says:

Find the names of students who failed some courses that 'J Bond' Passed.

select distinct SNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where MARK<60 and CNAME in (
select CNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where SNAME='J Bond' and MARK>60);

Empty set (0.00 sec)

Now how would I do the Q3? If I have the classes that J Bond passed... And I have to find the students that pass all courses that he passed.
I tried using EXISTS but I still get L Tree because he has one class (C2) and he did pass it.

Think of it the other way around, did anyone fail a class in the classes he passed?

urahonky

Aumni wrote: »

urahonky wrote: »

Well I got Query 2, which is the one that says:

Find the names of students who failed some courses that 'J Bond' Passed.

select distinct SNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where MARK<60 and CNAME in (
select CNAME
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where SNAME='J Bond' and MARK>60);

Empty set (0.00 sec)

Now how would I do the Q3? If I have the classes that J Bond passed... And I have to find the students that pass all courses that he passed.
I tried using EXISTS but I still get L Tree because he has one class (C2) and he did pass it.

Think of it the other way around, did anyone fail a class in the classes he passed?

Not one person did...

Infidel

Another thing that's unrelated to your main query woes.

If > 60 is a pass, and < 60 is a fail, what is exactly 60?

urahonky

So I was thinking Contains is what I need to do. What I need to do is get all the courses that all students passed and then take that and check if it contains C2 and C3, then output the SNAME.

e: Haha, yeah I guess I should start doing that >= for passing

Aumni

urahonky wrote: »

So I was thinking Contains is what I need to do. What I need to do is get all the courses that all students passed and then take that and check if it contains C2 and C3, then output the SNAME.

e: Haha, yeah I guess I should start doing that >= for passing

See now you're getting away from Access type stuff, so I'll have to back out. Unfortunately I can only assume what Contains will do, but it definitely sounds like it could be helpful here.

urahonky

So you guys have been really helpful. I've been able to finish Q1, Q2, Q5, Q7 and Q8 out of 8. Currently stuck on one and I needed some input.

Q6 says:

Extend Q5 by also including the number of students who failed the course in the listing. (Hint: Use Outer Join)

Q5 was: For each course, list the course number, the average mark of all students in the course, and the total number of students in the course.

So my answer for Q5 was:

select course.CNO, AVG(MARK) as AVG, COUNT(*) as Num_Students
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
group by course.CNO;

+------+---------+--------------+
| CNO  | AVG     | Num_Students |
+------+---------+--------------+
| C1   | 63.5000 |            2 |
| C2   | 79.8000 |            5 |
| C3   | 87.5000 |            4 |
| C4   | 65.2500 |            4 |
+------+---------+--------------+
4 rows in set (0.00 sec)

So... Do I add another COUNT in the select? How would I compute the number of students that failed the courses? I know how to do that by itself:

select course.CNO, COUNT(*) as NumFail
from course join result on course.CNO=result.CNO join student on 

result.SNO=student.SNO
where MARK<60
group by course.CNO;

How would I display this in the query? That's what I'm stuck on.

urahonky

Okay as soon as I typed that I had a brainstorm. It didn't solve it but I am further than I was before:

create view Q5 as(select course.CNO, AVG(MARK), COUNT(*)
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
group by course.CNO);

create view Q6 as(select course.CNO, COUNT(*) as NumFail
from course join result on course.CNO=result.CNO join student on result.SNO=student.SNO
where MARK<60
group by course.CNO);

select distinct *
from Q6 full join Q5;

+------+---------+------+-----------+----------+
| CNO  | NumFail | CNO  | AVG(MARK) | COUNT(*) |
+------+---------+------+-----------+----------+
| C1   |       1 | C1   |   63.5000 |        2 |
| C4   |       2 | C1   |   63.5000 |        2 |
| C1   |       1 | C2   |   79.8000 |        5 |
| C4   |       2 | C2   |   79.8000 |        5 |
| C1   |       1 | C3   |   87.5000 |        4 |
| C4   |       2 | C3   |   87.5000 |        4 |
| C1   |       1 | C4   |   65.2500 |        4 |
| C4   |       2 | C4   |   65.2500 |        4 |
+------+---------+------+-----------+----------+
8 rows in set (0.00 sec)