8 Replies - 483 Views - Last Post: 09 November 2017 - 09:39 AM

#1 gbertoli3   User is offline

  • DIC at Heart + Code
  • member icon

Reputation: 41
  • View blog
  • Posts: 1,166
  • Joined: 23-June 08

IF Statement on Single Query

Posted 07 November 2017 - 08:19 PM

So I haven't been active on here in a while, but I'm taking an Oracle SQL class and I've been looking through the text book and I can't seem to find the answer.

Here is the question:
Write a SQL query, which shows a list of all employees last name, their department names and their department city where that department is located from the employees table. If an employee does not belong to a department, then their department number should show as “N/A” and their department city should show up as “No City info Found”. MAKE SURE you have all the employees displayed including the employees who do not have a department.

Here is the code I've come up with:
SELECT e.last_name, d.department_name, l.city FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l WHERE e.department_id = d.department_id AND d.location_id = l.location_id;



Everything works except I cannot figure out how to return the supplied "No City Info Found" or "N/A" if null and the correct values if found. I've looked into using: BEGIN, DECLARE, IF/THEN, and I can't seem to code it correctly. I know there are a few SQL languages and I'm thinking that is why I am getting confused when I look this up. Everything I've seen online uses multiple lines, but so far in my textbook we only use 1 query for every problem / answer so I think I've been looking in the wrong place. I'm using Oracle SQL Developer version 4.1.5.21 if that helps.

EDIT:
I think using the IF statement might actually be incorrect because everything I see is multiline. So I know we've been learning about EXISTS and NOT EXISTS. Is there a way to use NOT EXISTS with this?

Basically I'm wondering if this is possible:
SELECT e.last_name, d.department_name, l.city FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l WHERE e.department_id = d.department_id AND d.location_id = l.location_id EXISTS e.department_id AND NOT EXISTS e.department_id := 'Not Found';



Or something similar to that.

Thanks guys!

This post has been edited by gbertoli3: 07 November 2017 - 09:33 PM


Is This A Good Question/Topic? 0
  • +

Replies To: IF Statement on Single Query

#2 andrewsw   User is online

  • quantum multiprover
  • member icon

Reputation: 6792
  • View blog
  • Posts: 28,046
  • Joined: 12-December 12

Re: IF Statement on Single Query

Posted 08 November 2017 - 12:55 AM

Where are the JOINs?

"MAKE SURE you have all the employees displayed including the employees who do not have a department."

Is your output showing all employees, and the NULL values you want to work with?

Concentrate on displaying all the data you need, then you can substitute text for NULLs.

(I am not sure what you are referring to with 'multiline'.)
Was This Post Helpful? 0
  • +
  • -

#3 gbertoli3   User is offline

  • DIC at Heart + Code
  • member icon

Reputation: 41
  • View blog
  • Posts: 1,166
  • Joined: 23-June 08

Re: IF Statement on Single Query

Posted 08 November 2017 - 08:28 AM

Ok, I'll relook at my book at the joins thank you.

As for multiline I've seen this with my research online:
CREATE OR REPLACE Function IncomeLevel
   ( name_in IN varchar2 )
   RETURN varchar2
IS
   monthly_value number(6);
   ILevel varchar2(20);

   cursor c1 is
     SELECT monthly_income
     FROM employees
     WHERE name = name_in;

BEGIN

   open c1;
   fetch c1 into monthly_value;
   close c1;

   IF monthly_value <= 4000 THEN
      ILevel := 'Low Income';

   ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
      ILevel := 'Avg Income';

   ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
      ILevel := 'Moderate Income';

   ELSE
      ILevel := 'High Income';

   END IF;

   RETURN ILevel;

END;


This is what I've seen. Is this the same SQL that I'm using?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is online

  • quantum multiprover
  • member icon

Reputation: 6792
  • View blog
  • Posts: 28,046
  • Joined: 12-December 12

Re: IF Statement on Single Query

Posted 08 November 2017 - 08:34 AM

That is still SQL, but it is programmatic SQL that creates a function.

You are building and executing a single SQL (select) statement.

(It is possible to format your select statement across several lines - depending on how and where you will execute it - but it will still be a single statement that you are executing.)
Was This Post Helpful? 0
  • +
  • -

#5 gbertoli3   User is offline

  • DIC at Heart + Code
  • member icon

Reputation: 41
  • View blog
  • Posts: 1,166
  • Joined: 23-June 08

Re: IF Statement on Single Query

Posted 08 November 2017 - 10:25 PM

Ok, I've figured out my join. Here is my code:
SELECT e.last_name, d.department_name, l.city FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l WHERE e.department_id (+) = d.department_id AND d.location_id = l.location_id;



So how can I substitute text for a null value?

:::EDIT:::

Ok so I've figured out how to display text if there is a null value in a specific column:
SELECT e.last_name "Last Name", d.department_name "Department", l.city "City", nvl(TO_CHAR(e.department_id), 'N/A') "Department ID" FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l WHERE e.department_id (+) = d.department_id AND d.location_id = l.location_id;

Now how can I display a specified text on Column "City" when the column "Department ID" is NULL?

Thanks

This post has been edited by gbertoli3: 08 November 2017 - 10:33 PM

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is online

  • quantum multiprover
  • member icon

Reputation: 6792
  • View blog
  • Posts: 28,046
  • Joined: 12-December 12

Re: IF Statement on Single Query

Posted 09 November 2017 - 12:44 AM

That (+) notation for an outer join is discouraged apparently.

"Now how can I display a specified text on Column "City" when the column "Department ID" is NULL?"

Is that what the question is asking? Seems a little odd that this would disguise information; that a lack of Department would dissolve a City. Still, the question does say "department city"(??).
Was This Post Helpful? 0
  • +
  • -

#7 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7429
  • View blog
  • Posts: 15,399
  • Joined: 16-October 07

Re: IF Statement on Single Query

Posted 09 November 2017 - 03:41 AM

View Postandrewsw, on 08 November 2017 - 10:34 AM, said:

That is still SQL, but it is programmatic SQL that creates a function.


I would disagree. There is SQL and then there are RDMBS procedural extensions: not the same critter, no such thing as "programmatic SQL." In Oracle, you have the distinct PL/SQL.

View Postgbertoli3, on 09 November 2017 - 12:25 AM, said:

Ok, I've figured out my join. Here is my code:
SELECT e.last_name, d.department_name, l.city FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l WHERE e.department_id (+) = d.department_id AND d.location_id = l.location_id;



Let's bring that code into this current century.
SELECT e.last_name, d.department_name, l.city 
  FROM EMPLOYEES e
    LEFT JOIN DEPARTMENTS d ON e.department_id = d.department_id
    JOIN LOCATIONS l ON d.location_id = l.location_id



This immediately highlights that you have an outer join, but then do an inner join on an outer joined table. That may not work...

View Postgbertoli3, on 09 November 2017 - 12:25 AM, said:

Ok so I've figured out how to display text if there is a null value in a specific column:
SELECT e.last_name "Last Name", d.department_name "Department", l.city "City", nvl(TO_CHAR(e.department_id), 'N/A') "Department ID" FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l WHERE e.department_id (+) = d.department_id AND d.location_id = l.location_id;


Good, you found Oracle's NVL function. Also, look up the more general SQL CASE statement. Let's go with another refactor:
SELECT 
    e.last_name as "Last Name", 
    d.department_name as "Department", 
    l.city as "City", 
    nvl(TO_CHAR(e.department_id), 'N/A') as "Department ID" 
  FROM EMPLOYEES e
    LEFT JOIN DEPARTMENTS d ON e.department_id = d.department_id
    JOIN LOCATIONS l ON d.location_id = l.location_id



If e.department_id is NULL, so is d.department_name and l.city. Why are you showing the TO_CHAR id? Still don't like that outer / inner join confusion...

Here's how I'd go about it.

Step one, let's just get departments and locations:
SELECT d.department_id, d.department_name, l.city
  FROM DEPARTMENTS d
    JOIN LOCATIONS l
      ON d.location_id = l.location_id



Here, we are assuming that d.location_id has a NOT NULL constraint and a foreign key constraint: i.e. all departments have cities.

Now we have that, I'm going to introduce a new tool: the sub query. This is something you will use a lot.

With that, we can move down to one outer join:
SELECT 
    a.last_name as "Last Name", 
    NVL(b.department_name, 'N/A') as "Department",
    NVL(b.city, 'N/A') as "City"
  FROM EMPLOYEES a
    LEFT JOIN (
      SELECT d.department_id, d.department_name, l.city
        FROM DEPARTMENTS d
          JOIN LOCATIONS l ON d.location_id = l.location_id
      ) b ON a.department_id = b.department_id



As an aside, I'm formatting this SQL in ways I find easy to read. While you needn't follow my formatting, a single line of SQL is generally the least legible. Find a format that works for you.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is online

  • quantum multiprover
  • member icon

Reputation: 6792
  • View blog
  • Posts: 28,046
  • Joined: 12-December 12

Re: IF Statement on Single Query

Posted 09 November 2017 - 04:13 AM

View Postbaavgai, on 09 November 2017 - 10:41 AM, said:

View Postandrewsw, on 08 November 2017 - 10:34 AM, said:

That is still SQL, but it is programmatic SQL that creates a function.


I would disagree. There is SQL and then there are RDMBS procedural extensions: not the same critter, no such thing as "programmatic SQL." In Oracle, you have the distinct PL/SQL.

I didn't say it was "a thing", I was using a descriptive phrase. As procedural extensions enable conditions and loops I'm happy that it is a useful phrase.

But we won't quibble ;)
Was This Post Helpful? 0
  • +
  • -

#9 gbertoli3   User is offline

  • DIC at Heart + Code
  • member icon

Reputation: 41
  • View blog
  • Posts: 1,166
  • Joined: 23-June 08

Re: IF Statement on Single Query

Posted 09 November 2017 - 09:39 AM

@baavgai

Thank you, the code executes perfectly. Now where can I learn more about joins? I'm trying to read my textbook but I don't really understand it.

This post has been edited by gbertoli3: 09 November 2017 - 09:39 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1