Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'm trying to answer a question on Stack Overflow, and I want to test the answer to see if it's correct. I don't have Oracle, though, so I'm using sqlfiddle.com.

Here is the query: http://sqlfiddle.com/#!4/b18a2/11.

Where can I see the result? The result should be '1' (employee 1).

share|improve this question

migrated from superuser.com Nov 5 '12 at 9:00

This question came from our site for computer enthusiasts and power users.

add comment

2 Answers

up vote 2 down vote accepted

You see the result in the lower part of the screen. Your query, however, doesn't return any records(you've created the tables, but not inserted any data) and hence there's no result shown

enter image description here

share|improve this answer
    
Oh, I see! Thank you. ^^ –  Wolfpack'08 Nov 5 '12 at 9:18
    
Actually, I can't get results for anything. I inserted two entries, so it's a little odd, right? sqlfiddle.com/#!4/b18a2/16 –  Wolfpack'08 Nov 5 '12 at 9:22
1  
@Wolfpack'08 you haven't inserted anything. This is how it should look if you did sqlfiddle.com/#!4/b18a2/20 –  Sathya Nov 6 '12 at 6:15
    
if you remove the insert statements, are the rows removed? –  Wolfpack'08 Nov 6 '12 at 8:46
    
@Wolfpack'08 the data isn't persistently stored; the objects are recreated each time you run the query. –  Sathya Nov 6 '12 at 9:13
show 2 more comments

In addition to the answer from Sathya, there are a couple of things wrong with your query.

  1. You need to insert some data like this:

    Insert into employees (Employee, Name, Email)
    Values (1, 'joebloggs','joebloggs@somewhere.com');
    
  2. I don't think your query will work anyway as you need to concatenate some % signs in there too, this query does work

    Select Employee
    From Employees
    Where email like '%' || name || '%';
    
  3. You also need to change your datatypes for the name column from char(25) to varchar(25) using char(25) I think, pads the datatype with spaces so your like comparison won't match.

This type of thing always depends on the data. You are right it is kinda stange having the name as one word. To get around that you need to change your query to split the email address on the period.

So your insert would be :

    Insert into employees (Employee, Name, Email)
    Values (1, 'joe bloggs','joe.bloggs@somewhere.com');

And your select would be:

    Select Employee
    From Employees
    Where Replace(email, '.' ,' ') like '%' || name || '%';

Obviously this means that names must only be in the format of FirstName{space}LastName and that email addresses must only be in the format of FirstName{dot}LastName@whatever.com

I would be wary of doing anything like this unless you are 100% sure that the data will always be in the specified format.

share|improve this answer
    
But having the name as all one word is strange, too, right? –  Wolfpack'08 Nov 5 '12 at 9:27
    
@Wolfpack'08 See my edit –  Barry Nov 5 '12 at 9:32
    
Really, I'm just trying to see some more in-depth uses of SQL. The original/actual question was 'how do I get a list of employees whose names exists in their e-mail addresses in Oracle'? I'm trying to apply what you've written to sqlfiddle, right now. I can't tell if you're using variables.... Is name, on the last line, a reference to the name row of the employees table? Neither of the queries return any results for me. sqlfiddle.com/#!4/b18a2/18 –  Wolfpack'08 Nov 5 '12 at 9:50
    
@Wolfpack'08 You need to add the insert statement to the build schema section. You don't have data in your table. There are no variables being used here. –  Barry Nov 5 '12 at 9:59
    
And you need to change your schema as I mentioned in my answer –  Barry Nov 5 '12 at 10:00
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.