Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Jun 2007
    Posts
    25

    Unanswered: Create View from specification (was "Help!!!!")

    I have to try and get my head around this question and ive never seen sql this advanced before.please set me in the right direction.thanks in advance.

    Using the meeting table schema below provide a SQL statement to create a view in Microsoft SQL Server that will return only 1 record displaying the contents of the MEETING_NUMBER field. The rules for the view are as follows:
    • The START_DATE field which contains the start date of the meeting record must contain a value before the current system date.
    • The START_DATE field must be the closest date to the current system date.

    ID int (4)
    DOC_CLASS varchar (30)
    DOC_NUMBER int (4)
    REVISION int (4)
    START_DATE datetime (8)
    FINISH_DATE datetime (8)
    MEET_TIME varchar (5)
    VENUE varchar (50)
    TYPE varchar (50)
    MEETING_NUMBER varchar (11)
    CUTOFF_PERIOD int (4)
    CUTOFF_TIME varchar (5)
    AGENDA_STATUS varchar (50)
    SUBJECT varchar (50)
    SPONSORING_MIN varchar (50)
    SUPP_AGENDA_STATUS varchar (50)

    Possible answer:
    select meeting_number
    from meeting
    where start_date < getdate()
    order by start_date desc
    Last edited by superdrog; 07-03-07 at 02:37.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by superdrog
    please set me in the right direction.
    Homework!!!!

    the right direction is to

    1. your notes
    2. your course material
    3. your teacher

    then give the query a try yourself and we might offer comments if it isn't working
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    25
    its a exam q for a job im apllying to.I dont have a teacher or course work or i wouldnt have bothered to ask

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, if sql is a requirement for the position you're applying for, you should be able to do this easily

    can we see your attempt?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Posts
    25
    It is part of the job.....but since im starting at a graduate level ive never seen sql this advanced before.Im a Java programmer by college standards I suppose

    Heres my attempt:

    CREATE VIEW Meeting_View
    AS
    SELECT Meeting.MEETING_NUMBER
    FROM Meeting
    WHERE START_DATE < GETDATE()
    Last edited by superdrog; 07-03-07 at 02:39.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good start

    that takes care of one criterion, now let's see if we can add the other one -- "The START_DATE field must be the closest date to the current system date"

    this means there cannot be another row that is closer

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

  7. #7
    Join Date
    Jun 2007
    Posts
    25
    Will the getdate() function work without a declaration???

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not sure what you mean by "declaration" but i suspect the answer is yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I think he means, can you call getdate() in a where clause, or do you have to assign it to a variable first, and the use the variable in a where clause.

    if that's what you meant, answer is yes.

  10. #10
    Join Date
    Jun 2007
    Posts
    25
    thats what i meant....sorry....im not used to sql syntax

  11. #11
    Join Date
    Jun 2007
    Posts
    25
    CREATE VIEW Meeting_View
    AS
    SELECT Meeting.MEETING_NUMBER
    FROM Meeting
    WHERE START_DATE < GETDATE()
    AND EXISTS
    (SELECT Meeting.MEETING_NUMBER
    FROM Meeting
    WHERE START_DATE < GETDATE()
    ORDER BY START_DATE DESC)

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, that's progress, i guess, although you still have a ways to go

    let's have a look at that subquery

    the subquery says "get all meetings that start before right now"

    as long as there is at least one meeting that starts before right now, the EXISTS will evaluate true

    so if there are any meetings that start before right now, your view will return all of them, and if there aren't, then it won't return any rows at all

    does that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2007
    Posts
    25
    I know i need a single value.A single date closest to the current date but will DISTINCT work?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you will need MAX at some point

    you want the latest date that's less than getdate()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2007
    Posts
    25
    CREATE VIEW Meeting_View
    AS
    SELECT Meeting.MEETING_NUMBER
    FROM Meeting
    WHERE START_DATE < GETDATE()
    AND EXISTS
    (SELECT MAX(START_DATE)
    FROM Meeting
    WHERE START_DATE < GETDATE())

Posting Permissions

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