back to Databases

3 minute read

SQL Basics for Kris

Michał Będkowski

30/04/2023 10:16

Some time ago my friend Kris  asked me for help in extracting data from databases in his company. They operate on complex software that uses multiple mssql databases. One day there was an urgent need to pull out a simple list and you know what? It turned out that this very expensive software does not allow to perform such an operation. At this point, the knowledge of several SQL commands that export data directly from databases came in handy. In this post, I would like to include basic information about SQL, along with examples that will help Kris and his employees solve similar problems in the future.

What is SQL

SQL stands for Structured Query Language, and it is a script language designed for managing and manipulating relational databases. It is used to create, modify, and manage databases, as well as to query, retrieve, and manipulate data stored in them.

SQL is a standard language that is widely used in the industry and supported by most relational database management systems (RDBMS). It is used to perform a wide range of tasks such as creating tables and views, inserting, updating and deleting data, and retrieving data using queries.

SQL is a powerful tool for data analysis and management, making it a critical skill for anyone working with data.

Basic SQL methods, SELECT / INSERT / UPDATE / DELETE

  1. The SELECT statement is used to retrieve data from one or more tables. In square brackets I put optional commands.

SELECT [DISTINCT] column1, column2, column3... 
FROM table_name 
[WHERE condition [AND condition OR condition]]
[ORDER BY column1 ASC/DESC] 
[LIMIT 10];

 

DISTINCT - if you want only unique values as a query result

LIMIT - take only x records from query

  1. The JOIN  statement is used to retrieve data from multiple tables. We’re joining here records from table_x with table_y using id and user_id columns.

SELECT [DISTINCT] table_x.column1, table_x.column2, table_y.column1 AS `Name` ... 
FROM table_x 
JOIN table_y ON table_x.id=table_y.user_id
[WHERE condition [AND condition / OR condition ...]]
[ORDER BY table_x.column1 ASC/DESC] 
[LIMIT 10];

  1. NSERT INTO statement is used to insert new rows into a table.

INSERT INTO table_name (column1, column2, column3...) VALUES (value1, value2, value3...);

 

  1. UPDATE: The UPDATE statement is used to modify existing data in a table.

UPDATE table_name 

SET column1 = value1, column2 = value2 

WHERE condition;

 

  1. DELETE: The DELETE statement is used to delete rows from a table.

DELETE FROM table_name WHERE condition;

 

Practice a bit

Use website https://www.sql-practice.com/ to practice those basic queries. Learn about db schema you’ll be playing with by using View Schema button on the left. 

Example queries that should work with schema from 25.03.2023:

 

SELECT DISTINCT patients.patient_id, patients.first_name, patients.last_name,patients.gender, patients.birth_date, province_names.province_name as province
FROM patients
INNER JOIN province_names ON patients.province_id=province_names.province_id
WHERE patients.birth_date between '1988-01-01' AND '2000-12-31'
ORDER BY patients.birth_date DESC
LIMIT 100;