SQL Microsoft Access

1
SQL / Microsoft Access

• MIS 70-451
• June 15, 2007

2
Agenda

• Create tables and add data in Microsoft Access
• Create a database
• Create a table
• Define attribute names, domains, attribute
  properties, and identify primary keys
• Save the table structure
• Add data to the table.
• SQL exercises
• Create tables
• Hardware database

3
Creating a New Database Employee Table

• Creating and naming the database file
• Click on Create a new file
• Click on Blank Database
• Select location for new database
• File saved with .mdb extension
• Can also open an existing database

4
(No Transcript)
5
Creating Tables

• Access DBs are made up of objects, which can be
  created, selected, and manipulated as a unit
• Table object is the basic unit of a DB and must
  be created first, before other objects are
  created.
• Objects bar along the left edge of database
  window organizes database objects into types and
  groups
• Click on Table object. Several methods to create
  tables
• Table Wizard Set of steps to generate table
  automatically
• Design View Permits more advanced options
• Datasheet View Enter data and Access defines
  accordingly
• We will use Design View

6
(No Transcript)
7
Table Design

• Separate toolbar for table design view
• Define field names
• First enter Employee_ID
• Then choose data type Text
• Enter field description
• Define this field as primary key by
  right-clicking on it and selecting Primary Key
• Define additional fields
• Finally, save the table by naming it
• Define field properties
• Located in lower part of window
• Enter field size

8
Access Data Types

• Text Text entries up to 255 characters in
  length. E.g., Name and phone number
• Memo Text that is variable in length and too
  long to be stored in a text field
• Number Numeric digits only
• Date/time Any valid date
• Currency Same as number field, but formatted to
  display decimal places and a currency symbol
• Autonumber Generates a key
• Yes/no Accepts binary entries
• OLE Accepts data objects, e.g. pictures, sound,
  spreadsheet

9
SQL Exercises 1 Create Tables

• Database has following tables
• Student
• Student_ID, Last_Name, First_Name, Phone,
  Street, City, Zip
• 2. Registration
• Student_ID, Course_Number, Term, Reg_Date, Score
• 3. Course
• Course_ID, Term, Name, Description, Room_Number,
  Bldg_Name, Faculty_ID
• 4. Faculty
• Faculty_ID, Last_Name, First_Name, Phone,
  Office_Room, Office_Bldg
• Underlined Primary Key, Italicized Foreign
  Key
• Tip Create the tables with no foreign keys
  first, Order of creating tables
• Student and Faculty
• Course
• Registration
• WHY do we need this order of creating tables?

10
Create Faculty Table

• Use the CREATE TABLE command to create the
  Faculty Table, including all attributes, and
  primary key.
• How to get to SQL?
• In the Database window, click Queries under
  Objects, and then click New on the Database
  window toolbar.
• In the New Query dialog box, click Design View,
  and then click OK.
• In the Show Table dialog box, click Close (or
  Cancel).
• On the View menu, click SQL View. This opens the
  SQL view window and displays the SQL syntax that
  Access is using for this query.

11

• CREATE TABLE Faculty (
• Faculty_ID Number,
• Last_Name TEXT(15),
• First_Name TEXT(15),
• Phone TEXT(50),
• Office_Room Number,
• Office_Bldg TEXT(50),
• PRIMARY KEY (Faculty_ID))

12
Create Course Table

• Use the CREATE TABLE command to create the Course
  Table, including all attributes, primary and
  foreign keys
• Make sure that the tables to which this table is
  referring to have been created (Student and the
  Faculty Tables).

13

• CREATE TABLE Course (
• Course_ID Number,
• Term TEXT(15),
• Name TEXT(15),
• Description TEXT(50),
• Room_Number Number,
• Bldg_Name TEXT(50),
• Faculty_ID Number,
• PRIMARY KEY (Course_ID, Term),
• FOREIGN KEY (Faculty_ID) REFERENCES
  Faculty(Faculty_ID))

14
Insert Data

• Faculty
• Double click the Faculty Table and insert data as
  you like.
• Course
• Insert data into the Course Table. Course No
  70451Term Fall 2005Name Introduction to
  MISDescription Undergraduate
• Room Number 153
• Building Name Posner Hall

15

• INSERT INTO Course ( Course_ID, Term, Name,
  Description, Room_Number, Bldg_Name)
• VALUES (70451, "Fall 2005", "Introduction to
  MIS", "Undergraduate", 153, "Posner Hall")

16
Exercise 2 SQL queries

• Load the sample database Help-gtSample
  Databases-gtNorthwind Sample Database
• Examine the table definitions by right-clicking
  each table and choosing designer view

17
List all customers from UK

• Open Queries gt New gt Design View gt Select
  Customer Table gt Add.
• Double-Click on the Columns we want.

2. RUN Query
1. Enter Query Criteria
18
Plain Query with Where Clause

• How do we do this in plain SQL?
• Query gt New gt Design View gt Ok gt Cancel gt View gt
  SQL.
• Then type
• SELECT Customers.CustomerID, Customers.ContactName
  ,
• Customers.Country
• FROM Customers
• WHERE Customers.Country "UK"

19
Query Selection Criteria

• AND condition
• OR condition
• Relational operators
• Between function
• NOT function
• Wild card

20
AND Query

• List the detailed information for customers from
  London, UK?
• SELECT Customers.
• FROM Customers
• WHERE Customers.Country "UK"
• AND Customers.City "London"
• Note You can list all the columns of interest in
  the Select Clause. lists all the columns.

21
Between Query

• What were the orders between July 1, 1997 and
  July 31, 1997?
• SELECT Orders.
• FROM Orders
• WHERE Orders.OrderDate between 7/1/1997 and
  7/31/1997
• is used to signify dates.

22
Order By

• Sorting data.
• Display the Products Table, sorted by Product
  Name.
• SELECT Products.
• FROM Products
• ORDER BY Products.ProductName
• Sort by Descending ProductName?
• The Order By changes to
• ORDER BY Products.ProductName DESC

23
JOINS

• What orders were from UK companies placed in
  1996? Sort results by CompanyName.
• Need data from Customers and Orders tables.
• Join the two tables using the common column
  between them.
• SELECT Customers.CompanyName, Orders.OrderID,
  Orders.OrderDate
• FROM Customers, Orders
• WHERE Customers.CustomerID Orders.CustomerID
  and (Orders.OrderDate between 1/1/1996 and
  12/31/1996) and Customers.Country"UK"
• ORDER BY Customers.CompanyName

24
Using Built In Functions

• What is the total number of customers?
• SELECT Count(Customers.CustomerID)
• FROM Customers
• What is the count of customers in each country?
• SELECT Count(Customers.CustomerID),
  Customers.Country
• FROM Customers
• GROUP BY Customers.Country
• Remember When using functions like SUM, Count,
  Min, Max, AVG, need to GROUP BY columns that are
  not participating in these functions (such as
  Country above).

25
Using Built In Functions

• What is the number of customers in each city in
  each country?
• SELECT Customers.Country, Customers.City,
  Count(Customers.CustomerID)
• FROM Customers
• GROUP BY Customers.Country, Customers.City

26
Summary

• Create a database in ACCESS
• Define a table and add data
• Creating different types of queries
• Use of Design and SQL view