School Project select - 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

School Project select

Author

Topic

justjohno
Starting Member

23 Posts

Posted - 2009-06-11 : 12:38:34

I'm new to SQL and need some assistance on a school project. Below are the instructions and 4 database. I have my logic down, i think but my joins are messed up. If anyone can assist it would be great. Also attached the code I have already completed as well as the expected results.

Thanks

Instructions:
print
total number of owner sex (males / females)
total number of owner dog runs / doggie doors,
total combined height of owners
total combined pets age rounded to next higher integer

where
owner hair color = pet hair color
owner hair color is not their preferred pet hair color
or they have no pet hair color preference
owner perferred pet sex = their pet's sex and their pet a male cat
owner perferred home = the home they live in and it is an apartment

Tables:
Table name owners
o_id o_name o_sex o_height o_hair_color
----------- ------------------------------ ----- ---------- ------------
1 Mary Smith F 5.9 black
2 Jane Doe F 5.3 brown
3 Harry jones M 6.7 bald
4 Joe Green M 5 white
5 Gary French M 4.11 blonde
6 Chris Hoplen M 6.0 red
7 Lucy Skyler F 5.2 grey
8 Greg Young M 5.11 black

(8 row(s) affected)

Table Name Owner_preferences
op_id op_o_id op_pet_color op_home_type op_breed op_pet_sex
----------- ----------- -------------------- ------------ -------------------- ----------
1 1 grey apartment cat M
2 2 brown apartment cat F
3 4 red apartment cat M
4 5 grey apartment dog M
5 6 yellow apartment dog M

(5 row(s) affected)

Table Name Homes
h_id h_o_id h_home_type h_doggie_door h_dog_run
----------- ----------- ----------- ------------- ---------
1 1 apartment Y Y
2 2 home Y Y
3 3 condo N N
4 4 apartment Y N
5 5 campsite N N
6 6 condo Y Y
7 7 home Y Y
8 8 apartment N Y

(8 row(s) affected)

Table name Pets
p_id p_o_id p_pet_color p_breed p_born p_pet_sex
----------- ----------- -------------------- ---------- ----------------------- ---------
1 1 black cat 1999-12-01 00:00:00.000 M
2 2 brown cat 2008-11-21 00:00:00.000 F
3 2 white cat 2007-12-12 00:00:00.000 F
4 3 tan dog 2007-02-15 00:00:00.000 M
5 4 white cat 2005-04-07 00:00:00.000 M
6 4 black dog 2009-03-22 00:00:00.000 M
7 6 red dog 2004-07-26 00:00:00.000 F
8 8 black dog 2006-09-12 00:00:00.000 M
9 8 white cat 2007-10-16 00:00:00.000 F

(9 row(s) affected)

Below expected results:males females height dog doors dog runs age
----------- ----------- ----------- ----------- ----------- --------
1 1 10.9 2 1 15.0

Below is what I got so far:
select o.o_sex, count(*) as 'Male',
o.o_sex, count(*) as 'Female',
sum (cast(o.o_height as decimal(20,2))) as 'Height',
round (sum (datediff (yy, p.p_born, getdate())),-1) as 'Age',
h.h_dog_run, count (*) as 'Dog Runs',
h.h_doggie_door, count (*) as 'Dog Doors'

from owners as o, pets as p, homes as h
where o.o_id=p.p_o_id and o.o_id=h.h_o_id
group by o.o_sex, h.h_dog_run,h.h_doggie_door

Thanks anyone for the help

John O

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources