[OliveOyl3471]

This is not my homework problem but it is my husband's. I am not even sure what to ask, since I haven't taken this class yet and have very limited experience with SQL. All I can do is post the problem and the code he has so far. Can you tell me what needs to be fixed in order to make this work right?

Here's the problem:

List the names of any pair of boats that have the same type. For example, one pair would be Anderson II and Escape, because the boat type for both boats is Sprite 4000. The first name listed should be the major sort key and the second name should be the minor sort key.

Here's the table:

# create a table marina slip


create table marina_slip

	 (Slip_Id  int(2)  primary key,

	 marina_Num  char(4),

	 Slip_Num  char(4),

	 length  dec(4,0),

	 rental_Fee  dec(8,2),

	 boat_Name char(50),

	 boat_Type  char (50),

	 Owner_Num   char(4));

 

INSERT INTO MARINA_SLIP

	 VALUES

	  ('1','1','A1','40','3800.00','Anderson II','Sprite 4000','AN75');

 

INSERT INTO MARINA_SLIP

	VALUES

	 ('2','1','A2','40','3800.00','Our Toy','Ray 4025','EL25');

 

INSERT INTO MARINA_SLIP

   VALUES

   ('3','1','A3','40','3600.00','Escape','Sprite 4000','KE22');

 

INSERT INTO MARINA_SLIP

   VALUES

   ('4','1','B1','30','2400.00','Gypsy','Dolphin 28','JU92');

 

INSERT INTO MARINA_SLIP

   VALUES

   ('5','1','B2','30','2600.00','Anderson III','Sprite 3000','AN75');

 

INSERT INTO MARINA_SLIP

   VALUES

   ('6','2','1','25','1800.00','Bravo','Dolphin 25','AD57');

 

INSERT INTO MARINA_SLIP

VALUES

   ('7','2','2','25','1800.00','Chinook','Dolphin 22','FE82');

 

INSERT INTO MARINA_SLIP

  VALUES

   ('8','2','3','25','2000.00','Listy','Dolphin 25','SM72');

 

INSERT INTO MARINA_SLIP

  VALUES

   ('9','2','4','30','2500.00','Mermaid','Dolphin 28','BL72');

 

INSERT INTO MARINA_SLIP

  VALUES

  ('10','2','5','40','4200.00','Axxon II','Dolphin 40','NO27');

 

INSERT INTO MARINA_SLIP

  VALUES

  ('11','2','6','40','4200.00','Karvel','Ray 4025','TR72');

Here's the query:

SELECT F.boat_Name,S.boat_Name,boat_Type

FROM marina_slip F, marina_slip S

WHERE boat_Type = boat_Type

AND F.boat_Name NOT S.boat_Name

ORDER BY boat_Type;

edit--figured out what a major sort key and a minor sort key is:

When sorting query results using more then one field, the leftmost sort key is the design grid is the major sort key (also called the primary sort key) and the sort key to its right is the minor sort key (also called the secondary sort key).

edit again--he found the answer, and here it is in case anyone needs to know:

SELECT boat_Name

FROM marina_slip

WHERE boat_Type IN ('Sprite 4000','Sprite 3000','Ray 4025','Dolphin 25','Dolphin 

28')

ORDER BY boat_Type;

This post has been edited by OliveOyl3471: 02 August 2008 - 07:57 PM

[Martyr2]

The answer he has there is not going to be right. First all he is doing is selecting all boats which have a boat type in that list, then he is sorting them. If he is to have a major AND a minor sort key, he is going to have TWO variables in his ORDER BY clause.

The first field name is the major key and it goes to more minor keys.

For example if he wanted to sort first by boat type and then for each type order by their name he may have something like...

select boat_Name, boat_Type from marina_slip order by boat_Type, boat_Name

This sorts all records based on the boat_type in ascending order, but then for each group with the same boat type, it will sort the boat names in ascending order. So you will get a result that looks like this...

Gypsy, Dolphin 28
Mermaid, Dolphin 28
Anderson II, Sprite 4000
Escape, Sprite 4000

Notice that Dolphin is before Sprite (sorted) but then for Dolphin Gypsy appears before Mermaid. Because if they just had sorted by boat type, you could end up with results like this...

Mermaid, Dolphin 28
Gypsy, Dolphin 28
Anderson II, Sprite 4000
Escape, Sprite 4000

Which shows Dolphin before Sprite, but that Mermaid appears before Gypsy for the Dolphin boat type. This is sorted only on ONE key.

Just so you know the difference of sort keys. Might want to double check the answer but I am sure you are suppose to have two fields in your order by clause. One is a major key and one is minor.

[OliveOyl3471]

Oh...so you want to sort within each group.

If your major sort key is boat type and your minor sort key is boat name, then you want it sorted first by type, and then within each boat type you want it sorted (alphabetically) by the names.

"minor keys" ? I guess that means you can have more than one minor key, huh? Kind of like a nested if...you can have lots of them?

Thanks for this answer, Martyr.


But, just out of curiosity (and because I'm taking this same class next semester) how did you get that from this:

"List the names of any pair of boats that have the same type. For example, one pair would be Anderson II and Escape, because the boat type for both boats is Sprite 4000. The first name listed should be the major sort key and the second name should be the minor sort key."

The names of any pair...does that mean every pair? What happens if there are three that have the same boat type?

The first name...major sort key...second name...minor sort key. So how do you know what to sort by? In this case, wouldn't the name be both the major and the minor sort key?

[OliveOyl3471]

This project is finished and handed in, so I thank you Martyr for your help.
I'll be back here asking for help with my own homework in that class if necessary. Hopefully it won't be necessary.

Thanks again.

[tet]

This is another interpretation of the query:

SELECT F.boat_Name, S.boat_Name

FROM marina_slip F, marina_slip S

WHERE F.boat_Type = S.boat_Type

AND (F.boat_Name != S.boat_Name)

ORDER BY F.boat_name, S.boat_name;

Cheers!

[OliveOyl3471]

Thank you , tet. I think that is pretty much what the instructor was looking for. I'm taking the class now, and this is the answer
for a similar question using a different database. Use an alias for your table, since all of the information comes from the same table.
F for first, S for second. Then compare the two copies of our table to each other, and find the pairs with duplicate prices but different book codes.

SELECT F.book_code, S.book_code
FROM book F, book S
WHERE F.price = S.price
AND F.book_code < S.book_code  //just to make sure we don't duplicate the book codes
ORDER BY F.book_code, S.book_code;

It works perfectly.

Thanks again for taking the time to answer my question.