CS 317: MS Access and SQL Examples

"???"

Syllabus
Assignments

Changed: 6/3/2002

Here are some Access and SQL examples from class.

May 30, 2002

Create a query

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

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

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

Second: Make a new form

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)