KEYWORD - California State University, Sacramento



INTRODUCTORY SQL

ESSENTIAL KEYWORDS

The following Dozen keywords will allow you to SELECT, UPDATE, INSERT, and DELETE Records.

|KEYWORD |DESCRIPTION |EXAMPLE |

|SELECT |Creates a recordset |SELECT * FROM Customers; |

| | | |

|* |All, Everything | |

|FROM |Chooses the table | |

|WHERE |Establishes Conditions |SELECT * FROM Customers |

| | |WHERE Cust_ID 75 |

| | |AND OrderDate = '11/5/00/' |

|OR |Either Condition may be met to return results |SELECT * FROM Orders WHERE CustID >75 |

| | |OR OrderDate = '11/5/00'; |

SOME MORE USEFUL KEYWORDS

|KEYWORD |DESCRIPTION |EXAMPLE |

|ORDER BY |Indicates column by which to arrange recordset |SELECT LastName, FirstName FROM Customers |

| |alphabetically or numerically. |ORDER BY LastName DESC; |

|DESC |Optional to list by highest value first | |

|NULL |Indicates absence of data. |SELECT * FROM Customers WHERE ZipCode = NULL; |

|IN |Offers a list of specific values as criteria |SELECT * FROM Products WHERE ZipCode IN ('95816',|

| | |'95817', '95818'); |

|LIKE |% sign indicates a potentially longer section of |SELECT * From Customers WHERE (LastName Like |

| |characters. The underscore indicates any |'Jo%' |

| |character. |OR LastName LIKE 'GR_Y'); |

|DISTINCT |Selects only one of a duplicate field. It would |SELECT DISTINCT LastName FROM Customers; |

| |only select the last name “Smith” once from the | |

| |phone listings. | |

OPERATORS

|BETWEEN / AND |= Equal | Not Equal |

| | |!= Not Equal |

|< Is Less Than | Is Greater Than |>= Greater Than or Equal |NOT Reverses a condition |

You may need to specify which table you are referring to:

SELECT * FROM Customers.ID;

You may use query results as your conditions:

SELECT * FROM Customers:

WHERE LastName IN (Select * From Employees;)

You can perform an INNER JOIN with multiple tables:

SELECT * FROM Customers, Orders

WHERE Customers.Cust_ID = Orders.Buyer_ID;

There is much more to know about SQL. In fact SQL has around 300 Keywords. Different vendors have different “dialects” of SQL.

It is entirely possible that with a well-designed database you may need only the above Keywords.

Rev. 2.5 Kevin Harville harville@ecs.csus.edu

Database Introduction

Database: A collection of data arranged for ease and speed of search and retrieval.*

Record or Row: Information or data on a particular subject.*

Column or Field: A particular type of data.

Table: A collection of related rows and columns.

Databases typically consist of several tables. The tables may be indirectly related. For instance customer 700 may be linked to orders 1012, 1853, and 2003. The orders are indirectly linked to numerous products. In this case tables regarding customers, orders, and products are all utilized.

Microsoft Access may be used on Web sites with small traffic. Other common databases include Microsoft SQL Server and numerous Oracle databases. Microsoft SQL Server and numerous other databases are specifically client-server databases built to handle heavy traffic. MySQL is a popular open-source database.

Table rows typically have a primary key. That is a unique identifier that could never possibly repeat itself in the database. Examples include unique customer ID’s and unique product ID’s.

CREATING A DATABASE

Open MS Access. On many systems with Access installed you may access it via “START”, ”New Office Document”. It will prompt you to name and save your database.

Click on “Create Table in Design View”. You will have a grid appear. In this grid you enter the column name and the data type. Autonumber should be the data type for the first field, which should indicate something unique like the customer’s unique ID. The computer will automatically increment the Autonumber field with each new addition. Let’s call this field CustID. Right click on the column in front of that listing and assign it as Primary Key. Add a few other characteristics, such as LastName and FirstName.

Look down below in the field properties section. You will find such entries as “Field Size”, “Required”, and “Allow Zero Length”. Field Size specifies the maximum characters in a field, Required specifies if entries can be made without that entry being made or previously existing, and Allow Zero Length indicates whether or not an entry of no characters can be made.

Close out the table definition window you have been working in. It will prompt you for a name. Use “Customers” for this first table. Now you will see a table called customers in your database window. Double-click on the table icon. Fill it in with several customers. Use some common and some unique first and last names.

DIRECT SQL QUERIES IN ACCESS

Now we are going do do a database query. Double click on “Queries” and then on “Create query in Design View.” There are several ways to generate queries. We will use SQL directly.

A “Show Table” window pops up. Close it. Right click on the large gray area. Choose “SQL View”. It is here you can enter your SQL Queries. The basics of SQL syntax are included on an associated sheet. A resulting recordset will appear with the results of your query.

USES

Most e-Commerce and interactive Web sites make extensive use of databases using SQL as the query language. MS Access is used as an in-office database, an intranet database, a test database, and a database for small Web sites.

*Definitions derived from

Rev. 1.5 Kevin Harville harville@ecs.csus.edu

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download