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

    Question Unanswered: ask:how to make syntax from this question

    table sailors
    =============

    sid sname rating age
    --------------------------------------
    22 Dustin 1 45
    29 Brutus 1 33
    31 Lubber 8 55,5
    32 Andy 8 25,5
    58 Rusty 10 35
    64 Hiratio 7 35
    71 Zorba 10 16
    85 Art 3 25,5
    95 Bob 3 63,5
    74 Horatio 9 35

    table Reserves
    ==============

    sid bid day
    -----------------------
    22 101 10/10/08
    22 102 10/10/08
    22 103 10/08/08
    22 104 10/07/08
    31 102 11/10/08
    31 103 11/06/08
    31 104 11/12/08
    64 101 09/05/08
    64 102 09/08/08
    74 103 09/08/08


    table boat
    ==========

    bid bname color
    -----------------------------
    101 Interlake Blue
    102 Interlake red
    103 Chipper green
    104 Marine red


    *********************************


    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')


    how to make syntax from question below?

    1.find sailor's names that have reserve boat 103
    2.find sailor's names that have reserve red boat
    3.find sailor's names that have reserve atleast 2 boat
    4.find names and ages of sailors which rating >7
    5.find sailor's names that have reserve all the red boat

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    two points i would like to make to help you:

    1. we don't do homework assignments on this site

    2. we'd be happy to help you, but you have to do the work

    so, what have you tried so far?

    show your query, and explain how you think it works, and whether you think it got the correct results, and if not, why not

    get it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh what the heck, I was bored so I've created exactly what compileman has requested. I'd still recommend for many reasons that you consider doing your assignment yourself instead of just copying an answer provided by a DBA and professional SQL programmer.
    Code:
    SELECT 'Dustin' AS sname
    UNION SELECT 'Lubber'
    UNION SELECT 'Horatio'
    
    SELECT 'Dustin' AS sname
    UNION ALL SELECT 'Lubber'
    UNION ALL SELECT 'Horatio'
    
    SELECT 'Dustin' AS sname
    UNION ALL SELECT 'Lubber'
    UNION ALL SELECT 'Horatio'
    
    SELECT 'Lubber' AS sname, '55.5' AS age
    UNION ALL SELECT 'Andy', '25.5'
    UNION ALL SELECT 'Rudy', '35.0'
    UNION ALL SELECT 'Zorba', '26.0'
    UNION ALL SELECT 'Horatio', '25.5'
    
    SELECT 'Dustin' AS sname
    UNION ALL SELECT 'Lubber'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    May 2009
    Posts
    7
    Quote Originally Posted by Pat Phelan
    Oh what the heck, I was bored so I've created exactly what compileman has requested. I'd still recommend for many reasons that you consider doing your assignment yourself instead of just copying an answer provided by a DBA and professional SQL programmer.
    Code:
    SELECT 'Dustin' AS sname
    UNION SELECT 'Lubber'
    UNION SELECT 'Horatio'
    
    SELECT 'Dustin' AS sname
    UNION ALL SELECT 'Lubber'
    UNION ALL SELECT 'Horatio'
    
    SELECT 'Dustin' AS sname
    UNION ALL SELECT 'Lubber'
    UNION ALL SELECT 'Horatio'
    
    SELECT 'Lubber' AS sname, '55.5' AS age
    UNION ALL SELECT 'Andy', '25.5'
    UNION ALL SELECT 'Rudy', '35.0'
    UNION ALL SELECT 'Zorba', '26.0'
    UNION ALL SELECT 'Horatio', '25.5'
    
    SELECT 'Dustin' AS sname
    UNION ALL SELECT 'Lubber'
    -PatP
    thanks for your help.

  5. #5
    Join Date
    May 2009
    Posts
    7
    this is not my homework.this questions that i asked i got from a mysql tutorial exercises.i'm just training to answer the tutorial's exercises.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by compileman
    this is not my homework.this questions that i asked i got from a mysql tutorial exercises.i'm just training to answer the tutorial's exercises.
    that's the same situation -- you have to do the thinking, and we'll only give you hints

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2009
    Posts
    7
    code below is for number 3

    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)


    can you give another code like code above?i think this code is not so simple.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by compileman
    i think this code is not so simple.
    never mind if it's simple or not simple -- does it give the correct answer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    compileman, try thinking of it this way.

    What information from what table(s) do you need to satisfy the request?

    What columns do you want to return (Select List)?
    What columns do you need to filter (Join predicates, Where clauses, and/or Having clauses)?

    I think what you have will work but has more items than you really need.

    By the way, any particular reason you named the result of the COUNT banyak? I would think something like ReserveBoat_Count might be more descriptive.

Posting Permissions

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