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/
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
