--ex1
create view Oferte_Desfasurat as
select Nume_Furnizor = Furnizor.Nume,
Oras_Furnizor = Furnizor.Oras,
Nume_Produs = Produs.Nume,
Cantitate, Pret,
Valoare= Cantitate*Pret
from Furnizor, Oferte, Produs
where Furnizor.CodF = Oferte.CodF
and Produs.CodP = Oferte.CodP

--
update Oferte_Desfasurat set Oras_Furnizor = 'Timisoara'

update Oferte_Desfasurat set Nume_Produs = 'Televizor' where Nume_Produs = 'Auto'

insert Oferte_Desfasurat (Nume_Furnizor,Oras_Furnizor) VALUES ('SANEX' , 'Cluj')

--2
create view O as select * from Oferte union select * from Oferte

update O set Cantitate = Cantitate + 100



--3
create view FF(NF,O1,O2) as
select F1.Nume, F1.Oras, F2.Oras
from Furnizor F1, Furnizor F2
where F1.CodF = CodF




--4
execute ('select * from Furnizor  where Oras = Cluj')
execute ('set quoted_identifier on select * from Furnizor where Oras ="Cluj"')
execute ('select * from @tabel1')


--5

DECLARE @table_name varchar(50)
DECLARE @field varchar(50)
DECLARE @value varchar(50)
DECLARE @newvalue varchar(50)

set @table_name = 'Furnizor'
set @field = 'Oras'
set @value = 'cluj'
set @newvalue = 'Timisoara'
select * from Furnizor
print('update ' + @table_name + ' set ' + @field + ' = "' + @newvalue + '" where ' + @field + ' = "' + @value + '" ')

set quoted_identifier off
execute('update ' + @table_name + ' set ' + @field + ' = "' + @newvalue + '" where ' + @field + ' = "' + @value + '" ')

select * from Furnizor

set quoted_identifier off
 update Furnizor set Oras = "cluj" where Oras = "Timisoara"


 --

DECLARE @table_name varchar(50)
DECLARE @field varchar(50)
DECLARE @value varchar(50)
DECLARE @newvalue varchar(50)


declare @nume_oras nvarchar(1000)

declare @sirsql nvarchar(1000)
declare @sir_parametri nvarchar(1000)

set @table_name = 'Furnizor'
set @field = 'Oras'
set @value = 'Timisoara'
set @newvalue = 'Cluj'
select * from Furnizor

print('update ' + @table_name + ' set ' + @field + ' = "' + @newvalue + '" where ' + @field + ' = "' + @value + '" ')

--set @newvalue = N'UPDATE ' + @table_name + ' set ' + @field + ' = "' + @newvalue + '" where ' + @field + ' = "' + @value + '" '

set @sirsql =  N'UPDATE ' + @table_name + ' set ' + @field + ' = "' + @newvalue + '" where ' + @field + ' = "' + @value + '" '

--EXECUTE sp_executesql @sirsql




set @nume_oras ='Cluj'
EXECUTE sp_executesql @sirsql, @sir_parametri, @par_nume_oras=@nume_oras

set @nume_oras ='bucuresti'
EXECUTE sp_executesql @sirsql, @sir_parametri, @par_nume_oras=@nume_oras

select * from Furnizor