Pergunta 16
Explain the SELECT statement.
The SELECT statement is used to query the database and retrieve data from one or more tables.
Example:
SELECT column1, column2 FROM table WHERE condition;
Pergunta 17
What is normalization?
Normalization is the process of organizing data in a database to reduce redundancy and dependency.
Pergunta 18
Explain INNER JOIN and LEFT JOIN.
INNER JOIN returns rows when there is a match in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table.
Example:
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
Pergunta 19
Write a SQL query to calculate the average salary for each department.
SELECT department, AVG(salary) FROM Employee GROUP BY department;
Pergunta 20
Explain the difference between UNION and UNION ALL.
UNION combines and returns unique rows from multiple SELECT statements, while UNION ALL returns all rows including duplicates.
Example:
SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
Pergunta 21
Explain the difference between DELETE and TRUNCATE commands.
DELETE is used to remove rows from a table based on a condition. TRUNCATE removes all rows from a table without logging individual row deletions.
Example:
DELETE FROM table WHERE condition;
Pergunta 22
Explain the concept of ACID properties in a database.
ACID (Atomicity, Consistency, Isolation, Durability) properties ensure the reliability of database transactions.
Pergunta 23
Explain the GROUP BY clause.
The GROUP BY clause is used in a SELECT statement to arrange identical data into groups, typically used with aggregate functions.
Example:
SELECT column, COUNT(*) FROM table GROUP BY column;
Pergunta 24
What is a stored procedure?
A stored procedure is a set of SQL statements that can be stored in the database and executed by calling the procedure.
Pergunta 25
Write a SQL query to find duplicate records in a table.
SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1;
Pergunta 26
Explain the purpose of the HAVING clause.
The HAVING clause is used in combination with the GROUP BY clause to filter the results of aggregate functions based on specified conditions.
Example:
SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1;
Pergunta 27
Explain the difference between a primary key and a unique key.
A primary key is used to uniquely identify each record in a table and cannot contain NULL values. A unique key enforces uniqueness but can contain NULL values.
Pergunta 28
Explain the difference between UNION and JOIN.
UNION is used to combine the results of two or more SELECT statements, while JOIN is used to retrieve data from multiple tables based on a related column between them.
Pergunta 29
What is a trigger in SQL?
A trigger is a set of instructions that are automatically executed (or 'triggered') in response to certain events on a particular table or view.
Pergunta 30
What is a self-join?
A self-join is a regular join, but the table is joined with itself. It is useful when you want to combine rows with related data in the same table.
Example:
SELECT employee1.name, employee2.name FROM Employee employee1, Employee employee2 WHERE employee1.manager_id = employee2.employee_id;
Pergunta 31
Explain the concept of a composite key.
A composite key consists of more than one column and is used to uniquely identify a record in a table when a single column is not sufficient.
Pergunta 32
What is the difference between a stored procedure and a function?
A stored procedure does not necessarily return a value, while a function must return a value. Functions can be used in SQL statements, whereas procedures cannot.
Pergunta 33
Explain the purpose of the SQL CASE statement.
The CASE statement is used to perform conditional logic within a SQL query, similar to an IF-THEN-ELSE statement in other programming languages.
Example:
SELECT column1, CASE WHEN condition THEN 'Result1' ELSE 'Result2' END AS NewColumn FROM table;
Pergunta 34
What is a subquery? Provide an example.
A subquery is a query embedded within another query. It can be used to retrieve data that will be used in the main query. Example: SELECT column FROM table WHERE column IN (SELECT column FROM another_table);
Pergunta 35
Write a SQL query to find the total count of rows in each table of a database.
SELECT table_name, COUNT(*) FROM information_schema.tables GROUP BY table_name;
Pergunta 36
Explain the purpose of the SQL HAVING clause.
The HAVING clause is used to filter the results of aggregate functions in a SELECT statement based on specified conditions.
Example:
SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1;
Pergunta 37
What is the difference between UNION and UNION ALL?
UNION combines and returns unique rows from multiple SELECT statements, while UNION ALL returns all rows including duplicates.
Example:
SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
Pergunta 38
Explain the purpose of the SQL INDEX.
An INDEX is a performance optimization feature that allows faster retrieval of records from a table. It is created on one or more columns of a table.
Example:
CREATE INDEX index_name ON table(column);
Pergunta 39
What is a self-referencing foreign key?
A self-referencing foreign key is a foreign key in a table that references the primary key of the same table, creating a relationship within the same table.
Pergunta 40
Write a SQL query to find the total salary expense for each department.
SELECT department, SUM(salary) FROM Employee GROUP BY department;
Pergunta 41
Write a SQL query to find the employees who do not belong to any department.
SELECT * FROM Employee WHERE department_id IS NULL;
Pergunta 42
Explain the difference between the INNER JOIN and OUTER JOIN.
INNER JOIN returns rows when there is a match in both tables, while OUTER JOIN returns all rows from one table and the matching rows from the other table, filling in the missing values with NULLs.
Example:
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
Pergunta 43
Explain the concept of a natural join.
A natural join is a type of JOIN that automatically matches columns with the same name in the joined tables. It eliminates duplicate columns in the result set.
Example:
SELECT * FROM table1 NATURAL JOIN table2;
Pergunta 44
Explain the concept of a CTE (Common Table Expression).
A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves the readability and maintainability of complex queries.
Example:
WITH cte_name AS (SELECT * FROM table) SELECT * FROM cte_name;
Pergunta 45
Explain the purpose of the SQL GROUP_CONCAT() function.
The GROUP_CONCAT() function is used to concatenate values from multiple rows into a single string, grouped by a specified column.
Example:
SELECT department, GROUP_CONCAT(employee_name) FROM Employee GROUP BY department;
Pergunta 46
What is a common use case for the SQL COALESCE() function?
The COALESCE() function is used to return the first non-null expression among its arguments. A common use case is handling null values in a query.
Example:
SELECT COALESCE(column1, 'DefaultValue') FROM table;
Pergunta 47
Explain the purpose of the SQL SESSION_USER and SYSTEM_USER functions.
SESSION_USER returns the current username, and SYSTEM_USER returns the login name of the current user.
Example:
SELECT SESSION_USER, SYSTEM_USER;
Pergunta 48
What is the purpose of the SQL JSON functions (JSON_VALUE, JSON_QUERY, etc.)?
JSON functions in SQL are used to process JSON data. JSON_VALUE is used to extract a scalar value, and JSON_QUERY is used to extract an object or an array.
Example:
SELECT JSON_VALUE(json_column, '$.key') AS Value FROM table;
Pergunta 49
Explain the purpose of the SQL STRING_AGG() function.
The STRING_AGG() function is used to concatenate values from multiple rows into a single string with a specified separator.
Example:
SELECT department, STRING_AGG(employee_name, ', ') AS EmployeeList FROM Employee GROUP BY department;
Pergunta 50
Write a SQL query to find the employees who joined the company in the last N months.
SELECT * FROM Employee WHERE JOIN_DATE >= DATEADD(MONTH, -N, GETDATE());
