Basic SQL Commands

Structured Query Language (SQL) is a programming language specifically designed for managing and manipulating relational databases. It provides a set of commands and syntax for adding, modifying, and retrieving data from databases. SQL is widely used in various applications, including search engines, video streaming services, and online store catalogs, to effectively search, retrieve, and organize data based on user queries. Therefore, whenever you’ve conducted searches online, it’s highly probable that SQL was employed by the computer system to locate the information you sought.

To ensure the proper functioning of a database, users typically perform four essential tasks: creating new records, retrieving existing information, updating data, and deleting records. These tasks are commonly referred to using the acronym CRUD, which stands for create, read, update, and delete. This acronym serves as a helpful reminder of the fundamental operations users can perform on a database.

The following tables define a few of the most common SQL commands.

Data Definition Language Commands: This first set of commands are part of the data definition language (DDL). These commands change the structure (schema) of the database.

Commands

COMMAND	DESCRIPTION
CREATE TABLE	The CREATE TABLE command can add a new table to an existing database. Example: CREATE TABLE books ( id int, title text ); Creates a new table with two fields—an integer field for the primary key and a text field for the title.
ALTER TABLE	ALTER TABLE is used to add, change, or remove fields on an existing table. It also lets you define primary and foreign keys. Example: ALTER TABLE books ADD author text; Adds a new text field to the books table to store the author of each book.
DROP TABLE	The DROP TABLE command deletes an entire table, including all the data stored in it. Example: DROP TABLE books; Deletes the books table and all its records from the database.
CREATE DATABASE	The CREATE DATABASE command allows you begin a brand-new database. Example: CREATE DATABASE library; Creates a new database using only one parameter.
CREATE VIEW	The CREATE VIEW command creates a virtual table populated from an SQL statement result-set. Example: CREATE VIEW book_authors ASSELECT column1, column2, …FROM booksWHERE junior_fiction; Displays a table with book authors from the junior fiction category.
CREATE INDEX	The CREATE INDEX command creates an index within a table which can be used to accelerate the speed of query retrieval time. Example: CREATE INDEX book_copyrightdateON books (column1, column2, …); Creates an index for copyright dates from the books table.

Data Manipulation Language Commands

The following table shows a few Data Manipulation Language (DML) commands. DML deals with the records within the database and not the schema itself.

Table 2: Data Manipulation Language Commands

COMMAND	DESCRIPTION
INSERT INTO and VALUES	These commands work together to add new records to an existing table. The INSERT INTO command tells the database which table should store the new record. VALUES tells the database what to write in each field. Example: INSERT INTO books (id, title, author) VALUES (12345, “The Cat in the Hat”, “Dr. Seuss”) Adds The Cat in the Hat to the books table.
SELECT	The SELECT command lets you view the specific information you want from the database. It’s commonly used with the asterisk (*) operator, which tells the computer to include everything. Example: SELECT * FROM books Returns all records from the books table
WHERE	The WHERE command lets you narrow your query to only the results you want. Example: SELECT * FROM books WHERE author = “Dr. Seuss” Returns all records from the books table where the value of the author field is Dr. Seuss.
UPDATE and SET	The UPDATE command makes changes to existing records in a table. It’s usually used with the SET command, which tells the database the changes to make. Example: UPDATE books SET title = “Green Eggs and Ham” WHERE id = 2345 Finds all records with an id of 2345 (probably just one book) and changes the value of the title field to Green Eggs and Ham.
DELETE	The DELETE command deletes records from a table. Example: DELETE * FROM books WHERE id = 2468 Deletes all records from the books table where the value of the id field is 2468. Using the primary key field is a good way to make sure you don’t accidentally delete a wrong record.

Data Control Language and Other Common SQL Commands

This following table describes other common SQL commands that deal with backing up and restoring databases, as well as managing permissions. It’s important to note that the SQL permissions are how the objects in the database are kept secure.

When an object is created, the creator of the object becomes the owner of the object. The owner has the ability (through permissions) to grant rights to specific objects, tables, or columns to other users. Even if other users are granted rights, the owner retains complete control of the object.

Table 3: Other Common SQL Commands

COMMAND	DESCRIPTION
BACKUP DATABASE	The BACKUP DATABASE command creates a backup copy of the database. Example: BACKUP DATABASE library TO DISK = “C:\Users\student\Desktop” Backs up the entire database to the desktop. (Many relational database management systems also provide stored procedures to backup individual tables.)
GRANT	The GRANT command gives a user specified permissions to a specific database object and is part of the data control language within SQL. Example: GRANT SELECT ON books TO marian; Gives the user marian permission to run select queries on the books table.
REVOKE	The REVOKE command removes the specified permissions to specific database objects and is also part of the data control language within SQL. Example: REVOKE DROP ON books FROM marian; Makes it so the user marian can’t drop the books table.
Database Dump	Another way to back up a database is to export the data into a format that can be read by another program like a spreadsheet. This is called a database dump. It typically stores the database schema and data in a CSV file. The command syntax for exporting and importing a database dump depends on the database platform you are using.