Skip to content

Latest commit

 

History

History
113 lines (81 loc) · 5.78 KB

quick_steps.textile

File metadata and controls

113 lines (81 loc) · 5.78 KB
Raw

A Quick Introduction to SQL Using SQLite (Just the Steps)

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;

Importing data from a flat file

14. Import a delimited file:
a. Go to https://github.com/tthibo/SQL-Tutorial/raw/master/tutorial_files/contributors.txt

Save the file as “contributors.txt” somewhere you can find it (your desktop is a good place).

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.

Aggregate Functions

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;

Creative Commons License
A Gentle Introduction to SQL Using SQLite by Troy Thibodeaux is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.