--Task no.

1 (Select EmployeeID, First Name, Last Name and number of years of

employment for
each employee, where:
• header – the column should be called Number of years, and
• values – instead of year of employment should be displayed stinrg as Employee
has
been working for X years (where X is the number of years of work).)
--

--calculation of diff between two dates

--DATEDIFF

--current date
--select getdate()

select EmployeeID, FirstName, LastName, EmpDate,

'Employee is working since' +
--convert(varchar(2),datediff(year, EmpDate, getdate()))
cast(datediff(year,EmpDate,getdate()) as varchar(2))
+ 'years'
as 'Number of years'
from Employee

--Task no.2 (Select data as above (task no. 1), but the employment date should be
displayed as dd-mm
yyyy (i.e., use the Italian datetime format). The result should be as follows:)
--

select EmployeeID, FirstName, LastName, EmpDate,

convert(char(10), EmpDate, 105)
as 'Date of employment (in Italian style)'
from Employee

--Task no.3 (Select the identifiers of employees and number of rentals for each
employee who has
made at least one rental:)
--

select RentEmployeeId, count(RentEmployeeId) as 'The number of rented cars'

from Rent
group by RentEmployeeId

--Task no.4 (Select columns like above (i.e., in point no.3). Furthermore, for each
row display First
Name and Last Name. Note – use INNER JOIN)
--

select RentEmployeeId, FirstName, LastName, count(RentEmployeeId) as 'The number of

rented cars'
from Rent inner join Employee
on Rent.RentEmployeeId = Employee.EmployeeID
group by RentEmployeeId, FirstName, LastName

--Task no.5 (At first, insert data for new client (i.e., for Kamil Maliszewski) as
below:
Afterwards, select clients – including their indetifiers, Last Names and First
Names – who
have not rented anything. Advice – use LEFT (or RIGHT) OUTER JOIN:)
--
--if we use only inner join here, there will be no Maliszewski, as he has no cars
rented, so is not in Rent table
--we need to use right outer join: take what is inner and something additional from
Client (in that case new row with Maliszewski)
--where excludes some rows
--if we group data, then only we can use having (can't be used without grouping)

--insert into Client values ('Kamil', 'Maliszewski', null, null, 'Legionowa', '3',
'Warszawa', '01-200', '555-233-256', '44-342-116' );

--Task no.6 (Select cars (including CarID, Brand, Model, Color, Engine capacity and
Number of
rentings), which have been never rented)
--

select Car.CarID, Brand, Model,

count(Rent.CarID) as 'No. of rentings'
from Rent right outer join Car
on Rent.CarID = Car.CarID
group by Car.CarID, Brand, Model
having count(Rent.CarID) = 0

--Task no.7 (Select First Names and Last Names of clients and employees whose last
names ending
with ski. The result can contain identical rows, since client(s) and employee(s)
can have the
same first and last names. Sort data by last name (in an ascending order))
--

--use union all when two identical people can occur, union when there is no second
occurences

select FirstName, LastName from Employee

where LastName like '%ski'
union all
select FirstName, LastName from Client
where LastName like '%ski'

--Task no. 8 (Select cars (CarID, Brand and Model) that no client has ever rented.
Use subquery and
predicate IN:)
--

--use of subquery in this case makes the database faster,

--what is more the query is easier to write by a developer
select CarID, Brand, Model
from Car
where CarID not in
(
select CarID from Rent
)

--Task no. 9 ( Select data for employees (FirstName, LastName, Department, Position
and Salary)
whose salary is higher than the value of average salary. Use subquery:)
--

select FirstName, LastName, Department, Position, Salary

from Employee
where salary >
(
select avg(Salary)
from Employee
)

--Task no. 10 (Select data for employees (FirstName, LastName, Department, Position
and Salary)
who earn more than average salary in each department)
--

--exam task
select FirstName, LastName, Department, Position, Salary
from Employee
--we must use all keyword to compare salary to every average (it has to be bigger
than the highest average)
where salary > all
(
select avg(Salary)
from Employee
group by department
)

--Task no. 11 (Select data for employees (FirstName, LastName, Department, Position
and Salary)
who earn more than average salary in any department:)
--

--exam task
select FirstName, LastName, Department, Position, Salary
from Employee
--we must use any keyword to compare salary to any average (has to be bigger than
the smallest average)
where salary > any
(
select avg(Salary)
from Employee
group by department
)

--Task no. 12 (Create view which contains data from columns: First Name, Lasta
Name, Company,
TaxID and City for each client who has got a company. View should be saved with the
name
as follwos view_FirstLetterOfName_LastName (each student using own personal
data). )
--

create view task_no_12 as --can be done only once, then the instance is already
created
select FirstName, LastName, Company, TaxID, City
from Client
where Company is not null

--then we can use the view

select *
from task_no_12
--Task before 13
--When we create a copy of the table, everything is copied except of the primary
key
select * into New_Car from Car
select * into New_Client from Client
select * into New_Employee from Employee
select * into New_Place from Place
select * into New_Rent from Rent

--Task no. 13 --INSERT

--creation of an object (table)
create table Temp_Gabriela_Wanat
(
numberId int not null identity constraint pk_tgw primary key,
FirstName varchar(20),
LastName varchar(20),
)

--inserting data into above table

insert into Temp_Gabriela_Wanat values('Gabriela', 'Wanat')
insert into Temp_Gabriela_Wanat values('Jan', 'Kowalski')

--display of the data

select *
from Temp_Gabriela_Wanat

--Task no. 14 __DELETE

--DELETE - DML
--1st select
select *
from New_Car

--removing data
delete New_Car

--2nd select
select *
from New_Car

--DROP - DDL
--1st select
select *
from New_Place

--removing the whole object

drop table New_Place

--2nd select -- will throw an error (table isn't in DB anymore)

select *
from New_Place

--Task no. 15 (delete clients which city starts with 'W' letter)
--1st select
select *
from New_Client

--removing data
delete New_Client
where City like 'w%'
--2nd select
select *
from New_Client

--Task no. 16
--1st select
select *
from New_Employee

--change = update data

update New_Employee set Salary = Salary * 1.1
where ExtSalary is null

--2nd select
select *
from New_Employee

--Task no. 17
declare @noOfSomething int
select @noOfSomething = count(*)
from Car
where Brand = 'Mercedes'
print 'In our company we have got ' +
--convert(varchar(5), @noOfSomething)
cast(@noOfSomething as varchar(5))
+ ' Mercedeses.'

--Task no. 18
declare @avgSal decimal
select @avgSal = avg(salary)
from Employee

if(@avgSal > 1600)

print 'Incomes are ok'
else
print 'Incomes are too small!!'

--Task no. 19
CREATE PROCEDURE CheckClientRentals
@ClientID INT
AS
BEGIN
IF EXISTS (SELECT 1 FROM Rent WHERE ClientID = @ClientID)
BEGIN
DECLARE @CarCount INT
SELECT @CarCount = COUNT(*) FROM Rent WHERE ClientID = @ClientID
PRINT 'The customer with the given ID rented: ' + CAST(@CarCount AS
VARCHAR(10)) + ' cars.'
END
ELSE
BEGIN
PRINT 'The client with the given ID has not rented anything!'
END
END
GO

-- Execute the stored procedure with ClientID = 6

EXEC CheckClientRentals @ClientID = 6;
--Task no. 20
select FirstName, LastName, Position, Department,
Salary as 'Salary in value(s)',
case
when Salary < 1500 then 'low salary'
when Salary between 1500 and 2000 then 'medium salary'
--when Salary >= 1500 and Salary <= 2000 then 'medium slary'
when Salary > 2000 then 'high salary'
end as 'Salary in string(s)'
from Employee

--Task ?
--how to create a procedure
create procedure RetMaxIncome
as
declare @salVal decimal
select @salVal = max(Salary)
from Employee
print 'Maximal salary in our company = '
+ convert (varchar(20), @salVal) + '(That is info from PROC)'

--later to call the created procedure

exec RetMaxIncome