[Tutor] mysqlite

[Alan Gauld]

On 06/03/13 23:25, Lolo Lolo wrote:
> can someone please explain this to me.
>
> update Employee
> set ManagerID = (Select EmpID
>                   from Employee
>     where Name = 'John Brown')
> where Name = 'Fred Smith' OR
>        Name = 'Anne Jones';
>
>
> this is saying set the managerID of 'Fred Smith' or
 > 'Jane Brown' to the ID returned in the nested select statement?

Almost.
Its saying set the Manager of both Fred and Anne to John.
The OR selects both entries and then applies the update
to them. We can logically split it like this (so you can
test it at the command prompt :-):

SELECT * FROM Employee
where Name = 'Fred Smith' OR
       Name = 'Anne Jones';

That will get you two rows.

Select EmpID
  from Employee
  where Name = 'John Brown'

That will get you one ID, lets call it JohnsID

and putting them together:

update Employee
set ManagerID = JohnsID
where Name = 'Fred Smith' OR
       Name = 'Anne Jones';

Hopefully that clarifies it a bit.

> and this:
>
> drop table author;
> create table author (
>
> drop table book;
> create table book (
>
> drop table book_author;
> create table book_author (

sqlite> SELECT title from book, book_author
...> where book_author.bookID = book.ID
...> and book_author.authorID = (select ID from author
...>                             where name = "Jane Austin");

> maybe its simple but because its more complex im finding it hard to read.

Yes, its where SQL starts to get more difficult when you start joining 
tables together. We are basically extracting data from the author table
(Jane Austins ID) to use as a filter in the book_author table to get the 
IDs of all the books in our database written by Jane Austin. We then use 
this list of IDs to filter the Books table to get the names of those books.

The whole thing could be simplified ifg we didn't use IDs but just used 
the names directly. But that leads to other problems (discussed in the 
tutorial) when names change etc (not so likely with books but very 
common with real people!). So using IDs, even though it adds complexity, 
is usually a worthwhile habit to get into.

> ... why is book_author
 > also in the select statement when it isn't
> being returned?

Because we are using it in the query (the where clause) we need to 
include it in the list of tables.

You can think of the SELECT statement as a mini program and the tables 
list as a list of all the objects used in the program. Its not just for 
the values being returned.


> or are we actually returning every single field from book author?

No, only the set of IDs corresponding to Jane Austin's ID.
The thing to remember about book_author is that it only contains IDs it 
has no "real" data of its own. It's purely there to facilitate a multi 
way join between books and authors.

The good news is that once you wrap your brain around this construct 
most SQL queries you will come across will be comprehensible to you.

HTH, if not, just keep firing in the questions.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/