SQL Tutorial

This tutorial will be covering four SQL statements: SELECT, for queries, INSERT INTO, UPDATE and DELETE, for data manipulation.

SELECT Statement

The SELECT statement is used to return records from a table in a relational database. The first example is just a simple SELECT statement without any sorting or filters. The second has a WHERE clause to filter by last name returning only records with 'Benson' as the last name. The third adds sorting by last name, returning them in an ascending order. The fourth counts how many duplicate last names are entered into the table and sorts them in descending order. The fifth is using a subquery and will return all automobiles with their price greater than the average selling price. The SELECT statement is very powerful and does much more than this but its total syntax and use is beyond the scope of this tutorial.

SELECT	LASTNAME, FIRSTNAME, SSN, ADDRESS, CITY, STATE, ZIPCODE
FROM		ACCOUNTS

SELECT	LASTNAME, FIRSTNAME, SSN, ADDRESS, CITY, STATE, ZIPCODE
FROM		ACCOUNTS
WHERE	 LASTNAME='Benson'

SELECT	LASTNAME, FIRSTNAME, SSN, ADDRESS, CITY, STATE, ZIPCODE
FROM		ACCOUNTS
WHERE	 FIRSTNAME='John'
ORDER BY	LASTNAME

SELECT	COUNT(*) AS TheCount, LASTNAME
FROM		ACCOUNTS
GROUP BY	LASTNAME
HAVING	(COUNT(*)>1)
ORDER BY	LASTNAME DESC

SELECT	MODEL, MAKE, COLOR, PRICE
FROM		AUTOMOBILES
WHERE	 PRICE > (SELECT AVG(PRICE) FROM AUTOMOBILES)
ORDER BY	PRICE DESC

SELECT	LASTNAME, FIRSTNAME, SSN, ADDRESS, CITY, STATE, ZIPCODE, INVOICENUMBER
FROM		ACCOUNTS INNER JOIN INVOICES ON INVOICES.ACCOUNTID=ACCOUNTS.ID
WHERE	 ZIPCODE='20002'

In the last example, two tables are joined together related by their record identification number.

INSERT INTO Statement

This statement is used to add records to a table in the database. Notice that the last data item, 1970, does not have quotes around it. This is because it is a number rather than a string data type.

INSERT	INTO ACCOUNTS
		(LASTNAME, FIRSTNAME, SSN, ADDRESS, CITY, STATE, ZIPCODE, YEARBORN)
VALUES	('Benson', 'John', '655-43-4556', '1012 Long St', 'Spring', 'MA', '02045', 1970)

UPDATE Statement

This statement is used to modify records in a table in the database. It is important to specify a WHERE clause otherwise all the records in the table will be updated. Notice that O'Riley has two single quotes. The database will interpret this as one single quote.

UPDATE	ACCOUNTS
SET	 LASTNAME='O''Riley', FIRSTNAME='David', SSN='434-55-6758'
WHERE	 SSN='655-43-4556'

UPDATE	ACCOUNTS
SET	 LASTNAME='O''Riley', FIRSTNAME='David', SSN='434-55-6758', INVOICEDATE='1/1/2000'
FROM	ACCOUNTS INNER JOIN INVOICES ON INVOICES.ACCOUNTID=ACCOUNTS.ID
WHERE	 SSN='655-43-4556'

DELETE Statement

This statement is used to delete records from a table in the database. It is important to specify a WHERE clause otherwise all the records in the table will be deleted. Generally, it is not necessary to delete data from a database when it can just be filtered out. The exception is when very large amounts of data are being deleted.

DELETE	FROM ACCOUNTS
WHERE	 (SSN='434-55-6758')

About Indexes

Indexes are used to speed up a table's search and sort; however, lots of indexes slow adding and editing of records because the indexes must be updated, too. The clustered index is the default sort order of a table. Other indexes are non-clustered. Lack of an index requires a table scan when doing a search or sort.