2022 – Introduction to SQL

What’s SQL?

SQL stands for “Structured Question Language”. A language created in 1974 for expressing queries to relational databases.

SQL is not like normal objective programming languages that are usually interpreted or compiled into machine language for execution. SQL as an alternative is a declarative abstraction over the APIs of various information sources.

For instance if I create a desk with the next SQL Assertion.

CREATE TABLE cheese (identify VARCHAR(50), origin VARCHAR(50));

We all know that this creates a desk, however how that is achieved will probably be completely different based mostly on the precise database that is despatched to. This abstraction permits us to precise widespread entry patterns to completely different databases with out having to understand how every of them work beneath the hood.

The place can I exploit SQL?

SQL can after all be utilized in a wide range of relational databases equivalent to:

  • PostgreSQL
  • Microsoft SQL
  • mySQL
  • SQLite3
  • MariaDB
  • Many Many extra

However past these transactional databases, SQL can be utilized in Information Warehouses, Lakehouse Question Engines, and in lots of different contexts as SQL continues to develop its use circumstances significantly within the Information Analytics world.

Let’s Take a Spin!

We’ll be created an SQLite3 REPL at Replit.com for us try to follow SQL. Fork this REPL right here.

Create Fork of this REPL to follow the exercises in this tutorial

After forking, be sure you are on the “shell” tab not the “console” tab as we are going to use this as it will be from the terminal.

SQLite3 is uniqe in that as an alternative of getting one giant database server that manages all of your databases like PostgresQL and MySQL, SQLite3 as an alternative saves every database as a file. This makes SQLite3 an amazing candidate for studying SQL, working in a develop setting and different use circumstances (Typically, in manufacturing utility you may go for a Database server like PostgreSQL or MySQL).

To open a database with SQLite3 the command is:

sqlite3 <database identify>

We’ll open the study.sql database that ought to already be within the REPL.

sqlite3 study.sql

It is best to be aware the shell immediate has modified to sqlite> this confirms that we are actually in sqlite.

Information in a relational database is organized into tables which seem like a spreadsheet you might have seen in Excel or Google Sheets. In these tables, every column is a subject representing a chunk of data (identify, age). Every row in a desk is a document that defines a price for every subject (Bob is 24).

Making a Desk

After we create a desk we have to outline all fields and the kind of information they are going to maintain, each database might have completely different information sorts. Here is the record of sorts in SQLite3.

Let’s create a easy desk to trace canines.

CREATE TABLE canines (identify TEXT, age INT);

Couple of issues to note:

  • Sure phrases are in all caps, this isn’t required however widespread follow to tell apart SQL key phrases from consumer outlined values.
  • All SQL statements MUST finish in a semi-colon and if it is forgotten will look forward to a semi-colon to be given.
  • Within the parenthesis we’re defining the completely different columns within the canines desk.

Every database has their very own instructions that transcend SQL. If I needed to record all of the tables at the moment on this database I might enter the next command.

.tables

It is best to see the canines desk we simply created and a pre-existing textual content desk. Though if this have been postgres we’d use the command dt to show all tables. So attempt to be cognisant that completely different tables could have differnt command outdoors of the SQL language (even some variations of their SQL as effectively, normally minor).

Inserting Information

So as to add information to that desk we are going to use INSERT INTO statements like so:

INSERT INTO canines (identify, age) VALUES ('Spot', 6), ('Biff', 8);

Issues to note:

  • The parenthesis after the desk identify is used to specify which fields you may present information for.
  • Every parenthesis after the VALUES key phrase represents one document to be added with values within the order specified earlier within the assertion.
  • Textual content values should be surrounded by single quotes not double quotes.

Querying Information

To fetch information from the database we use SELECT statements.

We are able to get all of the information displaying all columns with this question:

SELECT * FROM canines;

We are able to get all information however solely the identify column with this question:

SELECT identify FROM canines;

You’ll be able to choose a selected document like so:

SELECT * FROM canines WHERE identify="Spot";

Studying Extra

This offers you a small style of SQL and how one can add retrieve information from a database utilizing it.

To study extra watch this video playlist the place I am going deeper into SQL.

Video Playlist

Then checkout SQLZoo to proceed practices and studying extra key phrases:

SQLZoo

Add a Comment

Your email address will not be published. Required fields are marked *