Build Your Data Superpower with SQL Basics
SQL Structured Query Language, is a programming language designed to manage data stored in relational databases.
Some of the most common data types are:
Integer, a positive or negative whole number
TEXT, a text string
DATE, the date formatted as YYYY-MM-DD
REAL, a decimal value
CHAR(n) : Fixed-length storage. It always reserves n bytes, even if the actual data is shorter. If a string is shorter than n, it is right-padded with spaces.We should use CHAR when all values are of the same or nearly the same length (e.g., country codes, fixed-length IDs, status codes).
VARCHAR(n): Variable-length storage. It only uses as much space as needed, plus 1 or 2 extra bytes for storing the string length.We use this when values have varying lengths (e.g., names, addresses, descriptions).
Comments in SQL:
-- : This can be use to comment single line
/* */ : This can be use to comment multiple line
SQL Statements:
SQL statements always end up with a semicolon. (Note: no restriction of uppercase/lowercase even in clauses/keywords)
Data Querying: SELECT statement
This is useful when you want to quickly get all the columns from a table without writing every column in the SELECT statement.
Syntax: Select * from Employees;
Explanation:
Whenever you want to select any number of columns from any table, you need to use the SELECT statement. You write it, rather obviously, by using the SELECT keyword.
After the keyword comes an asterisk (*), which is shorthand for “all the columns in the table”.
To specify the table, use the FROM clause and write the table’s name afterward.
DATA Manipulation (DML) :
DML, or Data Manipulation Language, statements are SQL commands used to manipulate data within a database. They primarily focus on accessing, retrieving, and modifying data stored in tables. Common DML statements include INSERT, UPDATE, DELETE.
a) INSERT statement: Add new rows or records to a table.
Syntax : INSERT INTO columns in order:
INSERT INTO table_name
VALUES (value1,value2);
-- INSERT INTO columns by name:
INSERT INTO table_name(column1,column2)
VALUES (value1,value2);
Example : INSERT INTO Employees (FirstName, LastName, Department)
VALUES ('John', 'Doe', 'Sales');
--adds a new employee to the table.
The number of rows we want to add data,we can use Insert into statements accordingly.
Note: Values statement cannot use with SELECT statement.
UPDATE : The UPDATE statement is used to edit records (rows) in a table.
Modify existing data within a table, changing the values of specific columns.
Example 1 : UPDATE celebs
SET twitter_handle = '@taylorswift13'
WHERE id = 4;
Example 2:
UPDATE Employees SET Department = 'Marketing' WHERE EmployeeID = 1; changes the department of the employee with EmployeeID 1 to "Marketing".
DELETE: Remove rows or records from a table based on a specified condition.
Used as 'Delete from'
Example 1:
DELETE FROM celebs
WHERE twitter_handle IS NULL;
Example 2:
DELETE FROM Employees
WHERE Department = 'HR';
It removes all employees from the "HR" department.
Data Definition Language (DDL) Statements:
Data definition language (DDL) statements let you create and modify BigQuery resources using GoogleSQL query syntax. You can use DDL commands to create, alter, and delete resources, such as the following: Datasets. Tables. Table schemas.
CREATE TABLE :
Create a table and its columns together with their datatype.
Syntax: CREATE TABLE table_name (
column_1 data_type,
column_2 data_type,
column_3 data_type
);
Example : CREATE TABLE Employees (
Emp_id int,
Emp_name Varchar,
EMP_address Varchar
);
ALTER TABLE : The ALTER TABLE statement is used to modify the columns of an existing table.
Syntax: ALTER TABLE table_name
ADD column_name data_type;
Example: ALTER TABLE Employees
ADD Emp_city Varchar;
DROP: Delete the table with its data.
Syntax : DROP table_name;
Example : DROP Employees;
TRUNCATE: It empties the contents in a table.
Syntax : TRUNCATE table table_name;
Example: TRUNCATE TABLE EMPLOYEES;
SQL Constraints: (Limitations/boundaries) :
SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table.
CREATE TABLE celebs (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
date_of_birth TEXT NOT NULL,
date_of_death TEXT DEFAULT 'Not Applicable'
);
a) Primary Key :PRIMARY KEY columns can be used to uniquely identify the row.If we declare more than one column as primary key,then this is k/as Composite primary key.meaning that the primary key consists of multiple columns.
For single column:
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID, LastName));
Composite Primary Key (PRIMARY KEY (ID, LastName))
This means that both ID and LastName together must be unique for each row.
A duplicate ID is allowed as long as the LastName is different.
A duplicate LastName is allowed as long as the ID is different.
A row is uniquely identified only if both values are unique together.
Named Constraint (CONSTRAINT PK_Person)
PK_Person is the name of the primary key constraint.
Naming constraints explicitly makes it easier to reference them later.
b)UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.
NOT NULL columns must have a value.
DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.
Foreign keys in a table must always refer to valid primary keys in another table.
For example, if there is an Orders table with a foreign key referencing the Customers table, referential integrity ensures that an order cannot exist without a valid customer ID in the Customers table.
Widely used keywords in SQL:
AS: It s a keyword in SQL that allows you to rename a column or table using an alias.
SELECT name AS 'Titles'
FROM movies;
DISTINCT :It is used to return unique values in the output. It filters out all duplicate values in the specified column(s).
SELECT DISTINCT genre
FROM movies;
LIKE : it can be a useful operator when you want to compare similar values.
SELECT *
FROM movies
WHERE name LIKE 'Se_en';
SELECT *
FROM movies
WHERE name LIKE '%e%';
IS NULL, IS NOT NULL Keywords
SELECT name
FROM movies
WHERE imdb_rating IS NOT NULL;
Group By : In SQL, if you use columns in the SELECT statement, they should also appear in the GROUP BY clause (unless they're inside an aggregate function
BETWEEN : It filters the result set within a certain range. It accepts two values that are either numbers, text or dates.
SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999; : This query will give results from 1990 to 1999(1990 and 1999 are included)
SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';
Inabove query, BETWEEN filters the result set to only include movies with names that begin with the letter ‘A’ up to, but not including ones that begin with ‘J’.
Order By :
We can sort the results using either alphabetically or numerically.
SELECT *
FROM movies
ORDER BY name; : this query will sort the result according to ascending order,if we don't mention ASC,then by default it takes ascending order.
ASC: Ascending DESC: Descending
LIMIT : It is a clause that lets you specify the maximum number of rows the result set will have. This saves space on our screen and makes our queries run faster.
SELECT *
FROM movies
LIMIT 10;
It always goes at the very end of the query. Also, it is not supported in all SQL databases.
Wrapping Up
Congratulations! You've just taken your first step toward mastering SQL — this is the language which is used in every industry to provide data insights. As it contains different kinds of Data definition,manipulation and querying statements which help us to query, manipulate, and manage data efficiently.
Whether you're aspiring to be a data analyst or just want to handle the data efficiently, knowing SQL will give you a superpower in today’s data-driven world. Keep practicing with real datasets, experiment with queries, and soon you’ll be writing complex SQL like a pro.
What’s Next?
In upcoming blogs, we’ll explore:
Normalization
Data transactions
SQL Joins
Window functions
Real-world business use cases
Stay tuned, and keep querying!
REFERENCES :
