[Friday, Feb 11, 14:10] > I just started working on hw4 so my question is about executing cd.sql. In > your homework description you said we can use the path (e.g., D:\cd) as a > command. But when I tried it I got an error. I looked through the > documentation and found @@, so I tried @@D:\cd and it worked. Could you > tell me which one is the correct command? The two commands should only work differently if they are called from another SQL*Plus script. Specifically, @@ is intended for calling nested scripts. When you use @file, it will look in the current working directory (another place in the manual says curent default directory) first. Under UNIX, this is the directory where you started sqlplus. I don't know windows well. If you have a program as an icon on your desktop, you can right-click on it and go under properties and then to shortcut to find "Start in". This is probably the current working directory. Menu items are also shortcuts in the appropriate directory. Programs may also have settings in the Registry. If it is not in the current working directory, under UNIX the directories listed in the environment variable SQLPATH will be searched. I don't know how this works under Windows. Now the difference of @@ is for calling scripts from other scripts. Here the search starts in the same directory as the calling script. So when you call @/home/sbrass/public_html/db/x.sql, and x.sql contains @@y, it would first try to execute /home/sbrass/public_html/db/y.sql In this way, the scripts x.sql and y.sql can be moved together to another directory and I don't have to put absolute paths into my scripts. I cannot explain the behaviour you observed (that @D:\cd did not work, but @@D:\cd did work). I will post this email on the web site. If you or any other student could find out more, it would be great. I personally tried @A:\cd once, and it did work. > Second, I used @@ instead of @cd because I do not know what is my current > directory. How do I know where my current directory is? I tried "open" and > "save as" to see where the default dir is, and moved cd.sql there but no > luck. Is there a command that's equivalent to ls or dir? Under UNIX, you can use "! pwd" or "host pwd" in SQL*Plus. Under Windows, you can also execute MSDOS commands from SQL*Plus with "host command", but unless you do something special (?), the MSDOS window just flashes up and is gone. If you find out a good solution, please tell me. > Third, after @@D:\cd worked, It gave me a whole bunch of lines running for > a while. When it ended I just saw a lot of "1 row created." Is this > normal? I just want to know whether I did it correctly. If so, does this > mean that tables have been created based on the commands in cd.sql? Yes, this is normal. The file contains an INSERT statement for every row, and SQL*Plus tells you that every statement really worked. If you scroll back, there also should be "Table created." for the CREATE TABLE statements. The first time you execute the script, you will get error messages for the DROP TABLE statements at the beginning. They are intended to remove old versions of the tables. Don't worry about the message "Table does not exist." SQL*Plus still continues to execute the other commands in the script. ------------------------------------------------------------------------------- [Friday, Feb 11, 14:25] > After I save the cd.sql file on your web as all file adding extension > .sql by my self, I can't run it on the SQL*plus on my PC, because the > that file has been treated as cdsql.txt. could tell me how to transform > that file? You open the directory, click on the file, click on the file name, and edit it (as you would rename any other file). You can also select the object and press F2 for renaming. The file menu has an entry for renaming. And MSDOS has the "ren" command. But actually, the extension is not important, you can execute @cdsql.txt if you are in the right directory. Otherwise add the directory. One student told me that it only works with @@ instead of @. I cannot explain that, but you should try it. ------------------------------------------------------------------------------- [Sunday, Feb 13, 15:15] > I can run the cd under SQL now, but it showed a error message "ORA-00972: > identifier is too long" This is strange. I run it and it worked. I just tried it again and it worked. I tried it on a SUN, but Oracle is highly portable between different platforms. This error message means that a table name or a column name is longer than 30 characters, which is Oracle's limit (the SQL-92 standard allows up to 128 characters). Maybe the file is destroyed in some way during the download. If, e.g., the ' delimiting strings was replaced by a " delimiting identifiers this would explain the error message. The file is a UNIX file and has only a line feed at the end of the lines, and not carriage return and a line feed as usual under MSDOS/Windows. I have put a version for Windows under http://www2.sis.pitt.edu/~sbrass/db/cd_win.sql But as far as I remember, this is not a problem for SQL*Plus. It would be helpful if you could find the SQL command in the file which causes the error. In case of an error in an SQL command, Oracle usually prints the position where it found the error. Also, don't use the menu commands. There are GET and SAVE (or something similar) but they refer to the SQL buffer which can contain only a single SQL command. The file cd.sql contains many commands. I hope this helps. Please keep me informed about your progress and tell me what I should tell my students next time when I teach the course to make this a bit simpler for them. ------------------------------------------------------------------------------- [Tuesday, Feb 15, 10:55] > I copied the cd.sql off from your web site to e:\dbmgt\cd.sql. > > I telnet into paradox.sis.pitt.edu and assuming that this is the solaris > unix. > > Once I'm in sqlplus I get > SQL> > > I tried command > SQL> @e:\dbmgt\cd; and get the message 'unable to open file 'e:dbmgtcd.sql' > > What am I doing wrong? When you use telnet, it means that all commands are sent to paradox, executed there, and only the output is sent back to your PC. So the problem is that when you execute "@e:\dbmgt\cd" on paradox, it has no E: disk (it is a UNIX machine). You can only refer to files stored on paradox. You can move files between machines with ftp. Fortunately, since paradox also contains my webpages, there is no need to download the file. You could copy it with cp ~sbrass/public_html/db/cd.sql . This copies it into your current working directory. It is a UNIX command, not an SQL*Plus command. So either you execute it before you start SQL*Plus, or you use host cp ~sbrass/public_html/db/cd.sql . from inside SQL*Plus. Then you execute @cd in SQL*Plus. Actually, you don't have to copy the file. You could execute it directly with @/home/sbrass/public_html/db/cd ------------------------------------------------------------------------------- [Tuesday, Feb 15, 11:50] > on question e, should both CDs and total run-time be > sorted descending (larger numbers first) or does your > comment only apply to the runtime? Both parts should be sorted with the larger numbers first. I am sorry that I didn't made this clearer. Also, in homework 5d) I didn't made clear that the department number suffices. (Although it might be a useful exercise to try the join in SQL). ------------------------------------------------------------------------------- [Tuesday, Feb 15, 16:50] > I have a question regarding f of HW#4 > I typed the query following and got the error message. > > SQL> select cno,title from composer,piece where title like 'Symphon%'; > select cno,title from composer,piece where title like 'Symphon%' > * > ERROR at line 1: > ORA-00918: column ambiguously defined > > Which part I did it wrong? The problem is that you listed both tables under the from clause, and both tables have a column cno. In this case you must say to which table you refer, even if you have the join condition under where composer.cno = piece.cno which ensures that the two are equal. Oracle still will print the above error message if you write "cno" instead of "composer.cno" or "piece.cno". By the way, the homework is not intended to be so difficult. The two columns which you need, cno and title are both contained in the table piece. If you list only "piece" under from, you don't have these problems. ------------------------------------------------------------------------------- [Tuesday, Feb 15, 20:05] > I have a question regarding c) of HW4. Is the natural join commutative, i.e., does it matter whether I put parentheses around a pair of relations or does it work without parentheses? > The natural join is associative. So the parenthesis are not needed. It is commutative only if the sequence of columns does not matter (e.g. what is the first result column, what is the second). Many authors see tuples as mappings from column names to column values, and then it is commutative. ------------------------------------------------------------------------------- [Thursday, Feb 17, 14:15] > I have question on wildcard. > If I use '%Sinfon%' > Would this match anything that might come before and after the word Sinfon? > What about if Sinfon is the beginning or the ending which case there will > not be a match at either end of the word. > Or I could do %info%. % matches 0 or more characters. So there is no problem if the name starts with Sinfon. > I think I can also do without wildcard... > WHERE Title LIKE 'Symphony' OR 'Sinfon' This would be a syntax error. OR must be between conditions, so you would have to write WHERE Title LIKE 'Symphony' OR Title LIKE 'Sinfon' 'Sinfon' alone is not a condition, only a term/expression (evaluates to a string and not a boolean value). However, this would still not be the right solution. If the comparison string does not contain % or ?, LIKE is equivalent to =. So only exact matches will be found, not substrings. ------------------------------------------------------------------------------- [Friday, Feb 18, 15:15] > Is it possible to "assign" temporary storage locations to relational algebra > expressions? > > eg: > > Print number and name of CD that contains a music piece by Prokofiev > > > R := SELECT(Last = "Prokofiev")(Composer) * (Piece) > > S:= (Recording) * (R) > > PROJ(cdno, name ) ((S)*(CD)) Yes, this is possible. I only would prefer that you use [ ] for the indices, e.g. R := SELECT[Last = "Prokofiev"](Composer) * Piece The parentheses around Piece would be round ( ) as you did it, but they are not needed. ------------------------------------------------------------------------------- [Tuesday, Feb 22, 11:30] > SQL> select title from Piece where title like '%Symphony%'; > > no rows selected > > SQL> select title from Piece where title like '%Symphonie%' > 2 ; > > TITLE > --------------------------------------------- > Symphonie Nr.1 D-dur `Symphonie classique' > Symphonie Nr.5 B-dur > Symphonie Nr.2 D-dur > Symphonie Fantastique > Symphonie Nr.8 `Die Unvollendete' > Symphonie Nr.5 > Symphonie Nr.7 > Symphonie Nr.6 > Symphonie Nr.4 (1930) > Symphonie Nr.4 (1947) > Symphonie Nr.2 > Symphonie Nr.3 > Symphonie Nr.9 > Symphonie Nr.12 `1917' > Symphonie Nr.2 > > 15 rows selected. > > thank you for your email. I must translate the example data into English, most of it is still German. I thought I already had some English titles in it. The exercise makes little sense when there is no match for Symphony. I hope I will find the time for at least a partial translation. I apologize for the problems.