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:
CODE
# 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:
CODE
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:
CODE
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: 2 Aug, 2008 - 07:57 PM
1 Aug, 2008 - 09:17 PM
|