Lesson 4 of 12 · 4 min

What is MYSQL Database?

SQL, which stands for Structured Query Language, is a domain-specific language that plays a pivotal role in managing and manipulating relational…

On this page
  1. Data Manipulation:
  2. Data Definition:
  3. Data Querying:
  4. Data Joins:
  5. Constraints:

SQL, which stands for Structured Query Language, is a domain-specific language that plays a pivotal role in managing and manipulating relational databases. By using SQL, professionals can perform a wide array of tasks, including creating, retrieving, updating, and deleting data within a database. This language forms the backbone of interactions with databases and is instrumental in maintaining the integrity and efficiency of data management processes.

Let’s delve into the core components of SQL:

Data Manipulation:

SQL grants users the capability to interact with databases through a variety of operations. These operations encompass:

SELECT:

This operation retrieves data from specified database tables, allowing users to tailor their queries to meet specific requirements.

Syntax : SELECT column1, column2, … FROM table_name;

Example :

SELECT * FROM EMPLOYEE;

INSERT INTO:

Through this operation, new records are seamlessly incorporated into existing database tables.

Syntax : INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);

Example :

INSERT INTO Employee(EmployeeName, ContactName,   Address, City, PostalCode, Country)  VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21',   'Stavanger', '4006', 'Norway');

UPDATE:

Should the need arise, existing records within tables can be modified or updated using this operation.


Syntax : UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

Example :

UPDATE Employee SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE Employed = 1;

DELETE:

This operation enables the removal of records from tables, facilitating data management.


Syntax : DELETE FROM table_name WHERE condition;

Example :

DELETE FROM Customers WHERE EmployeeName='Alfreds Futterkiste';

Data Definition:

SQL is also adept at defining the structure of databases and their associated components. This includes:

CREATE TABLE:

Users can define new tables along with their requisite columns, data types, and constraints.

Syntax : CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ….
);

Example :

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

ALTER TABLE:

Existing tables can be modified to accommodate changes, such as adding, altering, or removing columns or constraints.

Syntax : ALTER TABLE table_name ADD column_name datatype;

Example :

ALTER TABLE Persons ADD Email varchar(255);

DROP TABLE:

Tables, along with their associated data, can be deleted from the database.

Syntax : DROP TABLE table_name;

Example :

DROP TABLE Persons;

Data Querying:

SQL equips users with the ability to query data by utilizing various conditions and operators, such as:

WHERE:

This operator facilitates the filtering of rows based on specific conditions.


Syntax : SELECT column1, column2, … FROM table_name WHERE condition;

Example :

SELECT * FROM Person WHERE Country='Mexico';

GROUP BY:

By grouping rows that share common values in designated columns, users can perform aggregate functions like SUM, COUNT, and AVG.

Syntax : SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example :

SELECT COUNT(PersonID), Country
FROM Person
GROUP BY Country;

HAVING:

This operator functions as a filter that comes into play after utilizing the GROUP BY clause.

Syntax : SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example :

SELECT COUNT(PersonID), Country
FROM Persons
GROUP BY Country
HAVING COUNT(PersonID) > 5;

ORDER BY:

It’s possible to sort query results either in ascending or descending order based on selected columns.

Syntax : SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

Example :

SELECT * FROM Persons ORDER BY Country;

Data Joins:

SQL boasts the functionality to consolidate data from multiple tables through joins, including:

INNER JOIN:

This join retrieves matching records from multiple tables based on specific conditions.

Syntax : SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example :

SELECT Orders.OrderID, Customers.CustomerName FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN (or LEFT OUTER JOIN):

It fetches all records from the left table alongside matching records from the right table.


Syntax : SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example :

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

RIGHT JOIN (or RIGHT OUTER JOIN):

Similarly, this join retrieves all records from the right table alongside matching records from the left table.

Syntax : SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name

Example :

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

FULL JOIN (or FULL OUTER JOIN):

It assembles all records from both tables.

Syntax : SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Example :

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Constraints:

SQL caters to maintaining data integrity through the application of constraints, including:

PRIMARY KEY constraint:

Ensuring uniqueness and identifying unique records within a table.

Syntax : CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

FOREIGN KEY constraint:

Establishing relationships between tables, thus upholding referential integrity.

Syntax : CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

CHECK constraint:

Specifying conditions that data must adhere to during insertion or updating.

Syntax : CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

UNIQUE constraint:

Ensuring that values within a column remain unique.

Syntax : CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

NOT NULL constraint:

Ensuring that columns do not contain null values.

Syntax : CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

SQL’s ubiquity spans across various database management systems (DBMS), encompassing names like MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, and SQLite. Although SQL’s fundamental syntax retains a semblance of consistency across these platforms, variations in syntax and feature implementations can occur.

Mastering SQL proves indispensable for competent management and querying of relational databases. Whether it’s for web applications, analytical insights, reporting, or other data-related objectives, possessing SQL proficiency empowers individuals to retrieve, manipulate, and safeguard data within a structured framework.

As such, SQL emerges as a critical skill for developers, data analysts, and administrators. Its significance resonates through the efficiency it brings to database management and the seamless retrieval and manipulation of data, all within a standardized syntax that transcends diverse database systems.

More from Database Systems