Learn Basic SQL Queries and Commands

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. 

CREATE

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. 

DROP

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. 

TRUNCATE

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. 

ALTER

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. 

BACKUP DATABASE

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. 

USE

Lets you choose which database you will be performing operations on using your SQL queries. 

INSERT INTO

Inserts additional records in the table. 

UPDATE

Modifies records already in the table. 

DELETE

Deletes the records currently existing in a table. 

SELECT

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. 

ORDER BY

Sorts your results in descending or ascending order. The default is ascending while the DESC keyword changes it to descending. 

GROUP BY

Works with aggregate functions, grouping the result-set in at least one column. 

HAVING

Used in situations with SQL queries that do not allow the use of the WHERE keyword. 

SELECT INTO

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. 

GRANT

Provides privileges or access to the database as well as its objects. 

REVOKE

Withdraws access privileges previously given with GRANT commands. 

Transaction Control Language Commands

Transaction Control Language (TCL) SQL commands relate to database transactions. 

COMMIT

Saves transactions in the database. 

ROLLBACK

Restores your database to its last committed state. 

SAVEPOINT

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. 

Aggregate Functions

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.

Single-Line Comments

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. 

Multi-Line Comments

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. 

NOT NULL

Indicates that no column can contain a NULL value. 

UNIQUE

Indicates that each value in a column must be unique. 

CHECK

Requires all values within a column to meet a specified condition. 

DEFAULT

Provides a default value or set of values to appear in columns if values are not specified. 

INDEX

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. 

Candidate Key

Uniquely identifies a table. For multiple candidate keys, there will likely be a Primary Key. 

Super Key

Uniquely identifies your tuple. 

Primary Key

Includes all sets of attributes used to identify unique tuples. 

Alternate Key

This is a candidate key that is not used as a primary key. 

Unique Key

Nearly identical to a primary key, with the exception that it allows for the inclusion of one NULL value within the column. 

Foreign Key

Takes values of another attribute. 

Composite Key

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:

Aliases

Provides your table or column with a temporary name. When using them in SQL queries, they will only exist for part of the query. 

Case Statements

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. 

Joins

Combines rows from multiple tables, on the basis of a related common in the tables. There can be INNER, FULL, LEFT or RIGHT JOINs. 

Nested Queries

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.

*Please note, these articles are for educational purposes and the topics covered may not be representative of the curriculum covered in our boot camp. Explore our curriculum to see what you’ll learn in our program.