Job Ready SQL

Appendix BSQL Quick Reference

This appendix is a quick reference containing much of the basic syntax for accomplishing standard tasks with SQL. The following areas are covered:

• Working with databases
• Defining tables, columns, and rows
• Performing table queries
  • Filtering and grouping selection results
  • Queries using multiple tables
• Basic SQL data types

WORKING WITH DATABASES

Creating a new database:

 CREATE DATABASE databaseName; 
 

Using an existing database:

 USE databaseName; 
  

Deleting a database:

 DROP DATABASE databaseName; 
 

DEFINING TABLES, COLUMNS, AND ROWS

Creating a new table:

 CREATE TABLE tableName 
 (
    columnName1 dataType, 
    columnName2 dataType, 
 …
 );
 

Removing a table:

Option 1:

DROP TABLE tableName; 
 

Option 2:

DELETE FROM tableName 
WHERE columnName = value; 
 

Option 3:

DELETE * FROM tableName; 
 

Option 4:

DELETE FROM tableName; 
 

Renaming a table:

Option 1:

ALTER TABLE originalTableName RENAME TO newTableName; 
 

Option 2:

RENAME TABLE originalTableName TO newTableName; 
 

Adding a new column to a table:

 ALTER TABLE tableName 
 ADD [COLUMN] columnName datatype; 
 

Removing a column from a table:

 ALTER TABLE tableName 
 DROP [COLUMN] columnName; 
 

Adding a column to a table:

 ALTER TABLE tableName 
 ADD newColumnName datatype [columnConstraint(s)] [AFTER existingColumn]; 
 

Altering a column in a table:

 ALTER TABLE tableName 
 MODIFY columnName [columnDefinition] [columnConstraint(s)]; 
 

Adding a row of data into a table:

 INSERT INTO tableName [(columnName1 ...