Oracle - SQL task.

----------------------------------------------------------------
(C) gRAVER_ZX, 2007
----------------------------------------------------------------

Oracle
SQL task

Task text.

For each country, determine the year when
the maximum was launched
the number of its ships.  In case
there will be several such years, take
the least of them.  Output: country, year,
number of ships.

The ship database is being considered,
participated in the Second World War.
There are the following relationships:

Classes (class, type, country, numGans, bore, displacement)
Ships (name, class, launched)

In fact, we will need it from the Classes table
only these fields: class, country.

In general, check your decisions on that
site where the condition of this was taken from
tasks, a very dubious pleasure.
It's better to create these two signs in
existing database and experiment with
with your requests to them as much as you like.
Moreover, the site gives an error:
this request:

SELECT country, launched, COUNT(name) c
FROM Classes, Ships
WHEREClasses.class=Ships.class
GROUP BY country, launched
ORDER BY country;

Oracle copes with it naturally.
So it’s better to experiment in that
environment from which you least expect
glitches.


Creating and filling tables.

CREATE TABLE classes(
  class VARCHAR2(16) NOT NULL,
  type VARCHAR2(2) NOT NULL,
  country VARCHAR2(16) NOT NULL,
  numguns NUMBER(2,0) NULL,
  bore NUMBER(2,0) NULL,
  displacement NUMBER(5,0) NULL
);

insert into classes
(class,type,country,numguns,bore,displacement) values
('Bismarck','bb','Germany',8,15.0,42000);

insert into classes
(class,type,country,numguns,bore,displacement) values
('Iowa','bb','USA',9,16.0,46000);

insert into classes
(class,type,country,numguns,bore,displacement) values
('Kongo','bc','Japan',8,14.0,32000);

insert into classes
(class,type,country,numguns,bore,displacement) values ('North
Carolina','bb','USA',12,16.0,37000);

insert into classes
(class,type,country,numguns,bore,displacement) values
('Renown','bc','Gt.Britain',6,15.0,32000);

insert into classes
(class,type,country,numguns,bore,displacement) values
('Revenge','bb','Gt.Britain',8,15.0,29000);

insert into classes
(class,type,country,numguns,bore,displacement) values
('Tennessee','bb','USA',12,14.0,32000);

insert into classes(class,type,country,numguns,bore,displacement) values
('Yamato','bb','Japan',9,18.0,65000);

CREATE TABLE ships(
  name VARCHAR2(16) NOT NULL,
  class VARCHAR2(16) NOT NULL,
  launched NUMBER(5,0) NULL
);

insert into ships (name,class,launched) values
('California','Tennessee',1921);

insert into ships (name,class,launched) values
('Haruna','Kongo',1916);

insert into ships (name,class,launched) values ('Hiei','Kongo',
1914);

insert into ships (name,class,launched) values
('Iowa','Iowa',1943);

insert into ships (name,class,launched) values
('Kirishima','Kongo',1915);

insert into ships (name,class,launched) values
('Kongo','Kongo',1913);

insert into ships (name,class,launched) values
('Missouri','Iowa',1944);

insert into ships (name,class,launched) values
('Musashi','Yamato',1942);

insert into ships (name,class,launched) values ('New
Jersey','Iowa',1943);

insert into ships (name,class,launched) values ('North
Carolina','North Carolina',1941);

insert into ships (name,class,launched) values
('Ramillies','Revenge',1917);

insert into ships (name,class,launched) values
('Renown','Renown',1916);

insert into ships (name,class,launched) values
('Repulse','Renown',1916);

insert into ships (name,class,launched) values
('Resolution','Renown',1916);

insert into ships (name,class,launched) values
('Revenge','Revenge',1916);

insert into ships (name,class,launched) values ('Royal
Oak','Revenge',1916);

insert into ships (name,class,launched) values ('Royal
Sovereign','Revenge',1916);

insert into (ships,class,launched) values ('South
Dakota','North Carolina',1941);

insert into (name,class,launched) values
('Tennessee','Tennessee',1920);

insert into (name,class,launched) values
('Washington','North Carolina',1941);

insert into (name,class,launched) values
('Wisconsin','Iowa',1944);

insert into (name,class,launched) values
('Yamato','Yamato',1941);


Path to the solution.

Well, the first sketch.

SELECT country, launched, COUNT(*)
FROM classes, ships
GROUP BY country, launched
ORDER BY country;


hmm, a lot of lines are displayed.


***

so a country can produce several
classes of ships, as can be seen from
request:

SELECT country, class FROM classes ORDER BY country;


COUNTRY CLASS
---------------- ----------------
Germany Bismarck
Gt.Britain Revenge
Gt.Britain Renown
Japan Kongo
Japan Yamato
USA Tennessee
USA Iowa
USA North Carolina

8 rows selected.


***

it turns out that COUNTRY---SELECT country, launched, name, classes.class
FROM classes, ships
WHERE classes.class=ships.class
GROUP BY country, launched
ORDER BY country, launched;

number of ships launched for each
year, for each country.


***

SELECT country, launched, COUNT(*)
FROM classes, ships
WHERE classes.class=ships.class
GROUP BY country, launched
ORDER BY country, launched
HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM classes, ships
WHERE classes.class=ships.class
GROUP by country, launched);


HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM classes, ships
*
error on line 6:
ORA-00933: invalid SQL statement termination

well, it’s clear that HAVING comes before ORDER,
which is confirmed further.


***

SELECT country, launched, COUNT(*)
FROM classes, ships
WHERE classes.class=ships.class
GROUP BY country, launched
HAVING COUNT(*)>=2


COUNTRY LAUNCHED COUNT(NAME)
---------------- ---------- -----------
USA 1943 2
USA 1941 3
USA 1944 2
Gt.Britain 1916 6


***

SELECT country, launched, COUNT(*)
FROM classes, ships
WHERE classes.class=ships.class
GROUP BY country, launched
HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROMclasses, ships
WHERE classes.class=ships.class
GROUP by country, launched);


COUNTRY          LAUNCHED   COUNT(*)
---------------- ---------- ----------
Gt.Britain       1916       6

дело  в том, что подзапрос в HAVING выдает
значения  по  всем странам, а нужно только
по конкретной.


***

SELECT *
FROM (SELECT country, launched, COUNT(*) c
FROM classes, ships
WHERE classes.class=ships.class
GROUP BY country, launched)
WHERE c>=ALL(SELECT COUNT(*) FROM classes, ships
WHERE classes.class=ships.class
GROUP by country, launched);


строки не выбраны

что-то такая мысль ни к чему не привела.


***!!!***

SELECT country, launched, COUNT(*)
FROM classes c1, ships
WHERE c1.class=ships.class
GROUP BY country, launched
HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM classes c2, ships
WHERE c2.class=ships.class AND c1.country=c2.country
GROUP by country, launched)
ORDER BY country;

итак  я  нашел  года  для  каждой  стран с
максимальным  количеством спусков кораблей
на воду:


COUNTRY          LAUNCHED   COUNT(*)
---------------- ---------- ----------
Gt.Britain       1916       6
Japan            1914       1
Japan            1915       1
Japan            1941       1
Japan            1942       1
Japan            1913       1
Japan            19161
USA              1941       3

8 строк выбрано.


***

SELECT *
FROM (SELECT country, launched, COUNT(*) c
FROM classes, ships
WHERE classes.class=ships.class
GROUP BY country, launched) c1
WHERE c>=ALL(SELECT COUNT(*) FROM classes c2, ships
WHERE c2.class=ships.class AND c1.country=c2.country
GROUP by country, launched);


строки не выбраны

опять  не  получилось.  я уж не помню чего
хотел-то здесь.


***

SELECT country, launched, COUNT(*)
FROM classes c1, ships
WHERE c1.class=ships.class
GROUP BY country, launched
HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM classes c2, ships
  WHERE c2.class=ships.class AND c1.country=c2.country
  GROUP by country, launched)
AND launched<=ALL(SELECT launched FROM classes c2, ships
  WHERE c2.class=ships.class AND c1.country=c2.country
  GROUP by country, launched)
ORDER BY country;


COUNTRY          LAUNCHED   COUNT(*)
---------------- ---------- ----------
Gt.Britain       1916       6
Japan            1913       1


***

отталкиваясь от ***!!!*** получаю

SELECT * FROM
(
SELECT country, launched, COUNT(*)
FROM classes c1, ships
WHERE c1.class=ships.class
GROUP BY country, launched
HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM classes c2, ships
  WHERE c2.class=ships.class ANDc1.country=c2.country
  GROUP by country, launched)
ORDER BY country
) l1
WHERE launched<=ALL(SELECT launched FROM(
  SELECT country, launched, COUNT(*)
  FROM classes c1, ships
  WHERE c1.class=ships.class
  GROUP BY country, launched
  HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM classes c2, ships
  WHERE c2.class=ships.class AND c1.country=c2.country
  GROUP by country, launched)
  ORDER BY country
  ) l2
  WHERE l1.country=l2.country);


COUNTRY          LAUNCHED   COUNT(*)
---------------- ---------- ----------
Gt.Britain       1916       6
Japan            1913       1
USA              1941       3

что и требовалось найти : )))