Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    7

    Question Unanswered: error in this syntax

    create table sailors(
    sid int not null,
    sname char(20) not null,
    rating int,
    age decimal,
    constraint pk_sailors primary key(sid)
    )
    create table boats(
    bid int not null,
    bname char(10) not null,
    color char(10) not null,
    constraint pk_boats primary key(bid)
    )
    create table reserves(
    sid int not null,
    bid int not null,
    tgl datetime not null,
    constraint fk_reservesSid foreign key(sid) references sailors(sid),
    constraint fk_reservesBid foreign key(bid) references boats(bid),
    )

    insert into sailors(sid,sname,rating,age) values('22','Dustin','7','45.0')
    insert into sailors(sid,sname,rating,age) values('29','Brutus','1','33.0')
    insert into sailors(sid,sname,rating,age) values('31','Lubber','8','55.5')
    insert into sailors(sid,sname,rating,age) values('32','Andy','8','25.5')
    insert into sailors(sid,sname,rating,age) values('58','Rudy','10','35.0')
    insert into sailors(sid,sname,rating,age) values('64','Horatio','7','35.0')
    insert into sailors(sid,sname,rating,age) values('71','Zorba','10','16.0')
    insert into sailors(sid,sname,rating,age) values('74','Horatio','9','25.5')
    insert into sailors(sid,sname,rating,age) values('85','Art','3','63.5')
    insert into sailors(sid,sname,rating,age) values('95','Bob','3','35.0')

    insert into boats(bid,bname,color) values('101','Interlake','blue')
    insert into boats(bid,bname,color) values('102','Interlake','red')
    insert into boats(bid,bname,color) values('103','Clipper','green')
    insert into boats(bid,bname,color) values('104','Marine','red')

    insert into reserves(sid,bid,tgl) values('22','101','10/10/08')
    insert into reserves(sid,bid,tgl) values('22','102','10/10/08')
    insert into reserves(sid,bid,tgl) values('22','103','10/08/08')
    insert into reserves(sid,bid,tgl) values('22','104','10/07/08')
    insert into reserves(sid,bid,tgl) values('31','102','11/10/08')
    insert into reserves(sid,bid,tgl) values('31','103','11/06/08')
    insert into reserves(sid,bid,tgl) values('31','104','11/12/08')
    insert into reserves(sid,bid,tgl) values('64','101','09/05/08')
    insert into reserves(sid,bid,tgl) values('64','102','09/08/08')
    insert into reserves(sid,bid,tgl) values('74','103','09/08/08')

    select * from boats
    select * from sailors
    select * from reserves

    select s.sname from sailors s inner join reserves r on s.sid=r.sid where r.bid=103
    select s.sname from sailors s inner join reserves r on s.sid=r.sid inner join boats b on b.bid=r.bid
    where b.color='red'

    select dbo.sailors.sid, dbo.sailors.sname, count(dbo.reserves.bid) as banyak from dbo.boats inner join
    dbo.reserves on dbo.boats.bid = dbo.reserves.bid inner join dbo.sailors on dbo.reserves.sid = dbo.sailors.sid
    group by dbo.sailors.sid, dbo.sailors.sname
    having (count(dbo.reserves.bid)>1)

    select sname,age from sailors where sailors.rating>7

    drop table boats
    drop table reserves
    drop table sailors

    ------------------------
    Msg 2714, Level 16, State 6, Line 1
    There is already an object named 'sailors' in the database.

    when Execute syntax above,i found Error like above.
    can you help me to fix it?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just a quick question - do you know what the error means and why it happens?
    I want to check, since "fixing" it depends on what you think is the problem here.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    There is already an object named 'sailors' in the database.
    When SQL Server encounters your first "create table sailors(..", it notices the table already exists.

    Precede your code with
    Code:
    IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE [ID] = OBJECT_ID('sailors') AND type = ('U'))
    	DROP TABLE sailors
    
    IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE [ID] = OBJECT_ID('boats') AND type = ('U'))
    	DROP TABLE boats
    
    IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE [ID] = OBJECT_ID('reserves') AND type = ('U'))
    	DROP TABLE reserves
    to drop those tables if they exist.

    Use with caution, dropping tables is not always what you want.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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