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

1. 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

1. 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

1. 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

1. 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

1. 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

1. We update the data in the tables using SQL as follows:

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

2. 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

1. 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

1. Open PgAminIII from Start menu > programs > PostgresSQL 9.1 > PgAdminIII
2. After PgAdminIII opens, you will see the following screen:

1. 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.

1. 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.

1. 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.

1. 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.

References

Update soon.

Metadata

Title:SQL

CreationDate:01/22/2013

SoftwareUsed:PostgresSQL

SoftwareVersion: 9.1

Contributors: Zac Miller, Crystal Lyliston