Oracle SQL Tips: Non standard features, and limitationPage

Oracle SQL Tips: non-standard features, and limitations

The following issues are for Oracle 10, and may or may not apply to later versions of Oracle.

1. Oracle does not support the as clause, except as part of the "with" statement. In all other
cases, just use the same syntax without the "as" keyword, and things should work.
2. To use a single quote in a string, use two consecutive single quotes, e.g. 'D''Souza', the usual
backslash escape (e.g. 'D\'Souza') does not work.
3. Oracle supports the with clause, but there are some syntax differences: the query defining a
temporary view should be enclosed in parenthesis, and if there are multiple views, separate them
by commas, instead of repeating the with keyword. Also column renaming is not supported, you
can only give a name for the temporary view. The following example illustrates the Oracle with
clause syntax:

with foo as (select * from bar),

baz as (select * from zap, zit where P)
select *
from foo, baz
where P
4. The with clause cannot be used in update queries
5. Oracle does not support tuple comparisons; thus, (r.A,r.B) < (s.A, s.B) will not work in Oracle;
instead you have to use ((r.A < s.A) OR (r.A=s.A and r.B < s.B))
6. Oracle does not allow giving an alias to (renaming) a join expression such as (r natural join
s), using FROM (r natural join s) T does not work. You can use FROM (select * from r natural join
s) T, or even define T using a with clause.
7. If you use the expression "r join s using (A, B)", you cannot refer to r.A or r.B, you have to
refer to them as just A and B; however, if you have another common attribute C, you can use
r.C and s.C. For the case of "r natural join s", all attributes can be accessed using only their
name, you cannot prefix them with "r." or "s."
8. Oracle allows correlation variables (table aliases) to be used in an immediate subquery, but
not in a subquery of a subquery. This caused a lot of grief in question 9, where the obvious
formulation required the variable to be used two levels down. I will discuss this query in more
detail in a separate post and in class.
9. Instead of the keyword LATERAL, use the keyword TABLE
10. Oracle supports a weird non-standard SQL syntax which you should NOT use:
select max(count(*)) from student group by dept_name
 Common and uncommon problems with Netbeans and with Oracle

1. Some users get an exception when connecting to Oracle (from netbeans or other places) with a
message containing "NLS". This usually happens when your login has a "locale" which Oracle
does not understand (the locale is passed to Oracle without your knowledge). As an example,
someone had a locale with language set to en_PH, which Oracle didn't know about. Normally
locale should be set to something meaningful, but if this happens check your locale using the
"locale" command, and make sure it is something such as en_US.
2. Some students get into problems with weird errors from netbeans. This usually happens when
they have run out of disk quota. Make sure you have plenty (say 50 to 100 MB) of space free,
using quota -v to see how much space is used and what the quota is. If you need to clean up files
use:
1. "du -s *" to find space used by directories and files other than those that start with a
leading dot.
2. "du -s .[^.]*" to do the same as above, for files/directories starting with a leading dot.
Many times directories such as .thunderbird, .mozilla and .wine are culprits.
3. When doing web projects, make sure to set browser preferences to noproxy for localhost, or you
won't see the output of your code on the browser.
4. When using netbeans 7.0, the glassfish issues are documented separately. Please follow the
instructions carefully. You may still get errors about accessing /usr/local/glassfish since the
default glassfish server is still there. You can either delete that server, or once the basic code
works you can start ignoring these error messages.

Steps for accessing netbeans:

cd /usr/local/netbeans-7.0/bin/
./netbeans

(if your machine does not have netbeans-7, you can use 6.8, 6.7 or 6.5 instead all of which should be
under /usr/local/netbeans-x.y/bin).

Netbeans allows you to set proxy to access updates/addons from the web.
In earlier versions of netbeans, setting the proxy prevented netbeans
from accessing local databases. This appears to have been fixed on 7.0,
but I would still recommend not setting the proxy to be safe.
Instructions for accessing Oracle from Netbeans

Steps:

1. Get the required driver from Oracle 10g JDBC Drivers site. Or better still from here.
2. In Netbeans, select tab Services
3. Goto Databases > Drivers
4. Right click on Drivers and choose New Driver and ADD to add the above driver
5. It then adds Oracle under Drivers
6. Right click on the Oracle driver and choose Connect using ...
.

7. Then select the radio button "Direct URL Entry", and then in the Database URL tab,
enterjdbc:oracle:thin:@[Link]:1521:oradb
8. Enter your Username and Password
9. Then it adds a new connection under Databases. Click on that to browse the schema
1. Note: if you have a proxy set up for netbeans, this step appears to fail. Turn off proxy and
it works.
10. To execute sql commands on the database, right click on the connection and select Execute
command ... . This opens a tab on the right side, where you can enter the sql commands.
11. To change your password use the command
alter user <userid> identified by password
where <userid> is your Oracle userid, and password is the new password (don't use quotes).

Assignment 0: SQL Familiarity

Graded by TAs: Bikash and Ankit Shah

A simple first assignment.

1. Start up netbeans 7 and set up your oracle connection (see instructions on the moodle cs 387
page)
2. Log in to oracle, using netbeans
3. Browse the tables in your schema (which will be empty initially, till you create tables).
4. Browse the tables in the dbis account; these are based on the University schema diagram, except
that X_time attributes have been split into X_time_hr and X_time_min attributes.
5. Open an SQL command session, and execute simple queries such as
1. SELECT * FROM [Link]
2. SELECT * FROM [Link]
3. etc.
6. Create local copies of tables by executing the two scripts (copy paste them into netbeans)
1. Script to create tables
2. Script to populate tables with data

3. Note: you have to use refresh (right click) on the tables list to see tables you created
above from netbeans.
7. Try out some of these queries, and see what they do. To run them, copy/paste them into the
netbeans sql command window, select the command, right click, and choose "Run Selection"
1. SELECT * FROM instructor
1. As above, but replace SELECT by select or SeLeCt and instructor by Instructor
2. SELECT name FROM instructor WHERE dept_name = 'Comp. Sci.' and salary > 70000
3. SELECT * FROM instructor, department WHERE instructor.dept_name =
department.dept_name
4. SELECT course.course_id, [Link] FROM course, section WHERE course.course_id
= section.course_id AND [Link] = 2010 AND [Link] = 'Spring';
5. Do the same query as above, but remove the "course." and "section." prefix for
everything except the attribute course_id, and see what happens.
6. Set operations:
1. (SELECT course_id FROM section WHERE year = 2010) INTERSECT (SELECT
course_id FROM section WHERE year = 2009)
2. As above, but replace intersect by union
3. As above, but replace union by minus (the SQL standard uses except, but Oracle
uses minus)
7. Creating tables, inserting and deleting data
1. CREATE TABLE classRep(batchyear numeric(4,0), dept_name varchar(20),
program varchar(10), ID varchar(5));
2. INSERT INTO classrep values (2010, 'Comp. Sci.', '[Link].', '12345');
3. DELETE FROM classrep WHERE ID = '12345';
8. Try inserting data about yourselves into the university schema tables. Refer to the populate script
above for examples.
9. Take "Quiz 0" on SQL queries on the lab moodle page. This is NOT a graded quiz.

1) SELECT course_id, title

FROM course;

2) SELECT course_id,title
FROM course
WHERE dept_name='Comp. Sci.' ;

3) SELECT course.course_id,[Link], ID
FROM course, teaches
WHERE course.course_id=teaches.course_id
AND [Link]='Spring' AND [Link]='2010';

4) SELECT [Link], [Link]

FROM student, takes
WHERE [Link]=[Link] AND takes.course_id='CS-101';

5) SELECT DISTINCT course.dept_name

FROM course, section
WHERE section.course_id=course.course_id
AND [Link]='Spring' AND [Link]='2010';

6) SELECT [Link]
FROM student, takes, section, time_slot
WHERE [Link]=[Link] AND takes.course_id=section.course_id
AND time_slot.time_slot_id=section.time_slot_id
AND time_slot.day='T' AND time_slot.start_hr = 10
AND time_slot.start_min = 30 AND [Link] = 'Spring'
AND [Link] = 2010;

7) SELECT [Link] FROM student

MINUS
SELECT [Link] FROM student, takes, section, time_slot
WHERE [Link]=[Link] AND takes.course_id=section.course_id
AND time_slot.time_slot_id=section.time_slot_id
AND time_slot.day='T' AND time_slot.start_hr = 10
AND time_slot.start_min = 30 AND [Link] = 'Spring'
AND [Link] = 2010;
-- In standard SQL we would use EXCEPT where Oracle uses MINUS

Instructions for using JDBC

A quick overview of JDBC is available in the slides for Chapter 5 (on CS 317 moodle page), and here
[Link]

Detailed JDBC documentation is available from [Link]

Select [Link] on the top left pane, and you will find the classes in the pane below. Select a class and
you will find its methods on the right pane.
However, this documentation is NOT NECESSARY for the in-lab assignment, read it afterwards for the
take home assignment.

You can do this assignment either using javac directly, or using netbeans.

If you use netbeans, create a new project (choose Java/Application), and then create required files within
the project. Your database connection on netbeans has already been set up in earlier classes, so you
don't have to configure netbeans again.

If you directly use javac, follow the instructions below:

First download a copy of [Link] ( [Link] to your

work directory.

set CLASSPATH as follows

export CLASSPATH=[Link]:$CLASSPATH

You may want to put the above command in your .bashrc

Then javac your program and execute it

How to create a Web project in netbeans

1. Set up a glassfish domain in your home directory as follows. (In earlier versions of Netbeans this
was not required but Netbeans 7.0 with the default glassfish server tries to save your project
control files in /usr/local which you don't have permissions to.):
1. Download the file [Link] from the moodle home page to your home director,
and extract it using the command
tar zxvf [Link]
( Extracting directly by right clicking may not extract the "links to other folders" properly)
2. Open Netbeans, click on services. Then right click on servers to "add server"
3. Choose glassfish 3.x from the dropdown list.
4. Then browse the path for the extracted glassfish folder upto your-home-director/glassfish-
3.1
5. Accept the licence agreement by clicking on it.
6. Give a suitable name for the local domain you would be using. Any name is fine,
Domain1 works just fine. Don't use remote domain, stick to "Register local domain" which
is the default.
7. Click on next and finish
8. NOTE: DO NOT work on a remote machine since glassfish runs on default ports
such as 8080, and if two people use netbeans on the same machine one or both
will not be able to run Web applications.
2. Then create a web project (New > Project > Java Web > Web Application and choose your
glassfish-3.x server when you get to the server and settings menu. Do not select any Framework,
since you won't need them currently.
1. if you need to change it later go to it's properties
1. Click on "Run" and change the server to glassfish-3.x. (Name of your glassfish
server if you changed it)
2. Now run the sample JSP created by netbeans and it should open the browser displaying
the message.
3. You can then rightclick on the newly created web application and select new, then select servlet.
Follow the wizard, and give an appropriate name to the servlet. Do NOT create a class directly,
since then netbeans/tomcat will not know how to map the pathname in the url to the class;
creating a new servlet does this mapping automatically.
4. Make sure the JDBC driver is loaded: go to project -> properties -> libraries, and add the
[Link] file which you have saved earlier.
5. Also make sure the driver is loaded in your program using [Link] or
[Link], and the JDBC URL is correct.
6. When you run the application, it runs the file [Link]. You can add a hyperlink to your servlet
from [Link]. Treat [Link] as a html file for now, don't bother about other jsp features.
Here's a tutorial on servlets. You don't have to read it before you do the assignment, but read it at leisure
sometime to understand what is going on behind the scenes.

How to Debug:

You can output debug messages to [Link], and you will see them in the server logs (this shows up in
a tab at the bottom of the netbeans window).
If the tab is not visible, add it using View -> IDE logs

Sometimes you need to restart the application server, if your browser shows the web request hanging. To
do so, go to Services -> Servers -> , right click on the server name you chose earlier, and select restart.
Sample form

<form action="PersonQueryServlet" method=get>

Search for:
<select name="persontype">
<option value="student" selected>Student </option>
<option value="instructor"> Instructor </option>
</select> <br>
Name: <input type=text size=20 name="name">
<input type=submit value="submit">
</form>

Sample servlet

/* Following imports already added by Netbeans when you create a servlet. */

import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];

/**
* Copy just the doGet method below into the autogenerated template in Netbeans,
* replacing the processRequest method, or (if you don't use get and post with the same servlet),
* paste the body of doGet into processRequest, but don't define the method doGet (netbeans creates it
for you but hides it).

You should indent your code properly, Moodle autoformat removed the indentation on this example.
*/
public class PersonQueryServlet extends HttpServlet {

public void doGet(HttpServletRequest request,

HttpServletResponse response)
throws ServletException, IOException
{
[Link]("text/html");
PrintWriter out = [Link]();
[Link]("<HEAD><TITLE> Query Result</TITLE></HEAD>");
[Link]("<BODY>");
String persontype = [Link]("persontype");
String number = [Link]("name");
if([Link]("student")) {
/* ... code to find students with the specified name ...
... using JDBC to communicate with the database ..
*/
[Link]("<table BORDER COLS=3>");
[Link](" <tr> <td>ID</td> <td>Name: </td>" +
" <td>Department</td> </tr>");
/* for(... each result ...){
... retrieve ID, name and dept name
... into variables ID, name and deptname
[Link]("<tr> <td>" + ID + "</td>" +
"<td>" + name + "</td>" +
"<td>" + deptname + "</td></tr>");
};
*/

[Link]("</table>");
}
else {
/* ... as above, but for instructors ... */
}
[Link]("</BODY>");
[Link]();
}
}

Servlet tutorial

Here's a tutorial on servlets. You don't have to read it before you do the assignment, but read it at leisure
sometime to understand what is going on behind the scenes.

Assignment 4a: Servlets (in class)

4a and 4b graded by TAs: Bikash, Ankit and Zibran

You can copy the sample servlet and edit it to get your servlets working. You may find it useful to output
debug statements both to the response, and to [Link] (you will see it on the netbeans screen and/or
in the tomcat/glassfish log files)

Create a web page with appropriate forms, and servlets, to perform each of the following actions:
1. Form 1: Accepts a string, and display a list (using the ordered list tag OL in HTML) of students
whose name contains the given string, showing ID, name, dept_name and tot_creds in a single
line.
2. Form 2: Shows a drop down menu containing the names of all departments (generate this from
the database), and when a particular department is selected, a list of all students in the
department is displayed (as in the previous question).
If you finish with the above, you can move on to the take home servlet assignment.

Assignment 4: Servlets (take home)b

1. Modify form 2 of Assignment 4a to display the ID as a hyperlink to a servlet which displays the
same information as above for that student, followed by course registration information for that
student in tabular form. The columns to be displayed are:
1. Year
2. Semester
 3. Course ID
4. Section ID
5. Grade (if grade is null, show it as blank, not as the string null).

2. This assignment uses JDBC metadata features. Create a dropdown which displays the names of
all relations in the database (in your schema). On choosing a relation and submitting the form,
you should display an interface to add/modify/delete records in the relation. This interface should
show all records of the relation as a table, and
1. after each record, add a column with two links:
1. a delete link, which when clicked deletes that record (or more precisely all copies
of that record).
2. a modify link, which when clicked shows all fields of the record as prefilled text
boxes, which can be edited, plus a save link, which updates (all copies of) the
record with the new values.
2. at the end of the table, show an add record link, which is similar in format to the modify
link, but creates a new record.
(For extra credits which won't increase your overall total beyond the maximum, but can make up for any
mark reduction in the earlier parts): Replace text boxes by dropdowns for fields which are foreign keys;
you can assume that no column appears in more than one foreign key, so if a foreign key has multiple
columns you can have a single dropdown for all those columns together.

Project Resources (tips, SRS structure, SRSs from earlier years)

Here are links to information about the project:

 System Requirements Specification (SRS)

 [Link]
 [Link]
 Example SRS from earlier years
 Event Scheduler
 Shopping
 Structuring your code
 Directory organization

Directory Organization

DBIS LAB

The following directory structure should be used for the DBIS Lab projects.

[MODULE NAME]
 |
|----------------[bin]---| All Class files
|
|----------------[doc]---|---- Final SRS sources and postscript
| and any other docs
|
|----------------[html] HTML pages used in your application
|
|
|----------------[src]---| Source Files
|
|
|----------------[tutorial] if any
|
|
|----------------[dat]---| All the data in the flat files,
| use scripts to load data into tables.
|
|----------------[imgs]--| All images(if any) should be stored here.
|
|----------------[tmp]---| Temporary Space.
|
|----------------[scripts]-| SQL and Shell Scripts

Project Suggestions
Here are some suggestions for projects.
Many of these projects are best done as Web applications. However, if you wish to, you can create front
ends designed for smart phones, which communicate with the backend (typically using AJAX).

1. SQL assignment submission/feedback system: Allow students to submit answers for

assignments, which can be run immediately against a one or more datasets to check correctness
(as compared to a correct model answer). Feedback can be immediate on some datasets
allowing students to correct basic errors, and could be offline on other datasets, allowing grading
based on handling all datasets correctly.
2. Moodle project 1: Assignment Correction: Currently its a pain for TAs to mark up corrections
on your assignments on moodle, since they have to type the comments separately. Add a feature
to moodle that can take a submission (in plain text, code, doc, PDF, whatever) and generate a
PDF from it, which a TA can then update by adding notes on it. After saving the document,
clicking on an upload link should result in the marked up assignment being uploaded back to
moodle. To do this you need to know or learn php, and dig into moodle. You can set up a copy of
moodle on your own computer to be able to hack it up. [Note: This was done in 2009, but could
do with extensions]
3. Other Moodle projects: Come up with your own ideas for extending moodle. Moodle is a great
system to learn about real world web/database application programming using PHP.
4. Online Spreadsheet: Implement google spreadsheets (or a subset of it), allowing users to create
spreadsheets online. Optionally integrate it with the access control project. Optionally deal with
concurrency control issues.
5. Form application creation system: This was done partly in 2010, but needs more work to make
a system where you can create form applications (kind of like moodle's quiz system, which allows
creation of quizzes which students can answer, but with a lot more features).
6. TA allocation system, which takes student preferences, faculty preferences for students, course
TA requirements (including prereq courses/minimum grade) and comes up with an allocation.
7. Project allocation system, which allows faculty to float projects, students to indicate interest in
projects (with a hidden preference order), faculty to choose students (again with a preference
order), and final allocation to be done based on these factors. Speak to recent faculty advisors to
get more ideas on the issues involved.
8. CourseRank: This system developed by a few undergrads at Stanford under the guidance of
DB/IR faculty there, and apparently a big hit there.
<quote>CourseRank, a social site where Stanford students can review courses and plan their
academic program by accessing official university information and statistics, such as bulletin
course descriptions, grade distributions. Students can also provide information, such as
comments on courses, ratings, questions and answers.

At our site, ([Link] visitors can see a video with student testimonials and a demo
(demo tab). A paper is available at: [Link]

CourseRank offers powerful tools geared to our domain, for example, a tool for planning an
academic program (Planner ) that checks for schedule conflicts and computes grade point
averages, a tool that checks if requirements for a major have been met (Requirement Tracker ),
and a tool for searching and browsing with help from a “tag cloud” (CourseCloud ). CourseRank
also offers a tool for“flexible recommendations” (FlexRecs) for the site administrator. This tool lets
the administrator quickly define recommendation strategies."</quote>
9. Access control for a Wiki site. The access control should be based on roles such as faculty,
student, with a role hierarchy (UG student, PG student under student) coupled with
organizational-units, such as CSE department, H1, etc. User-role-organization-unit mappings can
be partially taken from LDAP, but also added to in your system (e.g. Sanyal-HOD-CSEDept).
Access control lists (ACLs) contain a list of role/org-units; a resource can specify one or more
ACL explicitly, and in addition can inherit ACLs from parent resources. Permissions include
 read/write/admin. Your code should be based on a database but you can hack up your favourite
wiki to access your code and decide on who can do what on the wiki
10. Web-based DB Client: You have used the database client which comes with Netbeans. Now
create a web app that lets you do the same things with any database through JDBC. Have a look
at this [Link]
11. Timesheet Software: Project students can use the software to log the work items and effort
spent on it (weekly/daily), meeting notes with the faculty etc. The tool would show a number of
options to generate various kinds of reports from this data. These reports would be useful for the
student at a later point of time to recollect everything about his work.

12. Resume Generator: The tool will present a user with a UI to input all the necessary information
to be put in his resume. As soon as a student submits his information, a notification would be sent
to the department placement nominee(DPN) for the verification of the resume. The DPN would
schedule an
appointment for verification with the student. After the verification, the student will be shown a
button to generate a resume. On clicking the button, a latex document for the resume will be
created internally and a PDF will be generated and presented to the student.

13. Department room allocation and tracking system: Identifies which rooms have been allocated
to which students/faculty, what is the area/capacity of each room, multiple views such as who is
in a room, or which rooms/parts of rooms have been allocated to a faculty member or his/her
students, etc.

o Using IDEs properlyPage

 Majority of students still use IDEs such as Eclipse or Netbeans as text editor. They support lot of
features which could make a developer more productive.

Please go through the following post and also in the comments section, people have pointed out their
favourite shortcuts along with alternate keys for some tasks.
[Link]
should-know/

Below I have listed down the Short-cuts that have been mentioned in the above post + few more. One
more thing if you are comfortable with Netbeans, such shortcuts exists for Netbeans as well.
Moreover you can configure them so that both ide's have same effect for same key combinations. I
have listed listed down few shortcuts for your reference.

1] For a resource like a file - Ctrl+Shit +R

2] To directly jump to a declaration. - Ctrl+Shift+T

3] Ctrl+O and again Ctrl+O (Alternatively, if you just want to jump from one member to the next (or
previous), you can use Ctrl + Shift + ↓ or Ctrl + Shift + ↑, respectively.)

4] Go to line number N in the source file: Ctrl + L, enter line number.

5] Go to the last edit location: Ctrl + Q

6]Go to a supertype/subtype: Ctrl + T

7]Go to other open editors: Ctrl + E

8] Move to one problem (i.e.: error, warning) to the next (or previous) in a file: Ctrl + . for next,
and Ctrl + , for previous problem. No need to lift your hands off the keyboard to click on that red or
yellow stripe.

9] To hop back and forth through the files you have visited: Alt + ← and Alt + →,

10]Go to a type declaration: F3.

11] One of my favorites is CTRL+Shift+G, which searches the workspace for references to the
selected method or variable. CTRL+G searches for the declarations with a given name.

12] A complete list of shortcuts can be seen with CTRL+Shift+L . Going through that list is sure to
teach you a few new tricks about Eclipse

13 ] Suppose in your code you have

line1

line2

if your cursor is on line2 and you want that line to be before line1 then you can use Alt+↑ or Alt+↓ to
move a line up and down

14] Use Ctrl + D to delete a line.

o Javascript TutorialPage
 Some material on the JavaScript language and its use within a web browser.
(Downloaded from the Web)

Talks:

 [Link]
 [Link] (quick introduction)
 [Link] (language details)
 [Link]
Tutorials/Manuals

 [Link]
javascript/
 A quick read manual for
javascript: [Link]
 [Link]
 Somewhat more detailed
manuals : [Link]
Note: Follow the links on the top of the frame (just below VIEW INDEX) to learn more about
Javascript. The Objects link gives details about a number of objects supported by Javascript,
while the Functions link documents several useful functions.
 And more detailed language
documentation: [Link]
[Link]/javascript/reference/core/

Examples:

 [Link]
 [Link]

Ajax. Here's a site with lots of links to AJAX

[Link]://[Link]/[Link]/weblog/comments/round_up_of_30_ajax_tutorials/

o Using Yahoo's YUI Javascript libraryPage

o GWT YUI ResourcesPage

o More on GWT UpdatesPage

o Assignment 9: Project SRS Stage 1

o Assignment 10: Javascript

o YUI 3 and Javascript Nested Functions