BIT320 Remix: SQL Archives

Don’t write more in your blog than it takes to solve the issue

HOWEVER, he went on to say:

Their problem description needed a little work, but their solution was spot on and well thought out.

This was a little disappointing to hear. Obviously, it is not a hard problem to solve, but the lessons that are behind it are important.

You guys are doing fine, just sharpen that objective. Your other points are good, but I really just meant for you to sharpen things a bit.

In Blogonautic Solutions, 12/14/2005 | Original | Archive | Post to del.icio.us | Technorati

Project Feedback - My thoughts

I was happy that many people complemented the effort and technical success that my group has achieved. Bud was kind enough to say:

All groups should aspire to the technical level that Kevin, Aga, Matt Ruprecht, and Supriya achieved.

HOWEVER, he went on to say:

Their problem description needed a little work, but their solution was spot on and well thought out.

This was a little disappointing to hear. Obviously, it is not a hard problem to solve, but the lessons that are behind it are important.

The first lesson is understand the level of comprehension of the problem on both sides of the table. For this project we are IT consultants, we are given a real business problem from a customer and we are expected to have the business and IT knowledge to solve. For any of us that are going into consulting, IT consulting, or any technical field it is important to understand more than just the problem. We need to understand how the other person understands the problem A funny example of this actually happened to me this week at Merrill Lynch. My boss at work has a dual display for his computer. He has very little understand of computers and so to keep things simple for himself he keeps Outlook open on his left monitor and Merrill software on the right monitor. When I was at work this week his Outlook (on his left monitor) froze. Since he has such a limited understand of computers, he thought there was a problem with his left monitor. Now of course this is such a simple example, but when things get technical these problem could easily exist.

The second problem is we need to understand how the person wants the problem solved. For example, we all assumed that Denali flavors would want to use html insert forms to enter data into their database (of course this assumption goes along with course materials and project requirements). The truth is that John never mentioned that he wanted us to use web form to input data. If he only wanted data to be added at the main office typing in ugly SQL language, we would have wasted our time, and not really solved the problem the way the client wanted.

The third problem is over doing it. Bud told about an experience he had in his professional career about how sometimes doing more doesn’t always help. As students we barely have enough time to do all of our required assignments let alone time to over do it. It is something to remember that extra work isn’t always worth it.

As much as doing extra work is a bad thing. I think there is value in explaining to customers the full potential of their database. For example, in my part of presentation, I made many demands for my database, and I bet that when our projects are done ALL of the groups well have solved the problems I “demands” I listed. One lesson that Bit301 taught is the value of information and databases, now in Bit320 I’m learning how to make them, I want to harness their full value. So while I will state the problem in the project that Denali wanted a well-formed normalized that database that would allow customers to search for and find their favorite flavors in their local stores. I will know and make a database that can do much more.

In SuperMatt, 12/13/2005 | Original | Archive | Post to del.icio.us | Technorati

JB’s problems in her ice cream cone

JB’s group has an error. We are trying to create a customer input form. We can get the page up with the source. check it out here. But the problem we have is when we input information on a customer and we get an error that looks like this!

This is the code we used to insert the info. We pretty much took the examples from class and changed it to our specific case.

bind-params=”customerID zip flavor”>
BEGIN
INSERT INTO customer VALUES(?,?,?);
COMMIT;
END;

p>

Help us please…otherwise JB can’t eat her ice cream

In Shady Waters, 12/07/2005 | Original | Archive | Post to del.icio.us | Technorati

SQL Central

SQL Server articles, scripts, and discussion groups.

In del.icio.us/kevlers, 11/26/2005 | Original | Archive

Database Foundry

Project descriptions, news, guides, forums, and articles.

In del.icio.us/kevlers, 11/26/2005 | Original | Archive

The Tempo Quickens as the Templetes Diversify

ahhhhhhhh! I’ve been trying to work on this chapter of SQL. And I was moving ok until the end of the chapter and I hit a wall with Attribute Value Templates and Working with Named Templates.

Ok first with Attribute Value Templates:

What is an Xpath?
What is the purpose of this template

Second with Named Templates, I understand that its creating another template rule so that one doesn’t need to rewrite it at every place you want to use it buuuuuuuuut:

at the end of the example, why do they match year and grade?
and why does my sample when I run it have an error message of:

file:/home/usr01/bit320/steviek/webapps/XSLT/basic.p63-2.xsl: XML-0121: (Fatal Error) End tag does not match start tag ‘xsl:template’.
file:/home/usr01/bit320/steviek/webapps/XSLT/basic.p63-2.xsl: XML-0210: (Fatal Error) Unexpected EOF.
Error occurred while parsing basic.p63-2.xsl: End tag does not match start tag ‘xsl:template’.

OK that’s it for right now ;D Over and Out!

In Shady Waters, 11/19/2005 | Original | Archive | Post to del.icio.us | Technorati

I love this book!

So I think I’m finally figuring out how XSLT works.. I was confused on what exactly the “match” does. But now I see that it is probably one of hte most important commands. FOllownig the example, if I’m getting this right..if it calls for a match=film..It will go through each part and find where is written, and then everything undereneath that will be styled into the template chosen.

But would film have to be a node which encompasses the other information? I guess I’m still confused on where it is looking for the match.

In zee124, 11/18/2005 | Original | Archive | Post to del.icio.us | Technorati

XSQL Problemos

I decided to go through our notes for XSQL once more, but now I’m getting a weird error. WHen I try to retrieve “xsql query1.no_style.xsql” or even any other table im obtaining an error:

< ?xml version = '1.0'?>

ect customer “ID”, salary “salary”, age “age”, zip “zip”
from house
where age = ?
ORA-00942: table or view does not exist

But when I list all in the folder, it definently exists. Anyone have any solutions?

In zee124, 11/18/2005 | Original | Archive | Post to del.icio.us | Technorati

Because I’m a Dummy!

I found an excellent website which explains XML and SQL’s relationship. It also explains the best times to use XML:

When you want to store an entire block of data and retrieve the whole block later.

When you want to be able to query the whole XML document. Some implementations have expanded the scope of the EXTRACT operator to enable extracting desired content from an XML document.

When you need strong typing of data inside SQL statements. Using the XML type guarantees that data values are valid XML values and not just arbitrary text strings.

To ensure compatibility with future, as yet unspecified, storage systems that might not support existing types such as CLOB.

To take advantage of future optimizations that will support only the XML type.

And when not to use XML:

When the data breaks down naturally into a relational structure with tables, rows, and columns

When you will need to update pieces of the document, rather than deal with the document as a whole

I like the For Dummies series..It explains things so well!

In zee124, 11/18/2005 | Original | Archive | Post to del.icio.us | Technorati

Exam clarification #2: Question 16, SQL

Another one of those possibly contested questions that some of us discussed has to do with question 16. It looks at whether the subquery within the query is same as customer.customer_num <> orders.customer_num. I was a little confused on that one because I didn’t understand the real difference in the cross product. What helped me see that they are really different is running it in Oracle (because your tables are still there). The first command basically eliminates duplicates and filters out all the possibilities. It’s easier to see if you run it than explain it verbally (it’s simply one of those rules). I hope this helps.

In Pink Footsie, 11/10/2005 | Original | Archive | Post to del.icio.us | Technorati

A VC: Technology and Markets Trump The Law

Open Source Software discussion

In del.icio.us/mridge, 11/01/2005 | Original | Archive

SQL tutorial

SQL tutorial site listing commonly used SQL commands -- good resource

In del.icio.us/kevlers, 10/25/2005 | Original | Archive

Matt’s featured Feature

In the spirit of the midterm tomorrow I thought I’d blog about my favorite feature in Oracle the IN operator. Let just say the IN operator did exist and I wanted to list the number, name, and credit limit for each customer with a credit limit of 5k, 10k 15k. My sql script would be:

SELECT CUSTOMER_NUM, CUSTOMER_NAME, CREDIT_LIMIT

FROM CUSTOMER

WHERE CREDIT_LIMIT = 5000

OR CREDIT_LIMIT = 10000

OR CREDIT_LIMIT = 15000;

But thanks to the IN Operator it looks something like:

SELECT CUSTOMER_NUM, CUSTOMER_NAME, CREDIT_LIMIT

FROM CUSTOMER

WHERE CREDIT_LIMIT IN (5000, 10000, 15000);

Anything that saves me typing is a great help. Are you interested in more ways to use the in functions? Check out what these bloggers had to say about it.

In SuperMatt, 10/24/2005 | Original | Archive | Post to del.icio.us | Technorati

HAVING clause

Can “having count” or generally the HAVING clause refer to non-primary key attributes? It refers to groups and i understand the basic differences between it and the where clause that seeks out specific rows. If it can, can someone provide an example? thanks.

In Pink Footsie, 10/22/2005 | Original | Archive | Post to del.icio.us | Technorati

The brain is still smarter than the computer

As I was repracticing some commands in oracle I noticed that you can’t uniquely identify several aggregate functions in one command (ie. identify by customer_name, sum(credit_limit), ave(credit_limit) and so on even when you try to split up each command on a unique line with customer_name repeated each time). So it’s best to do these individually by either retyping the command (if short) in oracle or saving it in jEdit and then augmenting it to get new info. By the way can we link somehow to jEdit files or Oracle since they’re not webbased? All the sql assignments are found in the sql book and chapter covered are found here.

In Pink Footsie, 10/22/2005 | Original | Archive | Post to del.icio.us | Technorati

Supriya: INSERT question

Supriya said she is having the following problem:

INSERT INTO REP VALUES (’20′, ‘BAJORIA’, ‘SUPRIYA’, ‘848 TAPPAN’, ‘ANN ARBOR’, ‘MI’, ‘48104′, 2000.00,0.05);

Do I type this in jEdit or Oracle? I typed in jEdit and then I tried running

select * from rep;

in Oracle but I dont get anything. Am I suppose to also save every insert command like we saved the tables rep etc.? Or what am I suppose to do?

The way I understand it, you should type everything into jEdit and save it. Then run everything in Oracle.

I know the process is confusing so here are a couple things to doublecheck if it isn’t working:

1. Did you start SQLplus in Oracle?

2. Did you actually create the REP table in Oracle? You have to create the table before you can reference it in any commands.

3. Are you using the SELECT command appropriately? In the book on page 50, it says that you have to hit the enter key in the middle of this command. So your keystrokes should be SELECT, *, , FROM, REP, ;, . In other words, FROM REP is on a new line.

Hope it helps!

In jb's blog, 10/17/2005 | Original | Archive | Post to del.icio.us | Technorati

jEdit - Programmer's Text Editor

What is jEdit? What are its features? What is it used for?

In del.icio.us/supriya, 10/16/2005 | Original | Archive

To Learn More about ORACLE

In addition to the famous database, Oracle supply a range of software to manage, share and protect data and information.

In del.icio.us/supriya, 10/16/2005 | Original | Archive

Call me CrAzY….

but I prefer Oracle to Access. Supriya is right, it’s not as user friendly as Access, but for some reason I feel more comfortable using it. I think it may be because I feel like I have more control to change things and manipulate them using Oracle. I’m happy we’re learning a server based app.

In zee124, 10/16/2005 | Original | Archive | Post to del.icio.us | Technorati

Oracle, Oh how I love thee

Supriya hits the nail on the head when she remarks:

I was just comparing writing SQL queries using Oracle as opposed to using Access. I think Oracle is not as user-friendly as Access.

As I mentioned in the class where we started Oracle, it is not as user friendly. No server based app is as user friendly as Access (you may recall that Access has its problems too, like occasionally munging queries).

We’re going to server based databases because those are the only databases suitable for the Internet. Further, Oracle, and Oracle equivalents, are by far the most widely used server databases. Access just cannot handle it.

Why?

It cannot handle multiple concurrent users. It can only be accessed by single users at a time.
It does not have any facilities built in for connecting to the Internet.
There is no professional future in learning more with Access because people view it as no more than the toy database it is.

I realize everyone is busy, and the last thing you want is to learn a new skill while you are trying to get your lives in order senior year, but that is what this is about.

This will get to be second nature to you, to the point where you do not even notice the difference with Access.

In Blogonautic Solutions, 10/15/2005 | Original | Archive | Post to del.icio.us | Technorati

Exists

Shady’s on a roll when she asks:

Ok, but no really. Page 110 in chapter 5. I’m confused about what Exists is and how it can be used. Can you only use it with a subquery?

The real point of the example on page 110 in Chapter 4 is that you are trying to find orders that include Part DR93. There are 3 ways to do that:

A join
A query with a subquery using IN
A query with a subquery using EXISTS

They all provide equivalent ways of answering the question. The key point with EXISTS is to see the correlated subquery. Basically, what happens is that the correlated subquery is run against each row of the ORDERS table in the outer query to see if it is true. If so, that line from ORDERS appears in the output.

When you can see how each query succeeds in answering the basic quesiton: “What orders include Part DR93?” You have understood this well. Note how hard the query is. This is one of the drawbacks of normalization.

In Blogonautic Solutions, 10/15/2005 | Original | Archive | Post to del.icio.us | Technorati

Exists Exists…oh what a word

alright. Do you really exist? Do you know how we make ourselves exists?
Because I don’t think I can exist if I don’t figure out how to use Exists in SQL. The program is defeating me and I really have no reason to go on.

Ok, but no really. Page 110 in chapter 5. I’m confused about what Exists is and how it can be used. Can you only use it with a subquery? Bud says its important and I’m like ack cause it doesn’t really provide a very good explaination for it. Soooo glad that I’m doing the practice problems.

In Shady Waters, 10/15/2005 | Original | Archive | Post to del.icio.us | Technorati

Oracle Upgrades Recently Acquired TimesTen In-Memory Database

One improvement still to be made is getting TimesTen to recognize and use Oracle's modified SQL query development language

In del.icio.us/tigerlily23, 10/13/2005 | Original | Archive

I take it back

I should probably read the ENTIRE chapter (or at least the next page) before trying things out on my own! I was doing run Rep thinking that is how I view the table, but really it’s Select *from rep; which does the job. So I think I’m good to go!

In zee124, 10/13/2005 | Original | Archive | Post to del.icio.us | Technorati

Getting an Error

After inserting a new row and running it, I’m getting this error

Insert into rep
values
(’20′,’Kaiser’,'Valerie’,'624 Randall’,'Grove’,'FL’,'33321′,20542.50,.05);
5)
Insert into rep
*
ERROR at line 1:
ORA-00001: unique constraint (ZEENAH.SYS_C0048437) violated

I’m following the instructions and am getting the information, but not sure what this error means.

In zee124, 10/13/2005 | Original | Archive | Post to del.icio.us | Technorati

Editing Issues

Tigerlily, Matt, and Ryan have reported issues connecting to the server. The reports seem to be about slightly different issues. Tigerlily reports:

I was wondering if anyone had tried using jEdit and saving the sql like we did in class yesterday, from home. I didn’t see the plugin for connecting to secure FTP server like we used in class and I am not sure if that feature can be used from home. If I can do it from home, does anyone know what I have to do?

This seems like a problem to be solved by downloading the plugin, as I describe here.

Matt and Ryan both describe issues connecting even with the plugin. Matt seems to have been able to connect using other programs, so we know the issue is not a general connection problem. I’m less sure about Ryan because he reports no succes with another program. Let me suggest this process for debugging connection issues:

Make sure you have the jEdit SFTP plugin.
Make sure you can connect by another means. That rules out the potential that you are having a connection vs. a jEdit problem.
If you can connect, but jEdit won’t do it, try closing and reopening jEdit.

If you cannot get jEdit to work, but can connect, you can use the pico editor as I describe here.

You can also use ultraedit on the PC. It has SSH support.

In Blogonautic Solutions, 10/13/2005 | Original | Archive | Post to del.icio.us | Technorati

Creating SQL Files

Like Blogstar and TigerLily, I was having trouble connecting to the server, even after installing the plugin. So I began editing the SQL files in my favorite editor for Mac, SubEthaEdit, and then uploading them to the server via FTP. However, I realized I could edit files directly from the Oracle server. While logged into SQLPLUS, I typed “edit ‘filename’ “. It created a new file for me, I editied my SQL statements, saved it and exited. The files were saved in my SQL directory and were immediately available for running. Right now, this will probably be my preferred way of creating/editing files. The only downside is that PICO doesn’t provide any syntax hints.

In Matt's Musings, 10/12/2005 | Original | Archive | Post to del.icio.us | Technorati

jEdit problems

I’m also having trouble saving files through the program. I’m working from a campus computer so I can get to the “connect to secure FTP server” but that’s where my problem begins. Once I type in the required info host name, password etc. I get a message saying “the following I/O operation could not be completed….connection refused.” If anyone can assist me I’d greatly appreciate it.

In The Blogstar, 10/12/2005 | Original | Archive | Post to del.icio.us | Technorati

A Gentle Introduction to SQL

SQL tutorial

In del.icio.us/tigerlily23, 10/12/2005 | Original | Archive

Interactive Online SQL Training

In del.icio.us/tigerlily23, 10/12/2005 | Original | Archive

jEdit?

I was wondering if anyone had tried using jEdit and saving the sql like we did in class yesterday, from home. I didn’t see the plugin for connecting to secure FTP server like we used in class and I am not sure if that feature can be used from home. If I can do it from home, does anyone know what I have to do?

In Tigerlily's Blog, 10/12/2005 | Original | Archive | Post to del.icio.us | Technorati

SQL, XML, & Ice Cream

A remixable web learning project at Michigan's Ross School of Business

BIT320 Remix — SQL

Of Special Note

Search

Browse By Topic