Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
executable file 72 lines (48 sloc) 2.31 KB
-- BASIC QUERIES
-- Insert an author into the authors table with the name...wait for it...Gustave, Nationality Asotria and birth year 2016
INSERT INTO authors(name, nationality, birth_year) VALUES
('Gustave', 'Asotria', 2016);
-- Select the name and birth_year columns from the authors table
SELECT name, birth_year FROM authors;
-- Select all information from the author table where the name of the author is James Baldwin
SELECT * FROM authors WHERE name = 'James Baldwin';
-- Update the author name Gustave to Aloysius
UPDATE authors SET name = 'Aloysius' WHERE name = 'Gustave';
-- Delete Aloysius from the authors table.
DELETE FROM authors WHERE name = 'Aloysius';
-- Unless otherwise stated, all queries should return all columns
/*SELECT * FROM authors, books;*/
-- Get all information about all authors
SELECT * FROM authors;
-- Get just the name and birth year of all authors
SELECT name, birth_year FROM authors;
-- Get all authors born in the 20th centruy or later
SELECT * FROM authors WHERE birth_year > 1899;
-- Get all authors born in the
SELECT * FROM authors WHERE nationality = 'United States of America';
-- Get all books published on 1985
SELECT * FROM books WHERE publication_date = 1985;
-- Get all books published before 1989
SELECT * FROM books WHERE publication_date < 1989;
-- Get just the title of all books.
SELECT title FROM books;
-- Get just the year that 'A Dance with Dragons' was published
-- Cry when you realize how long it's been
SELECT publication_date FROM books WHERE title = 'A Dance with Dragons';
-- Get all books which have `the` somewhere in their title.
SELECT * FROM books WHERE title LIKE '%the%';
-- Add yourself as an author
INSERT INTO authors (name, nationality, birth_year)
VALUES ('Alexey Gorokholinsky', 'Russia', 1984);
-- Add two books that you'd like to write (you can hard-code your id as the author id)
INSERT INTO books (title, author_id)
VALUES ('How to Eat a Lot', 10);
INSERT INTO books (title, author_id)
VALUES ('How to Not Eat a Lot', 10);
-- Update one of your books to have a new title
UPDATE books SET title = 'How to Eat like a Pig' WHERE title = 'How to Eat a Lot';
-- Delete your books
DELETE FROM books WHERE title = 'How to Eat like a Pig';
DELETE FROM books WHERE title = 'How to Not Eat a Lot';
-- Delete your author entry
DELETE FROM authors WHERE id = 10;