Help with SQL HomeWork Question Please!

  • (2 Pages)
  • +
  • 1
  • 2

26 Replies - 5762 Views - Last Post: 23 September 2012 - 06:07 PM

#1 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Help with SQL HomeWork Question Please!

Posted 18 September 2012 - 06:48 PM

Hello there, I need help with my homework.

This is the question: http://i.imgur.com/CQa5b.png


So far I have done is:

SQL> create table LARGE_SLIP
(Marina_Num char (4) not null,
slip_num char (4) not null,
rental_fee int,
Boat_name char (50),
Owner_num char (4),);

--Table created--

SQL> insert into LARGE_SLIP
values (


(I am lost from there).

Is This A Good Question/Topic? 0
  • +

Replies To: Help with SQL HomeWork Question Please!

#2 fromTheSprawl  Icon User is offline

  • Monomania
  • member icon

Reputation: 513
  • View blog
  • Posts: 2,056
  • Joined: 28-December 10

Re: Help with SQL HomeWork Question Please!

Posted 18 September 2012 - 07:02 PM

To insert in a table simply do:

insert into TABLE_NAME(column1,column2,column3) values(value1,value2,value3);


That simple. Where do you get your data from anyway?
Was This Post Helpful? 0
  • +
  • -

#3 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Re: Help with SQL HomeWork Question Please!

Posted 18 September 2012 - 08:06 PM

hi, thanks for replaying fromTheSprawl.

Pardon me, but i am very naive to SQL or to Programming at all. So bear with me please.

So what you are saying is every time info is updated like in step 3, i should use that command?

Also what data are you referring to? I gave link to the question above and that's all there it is.
Was This Post Helpful? 0
  • +
  • -

#4 fromTheSprawl  Icon User is offline

  • Monomania
  • member icon

Reputation: 513
  • View blog
  • Posts: 2,056
  • Joined: 28-December 10

Re: Help with SQL HomeWork Question Please!

Posted 19 September 2012 - 06:44 PM

Well, UPDATE is another thing altogether. I suggest you read through this first.

Writing SQL Queries: Let's Start with the Basics

UPDATE's simple syntax:
update TABLE_NAME set columnName = value[,columnName = value] where condition


The question says insert into the table those slips whose length is 40 so I think the input is provided in that book somewhere. Is this a school homework or are you learning on your own? Yes, I read the title. :)
Was This Post Helpful? 0
  • +
  • -

#5 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 03:22 PM

Well..right, updates means something else. Actually this is my first Programming class i am taking in College, so I have total 3 Assignments for whole semester and this is the first of them.

I think i did not provide full info earlier. So i try to be more clear this time.
This is the book --> http://www.scribd.co...Chapter-Summary (Page 193) (Question#1 Alexamara Marina Group )

I just need to write SQL code, NO output is required.

btw, i took my first Test and it was way too easy and i think i aced it. :P
but this asst seems to be harder than test.

opps...it seems like i can't edit or delete my post...?!

This post has been edited by e_i_pi: 20 September 2012 - 05:09 PM
Reason for edit:: Edited out the duplicate content

Was This Post Helpful? 0
  • +
  • -

#6 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 05:56 PM

All right --> I was finally able to write ALL SQL Code using info on page 16 FIGURE 1-10 in that Book.

This is the FUll SQL Code --> http://pastebin.com/qdYvEyFG (Please go through it and see if you find it completely wrong or just few errors). :)


One more time: No output is required, I just need SQL Code. Thanks you!!
Was This Post Helpful? 0
  • +
  • -

#7 fromTheSprawl  Icon User is offline

  • Monomania
  • member icon

Reputation: 513
  • View blog
  • Posts: 2,056
  • Joined: 28-December 10

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 06:30 PM

I can't view it on pastebin. Could you try pasting it here inside code tags? :)
Was This Post Helpful? 0
  • +
  • -

#8 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 06:42 PM

     SQL> create table LARGE_SLIP
             (Marina_Num char(4) not null,
             Slip_Num char(4) not null,
             Rental_Fee char(10),
             Boat_name char(50),
             Owner_name char(4),);
     
    ---------Table Created--------------
     
    insert into LARGE_SLIP (column1,column2,column3,column4,column5)
    values ('1', 'A1', '$3800', 'Anderson II', 'AN75');
    values ('1', 'A2', '$3800', 'Our Toy', 'EL25');
    values ('1', 'A3', '$3600', 'Escape', 'KE22');
    values ('2', '5', '$4200', 'Dolphin 40', 'N027');
    values ('2', '6', '$4200', 'Ray 4025', 'TR72');
     
    Update LARGE_SLIP
    Set Rental_Fee = ' *150'
    where Marina_Num = '1'
    where Marina_num = '2' ;
     
    Update LARGE_SLIP
    set Rental_Fee = ' -40'
    where Rental_Fee = '4200' ;
     
    insert into LARGE_SLIP
    values ('1', 'A4', '$3900', 'Bilmore', 'FE82') ;
     
    Delete
    From LARGE_SLIP
    where Owner_Num = 'TR72" ;
     
    Update LARGE_SLIP
    Set Boat_name = 'UNKNOWN'
    where Slip_Num = 'A1' ;
    Alter LARGE_SLIP
    Modify Boat_name = 'UNKNOWN' ;  
     
    Alter LARGE_SLIP
    add new coloum 'Charter' char(1)
     
    Update LARGE_SLIP
    Set = Charter = "Y" ;
    where Slip_Num = 'A2' ;
     
    Update LARGE_SLIP
    Set  Boat_Name = ' char(60)'
    where  Boat_Name = 'char(50)' ;
     
    Alter LARGE_SLIP
    Modify Rental_Fee Not Null;
     
    Drop LARGE_SLIP;


That's the code. There were 12 steps i had to go through in the question. However i really have doubts on few steps where i have to multiply or subtract something. ex: look at 3,8,4 etc..
Thanks!
Was This Post Helpful? 0
  • +
  • -

#9 fromTheSprawl  Icon User is offline

  • Monomania
  • member icon

Reputation: 513
  • View blog
  • Posts: 2,056
  • Joined: 28-December 10

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 06:52 PM

I'm not really sure that your insert works, have you tried it? the columns after large slip should be column names, for example Marina_Num or Slip_Num. Like this:
insert into LARGE_SLIP (Marina_Num ,Slip_Num)
values ('1', 'A1');


Then if you want to add another value you have to still complete the statement,
insert into TABLE_NAME (column1) values (value1);
insert into TABLE_NAME (column1) values (value1);
insert into TABLE_NAME (column1) values (value1);
insert into TABLE_NAME (column1) values (value1);

Like that.

This is also wrong:
Update LARGE_SLIP
Set Rental_Fee = ' *150'
where Marina_Num = '1'
where Marina_num = '2' ;

If you have multiple conditions, don't repeat where again. Use the and keyword.
So, an example would be:
update TABLE_NAME set column1 = value1 where condition1 and condition2;


Also, look at line 31, you've made an inconsistency in wrapping your value.
Was This Post Helpful? 0
  • +
  • -

#10 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 07:13 PM

No, i haven't run it, because i am yet to download the software. And also I am pretty sure My professor won't run every code written by each student. So the code does not have to be perfect, but mostly correct.

Question about insert:

Since insert into LARGE_SLIP (Marina_Num ,Slip_Num, Rental_Fee, Boat_Name, Owner_Num), is same for every column, just different date into it, Do i still type it every time?

in other words:

insert into LARGE_SLIP (Marina_Num ,Slip_Num, Rental_Fee, Boat_Name, Owner_Num),
values ('1', 'A1', '$3800', 'Anderson II', 'AN75');


insert into LARGE_SLIP (Marina_Num ,Slip_Num, Rental_Fee, Boat_Name, Owner_Num),
values ('1', 'A2', '$3800', 'Our Toy', 'EL25');

Is that how you saying?

EDIT:

This is the Figure being used for all info: resource://jid0-gxjllfbcoax0lcltedfrekqdqpi-at-jetpack/as-ff/data/edit.html

FIGURE 1-10
Was This Post Helpful? 0
  • +
  • -

#11 fromTheSprawl  Icon User is offline

  • Monomania
  • member icon

Reputation: 513
  • View blog
  • Posts: 2,056
  • Joined: 28-December 10

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 07:18 PM

Yes, that is correct. Also, when you paste code put it in code tags so it looks better. ^^

I suggest downloading at least MySQL or SQL Server Express something to try out your code, that way you'll be sure what you're writing is correct. Also, your create has an extra , on it.

If you can't be bothered to download stuff try this, I think it's really good.

SQLFiddle

Preloaded is your initial SQL code. When using insert, update, create, and delete, use the Schema window. When querying(Select), use the right window. Try it. ^^
Was This Post Helpful? 0
  • +
  • -

#12 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 07:28 PM

All right, I will put code tags from now-on. :) I would defiantly download that software as i will be needing again in future. So thanks for giving the link to it.

SQL> create table LARGE_SLIP
         (Marina_Num char(4) not null,
         Slip_Num char(4) not null,
         Rental_Fee char(10),
         Boat_name char(50),
         Owner_name char(4),);

---------Table Created--------------

insert into LARGE_SLIP (Marina_Num ,Slip_Num, Rental_Fee, Boat_Name, Owner_Num),
values ('1', 'A1', '$3800', 'Anderson II', 'AN75'); 
insert into LARGE_SLIP (Marina_Num ,Slip_Num, Rental_Fee, Boat_Name, Owner_Num),
values ('1', 'A2', '$3800', 'Our Toy', 'EL25');
insert into LARGE_SLIP (Marina_Num ,Slip_Num, Rental_Fee, Boat_Name, Owner_Num),
values ('1', 'A3', '$3600', 'Escape', 'KE22'); 
insert into LARGE_SLIP (Marina_Num ,Slip_Num, Rental_Fee, Boat_Name, Owner_Num),
values ('2', '5', '$4200', 'Dolphin 40', 'N027'); 
insert into LARGE_SLIP (Marina_Num ,Slip_Num, Rental_Fee, Boat_Name, Owner_Num),
values ('2', '6', '$4200', 'Ray 4025', 'TR72'); 

Update LARGE_SLIP
Set Rental_Fee = ' *150'
where Marina_Num = '1'
where Marina_num = '2' ;

Update LARGE_SLIP
set Rental_Fee = ' -40'
where Rental_Fee = '4200' ;

insert into LARGE_SLIP 
values ('1', 'A4', '$3900', 'Bilmore', 'FE82') ; 

Delete 
From LARGE_SLIP
where Owner_Num = 'TR72"; 

Update LARGE_SLIP
Set Boat_name = 'UNKNOWN'
where Slip_Num = 'A1' ; 
Alter LARGE_SLIP
Modify Boat_name = 'UNKNOWN' ;  

Alter LARGE_SLIP
add new coloum 'Charter' char(1)

Update LARGE_SLIP
Set = Charter = "Y" ; 
where Slip_Num = 'A2' ; 

Update LARGE_SLIP
Set  Boat_Name = ' char(60)' 
where  Boat_Name = 'char(50)' ;

Alter LARGE_SLIP
Modify Rental_Fee Not Null;

Drop LARGE_SLIP; 


So now, I have made changes to insert code. I hope its right. Commas, semi-colon, etc..is the least that worries me. I can fix that in to time. :)

However i do need more help on code 17, 21 because that's where i have to do calculations.
Was This Post Helpful? 0
  • +
  • -

#13 fromTheSprawl  Icon User is offline

  • Monomania
  • member icon

Reputation: 513
  • View blog
  • Posts: 2,056
  • Joined: 28-December 10

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 07:38 PM

What do you mean code 17 and 21? By the way, you still haven't fixed the update with two where's. In the link I provided you could actually test if all your statements work, that thing would be really helpful for you to see the errors.
Was This Post Helpful? 0
  • +
  • -

#14 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 07:48 PM

yea.. update with two where's. gives me an error. it says:
Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Marina_num = '2' at line 4:

the original question is :Alexamara has increased the rental fee of each large slip by $150. Update the rental fees in the LARGE_SLIP table accordingly.

I added
Update LARGE_SLIP
Set Rental_Fee = '*150'
where Marina_Num = '1'
where Marina_num = '2';

I don't know how else to add, because Marina_num is primary key, but there are two. MARINA_
NUM = 1
and
MARINA_
NUM = 2
Was This Post Helpful? 0
  • +
  • -

#15 iceman01  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 16
  • Joined: 18-September 12

Re: Help with SQL HomeWork Question Please!

Posted 20 September 2012 - 07:53 PM

Also in code 26:

26 Update LARGE_SLIP
27 set Rental_Fee = ' -40'
28 where Rental_Fee = '4200' ;

the question is:
After increasing the rental fee of each large slip by $150 (Exercise 3), Alexamara decides
to decrease the rental fee of any slip whose fee is more than $4,000 by one percent. Update
the rental fees in the LARGE_SLIP table accordingly.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2