Results 1 to 4 of 4

Thread: code

  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: code

    EMPLOYEE

    ENO ENAME POSITION
    E870 Ahmed Iftekar Syst.analy
    E890 Mathew cercone Elect.eng
    E910 Mano Perera Programmer
    E920 Sam Aftasi Syst.analy
    E950 John Jacob Mech.Eng
    E960 Eric Liu Programmer
    E970 Geraldine Jones Programmer


    WORKLOAD

    ENO PNO LOAD
    E870 P01 0.6
    E870 P03 0.4
    E890 P03 0.5
    E890 P04 0.2
    E910 P01 0.4
    E910 P02 0.3
    E920 P05 0.9
    E960 P06 1
    E970 P05 0.3
    E970 P01 0.2



    PROJECTS:

    PNO PRJ_NAME BUDGET CNO
    P01 Database System 50000 C20
    P02 Internet Service 35000 C20
    P03 Business Management 30000 C42
    P04 Inventory Database 20000 C42
    P05 Data Warehouse 70000 C80
    P06 Intelligent Agents 55000 C20




    COMPANIES
    CNO CNAME PHONE CITY
    C20 IT Technology 91001232 Melbourne
    C42 E_Business 96002391 Sydney
    C65 Computer Solutions 97003595 Melbourne
    C80 Data Warehouse 93008876 Queensland




    (3). Get the names of the projects that are not involved by any Elect.Engineer



    i write the query



    select distinct
    prj_name from
    employee e, workload w, projects p
    where
    e.eno=w.eno and
    w.pno=p.pno and
    e.position not in 'elec engg'
    /
    but i am getting businessmanagement also which is not supposed to come .
    how can i retify the problem


    thanks
    kiran

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: code

    You don't specify that Bus Mgmt is not supposed to be returned.
    Add that as a criteria.

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    You can fidn the projects which are maintained by ELECT ENGR. If you MINUS this from list of all projects, you will get projects which are NOT maintained by elect engr.


    Code:
    select pno, prj_name from projects
    MINUS
    select p.pno, p.prj_name
    employee e, workload w, projects p
    where
    e.eno=w.eno and
    w.pno=p.pno and
    e.position ='elec engg'
    Oracle can do wonders !

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Given that workload.pno does not contain null values then,

    select *
    from employee e
    INNER JOIN
    workload w ON
    e.eno = w.eno
    WHERE w.pno NOT IN
    (select w.pno
    from workload w
    INNER JOIN
    employee e ON
    w.eno = e.eno AND
    e.pos = 'Elect.eng');
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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