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 trialJustin Sorensen
14,734 PointsSQL 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.
1 Answer
Zachary Betz
10,413 PointsTook 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.
-
sqlite3 playground.db
(you can name the db whatever you want) -
.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
14,662 PointsHi 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
10,413 PointsHi Neil. Glad it was useful :)
Nice work with mysql. This should be helpful for future students.
NiKole Maxwell
11,083 PointsNiKole Maxwell
11,083 PointsI'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*