BEGINNER

1. What is SQL?

SQL - Structured Query Language and is used to communicate with relational databases

2. What is a primary key in SQL?

Unique identifier for each record in a table. It ensures that each row in the table has a distinct and
non-null value in the primary key column. (create relationships between tables)

3. What is a foreign key?

It is a field in one table referencing the primary key in another. It establish connection between
tables.

Explain the difference between DELETE and TRUNCATE commands.

DELETE - to delete particular rows from a table, based on a condition

TRUNCATE - removes all rows from a table without specifying conditions

TRUNCATE is faster and uses fewer system resources than DELETE.

4. What is a JOIN in SQL, and what are its types?

JOIN operation merges information from two or more tables by utilizing a common column that
links them together.
 5. What do you mean by a NULL value in SQL?

NULL value in SQL represents the absence of data in a column.

6. Define a Unique Key in SQL.

Unique key guarantees that every value in a column (or a combination of columns) remains
distinct and cannot be replicated within a table.

7. Explain the differences between SQL and NoSQL databases.

 Structură relațională: Bazele de date SQL sunt bazate pe un model relațional, unde datele
sunt organizate în tabele cu rânduri și coloane.

 Schema fixă: Aceste baze de date necesită o schemă fixă și predefinită, care descrie
structura datelor. Schimbările de schemă pot fi dificile și necesită adesea modificări
complexe.

 Structured Data

 Diverse modele de date: NoSQL acoperă o gamă largă de modele de date, inclusiv
documente, chei-valoare, coloane și grafuri. Acestea sunt adaptate pentru diverse tipuri
de date și aplicații.

 Schema flexibilă: Nu necesită o schemă fixă și predefinită. Acest lucru permite

dezvoltatorilor să adauge, să modifice și să șteargă câmpuri în mod flexibil fără a necesita
modificări complexe de schemă.

 Semi-Structured (JSON, xml) or Unstructured Data(imagini si video-uri)

Ex de Baze de date cum sunt scalate:

 SQL – Orizontala (mySQL, PostgreSQL), Verticala(Oracle, MySqlServer),

 NoSQL – Orizontala (mongoDB), Verticala – Neo4j
 8. What is a constraint in SQL? Name a few.

A constraint in SQL defines rules or restrictions that apply to data in a table, ensuring data
integrity. Common constraints include:

 PRIMARY KEY: Ensures the values’ uniqueness in a column.

 FOREIGN KEY: Enforces referential integrity between tables.

 UNIQUE: Ensures the uniqueness of values in a column.

9. What is normalization in SQL?

Fn1 – avem primary key si toate elementele din tabel sunt atomice, in fiecare celula avem doar o
valoare

Fn2 – fn1 + coloană care nu face parte din cheia primară trebuie să depindă de întreaga cheie
primară, nu doar de o parte a acesteia. (coloanele care nu sunt chei primare depind de toate
componentele cheii primare.)

Fn3 – fn2+ fiecare coloană care nu face parte din cheia primară trebuie să depindă direct de cheia
primară, nu de alte coloane din tabelă. (o coloană nu trebuie să depindă de alte coloane care nu
sunt cheia primară.)

10. What are indexes in SQL?

Indexes improve the data retrieval operations speed. They provide a quick way to locate specific
rows in a table. Indexes are essential for optimizing query performance.

What is an SQL alias?

 11. What is an SQL alias?

SELECT first_name AS "fn", last_name AS "ln" FROM employees;

12. Describe the difference between WHERE and HAVING

in SQL.

Where – utilizat inainte de group by si de obicei nu este itilizat in acelasi context cu el

Haveing – este utilizat in acelasi context cu group by si este pozitionat dupa acesta

13. What is a view in SQL?

View este o tabele virtuala care stocheaza rezultatul unui query SQL.

CREATE VIEW ViewAngajati AS

SELECT Nume, Prenume, Departament

FROM Angajati

WHERE Departament = 'Vanzari';

14. What is a stored procedure?

O procedură stocată poate fi considerată similară cu o funcție în alte limbaje de programare, în

sensul că poate primi parametri, poate executa o serie de instrucțiuni și poate returna rezultate.

CREATE PROCEDURE GetEmployeeByID

@EmployeeID INT

AS
 BEGIN

SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;

END;

Avem procedura GetEmployeeByID, care primeste ca parametru de tip INT denumit

EmployeeID

15. What is a trigger in SQL?

Trigger is a special type of stored procedure that automatically executes in response to certain
database events or actions.

16. What are aggregate functions? Can you name a few?

Aggregate functions in SQL perform calculations on a set of values and return a single result.
(MIN, MAX, SUM, COUNT)
INTERMEDIARE
17. What is a self-join, and how would you use it?

A self-join is a type of join where a table is joined with itself. It is useful when creating
relationships within the same table, such as finding hierarchical relationships or comparing rows
with related data.

We have table Customers which have columns: CustomerI, CustomerName, ContactName,

Address, City, PostalCode Country

SELECT A.CustomerName AS CustomerName1,

B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

18. What is a subquery?

A subquery is like a SELECT in a SELECT.

SELECT name

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

19. What is the difference between UNION and UNION

ALL?
SELECT Name FROM Customers

UNION

SELECT Name FROM Suppliers;

RESULT:

UNION merges the outcomes of two or more SELECT statements, removing duplicate rows,
whereas UNION ALL merges the results without removing duplicates. While UNION ALL is
faster, it may include duplicate rows.

20. Explain ACID properties in SQL.

The ACID properties are a set of four characteristics that guarantee the reliability and
consistency of database transactions:

1. Atomicity: Transactions are atomic, meaning they are either executed in their entirety or
not at all. If any part of a transaction fails, the entire transaction is rolled back to its initial
state, ensuring that the database remains in a consistent state.
2. Consistency: Refers to the assurance that a transaction can only bring the database from
one valid state to another valid state. In simpler terms, it means that the data in the
database remains in a valid and expected state at all times, both before and after the
execution of a transaction.
 EXEMPLE when data is not valid: have rows where data is missing, insert a value (a
record) which have the same primary key value with an existent one

3. Isolation: Transactions operate independently of each other, and their effects are not
visible to other transactions until they are committed. This ensures that concurrent
transactions do not interfere with each other and that each transaction sees a consistent
view of the database.
4. Durability: Once a transaction is committed, its changes are permanently saved in the
database and cannot be lost, even in the event of a system failure. This ensures that the
database can recover its data to a consistent state after a crash or restart.

21. What is a transaction in SQL?

A transaction in SQL is a sequence of one or more SQL operations treated as a single unit of
work. Transactions ensure that database operations are either completed successfully or rolled
back entirely in case of failure.

22. What is a cursor, and how is it used?

In SQL, a cursor is a database element employed for the purpose of fetching and controlling data
one row at a time from a result set. Cursors find frequent application within stored procedures or
triggers when it becomes necessary to process data in a sequential manner.

23. Explain normalization and denormalization:

Normalization is the method used to streamline data in a database, decreasing redundancy
and enhancing data integrity. This procedure includes dividing large tables into smaller,
interconnected ones to eliminate duplicated data. Conversely, denormalization is the
deliberate act of introducing redundancy to enhance query performance.

Redundancy – refers to the repetition or duplication of data within a database.

Data integrity - refers to the accuracy, consistency, and reliability of data stored in a database. It
ensures that the data remains valid.

Inconsistency - refers to a state where data does not conform to the expected or desired state.
 24. How does a clustered index work and how is it different
from a non-clustered index?
Clustered Index:

 In a clustered index, the physical order of the rows in the table is the same as the order of
the index keys.

 Each table can have only one clustered index because the data rows themselves are stored
in the order of the clustered index keys.

 When a clustered index is created on a table, the table's data is physically reorganized to
match the order specified by the index keys.

Non-Clustered Index:

 In a non-clustered index, the index keys are stored separately from the actual data rows in
the table.

 Each table can have multiple non-clustered indexes, allowing for flexibility in indexing
different columns.

 Non-clustered indexes store pointers to the location of the actual data rows, rather than
storing the data itself.

25. How is data integrity ensured in SQL?

Data integrity in SQL is ensured through various means, including constraints (e.g., primary
keys, foreign keys, check constraints), normalization, transactions, and referential integrity
constraints. These mechanisms prevent invalid or inconsistent data from being stored in the
database.

26. What is SQL injection?

SQL injection is a type of security vulnerability that occurs when an attacker is able to inject
malicious SQL code into a query, thereby gaining unauthorized access to the database or
manipulating its contents

27. What is a deadlock in SQL? How can it be prevented?

A deadlock in SQL occurs when two or more transactions cannot proceed because they are
waiting for resources held by each other. Deadlocks can be prevented or resolved by using
techniques such as locking hierarchies, timeouts, or deadlock detection and resolution
mechanisms.