Programming Help

Web Development



What's Here?

  • Members: 131,704
  • Replies: 469,522
  • Topics: 72,773
  • Snippets: 2,534
  • Tutorials: 664
  • Total Online: 2,486
  • Members: 91
  • Guests: 2,395

Who's Online?

Welcome to Dream.In.Code
Getting Help is Easy!

Join 131,704 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,486 people online right now. Registration is fast and FREE... Join Now!




SQL Sytax Query

 
 Reply to this topicStart new topic

SQL Sytax Query
ohiggins
post 4 Oct, 2008 - 04:02 AM
Post #1


New D.I.C Head

*
Joined: 10 Apr, 2008
Posts: 10


My Contributions
Hi I am currently doing an assignment using SQL and have gotten quite stuck. If anybody can help me with my query I would appreciate it...

The schema of the research DB is as follows:

Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, *DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(*PaNum, *AcNum)
Field(FieldNum, ID, Title)
Interest(*FieldNum, *AcNum, Descrip)

Question 7: (2 marks)
Which academics have published a paper with at least one academic from Victoria? Return their academic number, given name, family name, and department number.



CODE
SELECT *
FROM author
WHERE author.panum IN(
SELECT paper.panum
FROM academic
LEFT OUTER JOIN department ON ( academic.DeptNum = department.DeptNum )
LEFT OUTER JOIN author ON ( academic.acnum = author.acnum )
LEFT OUTER JOIN paper ON ( author.Panum = paper.panum )
WHERE department.state = "Vic")


It does not throw an error but it does 'crash' the DB. Help!
User is offlineProfile CardPM

Go to the top of the page

ohiggins
post 4 Oct, 2008 - 04:36 AM
Post #2


New D.I.C Head

*
Joined: 10 Apr, 2008
Posts: 10


My Contributions
CODE
SELECT author.panum
FROM author
WHERE (author.panum) IN(
SELECT paper.panum
FROM academic
LEFT OUTER JOIN department ON ( academic.DeptNum = department.DeptNum )
LEFT OUTER JOIN author ON ( academic.acnum = author.acnum )
LEFT OUTER JOIN paper ON ( author.Panum = paper.panum )
WHERE department.state = "Vic")


I feel that this is correct, but mysql, just keeps thinking about it.... mad.gif

i get this....

Error

SQL query: DocumentationEdit

SELECT FOUND_ROWS( ) AS count;

MySQL said: Documentation
#2006 - MySQL server has gone away

This post has been edited by ohiggins: 4 Oct, 2008 - 04:58 AM
User is offlineProfile CardPM

Go to the top of the page

ohiggins
post 4 Oct, 2008 - 05:35 AM
Post #3


New D.I.C Head

*
Joined: 10 Apr, 2008
Posts: 10


My Contributions
Umm I figured it out, I thought i would post the solution if it helps anyone smile.gif
CODE
SELECT acnum,GiveName,FamName,DeptNum
FROM academic WHERE academic.acnum IN(
SELECT author.acnum
FROM author
UNION
(SELECT paper.panum
FROM academic
LEFT OUTER JOIN department ON ( academic.DeptNum = department.DeptNum )
LEFT OUTER JOIN author ON ( academic.acnum = author.acnum )
LEFT OUTER JOIN paper ON ( author.Panum = paper.panum )
WHERE department.state = "Vic"))
User is offlineProfile CardPM

Go to the top of the page

baavgai
post 4 Oct, 2008 - 06:48 AM
Post #4


Dreaming Coder

Group Icon
Joined: 16 Oct, 2007
Posts: 1,948



Thanked 94 times

Dream Kudos: 475

Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua

My Contributions
I'm not 100% sure your answer will give you what you're looking for. I'm particularly bothered by the union and the outer joins shouldn't be required. While a subquery is the solution, you don't need two. Also, where..in is to be avoided when possible.

Here are all the papers with an author from 'Vic' ( close to yours, with no nasty outers):
CODE

select distinct p.PaNum
    from Paper p
        inner join Author au on au.PaNum=p.PaNum
        inner join Academic ac on au.AcNum=ac.AcNum
        inner join Department d    on d.DeptNum=ac.DeptNum and d.State='Vic'


Here are the Acedemics with their papers:
CODE

select *
    from Academic ac
        inner join Author au on au.AcNum=ac.AcNum


Putting it all together:
CODE

select ac.AcNum, ac.GiveName, ac.FamName, ac.DeptNum
    from Academic ac
        inner join Author au on au.AcNum=ac.AcNum
        inner join (
            select distinct p.PaNum
                from Paper p
                    inner join Author au on au.PaNum=p.PaNum
                    inner join Academic ac on au.AcNum=ac.AcNum
                    inner join Department d    on d.DeptNum=ac.DeptNum and d.State='Vic'
        ) p on au.PaNum=p.PaNum


Hope this helps.
User is offlineProfile CardPM

Go to the top of the page

agent_logic
post 16 Oct, 2008 - 02:14 AM
Post #5


New D.I.C Head

*
Joined: 21 Jul, 2008
Posts: 11


My Contributions
QUOTE(ohiggins @ 4 Oct, 2008 - 05:32 PM) *

Hi I am currently doing an assignment using SQL and have gotten quite stuck. If anybody can help me with my query I would appreciate it...

The schema of the research DB is as follows:

Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, *DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(*PaNum, *AcNum)
Field(FieldNum, ID, Title)
Interest(*FieldNum, *AcNum, Descrip)



Hi, I'm doing the same assignment! With respect to the schema, I have to "list the paper numbers with the largest number of co-authors." I have constructed a query that "almost" does the job, as in it displays the paper numbers with the most number of authors first, and other papers as well in a descending order. After spending about three days straight on this assignment, I am unable to think straight. I'm not even sure if what I'm typing right now is proper English or not! So, onto the query I constructed:

CODE

SELECT DISTINCT PaNum, Title, COUNT(AcNum)
FROM Author NATURAL JOIN Paper
GROUP BY PaNum
ORDER BY COUNT(AcNum) DESC;


This query lists papers with maximum number of authors first, in descending order. I know I'm close to getting what I need, but I'm unable to think any further. Someone help me before my head blows off! :-S
User is offlineProfile CardPM

Go to the top of the page

Fast ReplyReply to this topicStart new topic
Time is now: 11/20/08 09:11AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month