20 May

SET Operators: EXCEPT

I was thinking this would be hard going into it, but, it’s actually really easy, except, that EXCEPT doesn’t exist in SQL Server.

EXCEPT is very simple.  What it does is return every unique row in the first result set, that is not in the second result set.  Here are two very simple examples:

SELECT 1 AS r1,2 AS r2,3 AS r3
EXCEPT
SELECT 3,4,5;

r1	r2	r3
1	2	3

SELECT 1 AS r1,2 AS r2,3 AS r3
EXCEPT
SELECT 1,2,3;

r1	r2	r3

Those are simple queries but they show how the EXCEPT operator works. Now, what happens if we have records that are duplicates in the first table? Well, this happens:

SELECT 1 AS r1,2 AS r2,3 AS r3
UNION ALL
SELECT 1,2,3
r1	r2	r3
1	2	3
1	2	3

(
SELECT 1 AS r1,2 AS r2,3 AS r3
UNION ALL
SELECT 1,2,3
)
EXCEPT
SELECT 3,4,5

r1	r2	r3
1	2	3

That’s really clean compared to most of my posts, and, it’s going to stay that way. The reason? I’m not going to mess with SAS and I’m going to let MySQL go right ahead not doing it. If you decide to do it in MySQL, and one of these days, I’ll do it, you need to get a bit creative with a JOIN or a couple of other options such as a correlated subquery and NOT EXISTS.

OK, I talked myself into it, but only because I literally just did this the other day.

First, I’ll create a new table that has just the records where sex = ‘M’

mysql> CREATE TABLE allMale AS SELECT * FROM junk WHERE sex = 'M';
Query OK, 10 rows affected (0.11 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT name, sex, age FROM allMale;
+--------------+------+------+
| name         | sex  | age  |
+--------------+------+------+
| Murray, W    | M    |   27 |
| LaMance, K   | M    |   51 |
| Jones, M     | M    |   29 |
| King, E      | M    |   35 |
| Pitts, D     | M    |   34 |
| Peterson, V  | M    |   30 |
| Cameron, L   | M    |   47 |
| Underwood, K | M    |   60 |
| Derber, B    | M    |   25 |
| Warren, C    | M    |   54 |
+--------------+------+------+
10 rows in set (0.00 sec)

Maybe I shouldn’t have called these tables junk?

Anyways, here is a query in SQL Server using the EXCEPT operator.

SELECT name, sex, age
FROM junk
EXCEPT
SELECT name, sex, age
FROM allMale

name	        sex	age
Almers, C	F	34
Bonaventure, T	F	31
Eberhardt, S	F	49
Ivan, H	        F	22
Johnson, R	F	43
Nunnelly, A	F	44
Oberon, M	F	28
Quigley, M	F	40
Reberson, P	F	32
Takahashi, Y	F	43
Wilcox, E	F	41

Now, for the MySQL version.

mysql> SELECT DISTINCT name, sex, age
    -> FROM JUNK j
    -> WHERE NOT EXISTS (SELECT name FROM allMale WHERE j.name = name AND j.
age = age AND j.weight = weight);
+----------------+------+------+
| name           | sex  | age  |
+----------------+------+------+
| Almers, C      | F    |   34 |
| Bonaventure, T | F    |   31 |
| Johnson, R     | F    |   43 |
| Reberson, P    | F    |   32 |
| Eberhardt, S   | F    |   49 |
| Nunnelly, A    | F    |   44 |
| Oberon, M      | F    |   28 |
| Quigley, M     | F    |   40 |
| Takahashi, Y   | F    |   43 |
| Ivan, H        | F    |   22 |
| Wilcox, E      | F    |   41 |
+----------------+------+------+
11 rows in set (0.00 sec)

I had to write that about 10 times because I kept forgetting to change the SELECT clause. Yeah, for me, SELECT = ‘HARD’, EXISTS = ‘Easy’.

The only part of this query that really matters is the WHERE clause. Essentially it uses a correlated subquery to compare the two data sets and where the record NOT EXISTS in allMale, it is returned to the result set. It’s not very clean, and all things being equal, I’d rather use EXCEPT.

OK, the SELECT matters too.  I have to use the DISTINCT there so the query won’t return duplicate values, or, as the documentation I’ve read for EXCEPT says “unique values only”.

And just for kicks, Oracle doesn’t have an EXCEPT operator either. Over on that part of the ranch, they roll with MINUS.

Fortunately, I’m done with EXCEPT, except, until I do the “SAS is special” operator article.

10 Jan

Oracle Database: SQL Certified Expert (1Z0-047)

I thought I’d take some time and write about this test. There is scant information on the web, only a couple of bloggers, and the rest is the buy a certification garbage that comes along with every certification. So, instead of that, I thought I’d toss out some thoughts and advice on the whole thing.

The first thing that will probably happen, if you are like me at least, is that you’ll look at the test, see it’s on SQL, and think that it’ll probably be pretty easy. Then, if you are also like me, you’ll pick up the book, study it, see all the stuff on basic SQL, and figure that you really, really know it. Trust me on this, unless you have a couple of years working with Oracle SQL, you don’t know it. Here’s why:

I thought I’d take some time and write about this test. There is scant information on the web, only a couple of bloggers, and the rest is the buy a certification garbage that comes along with every certification. So, instead of that, I thought I’d toss out some thoughts and advice on the whole thing.

The first thing that will probably happen, if you are like me at least, is that you’ll look at the test, see it’s on SQL, and think that it’ll probably be pretty easy. Then, if you are also like me, you’ll pick up the book, study it, see all the stuff on basic SQL, and figure that you really, really know it. Trust me on this, unless you have a couple of years working with Oracle SQL, you don’t know it. Here’s why:

First, you can write SQL multiple ways to achieve the same thing. For instance, some “easy” examples, that all produce the same result.

SELECT 1 var
FROM dual;

SELECT MOD(3,2) var
FROM dual;

SELECT id var
FROM (SELECT mod(3,2) id FROM dual);

These all produce (formatting aside):

VAR

1

Now, these are simple examples. You won’t see anything this simple on the test but it illustrates the point that multiple ways can achieve the same result. Now suppose you come from MySQL and want to do these same things:

SELECT 1 VAR; – Oracle automatically puts things in upper case.

SELECT mod(3,2) VAR; or SELECT 3%2 VAR; – Coming from a PHP background I tended to use the % but the function does exist in MySQL.

SELECT id VAR FROM (SELECT 3%2 id) somename; – Yes, you need to name the subquery or you get an error. Oracle, on the other hand, doesn’t care.

Also, note the use of dual. It’s required in Oracle while MySQL could care less.

So, there are multiple ways of doing the same things, not only amongst different databases but even within the same database. And these are easy one’s. The Study Guide shows several ways of add indexes and constraints and you need to know each and every one.

Secondly, if you haven’t worked with Oracle you’ll wander into the exam and start off down a handful of questions because you won’t be tight on Oracle security or what the difference is between user_tables and dba_tables, for instance. It’s pretty easy to get tripped up even if you kind of know the environment but if you aren’t familiar with it at all you may as well skip these questions.

Third, this test isn’t easy. When I did the MySQL exams, I twice ran into questions that it was literally impossible to get wrong. You, in fact, wouldn’t even have had to know a thing about SQL to have gotten them right. There simply was no other answer than the correct answer. That won’t happen here. Be prepared for questions that test multiple concepts and have multiple correct answers. And, if you’ve done the Microsoft exams, be prepared to run into questions that don’t make sense. Most of the questions were solid but not all of them.

Fourth, there can be time pressure and the test software isn’t the best. You have to answer 70 questions in 2 hours, which seems like a lot of time, until you run into something where you get confused by the question and hung up in the schema. Suddenly 5 minutes rolled by and you are behind. Also, the test software isn’t the best for reviewing questions. I hit question 70, wasn’t quite sure, but didn’t make a check mark. At the end of the test, I had a couple of minutes to spare, wanted to go back and double-check it, but couldn’t get back. I had to literally punch the NEXT button 70 times to get there.

How to study for the exam.

First, the official information can be found here. Of course, you’ve probably already been there and possibly even taken the sample test.

But, you don’t really care about that.

The first thing I believe you should do is buy OCA Oracle Database SQL Expert Exam Guide: Exam 1Z0-047. It’s the official study guide and, despite the negative reviews, is enough to pass the exam. But, here’s the catch. You’ll pretty much need to read and memorize the whole book. I mean it. Memorize it, especially the syntax. Also, the sample questions at the end of the exam are not even close to the actual exam. I suspect the source of many of the negative reviews are people who looked at those and thought they knew what was going on. I’d ignore them. The CD exam is tougher but the software isn’t very good. You’ll just have to deal with it.

Just remember that the book is enough, if, and only if, you memorize everything in it and I mean exactly that.

I would say that using Transcender is a toss up on this exam. I thought it was off relative to the test. It seemed like every question wanted to trick you, which is fair, because Oracle will, but Oracle tricks you and asks tough questions. I just didn’t see the value in Transcender for this exam. I’ve had good luck with them in the past, however.

Finally, the online documentation. I found this to be a real hit and miss with Oracle. If you have a concept that you need to flesh out, this is where you should go, in fact, it’s almost the only place you can go. And, it will help, but, it would be very hard to rely on it for this exam as some of it can literally bury you in 100 pages on a single topic.

One last piece of advice. Go into this test ready. Don’t kind of know it, know it, REALLY KNOW IT! It looks like a cute lil’ bunny but it’ll goddamn eat you alive if you aren’t careful.

Good luck with it.