SQL: Structured Query Language

• Structured Query Language (SQL) is a universal language used to make relational database queries.
• A row of the table is called a record or a row .
• Each column of the table represents a field .
• Some users want only certain subsets of the table columns (called projections). Other users wish to combine smaller tables into larger ones to produce more complex tables (called joins).
• A table's primary key uniquely identifies each record in the table. Every record must have a value in the primary key field--Rule of Entity Integrity --and the value must be unique.
• A foreign key is a field in a table for which every entry has a unique value in another table and where the field in the other table is the primary key for that table. The foreign key concept helps maintain the Rule of Referential Integrity--every value in a foreign key field must appear in another table's primary key field. Foreign keys enable information from multiple tables to be joined together and presented to the user.
• A typical SQL query "selects" information from one or more tables in a database. Such selections are performed by SELECT queries . The simplest format of a SELECT query is

SELECT * FROM TableName;

• where the asterisk ( * ) indicates that all fields from TableName should be selected and TableName specifies the table in the database from which the fields will be selected. To select specific fields from a table, replace the asterisk ( * ) with a comma-separated list of the field names to select.
• SQL uses the optional WHERE clause to specify the selection criteria for the query. The simplest format of a SELECT query with selection criteria is

SELECT * FROM TableName WHERE criteria;

• The condition in the WHERE clause can contain operators < , , <= , = , = , < and Like . Operator Like is used for pattern matching with the wildcard characters asterisk ( * ) and question mark ( ? ) .
• The results of a query can be arranged in ascending or descending order using the optional ORDER BY clause . The simplest format of an ORDER BY clause is

SELECT * FROM TableName ORDER BY field ASC;
SELECT * FROM TableName ORDER BY field DESC;

• where ASC specifies ascending (lowest to highest) order, DESC specifies descending (highest to lowest) order and field represents the field that is used for sorting purposes.
• Multiple fields can be used for ordering purposes with an ORDER BY clause of the form

ORDER BY field1 SortingOrder,field2, SortingOrder , ...;

• where SortingOrder is either ASC or DESC .
• The WHERE and ORDER BY clauses can be combined in one query.
• An INNER JOIN (from Microsoft) merges records from two tables by testing for matching values in a field that is common to both tables. The simplest format of an INNER JOIN clause is

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.field = Table2.field;

• The ON part of the INNER JOIN clause specifies the fields from each table that should be compared to determine which records will be selected.
• The syntax TableName.FieldName is used in a query to distinguish between fields in different tables that have the same name.

Using the Biblio Database

SELECT Title,[Year Published], ISBN FROM Titles;

SELECT * FROM Authors WHERE Author like 'd*';

SELECT * FROM Authors WHERE Author like 'd?i*';

SELECT * FROM Authors WHERE Author like 'd[a-e]*';

SELECT * FROM Authors ORDER BY Author ASC;

SELECT * FROM Authors ORDER BY Author DESC;

SELECT * FROM Authors WHERE Author Like 'd*' ORDER BY Author DESC;

Joining Tables the Microsoft way

SELECT Titles.Title, Titles.[Year Published], Titles.ISBN, Publishers.Name FROM Titles INNER JOIN Publishers on Titles.PubID = Publishers.PubID;

SELECT Author, ISBN
FROM Authors INNER JOIN [Title Author] ON Authors.Au_ID = [Title Author].Au_ID
ORDER BY Author ASC;

SELECT Titles.Title, Titles.ISBN, Authors.Author,
                Titles.[Year Published], Publishers.[Company Name]
FROM Publishers INNER JOIN (Authors INNER JOIN
            ([Title Author] INNER JOIN Titles
            ON [Title Author].ISBN = Titles.ISBN)
            ON Authors.Au_ID = [Title Author].Au_ID)
            ON Publishers.PubID = Titles.PubID
ORDER BY Titles.Title;

Or Standard SQL
SELECT Titles.Title, Titles.[Year Published], Titles.ISBN, Publishers.Name
FROM Titles,Publishers
WHERE Titles.PubID = Publishers.PubID;

SELECT Author, ISBN
FROM Authors, [Title Author]
WHERE Authors.Au_ID = [Title Author].Au_ID
ORDER BY Author ASC;

SELECT Titles.Title, Titles.ISBN, Authors.Author,
Titles.[Year Published], Publishers.[Company Name]
FROM Publishers, Authors, Titles, [Title Author]
WHERE [Title Author].ISBN = Titles.ISBN AND
Authors.Au_ID = [Title Author].Au_ID AND
Publishers.PubID = Titles.PubID
ORDER BY Titles.Title;

OR

Click on the BACK button of  your browser to return to the previous page.