SQL

International Webmasters Association
HTML Writers Guild Online Classes Program

Jan Nylund
2002

Första sidan

• Week 1, Build a Database
• Week 2
• 1. Write select statements using the like and not like clauses. Show your SQL statement and explain why or when you would be using this statement.
  2. Write select statements using the in and not in clauses. Show your SQL statement and explain why or when you would be using this statement.
  3. Use the between clause for a date search on your database. Show your SQL statement and explain why or when you would be using this statement.
  4. Write 2 select statements: one which uses at least two where conditions with an AND, the other which uses at least two where conditions with an OR. Show your SQL statements and explain why or when you would be using these statements.
  5. Write 2 insert statements for your database: the first with all of the fields receiving a value, and the second with only select fields receiving a value. Display your insert statements for my review.
     
     
• Week 3
• 1. Set up 3 different order by queries like below: Select ItemDescription from Item Order By Cost Select ItemDescription from Item Order By Cost DESC Select ItemDescription, Cost from Item Order By ItemDescription, Cost Also discuss why you'd use these 3 statements.
  2. Set up two different group by statements: one select count(*), the other a sum({Field}), and you may use a WHERE clause or omit it as you see fit. Use the AS keyword to rename the count(*) and sum({Field}) columns. Analyze your results, and discuss why you would use the aggregate statement. If sum does not result in meaningful results in your database, you may use a different aggregate statement such as avg, min, or max, and again explain why you are applying the statement. Please include the group by clause in both of your queries.
  3. Set up a select which includes a calculation based on two different database fields (for example, salesprice - cost). Show the SQL statement and explain why you'd use this statement.
  4. Set up a delete statement to apply against your database. Explain why the delete would take place. Also, please note that you will be adding a second table to your database next week. If you are using the table which I suggested for week 1, I am suggesting adding an inventory table for your second table in the database.
     
     
• Week 4
• 1. Add a second table to your database. Please refer to lesson 1 for rules on normalization. If you do not understand any of the normalization rules, please ask your questions in the questions area. If you are working on the Books database, you can develop an Inventory table, with a suggested key field of InventoryLocation. You can assume that each book will be placed in only one inventory location, and inventory locations can be within the same warehouse or several different warehouses. Add 5-10 records in your new table.
  2. Develop a statement using 1992 syntax which has a 2-table join and include in the WHERE clause a condition which would narrow down your results. Explain why you'd use this statement.
  3. Develop a statement using inner join syntax along with a different WHERE clause than in part 2. Explain why you'd use this statement.
  4. Develop an update statement which updates a field based on its old value, as I've done above when I changed the Salesman's contact phone to add an exchange. Explain when you would use this statement.
  5. Develop an update statement which would affect two different fields in a table, and explain why you would use the statement.
     
     
• Week 5
• 1. Add a 3rd table to your database. If you are using the suggested Books database, you may add either a Publisher, Author, or Sales table to your database. Please review the normalization rules discussed in the week 1 lecture. Add 5-10 records to your new table.
  2. Prepare a 3-table join not using aliases, using both 92 and inner join syntax. Use a different WHERE clause for the two SQL statements. Explain when you would use this statement.
  3. If necessary, add some records to your tables which have no matching keys in the other tables. Prepare a unique left outer join, right outer join and full outer join, each using two tables. By unique I mean, do not do the same join for the left and right outer joins, only reversing the table order.
     
     
• Week 6
• 1. Either: Create a self-join using a sub-select. Analyze what your statement is accomplishing. For further information on subqueries, please read the subqueries folder and check out the folder which contains an example of a self-join using a subquery, which has an example of a prior student's work. Or: Create 5 SQL statements based on what you have learned in this class and include at least one update and delete. Include an analysis of what the statement is to accomplish.
     
     If you want to know more about SQL read Using SQL by Rafe Colburn.

16-jun-2005