1. What is SQL and what are its key features?
SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. Its key features include data querying, data manipulation (inserting, updating, deleting), data definition (creating and altering tables), and data control (granting and revoking permissions).
2. What are the differences between SQL and NoSQL databases?
SQL databases are relational, table-based databases with a predefined schema, ideal for structured data and complex queries. NoSQL databases are non-relational, can be document-based, key-value pairs, wide-column stores, or graph databases, and are suitable for unstructured data and flexible schema requirements.
3. Explain the difference between a primary key and a foreign key.
A primary key is a unique identifier for a record in a table, ensuring that no two rows have the same primary key value. A foreign key is a field (or a collection of fields) in one table that uniquely identifies a row in another table, establishing a relationship between the two tables.
4. What are SQL joins and what are the different types?
SQL joins are used to combine rows from two or more tables based on a related column. The different types of joins include:
- INNER JOIN: Returns records with matching values in both tables.
- LEFT JOIN: Returns all records from the left table and matched records from the right table.
- RIGHT JOIN: Returns all records from the right table and matched records from the left table.
- FULL JOIN: Returns all records when there is a match in either left or right table.
5. How do you use the GROUP BY clause in SQL?
The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions (COUNT, SUM, AVG, MAX, MIN) to perform operations on each group of rows.
6. What is a subquery and how is it used in SQL?
A subquery is a query nested within another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements to perform operations based on the results of the subquery. Subqueries can return individual values or sets of rows.
7. Explain the difference between DELETE and TRUNCATE in SQL.
DELETE removes specific rows from a table based on a condition and can be rolled back. TRUNCATE removes all rows from a table, is faster, uses fewer resources, and cannot be rolled back (in most RDBMS).
8. What is a SQL View and how do you create one?
A view is a virtual table based on the result set of a SELECT query. It allows users to simplify complex queries, encapsulate logic, and secure data. A view is created using the CREATE VIEW statement:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
9. How do you optimize SQL queries for better performance?
SQL query optimization can be achieved through several methods, including:
- Using proper indexing.
- Avoiding unnecessary columns in SELECT statements.
- Using joins instead of subqueries where appropriate.
- Analyzing and rewriting complex queries for efficiency.
- Ensuring statistics are up-to-date.
10. What is a stored procedure and how do you create one in SQL?
A stored procedure is a set of SQL statements that can be executed as a single unit. It is used to encapsulate repetitive tasks, improve performance, and enhance security. A stored procedure is created using the CREATE PROCEDURE statement:
CREATE PROCEDURE procedure_name
AS
BEGIN
-- SQL statements
END;
Here you can find SQL Resources
👇👇
https://t.me/sqlanalyst
Credits: https://t.me/crackingthecodinginterview
ENJOY LEARNING 👍👍