Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
executable file 34 lines (23 sloc) 1.72 KB
-- ADVANCED / JOIN QUERIES
-- For all of these queries, you SHOULD NOT use an id # in a WHERE clause
-- Find all fields (book and author related) for all books written by George R.R. Martin.
SELECT * FROM authors, books WHERE authors.name = 'George R.R. Martin' AND authors.id = books.author_id;
-- Find all fields (book and author related) for all books written by Milan Kundera.
SELECT * FROM authors, books WHERE authors.name = 'Milan Kundera' AND authors.id = books.author_id;
-- Find all books written by an author from China or the UK.
SELECT * FROM authors, books WHERE (authors.nationality = 'China' AND authors.id = books.author_id)
OR (authors.nationality = 'United Kingdom' AND authors.id = books.author_id);
-- Find out how many books Albert Camus wrote.
SELECT COUNT(*) FROM authors, books WHERE authors.name = 'Albert Camus' AND authors.id = books.author_id;
-- Find out how many books were written by US authors.
SELECT COUNT(*) FROM authors, books WHERE authors.nationality = 'United States of America' AND authors.id = books.author_id;
-- Find all books written after 1930 by authors from Argentina.
SELECT title FROM authors, books WHERE books.publication_date > 1930 AND authors.nationality = 'Argentina'
AND authors.id = books.author_id;
-- Find all books written before 1980 by authors not from the US.
SELECT title FROM authors, books WHERE books.publication_date < 1980 AND authors.nationality != 'United States of America'
AND authors.id = books.author_id;
-- Find all authors whose names start with 'J'.
SELECT name FROM authors WHERE name LIKE 'J%';
-- Find all authors who have written books with that start with the letter 'N'.
SELECT * FROM authors, books WHERE books.title LIKE 'N%' AND authors.id = books.author_id;