How to create a view in SQL?

Question

I am new to databases and SQL and I am stuck with this problem. Any help would be apriciated. Thank you in advance.

I have the following schema:

The problem is to create a view with the names of the battles, where at least 3 ships with less than 9 guns took part and at least one of these 3 ships with guns less than 9, has a result 'sunk'. I tried solving this problem, here is my solution:

create view v_battles
as
    select distinct battle 
    from outcomes o1
    where battle in (select battle 
                     from outcomes 
                     where ship in (select name 
                                    from ships 
                                    join classes on classes.class = ships.class
                                    where numguns < 9)
                     group by battle 
                     having count(ship) >= 3)

The problem is that my solution is not finished and I am not sure how to check if any of these ships has result 'sunk'.

Answer 1

I think you were just missing the clause that checks the outcome 'sunk', so I just followed what you had been doing and add another where clause that checks that the battle has the 'sunk' outcome. So if the battle is in both where clauses it will be displayed in your view. Hope it helps!

create view v_battles
as
    select distinct battle 
    from outcomes o1
    where battle in (select battle 
                     from outcomes 
                     where ship in (select name 
                                    from ships 
                                    join classes on classes.class = ships.class
                                    where numguns < 9)
                     group by battle 
                     having count(*) >= 3)
   and battle in (select battle 
                   from outcomes
                   where result = 'sunk')