When writing my introduction to SQL, I included sample queries that all dealt with the same hypothetical database. The database wasn’t especially fancy or well-planned, it just had a book’s title, location, publisher, and publication year. While you could read it and imagine the query, it didn’t give any hands-on experience, which I’ve found is the best way to learn.

Rather than expect you to set up a database, I used a site called SQLFiddle to make a version of this hypothetical database you can practice on. The database should be loaded at that link. You can enter queries in the right-hand panel. Start by pasting in some of the code farther down in this post.

Sample database

If SQLFiddle doesn’t load the sample database/table, you can copy it from below and paste it in, then click “Build Schema” so SQLFiddle can analyze it for you. This query will also create a table in an existing MySQL database.

Sample Queries

Get all titles where book was published in 2015:

SELECT title
FROM books
WHERE publication_date = '2015';

Get the title where it was published in 2015 by “Simon & Shuster”:

SELECT title
FROM books
WHERE publication_date = '2015'
AND publisher = 'Simon & Shuster';

Same query taking into account that data-entry standards may differ:

SELECT title
FROM books
WHERE publication_date = '2015'
AND (publisher = 'Simon & Shuster' OR publisher='Simon and Shuster');

Find books published in 2015 but somehow not shelved in New:

SELECT title
FROM books
WHERE publication_date = '2015'
AND NOT location = 'new';

Find books that are in New OR were published in 2015. Include their location & publication date and sort them by year. This would let you check whether all 2015 books are where they should be and whether any earlier books are still in New.

SELECT title, location, publication_date
FROM books
WHERE publication_date = '2015'
OR location = 'new'
ORDER BY publication_date;

Now try your own!

Leave a Reply

Your email address will not be published. Required fields are marked *