April 4, 2011 at 1:39 pm
i have a table of the Following
CREATE TABLE Customer
(CustNo VARCHAR(6) NOT NULL,
CustName VARCHAR(25),
Custcity VARCHAR(20),
CustState VARCHAR(10),
CustZip VARCHAR(10),
CustBal VARCHAR(10),
CREATE TABLE Order
(OrdNo VARCHAR(4)NOT NULL,
OrdDate VARCHAR(10),
CustNo VARCHAR(6)
FOREIGN KEY (CustNo) REFERENCES Customer);
CREATE TABLE OrdLine
(OrdNo VARCHAR(4)NOT NULL,
ProdNo VARCHAR(10),
OrdQty VARCHAR(10),
FOREIGN KEY (OrdNo) REFERENCES Order
FOREIGN KEY (ProdNo) REFERENCES Product);
CREATE TABLE Product
(ProdNo VARCHAR(10)NOT NULL,
ProdName VARCHAR(25),
ProdColor VARCHAR(10),
OrdQty VARCHAR(10));
Commit;
I can't Insert the Following
1. Insert Into Customer ..... Frequency 100/day
2. Insert into OrdLine..... Frequency 9,000/day
April 4, 2011 at 1:48 pm
I'm not sure what you mean with your frequency data, but I'm assuming you mean that's how many total inserts you expect per day. Or do you mean the inserts work most of the time, but fail that number of times per day? Or something else?
You say you can't insert into the Customer table. Do you get an error message?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2011 at 2:13 pm
Well, what i should say is that i dont know what to put into it. to allow me to add 100 a day
April 4, 2011 at 2:19 pm
davidfoley84 (4/4/2011)
Well, what i should say is that i dont know what to put into it. to allow me to add 100 a day
:ermm: Huh? You've declared a table. You want to put 100 records a day into it, you run 100 insert statements (or some other statement you've written up that does a bunch at once).
If you can be a little more explicit then a 2 sentence answer, we might be able to figure out what you're trying to do and get you on the right path. What you've asked makes no sense to us as a question because I have tables doing 100,000 rows a day... and I didn't do anything special to allow that to happen.
EDIT: Oh, yeah, and this won't work because the syntax is off, you're missing a closing parentheses and have an extra comma:
CREATE TABLE Customer
(CustNo VARCHAR(6) NOT NULL,
CustName VARCHAR(25),
Custcity VARCHAR(20),
CustState VARCHAR(10),
CustZip VARCHAR(10),
CustBal VARCHAR(10),
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 2:32 pm
I was on the Internet looking at SQL Training, and came across this.
CHAPTER 8: QUESTIONS AND ANSWERS
4.For the following tables and SQL statements, select indexes that balance retrieval and update requirements. For each table, justify your choice using the rules discussed in Section 8.5.3.
Customer(CustNo, CustName, CustCity, CustState, CustZip, CustBal)
Order(OrdNo, OrdDate, CustNo)
FOREIGN KEY CustNo REFERENCES Customer
OrdLine(OrdNo, ProdNo, OrdQty)
FOREIGN KEY OrdNo REFERENCES Order
FOREIGN KEY ProdNo REFERENCES Product
Product(ProdNo, ProdName, ProdColor, ProdPrice)
SQL StatementFrequency
1. INSERT INTO Customer . . . 100/day
2. INSERT INTO Product . . . 100/month
3. INSERT INTO Order . . . 3,000/day
4. INSERT INTO OrdLine . . . 9,000/day
5. DELETE Product WHERE ProdNo = $X 100/year
6. DELETE Customer WHERE CustNo = $X 1,000/year
7. SELECT * FROM Order, Customer 300/day
WHERE OrdNo = $X AND Order.CustNo = Customer.CustNo
8. SELECT * FROM OrdLine, Product 300/day
WHERE OrdNo = $X AND OrdLine.ProdNo = Product.ProdNo
9. SELECT * FROM Customer, Order, OrdLine, Product 500/day
WHERE CustName = $X AND OrdDate = $Y
AND Customer.CustNo = Order.CustNo
AND Order.OrdNo = OrdLine.OrdNo
AND Product.ProdNo = OrdLine.ProdNo
10. UPDATE OrdLine SET OrdQty = $X 300/day
WHERE OrdNo = $Y
11. UPDATE Product SET ProdPrice = $X 300/month
WHERE ProdNo = $Y
April 4, 2011 at 2:55 pm
davidfoley84 (4/4/2011)
For the following tables and SQL statements, select indexes that balance retrieval and update requirements. For each table, justify your choice using the rules discussed in Section 8.5.3.
Um, this is a bit different then the question you originally asked. I assume you don't have access to section 8.5.3?
To get you started, look up query tuning and index optimization (or was that index tuning and query optimization? I forget, try all four. 😉 ). This is waaaay to large to tackle in a forum unless you had a particular question about a particular component.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 3:14 pm
well just on how i get 100/day into Customer,
I never did Index before. and from their i'll be able to figure the rest out.
April 4, 2011 at 4:09 pm
davidfoley84 (4/4/2011)
well just on how i get 100/day into Customer,I never did Index before. and from their i'll be able to figure the rest out.
The question is not about how to insert 100 records, it's with dealing with interaction between multiple indexes running against a table and then desiring that volume. You might be better off here with a tutorial course (Google, SQL Tutorial, there's a number of good ones for free out there). You seem to be getting caught up on some basic terms, definitions, and general methodology. I believe you would heavily benefit from a structured tutorial course.
As to how to put 100 customers into the customer table, this should be something that's relatively simple for you to work with as a shell. You'll have to make your own insert statement:
DECLARE @c INT
SET @c = 1
WHILE @c <= 100
BEGIN
-- Insert into statement producing random data.
END
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 5, 2011 at 6:42 am
You don't have to do anything in particular to allow for 100 inserts per day in the Customer table. There's no "rule" that says how many you can/can't do.
What it's asking is for you to come up with an indexing solution that optimizes the tables, based on that section you reference. I haven't read that section (since I don't know what book or site it's on, I really can't discus it much), but I'm going to assume it's about creating indexes and how they affect CRUD operations (look that up).
I'll second the suggestion that you take a look at a tutorial or a beginning book on the subject of SQL Server. That'll get you going in the right direction.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply