[Thursday, April 13, 16:25] > I was working on homework 10 and was wondering if you could let me know > what I'm doing wrong. This is a question on the part a, building the > table. > > Thanks. SQL> CREATE TABLE RESTAURANTS( > 2 RID NUMERIC(8) CHECK(RID>0), > 3 RNAME VARCHAR(20) NOT NULL, > 4 OWNER VARCHAR(20) > 5 PRIMARY KEY (RID)) > 6 ; > PRIMARY KEY (RID)) > * > ERROR at line 5: > ORA-00907: missing right parenthesis A comma is missing at the end of line 4. You intermix a column constraint and a table constraint. Both can be used here. If you want to use a column constraint, you don't put (RID) at the end of the PRIMARY KEY clause, because the primary key can only be the column to which you attach the column constraint. If you want to use a table constraint, you need a comma between the column declaration and the constraint. ------------------------------------------------------------------------------- [Thursday, April 13, 16:30] > ... > > NOW I'M TRYING TO ADD TO THE TEST TABLE AND IT'S NOT WORKING: > > SQL> INSERT INTO TESTS > 2 VALUES(00001, 040600, JANE, A, NULL, 15); > VALUES(00001, 040600, JANE, A, NULL, 15) > * > ERROR at line 2: > ORA-00984: column not allowed here You must include string constants (literals) in single quotes: VALUES(00001, 040600, 'JANE', 'A', NULL, 15) What is strange here is that Oracle complains about the second error, and not about the first (JANE). In this way it looks like it is ok the first time, but not the second time. But when you correct the second error, you will see that Oracle then complains about the first error. ------------------------------------------------------------------------------- [Thursday, April 13, 17:00] > When you get a chance, could you please tell me why I'm having problems > with the grant option. I looked on the oracle web site and this is how it > is listed to do, but it is not being accepted: > > > SQL> GRANT SELECT (TESTDATE,GRADE, COMMENTS) ON TESTS TO Scott; > GRANT SELECT (TESTDATE,GRADE, COMMENTS) ON TESTS TO Scott > * > ERROR at line 1: > ORA-00969: missing ON keyword This would work in SQL Server, but the SQL-92 standard and Oracle do not support giving SELECT rights for specific columns to some user. You can only give SELECT rights on the entire table GRANT SELECT ON TESTS TO SCOTT If you want to ensure that scott can only access these columns, you must declare a view which contains only these columns and then grant scott select rights on this view, but not on the base table. ------------------------------------------------------------------------------- [Thursday, April 13, 17:55] > I have a problem whenever I try to make tables to practice by myself. > What does it mean, " number of referencing columns must match referenced > columns"? Therefore, I copied the error below.. > What's wrong? As you see, when I make first table, there was no problem. > I think when I mentioned foreign key, is there any wrong? > > SQL> create table grade1 ( > 2 stud_no number(1) not null, > 3 stud char(10) not null, > 4 home char(5) not null, > 5 points number(3) not null, > 6 primary key (stud_no, stud)); > > Table created. > > SQL> create table grade2 ( > 2 stud_no number(1) not null, > 3 mid char(5) not null, > 4 points number(3) not null, > 5 primary key (stud_no), > 6 foreign key (stud_no) references grade1); > foreign key (stud_no) references grade1) > * > ERROR at line 6: > ORA-02256: number of referencing columns must match referenced columns The problem is that you declared a composed key for the relation grade1, which consists of two attributes, stud_no and stud. This means that there can be two rows with the same stud_no as long as they differ in the attribute stud. This is probably not what you intended. If you mean that stud_no and stud are both a key, you need two key declarations, e.g. primary key (stud_no), unique (stud) -- this declares stud as an alternative key. Then the foreign key declaration will work, because now the foreign key consists of exactly that columns which make up the primary key of the referenced table. ------------------------------------------------------------------------------- [Saturday, April 15, 12:30] > I have a question. In the Hw #10, a) quesion, RID->Restaurants is > underlined. I think it doesn't need to be underlined. > Would you let me know, which is correct. If RID would not be part of the key of the relation Tests, a tester could test only one restaurant per day. I see that this is probably the normal case, that the tester has dinner in a restaurant, and of course afterwards the tester cannot have a second dinner. So you are in part right. It might be, however, that the tester tests one restaurant at lunchtime and another restaurant at the same day for dinner. I have seen a funny film with Louis de Funes where he drives from one restaurant to the next and tests quite a lot of restaurants per day. If we want to allow this, RID must be part of the key. However, there is another problem with the key: If we allow that every tester tests every restaurant only once, as in the midterm exam, TestDate would not have to be part of the key. But maybe it is allowed here that a tester checks his previous opinion again sometime later. ------------------------------------------------------------------------------- [Saturday, April 15, 20:30] > Finally, regarding HW 10, I have 2 questions: > > 1. Is there any specific datatype you wanted us to use for the RID? I'm > not aware if it is just numbers or could also involve characters. Yes, you can use numbers. > 2. For part c, Do you just want a grant statement allowing scott to read > the table or do you want the view to also include a statement such that it > should be readable only by scott? the question states "He should not be > able to see the results of other restaurants" I am not really sure how you > want us to do that. I think you need to create a view which contains only scotts results (i.e. test results for his restaurant, i.e. where the owner is 'SCOTT'). You don't need to add a test AND USER = 'SCOTT' to the view. This would make testing the view difficult, and the exercise does not state that only he is allowed to see the results. ------------------------------------------------------------------------------- [Tuesday, April 18, 16:00] > i am having trouble with the second part of homework 10 question b. > sql will not accept my date. i was wondering why. > SQL> INSERT INTO TESTS(IRID,TESTDATE,TESTER,GRADE,COMMENTS ,COST) > 2 VALUES(5678,DATE'2000-02-15','JEFF','A+',NULL,25) > 3 ; > VALUES(5678,DATE'2000-02-15','JEFF','A+',NULL,25) > * > ERROR at line 2: > ORA-00936: missing expression The syntax DATE'2000-02-15' for date constants is proposed in the SQL standard, but none of the systems at which I have looked understands it. In Oracle, you should use a string of the form 'DD-MON-YY', e.g. INSERT INTO TESTS(IRID,TESTDATE,TESTER,GRADE,COMMENTS ,COST) VALUES(5678,'15-FEB-00','JEFF','A+',NULL,25) You can use other date formats, but then you need an explicit conversion. E.g. VALUES(5678, TO_DATE('2000-02-15', 'YYYY-MM-DD'), 'JEFF', ...)