The TOP keyword is used to limit the number of records returned in a SELECT statement.
For example, if you wanted to select the top 5 records from a table called ‘Customers’, you would use the following query:
SELECT TOP 5 * FROM Customers;
1. INNER JOIN: This is the most common type of join used in SQL. It returns rows when there is at least one match in both tables.
Example:
SELECT *
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2;
2. LEFT JOIN: This join returns all rows from the left table, even if there are no matches in the right table.
Example:
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2;
3. RIGHT JOIN: This join returns all rows from the right table, even if there are no matches in the left table.
Example:
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2;
4. FULL OUTER JOIN: This join returns all rows from both tables, even if there are no matches in either table.
Example:
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column2;
5. CROSS JOIN: This join returns the Cartesian product of both tables.
Example:
SELECT *
FROM table1
CROSS JOIN table2;
A primary key is a field in a table that uniquely identifies each record in the table. It is a combination of a unique index and a not null constraint. For example, a customer table may have a primary key of customer_id.
A unique key is a field in a table that uniquely identifies each record in the table. It does not have to be the primary key, but it must contain unique values. For example, a customer table may have a unique key of email address.
The FOR XML clause in SQL Server is used to return query results in XML format. It is useful for applications that require data in XML format.
For example, the following query returns a list of customer names and their respective orders in XML format:
SELECT c.Name, o.OrderNumber
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
FOR XML AUTO, ELEMENTS;
The output of this query would be something like this:
John Smith
12345
Jane Doe
54321
A DDL trigger is a special type of trigger that fires in response to a Data Definition Language (DDL) event. It can be used to audit and control changes to the database schema, such as when a table is modified, or when a user attempts to create or drop a table.
For example, you could create a DDL trigger to log any changes to the database schema, by logging the SQL command that was executed, or by sending an email to the DBA. You could also create a DDL trigger to block certain users from creating or dropping tables, by raising an error when the command is attempted.
A clustered index is a type of index that physically reorders the data in a table when the index is created. This means that when a clustered index is created, the data in the table is physically sorted in the order of the clustered index key. A non-clustered index does not physically reorder the data in a table when the index is created. Instead, a non-clustered index creates a separate structure that contains the index key and a pointer to the data in the table.
For example, if we have a table called ‘Employees’ with the columns ‘EmployeeID’, ‘FirstName’, and ‘LastName’, we could create a clustered index on the ‘EmployeeID’ column. This would cause the table to be physically sorted by the ‘EmployeeID’ column. We could also create a non-clustered index on the ‘LastName’ column. This would create a separate structure that contains the ‘LastName’ column and a pointer to the data in the table. The data in the table would not be physically sorted by the ‘LastName’ column.
Vaibhav Kothia