Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: SQL server 2008 - Queries problem

    I am new to SQL and I have to say, it is really confusing. Any help or explanation would be welcome and really appreciate.

    I have to Write a CREATE VIEW statement that defines a view named InvoiceBasic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

    Here is what I have so far but got confused with (write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.)

    CREATE VIEW InvoiceBasic
    AS
    SELECT VendorName, InvoiceNumber, InvoiceTotal
    FROM Invoices JOIN Vendors ON Invoices.InvoiceID = Vendors.VendorID

    ALTER VIEW Invoicebasic
    AS
    SELECT*
    FROM Invoices JOIN Vendors ON Invoices.InvoiceID = Vendors.VendorID
    WHERE VendorName '

    Here another one where the query doesn't work (Delete GroupMembership table from the Membership database. Then write a CREATE TABLE that recreates the table, this time with a unique constraint that prevents an individual from being a member in the same group twice)
    USE Membership
    DROP TABLE GroupMembership
    CREATE TABLE GroupMembership
    (GroupID INT Not NULL,
    IndividualID INT Not Null,
    CONSTRAINT IndividualID UNIQUE GroupIndividualID

    What is wrong with that query? Please I would really appreciate any help

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, i want to commend you on at least posting your attempt at the assignment, which sadly very few students do

    then i want to chide you for posting a SQL Server question in the MySQL forum

    but let's get on with it and start with your view

    the CREATE VIEW statement is fine, but there is an improvement that you should make in the SELECT part of the statement -- you should qualify each column in the SELECT clause the same way you did in the ON clause

    oh, and please always write INNER JOIN instead of just JOIN

    the next step was to write a SELECT statement that uses the view, and your ALTER statement is not right

    what you want is SELECT ... FROM InvoiceBasic ...

    see? the view name is in the FROM clause where you'd normally see a table name


    helps?

    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
  •