7 Replies - 411 Views - Last Post: 02 March 2017 - 12:14 PM

#1 RimmyTim  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 03-October 16

So....is it me or is it a weird problem I am having

Posted 01 March 2017 - 04:34 PM

Not sure how else to title this, because I am having a very strange error (in my mind). I've got 2 problems I am working on and the outcome doesn't make sense on one of them. The other I'm not sure if I am having a similar thing happen as the other query or just my dumb brain. (Not sure if that made any sense at all to you)

The databases;

Ships(name, class, launched)
+-----------------+----------------+----------+
| name | class | launched |
+-----------------+----------------+----------+
| California | Tennessee | 1921 |
| Haruna | Kongo | 1915 |
| Hiei | Kongo | 1914 |
| Iowa | Iowa | 1943 |
| Kirishima | Kongo | 1915 |
| Kongo | Kongo | 1913 |
| Missouri | Iowa | 1944 |
| Musashi | Yamato | 1942 |
| New Jersey | Iowa | 1943 |
| North Carolina | North Carolina | 1941 |
| Ramillies | Revenge | 1917 |
| Renown | Renown | 1916 |
| Repulse | Renown | 1916 |
| Resolution | Revenge | 1916 |
| Revenge | Revenge | 1916 |
| Royal Oak | Revenge | 1916 |
| Royal Sovereign | Revenge | 1916 |
| Tennessee | Tennessee | 1920 |
| Washington | North Carolina | 1941 |
| Wisconsin | Iowa | 1944 |
| Yamato | Yamato | 1941 |
+-----------------+----------------+----------+
21 rows in set (0.00 sec)

Classes(class, type, country, numGuns, bore, displacement): 'bb' for battleship and 'bc' for battlecruiser.
+----------------+------+-------------+---------+------+--------------+
| class | type | country | numGuns | bore | displacement |
+----------------+------+-------------+---------+------+--------------+
| Bismarck | bb | Germany | 8 | 15 | 42000 |
| Iowa | bb | USA | 9 | 16 | 46000 |
| Kongo | bc | Japan | 8 | 14 | 32000 |
| North Carolina | bb | USA | 9 | 16 | 37000 |
| Renown | bc | Gt. Britain | 6 | 15 | 32000 |
| Revenge | bb | Gt. Britain | 8 | 15 | 29000 |
| Tennessee | bb | USA | 12 | 14 | 32000 |
| Yamato | bb | Japan | 9 | 18 | 65000 |
+----------------+------+-------------+---------+------+--------------+
8 rows in set (0.00 sec)

Battles(name, date)
+----------------+------------+
| name | battledate |
+----------------+------------+
| North Atlantic | 1941-05-24 |
| Guadalcanal | 1942-11-15 |
| North Cape | 1943-02-26 |
| Suriago Strait | 1944-10-25 |
+----------------+------------+
4 rows in set (0.00 sec)

Outcomes(ship, battle, result)
+-----------------+----------------+---------+
| ship | battle | result |
+-----------------+----------------+---------+
| Bismarck | North Atlantic | sunk |
| California | Surigao Strait | ok |
| Duke of York | North Cape | ok |
| Fuso | Surigao Strait | sunk |
| Hood | North Atlantic | sunk |
| King George V | North Atlantic | ok |
| Kirishima | Guadalcanal | sunk |
| Prince of Wales | North Atlantic | damaged |
| Rodney | North Atlantic | ok |
| Scharnhorst | North Cape | sunk |
| South Dakota | Guadalcanal | damaged |
| Tennessee | Surigao Strait | ok |
| Washington | Guadalcanal | ok |
| West Virginia | Surigao Strait | ok |
| Yamashiro | Surigao Strait | sunk |
+-----------------+----------------+---------+
15 rows in set (0.00 sec)

Query 1

Which battleships launched before 1942 had 16 inch guns? List their names, their country, and the number of guns they carried.

My query code:
SELECT Ships.name, Classes.country, numGuns 
FROM Ships, Classes
WHERE launched = ‘1941’ AND bore = ‘16’ AND type = ‘bb’;



Expected output:
+----------------+---------+---------+
| name | country | numGuns |
+----------------+---------+---------+
| North Carolina | USA | 9 |
| Washington | USA | 9 |
+----------------+---------+---------+
2 rows in set (0.00 sec)

Except I keep getting

+----------------+---------+---------+
| name | country | numGuns |
+----------------+---------+---------+
| North Carolina | USA | 9 |
| Washington | USA | 9 |
| Yamato | USA | 9 |
+----------------+---------+---------+
3 rows in set (0.00 sec)

Yamato doesn't have a bore = 16
Its bore is 18.

HUH?!?!?!? HOW?!?!? Driving me insane.


Now the other problem I am having is

List all the pairs of countries that fought each other in battles. List each pair only once, and list them with the country that comes first in alphabetical order first.

Expected output:
+---------+---------+
| country | country |
+---------+---------+
| Japan | USA |
+---------+---------+
1 row in set (0.00 sec)

And my code is

SELECT DISTINCT c1.country, c2.country
FROM Ships s, Classes c1, Outcomes o1, Outcomes o2, Classes c2
WHERE s.name = o1.ship AND s.name = o2.ship AND s1.class = c1.class 
AND s2.class = c2.class AND o1.battle = o2.battle c1.country < c2.country;



And my outcome is

+-------------+-------------+
| country | country |
+-------------+-------------+
| Germany | USA |
| Japan | USA |
| Gt. Britain | USA |
| Germany | Japan |
| Gt. Britain | Japan |
| Germany | Gt. Britain |
+-------------+-------------+
6 rows in set (0.01 sec)



Some help would be awesome. Yes this is homework, but I have been working on this for a while and have got all but these 2 problems done. So just shooting down that assumption of "Oh this is homework where a student is asking for someone else to do his work for him." I just need some help. Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: So....is it me or is it a weird problem I am having

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13401
  • View blog
  • Posts: 53,495
  • Joined: 12-June 08

Re: So....is it me or is it a weird problem I am having

Posted 01 March 2017 - 06:06 PM

A couple of things.. first - you should have keys.. be it sequential numbers or guids. After that explicit joins and not the comma joins and with that an 'ON'. JOINs need to know what key columns you are joining against.
Was This Post Helpful? 0
  • +
  • -

#3 RimmyTim  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 03-October 16

Re: So....is it me or is it a weird problem I am having

Posted 01 March 2017 - 08:06 PM

View Postmodi123_1, on 01 March 2017 - 06:06 PM, said:

A couple of things.. first - you should have keys.. be it sequential numbers or guids. After that explicit joins and not the comma joins and with that an 'ON'. JOINs need to know what key columns you are joining against.


I understand, I didn't get to make these databases, or else i would have given each a unique key. I have to make use of what I got sadly.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: So....is it me or is it a weird problem I am having

Posted 01 March 2017 - 10:49 PM

You can still do a join. But, you need to define the what and where still:

SELECT Ships.name, Classes.country, numGuns 
FROM Ships, Classes
WHERE launched = ‘1941’ AND bore = ‘16’ AND type = ‘bb’;



This isn't an inner join and professionally, I hate seeing queries like this.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13401
  • View blog
  • Posts: 53,495
  • Joined: 12-June 08

Re: So....is it me or is it a weird problem I am having

Posted 01 March 2017 - 11:41 PM

still missing the ON.
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: So....is it me or is it a weird problem I am having

Posted 02 March 2017 - 12:11 AM

!!! It's still his query !!!
Was This Post Helpful? 0
  • +
  • -

#7 RimmyTim  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 03-October 16

Re: So....is it me or is it a weird problem I am having

Posted 02 March 2017 - 11:14 AM

alright so i feel dumb. literally took one glance and face-palmed immediately.

so the first one is solved. yay

SELECT Ships.name, Classes.country, numGuns 
FROM Ships, Classes
WHERE launched = ‘1941’ AND bore = ‘16’ 
AND Ships.class = Classes.class;




So moving on to the other one and unfortunately figured out I have another query wrong and I am close to solving it as well, but let's focus in on the unique pairs.

Now the other problem I am having is

List all the pairs of countries that fought each other in battles. List each pair only once, and list them with the country that comes first in alphabetical order first.

Expected output:
+---------+---------+
| country | country |
+---------+---------+
| Japan | USA |
+---------+---------+
1 row in set (0.00 sec)

And my code is

SELECT DISTINCT c1.country, c2.country
FROM Ships s, Classes c1, Outcomes o1, Outcomes o2, Classes c2
WHERE s.name = o1.ship AND s.name = o2.ship AND s.class = c1.class 
AND s.class = c2.class AND o1.battle = o2.battle c1.country < c2.country;


What I am getting is and empty set now

So I am getting each unique pair, but not unique pairs that fought each other in battles. So would I need a NOT EXISTS or NOT IN the Outcomes table?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13401
  • View blog
  • Posts: 53,495
  • Joined: 12-June 08

Re: So....is it me or is it a weird problem I am having

Posted 02 March 2017 - 12:14 PM

For the love of Peter.. NO...no.. NO!

JOINs have a fairly consistent format. I would strongly suggest following it.
https://www.techonth...erver/joins.php

SELECT a.Col1, b.Col1
FROM myTableA a
JOIN mytable b ON a.key1 = b.key1
WHERE <conditions>

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1