Aptech SQL 2 Practical Exam 4

Create a database name: SqlExam4

  1. Create tables as following:
    Customer (Store Customers information):

    cID (int, Primary Key) cName (varchar(25)) cAge (tinyint)
    1 Tuan Anh 19
    2 Viet Hung 22
    3 Hoang Minh 40
    4 Ngoc Hai 14

    Product (Store Products information)

    pID (int, Primary Key) pName (varchar(25)) pPrice (int)
    1 Ban La 1000
    2 Quat Chay 500
    3 May Bom Hong 20
    4 Tivi 5000
    5 Tu Lanh 7000

    Order

    oID (int, Primary Key) cID (int, Foreign Key References Customer.cID) oDate (DateTime) oTotalPrice (int)
    1 1 09/15/2007 NULL
    2 3 09/20/2007 NULL
    3 1 09/23/2007 NULL

    OrderDetail

    oID (int, Foreign Key References Order.oID) pID (int, Foreign Key References Product.pID) odQTY (int, Order quantity)
    1 1 3
    1 3 5
    1 4 2
    2 5 1
    2 5 4
    2 3 5
    3 1 12
  2. Display Order as following (Result must be ordered by Date):
    oID cID cDate oTotalPrice
    3 1 2007-09-23 00:00:00.000 NULL
    2 3 2007-09-20 00:00:00.000 NULL
    1 1 2007-09-15 00:00:00.000 NULL
  3. Display the most expensive product:
    pName pPrice
    Tu Lanh 7000
  4. Show customer name with each item he bought like:
    cName pName
    Hoang Minh Tu Lanh
    Hoang Minh Tu Lanh
    Hoang Minh May Bom Hong
    Tuan Anh Ban La
    Tuan Anh May Bom Hong
    Tuan Anh Tivi
    Tuan Anh Ban La
  5. Show customers that didn’t buy any thing:
    cName
    Viet Hung
    Ngoc Hai
  6. Display sold items as following:
    oID oDate odQTY pName pPrice
    1 2007-09-15 00:00:00.000 3 Ba La 1000
    1 2007-09-15 00:00:00.000 5 May Bom Hong 20
    1 2007-09-15 00:00:00.000 2 Tivi 5000
    2 2007-09-20 00:00:00.000 1 Tu Lanh 7000
    2 2007-09-20 00:00:00.000 4 Tu Lanh 7000
    2 2007-09-20 00:00:00.000 5 May Bom Hong 20
    3 2007-09-23 00:00:00.000 12 Ba La 1000
  7. Show total cost in each Order:
    oID oDate Total
    1 2007-09-15 00:00:00.000 13100
    2 2007-09-20 00:00:00.000 35100
    3 2007-09-23 00:00:00.000 12000
  8. Create view name Sales show total cost of all Orders:
    60200
  9. Remove all constraints in all tables
  10. Create a trigger name: casUpdate on Customer table. This trigger will update cID in Order table when cID in Customer table changed
  11. Create a Store procedure name delCustomer with a parameter @name (Customer name). When input a customer name, this procedure will delete all the customer infomation in Customer table, Order table, OrdeDetail table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s