Course list http://www.c-jump.com/bcc/
SQL stands for structured query language. It is an industry-standard programming language for managing data in relational database management systems (RDBMS).
Many ideas expressed on this webpage are heavily influenced by one my favorite books, "The Practical SQL Handbook: Using SQL Variants", Fourth Edition, by Judith S. Bowman, Sandra L. Emerson, Marcy Darnovsky. Addison Wesley, ISBN 0-201-70309-2
However, the book does not cover Microsoft Access-specific SQL dialect, so I am using my own SQL examples here and in the rest of our SQL presentations.
Relational (SQL-friendly) databases exist at the heart of all non-trivial applications - healthcare, financial, communications, scientific, and, of course, the Internet. A few examples of typical SQL users might be
End user in a company, government office, or nonprofit agency;
The owner of a small business;
The manager of a small organization;
A home computer user working on a personal project;
A student learning about database technology;
End user moving from Excel to a relational database concept;
A computer programmer who embeds SQL in a programming language (such as C++ or Java) or uses it with a scripting language (such as XML or JavaScript).
In our course we concentrate on using SQL interactively, by typing commands and receiving results directly on the screen.
(Alternatively, programmers insert (or "embed") SQL statements in a programming language.)
Microsoft Access implementation of SQL has an interactive interface for writing ad hoc queries, viewing results, tables, setting up database relationships, running end-user reports, and much more.
While Access adds hundreds of interactive dialogs, visual tools, screen wizards, etc., we will use commercial SQL (aka "industry-standard SQL") to manipulate database objects and data by typing in SQL commands.
For example, the following three SQL statements create table named "Customer" and insert two rows of data into the table:
CREATE TABLE Customer ( CustomerID INTEGER, LastName TEXT ) INSERT INTO Customer ( CustomerID, LastName ) VALUES ( 1001, 'JONES' ) INSERT INTO Customer ( CustomerID, LastName ) VALUES ( 8027, 'SMITH' )
Here, Customer is the name of the data table in the database; CustomerID and LastName are data fields, otherwise known as data columns in the Customer table. The inserted values are
| Customer | |
| CustomerID | LastName |
| 1001 | JONES |
| 8027 | SMITH |
In SQL language syntax, the data column can often be used together with its table name: Customer.CustomerID. This is casually understood as "the customer ID field from the customer table."
In the above SQL statements SQL keywords (reserved words in the language itself) are highlighted. The keywords CREATE TABLE and INSERT INTO specify what each particular statement does. Most of SQL, although intended for processing by the database software, is quite readable by us, the humans. Two more keywords, INTEGER and TEXT indicate the data types of values accommodated by the columns in the Customer table.
Similar to these SQL examples, we shall learn how to design, populate, and query SQL-based database in a step-by-step approach.
As mentioned before, SQL stands for structured query language.
SQL is a programing language designed for dealing with databases.
Power of SQL is gettting data out of databases. It is not designed to draw graphs or output reports (all of this can be accomplished using other programming languages that interface with SQL databases.)
So why?
You will understand relational database structure inside out
You will learn the limitations and possibilities of relational databases
You will discover that many complex computational tasks can be solved by single SQL statements
Your goal is not to master SQL in 6 weeks, but you will learn enough to be able to communicate with people who use SQL daily
You can retrieve data from any relational database in exact subset that you want without having to depend on other people.
Databases become ubiquitous; knowing SQL provides you with some serious competitive advantage in the job market.
I expect you to read handout materials, and even practice quizzes in front of a running Access database, so you can try each and every SQL statement on a live database.
I challenge you to practice a lot, test your ideas, and explore a vast amount of material on the web.
There is no substitute to practical experimentation with a real code window: Our second homework assignment in this class is to install (if you haven't already) a version of MS Access available for download via MSDN Alliance BCC account.
All students will receive an email similar to this:
An account has been created for you
Bristol Community College - Information Technology - DreamSpark [noreply@e-academy.com]
** This is an automated message -- please do not reply as you will not receive a response. **
Welcome to your DreamSpark WebStore:
Bristol Community College - Information Technology - DreamSpark
...
The email will contain the link to the MSDN Academic Alliance website. After registering, you click on "Software", then "Applications", and download "Microsoft Access 2010." Follow the installation instructions on your screen. Feel free to download any other software as necessary.
(If you already have an earlier version of Access (like Access 2007 or 2003) on your computer, you can continue to use that version.)
Save all your practice SQL statements in a separate file (or a set files.) Annotate both correct and incorrect syntax examples, document difficult solutions, error messages that you receive, and anything unusual that you notice.
Emphasise elegant SQL solutions in your file: you may wish to reuse them in many, many situations of your professional carrier later on.
You will soon find out that you use some SQL commands much more often than others.
Databases store information
Relational databases rely on mathematical models, based on relational algebra and relational calculus. Theoretical concepts and issues are beyond the scope of our course.
In practice, relational database is a collection of tables. Tables contain data columns. The data is stored in columns on a row-by-row basis, similar to Excel spreadseets. Unlike Excel, the data from muliple tables can be easily joined, sorted, and filtered. The users can specify exactly what data they want to see without the need for any physical rearrangement of the data in tables.
A relational database
Represents all information in the database as tables
Supports SQL (the relational language) to insert, update, and retrieve the information
Protects data integrity through transactions and recovery procedures
To locate a single piece of data (field value) you need to know:
The table name
The column name
The unique identifier of the row where the data is stored, known as primary key.
For example, given "Customer" table in the example above, SQL statement
SELECT
LastName
FROM
Customer
WHERE
CustomerID = 1001
should yield result 'JONES', which is the value stored in the "LastName" column of the Customer table in the row identified by the unique customer ID 1001.
A set of related tables forms a database
There is no hierarchical ranking of tables - all tables are equal database objects
By definition, each table is a set of rows and columns:
Each row represents a conceptual entity: a person, a company, a sale order, or some other thing.
Each column describes one characteristic of the entity: a person's name or address, a company's phone number or name of its president, an item sold in the store, a number on customer's receipt, quantity of items in the order, or a date of the sale.
SQL can be used in three areas of database operations:
Data definition: creating tables and establishing relationships between tables. For example,
CREATE TABLE OrderItem ( ItemID INTEGER, ItemName TEXT, Quantity INTEGER, Amount FLOAT )
The above CREATE statement creates brand-new table named "OrderItem" with data columns to store item names, quantities, and amounts. The Amount column has data type FLOAT which stores floating-point values representing the cost of each item.
(this subset of SQL is known as data definition language, or DDL.)
Data manipulation: storing and retrieving data from the tables:
Data modification: adding, removing, or changing the data -- using keywords INSERT, DELETE, and UPDATE, respectively.
Data retrieval: finding the particular data you want. All SQL queries to retrieve data are expressed using the keyword SELECT, which stands at the beginning of every "data select" statement.
(this subset of SQL is known as data manipulation language, or DML.)
You can SELECT from the OrderItem table, even before it has any data in it:
SELECT * FROM OrderItem
If table has no actual data, so the result is empty. Here, the asterisk (*) is a shorthand notation for selecting all columns from the table.
Data administration: for example, controlling which users can access data in a multiuser environment. This subset of SQL is beyond the scope of our course.
Short answer - the SELECT statement!
As mentioned above, all SQL data retrievals are made using SQL SELECT statement.
Here are the features that make SELECT statement (and the entire database) relational:
Ability to specify data columns that you want to include in the resulting data set. (The list of columns that you want to see is known as projection list.)
Ability to join together data from two or more tables. Typically, such tables are considered related.
The join operation works on two or more tables at a time, combining the data so that you can compare and contrast information in your database. The join operation gives SQL and the relational model a good deal of power and flexibility. You can find any relationship that exists among data elements, not just the relationships you anticipated when you designed your database. The join operation combines data by comparing values in specified columns and displaying the results. (This all may sound a bit confusing, but should become more clear as we move towards the practical part of our course.)
For example, the following selection statement is possible because two tables contain column CustomerID, which specifies how records in both tables relate to one another:
SELECT Customer.CustomerID, Customer.CustomerName, Invoices.Amount FROM Customer, Invoices WHERE Customer.CustomerID = Invoices.CustomerID
Here, Customer.CustomerID, Customer.CustomerName, Invoices.Amount is the list of three fields that should be included in the resulting set of data. The WHERE part of the statement formulates the join between two tables based on the customer ID that can be found in both tables.
Ability to limit the retrieval of the resulting subset of rows from the database tables based on a user-defined criteria. For example, we could rewrite the above select statement as follows:
SELECT Customer.CustomerID, Customer.CustomerName, Invoices.Amount FROM Customer, Invoices WHERE Customer.CustomerID = Invoices.CustomerID AND Customer.State = 'MASSACHUSETTS'
Here, the AND operator specifies an additional criteria, suggesting that we want only the results for customers from a particular state.
Watch this short video tutorial http://www.youtube.com/watch?v=mFTU33sRprQ to learn how to write sql in Microsoft ACCESS 2007.