ACCESS 2000

(Note: cut and paste the SQL answers for questions 1 and 2 into a word document.

1. Write the
SELECT
FROM
WHERE
code that will select the row from the authors table in the attached WEEK1_Q1_Q2_ACCESS2000 database that has "twain, mark"

NOTE: You can use:
SELECT *
or
SELECT author_name
or
SELECT authors.author_name

in the SELECT line. Either one is fine since the table only has one column. Including the table name (as in "SELECT authors.author_name") is completely fine, in fact you'll have to do that when you have two or more tables involved in a query.

2. Select the row from the authors table where the author's name contains an "a" anywhere in their name (using the same attached database)

3. From the ORDER_LINES table, select the ISBNs and quantity where the quantity column is greater than one.

4. You may end up thinking this question is "too easy" and may wonder what my point is in having you do it. Its strictly for familiarization in using the INSERT command. I'll never have you do it again in this class & odds are that if you were in a position to add one row of data to a table, you'd type it in by hand. Your assignment is to create two tables and add data to each each. Use the attached database, WEEK1_Q3_ACCESS.mdb.

Create the CUSTOMERS table. To create the customers table use the below SQL code. Copy it into the SQL view of a new query to run it:

To get into a query's "SQL view" in Access:
1. Start at the queries TAB (as opposed to the TABLES, REPORTS, FORMS, or MACRO tabs)
2. Click on 'Create query in design view"
3. When presented with which tables or queries to chose to feed your query, click "CLOSE"
4. Now you are in a "blank" query - click on VIEW and then SQL view)

CREATE TABLE customers
(customer_numb COUNTER NOT NULL CONSTRAINT customer_numb PRIMARY KEY,
customer_first_name text(15),
customer_last_name text(15),
customer_street text(30),
customer_city text(15),
customer_state char(2),
customer_zip char(5),
customer_phone char(12),
customer_email text(40));

To actually run the query, click the button that looks like an exclamation point (!) while you are in the query's design view, or if you have saved the query then you can double click on the query's name.

When you close the query, Access will ask for a query name. Name your query "create_customers". If you were to name the query "customers", you'd have problems, as you cant create table and query names sharing the same name in Access.

INSERT data into the CUSTOMERS table. Utilize these 5 sets of SQL code. (NOTE: Unfortunately, you'll have to create five separate queries to insert the data - you cant "stack" SQL commands into one query. Use query names "INSRT_into_CUST_1", "INSRT_into_CUST_2", etc.

INSERT INTO customers VALUES (1, "Jane", "Jones", "125 W. 88th Blvd.", "Anytown", "ST", "01011", "552-555-1234", "[email protected]");

INSERT INTO customers VALUES (2, "Tom", "Smith", "4592 Maple Lane", "Some City", "SU", "12345", "555-555-4321", "[email protected]");

INSERT INTO customers VALUES (3, "Mary", "Johnson", "98 Elm St.", "Little Town", "SM", "23456", "551-555-4567", "[email protected]");

INSERT INTO customers VALUES (4, "John", "Smith", "867 Apple Tree Road", "Anytown", "ST", "01011", "552-555-9876", "[email protected]");

INSERT INTO customers VALUES (5, "Emily", "Jones", "7921 Fir Road", "Anytown", "SU", "12344", "555-555-7654", "[email protected]");

CREATE the ORDERS table. To create the orders table use this SQL code in a query:

CREATE TABLE orders (order_numb COUNTER NOT NULL CONSTRAINT order_numb PRIMARY KEY,
customer_numb long NOT NULL,
order_date DATETIME NOT NULL,
credit_card_numb text(15),
credit_card_exp_date char(5),
order_filled char(1),
CONSTRAINT customer_numb FOREIGN KEY ([customer_numb]) REFERENCES customers ([customer_numb]));

When you exit the query, Access will ask for a query name. Name it "create_orders"

INSERT data into the ORDERS table. To do this, use the existing table orders2 in the attached database WEEK1_Q3_ACCESS.mdb and this SQL code:
INSERT INTO orders
SELECT orders2.*
FROM orders2;

Name your query "INSRT_into_ORDERS"

To do this:
1. Start at the queries TAB (as opposed to the TABLES, REPORTS, FORMS, or MACRO tabs)
2. Click on 'Create query in design view"
3. When presented with which tables or queries to chose to feed your query, click "CLOSE"
4. Now you are in a "blank" query - click on VIEW and then SQL view)
5. Cut and paste this code:
INSERT INTO orders
SELECT orders2.*
FROM orders2;
6. To actually run the query, click the button that looks like an exclamation point (!)
7. When you exit the query, Access will ask for a query name - name your query "INSRT_into_ORDERS"

Attach the database to a message so that I can see that you have done the above steps.

FYI, Here's other examples of queries doing tasks similar to adding one row at a time to a table: (I sometimes hand-type in data into small tables or use the GUI for tasks like deleting data, appending data or making tables, that, but there's always an SQL equivalent, for example):

a delete query (to delete certain rows):
DELETE ColumnY
FROM TableX;
WHERE ColumnY like "*blahblah*";

an append query:
INSERT INTO TableZ
SELECT *
FROM TableX;

a make table query:
SELECT * INTO TableZ
FROM TableX;