Aptech SQL 2 Practical Exam

Use SQL Query Analyzer to work with EventManagement database and create a SQL script file follow those steps:

  1. Create 3 tables and insert data as follows:
    Customer (Customer list)

    CusID (int) CusName (Varchar(50)) CusAge (Smallint) CusGender (Bit)
    1 Truong Gia Binh 45 1
    2 Vuong Vu Thang 22 Null
    3 Trieu Tran Duc 20 1
    4 Tran Ngoc Oanh 19 0

    Event (Event list)

    EvtID (int) EvtName (varchar(50) EvtType (tinyint) EvtDate (DateTime)
    1 Hacker VN 1 2005-01-13
    2 TTVN 2 2004-12-12
    3 SVHN 3 2005-01-01

    CustomerEvent (Describe attendance of Event. Example: the first row of following table denote “Truong Gia Binh has attended in Hacker VN event”)

    CusID (Int) EvtID (Int) cesClass (Int)
    1 1 1
    1 2 1
    2 1 2
    2 3 2
    3 1 1
    3 2 2
    3 3 3

    Constraints: (Student must use ‘Alter Table’ statement to Apply them)
    – Apply the Default Constraint to the EvtType column of Event table with value is 1.
    – Apply the Primary Key Constraint for CustID of Customer table, EvtID of Event table and the Composite Primary Key Constraint for the CusID and EvtID of the CustomerEvent table.
    – Create a References Constraint between Customer and CustomerEvent table on CusID named ‘Contr1’, and a References Constraint between Event and CustomerEvent table on EvtID named ‘Contr2’.

  2. Display total number of customers that has attended TTVN event.
  3. Display total number of attendance customer for each event as following:
    Event Name Total
    Hacker VN 3
    TTVN 2
    SVHN 2
  4. Display Customer list as following:
    Name Age Gender
    Truong Gia Binh 45 Male
    Vuong Vu Thang 22 Nil
    Trieu Tran Duc 20 Male
    Tran Ngoc Oanh 19 Female
  5. Create a view named ‘vwAttendanceList’ as following: (the list must sorted by Event Name, if not student will only get ½ total mark)
    Event Name Customer Name
    Hacker VN Truong Gia Binh
    Hacker VN Vuong Vu Thang
    Hacker VN Trieu Tran Duc
    SVHN Vuong Vu Thang
    SVHN Trieu Tran Duc
    TTVN Truong Gia Binh
    TTVN Trieu Tran Duc
  6. Modify the view named ‘vwAttendanceList’ such that you can create an index on it. (Hint: Add ‘With SchemaBinding’ option and remove some statement such as ‘Top’, ‘Order by’,…)
  7. Create an index named ‘indAttendanceList’ on the (‘Event Name’,’Customer Name’) column of the ‘vwAttendanceList’ view.
  8. Display name of the customer that has number of attendance in all events is greatest.
    (Notes: The correct result will be: ‘Trieu Tran Duc’).
  9. Drop References Constraint between Event and CustomerEvent table.
  10. Create an Delete Trigger named ‘tgDeleteAll’ on the Event table such that if you delete a event, all relative row on CustomerEvent table will be deleted.
  11. Create a stored procedure named ‘spDropCustomer’ that accept an integer number as parameter. The stored procedure will delete all customer who has the number of attendance in all events is less than the parameter.
    (Notes: You must ensure that this action will completely.)

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