This tutorial will be covering four SQL statements: SELECT, for queries, INSERT INTO, UPDATE and DELETE, for data manipulation.
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)
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'
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')
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.