--select * from SalesLT.Product
--1. cte :
select * from SalesLT.ProductCategory

--2. plecand de la SalesLT.SalesOrderDetails sa scoatem comenzile
--cele mai mari ca si valoare absoluta

--3. in ce oras am trimis cele mai multe vanzari

select * from SalesLT.CustomerAddress
select * from SalesLT.Address

--4. care sunt top clienti cu cele mai mari discount-uri

--5. update pe ListPrice cand se modifica costul de productie si poate

select ssd.ProductID,ssd.UnitPrice,ssd.UnitPriceDiscount,'**',sp.ListPrice,sp.StandardCost

from SalesLT.SalesOrderDetail ssd
inner join SalesLT.Product sp on sp.ProductID=ssd.ProductID
order by ssd.ProductID

-- sa vedem adaos comercial la fiecare produs
-- sa vedem produsele pentru care costul standardCost este mai mare decat pretul de vanzare

--
update SalesLT.Address
set PostalCode='xxx'
from
SalesLT.Address a
join xxx

--

--pe structura actuala "pretul de lista" sa nu fie mai mare de 50% decat "pretul de achizitie" ( standardCost) si
a. sa se actualizeze pretul de pe order pentru produsele de mai sus si pentru care statusul comenzii nu este "plata neefectuata" ( un nou status = 1 in tabela de SalesLT.SalesOrderHeader)
b. sa se scrie informatia cu user cu user care a facut modificarea intr-o tabela de audit

-- se face un update pe **SalesOrderHeader** si avem o eroare de actualizare ( nu ii zicem ca avem un trigger) si vrem sa vedem de unde vine

========================================================================================================================
2024.02.16
-Q : care sunt optiunile de a rula un query dinamic in sql
A:

a. Using EXEC or EXECUTE Command:
The EXEC or EXECUTE command allows you to execute a dynamically constructed SQL statement. You can build the SQL query as a string and then execute it using EXEC. Here’s a simple example:
SQL

    DECLARE @SQL nvarchar(1000)
    DECLARE @Pid varchar(50)
    SET @Pid = '680'
    SET @SQL = 'SELECT ProductID, Name, ProductNumber FROM SalesLT.Product WHERE ProductID = ' + @Pid
    EXEC (@SQL)

Note: Be cautious when constructing SQL statements by concatenating strings from user input values to avoid SQL injection. Also, handle null values appropriately when concatenating strings.

b. Using sp_executesql:
sp_executesql is an extended stored procedure specifically designed for executing dynamic SQL statements in SQL Server. It allows you to pass parameters and provides better performance than plain EXEC.
Here’s an example using sp_executesql:
SQL

    DECLARE @SQL nvarchar(1000)
    DECLARE @Pid varchar(50)
    SET @Pid = '689'
    SET @SQL = 'SELECT ProductID, Name, ProductNumber FROM SalesLT.Product WHERE ProductID = @ProductId'
    EXEC sp_executesql @SQL, N'@ProductId varchar(50)', @ProductId = @Pid

In this example, we define a parameter (@ProductId) and set its value before executing the dynamic SQL.
c. Using Variables and Quotename:
You can use variables to hold parts of your dynamic SQL query and then concatenate them together. For example:
SQL

    DECLARE @sql NVARCHAR(255)
    DECLARE @tempTableName NVARCHAR(50) = 'MyTable'
    SET @sql = N'SELECT @toStart = COUNT(ID) FROM ' + QUOTENAME(@tempTableName)
    EXEC sp_executesql @sql, N'@toStart INT OUTPUT', @toStart OUTPUT
    PRINT @toStart

**-Q: Ce optiune pentru executia query-urilor dinamice preferi si de ce?
** If you’re executing a simple, one-off dynamic query, EXEC/EXECUTE might suffice.
For more complex or frequently executed dynamic queries, prefer sp_executesql due to its performance benefits and parameterization capabilities.

**-Q: Cum ai putea scrie un "IF" intr-un select
**
Using the CASE Statement:
The CASE statement is the most common way to perform conditional checks in SQL. It allows you to evaluate conditions and return different values based on those conditions.
Here’s an example using the CASE statement:
SQL

SELECT
    CASE
        WHEN Age < 21 THEN 'child'
        ELSE 'adult'
    END AS AgeGroup,
    Name
FROM Person;

In this example, if the Age is less than 21, it returns 'child'; otherwise, it returns 'adult'.
Using the IIF Function (SQL Server 2012 and later):
The IIF function is a shorthand way of writing a simple CASE statement. It returns one value if the condition is true and another value if false.
Example using IIF:
SQL

SELECT
    IIF(Age < 21, 'child', 'adult') AS AgeGroup,
    Name
FROM Person;

**-Q: diferente intre proceduri si functii pe scurt**

a. Functions:
Think of functions like mini calculators. You give them some numbers, and they perform a specific calculation and give you an answer.
Functions return a single value (like the result of an equation).
You can use them in your SQL queries to get specific information.

b. Stored Procedures:
Imagine stored procedures as sets of instructions. They can do more than just calculations.
Stored procedures can accept input (like parameters) and perform various tasks (like updating data or sending emails).
They’re like reusable scripts that you can run whenever you need.
In summary:

Functions are like calculators that return a value.
Stored procedures are like versatile scripts with instructions.

a.Functions:
Purpose:
Functions are named operations that return a single value or a table.
They are typically used for specific calculations or operations.
Return Value:
Always returns a single value (either scalar or a table).
Can be used in SQL statements (e.g., SELECT queries).
Compilation and Execution:
Functions are compiled and executed at runtime.
Allowed Statements:
Only allows SELECT statements.
Data manipulation language (DML) statements like UPDATE and INSERT are not allowed.
Parameters:
Allows only input parameters.
Does not allow output parameters.
Exception Handling:
Cannot use TRY...CATCH blocks for exception handling.
Transactions:
Cannot have transactions within a function.
Calling from Other Objects:
Cannot call a stored procedure from a function.
Temporary Tables:
Cannot use temporary tables within a function; only table variables are allowed.

b.Stored Procedures:
Purpose:
Stored procedures are reusable sets of SQL statements.
They can accept parameters and return results.
Return Value:
Can return zero, single, or multiple values.
Compilation and Execution:
Stored procedures are stored in a parsed and compiled state in the database.
Allowed Statements:
Can perform any operation on database objects, including SELECT and DML statements.
Parameters:
Allows both input and output parameters.
Exception Handling:
Allows the use of TRY...CATCH blocks for exception handling.
Transactions:
Can have transactions within a stored procedure.
Calling from Other Objects:
Can call a function from a stored procedure.
Temporary Tables:
Both table variables and temporary tables can be used.

**- Q: Removing duplicate rows from an SQL table**

 WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [FirstName], [LastName], [Country] ORDER BY ID) AS RowNum
    FROM Employee
)
DELETE FROM CTE WHERE RowNum > 1;

##############################################################################################################################
##############################################################################################################################

Q: what is the use of the IDisposable interface

The IDisposable interface in .NET serves as a way to manage unmanaged resources. These resources include things like file handles, database connections, and network sockets. When an object implements IDisposable, it means it holds such resources that need explicit cleanup.

Here are the key points:

Resource Management: Objects implementing IDisposable manage unmanaged resources.
Explicit Cleanup: The Dispose() method is used to release these resources.
Using using: The using statement helps ensure proper cleanup.
Garbage Collection Limitations: The GC can’t directly reclaim unmanaged resources.
Example: A class implementing IDisposable should release resources in its Dispose() method.
Remember, using IDisposable is essential for responsible resource management in .NET applications.

Q: Avem un batch in memory intr-o lista si vrem sa o procesam si actiunea ar fi trimiterea unui SMS pentru fiecare numar din lista
Cum s-ar putea face cat mai rapid

//IEnumerable<Message>  messageList
var partitioner = Partitioner.Create(messageList, EnumerablePartitionerOptions.NoBuffering);
var threadsNumber=4;

Parallel.ForEach(partitioner, new ParallelOptions { MaxDegreeOfParallelism = threadsNumber }, group => {
	foreach (var eventInfo in group) {
		SendSMS(event)
	}
}

**Q: Lock meaning in C# **

The lock statement helps manage concurrent access to shared resources (like variables or data) in a multi-threaded environment.
It ensures that only one thread can access the resource at a time, preventing issues like data corruption or race conditions.

**Q: Lock options in distributed systems ?

**Q : Optiuni prin care se poate face o refactorizare pentru a elimina o referinta circulara intre doua clase

public class Foo
{
    Bar myBar;
}

public class Bar
{
    Foo myFoo;
}

se adauga o interfata

A: public interface IBar
{
}

public class Foo
{
    IBar myBar;
}

public class Bar : IBar
{
    Foo myFoo;
}

** Ai folosit Message queue-uri si daca da, ce anume?