SQL Study Sheet

SQL (Structured Query Language) is the basic command language for database management systems. A full discussion of this command language is beyond the scope of this course. However, this guide describes several of the basic SQL commands and terms that allow you to perform simple database manipulations.

In the statement basic syntax descriptions:

  • Boldface words are typed exactly as shown.
  • Italicized words are place holders, to be replaced by the specific field names, table names, or values from your specific database
  • Wildcard characters may be used as follows:
    • In a field name list, * may be used to mean all fields of the record.
    • In a criterion (WHERE clause), % may be used as part of a string value to mean any value, including an empty value:
      • '%' means any value
      • '%word%' means any phrase containing the word shown, including just the word itself
      • '%word' means any phrase ending with the word shown, or just the word itself.
      • 'word%' means any phrase beginning with the word shown, or just the word itself.
  • Any value representing a text value, including numbers that are being used as string values, MUST be enclosed in quotation marks when used as criterion values, replacement values, or new record values.

SELECT command

Purpose:

Choose and display a specific group of data fields from one or more tables. The data fields may or may not be further filtered based on matching a specific data value or containing a specific data value.

Basic syntax:

SELECT field-name-list FROM table-name
Displays the value of the specified fields for all records in the table

SELECT field-name-list FROM table-name WHERE field-name = specific-value
Displays the value of the specified fields for all records in the table that match the criterion field's chosen value.

SELECT field-name-list FROM table-name WHERE field-name LIKE specific-value
Displays the value of the specified fields for all records in the table where the criterion field contains the indicated value.

INSERT command

Purpose:

Add a new record to the specified database table. Values for all fields in the record MUST be supplied in the statement. Do not confuse this with the UPDATE command, which changes an already existing record.

Basic syntax:

INSERT INTO table-name VALUES(field-value:key, field-value, field-value…)
Adds a new record to the table with the listed values assigned to the fields in the order given by the database schema for the table. The key field's value should be listed first. The number of values given MUST match the number of fields in the record.

UPDATE command

Purpose:

Change one or more field values in one or more records of the specified table. The record you are changing must already exist; do not confuse this command with the INSERT command, which adds a completely new record.

Basic syntax:

UPDATE table-name SET field-name = field-value
Change the specified field's value in all records in the table to the new value given.

UPDATE table-name SET field-name = field-value WHERE field-name = specific-value
Change the specified field's value to the new value given for all records in the table matching the criterion.

UPDATE table-name SET field-name = field-value WHERE field-name LIKE specific-value
Change the specified field's value to the new value given for all records in the table containing the criterion value.

DELETE command

Purpose:

Completely remove one or more entire records from the specified table, based on one or more field values matching a specific value or containing a specific value. 

Basic syntax:

DELETE FROM table-name

Delete ALL RECORDS and their data from the indicated table.

DELETE FROM table-name WHERE field-name = specific-value
Delete all records and their data where the indicated field of the record matches the value given.

DELETE FROM table-name WHERE field-name LIKE specific-value
Delete all records and their data where the indicated field of the record contains the value given.

Print Friendly, PDF & Email

Comments are closed