What is SQL and what can it do?

What is SQL and what can it do?
What is SQL and what can it do?

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 the Structured Query Language or otherwise SQL.

It is the basic version common to all RDBMS (Relational Database Management Systems) or relational database management systems.

So SQL depends on it RDBMS it has some variations like T-SQL for SQL-Server, PL-SQL for Oracle but we will talk about common SQL.

Types of commands in SQL

SQL statements are divided into 3 basic categories DDL(Data Definition Language), DML(Data Manipulation Language), DCL(Data Control Language).

The DDL statements concern changes to the shape of the tables (CREATE/ALTER/DROP etc.), the DML changes to records (INSERT, UPDATE, DELETE), the DCL are about changes to permissions in the base.

Let's see what we can do with SQL.

Our examples will be in Greek so we don't have to mess with day one collations.

We must not forget that since we can have many different logical groups of named objects schema. 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. For the simplicity of the examples we will work on the default schema.

create table

First we will make a table (DDL statement).

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.

create table pinakas (
id INT identity (1,1) primary key,
onoma varchar(100),
tilefono INT
)
Commands completed successfully.
Total execution time: 00:00:00.010

Insert records

Let's now add 2 records to it with insert statement (DML). We should mention which table

insert into pinakas values 
('Kwstas','210772049′),
('Nikos','215294882′);
(1 row affected)
(1 row affected)
Total execution time: 00:00:00.018

Read data (select)

Let's see now how to read the data from the table...

It is quite simple with the command Select (DQL)

select * from pinakas -- Με * ορίζουμε ότι θέλουμε να δούμε όλα τα πεδία του πίνακα.
What is SQL and what can it do?

But how do we tell him bring me only 2 fields, name and phone?

Easy.

select onoma,tilefono from pinakas
What is SQL and what can it do?

Now I want you to bring me only Nikos, how will this be done?

With the addition where.

select onoma from  pinakas where onoma = 'Nikos'
What is SQL and what can it do?

Data update (update)

Let's look at one more DDL statement update. The update gives us the possibility to change the values in the records in all or selectively.

Let's say we want to make as many records as George have the name Kostas.

update pinakas set onoma ='Giorgos'
where onoma = 'Kwstas'

select * from pinakas
What is SQL and what can it do?

Now we want to change the name to a phone.

update pinakas set onoma ='Leonidas'
where tilefono = '210772049'

select * from pinakas
What is SQL and what can it do?

Sorting (order)

Ok now I want to see the results alphabetically by name from A to Z.

select * from pinakas order by onoma desc
What is SQL and what can it do?

*looks like we were already in alphabetical order.

Delete data (delete)

But now we want to delete the record that has the name of Leonidas. This will be done with one delete (DML).

delete from pinakas where onoma = 'Leonidas'

select *  from pinakas
What is SQL and what can it do?

Import records with selected fields filled in only

Let's put another record with insert.

insert into pinakas (tilefono,onoma) values ('Kwstas','210772049′)
Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘Kwstas’ to data type int.

Oops!! Something went wrong... What happened now?

We made a mistake, we tell it to wait for the fields in the order phone and name in the table but when we put the values we put them in reverse so we ask to go to a field that accepts numbers to enter text.

insert into pinakas (onoma,tilefono) values ('Kwstas','210772049′)

select * from pinakas
What is SQL and what can it do?

But why are registration IDs missing and Kostas got 4 and not, for example, 3?

Truncate table

Because even though we did delete before the identities are bound (and they do well) because they may be connected to other tables.

In case we want identity again from the beginning we can do it truncate (DDL) that will delete all data in the table.

truncate table pinakas
go

insert into pinakas values ('Kwstas',’210772049′)
insert into pinakas values ('Nikos','215294882′)

select * from pinakas
What is SQL and what can it do?

Find data that contains… (like)

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, putting the pattern we want and a wild card %.

select * from pinakas where tilefono like '210%'
What is SQL and what can it do?

θα μπορούσαμε να βάλουμε το μπαλαντερ μπροστά π.χ. ‘%779510’ ή να ορίσουμε συγκεκριμένο εύρος χαρακτήρος βάζοντας ‘?’ αντι για ‘%’ π.χ. ‘2107050???’.

How do we count how many entries a table has (count)

Right now how many records does the table have though? Let's do one count to see.

select count(onoma) from pinakas
What is SQL and what can it do?

Pivoting (group by)

Let's go a step further to see how we can see how many records the table has per name (called pivoting).

This is done with one group by in the name. But demand at select to have the fields that are in the group by in our case the name.

select onoma,count(onoma) as 'Arithmos' from pinakas group by onoma
What is SQL and what can it do?

Adding a new field to the table (alter table add)

Let's add with a DDL statement the alter a text field for the last name.

alter table pinakas add

epitheto varchar(100)
What is SQL and what can it do?

Nulls

In SQL that field is empty is translated as NULL.

These are the basic functions of SQL in a nutshell. Soon the article will be uploaded that will contain analysis and for more advanced uses such as: dynamic sql, cursors, common table expressions, loops, nested selects and subqueries.

Share it

2 thoughts on “Τι είναι η SQL και τι μπορεί να κάνει

  1. Good morning!

    Finally, is SQL considered a programming language or a database management system?

Leave a reply