{"id":886,"date":"2020-07-09T07:00:00","date_gmt":"2020-07-09T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=886"},"modified":"2026-02-24T11:21:35","modified_gmt":"2026-02-24T08:21:35","slug":"ti-einai-i-sql-kai-ti-mporei-na-kanei","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-einai-i-sql-kai-ti-mporei-na-kanei\/","title":{"rendered":"What is SQL and what can it do?"},"content":{"rendered":"
We have seen what databases are but in order to use them we must somehow be able to export or import data from them.<\/p>\n\n\n\n
How is this possible? With the Structured Query Language<\/strong> or otherwise SQL.<\/p>\n\n\n\n
It is the basic version common to all RDBMS<\/strong> (Relational Database Management Systems) or relational database management systems.<\/p>\n\n\n\n
So SQL depends on it RDBMS <\/strong>it has some variations like T-SQL<\/strong> for SQL-Server, PL-SQL<\/strong> for Oracle but we will talk about common SQL.<\/p>\n\n\n\n
Types of commands in SQL<\/h4>\n\n\n\n
SQL statements are divided into 3 basic categories DDL<\/strong>(Data Definition Language), DML<\/strong>(Data Manipulation Language), DCL<\/strong>(Data Control Language). <\/p>\n\n\n\n
The DDL <\/strong>statements concern changes in the shape of the tables (CREATE\/ALTER\/DROP etc.), the DML <\/strong>changes to records (INSERT, UPDATE, DELETE), the DCL <\/strong>are about changes to permissions in the base.<\/p>\n\n\n\n
Let's see what we can do with SQL.<\/p>\n\n\n\n
Our examples will be in Greek so we don't have to mess with day one collations.<\/p>\n\n\n\n
We must not forget that since we can have many different logical groups of named objects schema<\/strong>. It is correct to define its schema (with '.' as separator) before the name of the object. As well as before the field to define the object that belongs to e.g. dbo.pinakas.kwdikos_pelati<\/em>. For the simplicity of the examples we will work on the default schema.<\/p>\n\n\n\n
create table<\/h4>\n\n\n\n
First we will make a table (DDL statement).<\/p>\n\n\n\n
This table will have an integer type field that is identity with step (1,1) as primary key. That is, a unique number that will start from 1 and will increase by one unit. We will also make a field for text up to 100 characters (variable size) and one for a phone number.<\/p>\n\n\n\n
But how do we tell him bring me only 2 fields, name and phone?<\/p>\n\n\n\n
Easy.<\/p>\n\n\n\n
select onoma,tilefono from pinakas<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Now I want you to bring me only Nikos, how will this be done?<\/p>\n\n\n\n
With the addition where<\/strong>.<\/p>\n\n\n\n
select onoma from pinakas where onoma = 'Nikos'<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Data update (update)<\/h4>\n\n\n\n
Let's look at one more DDL statement update<\/strong>. <\/strong>The update gives us the possibility to change the values in the records in all or selectively.<\/p>\n\n\n\n
Let's say we want to make as many records as George have the name Kostas.<\/p>\n\n\n\n
update pinakas set onoma ='Giorgos'\nwhere onoma = 'Kwstas'\n\nselect * from pinakas<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Now we want to change the name to a phone.<\/p>\n\n\n\n
update pinakas set onoma ='Leonidas'\nwhere tilefono = '210772049'\n\nselect * from pinakas<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Sorting (order)<\/h4>\n\n\n\n
Ok now I want to see the results alphabetically by name from A to Z.<\/p>\n\n\n\n
select * from pinakas order by onoma desc<\/pre>\n\n\n\n<\/figure>\n\n\n\n
*looks like we were already in alphabetical order.<\/p>\n\n\n\n
Delete data (delete)<\/h4>\n\n\n\n
But now we want to delete the record that has the name of Leonidas. This will be done with one delete<\/strong> (DML).<\/p>\n\n\n\n
delete from pinakas where onoma = 'Leonidas'\n\nselect * from pinakas<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Import records with selected fields filled in only<\/h4>\n\n\n\n
Let's put another record with insert.<\/p>\n\n\n\n
insert into pinakas (tilefono,onoma) values ('Kwstas','210772049\u2032)<\/pre>\n\n\n\n
Msg 245, Level 16, State 1, Line 1\n\nConversion failed when converting the varchar value \u2018Kwstas\u2019 to data type int.<\/code><\/pre>\n\n\n\n
Oops!! Something went wrong... What happened now?<\/p>\n\n\n\n
We made a mistake, we tell him that in the table to wait for the fields in the order phone and name but when we put the values we gave them upside down so we ask to go to a field that accepts numbers to enter text.<\/p>\n\n\n\n
insert into pinakas (onoma,tilefono) values ('Kwstas','210772049\u2032)\n\nselect * from pinakas<\/pre>\n\n\n\n<\/figure>\n\n\n\n
But why are registration id's missing and Kostas got 4 and not, for example, 3?<\/p>\n\n\n\n
Truncate table<\/h4>\n\n\n\n
Because even though we did delete before the identities are bound (and they do well) because they may be connected to other tables.<\/p>\n\n\n\n
In case we want identity again from the beginning we can do it truncate<\/strong> (DDL) that will delete all data in the table.<\/p>\n\n\n\n
truncate table pinakas\ngo\n\ninsert into pinakas values ('Kwstas',\u2019210772049\u2032)\ninsert into pinakas values ('Nikos','215294882\u2032)\n\nselect * from pinakas<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Find data that contains\u2026 (like)<\/h4>\n\n\n\n
Now we have phones that start from 215 and 210 but we want to see only those that start from 210 how is this done? With his statement like<\/strong>, <\/strong>putting the pattern we want and a wild card %<\/strong>.<\/p>\n\n\n\n
select * from pinakas where tilefono like '210%'<\/pre>\n\n\n\n<\/figure>\n\n\n\n
How do we count how many entries a table has (count)<\/h4>\n\n\n\n
Right now how many records does the table have though? Let's do one count<\/strong> to see.<\/p>\n\n\n\n
select count(onoma) from pinakas<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Pivoting (group by)<\/h4>\n\n\n\n
Let's go a step further to see how we can see how many records the table has per name (called pivoting). <\/p>\n\n\n\n
This is done with one group by<\/strong> in the name. But demand at select<\/strong> to have the fields that are in the group by in our case the name.<\/p>\n\n\n\n
select onoma,count(onoma) as 'Arithmos' from pinakas group by onoma<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Adding a new field to the table (alter table add)<\/h4>\n\n\n\n
Let's add with a DDL statement the alter<\/strong> a text field for the last name.<\/p>\n\n\n\n
alter table pinakas add\n\nepitheto varchar(100)<\/pre>\n\n\n\n<\/figure>\n\n\n\n
Nulls<\/h4>\n\n\n\n
In SQL that field is empty is translated as NULL<\/strong>.<\/p>\n\n\n\n
These are the basic functions of SQL in a nutshell. Soon the article will be uploaded which will contain analysis and for more advanced uses such as: dynamic sql, cursors, common table expressions, loops, nested selects<\/strong> and subqueries<\/strong>.<\/p>","protected":false},"excerpt":{"rendered":"
We have seen what databases are but in order to use them we must somehow be able to export or import data from them. How is this possible? With Structured Query Language or SQL. It is the basic version common to all RDBMS (Relational Database Management Systems) or relational management systems [\u2026]<\/p>","protected":false},"author":1,"featured_media":700,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[29,92,39],"class_list":["post-886","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","tag-databases","tag-programming","tag-sql"],"yoast_head":"\n
![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/1-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/2-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/3-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/4-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/5-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/6-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/7-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/8-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/9-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/10-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/11-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/12-results.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dataplatform.gr\/wp-content\/uploads\/2020\/06\/13-results.png\")
<\/figure>\n\n\n\n