Out: June 3, 2014
Due: June 10, 2014 (start of class)
Reading: Ch 16 (SQL- pp298-322 only, summary on pp343),Ch 18 (PHP) of Meloni
Lab 5 - Connecting SQL to a Web Page
Part I: Practice SQL (1 mark each/10 total)
You’ll need your ubuntu installation of mysql to do the following exercises.
On your ubuntu installation of mysql, enter the following commands and submit the answers to the attached questions. Cut and pasting commands will likely fail as this document uses Windows quotations and newlines, as opposed to pure ascii characters. Focussing on the syntax as you type will help you learn SQL commands.
Examples of similar statements can be found in the first part of Ch 16 of the Meloni textbook. Do not hand in the questions- a 2 mark penalty will apply if you do. Do not hand in the output unless requested – simply answer the question about the output, which will often just be the number of records returned along with a simple explanation of the effect.
When you did yourinitial LAMP install you ran a script employee.sql. This created a database schema called .... To start your mysql session type in:
mysql -u yourusername -p yourDatabaseName #it then prompts for your password
Note: Based on the instructions given for the LAMP install in NEST401 yourdatabasename is demo1 and yourusername is root. If you’ve forgotten the password you used to do your LAMP install of mysql, you’ll need to reinstall mysql to reset it, then go thru the step in the NEST404 lab to run the employee script again.
(1 mark each)
1)Simple ‘projections’ – enter as single SQL statements
select NAME, DATEHIRED,SALARY from employee;
select DATEHIRED, SALARY, NAME from employee;
Note that the fields can be selected in any order.
How many records are displayed in each case?
2)Showing calculations – run each statement individually.
select NAME, SALARY from employee;
select name, SALARY, SALARY*1.03 as ‘2013 SALARY’ from employee;
select name as ‘Nom’, EMPID as ‘Identification’ from employee;
What is effect of ASas an operator? What happens to the title of a field when a calculation occurs both with and without theASclause (ie: remove as ‘2013 salary’ and note the difference?
Be careful using the same quotes as above – single and double quotes have different meanings. Also – you should be entering these statements by typing them in – if you use cut and paste at some point you should note that word processing packages use a different character code for both quote symbols than programmer’s editors and the statements will not work.
3)select distinct POSITION from employee;
select distinct BOSSID from employee;
select distinct POSITION, BOSSID from employee;
How many records were displayed in each case?
Why were the number of records displayed here less than in Q2?
Describe what the distinct operator does.
4)First use of the WHERE clause:
Select BOSSID , EMPID, POSITION, SALARY from employee
where position='Manager';
Select * from employee where SALARY<30000.00;
How many records were selected in each case?
5)More queries using the WHERE clause
select NAME, SALARY from employee
where SALARY> 38000 and SALARY <54000;
select NAME,SALARY from employee
where SALARY between 38000 and 54000;
How many records were selected by each query? What is the difference between the two queries?
6)Issue the following command:
update employee set SALARY=SALARY+100
where CANHIRE='Y' and POSITION> 'Manager';
Update employee set SALARY=SALARY-1,
BOSSID=’EMP2006’,
POSITION=’Linux Support’
where EMPID=’EMP2005’;
How many records were affected in each case? Identify using the employee name which employee records were updated.
7)Issue the following commands:
select sum(SALARY), avg(SALARY), min(SALARY), max(SALARY), count(*)
from employee;
select sum(SALARY), avg(SALARY), min(SALARY), max(SALARY), deptcode, count(*)
from employee
group by deptcode;
select sum(SALARY), avg(SALARY), min(SALARY), max(SALARY), BOSSID, count(*)
from employee
group by BOSSID;
How many records were generated in each case? What does the group by clause do? What happened when group by was not used?
8)Issue the following command:
select BOSSID, EMPID, SALARY
from employee
where BOSSID> 'EMP007'
order by BOSSID, SALARY desc;
How many records where generated? Describe how the records were sorted.
Review question 7 – in both cases did the group by clause sort the results?
9)Try inserting a new employee with the same empid as an existing record. You will need to check the table definition in order to apply the right data types for each field. Show the syntax of your insert statement and describe what happens. (You inserted a value into myTable in the last lab and we went over the syntax of the insert statement in the class PowerPoint presentation on databases. You can find examples in Ch 16 of Meloni.)
10)Issue the following command to delete some records:
delete from employee where bossid like ‘%10%’;
How many records were deleted.
11)Download and run the SQL script purchases.sql from the course website and read the code by running the command: source purchases.sql (The file must be in the same directory that your started mysql from… or you have to include a path to purchases.sql.
What the script does is create a table of purchases that can be linked to the employee table. It also illustrates a number of different types of business rules that can be embedded in the database engine.
Issue the following commands that links the 2 tables together based on the common field empid.
select name,whenPurchased,description,quantity, unitCost
from employee inner join purchase using(empid);
an equivalent statement is:
select name,whenPurchased,description,quantity, unitCost
from employee inner join purchase on(employee.empid=purchase.empid);
How many rows are returned?
Verify that the purchases are in fact linked together by comparing the records in each table. Are there any extra or missing connections?
Repeat the query without the on or using clause:
select name,whenPurchased,description,quantity, unitCost “Total”
from employee inner join purchase;
How many records are returned? Explain the results.
12)The following query will generate a summary report of purchases:
select name, count(*) as " # of Purchases ", sum(quantity*unitCost) “Total”
from employee inner join purchase using(empid)
group by name
order by 2 desc;
How many records are returned? Briefly summarize what the statement is doing.
13) Now try the following:
select name, count(*) as " # of Purchases ", sum(quantity*unitCost)
from employee left join purchase using(empid)
group by name
order by 2;
Describe the difference(s) in behaviour between this and the previous example.
Part II – Creating a Custom Apache Log (5 marks)
In class we created a custom log by doing the following:
- In /etc/apache2/apache2.conf we defined a custom log format by adding a line:
LogFormat “Hello,%{%B %H:%M}t,%A” formatName
The specific meaning of the log format codes can be found here:
Our example only used 2 formats: %t for time, and %A – the remote web address.
The specific date and time format codes are inserted inside the %t format using the codes used for the Unixdate command. In the above case we output the Month, followed by the hour and minute. - We then associated the log format with a particular file by adding the following line to our default site file in sites-enabled:
CustomLog /var/log/special.log formatName
Note that we put the log file where we wanted to, and not where the other apache logs are situated. - After making these changes, restart Apache for the log to take effect. Restarting Apache automatically creates an empty log file.
- Assigned Work:
Create and test custom log with the following fields: - The day of the week
- The name of the web page
- The number of seconds since the epoch (Use the %s code from the date command)
- The method (get or put)
- The total number of bytes sent and received
- The referrer and the user agent (see the standard log formats)
To hand in: The LogFormat line used to create the format
The CustomLog line
3 output lines of your log showing 3 different files
Part III– Connecting HTML to MySQL through PHP (3 marks each/12 total)
To debug php at the command line:
- Create a file php.ini and in it place the one line directive
display_errors=on - At the command line run the command:
php -l yourFile.php #That’s dash lower case el, not 1. - Test your program for syntax errors before bringing yourphp file as a web page.
1)Read through the PHP MySQL tutorial starting at:
Note that you will be copying and modifying existing code. Read the examples and create web pages to do the following:
a)Open and close a connection to your database. Check with a bad username or password and report on the message produced, and then try using the username and password you use to connect to mysql. The code (2nd page of tutorial) (3 marks)
b)Create a web page form in your ubunto/var/www directory (For mac users, use: ~/Site) that sends data for each of the fields in the employee table based on the example in the 4th page of the tutorial (3 marks). Hand in a listing of the page.
c)Write a PHP ‘action’ page that inserts the employee data into the employee table. The example to work from is also on the 4th page of the tutorial. . Keep in mind that EMPIDs must be unique, when you insert a new employee you cannot use the EMPID of any previous employee. (ref: 4th page of the tutorial)
Use a select statement to verify that the data actually got inserted.
Report on whether on not the insert actually took place. (3 marks)
d)The example on the 5th page of the tutorial shows you how to display the contents of a table on a web page. Write a PHP web page that displays the contents of the employee table as an HTML table. (3 marks)
Hand in a printout of the code for each of the above web pages. A demo will be requested in the lab on the week of March 31 where you will demonstrate that your coded can insert a new record into the database. Should you not be preparedwhen called upon to give a demo without coaching your mark on this part of the assignment will be reduced by 20%, so make sure that you practice before hand.