A web coding bootcamp will likely cover SQL queries and commands. Depending on your skill level and desired use, they can be relatively straightforward or complex. However, once you get a firm grip on the basics, you can start exploring more advanced ones.
To make SQL queries and commands easier to understand, most coders divide them into several categories.
Data Definition Language Commands
Data Definition Language (DDL) SQL queries and commands help define the database.
Creates a database or table. For example: CREATE DATABASE and CREATE TABLE create a database and a table, respectively; while CREATE TABLE AS creates a table using an existing one.
Drops a database or table that currently exists.
As with CREATE, examples include DROP DATABASE and DROP TABLE, which drop the existing database and table, respectively. Use these SQL commands with caution, as they result in the complete loss of the information within the database or table.
Deletes information within a table without deleting the table itself.
The relevant command is TRUNCATE TABLE. These types of SQL queries should also be performed with caution as information will be lost, leaving an empty table.
Modifies, deletes or adds columns within your existing table.
You can combine ALTER TABLE commands with DROP or ADD COLUMN commands, which will delete and add columns, respectively. You can also use ALTER TABLE statements combined with ALTER/MODIFY COLUMN to change the column’s datatype.
Makes a full backup of your specified database.
Adding WITH DIFFERENTIAL to the end of the code will back up only the elements that have changed since your last full backup.
Data Manipulation Language Commands
Data Manipulation Language (DML) SQL queries and commands manipulate data within the database.
Lets you choose which database you will be performing operations on using your SQL queries.
Inserts additional records in the table.
Modifies records already in the table.
Deletes the records currently existing in a table.
Selects the relevant data from your database and returns it in a result-set, which is the result table.
There is also a SELECT DISTINCT statement, which will only return values that are different.
Sorts your results in descending or ascending order. The default is ascending while the DESC keyword changes it to descending.
Works with aggregate functions, grouping the result-set in at least one column.
Used in situations with SQL queries that do not allow the use of the WHERE keyword.
Copies data originating in a table into another table.
Data Control Language Commands
Data Control Language Commands (DCL) are related to a database system’s user controls and permissions.
Provides privileges or access to the database as well as its objects.
Withdraws access privileges previously given with GRANT commands.
Transaction Control Language Commands
Transaction Control Language (TCL) SQL commands relate to database transactions.
Saves transactions in the database.
Restores your database to its last committed state.
Temporarily saves a transaction.
Operators with SQL Queries and Commands
As you learn SQL queries, make sure to pay attention to the arithmetic, bitwise, comparison, compound and logical operators.
The logical operators include AND, OR, NOT, BETWEEN, IN, LIKE, EXISTS, ANY and ALL.
Each of the aggregate functions is self-explanatory and include MIN(), MAX(), COUNT(), SUM() and AVG().
Comments in SQL
In addition to learning various SQL queries and commands, you should also understand the two methods used to comment.
To use a single-line comment, begin the comment with two hyphens. Keep in mind that any text that appears on the same line after “–” will be ignored by the compiler.
To make a multi-line comment to supplement your SQL queries, begin with “/*” and end with “*/”; the compiler will ignore the text between the symbol pairs.
Constraints in SQL Queries and Commands
Constraints indicate the rules that the data in a table must follow.
Indicates that no column can contain a NULL value.
Indicates that each value in a column must be unique.
Requires all values within a column to meet a specified condition.
Provides a default value or set of values to appear in columns if values are not specified.
Creates indexes within the table that can be used to very quickly create and retrieve data from your database.
Types of Keys in Databases
When using the basic SQL queries and commands, keep the seven types of database keys found in mind.
Uniquely identifies a table. For multiple candidate keys, there will likely be a Primary Key.
Uniquely identifies your tuple.
Includes all sets of attributes used to identify unique tuples.
This is a candidate key that is not used as a primary key.
Nearly identical to a primary key, with the exception that it allows for the inclusion of one NULL value within the column.
Takes values of another attribute.
Combines at least two columns to uniquely identify a tuple.
Other SQL Queries to Know
You will also likely use other elements in your SQL commands at some point. Here are a few that you should be familiar with:
Provides your table or column with a temporary name. When using them in SQL queries, they will only exist for part of the query.
Passes through various conditions and returns a value only when the data meets the first condition. If no conditions meet the criteria, it will return the ELSE clause’s value, or NULL if the code does not have an ELSE part.
Combines rows from multiple tables, on the basis of a related common in the tables. There can be INNER, FULL, LEFT or RIGHT JOINs.
Your web coding bootcamp may also cover nested queries, which have outer queries supplemented by inner subqueries. Common nested queries include DELETE, UPDATE, INSERT and SELECT.