Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jsaetrum
Starting Member
7 Posts |
 Posted - 2011-12-02 : 16:02:56
|
| HiGiven the following relations:EMPLOYEE (ssn,name,gender,address,salary,supervisorSSN,dnumber)DEPARTMENT(dnumber,dname,managerSSN)DEPTLOCATION(dnumber,dlocation)PROJECT(pnumber,pname,plocation)WORKSON(emplSSN,pnumber,hours)And the query:Retrieve the names of all male employees in department number 999, whowork more than 13 hours per week on project “xyz”.is it right to take the natural join of Employee, WorksOnand Project, and then use select operation with the predicate: gender="m" AND dnumber=999 AND hours>13 AND pname = "xyz", (and finally use projection with predicate name on the returned relation from select)? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-03 : 00:19:06
|
| your explanation looks fine. did you try it? what result you got?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-03 : 01:05:16
|
| natural join works on attribute names - as the attribute names aren't the same in employee and workson you can't use a natural join.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsaetrum
Starting Member
7 Posts |
Posted - 2011-12-04 : 17:09:31
|
| Thanks!I haven't tried it, since I need to formulate an SQL query from this.Yes I realize now that natural join does not work with all the relations, but isn't it possible to use natural join at Project and WorksOn, and use select operation on those as "hours>13 and pname='xyz" and then use a theta-join with the predicate "dnumber=999 and gender='m'" on Employee. (and finally project on name attribute)? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-05 : 05:59:28
|
| I assume you are doing a course - have a look at the definition of theta join - often with equality it is called an equi join rather than a special case of equi join.If you think about the tables produced at each stage (think of it as a lot of nested statements each producing a table) then you'll find it easy to spot what there are mistakes.Then the only problem is getting the syntax right.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Basic Relational Algebra/SQL