What is the definition of a foreign key?
A foreign key is a field in one table that relates to another table’s primary key. It’s used to connect the first table’s record to the second table’s record.
What is the definition of a composite key?
A main key that consists of two or more fields is known as a composite key. Primary keys are usually single fields, although in some circumstances, multiple fields are used to identify a row. A composite key is exactly that.
What is the meaning of a surrogate key?
A surrogate key is a field in a table that was built specifically to serve as the primary key. It is only used for internal storage and as a reference number.
For example, a customer may have a unique account number, but a customer id column on the table might be established and utilized as the primary key in the event that business requirements change and the account number is no longer unique.
What is a one-of-a-kind constraint? What distinguishes it from a main key?
A table constraint that states that a column or set of columns must have unique values is known as a unique constraint.
A unique constraint differs from a primary key in that a table can have zero, one, or many unique constraints, whereas a primary key can only have one.
Primary keys cannot have NULL values, although unique constraints can.
What exactly is a synonym?
A synonym is a database object that acts as a “link” or “alias” to another database object. This is frequently done to mask the name of the actual object for security reasons or to make future code maintenance easier.
What does it mean to “cross join”?
A cross join is a sort of join in which the results display all possible combinations of records from both tables. The join is performed without the usage of any fields.
If table A contains 10 records and table B contains 8, the cross join will produce 80 (or 10 x 8) records.
What exactly is a self join, and why would you want to utilize one?
A self-join is a form of join that joins two tables together.
When a table has a field that relates to another record in the same table, you’d use a self join. It’s frequently used in hierarchical structures, such as employee tables with a manager id field that corresponds to a different employee record.
What is the default ORDER BY sort order? What can be done about it?
Ascending is the default sort order. The word DESC following any column name in the ORDER BY clause can be used to adjust this. To specify ascending order, the term ASC might be used instead.
What is the difference between a window function and an analytical function?
A window function, also known as an analytic function, conducts a calculation across a group of linked rows. A window function is similar to an aggregate function, however it does not group any rows together. Behind the scenes, the window method accesses many rows.
What is the distinction between the terms UNION and JOIN?
A join allows us to lookup data in another table based on common fields from one dataset (for example employees and departments). It necessitates the existence of a field that is shared by both tables.
We can use a union to aggregate the results of two searches into one. There is no requirement to join the results. The only requirement is that the number and type of columns be the same.
What is the definition of a subquery?
A subquery is a query that is contained within a larger query. This subquery can appear in a variety of places, including the FROM, SELECT, and WHERE clauses.
It’s frequently used when you need to feed the output of one query into another.
What is the definition of a correlated subquery?
A connected subquery refers to a field in the outer query.
Subqueries can be non-correlated standalone inquiries, or they can leverage fields from the outer query. These fields are frequently used in WHERE clauses or join conditions.
SQL Interview Preparation 