socx Posted November 25, 2005 Share Posted November 25, 2005 HI ALLI need to do the following using the tables STUDENT AND FINANCE BELOW.1. Find the student number, student name and total amount paid for all students that do not pay in cash.2. Find the student number, name and amount paid for the students who have made the highest and lowest individual payments3. Display the course name and total amount paid for the course that has made the most money.table : studentSTNO NAME DOB TOWN COURSE1 Joe 01-JAN-70 London Maths2 Sam 11-AUG-71 York Art3 Dave 12-DEC-69 Leeds Art4 Bob 07-JUL-68 Derby History5 Jim 02-JUN-71 London 6 Dave 02-JAN-68 Leeds Maths7 Bob 07-APR-68 Derby 8 Kim 02-FEB-71 London Mathstable: financePAY_NO AMT METHOD STNO1 100 CASH 12 150 DDEBIT 23 200 CASH 34 75 CASH 85 95 CHEQUE 46 55 CHEQUE 37 100 CHEQUE 18 150 DDEBIT 69 100 CHEQUE 3THANX A LOT,CHEERS,SOCX D'BUCK Link to comment Share on other sites More sharing options...
aspnetguy Posted November 25, 2005 Share Posted November 25, 2005 lol, don't want to do your homework?Okay I'll bite.Here is Query 1SELECT s.STNO Student#, NAME Name, AMT AmountFROM student sINNER JOIN finance fON s.STNO = f.STNOWHERE METHOD <> 'CASH' Link to comment Share on other sites More sharing options...
aspnetguy Posted November 25, 2005 Share Posted November 25, 2005 Query 3SELECT TOP 1 COURSE Course, SUM(AMT) AmountFROM student sINNER JOIN finance fON s.STNO = f.STNOGROUP BY CourseORDER BY SUM(AMT) DESC Link to comment Share on other sites More sharing options...
aspnetguy Posted November 25, 2005 Share Posted November 25, 2005 and finallyquery 2SELECT s.STNO Student#, NAME Name, SUM(AMT) AmountFROM student s, finance fWHERE s.STNO = f.STNOGROUP BY s.STNO, NAME HAVING SUM(AMT) = (SELECT TOP 1 SUM(AMT) FROM finance GROUP BY STNO ORDER BY SUM(AMT) DESC)OR SUM(AMT) = (SELECT TOP 1 SUM(AMT) FROM finance GROUP BY STNO ORDER BY SUM(AMT) ASC) Link to comment Share on other sites More sharing options...
aspnetguy Posted November 25, 2005 Share Posted November 25, 2005 BTW these queries where written for SQL Server 2000 (what I have on hand at the moment), if this is for another Database you may have to tweak them a bit.Is this for a class or are you learning on your own?either way you probably have learned nothing from this, I really am not helping you but I know the feeling. I passed in many incomplete SQL Assignments in college. But I kept trying and here I am 3 years later and I am very confident in SQL.Good luck. Link to comment Share on other sites More sharing options...
Kcarson Posted November 26, 2005 Share Posted November 26, 2005 In the future socx, please post whatever attempts you have made at the queries thus far, rather than just asking for the solution. That way, we can help you understand where you are going wrong, and hopefully you will learn more at the same time. You got lucky that aspnetguy gave you all the answers, I would not have been so nice just kidding...well sort of. But all joking aside, the best way to learn is not to have people hand you answers but to allow us to help you troubleshoot your attempts (no matter how feeble they may be).In the meantime, you have your answers, please make sure you understand how the queries were made. Let us know if you have any other problems Link to comment Share on other sites More sharing options...
socx Posted November 27, 2005 Author Share Posted November 27, 2005 In the future socx, please post whatever attempts you have made at the queries thus far, rather than just asking for the solution. That way, we can help you understand where you are going wrong, and hopefully you will learn more at the same time. You got lucky that aspnetguy gave you all the answers, I would not have been so nice just kidding...well sort of. But all joking aside, the best way to learn is not to have people hand you answers but to allow us to help you troubleshoot your attempts (no matter how feeble they may be).In the meantime, you have your answers, please make sure you understand how the queries were made. Let us know if you have any other problems <{POST_SNAPBACK}> Hello,Well was giving it a trial myself and i was able to crack 1 & 2 and to the best of my knowledge a good attempt at 3.Here are my own versions.1.SELECT STUDENT.STNO, NAME, METHOD, SUM(AMT)FROM EMP, FINANCEWHERE STUDENT.STNO = FINANCE.STNO AND METHOD<>'CASH'GROUP BY METHOD, STUDENT.STNO, NAME;2.SELECT STUDENT.STNO, NAME, AMTFROM STUDENT JOIN FINANCE ON STUDENT.STNO = FINANCE.STNOWHERE AMT = (SELECT MAX(AMT) FROM FINANCE) OR AMT = (SELECT MIN(AMT) FROM FINANCE);3. For this one, I could get a single query to pull it out, but what i did was to first create a viewCREATE VIEW MYVIEW SELECT COURSE, SUM(AMT) as DSUM FROM STUDENT LEFT JOIN FINANCE ON STUDENT.STNO = FINANCE.STNO GROUP BY COURSE;And then got the requiired data from the viewSELECT MAX(DSUM) FROM MYVIEW;So I not as lazy as i seen to you.Also I tried out aspnetguy's queries on ORACLE but don't think the keyword TOP is in the ORACLE dialect. Link to comment Share on other sites More sharing options...
Kcarson Posted November 27, 2005 Share Posted November 27, 2005 So I not as lazy as i seen to you.Also I tried out aspnetguy's queries on ORACLE but don't think the keyword TOP is in the ORACLE dialect.<{POST_SNAPBACK}> I am glad to hear that, and it appears that you are trying to learn how to do it yourself, so congrats. You have no idea how many students will come onto forums and try to get answers to their homeworks without doing a single piece of work themselves (haven't seen it on this forum really yet, but on some C/C++ Programming forums it is a frequent problem).Anyways, to help with getting only the top number of rows using a particular query, I believe you can do that using the LIMIT command, you can see varying ways to accomplish what you are trying to do in this thread:http://w3schools.invisionzone.com/index.php?showtopic=652Best of luck and please do not take my previous comments to mean you should not post questions here, I know I am always willing to help those who have well thought out questions and who are genuinely wanting to learn rather than just get the answers, and you appear to be a learner .Well, let me know if the LIMIT command works for you or if you have any other questions. Link to comment Share on other sites More sharing options...
aspnetguy Posted November 28, 2005 Share Posted November 28, 2005 Here are some differences between databaes and the TOP statement.SQL Server:SELECT TOP 10 product, descr, email FROM products ORACLE:SELECT product, descr, emailFROM products WHERE ROWNUM <= 10MySQL:SELECT product, descr, emailFROM productsLIMIT 10New Queries for oracleSELECT s.STNO Student#, NAME Name, SUM(AMT) AmountFROM student s, finance fWHERE s.STNO = f.STNOGROUP BY s.STNO, NAME HAVING SUM(AMT) = (SELECT SUM(AMT) FROM finance WHERE ROWNUM <=1 GROUP BY STNO ORDER BY SUM(AMT) DESC)OR SUM(AMT) =(SELECT SUM(AMT) FROM finance WHERE ROWNUM <=1 GROUP BY STNO ORDER BY SUM(AMT) ASC) SELECT COURSE Course, SUM(AMT) AmountFROM student sINNER JOIN finance fON s.STNO = f.STNOWHERE ROWNUM <=1GROUP BY CourseORDER BY SUM(AMT) DESC Hope this solves the issue. Link to comment Share on other sites More sharing options...
socx Posted November 28, 2005 Author Share Posted November 28, 2005 THANKS A BUNCH! Link to comment Share on other sites More sharing options...
aspnetguy Posted November 29, 2005 Share Posted November 29, 2005 No problem Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now