How to write advanced SQL queries

How to write advanced SQL queries
How to write advanced SQL queries

In previous article we had analyzed what the SQL (Structured Query Language) and how to use it with basic examples.

In this article we will see its more advanced functions.

The code is written with the extension Transact-SQL used by SQL Server. In other RDBMS the syntax may differ such as e.g. to Oracle that uses it PL/SQL.

The structure and initial data for our examples is as follows:

create table pinakas (
id INT identity (1,1) primary key,
onoma varchar(100),
tilefono INT,
epitheto varchar(100)

insert into pinakas values 

Correlated subquery 

That Correlated subquery  is the inner subquery that depends on the outer query and compares one record to another record.

We want to update the suffix so that each record is suffixed with the name with _Epi. We will need to declare the table we are calling a second time with another alias name eg (from pinakas as p2) and we will have to set the id from the table to be equal to the same field in the alias of the same table to match line line:

update pinakas set epitheto = (select onoma+'_Epi' from pinakas as p2 where =

select * from pinakas
How to write advanced SQL queries

Non-correlated subquery 

The Non-correlated subquery  is the subquery that is executed independently of the outer query. The inner subquery is executed first and passes the results to the outer query:

select * from pinakas where id = (select 4,6,7)
How to write advanced SQL queries

After playing with the subquery let's delete the adjective column with one drop (dll statement):

alter table pinakas drop column epitheto

select * from pinakas
How to write advanced SQL queries


Depending on the price of each registration, can we bring something else on a case-by-case basis?

Yes we can, by using it CASE:

    WHEN onoma = 'Kwstas' THEN 'Kwnstantinos'
    WHEN onoma ='Nikos'  THEN  'Nikolaos'
    ELSE 'Asxetos'
END AS 'EinaiOdikosMas'
from pinakas
How to write advanced SQL queries


Through SQL we can call loops like a classic while. We should declare a variable(@i) and give it a value(set). Finally it should be inside a transaction begin/end:

SET @i=5
    SET @i=@i-1
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'test' to data type int.
Total execution time: 00:00:00.001

Oops... Something went wrong, what?

Quite simply we tried to join a text with a number by converting the text to a number it doesn't happen so what do we do?


With the cast function we can convert any field into a specific data type, e.g. a number field into a text field (as is also done with the T-SQL function convert). To solve the error message that appeared to us very simply do cast  the field which is a number in varchar to count as text:

SET @i=5
    PRINT('test'+cast(@i as varchar(2)))
    SET @i=@i-1

Total execution time: 00:00:00.001

Dynamic Query

We may well have code in variables and optionally concatenate them and call them as one request. How; With the Dynamic Query.

We should have defined variables that will contain each piece of code we want, then with the command EXECUTE and those variables in parentheses will be executed as if it were a complete query.

With print we see how the set of variables will be:

DECLARE @part1 as VARCHAR(100), @part2 as VARCHAR(100)
set @part1='SELECT onoma'
set @part2='from pinakas'


EXECUTE (@part1+@part2)
How to write advanced SQL queries

Nested Select

With the nested select we can select not from a table or view but from another select. This select can even be from the same table.

Let's look at an example:

select onoma from (select onoma,tilefono from pinakas) as nested
How to write advanced SQL queries

Common Table Expression or CTE

When we want a query to remain in memory with high performance as it iterates, returning subsets of data until all of the CTE it's the way.

Let's look at a simple example without much use:

We will define one CTE named dokimi_cte and we will call it with select in the next command:

with dokimi_cte
(select onoma from  pinakas  group by onoma)

select * from dokimi_cte
How to write advanced SQL queries


With cursors we have the ability to scroll back and forth in the records in order to build a desired result.

In this particular example we wanted to print each name of the table by adding extra text before it.

We will make a cursor named kersoras which will be filled with the entire number of names that the table contains, as long as there are still records it will continue to do action and when it finishes it will go to the next one:

declare @names varchar(1000)
declare kersoras CURSOR

for  select onoma from pinakas;
open kersoras
FETCH NEXT FROM kersoras INTO @names

print 'Mr '+@names
FETCH NEXT FROM kersoras INTO @names  
CLOSE kersoras  
DEALLOCATE kersoras 
Mr Nikos

Mr Kwstas

Total execution time: 00:00:00.008

Select into (copy/backup one table to another)

With the select into command set we have the possibility to copy the records by creating a new table corresponding to the one we called the data:

select * into pinakas2 from pinakas

select * from pinakas2
How to write advanced SQL queries


By command MERGE we can update a table (target) with values from another table (source).

Let's say we have the table pinakas(target) and we want to update the phones with new ones. We also want to bring in new customers if there aren't any already. The information will be in the table prosorinos_pinakas(source).

Let's look at the tables in detail:

select * from pinakas
How to write advanced SQL queries
pinakas (target)
insert into prosorinos_pinakas values 

select * from prosorinos_pinakas 
How to write advanced SQL queries
prosorinos_pinakas (source)

We will use a comparison field the customer's name, where it matches it will update the phone with the new one (from prosorinos_pinakas), any name it doesn't find will add the entire record to our original table (pinakas):

MERGE pinakas t 
    USING prosorinos_pinakas s
ON (s.onoma = t.onoma)
        t.tilefono = s.tilefono
    THEN INSERT (onoma,tilefono)
         VALUES (s.onoma,s.tilefono);

Now if we look at the table again we will have another phone in 'Kwstas' and we will have the new customer named 'Giorgos' who did not exist:

select * from pinakas
How to write advanced SQL queries
Share it

Leave a reply