🚀 go-pugleaf

As far as the Query:

This is how I read it:

It is looking for authors ,and their nationalities, who had books published
in the same year as ISBN #9444 , excluding the author of book #9444 unless
he/she appears as an author of another book.
----
Publication #6500 has 3 authors?  Brown , Bronw and Smith?  Bronw does not
show up in the authors table? Also in Book there is an ISBN 4104 but in
Publication there is not.  There is a 4101.  If this is right, it will
affect the query from above, it will change the meaning.

Please check and correct, if needed, the schema.  Then we can move on.

BKoz

"Geovanny Navarro-Bustos" <geovanny@ozemail.com.au> wrote in message
news:MNoU4.34950$PL4.679314@ozemail.com.au...
> I have a database schema which represents a simplified database used by a
> library to record details of books available for borrowing.  The books are
> identified by their International Standard book number(ISBN) and the
authors
> by their name, ie. the names of the authors are unique within the library
> system
>
> BOOK(isbn#,publisher,year,copies)
> PUBLICATION(isbn#,name#)
> AUTHOR(name#,gender,nationality,age)
>
> the relations look as follows:
>
> BOOK
>
> isbn      title                            publisher            year
> copies
> 4104    Databases                Macmill            1991                5
> 5202    SQL for the PC        MicoRes            1992                7
> 6426    DB Processing        Willey                1990                2
> 7345    dbaseIV Guide        Macmill                1993            1
> 8400    Program Design       Neilson            1989               5
> 9444    Advanced SQL        Neilson            1991                4
> 6500    Info in Action            Macmill            1991                2
>
> AUTHOR
>
> name                gender        nationality         age
> Adams AF        F                AUS                  23
> Brown J            M                AUS                    71
> Collin T            M                NZ                        44
> Jones S            F                USA                    65
> Kroenke DM     F                AUS                    42
> Smith JA            F                AUS                    32
> Smith JB            M                USA                    19
>
>
> PUBLICATION
>
> isbn                    name
> 4101                Brown J
> 4101                Collins T
> 5202                Adams AF
> 6426                Smith JB
> 7345                Jones S
> 8400                  Smith JA
> 9444                Adams AF
> 6500                Brown J
> 6500                Bronw J
> 6500                Smith JA
>
> here are my questions:
>
> Using the above database formulate the following sql queries:
>
> (a)  Using a JOIN list the authors' names and titles of books with titles
> beginning with 'D' or 'd'.
>
> (b)  Using UNION, list the authors' names with either Australian
nationality
> or who have written more than one book or both
>
> (c)  List the title and year of the most recently published book(s).
>
> (d)  Using a subquery, list titles of books written by authors with the
same
> nationality as 'Smith JB'.  Exclude titles written by 'Smith JB'.
>
>
>
>
> Lastly, can anyone tell me in English what the following SQL statement is
> meant to find:
>
> Select name, nationality
> From author
> Where name is in
>         (Select name
>         From publication
>         where isbn is in
>                 (Select isbn
>                 From book
>                 Where isbn <>9444
>                   And year is in
>                         (Select year
>                         From book
>                         Where isbn = 9444)))
>
> thanks in advance
>
>
>
>