PROWAREtech
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.