CoCalc Public FilesSQL_for_data_analysis.sagewsOpen with one click!
Authors: Mathias Burton, Tim Clemans SMC, ℏal Snyder, Hal Snyder, Luke Swart
Views : 563
Compute Environment: Ubuntu 18.04 (Deprecated)
%sh wget -O chinookdb.zip "https://www.udacity.com/api/nodes/5390363876/supplemental_media/chinook-dbzip/download?_ga=1.43670181.1631387024.1462304289"
--2016-06-10 18:18:23-- https://www.udacity.com/api/nodes/5390363876/supplemental_media/chinook-dbzip/download?_ga=1.43670181.1631387024.1462304289 Resolving www.udacity.com (www.udacity.com)... 52.40.122.139, 52.35.235.175, 54.68.144.150 Connecting to www.udacity.com (www.udacity.com)|52.40.122.139|:443... connected. HTTP request sent, awaiting response... 403 Forbidden 2016-06-10 18:18:23 ERROR 403: Forbidden.
import sqlite3 conn = sqlite3.connect('chinook.db') c = conn.cursor() for row in c.execute('SELECT count(name) FROM Track Where Composer = "U2"'): print row
(44,)
for row in c.execute('SELECT Title FROM Employee WHERE LastName = "Johnson"'): print row
(u'Sales Support Agent',)
for row in c.execute(" select name from sqlite_master where type = 'table';"): print row
(u'Album',) (u'Artist',) (u'Customer',) (u'Employee',) (u'Genre',) (u'Invoice',) (u'InvoiceLine',) (u'MediaType',) (u'Playlist',) (u'PlaylistTrack',) (u'Track',)
def sqlite3run(code): for row in c.execute(code): print row
%sqlite3run PRAGMA table_info(Invoice);
(0, u'InvoiceId', u'INTEGER', 1, None, 1) (1, u'CustomerId', u'INTEGER', 1, None, 0) (2, u'InvoiceDate', u'DATETIME', 1, None, 0) (3, u'BillingAddress', u'NVARCHAR(70)', 0, None, 0) (4, u'BillingCity', u'NVARCHAR(40)', 0, None, 0) (5, u'BillingState', u'NVARCHAR(40)', 0, None, 0) (6, u'BillingCountry', u'NVARCHAR(40)', 0, None, 0) (7, u'BillingPostalCode', u'NVARCHAR(10)', 0, None, 0) (8, u'Total', u'NUMERIC(10,2)', 1, None, 0)
%sqlite3run PRAGMA table_info(InvoiceLine);
(0, u'InvoiceLineId', u'INTEGER', 1, None, 1) (1, u'InvoiceId', u'INTEGER', 1, None, 0) (2, u'TrackId', u'INTEGER', 1, None, 0) (3, u'UnitPrice', u'NUMERIC(10,2)', 1, None, 0) (4, u'Quantity', u'INTEGER', 1, None, 0)
%md Phase 1 "SELECT sql, statement FROM Udacious WHERE queryId = 35;" Table Name: Udacious Columns: problemSet INTEGER, node INTEGER, queryId INTEGER, title TEXT, sql TEXT, statement TEXT" Awesome.db

Phase 1 "SELECT sql, statement FROM Udacious WHERE queryId = 35;" Table Name: Udacious

Columns: problemSet INTEGER, node INTEGER, queryId INTEGER, title TEXT, sql TEXT, statement TEXT"

%md All the tables in the zoo database animals This table lists individual animals in the zoo. Each animal has only one row. There may be multiple animals with the same name, or even multiple animals with the same name and species. name the animal's name (example: 'George') species the animal's species (example: 'gorilla') birthdate the animal's date of birth (example: '1998-05-18') diet This table matches up species with the foods they eat. Every species in the zoo eats at least one sort of food, and many eat more than one. If a species eats more than one food, there will be more than one row for that species. species the name of a species (example: 'hyena') food the name of a food that species eats (example: 'meat') taxonomy This table gives the (partial) biological taxonomic names for each species in the zoo. It can be used to find which species are more closely related to each other evolutionarily. name the common name of the species (e.g. 'jackal') species the taxonomic species name (e.g. 'aureus') genus the taxonomic genus name (e.g. 'Canis') family the taxonomic family name (e.g. 'Canidae') t_order the taxonomic order name (e.g. 'Carnivora') If you've never heard of this classification, don't worry about it; the details won't be necessary for this course. But if you're curious, Wikipedia articles Taxonomy and Biological classification may help. ordernames This table gives the common names for each of the taxonomic orders in the taxonomy table. t_order the taxonomic order name (e.g. 'Cetacea') name the common name (e.g. 'whales and dolphins') The SQL for it And here are the SQL commands that were used to create those tables. We won't cover the create table command until lesson 4, but it may be interesting to look at: create table animals ( name text, species text, birthdate date); create table diet ( species text, food text); create table taxonomy ( name text, species text, genus text, family text, t_order text); create table ordernames ( t_order text, name text); Remember: In SQL, we always put string and date values inside single quotes. Have questions? Head to the forums for discussion with the Udacity Community.

All the tables in the zoo database animals This table lists individual animals in the zoo. Each animal has only one row. There may be multiple animals with the same name, or even multiple animals with the same name and species. name — the animal's name (example: 'George') species — the animal's species (example: 'gorilla') birthdate — the animal's date of birth (example: '1998-05-18') diet This table matches up species with the foods they eat. Every species in the zoo eats at least one sort of food, and many eat more than one. If a species eats more than one food, there will be more than one row for that species. species — the name of a species (example: 'hyena') food — the name of a food that species eats (example: 'meat') taxonomy This table gives the (partial) biological taxonomic names for each species in the zoo. It can be used to find which species are more closely related to each other evolutionarily. name — the common name of the species (e.g. 'jackal') species — the taxonomic species name (e.g. 'aureus') genus — the taxonomic genus name (e.g. 'Canis') family — the taxonomic family name (e.g. 'Canidae') t_order — the taxonomic order name (e.g. 'Carnivora') If you've never heard of this classification, don't worry about it; the details won't be necessary for this course. But if you're curious, Wikipedia articles Taxonomy and Biological classification may help. ordernames This table gives the common names for each of the taxonomic orders in the taxonomy table. t_order — the taxonomic order name (e.g. 'Cetacea') name — the common name (e.g. 'whales and dolphins') The SQL for it And here are the SQL commands that were used to create those tables. We won't cover the create table command until lesson 4, but it may be interesting to look at: create table animals (
name text, species text, birthdate date);

create table diet ( species text, food text);

create table taxonomy ( name text, species text, genus text, family text, t_order text);

create table ordernames ( t_order text, name text); Remember: In SQL, we always put string and date values inside single quotes. Have questions? Head to the forums for discussion with the Udacity Community.

%sqlite3run SELECT BillingCity, Count(*) as NumTracks FROM Invoice Join InvoiceLine ON Invoice.InvoiceID = InvoiceLine.InvoiceID JOIN Track ON InvoiceLine.TrackID = Track.GenreID Join Genre ON Track.GenreID = Genre.GenreID WHERE Invoice.BillingCountry = 'France' AND Genre.Name = 'Alternative & Punk' GROUP BY BillingCity ORDER BY NumTracks DESC
import sqlite3 conn = sqlite3.connect('practice.db') c = conn.cursor()
%sqlite3run CREATE TABLE InvoiceLine ( InvoiceLineID INTEGER PRIMARY KEY, InvoiceID INTEGER, TrackID INTEGER, UnitPrice REAL, Quantity INTEGER )
Error in lines 1-1 Traceback (most recent call last): File "/projects/sage/sage-6.10/local/lib/python2.7/site-packages/smc_sagews/sage_server.py", line 905, in execute exec compile(block+'\n', '', 'single') in namespace, locals File "", line 1, in <module> File "/projects/sage/sage-6.10/local/lib/python2.7/site-packages/smc_sagews/sage_server.py", line 946, in execute_with_code_decorators code = code_decorator(code) File "", line 2, in sqlite3run OperationalError: table InvoiceLine already exists
import sqlite3 conn = sqlite3.connect('chinook.db') c = conn.cursor()
%sqlite3run SELECT count(*) FROM (SELECT Track.MediaTypeID FROM Track Join Genre ON Track.GenreID = Genre.GenreID WHERE Genre.Name = 'Pop') as t1 Join MediaType ON t1.MediaTypeID = MediaType.MediaTypeID Where MediaType.Name = 'MPEG audio file'
(14,)
%sqlite3run SELECT MediaType.Name FROM MediaType
(u'MPEG audio file',) (u'Protected AAC audio file',) (u'Protected MPEG-4 video file',) (u'Purchased AAC audio file',) (u'AAC audio file',)
%sqlite3run SELECT COUNT(*) FROM (SELECT DISTINCT Invoice.CustomerID FROM Invoice Join InvoiceLine ON Invoice.InvoiceID = InvoiceLine.InvoiceID Join Track On InvoiceLine.TrackID = Track.TrackID Join Genre On Track.GenreID = Genre.GenreID WHERE Genre.Name = 'Jazz')
(32,)
%sqlite3run SELECT g, count(*) FROM (SELECT Genre.Name as g, Track.milliseconds FROM Track Join Genre ON Track.GenreID = Genre.GenreID WHERE Track.milliseconds < 393599.2121039109) GROUP BY g ORDER BY COUNT(*) DESC
(u'Rock', 1162) (u'Latin', 569) (u'Alternative & Punk', 320) (u'Metal', 300) (u'Jazz', 116) (u'Blues', 72) (u'Classical', 60) (u'R&B/Soul', 60) (u'Reggae', 58) (u'Pop', 46) (u'Soundtrack', 43) (u'Alternative', 38) (u'Hip Hop/Rap', 34) (u'Electronica/Dance', 28) (u'World', 28) (u'Easy Listening', 24) (u'Heavy Metal', 23) (u'Bossa Nova', 14) (u'Rock And Roll', 12) (u'Drama', 1) (u'Opera', 1)
%sqlite3run SELECT Avg(milliseconds) FROM Track
(393599.2121039109,)