SQL Tutorial: An Introduction to Structured Query Language

What is SQL?

SQL, or Structured Query Language, is a programming language used to communicate with relational databases. With the help of SQL, you can retrieve, insert, update, and delete data. SQL is also used to define the structure of databases.

With the help of SQL, you can:

• Data Access: Users can access and manipulate data.
• Data Retrieval: You can pull specific data from a database.
• Database Management: Create, update, modify, and delete database elements.
• Structure Definition: Define how a database is organized.

Understanding Databases

A database is an organized collection of data. Data is information that exists in various forms, like videos, pictures, or text. A database organizes this information for easy access, management, and maintenance.

Examples of databases:

• Telephone directories
• Customer information
• Product inventory records
• Visitor registers
• Weather records

How Databases Store Data

Relational Database Management Systems (RDBMS) store data in tables. Each table uses rows and columns to organize data.

Consider this student data example:

Key Database Components

• Table: A table stores data in a structured way. It uses rows and columns to store data entries.
• Field: A field describes each record in a table. For example, in the student table above, ID, Name, Age, and Department are fields.
• Row (Record): A row, also called a record, provides information about a single entry in a table. Rows appear horizontally in a table.
• Column: A column appears vertically in a table and contains information about a specific header, which is the column name. For example, the Age column shows all ages.
• NULL Values: A NULL value means a field has no value. It differs from zero or an empty space.

Data Integrity

Data integrity ensures data remains accurate and consistent. RDBMS has categories for data integrity:

• Entity Integrity: Ensures no duplicate rows in a table.
• Domain Integrity: Guarantees valid entries for a column based on type, range, and format.
• Referential Integrity: Prevents deleting rows used by other records in the table.
• User-defined Integrity: Defines specific business rules set by users.

Database Management System (DBMS) Operations

DBMS allows you to manage data effectively. Key operations include:

• Adding new files
• Inserting data
• Retrieving data
• Modifying data
• Removing data
• Removing files

Introduction to RDBMS

A Relational Database Management System (RDBMS) stores data in a structured format, using rows and columns. Data values within each table are related, and tables can also link to other tables. This relational structure allows you to run queries across multiple tables simultaneously.

Features of RDBMS:

• All information stores as tables.
• Uses primary keys for unique row identification.
• Uses foreign keys to maintain data integrity.
• Provides SQL for data access.
• Uses indexes for faster data retrieval.
• Offers access privileges for data security.

DBMS vs. RDBMS

RDBMS vs. Traditional Approach

RDBMS applications store data in tabular form. Traditional approaches store data as files, often in hierarchical or navigational forms. In traditional systems, data units may have multiple child nodes but only one parent node, and no inherent “relation” between tables like in an RDBMS.

SQL Statements and Syntax

SQL statements begin with commands like SELECT, INSERT, DELETE, DROP, UPDATE, or ALTER. They end with a semicolon ;.

Example:
SELECT column_name FROM table_name;

The semicolon separates SQL statements. This is standard practice when using multiple SQL statements in one call.

SQL syntax follows a unique set of rules. Keywords include SELECT, WHERE, UPDATE, ORDER BY, and HAVING.

Basic Syntax Examples:

• SQL SELECT: SELECT col1, col2, ... colx
• SQL WHERE:
  

  SELECT col1, col2, ... colx
  FROM table_name
  WHERE condition;

  
  

How SQL Queries Work

When you execute an SQL query, the system determines the best way to fulfill your request. The SQL engine interprets the task and processes the query.

Important SQL Commands

These are essential SQL commands:

• SELECT: Extracts data from a database.
• UPDATE: Changes existing data in a database.
• DELETE: Removes data from a database.
• CREATE TABLE/DATABASE: Builds a new table or database.
• DROP TABLE/DATABASE: Deletes a table or database.
• ALTER TABLE/DATABASE: Modifies a table or database.
• INSERT INTO: Adds new data into a database.

Database Normalization

Normalization breaks down large, complex tables into smaller, simpler ones. It organizes your database efficiently, moving data into higher “normal forms.”

Why You Need Normalization

Normalization ensures a solid database design and smooth operations. It helps you:

• Create effective database structures.
• Run database operations efficiently.
• Avoid costly performance issues.
• Prevent unnecessary data duplication.

Functional Dependency

Functional Dependency explains how one piece of data (an attribute) is related to another. For example, if you have a “Student” record, the Student ID can uniquely determine the Student Name.

Example: In a student course table:

• Student ID → Student Name (One Student ID corresponds to one Name)
• Course ID → Course Name (Each Course ID corresponds to one Course Name)

Types of Functional Dependency:

• Partial Functional Dependency: An attribute depends on only part of a composite key, not the entire key.
• Transitive Dependency: A non-key attribute depends on another non-key attribute.

Normalization Forms

Database normalization follows specific rules called “normal forms”:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)

1. First Normal Form (1NF)

A table is in 1NF if:

• All data is atomic (each cell contains a single, indivisible value).
• There are no repeating groups of columns.

Example:

Before 1NF:

After 1NF:

2. Second Normal Form (2NF)

A table is in 2NF if:

• It is already in 1NF.
• No non-key attribute is partially dependent on a primary key. (This applies to tables with composite primary keys).

Example:

Consider a table with a composite primary key (Student#, Course#):

Here, Instructor depends on Course# (M1 determines Prof. A/B). Instructor does not depend on the full composite key (Student#, Course#). In 2NF, Instructor should move to a separate Course table, as it only depends on Course#.

3. Third Normal Form (3NF)

A table is in 3NF if:

• It is already in 2NF.
• No transitive dependency exists (non-key attributes do not depend on other non-key attributes).

Example:

Consider a table that is already in 2NF:

Here, Department depends on Instructor (Prof. A teaches in Math, Prof. B teaches in Science). Department does not depend directly on the primary key (Student#, Course#). In 3NF, Department should move to a separate Instructor table, as it only depends on Instructor, which is a non-key attribute here.

Normalization: Pros and Cons

Advantages:

• Based on strong mathematical foundations.
• Reduces data redundancy significantly.
• Minimizes issues during data inserts, updates, and deletes.

Disadvantages:

• Can negatively impact data retrieval performance due to more joins.
• Might not always perfectly represent complex real-world situations.

SQL Data Types

SQL uses various data types for columns:

1. Numeric: bit, tinyint, smallint, int, bigint, decimal, numeric, float, real
2. Character/String: Char, Varchar, Varchar(max), Text
3. Date/Time: Date, Time, Datetime, Timestamp, Year
4. Unicode Character/String: Nchar, Nvarchar, Nvarchar(max), NText
5. Binary: Binary, Varbinary, Varbinary(max), image
6. Miscellaneous: Clob, Blob, Json, XML

SQL Constraints

SQL Constraints enforce rules on data in a table:

SQL Database Operations

CREATE Database

This statement creates a new database.
Syntax: CREATE DATABASE database_name;
Example: CREATE DATABASE testdb;

DROP Database

This command deletes a database. All tables and views within it are also deleted.
Syntax: DROP DATABASE database_name;
Example: DROP DATABASE testdb;

RENAME Database

This statement changes a database’s name.
Syntax: RENAME DATABASE old_dbname TO new_dbname;
Example: RENAME testdb TO newdb;

SELECT Database

You must select a database before running queries on its tables.
Syntax: USE DATABASE database_name;
Example: USE DATABASE newdb;

SQL Keys

Primary Key

A primary key is a column or set of columns that uniquely identifies each row in a table. A primary key is NOT NULL, INDEXED, and UNIQUE by default. If multiple columns form the key, it is a composite primary key.

Syntax (CREATE TABLE):

CREATE TABLE students(
    ID INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    Age INT NOT NULL,
    Department VARCHAR(255),
    PRIMARY KEY(ID)
);

Syntax (ALTER TABLE):

ALTER TABLE students
ADD PRIMARY KEY(ID);

Note: The column must not contain NULL values when adding a primary key to an existing table.

Foreign Key

A foreign key links two tables in relational databases. It points to the primary key in another table.

Example:
First table (Students):

Second table (Faculty):

Here, Student_id in the Faculty table is a foreign key, referencing Student_id (primary key) in the Students table.

Syntax (CREATE TABLE with FOREIGN KEY constraint):

CREATE TABLE Faculty(
    faculty_ID INT NOT NULL,
    Faculty_name VARCHAR(255) NOT NULL,
    Student_id INT NOT NULL,
    PRIMARY KEY(Student_id),
    FOREIGN KEY(Student_id) REFERENCES Students(Student_id)
);

Primary Key vs. Foreign Key

Composite Key

A composite key combines two or more fields or columns in a table.
Syntax:

CREATE TABLE table_name(
    col1 INT,
    col2 INT,
    col3 VARCHAR(50),
    PRIMARY KEY (col1, col2)
);

Unique Key

A unique key identifies a record uniquely within a table. Both unique keys and primary keys provide uniqueness for columns.

Alternate Key

An alternate key is a candidate key not chosen as the primary key. If a table has multiple candidate keys, one becomes primary, and others are alternate keys.

SQL Table Operations

CREATE TABLE

This statement creates tables in a database.
Syntax:

CREATE TABLE table_name (
    column 1 "data type",
    column 2 "data type",
    ...
    column x "data type"
);

DROP TABLE

This command deletes a table from the database.
Syntax: DROP TABLE "table_name";
Example: DROP TABLE student;

DELETE TABLE (Rows)

This command deletes rows from a table. Use a WHERE condition to delete specific rows.
Syntax:

• DELETE FROM table_name; (Deletes all rows)
• DELETE FROM table_name WHERE condition; (Deletes specific rows)

Example:

• DELETE FROM student;
• DELETE FROM student WHERE student_id = 2;

ALTER TABLE

This command adds, modifies, or deletes columns in an existing table.
Syntax (ADD Column):

ALTER TABLE table_name
ADD (
    col1 col_definition,
    col2 col_definition,
    ...
    colx col_definition
);

Syntax (MODIFY Column):

ALTER TABLE table_name
MODIFY (
    col1 col_definition,
    col2 col_definition,
    ...
    colx col_definition
);

Syntax (DROP Column):
ALTER TABLE table_name DROP COLUMN column_name;

RENAME TABLE

This command changes a table’s name.
Syntax:

ALTER TABLE table_name
RENAME TO new_table_name;

SQL Command Groups

There are some types of SQL commands, these are:

• DDL (Data Definition Language): Creates and modifies database objects.
• DML (Data Manipulation Language): Manipulates data within objects.
• DCL (Data Control Language): Manages database permissions.
• TCL (Transaction Control Language): Saves and restores database changes.

DDL – Data Definition Language

Examples:

• CREATE TABLE:
  

  CREATE TABLE employees (
      emp_id INT (10) NOT NULL,
      first_name VARCHAR(10),
      last_name VARCHAR(10) NOT NULL,
      salary INT(10) NOT NULL,
      PRIMARY KEY (emp_id)
  );

  
  
• ALTER TABLE (Add Column): ALTER TABLE employees ADD COLUMN contact INT(10);
• ALTER TABLE (Rename Column): ALTER TABLE employees RENAME COLUMN contact TO job_code;
• TRUNCATE TABLE: TRUNCATE TABLE employees;
• DROP TABLE: DROP TABLE table_name; or DROP TABLE employees;

DML – Data Manipulation Language

Examples:

• INSERT INTO:
  

  INSERT INTO employees (emp_id, first_name, last_name, salary)
  VALUES (101, 'Steven', 'King', 10000);

  
  
• UPDATE:
  

  UPDATE employees
  SET last_name='Cohen'
  WHERE emp_id=101;

  
  
• DELETE FROM:
  DELETE FROM employees WHERE emp_id IN (101, 103);

DCL – Data Control Language

TCL – Transaction Control Language

TCL manages transactions in the database.

SQL Operators

SQL operators are special keywords or characters that you use in SQL statements. You use them within the WHERE clause to perform specific operations. These operations help you compare values, combine multiple conditions, or perform calculations, which allows you to filter, modify, or analyze data based on defined criteria.

SQL Arithmetic Operators

Example: In X + Y or X * Y, X is the left operand and Y is the right operand.

SQL Logical Operators

Sample Queries:

• SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;
• SELECT * FROM employees WHERE first_name LIKE ‘Steven’;
• SELECT * FROM employees WHERE salary IS NULL;
• SELECT * FROM employees WHERE salary IN (10000, 12000, 20000);
• SELECT DISTINCT(first_name) FROM employees;

SQL Comparison Operators

Sample Queries:

• SELECT * FROM employees WHERE first_name = ‘Steven’ AND salary <= 10000;
• SELECT * FROM employees WHERE first_name = ‘Steven’ OR salary >= 10000;
• SELECT * FROM employees WHERE first_name = ‘Steven’ AND salary <> 10000;

SQL Aggregate Functions

Aggregate functions perform calculations on sets of rows and return a single value:

Sample Queries:

• SELECT AVG(salary) FROM employees;
• SELECT COUNT(*) FROM employees;
• SELECT MIN(salary) FROM employees;
• SELECT MAX(salary) FROM employees;
• SELECT SUM(salary) FROM employees;

SQL Clauses

WHERE Clause

The WHERE clause specifies a condition when fetching data. It works with SELECT, UPDATE, and DELETE statements.
Example: SELECT * FROM employees WHERE emp_id = 101;

GROUP BY Clause

The GROUP BY clause arranges identical data into groups. It follows the WHERE clause and precedes the ORDER BY clause.
Example:

SELECT SUM(salary), dept_id
FROM employees
WHERE salary >= 15000
GROUP BY dept_id;

HAVING Clause

The HAVING clause filters groups based on aggregate functions. It must follow GROUP BY and precede ORDER BY.
Example:

SELECT AVG(salary), dept_id
FROM employees
WHERE salary >= 10000
GROUP BY dept_id
HAVING COUNT(dept_id) >= 2;

ORDER BY Clause

The ORDER BY clause sorts the output of a SELECT statement. Default sort order is ascending (ASC). Use DESC for descending order.
Example:

SELECT * FROM employees
ORDER BY salary DESC;

SQL Set Operators

Set operators combine results of two or more SELECT statements.

UNION ALL

UNION ALL combines results from two SELECT statements, including duplicate rows.
Syntax:

SELECT a.col1, b.col2, ..., a.coln FROM table1 a, table2 b WHERE a.commonfield = b.commonfield
UNION ALL
SELECT a.col1, b.col2, ..., a.coln FROM table1 a, table2 b WHERE a.commonfield = b.commonfield;

UNION

UNION combines result-sets of two or more SELECT statements, removing duplicates.

• Each SELECT statement must have the same number of columns.
• Columns must have similar data types and be in the same order.
• You can combine multiple queries with several UNION statements.

SQL JOINS

A SQL JOIN combines rows or columns from multiple tables based on a related column.

• INNER JOIN: Returns rows where there’s a match in both tables.
• LEFT JOIN: Returns all rows from the left table, plus matched rows from the right. Returns NULL for unmatched rows in the right table.
• RIGHT JOIN: Returns all rows from the right table, plus matched rows from the left. Returns NULL for unmatched rows in the left table.
• FULL OUTER JOIN: Combines results of left and right outer joins. Returns all records from both tables, with NULL for missing matches.
• SELF JOIN: Joins a table to itself, treating it as two separate tables by renaming one.
• CARTESIAN JOIN (CROSS JOIN): Returns the Cartesian product of records from two or more tables.

SQL INNER JOIN

INNER JOIN creates a new result table by combining column values from two tables based on a join condition.
Syntax:

SELECT table1.col1, table2.col2, ..., table1.coln
FROM table1
INNER JOIN table2
ON table1.commonfield = table2.commonfield;

SQL LEFT JOIN

LEFT JOIN returns all values from the left table and matched values from the right table. It returns NULL if no match exists in the right table.
Syntax:

SELECT table1.col1, table2.col2, ..., table1.coln
FROM table1
LEFT JOIN table2
ON table1.commonfield = table2.commonfield;

SQL RIGHT JOIN

RIGHT JOIN returns all values from the right table and matched values from the left table. It returns NULL if no match exists in the left table.
Syntax:

SELECT table1.col1, table2.col2, ..., table1.coln
FROM table1
RIGHT JOIN table2
ON table1.commonfield = table2.commonfield;

SQL FULL OUTER JOIN

FULL OUTER JOIN combines results from both LEFT JOIN and RIGHT JOIN. The result includes all records from both tables, filling in NULL for unmatched records.
Syntax:

SELECT table1.col1, table2.col2, ..., table1.coln
FROM table1
LEFT JOIN table2
ON table1.commonfield = table2.commonfield
UNION
SELECT table1.col1, table2.col2, ..., table1.coln
FROM table1
RIGHT JOIN table2
ON table1.commonfield = table2.commonfield;

SQL SELF JOIN

SELF JOIN joins a table to itself. You must temporarily rename at least one table in the SQL statement.
Syntax:

SELECT a.col1, b.col2, ..., a.coln
FROM table1 a, table1 b
WHERE a.commonfield = b.commonfield;

SQL CROSS JOIN

CROSS JOIN creates a result set where the number of rows equals the product of the rows in the joined tables.
Syntax:

SELECT table1.col1, table2.col2, ..., table1.coln
FROM table1
CROSS JOIN table2;

SQL FAQs

What are the basics to learn SQL?

SQL stands for Structured Query Language. It operates databases. To learn SQL basics, understand what SQL means and learn fundamental query syntax. Knowing different database management systems, RDBMS, and SQL commands helps you learn the basics.

How long does it take to learn SQL for beginners?

The time to learn SQL varies per person. Two to three weeks is generally enough to learn the basics and start working with databases. Becoming highly efficient takes longer.

Is SQL good for beginners?

SQL is widely used in companies. It is an excellent choice for beginners to enter programming.

Where can I learn SQL for beginners?

Many resources help beginners learn SQL. Learn from blog posts, YouTube content, books, or free SQL courses.

Is SQL coding?

SQL stands for structured query language and it is a programming language. The main purpose of SQL is to communicate with relational databases.

Is SQL easier than Python?

In terms of grammar and the amount of concepts to be learnt, SQL is simpler. However, as a tool, SQL may be more difficult. We cannot really compare the two as they are both easy to use in their own ways and for different purposes.

Can I get a job if I know SQL?

Yes, you can get a job with SQL skills. SQL is one of the most widely used programming languages, and recruiters actively seek individuals with SQL knowledge.

Should I learn SQL or Python first?

Both Python and SQL are in high demand. Learning Python fundamentals first, then adding SQL for data manipulation, can be an effective approach. You can choose either based on your goals.