SQL Basics
What is SQL?
SQL stands for Structured Query Language and lets you access and manipulate databases. It is an ANSI (American National Standards Institute) standard.
SQL lets you
create new databases
delete databases
insert records in a database
update records in a database
delete records from a database
execute queries against a database
retrieve data from a database
create new tables in a database
create stored procedures in a database
create views in a database
insert records in a table
update records in a table
delete records from a table
delete tables
set permissions on tables, procedures, and views
SQL Extensions
Several DBMS vendors have expanded the capabilities of SQL by introducing new statements or instructions to the language. These extensions serve the purpose of enhancing functionality or simplifying specific operations. While these extensions can be highly beneficial, they are often specific to a particular DBMS and are typically not supported by multiple vendors. On the other hand, the standard SQL governed by ANSI committee, is commonly referred to as ANSI SQL. It is noteworthy that all major DBMSs, including those with their own extensions, support ANSI SQL as a common standard.
SQL Statements
An SQL statement is a command or instruction written in the Structured Query Language (SQL) to perform a specific action on a database. SQL statements are used to interact with databases by retrieving, manipulating, or managing data stored in them.
There are several types of SQL statements, including:
Data Manipulation Language (DML) statements: These statements are used to retrieve, insert, update, and delete data from a database. Examples include SELECT, INSERT, UPDATE, and DELETE.
Data Definition Language (DDL) statements: DDL statements are used to define or modify the structure and schema of database objects such as tables, indexes, and constraints. Examples include CREATE, ALTER, and DROP.
Data Control Language (DCL) statements: DCL statements are used to control access permissions and security settings in a database. Examples include GRANT and REVOKE.
Transaction Control Language (TCL) statements: TCL statements are used to manage transactions within a database. Examples include COMMIT, ROLLBACK, and SAVEPOINT.
Keywords
SQL statements are made up of one or more English terms, called keywords. For example CREATE, SELECT, INSERT INTO, GRANT are only a few of the statements. Columns or tables should never be named using a keyword.
Clauses
Clauses are keywords and/or built-in functions that help fetch the records from a table. A clause is used with a conditional expression, i.e. a threshold number to limit the number of rows being displayed (LIMIT 20) or column(s) to perfom aggragration (GROUP BY year). Some of the widely used clauses including.
CONSTRAINT clause
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
ORDER BY clause
WITH clause
USING clause
LIMIT clause
OFFSET clause
AND / OR clause
Do not use clauses to name a column or field!
Last updated