Results 1 to 2 of 2

Thread: SQL help

  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: SQL help

    Hi,

    I have a friend that need some type of help with SQL it was on a pdf file but i think i can copy it here also to get some help for him.

    This is what he is asking for help pn SQL


    Hey guys, at university this year I have an advanced Database course that I have to do.
    It involves a lot of SQL, (among other things), and I did the beginners
    Database course 2 years ago, wheras most of the class did it only last year,
    so I have forgotton a lot of it.

    Its there anyone that knows enough about advanced joins/selections that
    would be able to help me out with some revision?
    (would probably take about 30 mins).

    I know its a lot to ask, but if any of you are willing, that would be great.

    I have (kinda) answered questions a - d.The rest I am having trouble with.

    I dont need the answers however, because they dont help me learn, I need to
    learn the process under which to get the answers. This isnt an assignment for grades.
    Any help would be greatly appreciated
    I have provided him with this link http://www.w3schools.com/sql/sql_and_or.asp

    and he replyed

    Yeah, been there, done that, doesnt help much
    These are more complex than the ones online..
    Here is the codes sql from the pdf

    Code:
     COMP 3006/8542 SQL revision.
    DB2/DAD SQL revision, JLMV 2005 Page 1 of 2
    
    create table person (
    name varchar2(20) not null,
    birthdate date not null,
    sex char(1) not null,
    location varchar2(35) not null,
    primary key (name) );
    A person is uniquely identified by their name. Their date of birth and location of birth are recorded in birthdate and
    location respectively. The only two values allowed in the sex column are ’M’ (for male) and ’F’ (for female).
    create table died (
    name varchar2(20) not null,
    deathdate date not null,
    location varchar2(35) not null,
    primary key (name),
    foreign key (name) references person);
    The date and location of the named person's death are recorded in deathdate. Note a person without an entry in the died
    table is assumed to be living.
    create table is_parent_of (
    parent_name varchar2(20) not null,
    child_name varchar2(20) not null,
    primary key (parent_name, child_name),
    foreign key (parent_name) references person,
    foreign key (child_name) references person);
    The person identified by parent_name is the parent of the person identified by child_name. A person may have none, one
    or both parents identified in the database. A parent may have one to several children identified, each in a separate tuple. A
    person not identified by any parent_name is assumed to not be a parent.
    create table married (
    male_name varchar2(20) not null,
    female_name varchar2(20) not null,
    year number(4,0) not null,
    location varchar2(35) not null,
    primary key (male_name, female_name, year),
    foreign key (male_name) references person,
    foreign key (female_name) references person);
    The male person identified by male_name and the female person identified by female_name married in the specified year
    at the specified location. A person may have married none to several times. Two people may marry each other more than
    once but in different years. A person not identified in married is assumed to be unmarried.
    ***********************************************************
    Give a SQL select statement whose query result set will contain each of the following. The phrase "details" refers to all
    columns of the relevant tuples and the phrase ”currently in the database” applies to all queries.
    a. the details of all marriages in ascending location and then descending year order.
    b. the names of all fatthers; without duplication.
    c. the name of any female who has married more than twice.
    d. the person details of any widowers i.e. living husbands of dead wives
    e. the names of any male and female who have parented at least one child together.
    f. the name of the location(s) in which the most marriages has occurred.
    g. the names of any living great grand-parent i.e. parent of a grandparent.
    h. the names of all bachelor uncles i.e. unmarried brothers of parents.
    i. the names of any only child i.e. a child with both parents identified and with neither having any other children.
    j. for each location, the location's name and the name and birthdate of the oldest living person born in that location.
    k. the names of any brothers i.e. two (separate) male children with the same mother. Give the names in alphabetic order.

    Code:
     COMP 3006/8542 SQL revision.
    DB2/DAD SQL revision, JLMV 2005 Page 2 of 2
    Rem library schema
    
    create table borrower(
    borrower_id varchar2(8) not null,
    pin varchar2(8) not null,
    name varchar2(45) not null,
    email varchar2(40),
    phone varchar2(16) not null,
    mobile varchar2(10),
    borrower_type char(2) not null,
    penalty_pts number(3,0) not null,
    primary key (borrower_id));
    create table item(
    item_id number(8,0) not null,
    title varchar2(80) not null,
    item_type char(2) not null,
    primary key (item_id));
    rem any number of keywords for any items
    create table indexed(
    keyword varchar2(20) not null,
    item_id number(8,0) not null,
    score number(4,3) not null,
    primary key (keyword, item_id));
    rem copy of item is what is borrowed
    create table item_copy(
    item_id number(8,0) not null,
    copy_num number(2,0) not null,
    status varchar2(12),
    primary key(item_id, copy_num),
    foreign key (item_id) references
    item);
    rem determines loan length and extension limit
    create table loan_rule(
    borrower_type char(2) not null,
    item_type char(2) not null,
    loan_days number(3,0) not null,
    renew_limit number(1,0) not null,
    primary key (borrower_type,
    item_type));
    rem determines overdue day penalty points
    create table penalty_rule(
    start_day_num number(3,0) not null,
    end_day_num number(3,0) not null,
    day_pts number(3,0) not null,
    primary key (start_day_num));
    create table loan(
    item_id number(8,0) not null,
    copy_num number(2,0) not null,
    borrower_id varchar2(8) not null,
    due_date date not null,
    repeat_count number(1,0) not null,
    primary key(item_id, copy_num),
    foreign key (item_id, copy_num)
    references item_copy,
    foreign key (borrower_id) references
    borrower);
    rem items with no copies available can
    rem be requested
    create table request(
    request_id number(6,0) not null,
    item_id number(8,0) not null,
    borrower_id varchar2(8) not null,
    request_date date not null,
    primary key(request_id),
    foreign key (item_id) references item,
    foreign key (borrower_id) references
    borrower);
    rem a copy of a requested item when returned
    rem goes on reserve
    create table reserve(
    request_id number(6,0) not null,
    item_id number(8,0) not null,
    copy_num number(2,0) not null,
    reserve_date date not null,
    primary key(request_id),
    foreign key(request_id) references request,
    foreign key (item_id, copy_num) references
    item_copy);
    rem after check, identifies copies on shelf
    create table inventory(
    item_id number(8,0) not null,
    copy_num number(2,0) not null,
    check_status varchar2(12),
    primary key (item_id, copy_num),
    foreign key (item_id, copy_num) references
    item_copy);
    I have also attached the pdf for better reading sql codes

    Thank You in advance

    I would check back soon
    Attached Files Attached Files
    Last edited by ZRaza; 03-03-05 at 05:12.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, we don't do homework

    some of us, though, would be quite willing to help privately on a contractual basis (with a suitable deposit payment in advance, of course)

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

Posting Permissions

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