Homework: Variety of Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Homework: Variety of Queries

Author

Topic

Little Blessed Villain
Starting Member

1 Post

Posted - 2010-10-02 : 05:13:23

Hi there SQL Team, sorry my first post was asking for homework help.

Anyway, as an assignment, I've been given a database with three tables.

Characters(Name: string, Race: string, Homeworld: string, Affiliation: string)
Planets(Name: string, Type: string, Affiliation: string)
TimeTable(Character's Name: string, Planet's Name: string, Movie: integer, Time of Arrival: integer, Time of Departure: integer)

I'm supposed to write 5 queries and accompanying relational algebraic formulas (for all but #3).

1. Find all characters that have been on all neutral planets
2. Find distinct names of the planets visited by empire affiliated humans.
3. For each character and for each neutral planet, how much time total did the character spend on the planet?
4. On which planets and in which movies has Luke been at the same time on the same planet as Darth Vader.
5. Find humans that visited desert planets and droids that visited swampy planets. List the movies where it happened and the names of the characters.

I've successfully written query #2

SELECT DISTINCT P.Name
FROM Planets AS P, TimeTable AS T, Characters AS C
WHERE P.Name = T.[Planet's Name] and C.Affiliation = 'empire' and C.Name = T.[Character's Name];

but the rest are being...stubborn.

For instance, in #1, I don't know how to check to see if a character has visited all of a certain kind of planet. Just if they've visited one of a certain kind of planet. My code so far for #1 looks like this:

SELECT DISTINCT C.Name
FROM Characters AS C, Planets AS P, TimeTable AS T
WHERE P.Name = T.[Planet's Name] and P.Affiliation = 'neutral' and C.Name = T.[Character's Name]

and all that does is return a list of everybody who has been on any neutral planet.

I'm working on queries 3-5, because they seem more manageable than #1, but I think if I just got a little help with these more complicated ones things would start falling into place.

So, if anybody's willing to help right now, that'd be amazing.

Sachin.Nand

2937 Posts

Posted - 2010-10-02 : 13:54:19

Maybe the sample below will help you understand on how to achieve the o/p for query #1.
The table below holds data for the characters implied with column name 'ch' that hv visited the planets which is implied with column name 'pl'.
Now as you can see in the sample data character 1 has visited all the planets that exists in the table while character 2 has just visited planet 1.So the o/p will be character 1.
You can add more data to test the query.


declare @tbl as table( pl int,ch int)
insert into @tbl
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 3,1

select ch,count(distinct pl)pl from @tbl
group by ch
having count(distinct pl)=(select count(distinct pl)from @tbl)

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-03 : 04:12:36

Post what you've done for 3, 4 and 5 and where you're struggling. We don't just do people's homework, but we are willing to offer suggestions.

For 1 what you have to do is count the neutral planets, then count the neutral planets that each char has visited. If they are equal, the char visited all the neutral planets. Will need a subquery somewhere.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources