Database Concepts & Notes - 1

Definition: a collection of tables, each consisting of rows & columns
Each row is a record; each column is a field
Relational DBMS models are used in Access, SQL Server, FoxPro, Dbase, Btrieve & Oracle.

Table Definitions & Recordsets

Recordset: a virtual table that can be
- an actual table
- a subset of the table
- a subset of data extracted from a number of tables

DDL & DML - Data Definition Language & Data Manipulation Language
Schema - an overall view of the structure of the database

SQL - Structured Query Language

Select Field1 [, field2, ...] From Table [Where Criteria] [Order by fields to sort] ;

Some examples:

Select [Employee Number] from Employees;
' returns a column of all the employee numbers in the Employees table
Try not to pick field names with embedded spaces!
Select EmployeeNumber, LastName, FirstName From Employees
' returns 3 columns: the employee number and the employees' full name
Select EmployeeNumber, LastName, FirstName From Employees Order by LastName, FirstName;
Sorted by Last Name then First Name
Select EmployeeNumber, LastName, FirstName From Employees Order by LastName Desc, FirstName Asc;
Sort Last Name in descending order, First Name in ascending order
Select * From Employees Order by LastName, FirstName;
return all the fields sorted by lastname, firstname
Select * from Employees where EmployeeNumber = 100;
return only the record with employee number 100
Select * from Employees Where (EmployeeNumber >= 010 and EmployeeNumber <=100);
return all the employees in the range
Select * from Employees Where (LastName = 'Smith' and FirstName = 'Charles');
use single quotes for strings
Select * from Employees Where LastName Like 'Sm%';
return all employees whose last name starts with Sm. % is similar to *
Select * from Employees Where LastName Like 'Sm???';
return employees with lastnames that are exactly 5 characters long that begin with Sm

Joins and Multiple Tables

Select LastName, FirstName, Paychecks.* From Paychecks Inner Join Employees on Paychecks.EmployeeNumber = Employees.EmployeeNumber;

Outer Joins:
Select LastName, FirstName, Paychecks.* From Paychecks Left Join Employees on Paychecks.EmployeeNumber = Employees.EmployeeNumber;
All records from the first table (Paychecks) are returned, whether they have a match on the second (Employees)

Right Join
Select LastName, FirstName, Paychecks.* From Paychecks Right Join Employees on Paychecks.EmployeeNumber = Employees.EmployeeNumber;
All records from the second table (Employees) are returned, whether they have a match on the first (Paychecks)

Last edited by DrB on Wednesday, February 18, 2009