|
View previous topic :: View next topic
|
| Author |
Message |
mijanurit
Currently Banned
New User
Joined: 26 Aug 2005
Posts: 33
Location: bangalore
|
|
|
|
With DBADM authority on the database and given the statements:
CREATE TABLE t1 (c1 CHAR(1))
INSERT INTO t1 VALUES ('b')
CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1='a' WITH CHECK OPTION
INSERT INTO v1 VALUES ('a')
INSERT INTO v1 VALUES ('b')
How many rows would be returned from the statement, SELECT c1 FROM t1?
A.0
B.1
C.2
D.3
please tell me , what will be the correct answer..?
I guess B. 1 would be the answer. |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| As your view has a CHECK your 2nd insert would fail because c1<>'a', but you first insert into V1 will be successful..so the total number of rows returned would be 2 ..... |
|
| Back to top |
|
|
rpuhlman
New User
Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio
|
|
|
|
Mijanurit,
The answer is A. It will produce an empty result set.
Also, the last INSERT
| Quote: |
INSERT INTO v1 VALUES ('b')
|
will fail because it does not satisfy the view definition.
Rick |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Quote: |
The answer is A. It will produce an empty result set
|
Even before creating the view there is a row being inserted in the table ... so what about that ?
| Quote: |
INSERT INTO v1 VALUES ('b')
will fail because it does not satisfy the view definition.
|
second INSERT INTO v1 VALUES ('a')
definitely satisfies the view definition wht about that ? |
|
| Back to top |
|
|
rpuhlman
New User
Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio
|
|
|
|
Oops ... sorry about that ... One row will be returned ... Had the wrong select statement.
Rick |
|
| Back to top |
|
|
mijanurit
Currently Banned
New User
Joined: 26 Aug 2005
Posts: 33
Location: bangalore
|
|
|
|
will that view be created on the base table if there is no row in the table satisfing view defination condition ? as for this example...
CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1='a' WITH CHECK OPTION
thanks |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| For creating a view only the table needs to be existing ..its not mandatory that the table has satisfying data or it is empty .... if the table does not have data satisfying view definition the view will return empty result set ... |
|
| Back to top |
|
|
rpuhlman
New User
Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio
|
|
|
|
Ahimer,
That's twice I executed the wrong select. I was selecting against the VIEW ... my bad. The select against T1 would return 2 rows. However, the third INSERT failed with a -166 which was just an FYI and nothing to do with the results ... what about THAT!
Rick |
|
| Back to top |
|
|
rpuhlman
New User
Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio
|
|
|
|
Sorry Ashimer ... spelled your name wrong.
Rick |
|
| Back to top |
|
|
rpuhlman
New User
Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio
|
|
|
|
Ok ... last post from me today ... too busy and in too much of a hurry ... I received a -161 on the third INSERT ... look it up.
Rick |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
 rick that is bound to happen because the third insert violates the view definition ..... and this will fail because of the CHECK option ... |
|
| Back to top |
|
|
mijanurit
Currently Banned
New User
Joined: 26 Aug 2005
Posts: 33
Location: bangalore
|
|
|
|
Thanks all.. thank you so much.. |
|
| Back to top |
|
|
|
|
