Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    14

    Unanswered: Newbie SQL Help on Views

    Hello I need some help on the following 3 part question I have included the table structure below, none of the tables have key constraints. Any help or suggestions would be appreciated. - Thanks for your time.

    1.) Create a view called PURITAINCHILDREN view to see the name, age and school year of all children of employees insured by Puritain.

    I have tried the following code which I hear is somewhat near the correct code to no avail.
    SQL> CREATE VIEW view_puritainchildren (cname, childage, schyear)
    2 AS SELECT DISTINCT cname,childage,schyear from child
    3 FROM child, insur_co
    4 WHERE insur_co.empno = child.empno
    5 AND insur_co.empno = 'PURITAIN');
    FROM child, insur_co
    *
    ERROR at line 3:
    ORA-00933: SQL command not properly ended

    2.) Using the PURITAINCHILDREN view, obtain a list of children whose age is less than or equal to 20 excluding the child whose age is less than 10. Show the name, age, and school year of each child.

    3.) Using the PURITAIN CHILDREN view, crate a new view called LITTLEPURITAINCHILDREN consisting of name, age and school year of children whose age is less than 10.

    SQL> DESC doctor;
    Name Null? Type
    ----------------------------------------------------- -------- ------------------------------------
    DNAME VARCHAR2(20)
    SPECIALTY VARCHAR2(20)
    YRSEXPER NUMBER(2)

    SQL> DESC clinic;
    Name Null? Type
    ----------------------------------------------------- -------- ------------------------------------
    DNAME VARCHAR2(20)
    CNAME VARCHAR2(20)
    ADDRESS VARCHAR2(30)

    SQL> DESC employee;
    Name Null? Type
    ----------------------------------------------------- -------- ------------------------------------
    DNAME VARCHAR2(20)
    EMPNO NUMBER(3)
    ENAME VARCHAR2(20)
    ADDRESS VARCHAR2(30)
    AMTOWED NUMBER(5)

    SQL> DESC insurco;
    Name Null? Type
    ----------------------------------------------------- -------- ------------------------------------
    DNAME VARCHAR2(20)
    EMPNO NUMBER(3)
    INAME VARCHAR2(20)
    ADDRESS VARCHAR2(30)
    CYCLE NUMBER(3)

    SQL> DESC child;
    Name Null? Type
    ----------------------------------------------------- -------- ------------------------------------
    DNAME VARCHAR2(20)
    EMPNO NUMBER(3)
    CNAME VARCHAR2(20)
    CHILDAGE NUMBER(2)
    SCHYEAR VARCHAR2(2)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Newbie SQL Help on Views

    Originally posted by smarque1
    I have tried the following code which I hear is somewhat near the correct code to no avail.
    SQL> CREATE VIEW view_puritainchildren (cname, childage, schyear)
    2 AS SELECT DISTINCT cname,childage,schyear from child
    3 FROM child, insur_co
    4 WHERE insur_co.empno = child.empno
    5 AND insur_co.empno = 'PURITAIN');
    FROM child, insur_co
    *
    ERROR at line 3:
    ORA-00933: SQL command not properly ended
    1) You have FROM clause twice (lines 2 and 3)
    2) You have an unwanted ")" on line 5

Posting Permissions

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