1. IMPORTANT: PLEASE USE SEPARATE MACHINE DURING
To avoid interference to/from other students, at any time one machine can be used by at most one student for project.
2. IMPORTANT: PLEASE SHUTDOWN TOMCAT SERVER BEFORE
CLOSING NETBEANS IDE
To avoid interference with others, please shutdown Tomcat Server when you finish working.
3. SQL*PLUS username & password
Q: I am able to type "sqlplus" on the command line then it asks for username and password. What should they be?
A: The username is the same as your UNIX userid and password is your SSN without dash or space.
Q: How do I reset my sqlplus password?
A: Use "PASSW" or "PASSWORD" commands in sqlplus.
4. Before shutdown Netbeans IDE, please first shutdown tomcat server
Q: I cannot shutdown Netbeans IDE. What should I do?
A: Check whether you
shutdown Tomcat Server.
4. Before run Netbeans IDE, please setup environment
Q: I cannot run Netbeans IDE. What should I do?
A: Check whether you setup environment "CATALINA_HOME", "CATALINA_BASE" and "JAVA_HOME" correctly.
5. Q: How can I include two foreign keys referencing to the same attribute of the same table.
A: Here is how you
can do it:
CREATE TABLE t_stupid (
FOREIGN KEY(a) REFERENCES cia(population),
FOREIGN KEY(b) REFERENCES cia(population));
In this example both attributes "a" and "b" references "population" attribute in "cia" table.
6. Q: How can I dump sql output to a file?
A: You can dump your sql outputs using the following command:
sqlplus <user_name>/<password> @<sql_script> > <output_file>
don't forget "quit" at the end of your scripts...
7. Q: I was trying to write some of the SQL statements from the book but I am running into a weird error. Whenever I try to execute a statement like the following:
FROM (SELECT D1.managerid, SUM(D1.budget) AS totbudget,
FROM Dept D1
GROUP BY D1.managerid)AS Temp
A: There are
some issues with Oracle DBMS so you should change this SQL as:
FROM (SELECT D1.managerid, SUM(D1.budget) AS totbudget (!!!comma removed!!!)
GROUP BY D1.managerid) (!!!"AS" Removed!!!) Temp WHERE Temp.totbudget>5000000;
1) instead of EXCEPT you should use MINUS
2) HAVING ANY and HAVING EVERY is not accepted in Oracle. So you need to find other ways to write these queries.
8. Q: How to combine Java code into the JSP files?
A: A JSP file is a html file containing some java code with the tag <% %>. Therefore, we you modify your java code into a JSP file just put the Java code in the section of <% %>.
9. Q: We design our interface using the DreamRiver. How can we combine the JSP files with the interface?
A: You just copy the JSP source code to the html file and use the tag of <% %>.
10.Q: How can we display all categories in the database?
A: What you have to do is to trace all the tree of the categories.
11.Q: How can we transfer parameters between JSP files without using html forms?
A: You can use session as follows:
_• Setting a session attribute:
• Reading a session attribute:
String username = (String) session.getAttribute("username");
12.Q: I was trying to write the following SQL statements
SELECT Temp.did, Temp.weird
_FROM _ (
SELECT D.did, AVG(E.age) as weird
FROM Dept D, Works W, Emp E
_WHERE _ E.eid=W.eid
_AND _ W.did=D.did
_GROUP BY D.did
_WHERE Temp.weird = (SELECT MAX(Temp.weird) FROM Temp);
_Unfortunatly, it gives me the following error: WHERE Temp.weird = (SELECT MAX(Temp.weird) FROM Temp)
_ _ *
ERROR at line 9: ORA-00942: table or view does not exist
A: Temp doesn't exist in (SELECT MAX(Temp.weird) FROM Temp). Maybe you can try to create another view for Temp.
13.Q: For problem 2 of HW3, I want to structure my query so that it will calculate the average age of employees per department._ I am not sure how to do this outside of a query for each department. Is it possible to do this within one query? And if so, how?
A: It is possible to do this within one query. First, you have to find the highest average age of employees. And then you can find the company whose average age of employee is equal to the highest average age of employees.
14.How to create custom functions in Oracle?
A: The following is provided by Megan Greensfelder:
CREATE or REPLACE FUNCTION getCurrentPrice(AID IN INT)
RETURN REAL IS
cp := -1;
SELECT COUNT(*) INTO bidCount FROM bid WHERE bid_item = AID;
IF bidcount = 0 THEN
cp := 0;
SELECT MAX(bid_amount) INTO highBid FROM bid WHERE bid_item = AID;
IF bidcount = 1 THEN
lowbid := highbid;
SELECT MAX(bid_amount) * 1.05 INTO lowBid FROM bid
_WHERE bid_item = AID AND bid_amount < highBid;
IF highBid < lowBid THEN
cp := highBid;
cp := lowBid;
After the above code is run in sqlplus (and the / at the end is important) the function getCurrentPrice can be called in any SQL
Statement. E.g., SELECT ai_id, getCurrentPrice(ai_id) FROM auction_item;
15.How to send emails from the system?
The attached file is a java program which shows how to send email by the system. To use this sample._
(a) Use tar to extract the javamail.tar_
(b) modify the context of sample.java as follows
_ em.sendmail("Receiver_Email_Address", "Your_PSU_Email_Address", "Email Sample","Email Body");
_ -change "Receiver_Email_Addr" to the email address you want to send.
_ -change "Your_PSU_Email_Address" to your PSU email address.
_ -change "Email Sample" to the subject of the mail
_ -change "Email Body" to what you want to send.
_(c) Type ‘run.sh’ to compile and execute the java program.
18. There is a limitation (150) on the number of connections to Oracle. To avoid connection problems, you should put 'try...catch' blocks around each entire JSP file, and close the connection when an exception is caught.
//Declare Database class instance here
//SQL CODE, JSP CODE
catch(Exception e) //at very end of file
out.print("Exception: " + e.getMessage();
In this way, the connection to the database will be closed even if an exception is thrown between the connect and disconnect statements in the try block.
19. Can I get some information about nonstandard Oracle SQL statements?