Chapter 1: Introduction to SQL

What is SQL?

SQL is a domain-specific language: it is designed for tasks related to databases. It is also declarative. You describe the result you want, and SQL works out the steps to get there.

SQL manages relational databases, which store data in tables made of columns and rows. Each table holds one kind of data, and each row is one instance of it. A customers table, for example, might have columns for name, address, and phone number, with one row per customer.

The language has several parts:

• Data Definition Language (DDL) creates and changes database objects such as tables, indexes, and constraints.
• Data Manipulation Language (DML) inserts, updates, and deletes the data in tables.
• Data Query Language (DQL) retrieves data from tables.
• Data Control Language (DCL) grants and revokes privileges to access database objects.

SQL is standardized, so the basic syntax is the same whichever database management system (DBMS) you use, whether that’s MySQL, Oracle, or Microsoft SQL Server. Each DBMS implements SQL a little differently, though, and some add features that aren’t part of the standard.

SQL is worth learning if you work with data, as a software developer, a data analyst, or a data scientist. Online courses, books, and tutorials on it are easy to find.

Key concepts of SQL

1. Relational databases: as described above, data lives in tables of columns and rows. Tables relate to each other through keys, which are columns that link one table to another.
2. SQL statements: you work with the data by running statements such as CREATE TABLE, INSERT, SELECT, UPDATE, and DELETE. They fall into categories like DDL, DML, and DQL.
3. Data types: every column in a table has a data type that sets what kind of data it can hold. SQL supports types such as integer, decimal, varchar, date, and Boolean.
4. SQL operators: these include arithmetic operators (+, -, *, /), comparison operators (>, <, =), and logical operators (AND, OR, NOT).
5. Joins: a join combines two or more tables based on a common key. Joins come in several types, including inner join, left join, right join, and full outer join.
6. Indexes: an index speeds up reading data from a table. It’s a separate data structure that lets the database quickly find the rows that match a condition.
7. Transactions: a transaction groups related operations into a single unit of work. If any part fails, the transaction can be rolled back, which keeps the database in a consistent state.
8. Views: a view is a virtual table built from the result of a SELECT statement. Views simplify complex queries and give you a convenient way to get at data you use often.
9. Stored procedures: a stored procedure is precompiled code stored in the database that you can run on demand. Stored procedures are often used for complex calculations or data manipulation.
10. Security: SQL provides several ways to secure data in a database, including user authentication and authorization, data encryption, and auditing of database activity.