SQL at Glance
SQL (Structured Query Language) is a standardized programming language used to manage relational databases and execute various operations on related data. SQL, which was developed in the 1970s, is now widely used not only by database administrators, but also by developers building data integration scripts and data analysts wanting to set up and perform analytical queries.
SQL is used to modify database table and index structures, add, update, and delete rows of data, and retrieve subsets of information from inside a database for transaction processing and analytics applications. Queries and other SQL operations take the form of statements, which are regularly used instructions. Select, add, insert, update, delete, create, change, and truncate are all SQL statements.
In this blog, we will learn how to perform basic operations in SQL. Get function for inserting data, update data, deleting data, grouping data, order data, etc. If you have started using SQL this is the best reference guide.
SQL Facts
SQL stands for Structured Query Language.
SQL is pronounced “sequel”.
SQL is a declarative language.
SQL is used to access & manipulate data in databases.
Top SQL DBs are MS SQL Server, Oracle, DB2, and MYSQL.
Database Definitions
RDBMS (Relational Database Management System) — Software that stores and manipulates data arranged in relational database tables.
Table — A set of data arranged in columns and rows. The columns represent characteristics of stored data and the rows represent actual data entries.
How to select data from a table
SELECT <Column List>
FROM <Table Name>
WHERE <Search Condition>
Example: SELECT FirstName, LastName, OrderDate
FROM Orders WHERE OrderDate > ‘15/12/2020
SQL Commands Categories
Data Query Language (DQL)
- SELECT — Retrieve data from table(s)
Data Manipulation Language (DML)
- INSERT — Insert data into db table
- UPDATE — Update data in db table
- DELETE — Delete data from table
Data Definition Language (DDL)
- CREATE — Create db object (table, view, etc.)
- ALTER — Modify db object (table, view, etc.)
- DROP — Delete db object (table, view, etc.)
Data Control Language (DCL)
- GRANT — Assign privilege
- REVOKE — remove privilege
How to insert data in a table
INSERT INTO <Table Name>
(<Column List>) VALUES (<Values>)
Example:
INSERT INTO Orders (FirstName, LastName, OrderDate) VALUES (‘VXYZ’, ‘ABCDE’, ‘15/12/2020’
How to update data in a table
UPDATE <Table Name>
SET <Column1>= <Value1>, <Column2> = <Value2>,
WHERE <Search Condition>
Example:
UPDATE Orders
SET FirstName = ‘VXYZ’, LastName ‘Who’ WHERE LastName=’Wo’
How to group data and use aggregates
SELECT <Column List>, <Aggregate Function>(<Column Name>)
FROM <Table Name>
WHERE <Search Condition>
GROUP BY <Column List>
Example:
SELECT LastName, SUM(OrderValue)
FROM Orders
WHERE OrderDate > ‘15/12/2020’
GROUP BY LastName
How to select data from more than one table
SELECT <Column List>
FROM <Table1> XYZ <Table2>
ON <Table1>.<Column1>=<Table2>.<Column1>
Example:
SELECT Orders.LastName, Countries.CountryName
FROM Orders JOIN Countries ON
Orders.CountrylID = Countries.lID
How to delete data from a table
DELETE FROM <Table Name>
WHERE <Search Condition>
Example:
DELETE FROM Orders
WHERE OrderDate < ‘15/12/2020’
How to order data
SELECT <Column List>
FROM <Table Name>
WHERE <Search Condition>
ORDER BY <Column List>
Example:
SELECT FirstName,
LastName, OrderDate
FROM Orders
WHERE OrderDate > ‘15/12/2020’
ORDER BY OrderDate
Using UNION
SELECT <Column List> FROM <Table1>
UNION
SELECT <Column List> FROM <Table2>
Example:
SELECT FirstName, LastName FROM Orders2020
UNION
SELECT FirstName, LastName FROM Orders2021
CREATE TABLE
CREATE TABLE <Table Name>
( Column1 DataType,
Column2 DataType,
Column3 DataType,
… )
CREATE TABLE Orders
( FirstName CHAR(100),
LastName CHAR(100),
OrderDate DATE,
OrderValue Currency )
