🚀 go-pugleaf

everything except the misspelling of Brown is correct...
let us proceed

BKoz <BKoz@gte.net> wrote in message
news:3aBU4.99$ky1.78288@paloalto-snr1.gtei.net...
> 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
> >
> >
> >
> >
>
>