Everyone around us is working with data in today’s data world. When it comes to interact with the database, there’s one tool that stands out and matters most: Structured Query Language, better known as SQL. What you are reading might be the start of your journey as a data analyst or just that serious step toward becoming a software developer or simply learning something new. The guide will step-by-step take you through SQL.
1. What is SQL?
SQL is the standard programming language for administrating and modifying a database system known as relational database management. Use SQL to retrieve and update records, add new information, or delete old data. It is also widely used within popular databases like MySQL, PostgreSQL, and SQLite.
2. Why Learn SQL?
SQL is today an important tool in data science and analytics. The following are some of the main reasons why learning SQL can be helpful:
a. Ability to manage data: SQL allows the insertion, updating, modification, or deletion of data in relational databases. The ability to do this is highly critical for efficient management of data-by-data professionals.
SQL is good for describing the structured data as it supports views, stored procedures and functions. This means that it supports organizing data in a rather efficient manner.
b. Control over the database: One of the greatest advantages of SQL is that users can create, alter or drop databases and tables, which further assists in an organized database environment.
c. Data Limitations: SQL allows setting of constraints on columns of a table, view, and stored procedures. This leads to a guarantee for data integrity by enforcing the business rules inside the database.
d. Strong Community Support: Because of the immense user community, there are abundant resources for learning and troubleshooting that facilitate better expertise.
3. Setting Up Your SQL Environment
To perform SQL queries on data in any database, you need to have a free, open-source database management system installed in your system. Some most popular and widely used DBMS are MySQL, Oracle, MongoDB, SQL Server, PostgreSQL, etc.
This is the setup you need for practicing SQL:
a. Choose a Database System: Some popular options are:
- MySQL: Open source. Most widely used. Download here
- PostgreSQL: Well-known for its features. Download here
- SQLite: Installation hardly takes any time. Download here
b. Install Your Required DBMS: Follow the installation process from the website.
c. SQL Client: MySQL Workbench, Deaver, pgAdmin- is a tool to connect to your database. Sample Database Load an example database, such as Sakila to play around with.
4. SQL queries
SQL statements are pretty easy to write and understand, and they are pretty much like plain English but with special syntax. Below are some of the most frequently used SQL commands along with what they do:
CREATE COMMAND
All new database objects in a database, including database, table or view, are created using the CREATE command. For example:
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
UPDATE COMMAND
The UPDATE command allows a user to change or update existing data in the database. For instance:
UPDATE customers SET email = 'shivan@example.com' WHERE name = 'Shivan Kumar';
DELETE COMMAND
The command that tells the program to delete data from a database, remove data from a database. Use it for deleting one or more rows.
DELETE FROM customers WHERE name = 'Shivan Kumar';
SELECT STATEMENT
This command is very important for accessing data from one or more tables in the database. You may also use this with the WHERE clause for filtering results. For example:
SELECT * FROM customers WHERE email LIKE '%example.com';
INSERT COMMAND
We can Use the INSERT command to add new records into our database.
INSERT INTO customers (name, email) VALUES ('Shivan Kumar', 'shivan@example.com');
5. SQL Syntax
Actually, to write any query in SQL you have to follow the predefined syntax. The syntax defined as ISO and ANSI standards contains a unique set of directions and guidelines. Here are some significant rules to remember:
- Case Sensitivity: SQL keywords can be written in uppercase or lowercase, but usually, they are written with uppercase because it is easier to read. For instance, the use of WHERE and SELECT becomes easier to spot when capitalized.
- Text Lines: A SQL statement can be put all on a single line, or you can break it into multiple lines. This flexibility in line breaking makes for easier formatting of your queries so that they are easier to read.
- Query Operations: Most all database operations are implemented by use of SQL queries. Knowing how to build such queries is essential for good database management.
- Foundations of SQL, for any SQL enthusiast, include a relation algebra and tuple relational calculus syntax. Understandings these aid the understanding of SQL in a greater extent.
Main Features of SQL Syntax
- Keywords are important words in the commands, including SELECT, FROM, and WHERE.
- Identifiers Names of tables, columns, and databases with which you are working.
- Operators: Symbols used within a condition, such as =, >, and <.
- Literals: Fixed values like numbers or text that you use in your queries.
6. Types of SQL Statements
SQL statements fall into several general categories:
- Data Query Language (DQL): Essentially SELECT statements.
- Data Manipulation Language (DML): INSERT, UPDATE and DELETE.
- Data Definition Language (DDL): It include CREATE, ALTER, and DROP statements, which describe the database.
- Data Control Language (DCL): Has GRANT and REVOKE statements for controlling access to the data.
7. Explain SQL Statements in Detail
SQL statements provide the database with information on what a user wants and what operations have to be performed on the data. Every statement starts with SQL keywords followed by a semicolon (;) that separates multiple statements. Here is an example of the valid SQL statement:
SELECT name, id, dept FROM employee WHERE dept = "HR";
For easier readability, the same statement would be written as:
SELECT name, id, dept FROM employee WHERE dept = "HR";
Note that SQL can by default be case-insensitive, although this depends on the OS as well. A typical case-invariant or insensitive operating system is the Windows platform. These are generally at least case-invariant. Linux platforms may not be so.
Now that we have a basic understanding of SQL, let’s take a look at the syntax and some example statements for some of the basic SQL commands:
1. SELECT Statement
A SELECT statement retrieves data from the SQL database.
Syntax of SELECT Statement:
SELECT column_name1, column_name2, ..., column_nameN FROM table_name WHERE condition;
Example of SELECT Statement:
SELECT stud_name, stud_id, grade FROM student_details WHERE grade = "A";
- This query selects the stud_name, stud_id, and grade columns of the student_details table where the condition, grade = “A”, is satisfied.
2. UPDATE Statement
The UPDATE statement can update existing data within the database.
Syntax of UPDATE Statement:
UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2, ... WHERE condition;
Example of UPDATE Statement:
UPDATE student_details SET grade = "A" WHERE stud_id = 12;
- This will update the grade of student with stud_id 12.
3. DELETE Statement
The DELETE statement deletes data from the database.
Syntax of DELETE Statement:
DELETE FROM table_name WHERE condition;
Example of DELETE Statement:
DELETE FROM student_details WHERE name = "Joe";
- This command deletes the record of the student named Joe.
4. CREATE TABLE Statement
CREATE TABLE is a SQL command that a new table creates in the SQL database.
Syntax of CREATE TABLE Statement:
CREATE TABLE table_name (
column_name1 data_type [column_constraint],
column_name2 data_type [column_constraint],
...
PRIMARY KEY (column_name)
);
Example of CREATE TABLE Statement:
CREATE TABLE student_details (
Name VARCHAR(30),
Stud_id INT(4),
Grade VARCHAR(2),
PRIMARY KEY(Stud_id)
);
- This example creates the table student_details with the specified columns and their respective data types.
5. INSERT INTO Statement
Use the INSERT Statement to add records in an existing table.
Syntax of INSERT INTO Statement:
INSERT INTO table_name (column_name1, column_name2, ...) VALUES (value1, value2, ...), (value1, value2, ...), ...;
An Example of INSERT INTO Statement:
INSERT INTO student_details (name, stud_id, grade)
VALUES ("Joe", 13, "B"), ("John", 21, "A"), ("Beck", 32, "B");
- It inserts three student records into the table student_details.
6. ALTER TABLE Statement
The ALTER TABLE statement modifies the structure of an existing table.
Syntax of ALTER TABLE Statement:
ALTER TABLE table_name ADD column_name datatype[(size)];
It actually adds another column.
ALTER TABLE table_name MODIFY column_name datatype[(size)];
It will vary an existing column.
ALTER TABLE table_name DROP COLUMN column_name;
It removes a chosen column.
Example of ALTER TABLE statement:
ALTER TABLE student_details ADD class_number INT(2);
- It introduces a column named class_number in the students_details table.
7. DROP TABLE Statement
The DROP TABLE statement drops a table and its dependent data.
Syntax of the DROP TABLE Statement:
DROP TABLE table_name1, table_name2, ...;
Example of DROP TABLE Statement:
DROP TABLE student;
- This statement removes the student table from the database.
8. CREATE DATABASE Statement
CREATE DATABASE Statement: It creates a new database.
Syntax for CREATE DATABASE Statement
CREATE DATABASE database_name;
Example of CREATE DATABASE Statement:
CREATE DATABASE Class;
- It creates a database named Class.
9. USE Statement
It selects the database for executing operations.
Syntax of USE statement:
USE database_name;
Example of USE Statement:
USE Class;
- This command sets Class as the default database for further operations.
10. DROP DATABASE Statement
The DROP DATABASE statement removes an existing database as well as its tables.
Syntax of DROP DATABASE Statement:
DROP DATABASE database_name;
Example of DROP DATABASE Statement:
DROP DATABASE Class;
- This command deletes the Class database.
11. DESCRIBE STATEMENT
The DESCRIBE statement returns information on a defined table or view.
Syntax of DESCRIBE Statement:
DESCRIBE table_name | view_name;
Example of DESCRIBE STATEMENT:
DESCRIBE student_details;
- It returns the details of the table student_details.
12. DISTINCT Clause
DISTINCT Clause Evaluates unique values from defined columns.
Syntax of DISTINCT Clause:
SELECT DISTINCT column_name1, column_name2, ... FROM table_name;
Example of DISTINCT Clause:
SELECT DISTINCT grade FROM student_details;
- The following query will return different grade values from the student_details table.
13. TRUNCATE TABLE Statement
The TRUNCATE TABLE statement removes all data from a table.
Syntax for TRUNCATE TABLE Statement:
TRUNCATE TABLE table_name;
Example of the TRUNCATE TABLE Statement:
TRUNCATE TABLE student_details;
- This statement erases all the records in the table student_details.
14. COMMIT STATEMENT
The COMMIT statement makes the database change permanent.
Syntax of COMMIT Statement:
COMMIT;
Example of COMMIT Statement:
DELETE FROM student_details WHERE fee = 5000; COMMIT;
- This delete will delete records where the fee is 5000, and this change will become permanent.
15. ROLLBACK Statement
The ROLLBACK statement rolls back unsaved transactions.
Syntax for ROLLBACK Statement:
ROLLBACK;
Example of ROLLBACK Statement:
DELETE FROM student_details WHERE grade = "N"; ROLLBACK;
- This command deletes records where the grade is “N” and then undoes that change.
16. CREATE INDEX
Statement The CREATE INDEX statement creates an index on a table.
Syntax of CREATE INDEX Statement:
CREATE INDEX index_name ON table_name (column_name1, column_name2, ...);
Example of CREATE INDEX Statement:
CREATE INDEX idx_name ON student_details (name);
- This creates an index named idx_name on the name column of table student_details.
17. DROP INDEX
Statement The DROP INDEX statement removes an existing index.
Syntax of DROP INDEX Statement:
DROP INDEX index_name;
Example of DROP INDEX Statement:
DROP INDEX idx_name;
- This statement removes the idx_name index from the database.
18. ORDER BY Clause
The ORDER BY clause sorts records on specified columns.
Syntax of ORDER BY Clause:
SELECT column_name1, ..., column_nameN FROM table_name ORDER BY column_name (ASC | DESC);
- This clause is used to order the data in ascending or descending order.
8. Let’s have a short glance at what SQL keys are:
In a database, especially when working with big tables having thousands of rows, you usually find duplicate data and redundancy. This is where the SQL keys come in handy. They deal with unique data management and establishing relationships with more than one table. Here’s a general overview of the types of keys in SQL:
a. Primary Key
The column(s) or columns that uniquely identify each row of a table are called a primary key. It can never allow any null values so that each record will get a unique identification. You can create it while creating or modifying the table with help of PRIMARY KEY constraint. If more than one column, along with each other, satisfy the uniqueness condition, then that is called composite primary key.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
b. Foreign Key
A foreign key refers to a link between two tables. It is a column or columns that may refer to the primary key of another table, thus creating a relationship between the two. That is, it maintains referential integrity by having its value corresponding to an existing record in the linked table.
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
c. Unique Key
A unique key also uniquely identifies each row in a table, kinda like the primary key. Only, this type of column is allowed to contain null values and can only have one null value per column, so while data does have to be unique, each row doesn’t necessarily have to have a value in it.
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
d. Alternate Key
Alternate key is a secondary key that may eventually become a primary key but isn’t selected as a primary key. Therefore, it also serves as a backup or alternative to uniquely identify records in a table.
Example: Take the example of the above users table where the email column isn’t taken to be the primary key; it can be an alternate key.
9. Advantages of SQL
- Fast Speed: Processes huge amounts of data speedily and efficiently .
- No programming knowledge: Easy access and manipulation of data without deep coding.
- Portable: Works on Laptops, Tablets, Desktops, and Smartphones; adaptable to different applications.
- Interactive: Friendly and easy to learn; handles complex queries in a few seconds.
- Standardized: Follows ISO and ANSI standards for consistency worldwide.
- View Multiple Data: Allows different users access to multiple views of the database structure.
10. Disadvantages of SQL
- Limited Database Control: The user has no complete control over the database; some restrictions are hidden.
- Complex Interface: It may become highly complex for some users to handle it.
- Cost: Some versions of SQL come with a high cost due to advanced features. It is, therefore, out of range for most programmers.
11. Conclusion
SQL is a robust tool in the present data world. In such an environment, knowing its commands and syntax will help you to manage the data very efficiently and open up opportunities for a variety of careers. Want to become a data analyst or a database administrator? Enhance your technical skill? SQL is a valuable language. All you need to do now is get started, set up your environment, and start practicing on how to write queries so you can make maximum use of your data!
