SQL Development: The Basics

Introduction to SQL

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It allows you to retrieve, insert, update, and delete data, as well as manage database structure.

Whether you're working with relational databases like SQL Server, MySQL, PostgreSQL, or Oracle, understanding SQL is fundamental for any developer or data professional.

Databases

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. It is designed to store, manage, and retrieve data efficiently.

In SQL, you can create, drop, and select databases to work with:


CREATE DATABASE MyNewDatabase;
DROP DATABASE OldDatabase;
USE MyNewDatabase; -- Select database to use

Tables

Tables are the fundamental structures within a database that hold your data. They are organized into rows and columns.

Creating a Table:


CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

Dropping a Table:


DROP TABLE Customers;

Columns

Columns define the attributes of the data stored in a table. Each column has a specific name and data type.

Adding a Column:


ALTER TABLE Customers
ADD PhoneNumber VARCHAR(20);

Modifying a Column:


ALTER TABLE Customers
ALTER COLUMN Email VARCHAR(150);

Dropping a Column:


ALTER TABLE Customers
DROP COLUMN PhoneNumber;

Common Data Types

Choosing the correct data type is crucial for efficient storage and data integrity. Some common SQL data types include:

  • INT: Integer numbers.
  • VARCHAR(n): Variable-length string of characters, up to n characters.
  • CHAR(n): Fixed-length string of characters, padded with spaces if necessary.
  • DATE: Stores a date.
  • DATETIME: Stores date and time.
  • DECIMAL(p, s): Exact numeric value with precision p and scale s.
  • FLOAT: Approximate floating-point number.
  • BOOLEAN: Stores TRUE or FALSE values (syntax may vary by RDBMS).

SQL Commands: DML and DDL

SQL commands are broadly categorized into Data Manipulation Language (DML) and Data Definition Language (DDL).

Data Manipulation Language (DML)

DML commands are used to manage data within schema objects. The primary DML commands are:

  • SELECT: Retrieves data from a database.
  • INSERT: Inserts new data into a table.
  • UPDATE: Updates existing data in a table.
  • DELETE: Deletes data from a table.

Example: Inserting Data


INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

Data Definition Language (DDL)

DDL commands are used to define and manage database structure. The primary DDL commands are:

  • CREATE: Creates database objects.
  • ALTER: Modifies existing database objects.
  • DROP: Deletes database objects.
  • TRUNCATE: Removes all records from a table quickly.
  • RENAME: Renames a database object.

CREATE, ALTER, and DROP were demonstrated in earlier sections.

Writing Basic Queries

The SELECT statement is your primary tool for retrieving data.

Selecting All Columns:


SELECT * FROM Customers;

Selecting Specific Columns:


SELECT FirstName, Email FROM Customers;

Filtering Results with WHERE:


SELECT * FROM Customers
WHERE LastName = 'Doe';

Sorting Results with ORDER BY:


SELECT * FROM Customers
ORDER BY LastName ASC; -- ASC for ascending, DESC for descending

Constraints

Constraints are rules enforced on data columns to ensure accuracy and reliability. Common constraints include:

  • PRIMARY KEY: Uniquely identifies each row in a table.
  • FOREIGN KEY: Links two tables together.
  • UNIQUE: Ensures all values in a column are different.
  • NOT NULL: Ensures a column cannot have a NULL value.
  • CHECK: Ensures all values in a column satisfy a specific condition.

Tip:

Using PRIMARY KEY and FOREIGN KEY constraints is fundamental for establishing relationships between tables, which is a cornerstone of relational database design.

Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. Without an index, the database system must scan through every row of the table to find the required rows.

Creating an Index:


CREATE INDEX idx_lastname
ON Customers (LastName);

Indexes can significantly improve query performance, especially on large tables, but they also add overhead to data modification operations (INSERT, UPDATE, DELETE).