STRZ1Dv4

1Basics of SQL | A little bit of everything tutorials || HOX FRAMEWORK
2In this tutorial we will learn the basics of writing SQL. But first lets explain some stuff:
3
4Databases are structured sets of data held in a computer, especially one accessible to users.
5
6Databases have tables where information is held. 
7
8Tables are often defined, when creating, with a first entry called "ID" which has a type of serial
9
10and another atribute PRIMARY KEY, allowing it to be linked with another table.
11
12DBMS- Database management system is a program that serves user data from the database. It helps
13
14the creator of the database to easier understand his database and it captures data for further analysis.
15
16examples of DBMS:
17
18>mysql
19
20>oracle
21
22>postgreSQL (self proclaimed ORDBMS)
23
24>SQL lite (the lite version)
25
26>mongoDB (document oriented)
27
28>...
29
30RDBMS- relational database management system
31
32You can write SQL in any text editor, and execute it in your DBMS program.
33
34First we start with removing a table if exist called "users".
35
36-> DROP TABLE IF EXISTS users;
37
38Then we create a table users:
39
40-> CREATE TABLE users(
41
42-> 	id serial PRIMARY KEY,
43
44->	 username varchar(20) NOT NULL UNIQUE,
45
46-> 	password text NOT NULL,
47
48-> 	postalcode numeric,
49
50-> 	city text
51
52);
53
54-So here we have an example of primary key, username with varchar( it defines the max amount of
55
56letters that can be used for username in this case, which is 20 here) and it adds NOT NULL atribute which
57
58does not allow this field to be empty, its a must-fill out. Then we have UNIQUE which means this username
59
60cannot be the same as someone else's.
61
62-After that we have the password in text format instead of varchar, we will allow password to be as long
63
64as the user wants and it can include all of the symbols
65
66-Postalcode and city in numeric and text. Then we closed the command with );
67
68Now let's insert some data and let me show you selects:
69
70-> INSERT INTO users(username,password) VALUES ("JohnTail","unicorn32")
71
72-This inserts into field username "JohnTail" and password "unicorn32".
73
74-We can also add more data 
75
76-> INSERT INTO users(username,password,postalcode,city) VALUES ("JohnTail","unicorn32",32000,"London")
77
78-Great, now let me show you SELECTS
79
80-the use of selects is just displaying data from our database, for example:
81
82-> SELECT * FROM users; --meaning select all data you can find from table "users"
83
84-> SELECT * FROM users WHERE id=1; --same thing but id=1 meaning first entry, our JohnTail
85
86-> SELECT users.username FROM users WHERE users.username ILIKE "%John%"
87
88-What this does is it selects just username column from users, and it finds
89
90-what username column has anything like "john", specifically:
91
92-ILIKE  or LIKE mean exactly what they say, only difference is ILIKE ignores if its capital letters or not
93
94-Then we have % sign, which means, ignore everything before, and ignore everything later:
95
96%john - ignore everything before john
97
98john% - ignore everything after john
99
100-> SELECT * FROM users WHERE users.city ILIKE "London";
101
102-and this just selects london
103
104Now let's make another table.
105
106-> CREATE TABLE products(
107
108-> 	id serial PRIMARY KEY,
109
110-> 	product_type varchar(20) NOT NULL UNIQUE,
111
112-> 	movie_title text,
113
114-> 	price integer CHECK(price>0)
115
116);
117
118Great so right here we have a great example of a condition, if price is less then 0$ we have invalid
119
120data in our table, so we put price>0 meaning price has to be more then 0$.
121
122Now let's insert some data:
123
124-> INSERT INTO products (product_type,movie_title,price) VALUES ("movie","Fast and furious 16",23)
125
126This just inserts data like we did before.
127
128I'd also like to show you references , lets take the same table and just change it out a bit:
129
130-> CREATE TABLE products(
131
132-> 	id serial PRIMARY KEY,
133
134-> 	product_type varchar(20) NOT NULL UNIQUE,
135
136-> 	movie_title text,
137
138-> 	buyer_id integer REFERENCES user(id) ON DELETE CASCADE,
139
140-> 	price integer CHECK(price>0)
141
142);
143
144So what we have here is a link between two keys, buyer id is a number that references our
145
146user id from the first table with this command, also we added ON DELETE CASCADE because
147
148if we try to delete buyer_ids the program will prevent us from doing so, so we told him to
149
150cascade instead of restrict.
151
152Also we have a great example of joins:
153
154SELECT column_name
155
156FROM table1
157
158LEFT JOIN table2
159
160ON table1.column_name = table2.column_name;
161
162-Here we are selecting a column name from table one (left table) and combining it with
163
164table two using LEFT JOIN - left join joins all the data from left table to right one (table2) and the
165
166data that is common to the both in this case. If we right joined them it would do the same but
167
168for table2 instead of table1, now if you don't want to include the common things and just 
169
170want to combine tables without them, you have to do some replacements, this is too advanced
171
172for this tutorial so you will have to google it.
173
174Crows foot notation- is  a system of displaying table relationships with "crow's foot" looking system
175
176for example table one: 	and table two:
177
178TEACHER_table || ------------0E ProgrammingClass_table
179
180Since i cannot textually draw, let's act that this E is just three lines connecting to
181
182the table 2
183
184now lets look at that connection:
185
186| ------------0  -means there can be one teacher and none classes ,in other words, 
187
188if there is a class there HAS to be a teacher
189
190|------------E - this means there can be one teacher and more classes
191
192Linked together it represents the real corelation between two, these are a bit hard to conclude
193
194but when you get used to it it's easy, because they are like real life.
195
196Okay that would be all, thank you so so much for reading, or watching, i really hope you
197
198have a nice day :) .