3 minute read
SQL Basics for Kris
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
- 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...
DISTINCT - if you want only unique values as a query result
LIMIT - take only x records from query
- 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` ...
- NSERT INTO statement is used to insert new rows into a table.
INSERT INTO table_name (column1, column2, column3...) VALUES (value1, value2, value3...);
- UPDATE: The UPDATE statement is used to modify existing data in a table.
SET column1 = value1, column2 = value2
- 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