Skip to Main Content

Indiana University Indiana University


Archive for the 'SQL Programming' Category

Using SQL: Data Retrieval in Self-Study Mode

This article contains information about how to configure your personal computer to use these materials at your own pace. All you will need is access to the internet.

First, let’s launch Aqua Data Studio. We will be using IUAnyWare. Follow the instructions here to get logged in and the Citrix Client installed.

Once you are logged in and have the Citrix Client installed, all we have to do is launch the application. Aqua Data Studio is located inside the Databases folder.

Once you have clicked on Aqua Data Studio, you can pick up where you left off in the materials.

Windows: Install and Configure WAMP for PHP Development

The Apache web server software application, MySQL database, and PHP interpreter are separate modules that work together to allow for testing of a dynamic PHP web site. Windows has none of these modules built-in, and so they must all be installed.

While you could install them separately, it is easiest to download and install them from a single bundled package. There are several companies that build these packages, and so there are certainly a few to choose from: XAMPP, AMPSS, and WampServer being a few popular options.

Today we will be installing WampServer, because it is among the fastest and easiest solutions.

Downloading WampServer

WampServer can be downloaded for free on the company’s web site.

  1. To get to the WampServer web site, go to www.wampserver.com/en in an internet browser.
  2. NOTE: If you didn’t type "/en" at the end of the web address, then you probably landed on the French version of the site. Click "English" in the top-right corner.

    You are now on the WampServer web site.

  3. To see the different available download options, scroll down to the Downloads section.
  4. You should see five different download options (at the time of this article’s publication). To narrow down which options to choose from, first we must know if our installation of Windows is 64-bit or 32-bit.

  5. To check if your installation of Windows is 64-bit or 32-bit, press the Windows key on the keyboard, and type msinfo32, then press enter.
  6. The System Information window should appear. Under "System Type", which is located a few rows from the top, the value should read as either "x64-based PC" or "x86-based PC."

    If you have an x64-based PC, you will choose one of the WampServer downloads that is labeled "64 bits"; with an x86-based PC, go with "32 bits."

    Now we have to choose the version of PHP and Apache, the options being either 5.3 or 5.4, and 2.2 or 2.4, respectively (at the time of this article’s publication). IU’s webserve server, for which different IU organizations and departments are hosted, has PHP version 5.2 installed (as of this article’s publication), and so we will select the 5.3 WampServer option.

  7. To select WampServer with PHP version 5.3, click either "WAMPSERVER (64 BITS…" or "WAMPSERVER (32 BITS…" depending on if you have an x64-based PC or x86-based PC, as described above.
  8. You should see a "Download WampServer" pop-up warning you that you must have the appropriate Visual C++ package installed on your computer in order for WampServer to work. You may or may not already have this installed.

    In order to verify this information, we can download and run the installer from the link provided.

    WampServer download warning pop-up

  9. To go to the Visual C++ download page, click either the x86 or x64 link, corresponding with your version of Windows.
  10. You should now be on Microsoft’s official download page for this package.

  11. In order to download the Visual C++ installer, click "DOWNLOAD."
  12. The file should begin downloading automatically.

  13. Once the file is downloaded, run the installer.
  14. The installer window should appear. If it is prompting you to either "Repair" or "Remove" Visual C++, then that means that you already have it installed, and you can just simply cancel out the window. Otherwise, go ahead and go through the install wizard, clicking "Next" at each page.

    We are now ensured that Visual C++ is installed, and so we can proceed with downloading WampServer.

  15. Go back to the WampServer page by clicking back on the browser window twice, and if necessary, click on the appropriate WampServer download link again.
  16. The warning pop-up should re-appear.

  17. To go to the WampServer download page, this time click the "you can download it directly" link on the "Download Wampserver" pop-up, shown in the image above.
  18. You will be taken to a page that may have a short countdown timer before your download automatically begins.

  19. Once the WampServer installer file downloads, run it.
  20. The installation wizard should open.

    WampServer installer wizard first page

    The window indicates what version of Apache, MySQL, and PHP will be installed, as well as a few other various applications that may assist you in your future web development.

    We will continue through the installation wizard.

  21. Click "Next" past this wizard’s start page, and license agreement page.
  22. You should now be on a page which allows you to select where WampServer will be installed. This is important. This directory will serve as the primary default location for your web sites. Thus, prevent hurdles to jump over in the future, you should choose a convenient location on one of your storage drive to hold your web files.

    NOTE: Make sure there are no spaces in any of the directories in the path you choose.

  23. Once you have chosen a path, click "Next." Check whether or not you want a "Quick Launch" or Desktop icon, and click "Next," and then click "Install."
  24. WampServer will now install. After it finishes, the installer will ask which web browser you want WampServer to default to. This choice dictates which browser opens when you tab through WampServer’s configuration options in the future.

    Keep in mind that whatever you choose here will not affect which browser can test your web site. You will be able to use whatever browser you like, no matter what you choose.

    The default option is Internet Explorer.

  25. Either simply click "Open" to accept the default browser (Internet Explorer), or locate the executable file for a different browser, and click "Open."
  26. WampServer will now ask you about the default PHP email. This allows you to set up which email address is used for various PHP tasks, for example, where a contact form hosted on your web site will be sent. However, in order for this to work, you’ll have to have an email server installed, which neither Windows nor WampServer comes with, or be using an email service that has one available.

    We will be skipping this today, as it is unnecessary to start testing PHP sites.

  27. To finish the installer and launch WampServer, click "Next," and click "Finish," keeping the "Launch WampServer…" checkbox selected.

WampServer should now be running.

Using WampServer to test a PHP site

If all went well, WampServer should be running. In order to verify this, look for a little WampServer ‘W‘ icon on your Windows taskbar on the right.

'w' icon for WampServer in taskbar.

This icon may appear either green, red, or orange. Green indicates that the server is online. Red indicates that the server is not online. Orange means that the server is online, but there is a problem keeping it from running any sites. If you ever get the orange icon, it is very likely a "port" issue.

NOTE: WampServer uses "port 80" by default in order to connect. So does Skype. If Skype is running, then you will likely run into this orange icon problem. The easy solution is to quit Skype, "Restart All Services" in the WampServer menu, and re-launch Skype, if desired. You can disable Skype from using port 80 by default in the Skype options.

Let’s test WampServer now. First we will activate WampServer’s menu.

  1. To open WampServer’s menu, click the ‘W‘ icon in the taskbar.
  2. A fly-out menu should appear.

    Fly-out menu for WampServer from taskbar icon.

    At the bottom of the menu, you should see "Start All Services," "Stop All Services," and "Restart All Services." If the server is offline (red ‘W’ icon), you can click "Start All Services." to bring WampServer online, making the taskbar icon go green.

    NOTE: If you make any changes to any configuration files associated with PHP, Apache, MySQL, or any other modules or plugins, you will have to click "Restart All Services" in order for the changes to take effect.

  3. To test if WampServer is working, click "Localhost" at the top of the WampServer menu.
  4. The default internet browser you chose earlier should launch or open a new tab, with the address being "localhost." localhost is the default alias for the root of the local server that WampServer is hosting. If all went well, you should see something like this:

    Successful localhost WampServer page.

    If your browser displays something like what the image above shows, then congratulations, WampServer is running successfully, and you can now start creating and editing PHP sites.

    NOTE: If the browser throws an error saying that it cannot find the resource, make sure that WampServer is running. Click "Start All Services" to make sure.

    If the browser says something else, like “forbidden”, then it’s possible that it is an IPv6-related issue. Try the WampServer localhost actual address, "127.0.0.1" in the address bar of the web browser instead, as a simple solution.

    Now that we have verified that WampServer is up and running, let’s create a test PHP document.

  5. Open Notepad, or other text editor of your choosing.
  6. In the text editor, type <?php phpinfo(); ?>
  7. To save this as a PHP file, click File, click Save As…
  8. The root directory where localhost is pointing is in the "www" directory of WampServer’s install location. We will now create a new sub directory within that root, and save our file there.

  9. Navigate to the directory you installed WampServer (default is C:\wamp), double-click the www directory. Click "New folder" and name it "testsite."
  10. To save our file, in the "File name" field, type " index.php, and click "Save."
  11. We now have a php file in a sub-directory of our root called index.php. Let’s test it in a browser.

  12. To test the newly-created PHP file, in a web browser, go to the address: "localhost/testsite"
  13. The browser should now execute the phpinfo() function you wrote, and display the configuration of Apache, MySQL, and PHP, that WampServer has set up.

    NOTE: You didn’t have to type index.php into the address because that file name is configured as a "default" home page file name.

Congratulations, you are now ready to start testing your own PHP web sites! Remember to create a different sub-folder of the www directory to serve as the root for any web site you wish to create or work on. For example, you would create a PHPBA folder within WampServer’s www folder to serve as the root directory for the PHP: The Basics class.

Good luck with your PHP sites!

PHP Workshops: Install and Use PHP Locally

To develop database-driven PHP applications, we need three things, the Apache web server, the PHP processor, and MySQL. This tutorial will walk you through all of the installation and setup that you need to do on your own machine to have a local instance of a Apache/MySQL/PHP (AMP) environment.

The instructions below will walk you through the setup you need for each of our PHP workshops, starting with PHP: The Basics.

At the time of writing, PHP: The Basics is the only PHP workshop offered by IT Training.

PHP: The Basics

The following links contain step-by-step instructions on configuring XAMP for the PHP: The Basics workshop.

Macintosh OS: Install and Configure MAMP for PHP Development

If you are planning on developing PHP applications on Mac OS X, you’re in luck! Most of the tools come built-in to the operating system; all we need to do is activate them.

This tutorial is written for the most recent (at time of writing) version of Mac OS X, 10.8 Lion. The instructions listed here will work on most of the recent versions of Mac OS X. If you need help with another OS version, leave a comment on this post.

This instruction set is also focused primarily on the setup needed to complete PHP: The Basics. Specifically, this post will not detail what needs to be done to set up MySQL and phpMyAdmin to administer a MySQL database. Instructions for that topic will be included in another post.

Configuring Apache for Mac OS X 10.8 Lion

Let’s start with Apache, the web server. This is built in to the operating system, but it’s not accessible by any of the graphical interfaces available. Let’s begin by launching Terminal.

  1. To launch Terminal,
    Press Command – Space,
    Type Terminal,
    Press Return

    This will launch Terminal to your home directory:

  2. To start Apache, type: sudo apachectl start
    Press Return

    Apache will start.

Other Useful Apache Commands

Now that Apache has started, let’s take a moment to review some other useful Terminal commands for Apache.

  • sudo apachectl stop — stops the Apache service
  • sudo apachectl graceful — restarts the Apache service
  • httpd -v — shows the current version of Apache (Apache 2.2.22 comes installed with Mac OS X 10.8 Lion)

These commands will be useful throughout your time working with apache. Let’s test that Apache has started.

  1. To see if Apache has started,
    Launch a Web Browser,
    In the Address bar, type: localhost,
    Press Return

    If apache has started, you should see a web page that says “It works!

    By default, this will make your document root be /Library/WebServer/Documents. When we navigate to http://localhost, the files in this directory are displayed. This is not a good place to do our work since it’s in the root level of the operating system. We will create a folder inside our own user directory that will contain all of our sites. This will be referred as User Root.

    To begin this process, let’s set up a folder called Sites in our user directory.

  2. To make the appropriate directory, in Therminal, type:
    mkdir /Users/username/Sites

    Where username is replaced with the short username of your account. If you do not know your short username, in Terminal, type: whoami and press return.Now that we have a User Root directory for all of our sites, let’s tell Apache where to find them. To do this, we need to create a file called username.conf and add some XML to that file.

  3. To create username.conf, in terminal, type:
    sudo nano /etc/apache2/users/username.conf

    Replacing username with your short username.The nano editor launches with a new file called username.conf. This is where we will add our XML.

  4. To add the XML needed to configure the User Root, type:
    <Directory "/Users/username/Sites/">
    Options Indexes MultiViews
    AllowOverride All
    Order allow,deny
    Allow from all
    </Directory>

    Again, replacing username with your short username.

  5. To save the file, press:
    Ctrl – O, Return, Ctrl – X

    Before we are finished, let’s make sure the file has the correct permissions.

  6. To change the permissions of the file, in Terminal, type:

    sudo chmod 644 /etc/apache2/users/username.conf

    Replacing username with your short username.

    Apache is now configured to look in /Users/username/Sites when we browse to http://localhost/~username. Let’s restart Apache and configure PHP.

  7. To restart apache, in Terminal, type:
    sudo apachectl graceful

Configuring PHP

Now that Apache is running, we need to start PHP. This is done by uncommenting a line in the Apache configuration file located at /etc/apache2/httpd.conf. Let’s do that now.

  1. To open the httpd.conf file, in Terminal, type:
    sudo nano /etc/apache2/httpd.conf
    Press Return

    Now, we will use nano’s built-in search functionality to find the line that talks about PHP.

  2. To search for PHP, in nano, type:
    Ctrl – W, php, Return

    This will locate the apropriate line.

  3. To uncomment the line,
    Remove the octothorp (#) from the beginning of the line

  4. To save the file and continue, press:
    Ctrl – O, Return, Ctrl – X

    Now that the line is uncommented, let’s restart Apache to have the changes take affect.

  5. To restart Apache, in Terminal, type:
    sudo apachectl graceful
    Press Return

Testing PHP

Now that we have PHP configured, let’s test the installation by creating a php document that contains the phpinfo(); function in our User Root directory.

  1. To create the PHP information file, in Terminal, type:
    nano /Users/username/Sites/phpinfo.php
    Press Return

    Again, replace username with your short username.

    This will create the file, now let’s add the code.

  2. To add the PHP code, type:
    <?php phpinfo(); ?>
  3. To save the file, press:
    Ctrl – O, Return, Ctrl – X

    This saves the file. We are now ready to test the connection.

  4. To test the PHP configuration, in a Web Browser, navigate to:
    http://localhost/~username/phpinfo.php

    You should now see the PHP installation information.

This concludes what is needed to move through PHP: The Basics in self-study mode. The last bit of information here is a list of recommendations on how to set up your Sites folder to complete the workshop.

  • Create a sub-folder in your User Root directory named PHPBA. This will be the Local Site Folder when configuring your site in Dreamweaver.
  • Since you are working on your local machine, you do not have to upload files using the Ctrl – Shift – U keyboard shortcut in Dreamweaver. Simply save and refresh the web browser.
  • To make sure all of your exercise files are ready to go, place the contents of the PHPBA folder in your newly created PHPBA folder inside your User Root directory.

That’s it! Happy PHP’ing!

How To Retrieve Top (n) Rows from a Database

In SQL:Data Retrieval, one of the topics we cover is how to write a database query to answer questions like, “How many students were enrolled each term in Biology 101 from 1997-2008″ or “What is the average salary for the employees who work in the Marketing department?” These types of questions can be answered by using SQL functions. A function is a small bit of code that can accept a value, do something with it (like a calculation), and then return a new result.  In SQL, there are functions like AVG (to average) and COUNT (for, um, counting.  I bet you guessed that, though, right? :-)

Some functions, like AVG and COUNT, are available in almost every database product. Other functions are database-specific, made available by the database vendor to make your life easier (and make the competitors’ products less enticing as well). An example of one of these functions is the TOP function in Microsoft SQL Server.  Here’s a faux query, written for Microsoft SQL Server, to find the top 5 students in a class by their course grade:

SELECT TOP 5 (studentID), grade
FROM students
ORDER BY grade DESC

Theoretically, this query would return a list of 5 student IDs and their grades from the table named Students. The ORDER BY clause at the end of the statement ensures that, before the database picks the 5 at the top of the list, the list is sorted according to grade, with the best grades on top. (In other words, the DESC keyword tells the database to sort records in descending order.)

Unfortunately for people working in other databases, like Oracle or MySQL, the TOP function is specific to Microsoft SQL Server.  So, how can we find the top 5 students in other databases?

In MySQL, an equivalent statement would be:

SELECT studentID, grade
FROM students
ORDER BY grade DESC
LIMIT 0,5

Here, the LIMIT command tells the database to display 5 records starting from the 1st record (0), after sorting the records in the table according to the grades.  To display 15 records starting with the 5th record, the last line of the previous statement would be LIMIT 4,15.

Unfortunately, the solution for Oracle is not nearly as simple:

SELECT studentID, grade
FROM (
SELECT studentID, grade, RANK() OVER (ORDER BY grade DESC) rankByGrade
FROM students
)
WHERE
rankByGrade <= 5

Whoa!  What's going on here?  This is a demonstration of a subquery. A subquery is simply a SELECT statement nested inside another SQL statement.  In the above example, the interior SELECT statement (the one that starts on the 3rd line) will be executed first. Any results from the subquery will essentially be treated as a temporary table, existing for the duration of the statement's execution only. This temporary table is used by the exterior query (the SELECT studentID, grade part) as its source of data.

This particular subquery  is using an Oracle function called RANK in association with (ORDER BY grade DESC) to create a column that provides a ranking based on the grade value, from highest to lowest. The highest grade gets a rank of 1, the next highest grade gets a rank of 2, and so on.  In other words, if we were to run just the subquery by itself, the results might look something like:

studentID  grade    rankByGrade
---------  -----    ------
373478     100       1
938937     98.6      2
671037     98.2      3
565422     96.8      4

These results, including the derived rankByGrade number, are then used by the exterior query, which essentially is:
SELECT studentID, grade
FROM [the results of the subquery]
WHERE rankByGrade <= 5

Thus, the exterior query returns only 5 records, which correspond to the records that have a rankByGrade of 5 or less, or, in our case, the top 5 students.

Thanks to Mike Halla, database guru, for providing a sample Oracle query to address this issue.

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.