SQL

Basic tasks collection

Stored procedures
User defined functions
Queries

Task 1: Stored procedure - Frequent names

Task: Write a stored procedure to find the most frequent names in employee table.

create proc dbo.sp_frequent_names
as
  begin
    select count(*) as 'counted_names', employee.name
    from db.employee employee
    group by employee.name
    order by counted_names desc
  end
  
/*For executing...*/
go
exec dbo.sp_frequent_names

Task 2: Stored procedure - Year variable

Task: Write a stored procedure to find a movie in a specific year.

create proc dbo.sp_find_movie (@year int)
as
  begin
    select movies.movie_name
    from db.movies movies
    where year(movie_release_date) = @year
    order by movies.movie_name desc
  end
  
/*For executing...*/
go
exec dbo.sp_find_movie '1994'

Task 3: Stored procedure - The IF statement

Task: Write a stored procedure with IF statement.

use dbo.test
go

create proc dbo.sp_IF (@alert varchar(4))
as
  begin
    if @alert = 'GOOD'
      begin
        (
        select * from employee employee
        where employee.salary between 100 and 500
        )
        return
      end

    if @alert = 'BAD'
      begin
        (
        select * from employee employee
        where employee.salary > 500
        )
        return
      end
  
    select 'It is GOOD to be BAD.'
  end
  
/*For executing...*/
go
exec dbo.sp_IF 'GOOD'

Task 4: Basic while loop

Task: Write a basic while loop with break statement.

declare @counter int
set @counter = 1

while @counter <= 10
  begin
    if @counter = 6 break
    print @counter
    set @counter = @counter + 1
  end

Task 5: Function - Age calculator

Task: Write a function that calculates age.

create function dbo.fn_age
 (
  @dob as datetime
 )
returns int
as
begin
  return year(getdate()) - year(@dob)
end    
    

/*For executing...*/
select emp.fname + ' ' + emp.lname as name,
dbo.fn_age(emp.date_of_birth) as age
from dbo.employee emp

Task 6: Function - pretiffy date

Task: Write a basic user defined function that will pretiffy date.

create function dbo.fnLongDate
 (
  @fullDate as datetime
 )
returns varchar(max)
as
begin
  return
    datename(dw,@fullDate) + ' ' +
    datename(d,@fullDate) + ' ' +
  case
    when day(@fullDate) in (1,21,13) then 'st'
    when day(@fullDate) in (2,22) then 'nd'
    when day(@fullDate) in (3,23) then 'rd'
    else 'th'
    end + ' ' +
    datename(m,@fullDate) + ' ' +
    datename(y,@fullDate)
end

/*For executing...*/
  
select emp.name, dbo.fnFullDate(emp.birth_date)
from dbo.employee emp

Task 7: Function - show only firstname

Task: Write a function that will show only first name (cuts last name).

create function dbo.fnFirstname
 (
  @fullName as varchar(max)
 )
returns varchar(max)
as
begin
    declare @spacePosition int
    declare @answer as varchar(max)
    set @spacePosition = charindex(' ', @fullName)
    
    if @spacePosition = 0
        set @answer = @fullName
    else
        set @answer = left(@fullName, @spacePosition - 1)
    return @answer    
    
end

/*For executing...*/

select dbo.fdFirstname(emp.fullName)
from dbo.employee emp

Task 8: Query - Spot the difference (not in)

Task: Write a query that spots all employees with rank 'A' but not with rank 'B'.

select emp.id, emp.name, ranks.rank
from dbo.emloyee emp
join dbo.ranks ranks on ranks.id = emp.id
where ranks.rank = 'A'
and emp.id not in
 (
  select emp2.id
  from dbo.emloyee emp2
  join dbo.ranks2 ranks2 on ranks2.id = emp2.id
  where ranks2.rank = 'B'
 )

Task 9: Query - Duplicity (group by; having)

Task: Write a query that spots multiple happy people.

select count(emp.property) as property
from dbo employee emp
where emp.property like '%HAPPY%TO%HELP'
group by property
having count(property) > 1

Task 10: Query - records with DATEADD and DATEDIFF system functions

Task: Write a query that shows records 30 days before licence ends.
We have only "valid from" date and we know that licence ends after 4 years; We have to calculate "valid to" date.

select *
from dbo.certificates c
where
datediff(day,dateadd(year,4, c.valid_from),getdate()) = -30

/*
We used simple formula:
valid_from + 4 years - 30 (days) from datediff with getdate
*/

Task 11: Query - generating IDs

Task: write a query that can be used for generating IDs.

select isnull(max(cast(machines.ID as int)), 0) + 1
from dbo.machines machines

/*
Idea: New record will always get highest ID from table + 1

1. select ID
2. select max ID to get latest number
3. avoid string value and cast it as intiger
4. handle null values with isnull
*/

SQL - Basic tasks