[Tuesday, Feb 15, 10:45] > I am resending this email because the last one I sent > returned an error, so if you get 2, just ignore one of > them. > > I am not quite sure if I am doing the relational > algebra correctly. Here is what I have so far, could > you tell me if I am on the right track? > > 1a. [PROJECTION]Empno(([PROJECTION]DeptNo(DEPT) - > [PROJECTION]DeptNo(EMP)) - > [JOIN]Dept) > > 1.b [PROJECTION]Dname(([PROJECTION]DeptNo(DEPT) - > [PROJECTION]DeptNo(EMP)) - > [JOIN]Dept) Are both of these for Homework 5 a)? It is quite difficult for me to read. Normally, you would use [] for the Index. If I put your solution 1a) into my notation (and shorten PROJECTION to PROJ), it would be: PROJ[Empno]((PROJ[DeptNo](DEPT) - PROJ[DeptNo](EMP)) - JOIN DEPT) ^^^^^^ This is a syntax error. JOIN and - are both binary operations. You must have an operand between them (maybe you intended the - only as hyphen). So something like (A - B) * C or A - (B * C) would be possible. You should write * if you mean the natural join. The general join R JOIN[...] S needs an explicit join condition. Without the minus before that join you would be on the right track. However, I don't understand why you project on EmpNo. You are asked for the department number and the department name. You must output both in a single query (single result table). It looks like you intend to have one query with department numbers and another query with department names. But then you get two result tables, and you don't know which number corresponds to which name. > 2a. > [PROJECTION]Mgr([SELECT]Dname="sales"^"research"(DEPT > [JOIN] EMP)) The Mgr column in the Emp table gives the direct superviser of that employee. It is not needed for this exercise. You need to select employees with "MANAGER" in the Job column. Dname="sales"^"research" is a syntax error. ^ means AND and must have a condition on the left and on the right side. DNAME='SALES' AND DNAME='RESEARCH' would be syntactically correct, but, as explained on slides 2-27 and 2-28, no rows would qualify. This condition is always FALSE. > > 2B. [PROJECTION]DeptNo(EMP)([SELECT]Job="Manager"(DEPT > [JOIN] EMP)) Job="Manager" is part of the correct query. However, PROECTION[DeptNo](EMP)SELECT[Job="Manager"](...) would be a syntax error: ^^^^ At this point you have already a complete query. You need a binary operator like * if you want to connect it with another query. > Also, when doing the SQL part, I am having trouble > with part d. When I print this list, I get the > correct output, however there are duplicate rows of > output. If I eliminate DEPTNO from the SELECT > portion, it does not double. Below is a example of my > query and the output. > > SQL> SELECT DISTINCT ENAME, EMPNO, SAL, DNAME > 2 FROM DEPT,EMP > 3 WHERE ((DEPT.DEPTNO = '10') OR (DEPT.DEPTNO = > '30')) > 4 AND (EMP.SAL < 1500) > 5 ; > > ENAME EMPNO SAL DNAME > ---------- --------- --------- -------------- > ADAMS 7876 1100 ACCOUNTING > ADAMS 7876 1100 SALES > JAMES 7900 950 ACCOUNTING > JAMES 7900 950 SALES > MARTIN 7654 1250 ACCOUNTING > MARTIN 7654 1250 SALES > MILLER 7934 1300 ACCOUNTING > MILLER 7934 1300 SALES > SMITH 7369 800 ACCOUNTING > SMITH 7369 800 SALES > WARD 7521 1250 ACCOUNTING > WARD 7521 1250 SALES > DISTINCT elimiates duplicate rows. And actually, you don't have exactly the same row two times. You have the same employee listed twice. The problem is that you forgot the join condition (see slide 2-30). However, it suffices if you print the department number. Then you don't need the join. I am sorry that I was not clear enough in the exercise. But it certainly is a good exercise to do the join already in SQL. ------------------------------------------------------------------------------- [Wednesday, Feb 16, 9:30] > What is the command that will direct the output to a file? > It is explained in Homework 5: First, you enter "spool xyz" Then you do your SQL queries. Finally, you say "spool off". Then your input (SQL queries) and the results are saved in "xyz.lst". You can also say "spool print" instead of "spool off". This should send the output immediately to a printer. ------------------------------------------------------------------------------- [Thursday, Feb 17, 11:45] > Last minute Homework#5 question. I am sorry that I couldn't answer earlier. On Wednesdays I am often very busy doing lst minute slide preparation. > In the Q&A of Homework#5 you mentioned the following for b). > > >DNAME='SALES' AND DNAME='RESEARCH' would be syntactically correct, > >but, as explained on slides 2-27 and 2-28, no rows would qualify. > >This condition is always FALSE. > > So do you mean that find an employee who is a manager of research > department and at the same time of sales department? Then there > is no output for that query. No, I mean employees of the sales or the research department, whose job is manager. In natural language we sometimes say "and" where "or" would be more appropriate. However, since, as you correctly said, there would be no output for "and", you can solve the ambiguity. Certainly I want some output. > The other question is for c) print all employee names with four letters. > I read through the manual about formatting query results, and I only found > about for numbers not characters. Would you give me any tip for that? You need the LIKE operator of SQL, which is explained on slide 2-25. Also, I should have said "four characters" instead of "four letters". Allowing only letters is at least difficult and needs some datatype functions which we haven't discussed yet. I am sorry for the imprecise formulation. PS: If you send Siripun your homework via email (siripun@lis.pitt.edu), it should still be graded before the exam. ------------------------------------------------------------------------------- [Thursday, Feb 17, 16:25] > I am still having trouble with the relational algebra. For HW 5a > here is what I have > > PROJ[Dname] JOIN (PROJ[Empno](PROJ[Deptno](DEPT) - PROJ[Deptno](EMP) > (DEPT JOIN EMP))) PROJ[Deptno](DEPT) - PROJ[Deptno](EMP) is a good start. It gives you the department numbers of the departments without employees. However, after this you put (DEPT JOIN EMP). You can never have two operands next two each other, i.e. (EMP)(DEPT JOIN EMP) is wrong. You must have an operator, e.g. JOIN, -, etc between each two operands. I think the misunderstanding is that you do not need to start with the join, even if you ultimately want to access both tables. You start with PROJ[Deptno](DEPT) - PROJ[Deptno](EMP). Then you might need a join to get the missing department name. > 4. Here is where I am not sure. I need to print one more > column, but must I join this with the previous statments > or can I just project further without joining them? When > you project, are selecting that column without conditions > stated previously,(in that case you would get data from the > column that you did not want) or is it selecting the items > from the column that match up with the other columns that > were projected? PROJ[X,Y](R) projects R on columns X,Y. R can be a stored relation, or it can be a computed relation. The input is whatever you write between the (...). So e.g. PROJ[DNAME](PROJ[DEPTNO](DEPT)) would be wrong, because the first projection results in a relation with only the column DEPTNO. Then you can no longer project on DNAME, because there is no such column in the input relation. > I took the same approach with part b: > > PROJ[Empno] JOIN (PROJ[Ename](SELECT[Job='Manager'](EMP) > (SELECT[Dname='Research'](DEPT) OR (SELECT[Dname='Sales'](DEPT) > (DEPT JOIN EMP)))) You can use OR only inside selection conditions, but not between relational algebra queries (which denote relations). Probably you mean UNION. PROJ[Empno] JOIN ... is an error, because the input relation to the projection is missing. On the left side of the JOIN (as well as on the right side) you need complete relational algebra expressions. ------------------------------------------------------------------------------- [Friday, Feb 18, 16:40] > If I change it to the following, would it be correct? I: > > 1. First join the 2 tables > 2. Select on departments Research or Sales > 3. Select all jobs that = Manager > 4. Project on empno, ename. This explanation is right. > > PROJ[Empno,Ename] JOIN ((SELECT[Job='Manager'](EMP) > (SELECT[Dname='Research' OR 'Sales](DEPT) (DEPT JOIN EMP) This still contains syntax errors, e.g. > PROJ[Empno,Ename] JOIN ((SELECT[Job='Manager'](EMP) > (SELECT[Dname='Research' OR 'Sales](DEPT) (DEPT JOIN EMP) ^^^^^ You can't have two relations next to each other without an operator between them. I think the misunderstanding is that you want to mention again from which relation you access DName. But the input to the selection is the output of the JOIN. So after this error is corrected, it would look like this: > PROJ[Empno,Ename] JOIN ((SELECT[Job='Manager'](EMP) > (SELECT[Dname='Research' OR 'Sales](DEPT JOIN EMP) Now the same error happens between the two lines. Change it to: > PROJ[Empno,Ename] JOIN ((SELECT[Job='Manager']( > (SELECT[Dname='Research' OR 'Sales](DEPT JOIN EMP)) Now there is another error with the JOIN in the first line. You didn't mention a second join in your correct explanation. It is a syntax error because the projection has no input relation. Correcting this mistake, it would be PROJ[Empno,Ename](SELECT[Job='Manager'](SELECT[Dname='Research' OR 'Sales'](DEPT JOIN EMP))) There is one final error: OR needs to have conditions on the left hand side and on the right hand side (something which evaluates to TRUE or FALSE). You have on the right hand side only the string 'Sales'. I am sure that you can easily correct that. You see how the results flow: DEPT and EMP are input to the JOIN operation. The output of the JOIN is input to the SELECT on the DName. The output of that SELECT is the input to the next SELECT (on JOB). Then finally the output of that SELECTION flows into the projection. The output of the PROJ is the query result which is shown to the user. ------------------------------------------------------------------------------- [Tuesday, Feb 22, 10:25] > One other question: > > When performing a selection in relational algebra, is it legal to use the > "Not Equals" sign, for example: > > Select all employees that are NOT managers. > > SELECT[ename != "Manager"] where != represents not equal to. Yes, in relational algebra you can use any understandable symbol for not equals, e.g. !=, <>, a crossed = (=/=) etc. In SQL, it is best to write <> for not equals, since this is the most portable notation (it is contained in the SQL-86 and the SQL-92 standards, and very system should support it). However, Oracle understands <>, !=, ^=, and the logical not sign followed by = (see page 3-5 in the Oracle SQL Reference).