For an instructor lead, in-depth look at learning SQL click below.
Structured Query Language, best known as SQL, is a standard computer language dedicated to managing, and manipulating relational databases. In this blog post, we will delve into the underlying syntax and structure that make SQL both powerful and flexible.
The Structure of SQL
SQL is made up of commands that are utilized to perform tasks such as retrieving data from a database or creating a new table. These commands can be divided into various categories: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).
Data Definition Language (DDL)
These commands are used to define or modify the structure of the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- To create a new table CREATE TABLE table_name ( column1 datatype, column2 datatype, ... ); -- To remove an existing table DROP TABLE table_name; -- To change the structure of a table ALTER TABLE table_name ADD column_name datatype; |
Data Manipulation Language (DML)
These SQL commands are used to add, retrieve or modify data in a database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- To add data into a table INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); -- To retrieve data from a table SELECT column1, column2, ... FROM table_name; -- To modify data in a table UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; |
Data Control Language (DCL)
p>These commands are used to control the access to data stored in a database.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- To grant privilege GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} -- To take back privileges given to a user. REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name} |
Transaction Control Language (TCL)
These commands are used to manage transactions in a database.
|
1 2 3 4 5 6 7 8 9 10 |
-- To start a transaction START TRANSACTION; -- To save a point in a transaction SAVEPOINT savepoint_name; -- To rollback a transaction to a savepoint ROLLBACK TO savepoint_name; |
Understanding SQL Syntax
The syntax in SQL is governed by a set of rules which defines how commands are structured. One of the primary rules is that SQL is case insensitive. Whether you write your commands in upper or lower case doesn’t affect how they’re processed. However, it’s best practice to write SQL keywords in all caps to make code easier to read and understand.
Another rule is that every SQL statement must end with a semicolon (;). This character signals the end of one statement and the start of next. Although not all SQL databases require this, it’s a good habit to adopt.
Conclusion
SQL is a powerful language for working with databases, and understanding its structure and syntax is vital for anyone looking to extract, add, or manipulate data. This guide is meant to provide a general overview of SQL, but each of these commands has additional options and potential complexities that you can explore. As with any programming, practice is key!