The Developer Space

3 0

Structured Query Language (SQL) is the backbone of database management and a must-know skill for anyone working with data. Whether you’re a front-end developer looking to expand your skill set or a beginner stepping into the world of databases, SQL is a powerful tool that allows you to interact with and manipulate data efficiently. This guide ‘SQL for Beginner’ will walk you through the basics of SQL, with clear explanations and practical code examples to help you get started. Intermediate and advanced SQL topics will be discussed in future articles.

Introduction to SQL

SQL is a domain-specific language used to manage and manipulate relational databases. It allows you to perform tasks like defining table structures, querying data, inserting new records, updating existing data, and deleting records. SQL is not tied to any specific database platform, and the skills you learn here can be applied to databases like MySQL, PostgreSQL, SQL Server, and more.

Why should you learn SQL?

SQL is a fundamental technology for managing and analyzing data efficiently. It plays a crucial role in various domains, including web development, data science, and enterprise applications. SQL is used for storing and retrieving data in relational databases, generating reports, performing data analysis, and integrating with programming languages for backend development. Businesses rely on SQL for decision-making, customer relationship management, and real-time data processing, making it an essential skill for developers, analysts, and IT professionals alike.

ANSI SQL Standard

SQL follows the ANSI (American National Standards Institute) standard, ensuring consistency across different database management systems (DBMS). While most database providers adhere to this standard, they may also introduce proprietary extensions and optimizations. MySQL, PostgreSQL, SQL Server, and Oracle support ANSI SQL with slight variations in syntax and additional features. Understanding the ANSI standard ensures that your SQL knowledge remains transferable across different database systems.

Understanding RDBMS Concepts

Before diving into SQL, it’s important to understand the foundational concepts of Relational Database Management Systems (RDBMS). RDBMS is a type of database management system that stores data in a structured format, using tables to represent relationships between data. Here are some key concepts:

1. Tables: Data is stored in tables, which consist of rows and columns. Each table represents a specific entity (e.g., users, orders).
2. Rows: A row, also called a record, represents a single entry in a table (e.g., a specific user).
3. Columns: A column, also called a field, represents a specific attribute of the data (e.g., first_name, age).
4. Primary Key: A unique identifier for each record in a table (e.g., user_id).
5. Foreign Key: A column that creates a relationship between two tables by referencing the primary key of another table.
6. Relationships: Tables can be related to each other using keys, enabling complex queries across multiple tables.

Understanding these concepts will help you design efficient databases and write effective SQL queries.

Basic SQL Data Types

When creating tables or defining columns, you need to specify the type of data each column will store. SQL supports a variety of data types to handle different kinds of data. Here are some of the most common SQL data types:

1. Numeric Types:
   • INT: Stores whole numbers (e.g., 42).
   • DECIMAL(p, s): Stores exact numeric values with a specified precision (p) and scale (s). For example, DECIMAL(5, 2) can store numbers like 123.45.
   • FLOAT: Stores approximate numeric values with floating-point precision.
2. Character/String Types:
   • CHAR(n): Stores fixed-length strings. For example, CHAR(10) always stores 10 characters, padding with spaces if necessary.
   • VARCHAR(n): Stores variable-length strings up to a maximum length (n). For example, VARCHAR(50) can store up to 50 characters.
   • TEXT: Stores large text data (e.g., paragraphs or documents).
3. Date and Time Types:
   • DATE: Stores a date. Example: 2015-01-01.
   • TIME: Stores a time. Example: 10:15:30.
   • DATETIME: Stores both date and time. Example: 2015-01-01 10:15:30 .
   • TIMESTAMP: Stores a timestamp, often used for recording when a row was created or modified.
4. Boolean Type:
   • BOOLEAN: Stores TRUE or FALSE values.
5. Binary Types:
   • BLOB: Stores binary data, such as images or files.

These data types are supported in most relational databases. However, a few database platforms may have different names for these types and minor syntax differences in how they are declared and used.

Classification of SQL Commands

SQL commands can be broadly classified into several categories based on their functionality. These categories help organize the language and make it easier to understand its purpose and usage:

1. Data Definition Language (DDL): Commands used to define and modify the structure of database objects like tables, indexes, and schemas. Examples include:
   • CREATE: Creates a new database object (e.g., CREATE TABLE).
   • ALTER: Modifies an existing database object (e.g., ALTER TABLE).
   • DROP: Deletes a database object (e.g., DROP TABLE).
2. Data Manipulation Language (DML): Commands used to manipulate data within database objects. Examples include:
   • SELECT: Retrieves data from a table.
   • INSERT: Adds new data to a table.
   • UPDATE: Modifies existing data in a table.
   • DELETE: Removes data from a table.
3. Data Control Language (DCL): Commands used to control access to data in the database. Examples include:
   • GRANT: Gives users access privileges to the database.
   • REVOKE: Removes access privileges from users.
4. Transaction Control Language (TCL): Commands used to manage transactions in the database. Examples include:
   • COMMIT: Saves changes made during a transaction.
   • ROLLBACK: Undoes changes made during a transaction.
   • SAVEPOINT: Sets a point within a transaction to which you can later roll back.
5. Data Query Language (DQL): A subset of DML focused solely on querying data. The primary command is:
   • SELECT: Retrieves data from one or more tables.

In this article, we’ll discuss Data Definition Language (DDL), Data Manipulation Language (DML) and Data Query Language (DQL).

Setting up the sample Database

Before diving into SQL commands, let’s create a simple database. The CREATE DATABASE command is used to create a new database, and USE selects it for subsequent queries. You can use any of the popular Relational Databases listed at the end of the article, to try out the code examples.

CREATE DATABASE BeginnerSQL;
USE BeginnerSQL;

Data Definition Language (DDL) Commands

The DDL commands allow you to define and modify the structure or schema that hold your data.

CREATE TABLE

The CREATE TABLE statement defines a new table with columns and constraints. Let’s create the Users table to practice our queries. The table has columns like id, firstname, lastname, age, and email. You can use this as a reference to understand how the data might look in a real-world scenario.

CREATE TABLE Users (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NULL,
    Age INT CHECK (age > 0),
    Email VARCHAR(100) UNIQUE NOT NULL
);

ALTER TABLE

The ALTER TABLE statement is used to modify an existing table, such as:

1. Adding new columns.
2. Removing existing columns
3. Changing data types of columns
4. Adding keys and constraints

ALTER TABLE Users
ADD PhoneNumber VARCHAR(20) NULL;

This command adds a new column PhoneNumber to the Users table, and sets NULL as the default value for existing rows. After running this statement, you can query the table to verify that the new column has been added to the table and contains NULLs for existing rows.

ALTER TABLE Users
DROP COLUMN PhoneNumber;

This command drops a column PhoneNumber from the Users table. When a column is dropped from a table, all of its data is permanently deleted. After running this statement, you can query the table to verify that the column has been removed to the table.

DROP TABLE

The DROP TABLE statement is used to delete an entire table from the database. After this statement is executed, the table is deleted permanently along with all of its data.

DROP TABLE Users;

After running this statement, querying the table returns an error as the table no longer exists in the database.

There are other DDL commands in SQL like creating an index to speed up database queries, but we’ll learn those advanced topics in future articles.

Data Manipulation Language (DML) Commands

The DML commands allow you to add, modify and delete data in tables in the database.

INSERT

To insert a new row into the Users table. Let’s insert some sample data into the Users table. The INSERT INTO statement adds new records by specifying column values.

INSERT INTO Users (FirstName, LastName, Age, Email) VALUES
('John', 'Doe', 30, 'john.doe@example.com'),
('Jane', 'Smith', 25, 'jane.smith@example.com'),
('Alice', 'Johnson', 28, 'alice.johnson@example.com'),
('Bob', 'Brown', 35, 'bob.brown@example.com');

After executing this command, you can verify that the records have been inserted by executing this SELECT command.

SELECT * FROM Users;

which will produce the following output:

UPDATE

The UPDATE statement modifies existing rows in a table. You can specify which rows are updated by providing a condition in the WHERE clause. If you do not provide a WHERE clause, all rows in the table are updated without any warnings.

This example changes David’s age to 42:

UPDATE Users 
SET Age = 42 
WHERE FirstName = 'David';

After running this statement, you can query the table to verify that the new row has been updated in the table.

DELETE

The DELETE statement deletes existing rows from a table. You can specify which rows are deleted by providing a condition in the WHERE clause. If you do not provide a WHERE clause, all rows in the table are deleted without any warnings.

This example deletes David’s row:

DELETE FROM Users 
WHERE FirstName = 'David';

This command deletes all users with the first name ‘David’. If you wish to delete only one row from a table, you must include the Primary key or a Unique key in the WHERE clause. After running this statement, you can query the table to verify that the row has been deleted from the table.

Data Query Language (DQL) Commands

The DQL command allow you to retrieve rows from tables in the database.

SELECT

The SELECT statement is used to fetch data from a table. The * is a wildcard that instructs the query engine to select all columns.

SELECT * 
FROM Users;

If you wish to select specific columns you can specify the column names separate by commas.

SELECT FirstName, LastName, Age 
FROM Users;

Filtering Data

The WHERE clause filters records based on conditions. The following query selects users who are 30 or older.

SELECT * 
FROM Users 
WHERE Age >= 30;

You can combine multiple conditions with the AND clause.

SELECT * 
FROM Users 
WHERE Age >= 30 
  AND FirstName = 'Bob';

Sorting Results

The ORDER BY clause sorts query results in ascending (ASC) or descending (DESC) order.

SELECT * 
FROM Users 
ORDER BY Age ASC;

If the order ASC or DESC is not specificed, it defaults to ASC.

Try it out yourself

All Relational databases support SQL. However, there are a few minor quirks in the SQL syntax supported by each of these providers. You can try these examples in any of the leading database systems.

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

https://www.postgresql.org/download

MySQL

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter My, and “SQL”, the acronym for Structured Query Language.

https://www.mysql.com/downloads

SQL Server

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. The Express Edition is free for Development, Testing and small web and desktop applications.

https://www.microsoft.com/en-in/sql-server/sql-server-downloads

Oracle

Oracle Database (commonly referred to as Oracle DBMS, or simply as Oracle) is a proprietary multi-model database management system produced and marketed by Oracle Corporation.

https://www.oracle.com/in/database/technologies/appdev/xe.html

Tools/IDEs

To access these databases interactively, you’ll need a Database Management/Development tool or IDE. You can consider the following tools/IDEs to learn SQL.

DBStudio is a database access tool that can connect to PostgreSQL and SQL Server (limted support)

DBeaver is a universal database tool that can connect to multiple database platforms.

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL.

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs to work with MySQL databases.

SQL Server Management Studio (SSMS) is a management tool to access SQL Server databases.

Conclusion

As a developer, mastering SQL is a valuable skill that can significantly impact your career. Whether you specialize in front-end, back-end, or full-stack development, understanding SQL allows you to interact with databases efficiently, optimize data retrieval, and build scalable applications. SQL knowledge is essential for working with APIs, analyzing large datasets, and improving application performance. By learning SQL, developers gain the ability to write efficient queries, troubleshoot database issues, and integrate data-driven features into their projects, making them more versatile and in-demand in the job market. Start practicing these commands in a database environment, and you’ll quickly become comfortable with SQL. Happy querying!

Related Articles