CS 4440 Wiki:
SQL Cheat Sheet

Below is an abridged cheat sheet of SQL (Structured Query Language) fundamentals that you'll use in this course.

This page is by no means comprehensive—we encourage you to bookmark and familiarize yourself with one of the many in-depth SQL tutorials on the web. Some great examples are:

• W3 Schools' SQL Introduction
• Online SQL Complier

Basic SQL Commands

Consider an SQL table named CourseStaff comprised of the following data records shown below:

Below are examples of common SQL commands interacting with the data contained in the above table.

Selecting Records:

SELECT: return a result set of rows, from one or more tables.

SELECT ID, Name from CourseStaff;
 Number of records: 4
 {ID, Name}
 {0, Stefan Nagy}
 {1, Bella Miller}
 {2, Alishia Seo}
 {3, Ethan Quinlan}

Inserting Records:

INSERT: insert data records into the table.

INSERT INTO CourseStaff (Name, Role, City) VALUES (“Hokie Bird”, “TA”, “Blacksburg, VA”);
 Inserted row: {4, Hokie Bird, TA, Blacksburg VA}

Updating Records:

UPDATE: update data records within table.

UPDATE CourseStaff SET Name = “C​ount Chocula”, City = “Hershey, PA” WHERE id = 0; 
 Updated row: {0, Count Chocula, Professor, Hershey PA}

Deleting Records:

DELETE: delete existing records.

DELETE FROM CourseStaff WHERE City="Salt Lake City, UT"
 Deleted 4 rows.

Union of Multiple Selections:

UNION: combine results of multiple queries.

SELECT Name FROM CourseStaff UNION SELECT Name FROM SomeOtherTable;
 Results in a list of all unique names in Name column from CourseStaff and all names from SomeOtherTable.

Conditional Operators:

WHERE/AND/OR: conditional operations to find data in table.

SELECT * FROM CourseStaff WHERE Name = “Ethan Quinlan”
 Number of records: 1
 {3, Ethan Quinlan, TA, Salt Lake City UT}
SELECT * FROM CourseStaff WHERE City = “Salt Lake City, UT” AND Role = “TA”
 Number of records: 4
 {1, Bella Miller, TA, Salt Lake City UT}
 {2, Alishia Seo, TA, Salt Lake City UT}
 {3, Ethan Quinlan, TA, Salt Lake City UT}

Syntactical Tips

Here are some SQL syntax tips to keep in mind.

SELECT * from CourseStaff Returns every column for every row in the table CourseStaff.

NULL = NULL Returns false because NULL is considered unknown. SELECT * FROM CourseStaff WHERE Role IS NULL; To check for NULL you must use IS NULL or IS NOT NULL operators.
 This query will return 0 rows as each row in CourseStaff has a value for role.

SELECT * from CourseStaff where id = 0; Number of records: 1
 {0, Stefan Nagy, Professor, Salt Lake City UT}

SELECT 1 = 1; Returns TRUE.

SELECT 1 = 4; Returns FALSE.

SELECT '1' = 1; Returns FALSE.
 A string is not equal to an integer, type mismatch results in FALSE.

 Comparison of two equal integer values results in TRUE. 

 "1" = 1 returns FALSE but the second part is OR 1. 
 In SQL, any non-zero number is evaluated as true. So 1 is considered TRUE.

SELECT '1' = 1 -- OR 1; Returns FALSE. 
 Everything after the comment is ignored by the SQL interpreter, so it is the comparison of a string to an integer.