What are SQL Joins?
In SQL, a join is used to combine rows from two or more tables based on a shared column. Joins can be used to retrieve data in various ways, depending on the relationship between tables.
Joins are classified into four types:
Full Join, Inner Join, Left Join, Right Join.
How can I make empty tables that share another table’s structure?
It is possible to create empty tables with the same structure by using the INTO operator to fetch the data from one table into a new table while setting a WHERE clause to false for each entry. Since the WHERE clause is in effect, SQL as a result creates a new table with a duplicate structure to accommodate the fetched entries, but nothing is stored in the new table.
What does “UNIQUE constraint” mean?
The UNIQUE Constraint prevents the occurrence of the same values in a column in two records. Every value in a column is guaranteed to be unique by the UNIQUE constraint.
What do the SQL terms “table” and “field” mean?
A table is defined as a collection of rows and columns of organised data. Simply put, it is a table-formatted collection of related data.
The number of columns in a table is referred to as a field, and rows and columns are referred to as tuples and attributes in this context. Fields represent the traits and attributes in the record and hold detailed information about the data.
What distinguishes a primary key from a unique key?
Both primary and unique keys can have distinct values, but only unique keys can have a null value for a primary key. There can only be one primary key per table, but there can be many unique keys.
What is the SELECT statement?
A SELECT command gets zero or more rows from one or more database tables or views. In most applications, SELECT is the most frequently used data manipulation language (DML) command. Due to the declarative nature of SQL, SELECT queries define a result set rather than how to calculate it.
List the various SQL relationship types.
In the database, there are various kinds of relations:
- Each record in one table corresponds to a maximum of one record in the other in a connection known as a one-to-one relationship between two tables.
- The most common connection, where a record in one table is linked to several records in another, is one-to-many and many-to-one.
- When defining a relationship that necessitates multiple instances on both sides, the term “many-to-many” is used.
- Self-Referencing Relationships – This technique should be used when a table needs to declare a relationship with itself.
What is a Self-Join?
A join type that can be used to link two tables is a self-join. It is a unary relationship as a result. Each row of the table is joined in a self-join to all other rows in the table as well as to itself. In order to combine and compare rows from the same database table, a self-join is typically used.
What are the comments in SQL?
SQL Comments are used to add clarification to specific areas of SQL statements as well as to stop them from being executed. In many programming languages, comments play a significant role. Unsupported by a Microsoft Access database are the comments. As a result, the examples in Mozilla Firefox and Microsoft Edge use the Microsoft Access database.
Comments on a Single Line: It begins with two consecutive hyphens (–).
Comments that span multiple lines: It begins with * and ends with *.
What do the commands UNION, MINUS, and INTERSECT mean?
The results of the two tables are combined using the UNION operator, and any duplicate entries are also removed.The first query’s rows are returned using the MINUS operator, but the second query’s rows are not returned.The results of both queries are combined into one row using the INTERSECT operator.
What is a view?
In SQL, a view is a fictitious table built using the results of a SQL statement. Like a true table, a view also has rows and columns. A view contains fields from one or more actual database tables.
What is Normalization?
The process of efficiently organising structured data in a database is known as normalisation. It involves building tables, establishing connections between them, and specifying the rules for those connections. These guidelines can be used to control consistency and redundancy, giving the database more flexibility.
What is the difference between NOW() and CURRENT_DATE
The constant time that NOW() returns is the moment when the statement started to run. (Within a trigger or stored function, NOW() returns the moment the function or triggering statement started to run.
The only distinction between NOW() and CURRENT DATE() is that NOW() will retrieve the current date and time in the format “YYYY-MM DD HH:MM:SS,” whereas CURRENT DATE() will retrieve the current day’s date in “YYYY-MM DD.”
What is a foreign key?
A FOREIGN KEY is a field or group of fields in a table that serve as a direct reference to the PRIMARY KEY in a different table. Referential integrity in the relationship between two tables is guaranteed by a foreign key constraint.The table containing the candidate key is referred to as the referenced or parent table, and the table with the foreign key constraint is referred to as the child table.
What is a constraint?
Constraint can be used to specify the limit on the data type of table. Constraints can be specified while creating or altering the table statement.
What is a query?
A database query is a piece of code created to retrieve data from a database. A query can be created so that it matches our expectations for the set of results. Just a simple query to the database.
What is an Index?
An index is a performance improvement technique that makes it possible to retrieve records from a table more quickly. Since an index creates an entry for each value, data retrieval will be quicker.
What are the different types of indexes in SQL?
Unique Index: If the column is uniquely indexed, this index prevents the field from having duplicate values. An automatic unique index can be used if a primary key is defined.
Clustered Index: This index uses key values to conduct searches while rearranging the table’s physical order. There can only be one clustered index per table.
Non-Clustered Index: A non-clustered index keeps the data in a logical order while not changing the physical order of the table. Nonclustered indexes can be numerous for each table.
What is meant by denormalization?
Database administrators employ the denormalization technique to boost the effectiveness of their database infrastructure. The concept of denormalization is based on normalisation, which is defined as correctly organising a database into tables for a specific purpose. In order to resolve problems with database queries that combine data from multiple tables into a single table, we can use this method to add redundant data into a normalised database.
What is the difference between DELETE and TRUNCATE statements?