Home > DBA, Oracle, SQL > Mailbag: SQL Exercises

Mailbag: SQL Exercises


Hello Iggy,

I recently bought your book, Beginning Oracle Database 11g Administration: From Novice to Professional.  I took a promotion with my organization to be the DBA, but I have precious little experience in the field. Your line in the introduction, “If you are an IT professional who has been thrust into an Oracle Database administration role without the benefit of formal training … then I wrote this book for you” suits me exactly. Now I have been to one training session and there are more to come, but this book looks like exactly what I need to get a good start on my new career.

I have obviously started reading your book and I have a question for you. I understand the end of chapter exercises, but where do I go to verify that my SQL statements are more or less correct? I want to take full advantage of this book and I don’t want to get off on the wrong foot.

Thank you for your time,

C. M. [name withheld]

Hello C.,

I apologize for the delay in my reply. I have a similar story. My first IT job was COBOL programming and C programming. Then a DBA position opened up and I convinced my manager to let me try my hand at it. Several years later, the Oracle DBA quit and another manager offered me the chance to learn Oracle. My hiring philosophy is that we should simply give the smartest people the chance and then buy them as many textbooks as they want!

The SQL exercise at the end of Chapter 2 is to rewrite the SQL statements in Listings 2–22 through 2–28 by using the NOT EXISTS clause instead of the MINUS operator and without using subquery factoring. You can download all the source code in my book from the publisher’s website. This will allow you to verify that your queries produce the same results as the listings in Chapter 2.

If you would like to continue studying SQL, I can recommend the recently revised Beginning Oracle SQL (Apress, December 2009) by Lex de Haan, Tim Gorman, Karen Morton, and Daniel Fink.

Best of luck in your new assignment,

Iggy

Mailbag: Advice for an Oracle 9i DBA
Mailbag: Is It Too Late For Me To Start
Mailbag: Is This Book For Me?

Categories: DBA, Oracle, SQL
  1. March 6, 2010 at 5:52 am

    I would like to know your opinion about online sql exercises: http://www.sql-ex.ru/.

  2. Iggy Fernandez
    March 6, 2010 at 11:51 pm

    Hello Serge,

    Congratulations on your site. I think it is a great idea because SQL requires a lot of practice. I hope that you add Oracle Database to your site soon; you can use Oracle Database Express Edition (XE) free of charge.

    I attempted one of the exercises and found the English translation of the Russian version a little hard to understand. The problem was to find the battles in which at least three ships from the same country took part. The explanation read as follows:

    Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database. The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed – OK) is in the Outcomes relation. Note: the Outcomes relation may include the ships not included in the Ships relation.

    The problem is from the book A First Course in Database Systems by Ullman. The original text from Ullman’s book reads as follows:

    Ships are built in “classes” from the same design, and the class is usually named for the first ship of that class. The relation Classes records the name of the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tons). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of battles involving these ships and relation Outcomes gives the result (sunk, damaged, or ok) for each ship in each battle.

    The details of the relations are as follows:

    Classes(class, type, country, numGuns, bore, displacement)
    Ships(name, class, launched)
    Battles(name, date)
    Outcomes(ship, battles, result)

    Here was my first solution:

    SELECT DISTINCT battle
    FROM
      (
        SELECT 
          o.battle,
          c.country,
          COUNT (*) AS count
        FROM outcomes o
        INNER JOIN ships s ON s.name = o.ship
        INNER JOIN classes c ON c.class = s.class
        GROUP BY o.battle, c.country
        HAVING count (*) >= 3
      ) t
    

    I received the following error message:

    Your query produced correct result set on main database, but it failed test on second, checking database.
    * Wrong number of records (less by 2)
    This exercise has FAQ

    Upon reading the FAQ, I realized that the foreign-key relationship between Outcomes and Ships was missing. Also, that an attempt should be made to determine the ship’s class from the Classes relation if it could not be determined from the Ships relation since a class is normally assigned the name of the first ship in the class under consideration. (I had not noticed the last line of the problem statement which read “the Outcomes relation may include the ships not included in the Ships relation.”) It seemed unorthodox to me but I modified my solution to use left joins as follows:

    SELECT DISTINCT battle
    FROM
      (
        SELECT
          o.battle,
          c.country,
          COUNT (*) AS count
        FROM outcomes o
        LEFT JOIN ships s ON s.name = o.ship
        LEFT JOIN classes c ON c.class = COALESCE(s.class, o.ship)
        WHERE c.country IS NOT NULL
        GROUP BY o.battle, c.country
        HAVING COUNT (*) >= 3
      ) t
    

    That worked. Congratulations once again on your site.

    Iggy

  3. March 7, 2010 at 1:40 am

    Hello Iggy,

    > I hope that you add Oracle Database to your site soon; you can use Oracle Database Express Edition (XE) free of charge.

    I know, but our hoster had not Oracle installed to this time. I’ll plan Oracle if circumstances will allow it.

    > The original text from Ullman’s book reads as follows:

    Thank you. I could not find the original. I’ll correct it, but the words “A class is NORMALLY assigned the name of the first ship in the class” adopt ambiguous understanding of a HEAD ship. In particular, does the query

    select class from classes

    give all the head ships? My understanding that I wrote in the description says NO – head ship must be present in the tables of DB.
    This makes the queries a little more difficult.

    > I realized that the foreign-key relationship between Outcomes and Ships was missing.

    Yes, but this corresponds to Ullman’s schema.🙂
    Many people ask about it, and I’d tired when answering them.🙂

    By the way, I should prefer no to give here a correct answers to the exercises.🙂
    I’ll be glad to discuss solutions by email.

    > Congratulations once again on your site.

    Thank you for kind words.

    Serge

  4. February 4, 2013 at 1:55 am

    Hello Iggy,
    >you can use Oracle Database Express Edition (XE) free of charge.

    We have done it: http://sql-ex.ru/exercises/index.php?act=learn

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: