| Author |
Message |
Abha
*nix forums beginner
Joined: 19 Jun 2006
Posts: 9
|
 Posted: Wed Jun 28, 2006 4:11 pm Post subject:
Hi! All
|
|
|
Can anyone send me SQL Question papers as I am preparing for OCP
Examination. |
|
| Back to top |
|
 |
psoug
*nix forums Guru
Joined: 15 May 2005
Posts: 3492
|
| Posted: Thu Jun 29, 2006 3:42 pm Post subject:
Re: Hi! All
|
|
|
Abha wrote:
| Quote: | Can anyone send me SQL Question papers as I am preparing for OCP
Examination.
|
Sure ... here's my favorite.
You have two tables that are absolutely identical in structure. Each
contains rows of data ... some of which are identical between the
tables and some of which are not. Find the rows that are distinct and
in one query return the distinct rows from both tables and the name
of the table that contained them.
CREATE TABLE t1 (
col1 VARCHAR2(5));
CREATE TABLE t2 (
col1 VARCHAR2(5));
INSERT INTO t1 VALUES ('A');
INSERT INTO t1 VALUES ('B');
INSERT INTO t1 VALUES ('C');
INSERT INTO t2 VALUES ('A');
INSERT INTO t2 VALUES ('C');
INSERT INTO t2 VALUES ('D');
COMMIT;
The result from the single query should be:
A T1
D T2
Enjoy!
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org |
|
| Back to top |
|
|
Michel Cadot
*nix forums Guru
Joined: 24 Apr 2005
Posts: 457
|
| Posted: Thu Jun 29, 2006 4:09 pm Post subject:
Re: Hi! All
|
|
|
"DA Morgan" <damorgan@psoug.org> a écrit dans le message de news: 1151595765.559@bubbleator.drizzle.com...
| Abha wrote:
| > Can anyone send me SQL Question papers as I am preparing for OCP
| > Examination.
|
| Sure ... here's my favorite.
|
| You have two tables that are absolutely identical in structure. Each
| contains rows of data ... some of which are identical between the
| tables and some of which are not. Find the rows that are distinct and
| in one query return the distinct rows from both tables and the name
| of the table that contained them.
|
| CREATE TABLE t1 (
| col1 VARCHAR2(5));
|
| CREATE TABLE t2 (
| col1 VARCHAR2(5));
|
| INSERT INTO t1 VALUES ('A');
| INSERT INTO t1 VALUES ('B');
| INSERT INTO t1 VALUES ('C');
| INSERT INTO t2 VALUES ('A');
| INSERT INTO t2 VALUES ('C');
| INSERT INTO t2 VALUES ('D');
| COMMIT;
|
| The result from the single query should be:
|
| A T1
| D T2
|
| Enjoy!
| --
| Daniel A. Morgan
| University of Washington
| damorgan@x.washington.edu
| (replace x with u to respond)
| Puget Sound Oracle Users Group
| www.psoug.org
I think the result is:
B T1
D T2
Can I compete? ;-)
Regards
Michel Cadot |
|
| Back to top |
|
|
psoug
*nix forums Guru
Joined: 15 May 2005
Posts: 3492
|
| Posted: Mon Jul 03, 2006 3:42 pm Post subject:
Re: Hi! All
|
|
|
Michel Cadot wrote:
| Quote: | "DA Morgan" <damorgan@psoug.org> a écrit dans le message de news: 1151595765.559@bubbleator.drizzle.com...
| Abha wrote:
| > Can anyone send me SQL Question papers as I am preparing for OCP
| > Examination.
|
| Sure ... here's my favorite.
|
| You have two tables that are absolutely identical in structure. Each
| contains rows of data ... some of which are identical between the
| tables and some of which are not. Find the rows that are distinct and
| in one query return the distinct rows from both tables and the name
| of the table that contained them.
|
| CREATE TABLE t1 (
| col1 VARCHAR2(5));
|
| CREATE TABLE t2 (
| col1 VARCHAR2(5));
|
| INSERT INTO t1 VALUES ('A');
| INSERT INTO t1 VALUES ('B');
| INSERT INTO t1 VALUES ('C');
| INSERT INTO t2 VALUES ('A');
| INSERT INTO t2 VALUES ('C');
| INSERT INTO t2 VALUES ('D');
| COMMIT;
|
| The result from the single query should be:
|
| A T1
| D T2
|
| Enjoy!
| --
| Daniel A. Morgan
| University of Washington
| damorgan@x.washington.edu
| (replace x with u to respond)
| Puget Sound Oracle Users Group
| www.psoug.org
I think the result is:
B T1
D T2
Can I compete? ;-)
Regards
Michel Cadot
|
So much for typing rather than cut and paste. Anyone want to post their
answer?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org |
|
| Back to top |
|
|
Marino Ljubic
*nix forums beginner
Joined: 10 May 2005
Posts: 20
|
| Posted: Tue Jul 04, 2006 1:59 pm Post subject:
Re: Hi! All
|
|
|
"DA Morgan" <damorgan@psoug.org> wrote in message
news:1151941370.867909@bubbleator.drizzle.com
| Quote: |
So much for typing rather than cut and paste. Anyone want to post
their answer?
|
There are several SQL queries that can achieve the result, 3 as I can think
of at this moment. What's the point? Exercise? |
|
| Back to top |
|
|
psoug
*nix forums Guru
Joined: 15 May 2005
Posts: 3492
|
| Posted: Tue Jul 04, 2006 3:42 pm Post subject:
Re: Hi! All
|
|
|
Marino Ljubic wrote:
| Quote: | "DA Morgan" <damorgan@psoug.org> wrote in message
news:1151941370.867909@bubbleator.drizzle.com
So much for typing rather than cut and paste. Anyone want to post
their answer?
There are several SQL queries that can achieve the result, 3 as I can think
of at this moment. What's the point? Exercise?
|
Whatever. Here are the first 3 that popped into my head.
SELECT col1, 'T1'
FROM t1
MINUS
SELECT col1, 'T1'
FROM t2
UNION
SELECT col1, 'T2'
FROM t2
MINUS
SELECT col1, 'T2'
FROM t1;
----------------------------------------
SELECT col1, 'T1'
FROM t1
WHERE col1 NOT IN (SELECT col1 FROM t2)
UNION
SELECT col1, 'T2'
FROM t2
WHERE col1 NOT IN (SELECT col1 FROM t1);
----------------------------------------
SELECT col1, 'T1'
FROM t1
WHERE NOT EXISTS (
SELECT col1
FROM t2
WHERE t1.col1 = t2.col1)
UNION
SELECT col1, 'T2'
FROM t2
WHERE NOT EXISTS (
SELECT col1
FROM t1
WHERE t1.col1 = t2.col1);
----------------------------------------
Your three?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org |
|
| Back to top |
|
|
Marino Ljubic
*nix forums beginner
Joined: 10 May 2005
Posts: 20
|
| Posted: Wed Jul 05, 2006 8:44 am Post subject:
Re: Hi! All
|
|
|
"DA Morgan" <damorgan@psoug.org> wrote in message
news:1152027738.776684@bubbleator.drizzle.com
| Quote: | Marino Ljubic wrote:
There are several SQL queries that can achieve the result, 3 as I
can think of at this moment. What's the point? Exercise?
Whatever. Here are the first 3 that popped into my head.
.... |
Since you provided the most logical answers, I can only give a few more
complex for statistics...
select col1, max(tab) tab
from
(
select col1, 'T1' tab from t1
union all
select col1, 'T2' from t2
)
group by col1
having count(*) = 1
----------------------------------------
select col1, tab from (
select col1, tab, count(*) over (partition by col1) num
from
(
select col1, 'T1' tab from t1
union all
select col1, 'T2' from t2
)
)
where
num = 1
----------------------------------------
select r.col1, r.tab from
(
select col1, 'T1' tab from t1
union all
select col1, 'T2' from t2
) r,
(
select col1 from
(
select col1, 'T1' tab from t1
union all
select col1, 'T2' from t2
)
group by col1
having count(*) = 1
) c
where r.col1 = c.col1
I'm sure there's more, but my playtime is up...  |
|
| Back to top |
|
|
ChrisF
*nix forums beginner
Joined: 31 Oct 2005
Posts: 15
|
| Posted: Wed Jul 05, 2006 4:02 pm Post subject:
Re: Hi! All
|
|
|
Can I play too?
SELECT T1.COL1, 'T1' FROM T1, T2
WHERE T1.COL1 = T2.COL1 (+) AND T2.COL1 IS NULL
UNION
SELECT T2.COL1, 'T2' FROM T1, T2
WHERE T2.COL1 = T1.COL1 (+) AND T1.COL1 IS NULL;
"NOT IN" constructs can be inefficient.
Doing left/right joins and excluding the NULL values is in my
experience faster. |
|
| Back to top |
|
|
Marino Ljubic
*nix forums beginner
Joined: 10 May 2005
Posts: 20
|
| Posted: Thu Jul 06, 2006 8:27 am Post subject:
Re: Hi! All
|
|
|
"ChrisF" <chris.freel@gmx.ch> wrote in message
news:1152115368.636954.123810@75g2000cwc.googlegroups.com
Why not? :-)
| Quote: | SELECT T1.COL1, 'T1' FROM T1, T2
WHERE T1.COL1 = T2.COL1 (+) AND T2.COL1 IS NULL
UNION
SELECT T2.COL1, 'T2' FROM T1, T2
WHERE T2.COL1 = T1.COL1 (+) AND T1.COL1 IS NULL;
"NOT IN" constructs can be inefficient.
Doing left/right joins and excluding the NULL values is in my
experience faster.
|
Yes it is, especially on indexed columns. As well as 'NOT EXISTS'.
Just for information, you can also try this subquery syntax used by ad-hoc
query generators, but it shouldn't be any faster.
SELECT col1, 'T1' tab
FROM t1
WHERE NOT col1 = ANY (
SELECT col1
FROM t2
)
UNION ALL
SELECT col1, 'T2'
FROM t2
WHERE NOT col1 = ANY (
SELECT col1
FROM t1
); |
|
| Back to top |
|
|
Andrey Odegov
*nix forums beginner
Joined: 28 Apr 2006
Posts: 6
|
| Posted: Thu Jul 06, 2006 12:13 pm Post subject:
Re: Hi! All
|
|
|
DA Morgan wrote:
| Quote: | Abha wrote:
Can anyone send me SQL Question papers as I am preparing for OCP
Examination.
Sure ... here's my favorite.
You have two tables that are absolutely identical in structure. Each
contains rows of data ... some of which are identical between the
tables and some of which are not. Find the rows that are distinct and
in one query return the distinct rows from both tables and the name
of the table that contained them.
CREATE TABLE t1 (
col1 VARCHAR2(5));
CREATE TABLE t2 (
col1 VARCHAR2(5));
INSERT INTO t1 VALUES ('A');
INSERT INTO t1 VALUES ('B');
INSERT INTO t1 VALUES ('C');
INSERT INTO t2 VALUES ('A');
INSERT INTO t2 VALUES ('C');
INSERT INTO t2 VALUES ('D');
COMMIT;
The result from the single query should be:
A T1
D T2
Enjoy!
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
how about the full outer join approach?
SELECT CASE WHEN T1.col1 IS NULL THEN T2.col1 ELSE T1.col1 END col1,
CASE WHEN T1.col1 IS NULL THEN 'T2' ELSE 'T1' END table_nm
FROM T1
FULL OUTER JOIN T2
ON T2.col1 = T1.col1
WHERE T1.col1 IS NULL OR T2.col1 IS NULL
The first CASE expression can be replaced
with a call to the COALESCE function:
COALESCE(T1.col1, T2.col1)
---
Andrey Odegov
avodeGOV@yandex.ru
(remove GOV to respond) |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
»
Tools
