A good entry-level interview question
I think this would make a reasonable question for would-be database developers:
What are the differences between the following statements?
UPDATE mytable SET mycolumn = NULL;
ALTER TABLE mytable MODIFY (mycolumn NULL);
If they can’t answer this correctly, they don’t get to stage two.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Scott Wesley
17 March 2010 - 5:49 am
I sense a background story to this… 🙂
Jeffrey Kemp
17 March 2010 - 6:26 am
I was trying to think of a good background story to this, but I'm just not that creative :)It's just something that popped into my head – I was typing the "ALTER TABLE" today, and thought, "I bet this would confuse someone who didn't know the difference between DML and DDL".So, I'm a bit boring today.
Connor McDonald
18 March 2010 - 1:16 am
Today's story – there was I pontificating about array fetch / bulk collect etc etc … when someone asks:"Excuse me, what's that SQL extension you are using at the bottom of the SQL. I've never seen it…"….. (long pause by me….)"Um…That would be the GROUP BY clause"Hmmmmm………
Jeffrey Kemp
18 March 2010 - 1:43 am
:)That's another good interview question: "Explain the difference between ORDER BY and GROUP BY."For advanced developers: "Is an ORDER BY required if a statement to be sorted already has a GROUP BY? Why?"
Anonymous
19 March 2010 - 8:51 pm
On a totally separate note….Jeff you need to update your movie list in your profile! It reads 'Geek'. 😉 Being a fellow iced-coffee lover I would hate for folks to associate the two together!
Jeffrey Kemp
20 March 2010 - 12:08 am
Hello Anonymous!I haven't checked my profile for ages – but not much has changed, really. Anyway, if I can convert just one iced-coffee-afficionado into a geek, or vice-versa, I will consider my mission to be complete 🙂
Anonymous
23 March 2010 - 7:43 pm
Hi Jeff. This is Anonymous. Good answer! But at least throw a Clint Eastwood movie in there. 😉 Ok Ok I'd settle for 'My Cousin Vinny'!
Laurent Schneider
6 April 2010 - 9:11 pm
stage two :
ALTER TABLE t MODIFY x CONSTRAINT c NULL;
in which dictionary view(s) will you find the constraint c ?
😆
Jeffrey Kemp
6 April 2010 - 9:52 pm
@Laurent: no fair!
Jeffrey Kemp
15 April 2010 - 12:24 pm
Another one:
CREATE TABLE x ( stringid VARCHAR2(10) );
INSERT INTO x VALUES (‘3’);
INSERT INTO x VALUES (‘1’);
INSERT INTO x VALUES (‘2’);
Q. Given the table above, what is the difference, if any, between the following two queries?
SELECT stringid FROM x ORDER BY 1;
SELECT stringid FROM x ORDER BY TO_NUMBER(1);
Jeffrey Kemp
16 April 2010 - 10:39 am
INSERT INTO x SELECT * FROM x;
Q. What will happen if the above statement is executed?
a) The insert will run successfully.
b) The insert will not run – an exception will be raised immediately.
c) The insert will insert many copies of the original rows in x, and will eventually raise an exception when the table space quota is full.