In this tutorial we will learn the basics of writing SQL. But first lets explain some stuff:
Databases are structured sets of data held in a computer, especially one accessible to users.
Databases have tables where information is held.
Tables are often defined, when creating, with a first entry called "ID" which has a type of serial
and another atribute PRIMARY KEY, allowing it to be linked with another table.
DBMS- Database management system is a program that serves user data from the database. It helps
the creator of the database to easier understand his database and it captures data for further analysis.
examples of DBMS:
>mysql
>oracle
>postgreSQL (self proclaimed ORDBMS)
>SQL lite (the lite version)
>mongoDB (document oriented)
>...
RDBMS- relational database management system
You can write SQL in any text editor, and execute it in your DBMS program.
First we start with removing a table if exist called "users".
-> DROP TABLE IF EXISTS users;
Then we create a table users:
-> CREATE TABLE users(
-> id serial PRIMARY KEY,
-> username varchar(20) NOT NULL UNIQUE,
-> password text NOT NULL,
-> postalcode numeric,
-> city text
);
-So here we have an example of primary key, username with varchar( it defines the max amount of
letters that can be used for username in this case, which is 20 here) and it adds NOT NULL atribute which
does not allow this field to be empty, its a must-fill out. Then we have UNIQUE which means this username
cannot be the same as someone else's.
-After that we have the password in text format instead of varchar, we will allow password to be as long
as the user wants and it can include all of the symbols
-Postalcode and city in numeric and text. Then we closed the command with );
Now let's insert some data and let me show you selects:
-> INSERT INTO users(username,password) VALUES ("JohnTail","unicorn32")
-This inserts into field username "JohnTail" and password "unicorn32".
-We can also add more data
-> INSERT INTO users(username,password,postalcode,city) VALUES ("JohnTail","unicorn32",32000,"London")
-Great, now let me show you SELECTS
-the use of selects is just displaying data from our database, for example:
-> SELECT * FROM users; --meaning select all data you can find from table "users"
-> SELECT * FROM users WHERE id=1; --same thing but id=1 meaning first entry, our JohnTail
-> SELECT users.username FROM users WHERE users.username ILIKE "%John%"
-What this does is it selects just username column from users, and it finds
-what username column has anything like "john", specifically:
-ILIKE or LIKE mean exactly what they say, only difference is ILIKE ignores if its capital letters or not
-Then we have % sign, which means, ignore everything before, and ignore everything later:
%john - ignore everything before john
john% - ignore everything after john
-> SELECT * FROM users WHERE users.city ILIKE "London";
-and this just selects london
Now let's make another table.
-> CREATE TABLE products(
-> id serial PRIMARY KEY,
-> product_type varchar(20) NOT NULL UNIQUE,
-> movie_title text,
-> price integer CHECK(price>0)
);
Great so right here we have a great example of a condition, if price is less then 0$ we have invalid
data in our table, so we put price>0 meaning price has to be more then 0$.
Now let's insert some data:
-> INSERT INTO products (product_type,movie_title,price) VALUES ("movie","Fast and furious 16",23)
This just inserts data like we did before.
I'd also like to show you references , lets take the same table and just change it out a bit:
-> CREATE TABLE products(
-> id serial PRIMARY KEY,
-> product_type varchar(20) NOT NULL UNIQUE,
-> movie_title text,
-> buyer_id integer REFERENCES user(id) ON DELETE CASCADE,
-> price integer CHECK(price>0)
);
So what we have here is a link between two keys, buyer id is a number that references our
user id from the first table with this command, also we added ON DELETE CASCADE because
if we try to delete buyer_ids the program will prevent us from doing so, so we told him to
cascade instead of restrict.
Also we have a great example of joins:
SELECT column_name
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
-Here we are selecting a column name from table one (left table) and combining it with
table two using LEFT JOIN - left join joins all the data from left table to right one (table2) and the
data that is common to the both in this case. If we right joined them it would do the same but
for table2 instead of table1, now if you don't want to include the common things and just
want to combine tables without them, you have to do some replacements, this is too advanced
for this tutorial so you will have to google it.
Crows foot notation- is a system of displaying table relationships with "crow's foot" looking system
for example table one: and table two:
TEACHER_table || ------------0E ProgrammingClass_table
Since i cannot textually draw, let's act that this E is just three lines connecting to
the table 2
now lets look at that connection:
| ------------0 -means there can be one teacher and none classes ,in other words,
if there is a class there HAS to be a teacher
|------------E - this means there can be one teacher and more classes
Linked together it represents the real corelation between two, these are a bit hard to conclude
but when you get used to it it's easy, because they are like real life.
Okay that would be all, thank you so so much for reading, or watching, i really hope you
have a nice day :) .