.. _SQL:

******************
SQL
******************

Lab Materials
=============

This lab will use the following data:

**Rider**

    +-----------+----------+----------+
    | Name      | Age      | Gender   |
    +===========+==========+==========+
    | Lindsay   | 16       | Female   |
    +-----------+----------+----------+
    | Tom       | 21       | Male     |
    +-----------+----------+----------+
    | Eric      | 33       | Male     |
    +-----------+----------+----------+
    | Marco     | 13       | Male     |
    +-----------+----------+----------+
    | Kristopher| 45       | Male     |
    +-----------+----------+----------+
    | Kelly     | 21       | Female   |
    +-----------+----------+----------+
    | Mitchell  | 24       | Male     |
    +-----------+----------+----------+
    | Tammy     | 30       | Female   |
    +-----------+----------+----------+
    | Brad      | 18       | Male     |
    +-----------+----------+----------+

**Make**

    +-----------+----------+
    | Name      | Make     |
    +===========+==========+
    | Lindsay   | Honda    |
    +-----------+----------+
    | Lindsay   | Yamaha   |
    +-----------+----------+
    | Tom       | Honda    |
    +-----------+----------+
    | Eric      | Honda    |
    +-----------+----------+
    | Eric      | Yamaha   |
    +-----------+----------+
    | Marco     | Honda    |
    +-----------+----------+
    | Marco     | Yamaha   |
    +-----------+----------+
    | Marco     | Kawasaki |
    +-----------+----------+
    | Marco     | Suzuki   |
    +-----------+----------+
    | Marco     | KTM      |
    +-----------+----------+
    | Mitchell  | Honda    |
    +-----------+----------+
    | Mitchell  | Yamaha   |
    +-----------+----------+
    | Kelly     | Honda    |
    +-----------+----------+
    | Kristopher| Honda    |
    +-----------+----------+
    | Kristopher| Yamaha   |
    +-----------+----------+
    | Kristopher| Kawasaki |
    +-----------+----------+
    | Tammy     | Honda    |
    +-----------+----------+
    | Tammy     | Yamaha   |
    +-----------+----------+
    | Brad      | Honda    |
    +-----------+----------+
    | Brad      | Yamaha   |
    +-----------+----------+

**Track**

    +-------------------+----------+----------+
    | Track             | Type     | Bike     |
    +===================+==========+==========+
    | Carters Racetrack | dirt     | Honda    |
    +-------------------+----------+----------+
    | Carters Racetrack | dirt     | Yamaha   |
    +-------------------+----------+----------+
    | RaceTrack Empire  | asphalt  | Honda    |
    +-------------------+----------+----------+
    | RaceTrack Empire  | asphalt  | Kawasaki |
    +-------------------+----------+----------+
    | Dirt Roads        | dirt     | Honda    |
    +-------------------+----------+----------+
    | Dirt Roads        | dirt     | Kawasaki |
    +-------------------+----------+----------+
    | Dirt Roads        | dirt     | KTM      |
    +-------------------+----------+----------+
    | Dirt Roads        | dirt     | Suzuki   |
    +-------------------+----------+----------+
    | Speedy Pavement   | asphalt  | Honda    |
    +-------------------+----------+----------+
    | Speedy Pavement   | asphalt  | KTM      |
    +-------------------+----------+----------+
    | Speedy Pavement   | asphalt  | Yamaha   |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | Honda    |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | Yamaha   |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | Kawasaki |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | KTM      |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | Suzuki   |
    +-------------------+----------+----------+
    | Georgia ATV Trails| dirt     | Honda    |
    +-------------------+----------+----------+
    | Georgia ATV Trails| dirt     | KTM      |
    +-------------------+----------+----------+
    | Georgia ATV Trails| dirt     | Suzuki   |
    +-------------------+----------+----------+

Lab Content
===========

Objective
---------
Students use Python to interact with a relational database management system.

Procedure
---------

Connecting to a PostgreSQL server
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can connect to a PostgreSQL server using Python as follows::

        import psycopg2
        try:
            conn = psycopg2.connect("dbname=yourdb user=yourusername host=ipaddress password=yourdbpassword")
        except:
            print "I am unable to connect to the database."
        cur = conn.cursor()

Creating Tables
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can create tables using SQL as follows::

        cur.execute("""CREATE TABLE rider(name varchar, age integer, gender varchar);""")


   Create the tables for Make and Track.

Inserting Data
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can insert data using SQL as follows::

        cur.execute("""INSERT INTO rider (name, age, gender) VALUES ('Lindsay', 16, 'female'),
                    ('Tom', 21, 'male'),('Eric', 33, 'male'), ('Marco', 13, 'male'),
                    ('Kristopher', 45, 'male'), ('Kelly', 21, 'female'),('Mitchell', 24, 'male'),
                    ('Tammy', 30, 'female'), ('Brad', 18, 'male');""")

   Insert the data into the Make and Track tables



Query Data
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can query data using SQL as follows::

    #View all the data in the table
    cur.execute("""SELECT * FROM rider;""")
    rows = cur.fetchall()
    for row in rows:
        print row

    #To view only the names of the males older than 30
    cur.execute("SELECT name FROM rider WHERE age > 30 AND gender = 'male';""")
    rows = cur.fetchall()
    for row in rows:
        print row

    #To view the name and age of riders who are 21 or older or who are males
    cur.execute("""SELECT name, age FROM rider WHERE age >= 21 OR gender = 'male';""")
    rows = cur.fetchall()
    for row in rows:
        print row

    #To view the name and age of all riders who are not 21
    cur.execute("""SELECT name, age FROM rider WHERE age != 21;""")
    rows = cur.fetchall()
    for row in rows:
        print row

Performing a Cross Product
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can perform the cross product in a SQL query::

    #View all the data in the table
    cur.execute("""SELECT * FROM rider, make;""")
    rows = cur.fetchall()
    for row in rows:
        print row


Creating Inconsistency
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We update the data in the tables using SQL as follows::

    cur.execute("""UPDATE rider SET name='Sally' WHERE name = 'Lindsay';""")

#. Now examine rider and make together based on common columns::

    cur.execute("""SELECT * FROM rider NATURAL JOIN make;""")
    rows = cur.fetchall()
    for row in rows:
        print row


Notice Lindsay and Sally do not appear in the table. The data is inconsistent.


Dropping Tables
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can delete tables using SQL as follows::

        cur.execute("""DROP TABLE rider;""")
        cur.execute("""SELECT * FROM rider;""")

        #You should see an error message psycopg2.ProgrammingError: relation "rider" does not exist

Delete tables make and track


Using pgAdminIII
^^^^^^^^^^^^^^^^
#. Open PgAminIII from  Start menu > programs > PostgresSQL 9.1 > PgAdminIII
#. After PgAdminIII opens, you will see the following screen:

.. figure:: /_static/hws/SQL/SQL1.PNG
    :align: center
    :scale: 50%

#. To connect to a database, click the 'Add a connection to a server button'. A new window will display.
    Enter the name of the database, host, port, and password into the boxes shown below.

.. figure:: /_static/hws/SQL/SQL2.PNG
    :align: center
    :scale: 50%

#. Next we will create a table and add data to the table.
    Double click on your database name in the Object Browser. Click on the expand the Database with the plus symbol.
    click on your database name. Now you can click the SQL icon at the top on PgAdmin and a new window will opened.

.. figure:: /_static/hws/SQL/SQL3.PNG
    :align: center

#. Inside on the new SQL window, There is a panel titled SQL Editor where you will type your queries, an output panel
   that will display the queries, and a scratch pad where you can place queries in progress. At the end of the every
   SQL statement, there must be a ; or you will recieve an error in the output panel.
   Note: When you exit the SQL window, you are asked if you would like to save changes. Selecting yes will save the text
   in the SQL Editor.

.. figure:: /_static/hws/SQL/SQL4.PNG
    :align: center

#. After creating a few tables return to the main PgAdminIII window, extend your database,
   extend Schemas, extend public, and extend tables. The table you created should be there.
   You can now right click on the table and select view data > view top 100 rows a new
   window will open showing the table filled with the data you inserted.

.. figure:: /_static/hws/SQL/SQL5.PNG
    :align: center

References
----------
Update soon.


Metadata
========
| Title:SQL
| CreationDate:01/22/2013
| SoftwareUsed:PostgresSQL
| SoftwareVersion: 9.1
| Contributors: Zac Miller, Crystal Lyliston