Blogonaut

Big Blog in the Sky: Chapter 3, Question 13

Big Blog in the Sky: Chapter 3, Question 13 blogs about some difficulties with the SQL. What do my answers say?

I

'm pretty sure I don't have any problems with parentheses, but the second part of my statement is where I'm having trouble, but I'm just not sure where/why/how. Can someone explain their solution to me?

Go Blue: The DISTINCT Operator

Go Blue: The DISTINCT Operator asks when the DISTINCT operator is used. She also expresses some confusion over all of the different ways to do things in SQL.

Is there a general rule that whenever you use subqueries you don't need to use the distinct and everything will be automatically listed only once? I have a feeling it is more on a problem-by-problem basis...

You typically use DISTINCT when you are selecting on a non-unique attribute. One obvious case would be an attribute that is not the primary key. Also, when you write queries combining across tables you may wind up with a result set (the set of rows output as the answer) for which there is no unique identifier.

A lot of times you can design the query to produce unique output in the columns you want. So, DISTINCT is mainly a convenience operator.

Go Blue: Yay for Search

Go Blue: Yay for Search talks about her experience using search. Nice to see this working for someone.

Hail to the Victors: JOIN vs. IN question

Hail to the Victors blogs a JOIN vs. IN question.

She wonders why these two queries are not equivalent.

Query 1

SELECT orders.customer_num, customer_name FROM orders, customer WHERE orders.customer_num = customer.customer_num AND order_date <> '20-OCT-2003';

Query 2

SELECT customer_num, customer_name FROM customer WHERE customer_num not in (SELECT customer_num FROM orders WHERE order_date = '20-OCT-2003');

The issue is in this condition from Query 1, “WHERE orders.customer_num = customer.customer_num”. The condition restricts the results to customers who have orders in the orders table as well as in the customers table. In Query 2, there is no such restriction.

You could use a MINUS query to achieve the same effect as Query 2. I do not see any way to do straight JOIN to achieve the same result as Query 2. A JOIN constrains you to entities that have entries in two or more tables while what you looking for is entities in one table that do not have entries in the other.

BTW, these insights did not come brilliant logical analysis. I did a SELECT * on both tables and saw what was going on.

Big Dog: SQL Commands

Big Dog blogs about SQL Commands

Have a look at this post.

computers: SQL question

computers blogs about an SQL question

Let me hit each of his questions:

In chapter 2 of "A guide to SQL", I created the Rep table successfully and then I tried to input actual data into the table. When I input the data into the table, do I input it via jEDIT and then view it in Oracle?

I recommend using the method you used to create the rep table. Write the statement in jedit and then run it in Oracle.

Also, if i am supposed to do it in jEDIT do i put the INSERT command in the same file that I created teh table or do i save it as a totally different file? I tried to do it saving it as the same file but had some difficulty getting the data to display.

Personally, I do each one in a separate file. Sometimes, I take a set of insert statements and group them in one file. sqlplus will warn you if there are problems with one of the inserts.