What is SQL and what can it do?

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

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 in 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 -- Με * ορίζουμε ότι θέλουμε να δούμε όλα τα πεδία του πίνακα.

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

Easy.

select onoma,tilefono from pinakas

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'

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

Now we want to change the name to a phone.

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

select * from pinakas

Sorting (order)

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

select * from pinakas order by onoma desc

*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

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

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

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%'

θα μπορούσαμε να βάλουμε το μπαλαντερ μπροστά π.χ. ‘%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

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

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)

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.