SQL Starter Kit

SQL Starter Notes for Project 2

I write this document so you can read about the steps I took to get our Project 2 (Entertainment Planning) database ready for your use. As you learn SQL, you will be able to try out your SQL Commands via our class SQL Select Tool. From there, you can type in SQL SELECT statements against our class project database.

After using SSH (secure shell) software to connect to triton.cev.washington.edu (our class server) with my account, I created the database by using the CREATEDB command that comes as part of the Red Hat 8.0 operating system (the operating system came with PostGres 7.3 included). So, I typed createdb travel at a ssh prompt to create a database named travel. Once the database existed, I could use the psql interface to run SQL commands against our database. To get the psql interface pointed towards our database, I typed psql entertain at the ssh command prompt. The ssh console responded:

[bdc@triton]> psql entertain

Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: travel

entertain=>

I then ran the following CREATE TABLE and INSERT INTO commands at the psql prompt (psql stands for Postgres Structured Query Language which is an extension of the SQL specification. Note how all the commands handle the appropriate type for each data attribute:

CREATE TABLE Entertainer (
Entertainer_ID int,
Genre int,
Name text,
Home_town text,
Home_state text,
Phone_number text,
Web_site text,
Min_revenue int,
Target_age int,
Owner text);

INSERT INTO Entertainer VALUES (0, 0, 'Orange Camel', 'La Crosse', 'Wisconsin', '(608) 555-0697', 'http://www.orangecamel.com', 10000, 30, ' ');
INSERT INTO Entertainer VALUES (1, 1, 'Natalie Merchant', 'Jamestown', 'New York', '(212) 555-6997', 'http://www.nataliemerchant.com/', 40000, 30, ' ');

CREATE TABLE Venue (
Venue_ID int,
Name text,
Min_take int,
Home_town text,
Home_state text,
Capacity int,
Open_time int,
Close_time int,
Owner text);

INSERT INTO Venue VALUES(0, 'Melbourne Alley', 25000, 'Melbourne', 'Florida', 4445, 4, 12, ' ');
INSERT INTO Venue VALUES(1, 'The Stone Baloon', 14000, 'Newark', 'Delaware', 2800, 4, 2, ' ');

CREATE TABLE Attendee (
Attendee_ID int,
Age int,
Home_state text,
Yr_income real,
Times_attended int,
Gender int,
Genre int,
Owner text);

INSERT INTO Attendee VALUES (0, 30, 'Michigan', 29000.45, 14, 0, 1, ' ');
INSERT INTO Attendee VALUES (1, 41, 'Ohio', 77456.00, 7, 1, 1, ' ');

CREATE TABLE Show (
Show_ID int,
Entertainer_FK int,
Venue_FK int,
Revenue int,
Avg_age real,
Ticket_price real,
Date date,
Start_time int,
Attendance int,
Owner text);

INSERT INTO Show VALUES (0, 0, 0, 87668, 24.87, 40.0, '04-04-2003', 7, 2900);
INSERT INTO Show VALUES (1, 0, 1, 47266, 26.16, 40.0, '04-04-2003', 9, 1433);

CREATE TABLE Attendee_Rating (
A_rating_ID int,
Attendee_FK int,
Show_FK int,
Rating int,
Comments text,
Owner text);

INSERT INTO Attendee_Rating VALUES (0, 1, 0, 7, 'Not bad at all', ' ');
INSERT INTO Attendee_Rating VALUES (1, 1, 0, 9, 'Orange Camel Rocks!', ' ');

CREATE TABLE Entertainer_Rating (
E_rating_ID int,
Entertainer_FK int,
Show_FK int,
Rating int,
Comments text,
Owner text);

INSERT INTO Entertainer_Rating VALUES (0, 0, 0, 7, 'Thanks all', ' ');
INSERT INTO Entertainer_Rating VALUES (1, 1, 1, 9, 'Invite me Back, please', ' ');

CREATE TABLE Genre_Lookup (
Genre_ID int,
Genre text,
Owner text);

INSERT INTO Genre_Lookup VALUES (0, 'Hard Rock', ' ');
INSERT INTO Genre_Lookup VALUES (1, 'Alternative', ' ');

CREATE TABLE Generic (
Gen_ID int,
First text,
Second text,
Third text,
Fourth text,
Fifth int,
Sixth int,
Seventh int,
Eighth real,
Ninth real,
Tenth date,
Owner text);

Each time you interact with the entertainment planning database using the class SQL Select Tool, you ask the HTML form to call a Java Server Page that can dynamically connect to a Postgres database and run commands. Take a look at the Java servlet code. We will discuss it in class on May 19th:

SQL Java Server Page (JSP) Example

/*
 select.jsp
 author: Bruce Campbell - bdc@hitl.washington.edu
 */
 
<%@ page contentType="text/html;charset=ISO-8859-1" language="java" import="postgresql.*" %>

<HTML>
<HEAD>
<TITLE>DHSVM Web Interface - Main</TITLE>
<META content="text/html; charset=unicode" http-equiv=Content-Type>
</HEAD>
<BODY>

<%
    Class.forName("postgresql.Driver");
    java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:postgresql://triton.cev.washington.edu/entertain","postgres","si99raph");
    
    // create and execute the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String query = request.getParameter("query");
	String command = "SELECT " + query;
    String error="";
    String updateButtonText = "Run Query";
    String data="";

%>

<table>

<%
    try {
        rs = stmt.executeQuery(command);
   
		while(rs.next()) {
			int x=1;

%>

<tr>

<%

			while(x!=-1) {
				if(rs.getMetaData().getColumnCount()<x) {
					x=-1;
				} else {
					data = rs.getString(x);
					x++;

            // To get back an integer, define int val5 = Integer.parseInt(row[4])
%>

				    <td><b>| <%= data %></b></td>
<%
				}
            }
%>

<td> |</td></tr>

<%

        }
    } catch (Exception exp) {
        error = exp.toString();
    }
%>
</table>
<H3><%= error %></H3>
</BODY>
</html>

SQL Java Servlet Example

/*
 SQLSelect.jsp
 author: Bruce Campbell - bdc@hitl.washington.edu
 */

import java.io.*;
import java.util.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*; 
import java.util.Enumeration;

/**
 * An HTTP Servlet that responds to the GET and HEAD methods of the
 * HTTP protocol.  It returns an HTML document with results of the
 * processing.
 */
public class SQLServlet extends HttpServlet {

   public void doGet (HttpServletRequest request,
                      HttpServletResponse response)
   throws ServletException, IOException
   {
      // set content-type header before accessing Writer
      response.setContentType("text/html");
      PrintWriter out = response.getWriter();

      //Get the identifier of the menu item to display
      String all = request.getParameter("All");
      String Title = "SELECT " + all;

      out.println("<HTML>" +
                  "<HEAD><TITLE>SQL Result Set</TITLE></HEAD>" +
                  "<BODY bgcolor=#ffff99 background=\"http://www.cev.washington.edu/learning_center/site_graphics/l_c_background.gif\">" +
                  "<H3>" + Title + "</H3><TABLE>");

      try {
         Class.forName("postgresql.Driver");
         Connection con = DriverManager.getConnection("jdbc:postgresql://sambucus.cev.washington.edu/travel","postgres","password");

         Statement st = con.createStatement();

         ResultSet rs = st.executeQuery(Title);

         while(rs.next()) {
             int x=1;
             out.println("<tr>");
             while(x!=-1) {
                 if(rs.getMetaData().getColumnCount()<x) {
                     x=-1;
                 } else {
                     out.println("<td>|" + rs.getString(x) + "</td>");
                     x++;
                 }
             }
             out.println("</tr>");
         }
         out.println("</TABLE>");

         rs.close();
         st.close();
         con.close();

         out.println("</BODY></HTML>");

      } catch (java.lang.Exception ex) {
         if (ex != null) {
            out.println("Exception: " + ex.getMessage() + " from command.");
         } else {
            out.println("Null exception from command.");
         }
      }
   }

   public String getServletInfo() {
      return "The SQLServlet allows for Select statements against the travel database.";
   }
}