Course list http://www.c-jump.com/bcc/

SQL 101 Homework

1. Getting Started
2. Creating New Database
3. Saving the Database File
4. Opening SQL Query Window
5. CREATE TABLE Customer
6. Running CREATE TABLE
7. DROP TABLE Customer
8. INSERT INTO Customer
9. SELECT FROM Customer
10. More Practice On Your Own
11. How to Submit

1. Getting Started

• This week I want you to start using MS Access and go over a series of very basic practical exercises.

• Open Microsoft Access. You may be using versions 2010, 2007, or 2003.

• I am writing this assignment sitting in front of my Windows 7 computer running Access 2010. Your version may use slightly different set of menus or icons, but they all are very similar.

• In case if you have trouble locating a particualr function, I want you to post your question on the CIT-122 Discussion Board.

• Students who will provide timely and helpful answers on the Discussion Forum will earn xtra points towards their final grade.

   

2. Creating New Database

• I am opening MS Access 2010. I would like you to follow along and repeat all of my steps on your own computer. Alternatively, you can use BCC labs in K building or in the library, or anywhere else where you can practice with a working Access product.

• After starting the program, it displays the screen with a variety of available templates, in other words - prototypes of popular databases. I am not planning to use any of these.

• Instead, I want to work with an empty database from scratch. I am double-clicking "New Blank Database" button to get started.

   

3. Saving the Database File

• The new table named "Table1" is displayed in the so-called "Datasheet view" as soon as the new database is created.

• Since I am planning to manipulate everything by SQL, I am closing "Table1" by right-clicking on the tip of its tab and then choosing "Close." (Please note that right-clicking on the tab may offer some really useful choices in many Microsoft products.)

• Now I have a blank screen with an empty list of "All Access Objects" pane on the right side of the screen.

• I am clicking "File" tab and then "Save Database As." (How you get to this option may differ slightly depending which version of MS Access you are using, but it is very consistent with other Microsoft Office products, such as Word or Excel.)

• The Save As dialog box opens. Please do not just accept the default location and file name! This is your homework in progress, and you should have a particular Windows folder already created for CIT-122 class work. I am saving my file in "My Documents\CIT122" folder, and changing the file name to "sql_101.accdb". You shoud always be aware of the exact location of your files!

• In Access 2003 and earlier the file name will be "sql_101.mdb". That's okay - it's an older format of the database which is still usable in later versions of Microsoft Access.

• For a completely unknown reason Access displays a Security Warning that says "some active content has been disabled..." Apparently, Microsoft is afraid of their own products, but pardon my ignorance, I am not reading any of this foolishness and just clicking "Enable Content." By the way, this Security Warning is going to show up every time I re-open Access or open a new database, so I will "Enable Content" every time this happens -- otherwise certain SQL commands may be disabled by Access!

   

4. Opening SQL Query Window

• Click Create tab on top of the ribbon.

• Click Query Design icon.

• A tab named "Query1" opens. On top of it, a "Show Table" dialog is displayed. This is an annoyance, because we have no tables and we don't want to use any helpers in specifying what to do.

• Dismiss the "Show Table" dialog box simply by closing it. You will do this every time you need to create a new query.

• By the way, the whole notion of a "query" is misleading at this point, because "query" means "an inquiry", but we know our database is empty at this point, so it's kind of meaningless. However, this is not Access's fault; in SQL, all commands are called "queries," this is just a technological term.

• An empty "Query1" tab opens in the so called "Design View". We need to switch to the "SQL View." To do this, locate the "View" in the "Results" group of the "Design" tab. Here is how it looks like on my screen:

   

  After switching to SQL View, the "Query1" changes to a simple text entry window with a SELECT keyword and a semicolon ";" like this:

   

  A semicolon can be entered at the end of any MS Access SQL statement. In many cases it is optional, so when you enter your own SQL commands you don't have to type the semicolon. Some textbooks use the semicolons, but I will avoid adding them at the end of my SQL examples.

   

5. CREATE TABLE Customer

• With "Query1" SQL View open, type in the following command (you can copy and paste it from this handout, but I would also recommend keeping your own text file with queries that you use in this course.)

  CREATE TABLE Customer 
      (
          CustomerID INTEGER,
          LastName TEXT
      )
  

  It looks like this on my screen:

   

   

• You can execute this query simply by clicking "Run!" in Access 2010:

   

  However, in Access 2007 this will give you a strange error message about non-existing database object. To stay consistent and trouble-free accross multiple versions of MS Access, I want you to save your query by right-clicking on the "Query1" tab and selecting "Save."

   

• Instead of just saving what you have so far, the database asks you to
  specify the Query Name:

   

  I am saving my query with the descriptive name CREATE_TABLE_Customer. This should always be the case when you work with Access. Never save your database objects as "Query1" or "Table1" - this is a very poor choice. You could also notice that instead of spaces between words I am using the underscores. In some databases, spaces are not allowed anywhere in the names of tables or queries. So my habit is to use the underscore characters instead. Although I am not encouraging it, it's probably okay for you to use spaces if you prefer. (More on this later.)

   

6. Running CREATE TABLE

• Once the query is saved, you can execute it like this:

   

  The result of CREATE TABLE query is a new, empty database table named "Customer". The table has two columns: the "CustomerID", which is an INTEGER, that is, a numerical field, and "LastName", which has a TEXT type - accepting strings of characters. (More on data types later.)

• If you double-click the "Customer" on the list of tables, it will open in a separate tab in "Datasheet View". There you can actually input some data into the table.

• Close the "Customer" table tab. (In fact, once I am done using a database object, such as a query or a table, I have a habit of closing it for two reasons: to reduce clutter, and often to avoid warnings that Access needs to close it before running a particular command. As you try different things, you will see this kind of message quite often in Access.)

   

7. DROP TABLE Customer

• Click Create tab on top of the ribbon.

• Click Query Design icon.

• Again, a tab named "Query1" opens. On top of it, a "Show Table" dialog is displayed, which you need to close.

• Switch to "SQL View" and enter the following SQL statement into the query text window:

      DROP TABLE Customer
  

• Save the query as DROP_TABLE_Customer

• Click the "Run!" icon to execute the DROP_TABLE_Customer query. In Access 2007 you get a warning that you are about to delete a database object. In Access 2010, the table is silently deleted.

• Why would you want to delete a table? Perhaps you created it by mistake, or you misspelled the table name and need to correct this mistake. Sometimes tables are used as temporary tables. In other cases, you may wish to restructure an existing database and delete a table that you know is no longer used.

• Note that in Micrsoft Access, the table is deleted even if it contains the actual data rows. Other SQL platforms, such as SQL server or Oracle, may refuse to delete a non-empty table. In that case you have to delete the data from the table first:

      DELETE FROM Customer
  

  The "DELETE FROM Customer" statement removes all data rows from the table, so the table becomes empty.

• Once the table is deleted by "DROP TABLE Customer", you can re-create it by opening the CREATE_TABLE_Customer and then again choosing "Run!"

   

8. INSERT INTO Customer

• Our next step is to populate some data in the "Customer" table. This can be done by opening the table and entering the data directly. However, our goal is to keep using the SQL for every manipulation of the content in our database. So instead of using the "Datasheet View", we will create a new query.

• Do all the steps to create the new query as outlined above. Switch to SQL view and paste in the following command:

  INSERT INTO Customer ( CustomerID, LastName )
                 VALUES ( 1001,       'JONES'  )
  

  The INSERT statement inserts a single row into the database table. This is very tedious and may seem much less convenient rather than entering the data directly (like you do in Excel.) However, the INSERT statement is exactly the way the data is manipulated when you go to an ATM machine and withdraw $20.00 dollars from your bank account, or when you register your Facebook account, or in general, when you create new record in any software that runs on top of some relational database.

• Save the query as INSERT_INTO_Customer, and "Run!" Typically, Access gives you a warning telling that you are about to insert a row into a table. Why this is happening is beyond my ability to know. You will never see this warning if you wrote a program in a different programming language and invoked your SQL query from that program. I prefer when programs are less verbose and generally keep their message boxes away from what I am trying to do. 'Nough said.

• You can open the "Customer" table and see the results. There should be one row of data present there with "1001, JONES" entered there.

• Next, change your SQL command to

  INSERT INTO Customer ( CustomerID, LastName )
                 VALUES ( 8027,       'SMITH'  )
  

  then save it, and run it again. This will insert another row into the table -- you get the idea.

• Modify your INSERT query to insert a couple more rows with different data.

   

9. SELECT FROM Customer

• Now it's time to run a query that returns back the data that was already inserted into the database table.

• For example:

      SELECT * FROM Customer
  

  The above is a typical SELECT statement that returns all data columns and rows from the table. As before, you should create a new query, enter the SQL command, and save it as SELECT_ALL_CUSTOMERS. As was explained in the previous handout, the asterisk (*) is a shorthand notation for selecting all columns from the table.

• Create one more query and enter SQL

  SELECT
      LastName
  FROM
      Customer
  WHERE
      CustomerID = 1001
  

  Decide what name would you like to use when saving this query. If you run this query, it should yield result 'JONES', which is the value stored in the "LastName" column of the "Customer" table in the row identified by the unique customer ID 1001.

   

10. More Practice On Your Own

• Think about another table that could be used with a "Customer" table.

• It could be a "Sale" in a supermarket, a "SavingAccount" at a bank, or anything else.

• Make sure you have at least two columns in the new table.

• Create, save and run the following SQL queries using the new table:

  1. A query containing CREATE TABLE SQL statement

  2. A query containing DROP TABLE statement to remove the table

  3. A query containing INSERT statement that inserts a data row into the table.

  4. A query containing DELETE statement that removes all data from your table.

  5. A query containing SELECT statement that returns some data from your table.

• Please don't hesitate to post questions on the Discussion Forum if you run into any unexpected problems. Likewise, check the discussion posts frequently and help others if you know the answer!

   

11. How to Submit

• Make sure you save everything and close Access program entirely before submitting the homework.

• Submit your database file by emailing it to your instructor:
  Igor Kholodov Igor.Kholodov@bristolcc.edu

• Attach your "sql_101.accdb" file (or "sql_101.mdb" if you are using Access 2003) to the email with the proper email subject line:

          CIT122 Homework 2 Lastname, Firstname
  

  If you have trouble attaching the file using BCC email, try placing your file into a ZIP archive and submitting the ZIP file instead. It's fine with me if you are using yahoo, gmail, or any other webmail account.

• As a general rule, your email must contain your last name (mandatory!) and first name (or at least the first name initial.)