Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2014
    Posts
    7

    Unanswered: The problem for SQL



    The following are four relational table.

    EMPLOYEE (EmployeeID, Fname, Lname, Bdate, Address, Sex, Salary, SupervisorID, Dno)
    WORKS_IN (EmpID, Pno, Hours)
    PROJECT (Pnumber, Pname, Plocation, Dnum)
    DEPARTMENT (DeptID, Dname, ManagerID, Mgr_strat_date )

    The problem is:
    Search all the name from every employee in all the project.

    The answer is:

    SELECT Fname, Lname
    FROM EMPLOYEE
    WHERE NOT EXISTS(
    SELECT *
    FROM PROJECT
    WHERE NOT EXISTS(
    SELECT*
    FROM WORKS_IN
    WHERE EMPLOYEE.EmployeeID= WORKS_IN.EmpID AND WORKS_IN.Pno= PROJECT.Pnumber))

    I don't understand this:

    WHERE EMPLOYEE.EmployeeID= WORKS_IN.EmpID AND WORKS_IN.Pno= PROJECT.Pnumber))

    Can everyone explain this to me?

    Thanks.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    looks like your sql and requirements do not match up.your SQL states that you want the names of employees who are not working on a project. Not sure why you would need that query to begin with as you can prevent it from ever happening with a foreign key constraint.
    Dave

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    WHERE EMPLOYEE.EmployeeID= WORKS_IN.EmpID AND WORKS_IN.Pno= PROJECT.Pnumber))

    You are doing a not exist through the project table which doesn't have a employee id field. So they are joining off of ph number it looks like.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The best way to learn SQL is by having a database at hand, create the needed tables, fill them with some test data, define what result you want (expected results), write the SQL statements and check if the result matches the expected results.
    Code:
    CREATE TABLE EMPLOYEE (
    	EmployeeID	int	NOT NULL, 
    	Fname	VARCHAR(50)	NULL, 
    	Lname	VARCHAR(50)	NULL, 
    )
    INSERT INTO EMPLOYEE(EmployeeID, Fname, Lname) VALUES
    (1, 'Hard', 'Worker'),
    (2, 'Normal', 'Pal'),
    (3, 'Ordinary', 'Man'),
    (4, 'Lazy', 'Guy')
    
    CREATE TABLE PROJECT (
    	Pnumber	int	NOT NULL, 
    	Pname	VARCHAR(50)	NOT NULL
    )
    INSERT INTO PROJECT(Pnumber, Pname) VALUES
    (1, 'Project1'),
    (2, 'Project2'),
    (3, 'Project3'),
    
    CREATE TABLE WORKS_IN (
    	EmpID	int	NOT NULL, 
    	Pno		int	NOT NULL
    )
    INSERT INTO WORKS_IN(EmpID, Pno) VALUES
    (1, 1),
    (1, 2),
    (2, 1),
    (3, 2)
    Now you have the tables ( I only provided the columns that were needed) and some test data in them.

    Search all the name from every employee in all the project.
    My language is not English, I hope I interpreted your question correctly:
    "Give the names (Lname, Fname) of the employees that work on one of the projects."

    I expect to see Hard Worker, Normal Pal and Ordinary Man.

    Let's see what the SQL query you provided gives:
    Code:
    SELECT Fname, Lname
    FROM EMPLOYEE
    WHERE NOT EXISTS(
    		SELECT *
    		FROM PROJECT
    		WHERE NOT EXISTS(
    				SELECT *
    				FROM WORKS_IN
    				WHERE EMPLOYEE.EmployeeID= WORKS_IN.EmpID AND WORKS_IN.Pno= PROJECT.Pnumber)
    		)
    This returns no results.

    This statement
    Code:
    SELECT  E.Fname, E.Lname
    FROM EMPLOYEE as E
    	INNER JOIN WORKS_IN as W ON
    		E.EmployeeID = W.EmpID
    	INNER JOIN PROJECT as P ON
    		W.Pno = P.Pnumber
    Gives this result:
    Code:
    Fname	Lname
    Hard	Worker
    Hard	Worker
    Normal	Pal
    Ordinary	Man
    Hard Worker appears twice, because she works on two projects. By adding DISTINCT after SELECT, we get the result we expected.

    Search all the name from every employee in all the project.
    One can also interprete this in a second way:
    "Give the names (Lname, Fname) of the employees that work on all of the projects."
    So, when there are three projects, give me those employees who work on those three projects at once.

    The expected result now is an empty list. There are three projects, and only Hard Worker works on more than 1 project, but not on all three projects.
    To create a situation that gives a result, we must delete Project3, so there are only two projects left:
    Code:
    DELETE FROM PROJECT
    WHERE Pnumber = 3
    Now we expect to have one single result: Hard Worker.

    Now the query you provided gives the correct answer.
    Code:
    Fname	Lname
    Hard	Worker
    I'd write it in this way: count the number of different projects and then select those employees who work on exactly that number of different projects. (well, I'd use CTE's but ignore this for now)
    Code:
    SELECT Works_For.Fname, Works_For.Fname
    FROM (SELECT count(*) as available_nmbr_projects
    	FROM PROJECT) as P
    	INNER JOIN 
    		(SELECT E.Fname, E.Lname, E.EmployeeID, count(distinct W.Pno) as works_on_nmbr_projects
    		FROM EMPLOYEE as E
    			INNER JOIN WORKS_IN as W ON
    				E.EmployeeID = W.EmpID
    		GROUP BY E.Fname, E.Lname, E.EmployeeID) as Works_For ON
    		P.available_nmbr_projects = Works_For.works_on_nmbr_projects
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Need more data

    >> The following are four relational tables<<

    Please read a book on basic data modeling. Your skeleton violates ISO-11179, has no DRI, and is not normalized! Data elements change names from table to table! Let's start to repair this:

    CREATE TABLE Personnel
    (emp_id CHAR(10) NOT NULL PRIMARY KEY,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    birth_date DATE NOT NULL,
    street_address CHAR(35) NOT NULL
    sex_code CHAR(1) NOT NULL);

    Why do you think that supervisor is an attribute? Are you born with him attached? NO! It is a relationship with another employee. Likewise a salary is an attribute of job.

    But there is no way to tell any relationships in this skeleton.
    Are projects assigned to departments? Are employees assigned projects or to departments?

    CREATE TABLE Projects
    (project_nbr CHAR(10) NOT NULL PRIMARY KEY,,
    project_name VARCHAR(25) NOT NULL,
    project_location_htm CHAR(16) NOT NULL,
    dept_id CHAR(10) NOT NULL
    REFERENCES Departments (dept_id));

    CREATE TABLE Departments
    (dept_id CHAR(10) NOT NULL PRIMARY KEY,
    dept_name VARCHAR(25) NOT NULL);

    Is the salary paid for the job on a project or is the salary related to the employee, without regard to any project?

    CREATE TABLE Job_Assignments
    (project_nbr (project_nbr CHAR(10) NOT NUL
    REFERENCES Projects(project_nbr),
    emp_id CHAR(10) NOT NULL
    REFERENCES Personnel (emp_id),
    salary_amt DECIMAL (12,2) NOT NULL,
    job_type CHAR(1) NOT NULL
    CHECK (job_type IN ('supervisor', 'worker'));

    The problem is:
    Search all the names of every employee in all*the*projects.

    We do not have a schema, so we cannot answer you! Want to try again?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •