Basics of SQL

INTRODUCTION:

SQL handles its data in the form of tables which are nothing but rows and columns and so it’s important to know how to manipulate the data that is stored in the database.

There are certain SQL concepts which you should be familiar with if you plan to have a good hands-on knowledge on SQL. No matter which RDBMS you use whether it is MySQL, Oracle, Microsoft SQL Server, PostgreSQL or any other, these SQL concepts are common for all of the popular RDBMS.

This tiny read is aimed at providing people who are novice but would like to start up their career that involves database handling. Today we’re going to discuss about the basics of SQL concepts like SQL Queries, joins and aggregate functions. Now, let us jump straight into the basics where you will lay your foundation strong!

Data Definition Language (DDL):

DDL statements are used to create, remove or modify database objects like table. You do not need to commit the changes after running DDL commands.

They include CREATE, DROP, ALTER and TRUNCATE statements.

• CREATE statement can be used to create any database objects like tables, views, functions, procedures, triggers etc.

• DROP statement can be used to remove any database objects like tables, views, functions, procedures, triggers etc.

• ALTER statement can be used to modify the structure of a database objects.

• TRUNCATE statement can be used to remove all the data from a table at once.

Data Manipulation Language (DML):

DML statements are used to add, remove or modify data from database tables. It is mandatory to run the COMMIT command after running a DML statement so as to save the changes to the database (some tools may have auto commit on so you don’t have to manually run the commit command).

DML includes INSERT, UPDATE, DELETE and MERGE statements.

• INSERT statement will add rows or records to a table.

• UPDATE statement will modify the data in the table.

• DELETE statement will remove one or multiple rows from a table.

• MERGE statement will either do an update or insert to a table based on the available data. If the data is present then it does an update. If data not present then merge will do an insert.

Data Control Language (DCL):

DCL includes GRANT and REVOKE statements.

• GRANT statements are used to provide access privileges to a database object to any database or schema.

• REVOKE statements are used to remove access privileges from a database object from any database or schema.

Transaction Control Language (TCL):

TCL includes COMMIT, ROLLBACK and SAVEPOINT.

• COMMIT statement will permanently save any open transactions in the current session to the database. By transaction, I mean any changes done to any database table using any of the DML statements like INSERT, UPDATE, DELETE and MERGE.

• ROLLBACK statement will remove (unsave) any open transactions in the current session to the database. So all un committed transactions in the current session will be lost.

• SAVEPOINT statement can be used to create a specific pointer in your session and provide a name to this pointer. You can then either rollback or commit transactions only until this point (savepoint name) rather than committing or rollbacking all the transaction in the session.

Data Query Language (DQL):

It includes only the SELECT statement. SELECT statement is used to fetch and view data from the database.

In a nutshell, the below picture explains the basic command structure of the query language.

DIFFERENCE BETWEEN DELETE and TRUNCATE:

DELETE can be used to remove either few or all the records from a table. Delete is a DML statement hence we will need to commit the transaction in order to save the changes to database.

Below statement will delete only the records from employee table where the name is ‘ABC’

DELETE FROM employee WHERE name = ‘ABC’;

COMMIT;

Below statement will delete all records from the employee table.

DELETE FROM employee;

COMMIT;

Whereas TRUNCATE will always remove all the records from the table. Truncate cannot have WHERE condition. Truncate is a DDL statement hence no commit is required.

Below statement will also delete all the records from the employee table. No commit is required here.

TRUNCATE TABLE employee;

DIFFERENCE BETWEEN A PRIMARY, UNIQUE AND A FOREIGN KEY:

Primary key, unique key and foreign key are CONSTRAINTS we can create on a table.

When you make a column in the table as PRIMARY KEY then this column will always have unique or distinct values. Duplicate values and NULL value will not be allowed in a primary key column. A table can only have one primary key. Primary key can be created either on one single column or a group of columns.

When you make a column in the table as UNIQUE KEY then this column will always have unique or distinct values. Duplicate values will not be allowed. However, NULL values are allowed in a column which has unique key constraint. This is the major difference between primary and unique key.

FOREIGN KEY is used to create a master child kind of relationship between two tables. When we make a column in a table as foreign key, this column will then have to be referenced from another column from some other table.

Imagine we have two tables A and B. Both have just 1 column let’s call it COLUMN_1. If we create foreign key in COLUMN_1 of table A which references the COLUMN_1 from table B then the only values COLUMN_1 in table A can have are the values which are already present in COLUMN_1 of table B.

This means table B becomes the master table and table A is the child table. COLUMN_1 of Table A can only have values which are already present in COLUMN_1 of table B.

WHAT IS A TRIGGER?

TRIGGER is a database object which is similar to a stored procedure which will automatically get invoked or executed when the specified event occurs in the database.

The most common types of triggers are DML triggers, DDL triggers and Database triggers (also referred as Logon triggers).

DML triggers are invoked when a DML operation (INSERT, UPDATE, DELETE) occurs on the respective table (table on which the trigger was created). Trigger can be configured to invoke either before the DM operation or after the DML operation.

DDL triggers are invoked when a DDL operation (CREATE, ALTER, DROP) occurs on the respective table (table on which the trigger was created).

Database trigger is invoked when the database session is established or shut down.

JOINS:

INNER JOIN will fetch only those records which are present in both the joined tables. The matching of the records is only based on the columns used for joining these two tables. INNER JOIN can also be represented as JOIN in your SELECT query.

LEFT JOIN will fetch all records from the left table (table placed on the left side during the join) even if those records are not present in right table (table placed on the right side during the join). If your select clause has a column from the right table then for records which are not present in right table (but present in left table), SQL will return a NULL value. LEFT JOIN can also be represented as LEFT OUTER JOIN in your SELECT query.

RIGHT JOIN will fetch all records from the right table (table placed on the right side during the join) even if those records are not present in left table (table placed on the left side during the join). If your select clause has a column from the left table then for records which are not present in left table (but present in right table), SQL will return a NULL value. RIGHT JOIN can also be represented as RIGHT OUTER JOIN in your SELECT query.

*Note: LEFT and RIGHT join depends on whether the table is placed on the left side of the JOIN or on the right side of the JOIN.

FULL JOIN will fetch all records from both left and right table. It’s kind of combination of INNER, LEFT and RIGHT join. Meaning FULL JOIN will fetch all the matching records in left and right table + all the records from left table (even if these records are not present in right table) + all the records from right table (even if these records are not present in left table). FULL JOIN can also be represented as FULL OUTER JOIN in your SELECT query.

SELF JOIN is when you join a table to itself. There is no keyword like SELF when doing this join. We just use the normal INNER join to do a self-join. Just that instead of doing an inner join with two different table, we inner join the same table to itself. Just that these tables should have different alias name. Other than this, SELF join performs similar to INNER join.

NATURAL JOIN is similar to INNER join but we do not need to use the ON clause during the join. Meaning in a natural join we just specify the tables. We do not specify the columns based on which this join should work. By default, when we use NATURAL JOIN, SQL will join the two tables based on the common column name in these two tables. So, when doing the natural join, both the tables need to have columns with same name and these columns should have same data type.

CROSS JOIN will join all the records from left table with all the records from right table. Meaning the cross join is not based on matching any column. Whether there is a match or not, cross join will return records which is basically number of records in left table multiplied by number of records in right table. In other words, cross join returns a Cartesian product.

AGGREGATE FUNCTIONS & TYPES:

Aggregate function can be used to perform calculation on a set of values, which will then return a single value. We can use aggregate function either with GROUP BY clause or without it. Following are the aggregate functions.

SUM:

Calculates the SUM of given values.

Below query will find the sum of all the salaries from the entire employee table:

SELECT SUM(salary) as total_salary FROM employee;

Whereas the below query will return the sum of salaries for each department in the employee table:

SELECT dept_id, SUM(salary) as total_salary_per_dept

FROM employee

GROUP BY dept_id;

AVG:

Calculates the AVERAGE from the given set of values.

Below query will find the average salary from the entire employee table:

SELECT AVG(salary) as average_salary FROM employee;

Whereas the below query will return the average salary for each department in the employee table:

SELECT dept_id, AVG(salary) as avg_salary_per_dept

FROM employee

GROUP BY dept_id;

MIN:

Find the MINIMUM VALUE in the given set of values.

Below query will find the minimum salary from the entire employee table:

SELECT MIN(salary) as min_salary FROM employee;

Whereas the below query will return the minimum salary for each department in the employee table:

SELECT dept_id, MIN(salary) as min_salary_per_dept

FROM employee

GROUP BY dept_id;

MAX:

Finds the MAXIMUM VALUE in the given set of values.

Below query will find the maximum salary from the entire employee table:

SELECT MAX(salary) as max_salary FROM employee;

Whereas the below query will return the maximum salary for each department in the employee table:

SELECT dept_id, MAX(salary) as max_salary_per_dept

FROM employee

GROUP BY dept_id;

COUNT:

Returns the NUMBER OF RECORDS or rows.

Below query will find the total number of records (or employees) from the entire employee table:

SELECT COUNT(emp_id) as no_of_emp FROM employee;

Whereas the below query will return the maximum salary for each department in the employee table:

SELECT dept_id, COUNT(emp_id) as no_of_emp_per_dept

FROM employee

GROUP BY dept_id;

Thus, we learned the basics of SQL statements that are expected to be known for a minimal level of data handling on any RDBMS. ALL THE BEST FOLKS !