Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2007
    Posts
    12

    Unanswered: Emergency Creating 2 Views

    Hello everyone,
    I have created 4 tables and populated them but I have a real problem creating 2 Views for them.
    Please if anybody can help because it is a real emergency.

    Well,

    1st View: I want to create a view called ProjMgt which will display the number of employees participating in projects, per department name, but only for those departments which have more than 3 projects.

    2nd View: I want to create a view called Duration which will display the number of employees participating in projects according to the duration of the projects.


    The script is the following:


    CREATE SEQUENCE "DEPARTMENT_ID_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 40 NOORDER NOCYCLE
    /

    CREATE TABLE Departments_pr
    (
    department_ID INTEGER NOT NULL ,
    department_Name VARCHAR2(15) NOT NULL ,
    CONSTRAINT XPKDepartmentsTable PRIMARY KEY (department_ID)
    );

    INSERT INTO departments_pr
    VALUES (department_id_seq.NEXTVAL, 'IT');
    INSERT INTO departments_pr
    VALUES (department_id_seq.NEXTVAL, 'CONSTRUCTION');


    CREATE SEQUENCE "EMPLOYEE_ID_SEQ" MINVALUE 100 MAXVALUE 999999999999999999999999999 INCREMENT BY 10 START WITH 100 CACHE 40 NOORDER NOCYCLE
    /

    CREATE TABLE Employees_pr
    (
    employee_ID INTEGER NOT NULL ,
    first_name VARCHAR2(15) NOT NULL,
    last_name VARCHAR2(20) NOT NULL ,
    position VARCHAR2(15) DEFAULT 'INSTRUCTOR',
    DOB DATE NULL ,
    hire_date DATE NULL ,
    salary NUMBER(9,2) NULL ,
    department_ID INTEGER NOT NULL ,
    CONSTRAINT XPKEmployeesTable PRIMARY KEY (employee_ID),
    CONSTRAINT Has FOREIGN KEY (department_ID) REFERENCES Departments_pr(department_ID)
    );

    INSERT INTO employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Nick','Maiden', DEFAULT, DATE '1985-03-01', DATE '2001-02-23', 500.50, 1);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Harry', 'Porter', DEFAULT, DATE '1979-03-13', DATE '1999-01-05', 700.56, 1);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Alice', 'Macain', 'MANAGER', DATE '1967-12-01', DATE '1990-11-25', 15460.89, 1);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Mario', 'Fallen', DEFAULT, DATE '1977-07-30', DATE '2000-11-14', 555.00, 1);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Petro', 'Juna', DEFAULT, DATE '1981-02-01', DATE '2005-09-05', 800.67, 1);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Michael', 'Rivers', 'MANAGER', DATE '1967-08-24', DATE '1992-09-24', 16650.45, 2);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Mary', 'Sivers', DEFAULT, DATE '1979-12-10', DATE '2003-12-12', 350.34, 2);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Nikoleta', 'Kalen', DEFAULT, DATE '1984-06-25', DATE '2006-02-03', 780.35, 2);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Rick', 'Jones', DEFAULT, DATE '1979-05-10', DATE '2003-02-12', 500.34, 2);
    INSERT INTO Employees_pr
    VALUES (employee_id_seq.NEXTVAL, 'Many', 'Rihter', DEFAULT, DATE '1984-12-25', DATE '2006-01-03', 980.35, 2);


    CREATE SEQUENCE "PROJECT_ID_SEQ" MINVALUE 50 MAXVALUE 999999999999999999999999999 INCREMENT BY 3 START WITH 50 CACHE 40 NOORDER NOCYCLE
    /



    CREATE TABLE Projects
    (
    project_ID INTEGER NOT NULL ,
    start_date DATE NOT NULL ,
    end_date DATE NULL ,
    pName VARCHAR2(15) NOT NULL ,
    pdescription VARCHAR2(100) NULL ,
    budget NUMBER(7) NULL ,
    department_ID INTEGER NOT NULL ,
    CONSTRAINT XPKProjects PRIMARY KEY (project_ID),
    CONSTRAINT Manages FOREIGN KEY (department_ID) REFERENCES Departments_pr(department_ID)
    );

    INSERT INTO Projects
    VALUES (project_id_seq.NEXTVAL, DATE '2007-01-01', DATE '2008-01-02', 'MADNESS', 'Build a Park in the center of Athens', 50000, 1);
    INSERT INTO Projects
    VALUES (project_id_seq.NEXTVAL, DATE '2007-03-23', DATE '2009-03-23', 'KIFISIAS', 'Create an intersection on Kifisias Avenue 157', 500870, 1);
    INSERT INTO Projects
    VALUES (project_id_seq.NEXTVAL, DATE '2009-02-23', NULL, 'MOLL MEGARA', 'Build a Mall Superstore in Megara', 80780, 1, manager_id_seq.NEXTVAL);
    INSERT INTO Projects
    VALUES (project_id_seq.NEXTVAL, DATE '2007-03-30', DATE '2010-01-30', 'ORACLE DATABASE', 'Create a database for Intracom clients', 300679, 2);
    INSERT INTO Projects
    VALUES (project_id_seq.NEXTVAL, DATE '2008-10-24', NULL, 'WIMAX', 'Create a WiMAX network for Athens municipality', 50800, 2);
    INSERT INTO Projects
    VALUES (project_id_seq.NEXTVAL, DATE '2007-11-13', DATE '2008-10-13', 'SERVERS', 'Order Microsoft Servers for Vodafone and update their IT department', 500500, 2);
    INSERT INTO Projects
    VALUES (project_id_seq.NEXTVAL, DATE '2007-11-12', DATE '2008-10-12', 'SERVERS2', 'Order Microsoft Servers', 60050, 2);



    CREATE TABLE Project_Management
    (
    employee_ID INTEGER NOT NULL ,
    project_ID INTEGER NOT NULL ,
    CONSTRAINT XPKProject_Control PRIMARY KEY (employee_ID,project_ID),
    CONSTRAINT Undertake FOREIGN KEY (employee_ID) REFERENCES Employees_pr(employee_ID),
    CONSTRAINT Are_Assigned_to FOREIGN KEY (project_ID) REFERENCES Projects(project_ID)
    );


    INSERT INTO Project_MANAGEMENT VALUES (100, 50);
    INSERT INTO Project_MANAGEMENT VALUES (110, 53);
    INSERT INTO Project_MANAGEMENT VALUES (120, 59);
    INSERT INTO Project_MANAGEMENT VALUES (130, 62);
    INSERT INTO Project_MANAGEMENT VALUES (130, 50);
    INSERT INTO Project_MANAGEMENT VALUES (110, 65);
    INSERT INTO Project_MANAGEMENT VALUES (100, 65);
    INSERT INTO Project_MANAGEMENT VALUES (140, 68);
    INSERT INTO Project_MANAGEMENT VALUES (150, 56);

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    emergency? you mean, like, the homework assignment is due tomorrow?

    can we see your attempt at declaring the views, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The lack of planning on your part does not necessarily mean an emergency on our part.
    If you are ever dissatisfied with the timeliness or quality of any response here,
    you are entitled to a full & complete refund of all amounts paid.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jul 2007
    Posts
    12
    Yes, it is a project i have to deliver and i have stuck on these 2 views. I have taken this course during the Session and I parallel work full time in a company, so as you can see there is not much time to catch up.
    I am fighting 2 weekends now with this project!
    Of course i have tried something but because i think especially in the 1st View that i have to use a triple join I am a little bit confused.
    This is something i tried to do.

    1st View Attempt:
    SELECT D.DEPARTMENT_NAME, COUNT(DISTINCT EMPLOYEE_ID)
    FROM DEPARTMENTS_PR D, PROJECTS P, PROJECT_MANAGEMENT PM
    WHERE P.PROJECT_ID=PM.PROJECT_ID
    GROUP BY D.DEPARTMENT_NAME HAVING D.DEPARTMENT_NAME > ANY (SELECT D.DEPARTMENT_NAME FROM DEPARTMENTS_PR D, PROJECTS P
    WHERE D.DEPARTMENT_ID=P.DEPARTMENT_ID
    GROUP BY D.DEPARTMENT_NAME);



    2nd View Attempt:
    select start_date, end_date, count(*) as employees
    from projects p, project_management pm
    where pm.project_id=p.project_id
    group by start_date, end_date;

  5. #5
    Join Date
    Jul 2007
    Posts
    12
    Quote Originally Posted by anacedent
    The lack of planning on your part does not necessarily mean an emergency on our part.
    If you are ever dissatisfied with the timeliness or quality of any response here,
    you are entitled to a full & complete refund of all amounts paid.
    No, no. I didn 't say something like this. I am very sorry if you misunderstood me. Maybe the post as "emergency" was wrongly expressed from my side.
    I really apologize if you are offended!

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >1st View: I want to create a view called ProjMgt which will display the number of employees participating in projects, per department name, but only for those departments which have more than 3 projects.

    Where in your 1st attempt do you handle the "which have more than 3 projects" requirement?

    While I have not actually tried your 1st SQL, IMO, it appears to have fatal syntax flaw(s).

    >2nd View: I want to create a view called Duration which will display the number of employees participating in projects according to the duration of the projects.

    where in your 2nd attempt do you handle the Duration requirement?

    You should read & follow posting guidelines as stated in the #1 STICKY post at top of forum.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try writing your joins like this:
    Code:
      FROM foo
    INNER
      JOIN bar
        ON bar.cumquat = foo.whizmo
    INNER
      JOIN qux
        ON qux.dongle = bar.zoozah
    this forces you to think about and specify the exact join conditions

    in your first view, you've got 3 tables, but you've joined only two of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2007
    Posts
    12
    Yes. The other join is inside the Subselect!

    For the 2nd View i this the Duration in the time between the start_date and end_date. So i have to GROUP BY with these two Attributes....
    Last edited by kal1mera; 07-21-07 at 15:50.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kal1mera
    Yes. The other join is inside the Subselect!
    nope, sorry, it isn't

    try the INNER JOIN syntax -- please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >SELECT D.DEPARTMENT_NAME, COUNT(DISTINCT EMPLOYEE_ID)
    >FROM DEPARTMENTS_PR D, PROJECTS P, PROJECT_MANAGEMENT PM

    Since only two tables return data to the SELECT clause, only 2 tables should be in the FROM clause.

    Since PROJECTS returns no actual data it should be subordinated into the WHERE clause.

    From where I sit, DURATION is END_DATE-START_DATE; not simply displaying either or both these columns.

    Rhetorical question - What is DURATION when END_DATE is NULL?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jul 2007
    Posts
    12
    I haven't been taught this kind of Join systax, but i am trying right now to understand it and implement it.

  12. #12
    Join Date
    Jul 2007
    Posts
    12
    Quote Originally Posted by anacedent
    From where I sit, DURATION is END_DATE-START_DATE; not simply displaying either or both these columns.

    Rhetorical question - What is DURATION when END_DATE is NULL?
    I have declared end_date as NULL, only from logic, because there may be a start_date for a Project and it's end_date may not be determined yet(by the company).

    If i have to calculate the difference, then obviously i should set it to NOT NULL.

  13. #13
    Join Date
    Jul 2007
    Posts
    12
    Quote Originally Posted by r937
    try the INNER JOIN syntax -- please
    I tried your way, and at least i managed to determine the constraint "only for departments which have more that 3 projects".

    This is it:

    SELECT department_name, COUNT(*)
    FROM departments_pr
    INNER
    JOIN projects
    ON projects.department_id = departments_pr.department_id
    group by department_name HAVING COUNT(*)>3;

    It returns:

    Department_name COUNT(*)
    -----------------------------------
    CONSTRUCTION ------- 4

    But i don't know now how to count the employees who are assigned projects for those departments!
    Last edited by kal1mera; 07-21-07 at 16:37.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks great, you do understand JOIN syntax and i think you will find that it's easier than the old style once you start using it more

    but unfortunately, you do realize that that query does not answer question 1

    the question was "number of employees participating in projects, per department name, but only for those departments which have more than 3 projects"

    your query produces "number of projects, per department name, but only for those departments which have more than 3 projects"

    the difference is, you have not incorporated the employee data into the join

    i will give you a big hint: since you are only going to count the number of employees, you do not need both the Employees and the Project_Management table, you need only the Project_Management tabl;e
    Last edited by r937; 07-21-07 at 16:32.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2007
    Posts
    12
    For the 2nd View (DURATION), i tried this but i don't know if i am counting correctly:


    SELECT ROUND(((end_date-start_date)/30), 0) as DURATION_MONTHS, COUNT(*)
    FROM PROJECTS
    INNER
    JOIN PROJECT_MANAGEMENT ON PROJECT_MANAGEMENT.PROJECT_ID = PROJECTS.PROJECT_ID
    GROUP BY ROUND(((end_date-start_date)/30), 0);

    RESULT:

    DURATION_MONTHS COUNT(*)
    --------------------------------------
    ------------------- ---- 2
    11 -------------------- 3
    24 ------------------- 1
    35 ------------------- 1
    12 ------------------- 2

Posting Permissions

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