Fisseha Berhane, PhD
Data Scientist
443-970-2353
[email protected]
CV Resume 
443-970-2353
[email protected]
CV Resume
This is MySQL excercise done with Jupyter. I simply love Jupyter. I use Python, R, Matlab and MySQL inside Jupyter, a simply cool tool!!
%load_ext sql
The following command will log me to the MySQL server at mysqlserver as the user 'studentuser' and will select the database named 'dognitiondb' :
%sql mysql://studentuser:[email protected]/dognitiondb
%sql USE dognitiondb
(1) confirm how many tables each database has, and (2) identify the fields contained in each table of the database. To determine how many tables each database:
%sql SHOW tables
To determine what columns or fields (we will use those terms interchangeably in this course) are in each table, you can use the SHOW command again, but this time (1) you have to clarify that you want to see columns instead of tables, and (2) you have to specify from which table you want to examine the columns.
The syntax, which sounds very similar to what you would actually say in the spoken English language, looks like this:
SHOW columns FROM (enter table name here)
or if you have multiple databases loaded:
SHOW columns FROM (enter table name here) FROM (enter database name here)
or
SHOW columns FROM databasename.tablename
Whenever you have multiple databases loaded, you will need to specify which database a table comes from using one of the syntax options described above.
Question 1: How many columns does the "dogs" table have? Enter the appropriate query below to find out:
%sql SHOW columns FROM dogs
An alternate way to learn the same information would be to use the DESCRIBE function. The syntax is:
DESCRIBE tablename
Question 2: Try using the DESCRIBE function to learn how many columns are in the "reviews" table:
%sql DESCRIBE reviews
Remember:
'the marks that surrounds this phrase are single quotation marks'
"the marks that surrounds this phrase are double quotation marks"
`the marks that surround this phrase are backticks`
An important note for executing queries in Jupyter: in order to tell Python that you want to execute SQL language on multiple lines, you must include two percent signs in front of the SQL prefix instead of one. Therefore, to execute the above query, you should enter:
%%sql
SELECT breed
FROM dogs;
SELECT breed
FROM dogs LIMIT 5;
The "5" in this case indicates that you will only see the first 5 rows of data you select.
Question : the first 10 rows of the breed column in the dogs table.
%sql SELECT breed FROM dogs LIMIT 10;
You can also select rows of data from different parts of the output table, rather than always just starting at the beginning. To do this, use the OFFSET clause after LIMIT. The number after the OFFSET clause indicates from which row the output will begin querying. Note that the offset of Row 1 of a table is actually 0. Therefore, in the following query:
SELECT breed
FROM dogs LIMIT 10 OFFSET 5;
10 rows of data will be returned, starting at Row 6.
An alternative way to write the OFFSET clause in the query is:
SELECT breed
FROM dogs LIMIT 5, 10;
In this notation, the offset is the number before the comma, and the number of rows returned is the number after the comma.
%%sql
SELECT breed
FROM dogs limit 10 OFFSET 5
%%sql
SELECT breed
FROM dogs limit 5,10;
The LIMIT command is one of the pieces of syntax that can vary across database platforms. MySQL uses LIMIT to restrict the output, but other databases including Teradata use a statement called "TOP" instead. Oracle has yet another syntax:
http://www.tutorialspoint.com/sql/sql-top-clause.htm
Make sure to look up the correct syntax for the database type you are using.
%%sql
SELECT breed, breed_type, breed_group
FROM dogs LIMIT 5,10;
Another trick to know about when using SELECT is that you can use an asterisk as a "wild card" to return all the data in a table. (A wild card is defined as a character that will represent or match any character or sequence of characters in a query.) Take note, this is very risky to do if you do not limit your output or if you don't know how many data are in your database, so use the wild card with caution. However, it is a handy tool to use when you don't have all the column names easily available or when you know you want to query an entire table.
%%sql
SELECT *
FROM dogs LIMIT 5,10;
SELECT statements can also be used to make new derivations of individual columns using "+" for addition, "-" for subtraction, "*" for multiplication, or "/" for division. For example, if you wanted the median inter-test intervals in hours instead of minutes or days, you could query:
Question: Go ahead and try it, adding in a column to your output that shows you the original median_iti in minutes.
%%sql
SELECT median_iti_minutes/60
FROM dogs LIMIT 5,10;
Question: How would you retrieve the first 15 rows of data from the dog_guid, subcategory_name, and test_name fields of the Reviews table, in that order?
%%sql
SELECT dog_guid, subcategory_name,test_name
FROM reviews LIMIT 15;
Question: How would you retrieve 10 rows of data from the activity_type, created_at, and updated_at fields of the site_activities table, starting at row 50? What do you notice about the created_at and updated_at fields?
%%sql
SELECT activity_type, created_at,updated_at
FROM site_activities LIMIT 10 OFFSET 49
Question: How would you retrieve 20 rows of data from all the columns in the users table, starting from row 2000? What do you notice about the free_start_user field?
%%sql
SELECT *
FROM users LIMIT 1999, 20