Flashcards on Advanced SQL

Click on the flashcard to see the answer


What is a subquery in SQL?

A subquery is a query within another query and is used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.

What is a temporary table in SQL?

A temporary table is a table that is stored temporarily in the database and can be used to store immediate results that can be referenced later in a SELECT, UPDATE, INSERT, or DELETE statement.

What is a self join in SQL?

A self join is a join in which a table is joined to itself. It is useful when working with hierarchical data or when comparing rows within a table.

What is a window function in SQL?

A window function is a function that performs a calculation across a set of rows that are related to the current row but are not part of the current row’s result set.

What is a materialized view in SQL?

A materialized view is a database object that stores the results of a query and updates the result set when the underlying data changes.

What is the difference between a left join and a right join in SQL?

A left join returns all the rows from the left table and matching rows from the right table. A right join returns all the rows from the right table and matching rows from the left table.

What is the difference between a inner join and a outer join in SQL?

An inner join returns only the matching rows from both the tables while an outer join returns all the rows from one table and matching rows from the other table.

What is a cross join in SQL?

A cross join returns the Cartesian product of the two tables i.e, all the possible combinations of rows from both the tables.

What is the difference between a primary key and a foreign key in SQL?

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. A foreign key is a column or set of columns in a table that refers to the primary key of another table.

What is database normalization in SQL?

Database normalization is the process of organizing the data in a database so that it is consistent, efficient, and reduces redundancy.

What is a trigger in SQL?

A trigger is a special type of stored procedure that automatically executes in response to certain events such as INSERT, UPDATE or DELETE statements.

What is database replication in SQL?

Database replication is the process of copying data from one database server to another so that the data is stored in multiple locations for better availability and disaster recovery.

What is the difference between a clustered index and a non-clustered index in SQL?

A clustered index determines the physical order of the records in a table and can be created on only one column or a group of columns. A non-clustered index is used to speed up data retrieval but does not determine the physical order of the records in a table and can be created on multiple columns.

What is a deadlock in SQL?

A deadlock is a situation where two or more transactions are waiting for each other to release locks, preventing any of the transactions from proceeding.

What is the difference between a view and a table in SQL?

A view is a virtual table that is based on the result of an SQL statement and does not store any data of its own. It is used to simplify complex queries and provide a level of security. A table is a physical entity that stores data in rows and columns.

What is a subquery in SQL?

A subquery is a query within another query and is used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.

What is a temporary table in SQL?

A temporary table is a table that is stored temporarily in the database and can be used to store immediate results that can be referenced later in a SELECT, UPDATE, INSERT, or DELETE statement.

What is a self join in SQL?

A self join is a join in which a table is joined to itself. It is useful when working with hierarchical data or when comparing rows within a table.

What is a window function in SQL?

A window function is a function that performs a calculation across a set of rows that are related to the current row but are not part of the current row’s result set.

What is a materialized view in SQL?

A materialized view is a database object that stores the results of a query and updates the result set when the underlying data changes.

What is the difference between a left join and a right join in SQL?

A left join returns all the rows from the left table and matching rows from the right table. A right join returns all the rows from the right table and matching rows from the left table.

What is the difference between a inner join and a outer join in SQL?

An inner join returns only the matching rows from both the tables while an outer join returns all the rows from one table and matching rows from the other table.

What is a cross join in SQL?

A cross join returns the Cartesian product of the two tables i.e, all the possible combinations of rows from both the tables.

What is the difference between a primary key and a foreign key in SQL?

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. A foreign key is a column or set of columns in a table that refers to the primary key of another table.

What is database normalization in SQL?

Database normalization is the process of organizing the data in a database so that it is consistent, efficient, and reduces redundancy.

What is a trigger in SQL?

A trigger is a special type of stored procedure that automatically executes in response to certain events such as INSERT, UPDATE or DELETE statements.

What is database replication in SQL?

Database replication is the process of copying data from one database server to another so that the data is stored in multiple locations for better availability and disaster recovery.

What is the difference between a clustered index and a non-clustered index in SQL?

A clustered index determines the physical order of the records in a table and can be created on only one column or a group of columns. A non-clustered index is used to speed up data retrieval but does not determine the physical order of the records in a table and can be created on multiple columns.

What is a deadlock in SQL?

A deadlock is a situation where two or more transactions are waiting for each other to release locks, preventing any of the transactions from proceeding.

What is the difference between a view and a table in SQL?

A view is a virtual table that is based on the result of an SQL statement and does not store any data of its own. It is used to simplify complex queries and provide a level of security. A table is a physical entity that stores data in rows and columns.

Share


Login to Save


Share



Login to Save


Explore Other Decks


Made for Grade 7

L'Illuminismo


View Deck
Made for Grade 8

Ivan Franko - Ukrainian Literary Phenomenon


View Deck
Made for Grade 9

Computer Components


View Deck

Explore More