Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    3

    Lightbulb Unanswered: I'm new to oracle... i need ur help guys :-)

    Hi there,

    guys im new to oracle i dnt know much abt it but im learning and willing to be gud in it.

    I have a project which requires from me to find out the solutions of it
    It is called Renting Car Agency… it has 4 tables (Renter, Car, Contract and Driver). I have created the tables which are like this

    Create table Renter (RNO varchar2 (5) primary key, RName varchar2 (10), INCOME number, CITY varchar2 (10));

    Create table Car (CNO varchar2 (5) primary key, CName varchar2 (10), AGE number, CITY varchar2 (10));

    Create table Contract (RNO varchar2 (5) not null references Renter, CNO varchar2 (5) not null references Car, DNO varchar2 (5) REFERENCES DRIVER, AMT number);

    Alter table contract add constraint contrcat_pk primary key (RNO,CNO, DNO);

    Create table Driver (DNO varchar2 (5) primary key, DNAME varchar2 (12), CREDIT number (7), CITY varchar2 (10));

    There are some queries which I have to answer by using SQL. These are the queries:


    I didn’t know how to do the “at least” ones, also see my answers and let me know if they r correct or not.

    1.Get renter number for renter who rent at least one car by renter R2.
    ** I got an error
    --Select r.rno from renter r, contract c where c.cno = (select cno from contract group by cno having cno >= 1);

    2.Get renter names for renters who rented car no C2. (2 solutions which one is correct or nor of them?)
    -- Select r.rname, from renter r, contract o, car c where r.rno=o.rno and o.cno=c.cno and c.cno=’C2’;

    --select r.rname from renter r, contract o where r.RNO = r.RNO and o.cno = 'C2';

    3.Get renter name for renter who rent at least one car with one year old.

    --Select r.rname from renter r, contract o, car c where r.rno=o.rno and o.cno=c.cno and c.age=1;

    4.Get the age of the car that rented by renter R1.

    --Select c.age from car c, contract o, renter r where c.cno=o.cno and o.rno=r.rno and r.rno=’R1’;

    5.Get car numbers for all car rented by more than one renter

    --Select cno from contract where rno IN (select rno from renter group by rno);


    6.Get renter names for renter who does not rent car C2.
    -- select r.rname from renter r, contract o where r.RNO = o.RNO and o.cno Not IN('C2');

    7.Get renter numbers for renter who are located in the same city as renter R1

    -- select RNO from renter where city = (select city from renter where RNO = 'R1');

    8.Get renter numbers for renter who rent at least all those car rented by renter R2.

    -- select RNO from contract where CNO IN (select CNO from contract where RNO = 'R2');

    9.Get the car name that has 2 contracts.
    ** got an error
    --Select c.cname from car c, contract o where c.cno=o.cno and ……. I don’t know how to complete it

    Thanks in advance ^_^

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by ku2
    Hi there,

    guys im new to oracle i dnt know much abt it but im learning and willing to be gud in it.
    Please excuse me, but my english isn't that good, so I need to ask:
    What is a "dnt"?
    What is "much abt"? (much what?)
    And what does "gud" mean?

    Regarding your questions: "got an error" definitely not enough to be able to help you. You need to give us the error message that you get.

    For all the other homework questions, where you provided a statement: you need to tell us, what the problem with each statements is.

    Does it work?
    Does give "an error"?
    Does it return the wrong data?
    If it does return the wrong data, what does it return, and what do you expect?

  3. #3
    Join Date
    May 2008
    Posts
    3
    Hi all

    Hope you all are doing well.

    Dear Shammat I’m extremely sorry for writing short cuts. Inshallah (if god wishes) next time I won’t repeat it. At the same time I’m thankful to you because you take a minute from your time to read my topic.

    I did this mistake because I thought all frequent internet users know about these short cuts.

    As you asked me to clarify the meaning of
    “dnt” = don’t
    “much abt” = much about
    “gud” = good

    As you said it’s homework but at the same time it’s a good learning way, because from you all I’m really getting valuable knowledge about Oracle which I’m not getting from my instructor.
    (Our instructor told us wherever we (students) try to figure out the answers, is fine for him. He suggested the internet forum for us as well).

    Regarding to the problem which I’m facing I got only one error and for others I’m not sure if I used correct functions or not.

    If they are correct please do let me know guys

    The solutions
    --------------------------------------------------------------
    1.

    SQL> /
    1 *Select r.rno from renter r, contract c where c.cno =(select cno from contract group by cno having cno)
    ERROR at line 1:
    ORA-00920: invalid relational operator

    2.

    SQL> Select r.rname from renter r, contract o, car c where r.rno=o.rno and o.cno=c.cno and c.cno='C2';
    RNAME
    ----------
    JOHN
    SCOTT
    2 rows selected

    3.

    SQL> Select distinct r.rname from renter r, contract o, car c where r.rno=o.rno and o.cno=c.cno and c.age=1;
    RNAME
    ----------
    JOHN
    SCOTT
    SARAH
    3 rows selected

    4.

    SQL> Select c.age from car c, contract o, renter r where c.cno=o.cno and o.rno=r.rno and r.rno='R1';
    AGE
    ----------
    1
    1
    3
    4
    5
    1
    6 rows selected.

    5.

    SQL> SQL> Select cno from contract where rno IN (select rno from renter group by rno);
    CNO
    -----
    C6
    C5
    C4
    C2
    C1
    C3
    C6
    C5
    C4
    C3
    C2
    C1

    12 rows selected.

    6.

    SQL> select DISTINCT r.rname from renter r, contract o where r.RNO = o.RNO and o.cno Not IN('C2');
    RNAME
    ----------
    MESSY
    JOHN
    SCOTT
    SARAH

    7.

    SQL> select RNO from renter where city = (select city from renter where RNO = 'R1');
    RNO
    -----
    R1
    R4

    8.

    SQL> select RNO from contract where CNO IN (select CNO from contract where RNO = 'R2');
    RNO
    -----
    R1
    R1
    R2
    R2

    9.

    SQL> Select c.cname from car c, contract o WHERE c.cno=o.cno ORDER BY O.AMT;
    CNAME
    ----------
    TOYOTA
    MAZDA
    HONDA
    NISSAN
    BMW
    MERCDES
    MAZDA
    MERCDES
    HONDA
    BMW
    NISSAN
    TOYOTA

    12 rows selected.


    ----------------------------------------------------------------------

    That’s all ^_^

    Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You should read & follow Posting Guidelines as stated in URL below:
    http://www.dbforums.com/showthread.php?t=1031644

    Also please NOTE capabilities as stated on
    http://www.dbforums.com/misc.php?do=bbcode
    especially the "<code tags>".

    Learn & use the "Preview Post" button prior to "Submit Reply" button
    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.

  5. #5
    Join Date
    May 2008
    Posts
    3
    Dear anacedent;

    thanks for the links

Posting Permissions

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