https://www.vistrails.org//index.php?title=Big_Data_Lab_SQL&feed=atom&action=historyBig Data Lab SQL - Revision history2024-03-28T22:16:49ZRevision history for this page on the wikiMediaWiki 1.36.2https://www.vistrails.org//index.php?title=Big_Data_Lab_SQL&diff=8313&oldid=prevJuliana: Created page with 'Today, we will work on SQL queries. Before the class, please download mySQL from from http://dev.mysql.com/downloads. There are many different versions, select MySQL Community S…'2014-10-03T17:04:50Z<p>Created page with 'Today, we will work on SQL queries. Before the class, please download mySQL from from http://dev.mysql.com/downloads. There are many different versions, select MySQL Community S…'</p>
<p><b>New page</b></p><div>Today, we will work on SQL queries.<br />
<br />
Before the class, please download mySQL from from http://dev.mysql.com/downloads.<br />
There are many different versions, select MySQL Community Server.<br />
<br />
== Installing and starting mySQL ==<br />
<br />
mySQL has an extensive documentation, including installation instructions for different OSes,<br />
for example, MacOS: https://dev.mysql.com/doc/refman/5.7/en/macosx-installation.html<br />
and for Windows: http://www.mysql.com/why-mysql/windows/<br />
<br />
''Several students had trouble installing the MySQL Startup Item, do not install this option!''<br />
<br />
I run mySQL on MacOS, so here's what I did after I installed the system to 'start' the database:<br />
<code><br />
shell> cd /usr/local/mysql<br />
shell> sudo ./bin/mysqld_safe<br />
(ENTER YOUR PASSWORD, IF NECESSARY)<br />
(PRESS CONTROL-Z)<br />
shell> bg<br />
(PRESS CONTROL-D OR ENTER "EXIT" TO EXIT THE SHELL)<br />
</code><br />
<br />
You can also use the shortcut command: mysqlup<br />
<br />
To connect to the MySQL server, on a terminal, invoke /usr/local/mysql/bin/mysql.<br />
<br />
You can add aliases to your shell's resource file to make it easier to access commonly used programs such as `mysql' and `mysqladmin' from the command line. I have added the following 2 lines to my .bashrc:<br />
<br />
alias mysql=/usr/local/mysql/bin/mysql<br />
alias mysqladmin=/usr/local/mysql/bin/mysqladmin<br />
<br />
You can also modified my PATH environment variable to include /usr/local/mysql/bin:<br />
<br />
export PATH=$PATH:./:/usr/local/mysql/bin:/usr/local/mysql/bin:/usr/local/hadoop-1.2.1/bin:/usr/local/pig-0.12.0/bin<br />
<br />
== Our running example ==<br />
<br />
We will work with three tables. Here are the table definitions:<br />
<br />
<code><br />
create table Sailors(<br />
sid int PRIMARY KEY,<br />
sname varchar(30),<br />
rating int,<br />
age int);<br />
<br />
create table Reserves(<br />
sid int,<br />
bid int,<br />
day date,<br />
PRIMARY KEY (sid, bid, day));<br />
<br />
create table Boats(<br />
bid int PRIMARY KEY,<br />
bname char(20),<br />
color char(10));<br />
<br />
</code><br />
<br />
You can download the sample file with these statements as well as some sample data from http://vgc.poly.edu/~juliana/courses/BigData2014/Lectures/Lab-SQL/sailors-mysql.sql<br />
<br />
== SQL Cheat Sheet ==<br />
<br />
<code><br />
<br />
SELECT AcctNo, Amount<br />
FROM ATMWithdrawal<br />
WHERE Amount < 50;<br />
<br />
</code> <br />
<br />
A SQL query is evaluated as follows: <br />
* First the FROM clause tells us the input tables<br />
* Second, the WHERE clause is evaluated for all possible combinations from the input tables<br />
* Third, the SELECT clause tells us which attributes to keep in the query answer</div>Juliana