Instructions

Work through the SQLcourse.com tutorials at least once and be sure to try out the code in the tutorial. For
instance, on the SELECT page, they show you the code you can use to display all of your fields using the “*” sign
rather than having to enter each field name. If you just focus on trying to do the minimum for the exercise, you
won’t learn very much and you won’t catch that nifty shortcut.

Work through the tutorial again to answer the homework questions. Use the SQL interpreter on the
sqlcourse.com web site, write and test the SQL code needed to answer the questions on the homework
assignment and then paste the code AND your screen captures of your results beneath each question. Make
sure your SQL statements work for you before pasting the code and screen captures into the exercise.

Screen Captures: To get a Snipet screen capture of your results, please use the Windows Snippet tool
(preferred) or you can click the <PrintScreen> key on your keyboard to copy your contents to the Windows
clipboard and then paste the contents into Word <CTRL>V. However, if you use <PrintScreen>, you will then need
to crop your picture to make it readable. Instructions are on the assignment’s Getting Started instructions.)

SQL Assignment

For Questions 1 – 3, please use the Employee table that was used in SQLcourses.com tutorials for Table Basics
and Selecting Data. The questions below follow along with the tutorial. Please keep in mind that the questions
build upon what you have learned on previous screens, so if you don’t remember how to do something, look
back at what you have learned thus far. For each question, (1) write the SQL statements below and (2) use the
Windows Snipping tool to copy your results. . Make sure your SQL statements work for you before pasting them
below.
NOTE: I suggest that you write the SQL statements in the SQL Interpreter at sqlcourse.com and then paste them
below. (Word puts some odd special characters into content that may cause your statements not to work if you
paste from Word into the SQL Interpreter.) Please remember to include the semicolon “;” at the end of the SQL
statement.

1a. Write a SQL statement to display all of the fields and all of the records in the empinfo table without specifically
listing each field name. Hint: Use the “*” as described in the middle of the web page tutorial. (Be sure to include
your exact SQL statement below followed by a screen capture of your statement’s results. Please do this for each
question.)

select * from empinfo

1b. Snipet screen capture your results from the interpreter and paste them below.
 2a. Write a SQL statement to display only the id, first name, last name and state for everyone who is 40 years old
or older.

select first, last, id, age from empinfo

where age >= 40;

2b. Snipet screen capture your results from the interpreter and paste them below.

3a. Write a SQL statement to display the first name, last name, and city of everyone with a ‘e’ anywhere in their
last name.

select first, last, city

from empinfo
where last LIKE '%e%';
3b. Snipet screen capture your results from the interpreter and paste them below.
4. Create an Accounts Receivable table in SQLcourse.com and use for the next set of questions.

Read the instructions on the Creating Tables web page. Use the code below to create the following accounts
receivable (AR) table being sure to name the table with your first initial, last initial, the letters ar and a random
number. The name of your table will look something like “dsartable26” - this stands for Dana Schwieger AR Table
26.

*** Be sure to put a space after each comma. Do not type <space>.*** You create a table one time and then
insert the records into that table.
create table yourinitialsarnumber create table yourinitialsarnumber
(firstname varchar(15), (firstname varchar(15), <space>
lastname varchar(30), lastname varchar(30), <space>
company varchar(20), company varchar(20), <space>
state varchar(15), state varchar(15), <space>
moneydue number(5)); moneydue number(5));

Once your table is created, you should see a result similar to:

Data Dictionary for Fields

 Firstname: First name of company representative

 Lastname: Last name of company representative
 Company: Name of company that made the purchase
 State: State where company is located
 Moneydue: Total amount owed by company

4a. Test and write the code below that you used to create your table. (SQL is case sensitive. Please note how you
type your fieldnames.)

create table ldartable093

(firstname varchar(15),
lastname varchar(30),
company varchar(20),
state varchar(15),
moneydue number(5));
4b. Snipet screen capture your results from the interpreter and paste them below. Remember that you have
learned how to display your records using the SELECT statement in the first three questions.

5. Follow the instructions on the “Inserting into a Table” web page to insert six records into your table.
 Please enter the three records displayed below (Please insert your first name in the second record.) and
 Three records of your own with payments between 2500 and 4000 dollars
(Make up data and do not input any people that you know.)
For this interpreter, you will need to write the code to enter each record individually; however, there are other
systems that support more efficient code.) ** When you insert a record successfully, the screen will just display
the phrase “SQL Command Executed .” Similar to :
  Suzie, Miller, Acme Medical, Missouri, 2050
 YourFirstName, Andrews, Computers Plus, Illinois, 2750
 Robert, Winters, Computers Plus, Missouri, 3675

insert into
ldartable093
(firstname, lastname,
company, state, moneydue)
values ('Robert', 'Winters',
'Computers Plus', 'Missouri',
3675);

insert into
ldartable093
(firstname, lastname,
company, state, moneydue)
values (' Jonie', ' Weber',
' Computers Plus ', 'Illinois',
3500);

insert into
ldartable093
(firstname, lastname,
company, state, moneydue)
values (' Potsy', ' Weber',
' Computers Plus ', 'Missouri',
4000);

insert into
ldartable093
(firstname, lastname,
company, state, moneydue)
values (' Linda', ' Weber',
' Computers Plus ', 'Missouri',
3000);
5a. Show the SQL code you used to insert two of your records. (In writing the SQL code below, you only need to
show me the code for two of your records (part a); but show the results for all six of the records your entered (part
b).

insert into
ldartable093
(firstname, lastname,
company, state, moneydue)
values ('Suzie', 'Miller',
' Acme Medical ', 'Missouri',
2050);

insert into
ldartable093
(firstname, lastname,
company, state, moneydue)
values ('Linh', 'Andrews',
' Computers Plus ', 'Illinois',
2750);

5b. Remember to use a SELECT statement to show the results for all of the entries (part b) and screen capture
your results below.

select * from
ldartable093

6a. Write a SQL statement to display just the last name, company, state and moneydue of all clients owing more
than 2700.

select lastname, company, state, moneydue

from ldartable093
where moneydue > 2700

6b. Snipet screen capture your results from the interpreter and paste them below.
 7a. Write a SQL statement to display all of the fields for clients owing more than 2800 from Computers Plus.

select * from ldartable093

where (moneydue>2800) AND (company='Computers Plus');

7b. Snipet screen capture your results from the interpreter and paste them below.

8a. Write a SQL statement to update all Missouri states to MO.

update ldartable093
set state = 'MO'
where state = 'Missouri'

8b. Snipet screen capture your results from the interpreter and paste them below.

9a. Write a SQL statement to delete all customers with the state value of MO.

delete from ldartable093

where state = 'MO';

9b. Snipet screen capture your results from the interpreter and paste them below.
10a. Write a SQL statement to drop your table and its structure from the system. Run your SQL statement to drop
your table.

drop table ldartable093;

 10b. Snipet screen capture your results from the interpreter and paste them below.

Use the Items Ordered Table from the SQL Course web site for the following questions. (This table is mentioned
on the “HAVING clause” page.) (Be sure to paste your SQL statements and results beneath each question.)

11a. Write a SQL statement to display item and quantity from the items_ordered table for items with a quantity
not equal to 2.

SELECT item, quantity

FROM items_ordered
GROUP BY item
HAVING quantity <> 2;

11b. Snipet screen capture your results from the interpreter and paste them below.
 12a. Write a SQL statement to count the number of customers from each state and display the state and count.

SELECT state, count(state)

FROM customers

GROUP BY state;

12b. Snipet screen capture your results from the interpreter and paste them below.
13a. Write a SQL statement to find for each customer, the sum of prices and quantities. Select the customerID,
the sum of the price and the sum of the quantity that was purchased. (Don’t forget to group.)

SELECT customerid, count(customerid), sum(price), sum(quantity)

FROM items_ordered
GROUP BY customerid;

13b. Snipet screen capture your results from the interpreter and paste them below.

14a. Write a SQL statement to display the customerid, order_date, item, and price from the items_ordered table
where the price is between 25.00 and 50 dollars. Display the results in descending order based upon customerid.

SELECT customerid, order_date, item, price

FROM items_ordered

WHERE price BETWEEN 25.00 AND 50.00

ORDER BY customerid DESC;

 14b. Snipet screen capture your results from the interpreter and paste them below.

15a. Write a SQL statement using a join to determine which items were ordered by each of the customers in the
customers table who live in Idaho, Colorado, or Hawaii. Select the customerid, lastname, state, order_date, item,
quantity, and price.

SELECT customers.customerid, customers.lastname, customers.state, items_ordered.item,

items_ordered.order_date, items_ordered.price, items_ordered.quantity
FROM customers, items_ordered
WHERE state IN ('Idaho', 'Colorado', 'Hawaii');

15b. Snipet screen capture your results from the interpreter and paste them below.