September 30

Postponed to Oct. 3

=> Postponed again to Oct. 5

1. Simple query

1) create table
2) alter
3) insert
4) select (distinct)…as…from…where…order by…
5) update

2. Advanced query

6) count/sum/avg/min/max
7) group by…
8) having
9) subqueries
10) multi table (join)

3. Various data types: datetime/varchar/char/int/decimal, Cast
4. view and security(grant and revoke)
5. index, and analyze the advantage/disadvantage of index
6. relational algebra

*The data for this lab can be found here.

  SQL Server 2000 Book Online can be downloaded from here.

*Notes:

  1. In question 3 (Part II), you don't have to create roles in SQL statement.

  2. The deliverable for question 9 should be your analysis and the data (such as the running time of queries) which your analysis is based on.

  3. In question 9, you could use any way you like to retrieve a big set of data, and also, you don't need to turn in the table you used for analysis (but you should indicate the size of table, i.e., column number and row number).

  4. In question 9, you could retrieve useful statistic data from "server trace" under "query" menu (which unfortunately isn't supported in CEC lab).  So if you have trouble getting statistic data from SQL tool, you could submit a summary of how index works, and what you guess might be the disadvantage of index in stead.

      You could refer "Book Online" and http://odetocode.com/Articles/70.aspx if you want to write a summary.

  5. In question 7, you don't have to write a single statement for the whole problem.

2. Complete complex queries using store procedures, function and transaction, and develop a webpage to support the queries.

If you are not sure about your solution to problem 7 in lab2, here's my solution to that problem:
Solution to Problem7