Basic SQL Concepts - Part 2

Introduction

So, we have covered some of the SQL basics in Part 1, let's continue from where we stopped and talk about different types of SQL first.

SQL subsections

Based on what the SQL commands do, they are divided into different subsections as below:

DDL (Data Definition Language): DDL is used to define the database, schema and schema objects.

Example: CREATE, DROP, ALTER, TRUNCATE etc.

DML (Data Manipulation Language): DML is used to modify or manipulate the data present in the database.

Example: INSERT, UPDATE, DELETE etc.

DQL (Data Query Language): DQL is used to retrieve data from the database.

Example: SELECT.

DCL (Data Control Language): DCL is used for permissions and privileges.

Example: GRANT, REVOKE etc.

ORDER BY

ORDER BY is used to order/sort the records in the retrieved result set. To sort the records in ascending order we can use ASC, and to sort in descending order we can use DESC. If we don't write ASC or DESC, the default sort order is ascending.

SELECT column1, column2, column3
FROM mytable
ORDER BY column1 DESC;

--Relative position of the column from result set works too
SELECT column1, column2, column3
FROM mytable
ORDER BY 1 DESC;

/* we can also use multiple columns in ORDER BY. 
Say order by first column but if there are duplicates, order by
second column, along with ASC/DESC */
SELECT first_name, last_name
FROM mytable
ORDER BY first_name ASC, last_name DESC;

GROUP BY AND HAVING

GROUP BY is used to group the rows in a result set by one or more columns. It is generally used with aggregate functions like AVG, SUM, COUNT, MAX, and MIN to group the rows.

Example: Let's say we have an employees' table and we want to see the total number of employees in a department. This is how we do it:

SELECT department, count(employee_id)  as total_emp
FROM employees
GROUP BY department;

Now, let's say you only want to get the departments where total number of employees is more than 50. You can not use the WHERE clause here to filter those rows because first, we need the department-wise count, only then we can filter departments with more than 50 employees.

We have to use the HAVING clause in such situations. In other words, WHERE does not work with aggregate functions.

SELECT department, count(employee_id) as total_emp
FROM employees
GROUP BY department
HAVING COUNT(*) > 50;

CONSTRAINTS

When we create a database, we want to have some control over what kind of data goes into it, so we have to add some restrictions to it. For example, in the school database, for a student table, we want to say that the first name of the student column must have a value. It can not be blank. We can create a constraint on the first name column saying it can not be null. We can apply constraints while creating tables or later alter them and apply them. Below are some frequently used constraints:

NOT NULL: If we apply the NOT NULL constraint to a table, we can not store NULL values in that column.

CREATE TABLE employees
(emp_id number NOT NULL,
name varchar(20) NOT NULL,
manager varchar(20));

UNIQUE: UNIQUE is to ensure that there are no duplicate values stored for a column. The difference between UNIQUE and NOT NULL is that UNIQUE allows NULL values.

CREATE TABLE employees
(emp_id number NOT NULL UNIQUE,
name varchar(20) NOT NULL,
manager varchar(20));

DEFAULT: We can use DEFAULT to provide a default value for a column in case there are no values entered for that column.

CREATE TABLE employees
(emp_id number NOT NULL UNIQUE,
name varchar(20) NOT NULL,
manager varchar(20) DEFAULT 'To be assigned');

CHECK: We can use CHECK to ensure that the values for a column meet particular conditions specified with the CHECK constraint.

CREATE TABLE employees
(emp_id number NOT NULL UNIQUE,
name varchar(20) NOT NULL,
age number NOT NULL CHECK(age >=18));

PRIMARY KEY: PRIMARY KEY is used to uniquely identify a record in a table. It can not be NULL.

CREATE TABLE employees
(emp_id number NOT NULL PRIMARY KEY,
name varchar(20) NOT NULL,
manager varchar(20));

--Or you can write it this way
CREATE TABLE employees
(emp_id number NOT NULL,
name varchar(20) NOT NULL,
manager varchar(20),
CONSTRAINT pk_emp_id PRIMARY KEY (emp_id)
);

FOREIGN KEY: FOREIGN KEY is useful to uniquely identify a record in a different table and to create a relationship with the other table.

CREATE TABLE departments
(dept_id number NOT NULL,
dept_name varchar(20) NOT NULL,
emp_id number,
CONSTRAINT pk_dept_id PRIMARY KEY (dept_id),
CONSTRAINT fk_emp FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

Conclusion

That's all for this article! Please remember that these are just the basics of SQL, if you want me to write about any of the topics from this article in-depth, please let me know.

Questions and feedback are most welcome :)