Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Querying Relational Databases Joining Table Data with SQL Outer Joins

Justin Sorensen
Justin Sorensen
14,734 Points
Posted by Justin Sorensen
Justin Sorensen
14,734 Points

SQL file for the SQL Playground?

Is there any way we might get a SQL file that accompanies the SQL Playground? I'd like to compare what's in the file to what's in the database to see how one ties in primary and foreign keys for each table.

NiKole Maxwell
NiKole Maxwell
11,083 Points

I'd also appreciate a data dump so I could play with it in the system I'm familiar with.

If this weren't locked down I'd tell you to run 

SELECT * FROM sys.foreign_keys*

1 Answer

Zachary Betz
Zachary Betz
10,413 Points
Zachary Betz
Zachary Betz
10,413 Points

Took me a bit of poking around but finally got it. If you "right-click" > "view page source" in the SQL Playground workspaces window, there is a script tag that has the CREATE and INSERT statements you need. After tidying up the original code, further below I've included what you'll need. You'll want to copy and paste the sql into a file named dump.sql.

If you have sqlite3 installed (and on your PATH), open a command line and run the following to setup the db locally.

  1. sqlite3 playground.db (you can name the db whatever you want)
  2. .read dump.sql (runs the sql file which creates tables and inserts values; here I had the file in the same directory as the db)
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;


CREATE TABLE Make (
  MakeID INTEGER PRIMARY KEY,
  MakeName TEXT );

INSERT INTO Make VALUES(1,'Ford');
INSERT INTO Make VALUES(2,'Chevy');
INSERT INTO Make VALUES(3,'Jeep');
INSERT INTO Make VALUES(4,'Dodge');
INSERT INTO Make VALUES(5,'Honda');
INSERT INTO Make VALUES(6,'Kia');
INSERT INTO Make VALUES(7,'Toyota');
INSERT INTO Make VALUES(8,'BMW');


CREATE TABLE SalesRep (
  SalesRepID INTEGER PRIMARY KEY,
  FirstName TEXT,
  LastName TEXT,
  SSN TEXT,
  PhoneNumber TEXT,
  StreetAddress TEXT,
  City TEXT,
  State TEXT,
  ZipCode TEXT );

INSERT INTO SalesRep VALUES(1,'Mike','Jones','555-44-3333','314-555-9999','1233 ThisStreet','Saint Louis','MO','63119');
INSERT INTO SalesRep VALUES(2,'Indira','Gupta','444-55-6666','314-555-8888','3344 StreetX','Saint Louis','MO','63126');
INSERT INTO SalesRep VALUES(3,'Rebecca','Williams','666-22-3333','636-555-7777','543 MyStreet','Saint Louis','MO','63114');
INSERT INTO SalesRep VALUES(4,'Jordan','Jackson','999-33-1111','573-555-6666','9876 Anywhere','Columbia','MO','65203');
INSERT INTO SalesRep VALUES(5,'Luis','Santiago','333-44-5555','573-555-5555','8769 StreetY','Columbia','MO','65203');
INSERT INTO SalesRep VALUES(6,'Vincent','Schwartz','888-99-7777','573-555-4444','7690 StreetZ','Columbia','MO','65203');
INSERT INTO SalesRep VALUES(7,'Shelley','Washington','111-00-9999','555-000-4444','5050 Park St.','St. Louis','MO','63141');


CREATE TABLE Location (
  LocationID INTEGER PRIMARY KEY,
  LocationName TEXT,
  StreetAddress TEXT,
  City TEXT,
  State TEXT,
  ZipCode TEXT );

INSERT INTO Location VALUES(1,'Auto Sellers - St. Louis','9090 Dealer St.','Saint Louis','MO','63110');
INSERT INTO Location VALUES(2,'Auto Sellers - Columbia','3322 Main St.','Columbia','MO','65203');


CREATE TABLE Sale (
  SaleID INTEGER PRIMARY KEY,
  CarID INTEGER,
  CustomerID INTEGER,
  LocationID INTEGER,
  SalesRepID INTEGER,
  SaleAmount REAL,
  SaleDate TEXT );

INSERT INTO Sale VALUES(1,3,1,2,6,17500.0,'2015-05-01');
INSERT INTO Sale VALUES(2,2,2,1,1,25000.0,'2015-06-01');
INSERT INTO Sale VALUES(3,1,3,1,2,25000.0,'2015-06-01');
INSERT INTO Sale VALUES(4,6,5,1,1,27300.0,'2015-06-05');
INSERT INTO Sale VALUES(5,5,4,2,4,24000.0,'2015-06-28');
INSERT INTO Sale VALUES(6,4,6,2,4,18900.0,'2015-07-03');
INSERT INTO Sale VALUES(7,9,7,1,3,16250.0,'2015-07-05');
INSERT INTO Sale VALUES(8,8,9,2,5,30999.0,'2015-07-05');
INSERT INTO Sale VALUES(9,7,8,2,4,28000.0,'2015-07-29');
INSERT INTO Sale VALUES(10,13,10,1,1,35000.0,'2015-08-05');
INSERT INTO Sale VALUES(11,12,11,1,1,33750.0,'2015-08-06');
INSERT INTO Sale VALUES(12,11,12,1,2,29900.0,'2015-08-06');
INSERT INTO Sale VALUES(13,10,13,1,3,16750.0,'2015-08-15');
INSERT INTO Sale VALUES(14,18,15,1,2,26900.0,'2015-08-20');
INSERT INTO Sale VALUES(15,17,14,2,6,27300.0,'2015-08-29');
INSERT INTO Sale VALUES(16,16,16,2,6,42250.0,'2015-09-02');
INSERT INTO Sale VALUES(17,15,17,2,5,39900.0,'2015-09-17');
INSERT INTO Sale VALUES(18,14,18,1,2,37750.0,'2015-09-25');
INSERT INTO Sale VALUES(19,21,19,2,4,32500.0,'2015-09-29');
INSERT INTO Sale VALUES(20,20,21,1,1,21500.0,'2015-10-10');
INSERT INTO Sale VALUES(21,19,20,2,5,18800.0,'2015-10-10');
INSERT INTO Sale VALUES(22,25,22,2,4,25000.0,'2015-10-10');
INSERT INTO Sale VALUES(23,24,23,1,1,27800.0,'2015-10-20');
INSERT INTO Sale VALUES(24,23,25,1,2,24500.0,'2015-10-20');
INSERT INTO Sale VALUES(25,22,24,1,3,27400.0,'2015-11-01');
INSERT INTO Sale VALUES(26,29,26,1,2,32000.0,'2015-11-05');
INSERT INTO Sale VALUES(27,28,27,1,3,18400.0,'2015-11-05');
INSERT INTO Sale VALUES(28,27,28,2,5,19000.0,'2015-11-15');
INSERT INTO Sale VALUES(29,26,1,2,6,26350.0,'2015-11-15');
INSERT INTO Sale VALUES(30,32,18,1,1,40850.0,'2015-11-15');
INSERT INTO Sale VALUES(31,31,22,1,2,39300.0,'2015-11-20');
INSERT INTO Sale VALUES(32,30,1,2,6,28000.0,'2015-11-23');
INSERT INTO Sale VALUES(33,29,17,2,NULL,31500.0,'2015-12-20');


CREATE TABLE Model (
  ModelID INTEGER PRIMARY KEY,
  MakeID INTEGER,
  ModelName TEXT );

INSERT INTO Model VALUES(1,1,'Escape');
INSERT INTO Model VALUES(2,1,'Focus');
INSERT INTO Model VALUES(3,1,'Fusion');
INSERT INTO Model VALUES(4,2,'Impala');
INSERT INTO Model VALUES(5,2,'Cruze');
INSERT INTO Model VALUES(6,2,'Colorado');
INSERT INTO Model VALUES(7,3,'Cherokee');
INSERT INTO Model VALUES(8,3,'Grand Cherokee');
INSERT INTO Model VALUES(9,4,'Challenger');
INSERT INTO Model VALUES(10,4,'Dart');
INSERT INTO Model VALUES(11,5,'Accord');
INSERT INTO Model VALUES(12,5,'CRV');
INSERT INTO Model VALUES(13,6,'Soul');
INSERT INTO Model VALUES(14,6,'Rio');
INSERT INTO Model VALUES(15,7,'Camry');
INSERT INTO Model VALUES(16,7,'Sienna');
INSERT INTO Model VALUES(17,5,'Element');


CREATE TABLE Car (
  CarID INTEGER PRIMARY KEY,
  ModelID INTEGER,
  VIN TEXT,
  ModelYear INTEGER,
  StickerPrice REAL );

INSERT INTO Car VALUES(1,1,'1111111111111',2014,25000.0);
INSERT INTO Car VALUES(2,1,'222222222222',2015,26000.0);
INSERT INTO Car VALUES(3,2,'333333333333333',2015,18000.0);
INSERT INTO Car VALUES(4,2,'444444444',2016,18900.0);
INSERT INTO Car VALUES(5,3,'5555555555555',2014,24600.0);
INSERT INTO Car VALUES(6,3,'66666666666',2015,27300.0);
INSERT INTO Car VALUES(7,4,'777777777777',2016,28000.0);
INSERT INTO Car VALUES(8,4,'88888888888888',2014,32000.0);
INSERT INTO Car VALUES(9,5,'9999999999999',2015,16500.0);
INSERT INTO Car VALUES(10,5,'123123123123',2015,16950.0);
INSERT INTO Car VALUES(11,6,'234234234234',2016,29900.0);
INSERT INTO Car VALUES(12,6,'345345345345',2015,34000.0);
INSERT INTO Car VALUES(13,7,'456456456456',2016,35500.0);
INSERT INTO Car VALUES(14,7,'567567567567',2016,37900.0);
INSERT INTO Car VALUES(15,8,'678678678678',2014,39900.0);
INSERT INTO Car VALUES(16,8,'333444555666',2016,42500.0);
INSERT INTO Car VALUES(17,9,'5676756464546660',2016,27500.0);
INSERT INTO Car VALUES(18,9,'888999777555',2015,26900.0);
INSERT INTO Car VALUES(19,10,'987654321',2014,18900.0);
INSERT INTO Car VALUES(20,10,'7777777666666650',2016,22000.0);
INSERT INTO Car VALUES(21,11,'99999888887777',2016,32500.0);
INSERT INTO Car VALUES(22,11,'66677776667777',2015,27500.0);
INSERT INTO Car VALUES(23,12,'22222111111145500',2014,24500.0);
INSERT INTO Car VALUES(24,12,'45455554455555',2015,27900.0);
INSERT INTO Car VALUES(25,13,'6666666555533330',2016,25000.0);
INSERT INTO Car VALUES(26,13,'7777770000000000',2016,26500.0);
INSERT INTO Car VALUES(27,14,'90909090909',2016,19000.0);
INSERT INTO Car VALUES(28,14,'3147765432',2015,18500.0);
INSERT INTO Car VALUES(29,15,'6666666673333330',2016,32000.0);
INSERT INTO Car VALUES(30,15,'2223334445552110',2014,28000.0);
INSERT INTO Car VALUES(31,16,'445544334455',2016,41000.0);
INSERT INTO Car VALUES(32,16,'332211334455',2014,39500.0);
INSERT INTO Car VALUES(33,2,'778866445566',2016,21000.0);
INSERT INTO Car VALUES(34,7,'999888777666555',2016,36900.0);
INSERT INTO Car VALUES(35,9,'444443333322222',2014,29000.0);
INSERT INTO Car VALUES(36,11,'5432154321',2015,38500.0);


CREATE TABLE Customer (
  CustomerID INTEGER PRIMARY KEY,
  FirstName TEXT,
  LastName TEXT,
  Gender TEXT,
  SSN TEXT );

INSERT INTO Customer VALUES(1,'Vincent','Schwartz','M','888-99-7777');
INSERT INTO Customer VALUES(2,'Shirley','Adams','F','111-99-1111');
INSERT INTO Customer VALUES(3,'Tasha','Bell','F','444-33-7234');
INSERT INTO Customer VALUES(4,'Adam','Turley','M','666-11-6455');
INSERT INTO Customer VALUES(5,'Duane','McGee','M','654-77-2222');
INSERT INTO Customer VALUES(6,'Marshawn','Jones','M','887-00-5544');
INSERT INTO Customer VALUES(7,'Debbie','Miller','F','778-99-3322');
INSERT INTO Customer VALUES(8,'Sai','Jajala','M','223-99-5557');
INSERT INTO Customer VALUES(9,'Phoong','Ming','F','990-99-4477');
INSERT INTO Customer VALUES(10,'Ablert','Matheny','M','545-44-5555');
INSERT INTO Customer VALUES(11,'Nicole','Dunn','F','776-33-3377');
INSERT INTO Customer VALUES(12,'Jasper','Olmer','M','232-11-7788');
INSERT INTO Customer VALUES(13,'Eddie','Sandberg','M','443-11-8877');
INSERT INTO Customer VALUES(14,'Sarah','Goldman','F','994-99-4444');
INSERT INTO Customer VALUES(15,'Lebron','McMichaels','M','767-66-7777');
INSERT INTO Customer VALUES(16,'Amanada','Peart','F','321-21-4321');
INSERT INTO Customer VALUES(17,'Mohammed','Wasem','M','575-55-5577');
INSERT INTO Customer VALUES(18,'Arif','Hadad','M','456-99-4567');
INSERT INTO Customer VALUES(19,'George','Schubert','M','779-99-9777');
INSERT INTO Customer VALUES(20,'Chris','Mortensen','M','888-99-8899');
INSERT INTO Customer VALUES(21,'Julia','Molina','F','880-01-0008');
INSERT INTO Customer VALUES(22,'Fred','Collier','M','809-99-5665');
INSERT INTO Customer VALUES(23,'Gail','Dickens','F','498-09-9876');
INSERT INTO Customer VALUES(24,'Hillary','Simpson','F','987-98-7987');
INSERT INTO Customer VALUES(25,'Andrew','Lee','M','908-43-3434');
INSERT INTO Customer VALUES(26,'Naomi','Naser','F','888-55-2211');
INSERT INTO Customer VALUES(27,'Ben','Crosby','M','866-65-0909');
INSERT INTO Customer VALUES(28,'Reggie','Smith','M','665-45-5454');


CREATE TABLE ForeignMake (ForeignMakeID INT Primary Key, MakeName TEXT);

INSERT INTO ForeignMake VALUES(1,'BMW');
INSERT INTO ForeignMake VALUES(2,'Volkswagen');
INSERT INTO ForeignMake VALUES(3,'Audi');
INSERT INTO ForeignMake VALUES(4,'Honda');
INSERT INTO ForeignMake VALUES(5,'Hyundai');
INSERT INTO ForeignMake VALUES(6,'Acura');
INSERT INTO ForeignMake VALUES(7,'Subaru');
INSERT INTO ForeignMake VALUES(8,'Kia');
INSERT INTO ForeignMake VALUES(9,'Toyota');


COMMIT;
Neil McPartlin
Neil McPartlin
14,662 Points
Neil McPartlin
Neil McPartlin
14,662 Points

Hi Zachery. Great work. I can only offer you '1 Point', but you do deserve 12. :)

Motivated by your efforts, I 'rebooted' my trusty old MySQL 5.6 server that lay idle on my Windows 7 machine. It nearly started first time (OK, I needed some jump leads), but my previous old database 'playware' was still functional.

I placed your dump.sql in the same folder as mysql.exe which for me is located at C:\Program Files\MySQL\MySQL Server 5.6\bin\dump.sql.

I started the server.

I then opened the MySQL 5.6 Command Line Client (entered my password) then at the mysql prompt (mysql>), I created the empty tth_cars database.

mysql> create database tth_cars;
Query OK, 1 row affected (0.01 sec)

I then selected it

mysql> use tth_cars;
Database changed

Imported the dump.sql file

mysql> source dump.sql;

[Lots of insertion entries finishing with...]
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>

Checked to see if there were new tables present

mysql> show tables;
+--------------------+
| Tables_in_tth_cars |
+--------------------+
| car                |
| customer           |
| foreignmake        |
| location           |
| make               |
| model              |
| sale               |
| salesrep           |
+--------------------+
8 rows in set (0.07 sec)

mysql>

Then ran a simple query on one of them

mysql> SELECT * FROM car;
+-------+---------+-------------------+-----------+--------------+
| CarID | ModelID | VIN               | ModelYear | StickerPrice |
+-------+---------+-------------------+-----------+--------------+
|     1 |       1 | 1111111111111     |      2014 |        25000 |
|     2 |       1 | 222222222222      |      2015 |        26000 |
|     3 |       2 | 333333333333333   |      2015 |        18000 |
|     4 |       2 | 444444444         |      2016 |        18900 |
|     5 |       3 | 5555555555555     |      2014 |        24600 |
|     6 |       3 | 66666666666       |      2015 |        27300 |
|     7 |       4 | 777777777777      |      2016 |        28000 |
|     8 |       4 | 88888888888888    |      2014 |        32000 |
|     9 |       5 | 9999999999999     |      2015 |        16500 |
|    10 |       5 | 123123123123      |      2015 |        16950 |
|    11 |       6 | 234234234234      |      2016 |        29900 |
|    12 |       6 | 345345345345      |      2015 |        34000 |
|    13 |       7 | 456456456456      |      2016 |        35500 |
|    14 |       7 | 567567567567      |      2016 |        37900 |
|    15 |       8 | 678678678678      |      2014 |        39900 |
|    16 |       8 | 333444555666      |      2016 |        42500 |
|    17 |       9 | 5676756464546660  |      2016 |        27500 |
|    18 |       9 | 888999777555      |      2015 |        26900 |
|    19 |      10 | 987654321         |      2014 |        18900 |
|    20 |      10 | 7777777666666650  |      2016 |        22000 |
|    21 |      11 | 99999888887777    |      2016 |        32500 |
|    22 |      11 | 66677776667777    |      2015 |        27500 |
|    23 |      12 | 22222111111145500 |      2014 |        24500 |
|    24 |      12 | 45455554455555    |      2015 |        27900 |
|    25 |      13 | 6666666555533330  |      2016 |        25000 |
|    26 |      13 | 7777770000000000  |      2016 |        26500 |
|    27 |      14 | 90909090909       |      2016 |        19000 |
|    28 |      14 | 3147765432        |      2015 |        18500 |
|    29 |      15 | 6666666673333330  |      2016 |        32000 |
|    30 |      15 | 2223334445552110  |      2014 |        28000 |
|    31 |      16 | 445544334455      |      2016 |        41000 |
|    32 |      16 | 332211334455      |      2014 |        39500 |
|    33 |       2 | 778866445566      |      2016 |        21000 |
|    34 |       7 | 999888777666555   |      2016 |        36900 |
|    35 |       9 | 444443333322222   |      2014 |        29000 |
|    36 |      11 | 5432154321        |      2015 |        38500 |
+-------+---------+-------------------+-----------+--------------+
36 rows in set (0.07 sec)

mysql>

And a new play area is born. Thanks again Zachery.

Zachary Betz
Zachary Betz
10,413 Points
Zachary Betz
Zachary Betz
10,413 Points

Hi Neil. Glad it was useful :)
Nice work with mysql. This should be helpful for future students.