You might be wondering what is SQL. Let's start with explaining what it stands for. SQL stands for Structure Query Language and it is a language that allows you to communicate with databases. In previous lesson we explained what is a database and we explained that you store data in tables that consist of columns, rows and data. You can have multiple tables related to each other so you need a way to retrieve data (from one or multiple related tables), insert, update or delete data in tables.
In order to do that you need language that your Database Management Software (DBMS) understand and can convert into actions. This language is SQL. To better understand what is SQL let's give you several simple examples of actions you might want to perform on your database.
Let's say you have been given a database and you have been given several tasks:
1) Show me all customers from our database
2) Insert new customers
3) Update customer information
4) Delete customers that requested to be permanently removed from your database
SQL is a very simple and easy to understand language as it is very similar to questions you need to answer or actions you need to perform so let me give you SQL example for the first 4 points:
1)
SELECT FirstName, Surname, DOB
FROM Customer
-- as you can it to retrieve field from a particular table we just need to use SELECT and provide fields names (separated using comma) and extract data FROM a table which is our case is customer so we added FROM Customer
2)
INSERT INTO Customer (FirstName, Surname, DOB)
VALUES ('Emil', 'Glownia','1983-08-19')
NOTE: single ' around values is needed to tell SQL that this is a string value as opposed to table name, field name, special keyword and so on. single quotes are also used for dates but are not used for numbers as number is not a string value.
-- second point asked us to insert new customer. This is simple with SQL you just type INSERT INTO Customer and specify field names and then put VALUES and provide the actual values. Strings are surrounded using single quotes. Isn't that simple?
3)
UPDATE Customer
SET Surname = 'Glownia'
WHERE FirstName = 'Katie' AND Surname = 'Sliwinska' AND DOB = '1986-08-20'
Note: We use CAPITAL letters for UPDATE, SET and AND operator. This is considered by many as best practice as it easier to read it and distinguish between tables names (Proper case) and user values.
-- third point is to update customer details. Katie got married and changed her surname to Glownia. In order to update the record using SQL we just need to type UPDATE Customer then use word SET provide field name we want to update (Surname) and provide value (='Glownia') next we need to identify unique customer record and we do that using WHERE and we specify firstname, surname and DOB.
4)
DELETE FROM Customer
WHERE FirstName = 'John' AND Surname = 'Smith' and DOB = '1980-01-01'
-- John doesn't want to have his records in our database and requested to be permanently removed. We do that using DELETE FROM Customer and specifying WHERE condition that identifies John's record.
NOTE: In real life you would normally use Primary Key (PK) or Foreign Key (FK) to identify unique records which we discussed in 'what is a database' page.
I explained briefly what is SQL and provided you with several simple examples that hopefully give you good understand of the concept.
If you have any question feel free to add comment.
Take care
Emil
