Please Help with this SQL Problem

Page 1 of 1

2 Replies - 5716 Views - Last Post: 25 November 2006 - 05:15 AMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'http://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=21300&amp;s=0af16d1d6a26f846cb30d72aba81c198&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

#1 otiseoj

• New D.I.C Head

Reputation: 0
• Posts: 18
• Joined: 28-February 05

Please Help with this SQL Problem

Posted 24 November 2006 - 07:03 PM

```drop table City
drop table VideoGameStore
drop table Employee
drop table Product
drop table ItemsSoldByEmployee
spool c:\results.txt

create table City(CID integer, City_Name char(15),
primary key(CID));

create table VideoGameStore(SID integer, Store_Name char(15),
primary key(SID));

create table Employee(EID integer, Lname char(15),
Fname char(15), SID integer,
Store_Name char(15), CID integer,
City_Name char(15), primary key(EID));

create table product(IID integer, Item_type char(15),
Manuf_Name char(15), Item_Cost float,
primary key(IID));

create table ItemsSoldByEmployee(TN integer,
Store_Name char(15), Lname char(15),
Fname char(15), Item_Type char(15),
Manuf_Name char(15), Item_Cost char(15),
primary key(TN));

insert into City values (1000, 'BatonRouge');
insert into City values (1001, 'Baker');
insert into City values (1002, 'PortAllen');
insert into City values (1003, 'NewOrleans');
insert into City values (1004, 'Shreveport');
insert into City values (1005, 'LakeCharles');
insert into City values (1006, 'Lafayette');
insert into City values (1007, 'Opeleausas');

insert into VideoGameStore (2000, 'GameStop');
insert into VideoGameStore (2001, 'GamesRUs');
insert into VideoGameStore (2002, 'VideoGamesCity');
insert into VideoGameStore (2003, 'GameBusters');
insert into VideoGameStore (2004, 'GameShowcase');
insert into VideoGameStore (2005, 'GamePalace');
insert into VideoGameStore (2006, 'VideoGameHaven');

insert into Employee (5000, 'Smith', 'John', 2000, 'GameStop', 1005, 'LakeCharles');
insert into Employee (5001, 'Taylor', 'Sallie', 2003, 'GameBusters', 1007, 'Opeleausas');
insert into Employee (5002, 'Jones', 'Ryan', 2006, 'VideoGameHaven', 1000, 'BatonRouge');
insert into Employee (5003, 'Walker', 'Sean', 2003, 'GameBusters', 1007, 'Opeleausas');
insert into Employee (5004, 'Combs', 'Sean', 2000, 'GameStop', 1000, 'BatonRouge');
insert into Employee (5005, 'Carter', 'Shawn', 2000, 'GameStop', 1000, 'BatonRouge');
insert into Employee (5006, 'Dorsey', 'Chris', 2001, 'GamesRUs', 1003, 'NewOrleans');
insert into Employee (5007, 'Carter', 'Dwayne', 2004, 'GameShowcase', 1001, 'Baker');
insert into Employee (5008, 'Tapp', 'James', 2002, 'VideoGameCity', 1003, 'NewOrleans');
insert into Employee (5009, 'Gray', 'Terius', 2002, 'VideoGameCity', 1003, 'NewOrleans');
insert into Employee (5010, 'Bishop', 'Eric', 2005, 'GamePalace', 1002, 'PortAllen');
insert into Employee (5011, 'Young', 'Andre', 2000, 'GameStop', 1005, 'LakeCharles');
insert into Employee (5012, 'Simmons', 'Earl', 2005, 'GamePalace', 1002, 'PortAllen');
insert into Employee (5013, 'Miller', 'Corey', 2006, 'VideGameHaven', 1000, 'BatonRouge');
insert into Employee (5014, 'Crump', 'Levell', 2001, 'GamesRUs', 1003, 'NewOrleans');
insert into Employee (5015, 'Stevens', 'Earl', 2004, 'GameShowcase', 1001, 'Baker');
insert into Employee (5016, 'Weston', 'Wesley', 2003, 'GameBusters', 1004, 'shreveport');
insert into Employee (5017, 'Miller', 'Percy', 2003, 'GameBusters', 1004, 'Shreveport');
insert into Employee (5018, 'Tyler', 'Michael', 2000, 'GameStop', 1006, 'Lafayette');
insert into Employee (5019, 'Shakur', 'Tupac', 2000, 'GameStop', 1006, 'Lafayette');

spool off

```

This is what Im getting when I run the database code

```
7  drop table City
8  drop table VideoGameStore
9  drop table Employee
10  drop table Product
11  drop table ItemsSoldByEmployee
12  spool c:\results.txt
13
SQL>
SQL> create table City(CID integer, City_Name char(15),
2			primary key(CID));
create table City(CID integer, City_Name char(15),
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL>
SQL> create table VideoGameStore(SID integer, Store_Name char(15),
2	 primary key(SID));
create table VideoGameStore(SID integer, Store_Name char(15),
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL>
SQL> create table Employee(EID integer, Lname char(15),
2	 Fname char(15), SID integer,
3	 Store_Name char(15), CID integer,
4	 City_Name char(15), primary key(EID));
create table Employee(EID integer, Lname char(15),
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL>
SQL> create table product(IID integer, Item_type char(15),
2	 Manuf_Name char(15), Item_Cost float,
3	 primary key(IID));
create table product(IID integer, Item_type char(15),
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL>
SQL> create table ItemsSoldByEmployee(TN integer,
2	 Store_Name char(15), Lname char(15),
3	 Fname char(15), Item_Type char(15),
4	 Manuf_Name char(15), Item_Cost char(15),
5	 primary key(TN));
create table ItemsSoldByEmployee(TN integer,
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL>
SQL>
SQL> insert into City values (1000, 'BatonRouge');
insert into City values (1000, 'BatonRouge')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005028) violated

SQL> insert into City values (1001, 'Baker');
insert into City values (1001, 'Baker')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005028) violated

SQL> insert into City values (1002, 'PortAllen');
insert into City values (1002, 'PortAllen')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005028) violated

SQL> insert into City values (1003, 'NewOrleans');
insert into City values (1003, 'NewOrleans')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005028) violated

SQL> insert into City values (1004, 'Shreveport');
insert into City values (1004, 'Shreveport')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005028) violated

SQL> insert into City values (1005, 'LakeCharles');
insert into City values (1005, 'LakeCharles')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005028) violated

SQL> insert into City values (1006, 'Lafayette');
insert into City values (1006, 'Lafayette')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005028) violated

SQL> insert into City values (1007, 'Opeleausas');
insert into City values (1007, 'Opeleausas')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005028) violated

SQL>
SQL>
SQL> insert into VideoGameStore (2000, 'GameStop');
insert into VideoGameStore (2000, 'GameStop')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into VideoGameStore (2001, 'GamesRUs');
insert into VideoGameStore (2001, 'GamesRUs')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into VideoGameStore (2002, 'VideoGamesCity');
insert into VideoGameStore (2002, 'VideoGamesCity')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into VideoGameStore (2003, 'GameBusters');
insert into VideoGameStore (2003, 'GameBusters')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into VideoGameStore (2004, 'GameShowcase');
insert into VideoGameStore (2004, 'GameShowcase')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into VideoGameStore (2005, 'GamePalace');
insert into VideoGameStore (2005, 'GamePalace')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into VideoGameStore (2006, 'VideoGameHaven');
insert into VideoGameStore (2006, 'VideoGameHaven')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL>
SQL>
SQL> insert into Employee (5000, 'Smith', 'John', 2000, 'GameStop', 1005, 'LakeCharles');
insert into Employee (5000, 'Smith', 'John', 2000, 'GameStop', 1005, 'LakeCharles')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5001, 'Taylor', 'Sallie', 2003, 'GameBusters', 1007, 'Opeleausas');
insert into Employee (5001, 'Taylor', 'Sallie', 2003, 'GameBusters', 1007, 'Opeleausas')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5002, 'Jones', 'Ryan', 2006, 'VideoGameHaven', 1000, 'BatonRouge');
insert into Employee (5002, 'Jones', 'Ryan', 2006, 'VideoGameHaven', 1000, 'BatonRouge')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5003, 'Walker', 'Sean', 2003, 'GameBusters', 1007, 'Opeleausas');
insert into Employee (5003, 'Walker', 'Sean', 2003, 'GameBusters', 1007, 'Opeleausas')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5004, 'Combs', 'Sean', 2000, 'GameStop', 1000, 'BatonRouge');
insert into Employee (5004, 'Combs', 'Sean', 2000, 'GameStop', 1000, 'BatonRouge')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5005, 'Carter', 'Shawn', 2000, 'GameStop', 1000, 'BatonRouge');
insert into Employee (5005, 'Carter', 'Shawn', 2000, 'GameStop', 1000, 'BatonRouge')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5006, 'Dorsey', 'Chris', 2001, 'GamesRUs', 1003, 'NewOrleans');
insert into Employee (5006, 'Dorsey', 'Chris', 2001, 'GamesRUs', 1003, 'NewOrleans')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5007, 'Carter', 'Dwayne', 2004, 'GameShowcase', 1001, 'Baker');
insert into Employee (5007, 'Carter', 'Dwayne', 2004, 'GameShowcase', 1001, 'Baker')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5008, 'Tapp', 'James', 2002, 'VideoGameCity', 1003, 'NewOrleans');
insert into Employee (5008, 'Tapp', 'James', 2002, 'VideoGameCity', 1003, 'NewOrleans')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5009, 'Gray', 'Terius', 2002, 'VideoGameCity', 1003, 'NewOrleans');
insert into Employee (5009, 'Gray', 'Terius', 2002, 'VideoGameCity', 1003, 'NewOrleans')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5010, 'Bishop', 'Eric', 2005, 'GamePalace', 1002, 'PortAllen');
insert into Employee (5010, 'Bishop', 'Eric', 2005, 'GamePalace', 1002, 'PortAllen')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5011, 'Young', 'Andre', 2000, 'GameStop', 1005, 'LakeCharles');
insert into Employee (5011, 'Young', 'Andre', 2000, 'GameStop', 1005, 'LakeCharles')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5012, 'Simmons', 'Earl', 2005, 'GamePalace, 1002, 'PortAllen');
ERROR:
ORA-01756: quoted string not properly terminated

SQL> insert into Employee (5013, 'Miller', 'Corey', 2006, 'VideGameHaven', 1000, 'BatonRouge');
insert into Employee (5013, 'Miller', 'Corey', 2006, 'VideGameHaven', 1000, 'BatonRouge')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5014, 'Crump', 'Levell', 2001, 'GamesRUs', 1003, 'NewOrleans');
insert into Employee (5014, 'Crump', 'Levell', 2001, 'GamesRUs', 1003, 'NewOrleans')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5015, 'Stevens', 'Earl', 2004, 'GameShowcase', 1001, 'Baker');
insert into Employee (5015, 'Stevens', 'Earl', 2004, 'GameShowcase', 1001, 'Baker')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5016, 'Weston', 'Wesley', 2003, 'GameBusters', 1004, 'shreveport');
insert into Employee (5016, 'Weston', 'Wesley', 2003, 'GameBusters', 1004, 'shreveport')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5017, 'Miller', 'Percy', 2003, 'GameBusters', 1004, 'Shreveport');
insert into Employee (5017, 'Miller', 'Percy', 2003, 'GameBusters', 1004, 'Shreveport')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5018, 'Tyler', 'Michael', 2000, 'GameStop', 1006, 'Lafayette');
insert into Employee (5018, 'Tyler', 'Michael', 2000, 'GameStop', 1006, 'Lafayette')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into Employee (5019, 'Shakur', 'Tupac', 2000, 'GameStop', 1006, 'Lafayette');
insert into Employee (5019, 'Shakur', 'Tupac', 2000, 'GameStop', 1006, 'Lafayette')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL>
SQL>
SQL>
SQL>
SQL> spool off
not spooling currently

```

Is This A Good Question/Topic? 0

Replies To: Please Help with this SQL Problem

#2 beef

• D.I.C Head

Reputation: 1
• Posts: 141
• Joined: 02-November 06

Re: Please Help with this SQL Problem

Posted 24 November 2006 - 09:22 PM

The database may not be registering that you have dropped the tables. Try putting either a / or a commit; between dropping and creating your tables. The / would execute the commands in sqlplus and the commit would also commit the changes.
Was This Post Helpful? 0

#3 thef0rce

• New D.I.C Head

Reputation: 1
• Posts: 39
• Joined: 02-November 06

Re: Please Help with this SQL Problem

Posted 25 November 2006 - 05:15 AM

I see you have syntax problems

try putting a semicolon after every drop statement. I don't believe they are being executed right now.

all insert into statements should be insert into (table name) values

ie: insert into employee values (5000, 'Smith', 'John', 2000, 'GameStop', 1005, 'LakeCharles');
Was This Post Helpful? 0

Page 1 of 1

 .related ul { list-style-type: circle; font-size: 12px; font-weight: bold; } .related li { margin-bottom: 5px; background-position: left 7px !important; margin-left: -35px; } .related h2 { font-size: 18px; font-weight: bold; } .related a { color: blue; }