CS 317: MS Access and SQL Examples

Here are some Access and SQL examples from class.

May 30, 2002

Create a query

• Click the "Queries" tab.
• Double-click the "Create query in design view" button.
• Close the "Show Table" dialog box.
• Switch to "SQL View" using the button on the left side of the tool bar.

Some example queries

SELECT name, hometown
FROM Band
WHERE hometown='New York';

SELECT DISTINCT name
FROM Band
WHERE hometown LIKE 'New York*';

SELECT DISTINCT name
FROM Band
WHERE hometown LIKE 'New York*' OR hometown LIKE ‘NY*’

SELECT *
FROM CD
WHERE bandName='REM';

SELECT bandName, COUNT( title)
FROM CD
GROUP BY bandName;

SELECT bandName, COUNT( title)
FROM CD
GROUP BY bandName
HAVING COUNT( title )>=10;

SELECT *
FROM (
  SELECT bandName, COUNT( title) AS count
  FROM CD
  GROUP BY bandName)
ORDER BY count DESC;

SELECT *
FROM CD
WHERE year>=1980 AND year<1990;
    

Make a Faves Table

• Click the "Tables" tab.
• Double-click the "Create Table in design view" button.
• Add the following attributes to the table: user (Text), cdID (Number), and rating (Number).
• Make user and cdID the primary key (shift and right-click to select both)
• Save as Faves.

Look at how to fill data into the new table

INSERT INTO Faves(user,cdID)
  SELECT 'Alain', id
  FROM CD
  WHERE bandName='U2';

DELETE FROM Faves
WHERE user='Alain'

INSERT INTO Faves(user,cdID, rating)
  SELECT 'Alain', id, 5
  FROM CD
  WHERE bandName='U2';

UPDATE Faves
SET rating=3
WHERE user='Alain' AND
  cdID IN (SELECT id FROM CD WHERE year >1990);

SELECT title, bandName
FROM CD, Faves
WHERE rating=5 AND CD.id=cdID;
    

Make a form to change the ratings in Faves: Take 1

• Click the "Forms" tab.
• Double-click the "Create form using wizard" button.
• Select "Faves" under "Tables/Queries"
• Move all the attributes over to the right using the ">" button.
• Click "Next" 3 times and then click "Finish" (or just click "Finish")

This one is lame! We need the CD info (title and artist)

Make a form to change the ratings in Faves: Take 1

First: Make a relationship between Faves:cdID and CD.id

• Click the "Tables" tab.
• Double-click the "Relationships" button.
• Drag "Faves" and "CD" into the "Relationships" window.
• Connect "Faves.cdID" to "CD.id" (drag one attribute onto the other)
• Click "OK".

Second: Make a new form

• Click the "Forms" tab.
• Double-click the "Create form using wizard" button.
• Select "Faves" under "Tables/Queries"
• Move all the attributes over
• Select "CD" under "Tables/Queries"
• Move the "Title" and "bandName" attributes over
• Click "Finish"

Basic steps in building a new relation

1. Design it
2. Create it (using "Tables"--"Create table in design view")
3. Make a query (INSERT INTO) to fill it with most of the data you need
4. Make a form to fill in the rest of the data (i.e. the new data)