Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hello, need help with some basic questions

Author  Topic 

2asQyu2al
Starting Member

2 Posts

Posted - 2013-06-16 : 11:17:13
I don't know the first thing about SQL. So don't assume I already know anything when you're writing answer. I know I'm asking way too much for free, but time is running out.


CREATE TABLE R1 (
a INT PRIMARY KEY,
b INT);
CREATE TABLE R2 (
c INT PRIMARY KEY,
d INT PREFERENCES R1(a));
CREATE TABLE R3 (
e INT PRIMARY KEY,
f INT CHECK(f IN(SELECT a FROM R1)));

Assume that the table R1 contains 3 rows: (1,10),(2,10), and (3,20), while R2 and R3 are empty. Which of the following sequences of statements would not be allowed by SQL?

(a)
INSERT INTO R3 VALUES(5,2);
DELETE FROM R1 WHERE a=2;
INSERT INTO R2 VALUES(1,1);

(b)

INSERT INTO R2 VALUES(1,1);
DELETE FROM R1 WHERE a=2;
INSERT INTO R3 VALUES(6,1);

(c)

INSERT INTO R3 VALUES(6,1);
UPDATE R1 SET a=4 WHERE a=3;
INSERT INTO R2 VALUES(10,2);

(d)

DELETE FROM R1 WHERE a=2;
INSERT INTO R2 VALUES(1,1);
INSERT INTO R3 VALUES(5,2);

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-16 : 12:39:09
Usually people on the forum are reluctant to answer homework questions and such; many of them would help if you show the work you already did and can't get past some stumbling block. But, most if not all of them stay away from just giving away the answers.
Go to Top of Page

2asQyu2al
Starting Member

2 Posts

Posted - 2013-06-16 : 13:58:13
This isn't homework. It's an exercise from an exam from the previous year. And I'm in a university not a school, there is no such thing as homework in a university. And most importantly, I think the best way to understand a subject is by understanding the exercises. So please help! I don't actually need the direct answer, just an explanation of the exercise and how to solve it.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 00:48:39
1) Before answering above questions, one thing you should get to know.....
CREATE TABLE R1 (
a INT PRIMARY KEY,
b INT);
CREATE TABLE R2 (
c INT PRIMARY KEY,
d INT REFERENCES R1(a));
CREATE TABLE R3 (
e INT PRIMARY KEY,
f INT CHECK(f IN(SELECT a FROM R1))); -- here we can't use sub queries in CHECK constraint

2) Your question is related to FOREIGN KEY and CHECK constraints... If you get basic idea on fundamentals then answer is very easy...

Refer the following links
http://www.w3schools.com/sql/sql_foreignkey.asp
http://www.w3schools.com/sql/sql_check.asp
http://beginner-sql-tutorial.com/sql-integrity-constraints.htm

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 01:03:07
[code]
f INT CHECK(f IN(SELECT a FROM R1)));

should be implemented as

f INT FOREIGN KEY REFERENCES R(a)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -