What version of MySQL are you using?

The version on my Site5 server is 5.1.76

What SQL command is used to make a new database? What command is used to make a new table in a database?

CREATE DATABASE databasename

CREATE TABLE tablename(column1name description, column2name description ...)

What SQL command is used to select the database with which you want to work?

USE databasename

What SQL commands are used for adding records to a table? Hint: There are multiple options.

Naming columns - INSERT INTO tablename (column1, column2,...) VALUES (value1, value2, ...)

Including values for every column - INSERT INTO tablename VALUES (value1, NULL, value2, value3, ...)

Inserting multiple records at once - INSERT INTO tablename (column1, column4) VALUES (valueA, valueB),(valueC, valueD), (valueE, valueF)

What types of values must be quoted in queries? What types of values shouldn’t be quoted?

String values - CHAR, VARCHAR and TEXT and Date and time values must always be quoted

Numeric values, functions and the word NULL must never be quoted

What does the asterisk in SELECT * FROM the tablename mean? How do you restrict which columns are returned by a query?

* means to select every column.

Specify exact column names to restrict which columns are returned. SELECT column1, column3 FROM tablename

What does the NOW() function do?

NOW() returns the current date and time according to MySQL. SELECT NOW()

How do you restrict which rows are returned by a query?

Add conditionals to a query to restrict which rows are returned. SELECT column1, column4 from tablename WHERE condition(s)

How do LIKE and NOT LIKE differ from simple equality comparison? Which type of comparison will be be faster? What are the two LIKE and NOT LIKE wildcard characters?

LIKE and NOT LIKE use wildcards in string searches. SELECT * FROM users WHERE last_name LIKE 'Smith%'

Wildcards underscore _ matches a single character and % matches 0 or more characters.

Queries with LIKE condition are slower since they cannot take advantage of indexes

How do you affect the sorting of the returned records? What is the default sorting method? How do you inverse the sort? What is the syntax for sorting by multiple columns?

Use ORDER BY to affect the sorting of the returned records. SELECT * FROM tablename ORDER BY column DESC

The default sorting method is to return records in a meaningless order

SELECT * from tablename ORDER BY column1, column2

What does the LIMIT clause do? How does LIMIT x differ from LIMIT x, y?

The LIMIT clause dictates the number of records that will be returned. SELECT * FROM tablename ORDER BY column LIMIT x will return x records.

LIMIT x returns the x records, LIMIT x,y returns y records, starting at record x SELECT * FROM tablename ORDER BY column LIMIT 10,20 will return records 11-30 that would be returned based on the ORDER BY. Result sets begin at 0 index. ALways use ORDER BY since default sort order is meaningless

What SQL command is used to change the values already stored in a table?

UPDATE is used to change values already stored in a table. UPDATE tablename SET column=value

How do you change multiple columns at once? How do you restrict to which rows the changes are applied?

Update multiple columns at once by separating the columns with a comma. UPDATE tablename SET column1=valueA, column2=valueB

Use WHERE to specify which records should be updated. UPDATE tablename set column1=valueA WHERE column5=value

What SQL command is used to delete rows stored in a table? How do you restrict to which rows the deletions are applied?

DELETE is used to delete rows stored in a table. DELETE FROM tablename

A WHERE clause is used to determine which records to delete. DELETE FROM tablename WHERE condition

What is a SQL alias? How do you create one? Why is an alias useful?

An alias is a symbolic renaming of an item used in a query.

Create an alias using the term AS. SELECT registration_date AS reg FROM users

Once an alias is defined it can be used throughout the SQL statement, even in WHERE conditions SELECT first_name AS name FROM users WHERE name='Sam'