Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: **New to database* Creating sql file

    Hi,

    I had to create a SQL for my first database assignment. I followed the instructions perfectly and I'm just wondering why when I go to access it through SQL on command prompt that it basically says it is full of errors.

    Any help would be appreciated.



    DROP TABLE Patient_T;
    CREATE TABLE Patient_T
    (
    PatientNbr CHAR(5) NOT NULL,
    Name VARCHAR(35) ,
    FirstSeen DATETIME ,
    SocialWorker VARCHAR(35) ,
    CONSTRAINT pk_Patient_T PRIMARY KEY (PatientNbr)
    );
    INSERT INTO Patient_T VALUES('9844','John Miller',DATE'10/1/2008','Matt Baker');
    INSERT INTO Patient_T VALUES('4211','Sheryl Franz',DATE'1/3/2009','Lynn Riley');
    INSERT INTO Patient_T VALUES('9766','Juan Ortega',DATE'2/2/2009','Matt Baker');
    INSERT INTO Patient_T VALUES('1234','Chris Whitton',DATE'5/5/2010','Bilbo Baggins');
    INSERT INTO Patient_T VALUES('0501','Shane Egan',DATE'6/6/2010','Frodo');


    DROP TABLE Visit_T;
    CREATE TABLE Visit_T
    (
    PatientNbr CHAR(5) NOT NULL,
    VisitDate DATETIME NOT NULL,
    VisitTime VARCHAR(8) NOT NULL,
    VisitReason VARCHAR(40) ,
    NewSymptoms VARCHAR(50) ,
    PainLevel NUMERIC(10,2) ,
    CONSTRAINT pk_visit PRIMARY KEY (PatientNbr,VisitDate,VisitTime)
    CONSTRAINT fk_visit FOREIGN KEY (PatientNbr)
    REFERENCES Patient_T (PatientNbr)
    );
    INSERT INTO Visit_T VALUES('9844',DATE '10/11/2009','2:30 pm','Sever leg pain','Severe leg pain for past 2 days','4');
    INSERT INTO Visit_T VALUES('9844',DATE '10/18/2009','11:30 am','Follow-up, also needed flu shot','none', '2');
    INSERT INTO Visit_T VALUES('9844',DATE '1/3/2010','10:00 am','Routine','None','0');
    INSERT INTO Visit_T VALUES('9844',DATE '3/15/2010','10:30 am','Routine','None', '0');
    INSERT INTO Visit_T VALUES('4211',DATE '1/3/2010','2:00 pm','Referred by Primary care physician',' ','0');
    INSERT INTO Visit_T VALUES('4211',DATE '2/11/2010','9:00 am','Physical','Greater difficulty with writing and buttoning shirts', '1');
    INSERT INTO Visit_T VALUES('4211',DATE '3/22/2010','4:00 pm','Routine and B12 shot','None','0');
    INSERT INTO Visit_T VALUES('8766',DATE '2/2/2010','9:30 am','Blurred vision in right eye',' ','0');
    INSERT INTO Visit_T VALUES('8766',DATE '2/14/2010','9:30 am','Follow-up',' ', '0');
    INSERT INTO Visit_T VALUES('8766',DATE '3/18/2010','????','New symptoms','Pins/needles in both legs; touble with balance','1');
    INSERT INTO Visit_T VALUES('1234',DATE '5/5/2010','9:30 am','Torn ACL/MCL',' ', '10');
    INSERT INTO Visit_T VALUES('0501',DATE '6/6/2010','10:30 am','New symptoms','Covered in bumps','0');

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm just wondering why when I go to access it through SQL on command prompt that it basically says it is full of errors.
    The OS command line interpreter knows NOTHING about the SQL language.
    You must utilize some SQL client software like sqlplus that is provided with Oracle RDBMS or
    utilize Freeware GUI like SQuirreL SQL Client Home Page

    Code:
    bcm@bcm-laptop:~$ sqlplus user1/user1
    
    SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 2 20:47:59 2012
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    20:48:00 SQL> @patient.sql
    20:48:07 SQL> DROP TABLE Patient_T;
    DROP TABLE Patient_T
               *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    20:48:09 SQL> CREATE TABLE Patient_T
    20:48:09   2  (
    20:48:09   3  PatientNbr CHAR(5) NOT NULL,
    20:48:09   4  Name VARCHAR(35) ,
    20:48:09   5  FirstSeen DATETIME ,
    20:48:09   6  SocialWorker VARCHAR(35) ,
    20:48:09   7  CONSTRAINT pk_Patient_T PRIMARY KEY (PatientNbr)
    20:48:09   8  );
    FirstSeen DATETIME ,
              *
    ERROR at line 5:
    ORA-00902: invalid datatype
    
    
    20:48:09 SQL> INSERT INTO Patient_T VALUES('9844','John Miller',DATE'10/1/2008','Matt Baker');
    INSERT INTO Patient_T VALUES('9844','John Miller',DATE'10/1/2008','Matt Baker')
                                                          *
    ERROR at line 1:
    ORA-01861: literal does not match format string
    
    
    20:48:09 SQL> INSERT INTO Patient_T VALUES('4211','Sheryl Franz',DATE'1/3/2009','Lynn Riley');
    INSERT INTO Patient_T VALUES('4211','Sheryl Franz',DATE'1/3/2009','Lynn Riley')
                                                           *
    ERROR at line 1:
    ORA-01861: literal does not match format string
    
    
    20:48:09 SQL> INSERT INTO Patient_T VALUES('9766','Juan Ortega',DATE'2/2/2009','Matt Baker');
    INSERT INTO Patient_T VALUES('9766','Juan Ortega',DATE'2/2/2009','Matt Baker')
                                                          *
    ERROR at line 1:
    ORA-01861: literal does not match format string
    
    
    20:48:09 SQL> INSERT INTO Patient_T VALUES('1234','Chris Whitton',DATE'5/5/2010','Bilbo Baggins');
    INSERT INTO Patient_T VALUES('1234','Chris Whitton',DATE'5/5/2010','Bilbo Baggins')
                                                            *
    ERROR at line 1:
    ORA-01861: literal does not match format string
    
    
    20:48:09 SQL> INSERT INTO Patient_T VALUES('0501','Shane Egan',DATE'6/6/2010','Frodo');
    INSERT INTO Patient_T VALUES('0501','Shane Egan',DATE'6/6/2010','Frodo')
                                                         *
    ERROR at line 1:
    ORA-01861: literal does not match format string
    
    
    20:48:09 SQL> commit;
    
    Commit complete.
    
    20:48:09 SQL>
    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.

Tags for this Thread

Posting Permissions

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