Introduction to SQL
What is SQL?
SQL stands for Structured Query Language, which is a standardized language for interacting with RDBMS (Relational Database Management System). Some of the popular relational database examples are MySQL, Oracle, MariaDB, PostgreSQL, etc.
SQL is used to perform C.R.U.D (Create, Retrieve, Update & Delete) operations on relational databases. It can also perform administrative tasks on the database such as database security, backup, user management, etc. We can create databases and tables inside a database using SQL.
SQL is basically a combination of four different languages, they are –
DQL (Data Query Language)
DQL is used to fetch the information from the database which is already stored there.
DDL (Data Definition Language)
DDL is used to define table schemas.
DCL (Data Control Language)
DCL is used for user & permission management. It controls the access to the database.
DML (Data Manipulation Language)
DML is used for inserting, updating, and deleting data from the database.
More advanced SQL queries
A Query is a set of instructions given to the database management system, which tells RDBMS what information you would like to get from the database or what operation you want to perform on the data.
- Alias
Alias is a name that you can reference in either a column of a table or the table itself. It also accounts for better readability. You can reference an alias by using as or add a space and creating a variable. Let’s look at some examples.
SELECT age, ROUND(avg(grade), 2) as average
FROM students
GROUP BY age
By doing this, you set the column name to be average, replacing round with average, which represents the column more appropriately.
SELECT s1.age, s1.grade
FROM students s1
Here, you can see that we set up s1 as an alias using a space in between students and s1. The students table has been set up as a variable s1 and we can reference the column using s1.<column name> -> s1.age, s1.grade etc.
- LIKE Operator
The LIKE Operator is commonly used in the WHERE Clause, where it will match a pattern given in a given column with wildcards in the query.
Wildcards include: % and _
SELECT first_name, last_name, age, grade
FROM students
WHERE first_name like 'M%'
This query will select all rows from the student's table where the first name begins with the letter ‘M’.
- JOIN Queries
A JOIN Query is used when we have to query a column from a second table. Normally there is a key from the first table that equates to a key in the second table. It can be in the form of an ID or string.
- INNER JOIN
Inner Join is joining one or more tables and selecting the rows as long as there is a match between both tables. It is like an INTERSECT.
- LEFT JOIN
LEFT JOIN selects all records from the left table and records that match the right table.
- RIGHT JOIN
RIGHT JOIN selects all records from the right table and records that match the left table.
- FULL OUTER JOIN
FULL OUTER JOIN selects all records for both the left and right tables.
CASE Statement
The CASE statement goes through a condition. If the condition is met, it will return the result specified and move on to the next query. It has the characteristic of an IF-ELSE statement.
SUBQUERIES
A Subquery is a query within a query. It is used to return data for use in the main query.
Relational queries in SQL
A question asked about data contained in two or more tables in a relational database. The relational query must specify the tables required and what the condition is that links them; for example, matching account numbers. Relational queries are tricky to specify because even the simplest of questions may require data from two or more tables. Both the knowledge of the query language and the database structure is necessary. Even with graphical interfaces that let you drag a line from one field to another, you still need to know how the tables were designed to be related. The join queries come under the relational queries.
Modifying databases with SQL
The modifying queries in SQL are:
- Use CREATE and DROP to create and delete tables.
- Use INSERT to add data.
- Use UPDATE to modify existing data.
- Use DELETE to remove data.
- It is simpler and safer to modify data when every record has a unique primary key.
- Do not create dangling references by deleting records that other records refer to.
We can further learn about databases and how to interact with it as databases are much important thing.