1. Open Firefox and find the SQLite Manager Plugin in the tools menu:
2. Create a new database called “contributions”:
3. Create a new table called “contributors” (
) and give it the following field definitions:
4. Insert a row of data:
Use the “Execute SQL” tab (it’s in the second menu row, under the icons): 
INSERT INTO contributors (last_name, first_name, city, state, zip, amount) VALUES ('Buffet', 'Warren', 'Omaha', 'Nebraska', '68101', 1500);
5. Insert three more rows (one at a time or all at once)
INSERT INTO contributors (last_name, first_name, city, state, zip, amount) VALUES ('Winfrey', 'Oprah', 'Chicago', 'IL', '60601', 500);
INSERT INTO contributors (last_name, first_name, city, state, zip, amount) VALUES ('Chambers', 'Anne Cox', 'Atlanta', 'GA', '30301', 200);
INSERT INTO contributors (last_name, first_name, city, state, zip, amount) VALUES ('Cathy', 'S. Truett', 'Atlanta', 'GA', '30301', 1200);
6. Select everything from the contributors table:
SELECT * FROM contributors;7. Choose some fields to select:
SELECT city, state FROM contributors;8. Get a distinct set of cities and states:
SELECT DISTINCT city, state FROM contributors;9. Add some conditions to the SELECT:
SELECT * from contributors WHERE state='GA';Now try playing around with some of the operators:
| operator | description |
| = | Equal |
| <> | Not equal* |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
Here are some examples:
SELECT * from contributors WHERE amount > 1200;SELECT * from contributors WHERE amount >= 1200;SELECT last_name from contributors WHERE state <> 'GA';10. Combine conditions with AND or OR:
SELECT * FROM contributors WHERE state = 'GA' AND amount > 1000;SELECT * FROM contributors WHERE state = 'GA' OR amount > 1000;11. But watch out for operator precedence:
SELECT * FROM contributors WHERE city = 'Chicago' OR state = 'GA' AND amount > 1000;How did Oprah sneak in there?
Try that again:
SELECT * FROM contributors WHERE (city = 'Chicago' OR state = 'GA') AND amount > 1000;12. Order the results:
SELECT last_name, amount FROM contributors WHERE amount > 200 ORDER BY amount;And reverse the order:
SELECT last_name, amount FROM contributors WHERE amount > 200 ORDER BY amount DESC;And order by more than one column:
SELECT last_name, state, amount FROM contributors ORDER BY state, amount DESC;13. Return only the top two contributors:
SELECT * FROM CONTRIBUTORS ORDER BY amount DESC LIMIT 2;14. Import a delimited file:
a. Go to https://github.com/tthibo/SQL-Tutorial/raw/master/tutorial_files/contributors.txt
c. Click the SQLite Manager Import Wizard icon (
).
- Using the Select File button, browse for the contributors.txt file you just saved.
- Check the “First row contains column names” check box.
- Select Pipe(|) for “Fields separated by”
- For everything else, you can keep the default selections.
- You should have 103 rows of data.
15. Count the number of contributors from California:
SELECT COUNT(id) FROM contributors WHERE state = 'CA';16. Select the minimum and maximum contributions:
SELECT MAX(amount) FROM contributors;SELECT MIN(amount) FROM contributors;17. Add up the contributions from Georgia:
SELECT SUM(amount) FROM contributors WHERE state = 'GA';18. Find the average contribution:
SELECT AVG(amount) FROM contributors;19. Find the total amount of contributions per state:
SELECT state, SUM(amount) FROM contributors GROUP BY state;20. Find the total amount of contributions per city and state:
SELECT city, state, SUM(amount) FROM contributors GROUP BY city, state;Now order cities by their total contributions:
SELECT city, state, SUM(amount) FROM contributors GROUP BY city, state ORDER BY SUM(amount) DESC;
A Gentle Introduction to SQL Using SQLite by Troy Thibodeaux is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.
