Reading time : 2 min

Last update: February 11th, 2022

Introduction

Data manipulation is done through access, modify, insert and delete of the content. There are 4 basic commands that allow performing these operations.

Half off everything 2manning

SELECT

The SELECT allows you to retrieve data from a database; it is a simple command. You can use the * star to select all the content or specify the column(s) you are interested in. So you can select several columns and several tables at the same time, separated by a comma. To retrieve the table name, we use FROM at the end of the query.

Market

The basket corresponds to the table, the fruits correspond to the columns. So we see a hand retrieving one fruit, several fruits, corresponding to the specific SELECT. We get all the fruits in the last comic strip, which corresponds to the global SELECT.

Code

pgsqlpostgreSQL
1-- SELECT column
2-- FROM table;
3
4SELECT *
5FROM humanresources.employee;
6
7SELECT jobtitle
8FROM humanresources.employee;
9
10SELECT jobtitle, gender
11FROM humanresources.employee;

Data

result :
businessentityidnationalidnumberloginidbirthdate...
11295847284adventure-works\ken01969-01-29...
22245797967adventure-works\terri01971-08-01...
..................

DISTINCT

By default duplicates are returned with ALL. If you want to delete them you have to use DISTINCT.

In this comic, there are fruits that are duplicates. In the second panel of the comic strip, we can get all the duplicates, this corresponds to the default selection, without the DISTINCT, but otherwise, we can choose a particular fruit by selecting its duplicate, which corresponds to the use of the DISTINCT.

Code

pgsqlpostgreSQL
1SELECT DISTINCT jobtitle, gender
2FROM humanresources.employee;

Data

result :
jobtitlegender...
1Production Technician - WC60M...
2RecruiterM...
............

Sorting

It is possible to retrieve the sorted data with ORDER BY. The cause must be placed at the end of the query (after the FROM) and is used only once. It can be accompanied by ASC (smallest to largest) or DESC (largest to smallest). However, it is recommended that you always use it with WHERE as it requires a lot of performance.

In this comic book, the goal is to collect the fruits precisely, by price, and therefore from the cheapest to the most expensive. The fruits of the last column are recovered, the banana first which is at 2 dollars then the strawberry which is at 3 dollars.

Code

pgsqlpostgreSQL
1SELECT DISTINCT jobtitle 
2FROM humanresources.employee ORDER BY jobtitle;
3
4SELECT unitprice 
5FROM purchasing.purchaseorderdetail
6ORDER BY unitprice DESC;
7

Data

result :
jobtitle...
1Accountant...
2Accounts Manager...
.........

LIMIT

You can limit the number of entries with LIMIT. For example, return only the first 10 elements of the result. You can shift the result with OFFSET and sort with DESC to get the last rows.

Here the goal is to get fruits, but with a limited number, here are the fruits of the first line. So we limit to 1, line one, then specify the fruits, for example, the banana or the strawberry.

Code

pgsqlpostgreSQL
1SELECT jobtitle
2FROM humanresources.employee LIMIT 2;
3
4SELECT jobtitle
5FROM humanresources.employee LIMIT 10 OFFSET 5;

Data

result :
jobtitle...
1Chief Executive Officer...
2Vice President of Engineering...

Alias

The alias is useful when performing a query with several tables. It improves readability and creates consistency in the query.

Code

pgsqlpostgreSQL
1SELECT jobtitle
2FROM humanresources.employee AS HR;

WHERE

You can decide to select only a part of the rows thanks to WHERE. This limits a query that could be too costly in terms of performance.

Here we specify the characteristic which interests us, for example, the color of the fruit, corresponding to the WHERE, i.e., we recover a banana whose (« where ») color is green.

Code

pgsqlpostgreSQL
1SELECT jobtitle 
2FROM humanresources.employee where maritalstatus = 'S';

Data

result :
jobtitlemaritalstatus...
1Chief Executive OfficerS...
2Vice President of EngineeringS...
............
Print booksmanning

Conclusion

Data manipulation in SQL is done using SELECT, DISTINCT, INSERT, LIMIT, ORDER BY, WHERE. The simplest order is SELECT. You can combine several operations to refine your query and optimize performance, especially with WHERE.

Resources

Database

Resources

Source code