|
Question: What are the different types of SQL joins?
|
Answer: There are several types of SQL joins:
• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
• FULL JOIN: Returns all records when there is a match in either left or right table.
• CROSS JOIN: Returns the Cartesian product of the two tables.
|
|
Question: What is the difference between UNION and UNION ALL in SQL?
|
Answer: The UNION is used to combine the results of two or more SELECT statements into a single result set,
removing duplicate rows.
UNION ALL, on the other hand, also combines the results of SELECT statements but includes all rows, including duplicates.
|
|
Question: Can you explain the ACID properties in the context of database transactions?
|
Answer: The word ACID stands for Atomicity, Consistency, Isolation, and Durability:
• Atomicity ensures that a transaction is treated as a single unit of work, either all of its operations are completed successfully or none are.
• Consistency ensures that the database remains in a consistent state before and after the transaction.
• Isolation ensures that concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially.
• Durability ensures that once a transaction is committed, its changes are permanent and survive system failures.
|
|
|
|
|
|
Question: What is a self-join in SQL?
|
Answer: A self-join is a type of join where a table is joined with itself.
This can be useful when querying hierarchical data or when you need to compare rows within the same table.
|
|
Question: Can you explain the difference between CHAR and VARCHAR data types?
|
Answer: The CHAR is a fixed-length character data type where you specify the length when defining the column.
And VARCHAR is a variable-length character data type where you specify the maximum length when defining the column,
but it only uses the amount of storage necessary for the actual data.
|
|
Question: What is the difference between a subquery and a join in SQL?
|
Answer: A subquery is a query nested within another query and can be used within SELECT, INSERT, UPDATE,
or DELETE statements.
A join is used to combine rows from two or more tables based on a related column between them.
|
|
|
|
|
|
Question: What is a 'composite key' in SQL?
|
Answer: A 'composite key', also known as a 'composite primary key', is a combination of two or more columns that
uniquely identify a record in a table when taken together.
By definition, each column in a composite key can contain duplicate values on its own,
but the combination of values across all the columns within the key must be unique.
|