Easy Hands-On Experience with Basic SQL Querying
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.
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.
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!