SQL Basics Simplified : 2023
Welcome to the exciting world of SQL! SQL, or Structured Query Language, is the language used to manage and manipulate data stored in relational databases. Whether you’re looking to become a data analyst, a database administrator, or just want to improve your data literacy, learning SQL is a great place to start.
In this comprehensive article, we will simplify and demystify the basics of SQL, so you can start your journey with confidence. From creating tables and inserting data, to filtering data using operators and performing calculations using functions, we’ll cover everything you need to know to get started.
So, if you’re ready to dive into the world of SQL and unlock the power of your data, grab a cup of coffee (tea in my case!) and let’s get started!
SQL, short for Structured Query Language, is a standard programming language used to manage relational databases. A relational database is a collection of data organized into tables, with each table containing rows and columns that represent the data. The data in a relational database can be easily searched, sorted, and manipulated using SQL commands. In this article, we will explore the basics of SQL and how you can use it to work with databases.
What is a relational database?
A relational database is a collection of data organized into tables. Each table has columns, also known as fields, that define the type of data that can be stored in that column, and rows, also known as records, which contain the actual data. The columns in a table represent the attributes of the data, while the rows represent individual instances of the data.
For example, consider a table that contains information about employees in a company. This table could have columns for the employee’s name, employee ID, date of birth, and job title, and a row for each employee.
SQL commands
SQL commands are used to interact with a relational database. These commands can be used to create, modify, and query tables and their data. Some of the most common SQL commands are:
SELECT
The SELECT command is used to retrieve data from a table. The syntax for the SELECT command is as follows:
SELECT column1, column2, ..., columnN
FROM table_name;For example, if you want to retrieve the names of all employees in the employee table, you would use the following SQL command:
SELECT name
FROM employee;INSERT
The INSERT command is used to add new data to a table. The syntax for the INSERT command is as follows:
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);For example, if you want to add a new employee to the employee table, you would use the following SQL command:
INSERT INTO employee (name, employee_id, date_of_birth, job_title)
VALUES ('John Doe', '1234', '1980-01-01', 'Manager');UPDATE
The UPDATE command is used to modify existing data in a table. The syntax for the UPDATE command is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE condition;For example, if you want to change the job title of an employee with the employee ID 1234 to “Director”, you would use the following SQL command:
UPDATE employee
SET job_title = 'Director'
WHERE employee_id = '1234';DELETE
The DELETE command is used to delete data from a table. The syntax for the DELETE command is as follows:
DELETE FROM table_name
WHERE condition;For example, if you want to delete an employee with the employee ID 1234 from the employee table, you would use the following SQL command:
DELETE FROM employee
WHERE employee_id = '1234';CREATE
The CREATE command is used to create a new table. The syntax for the CREATE command is as follows:
CREATE TABLE table_name (
column1 data_type constraint,
column2 data_type constraint,
...
columnN data_type constraint
);For example, if you want to create a new table called “employee”, you would use the following SQL command:
CREATE TABLE employee (
name varchar(50),
employee_id varchar(10),
date_of_birth date,
job_title varchar(50)
);In this example, we are creating a table called “employee” with four columns: “name”, “employee_id”, “date_of_birth”, and “job_title”. The data type of each column is specified using the “varchar” or “date” keywords. The number in parentheses after “varchar” specifies the maximum length of the data that can be stored in that column.
ALTER
The ALTER command is used to modify an existing table. The syntax for the ALTER command is as follows:
ALTER TABLE table_name
ADD COLUMN column_name data_type constraint;For example, if you want to add a new column to the employee table called “hire_date”, you would use the following SQL command:
ALTER TABLE employee
ADD COLUMN hire_date date;SQL operators
SQL operators are used to perform operations on data in a relational database. Some of the most common SQL operators are:
WHERE
The WHERE operator is used to filter data based on a specified condition. The syntax for the WHERE operator is as follows:
SELECT column1, column2, ..., columnN
FROM table_name
WHERE condition;For example, if you want to retrieve the names of all employees who were born in 1980, you would use the following SQL command:
SELECT name
FROM employee
WHERE date_of_birth = '1980-01-01';AND / OR
The AND and OR operators are used to combine multiple conditions in a WHERE clause. The syntax for the AND and OR operators is as follows:
SELECT column1, column2, ..., columnN
FROM table_name
WHERE condition1 AND condition2;
SELECT column1, column2, ..., columnN
FROM table_name
WHERE condition1 OR condition2;For example, if you want to retrieve the names of all employees who were born in 1980 and have the job title “Manager”, you would use the following SQL command:
SELECT name
FROM employee
WHERE date_of_birth = '1980-01-01' AND job_title = 'Manager';IN / NOT IN
The IN and NOT IN operators are used to filter data based on a set of values. The syntax for the IN and NOT IN operators is as follows:
SELECT column1, column2, ..., columnN
FROM table_name
WHERE column_name IN (value1, value2, ..., valueN);
SELECT column1, column2, ..., columnN
FROM table_name
WHERE column_name NOT IN (value1, value2, ..., valueN);For example, if you want to retrieve the names of all employees who have the job titles “Manager” or “Developer”, you would use the following SQL command:
SELECT name
FROM employee
WHERE job_title IN ('Manager', 'Developer');LIKE
The LIKE operator is used to filter data based on a pattern. The syntax for the LIKE operator is as follows:
SELECT column1, column2, ..., columnN
FROM table_name
WHERE column_name LIKE pattern;For example, if you want to retrieve the names of all employees whose name starts with the letter “J”, you would use the following SQL command:
SELECT name
FROM employee
WHERE name LIKE 'J%';The “%” symbol is used as a wildcard, which means it can match any number of characters.
SQL functions
SQL functions are used to perform operations on data in a relational database. Some of the most common SQL functions are:
COUNT
The COUNT function is used to count the number of rows in a table. The syntax for the COUNT function is as follows:
SELECT COUNT(column_name)
FROM table_name;For example, if you want to count the number of employees in the employee table, you would use the following SQL command:
SELECT COUNT(name)
FROM employee;SUM
The SUM function is used to calculate the sum of a column’s values. The syntax for the SUM function is as follows:
SELECT SUM(column_name)
FROM table_name;For example, if you have a table with a column called “salary”, and you want to calculate the sum of all salaries in the table, you would use the following SQL command:
SELECT SUM(salary)
FROM table_name;AVG
The AVG function is used to calculate the average of a column’s values. The syntax for the AVG function is as follows:
SELECT AVG(column_name)
FROM table_name;MIN / MAX
The MIN and MAX functions are used to retrieve the minimum and maximum values in a column, respectively. The syntax for the MIN and MAX functions is as follows:
SELECT MIN(column_name)
FROM table_name;
SELECT MAX(column_name)
FROM table_name;For example, if you have a table with a column called “salary”, and you want to retrieve the minimum and maximum salaries in the table, you would use the following SQL commands:
SELECT MIN(salary)
FROM table_name;
SELECT MAX(salary)
FROM table_name;Conclusion
In this article, we’ve covered the basics of SQL, including how to create and modify tables, filter data using operators, and perform calculations using functions. With these skills, you’re now ready to start working with SQL databases and retrieving valuable insights from the data stored within them.
It’s important to note that there’s much more to SQL than what’s covered in this article. For example, we’ve only touched briefly on SQL Joins, which allow you to combine data from multiple tables into a single result set. Additionally, there are advanced SQL concepts like subqueries, stored procedures, and triggers that can greatly enhance the power and flexibility of your SQL applications.
However, even with just the basics of SQL, you can already perform powerful data analysis and reporting, and it’s a great foundation to build upon as you continue to grow your skills.
In conclusion, SQL is a fundamental tool for data analysis and management, and learning the basics can help you unlock insights from large data sets. Whether you’re working with a small database or managing a massive data warehouse, SQL is a valuable skill to have in your toolkit.
I hope that you will find this article insightful and informative. If you enjoyed it, please consider sharing the link with your friends, family, and colleagues. If you have any suggestions or feedback, please feel free to leave a comment. And if you’d like to stay updated on my future content, please consider following and subscribing using the provided link. Thank you for your support!
