SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hello, need help with some basic questions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2asQyu2al
Starting Member

2 Posts

Posted - 06/16/2013 :  11:17:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 06/16/2013 :  12:39:09  Show Profile  Reply with Quote
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 - 06/16/2013 :  13:58:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/17/2013 :  00:48:39  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/17/2013 :  01:03:07  Show Profile  Reply with Quote

f INT CHECK(f IN(SELECT a FROM R1))); 

should be implemented as

f INT FOREIGN KEY REFERENCES R(a)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000