Answer:
SQL (Structured Query Language) is a standard language for relational database management systems. It is used to interact with databases to store, manipulate, and retrieve data.
Answer:
SQL statements include Data Definition Language (DDL) statements (CREATE, ALTER, DROP), Data Manipulation Language (DML) statements (SELECT, INSERT, UPDATE, DELETE), Data Control Language (DCL) statements (GRANT, REVOKE), and Transaction Control Language (TCL) statements (COMMIT, ROLLBACK).
Answer:
SQL is a language used to query and operate on databases, while MySQL is a specific implementation of a relational database management system (RDBMS) that uses SQL as its querying language.
Answer:
A primary key in SQL is a column or a set of columns that uniquely identifies each row in a table. It enforces entity integrity and ensures that no duplicate rows exist.
Answer:
`INNER JOIN` returns rows when there is a match in both tables based on the join condition. `LEFT JOIN` returns all rows from the left table and the matched rows from the right table, with unmatched rows in the right table filled with NULLs.
Answer:
A foreign key in SQL is a column or a set of columns that establishes a link between data in two tables. It enforces referential integrity by ensuring that the values in the foreign key column(s) match the primary key values in the referenced table.
Example Code:
CREATE TABLE Orders ( OrderID int PRIMARY KEY, ProductID int, FOREIGN KEY (ProductID) REFERENCES Products(ProductID) )
Answer:
Normalization in SQL is the process of organizing data in a database to reduce redundancy and dependency. It improves data integrity, reduces storage space, and facilitates efficient querying.
Answer:
SQL indexes are data structures that improve the speed of data retrieval operations on a database table at the cost of additional space and decreased performance for write operations. They are used to quickly locate and access rows.
Answer:
`WHERE` clause filters rows before any groupings are made, whereas `HAVING` clause filters rows after groups are applied. `HAVING` is used with `GROUP BY` to filter groups based on a specified condition.
Example Code:
SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10
Answer:
A SQL subquery is a query nested inside another query (main query). It can be used within `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statements to return data that will be used in the main query.
Answer:
ACID (Atomicity, Consistency, Isolation, Durability) properties ensure the reliability of transactions in SQL databases. Atomicity ensures that transactions are treated as a single unit, Consistency ensures that data meets all integrity constraints, Isolation ensures that transactions are independent and isolated from each other, and Durability ensures that committed transactions are permanent and survive system failures.
Answer:
`UNION` combines and returns the results of two or more `SELECT` statements without duplicates, while `UNION ALL` combines and returns all rows, including duplicates, from two or more `SELECT` statements.
Answer:
SQL injection is a technique used to exploit vulnerabilities in an application's software that interacts with SQL databases. Attackers inject malicious SQL code into input fields to manipulate the database or gain unauthorized access.
Example Code:
SELECT * FROM Users WHERE Username = 'admin' AND Password = '' OR '1'='1'
Answer:
A trigger in SQL is a set of SQL statements that automatically `triggers` or executes in response to specific events on a particular table or view in a database. Triggers are used to enforce business rules, validate data, or perform actions like updating other tables.
Answer:
A view in SQL is a virtual table created by a query. It behaves like a table but does not store data physically. Views are used to simplify complex queries, enforce security by restricting access to specific columns or rows, and provide a layer of abstraction.
Answer:
Advantages of SQL include its simplicity, scalability, and standardization across database systems. Disadvantages include limited support for non-relational data types, potential performance bottlenecks, and complex queries requiring optimization.
Answer:
`ROLLBACK` undoes all changes made in the current transaction and restores the database to its state before the transaction began. `COMMIT` saves all changes made in the current transaction permanently to the database.
Example Code:
BEGIN TRANSACTION; UPDATE Employees SET Salary = Salary * 1.1; COMMIT;
Answer:
The `LIKE` operator in SQL is used in a `WHERE` clause to search for a specified pattern in a column. It is often used with wildcard characters (`%` for zero or more characters, `_` for a single character) to perform flexible string matching.
Example Code:
SELECT * FROM Products WHERE ProductName LIKE 'Ch%';
Answer:
SQL joins are used to combine rows from two or more tables based on a related column between them. Common types of joins include `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, and `FULL JOIN`.
Answer:
A clustered index in SQL determines the physical order of data rows in a table based on the indexed column(s). A table can have only one clustered index. A non-clustered index does not affect the physical order of data rows and can be created on multiple columns.
Answer:
A transaction in SQL is a logical unit of work that comprises one or more SQL statements. Transactions ensure data integrity by guaranteeing that all operations within the transaction are completed successfully (committed) or rolled back (aborted) as a unit.
Answer:
Aggregate functions in SQL perform a calculation on a set of values and return a single value. Examples include `SUM()`, `AVG()`, `COUNT()`, `MIN()`, and `MAX()`. They are often used with `GROUP BY` to summarize data.
Answer:
A self-join in SQL is a join that joins a table to itself. It is used to retrieve related records in the same table, such as finding employees who have the same manager.
Example Code:
SELECT e1.Name, e2.Name AS Manager FROM Employees e1 JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID
Answer:
`TRUNCATE` is a DDL statement that removes all rows from a table and resets identity columns, whereas `DELETE` is a DML statement that removes specific rows based on a condition. `TRUNCATE` is faster but cannot be rolled back.
Example Code:
TRUNCATE TABLE Employees; DELETE FROM Employees WHERE Department = 'HR';
Answer:
Correlated subqueries in SQL are subqueries that reference a column from the outer query. They execute once for each row processed by the outer query, making them dependent on the outer query's results.
Answer:
Normalization in SQL is the process of organizing data in a database to reduce redundancy and dependency, improving data integrity and efficiency. Forms of normalization include First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF).
Answer:
`UNIQUE` constraint ensures that all values in a column are unique, but multiple columns can have unique values. `PRIMARY KEY` constraint is a combination of `UNIQUE` and `NOT NULL` constraints and uniquely identifies each record in a table.
Example Code:
CREATE TABLE Products ( ProductID int PRIMARY KEY, ProductName varchar(255) UNIQUE )
Answer:
`WHERE` clause filters rows before any groupings are made, whereas `HAVING` clause filters rows after groups are applied. `HAVING` is used with `GROUP BY` to filter groups based on a specified condition.
Example Code:
SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10
Answer:
A recursive SQL query is a query that refers to itself in order to return hierarchical data from a table. It uses the `WITH RECURSIVE` syntax in SQL.
Answer:
A view in SQL is a virtual table created by a query. It behaves like a table but does not store data physically. Advantages of views include simplified complex queries, enhanced security by restricting access to specific columns or rows, and providing a layer of abstraction.
Answer:
ACID (Atomicity, Consistency, Isolation, Durability) properties ensure the reliability of transactions in SQL databases. Atomicity ensures that transactions are treated as a single unit, Consistency ensures that data meets all integrity constraints, Isolation ensures that transactions are independent and isolated from each other, and Durability ensures that committed transactions are permanent and survive system failures.
Answer:
Database indexing in SQL is the process of creating an index on a table to improve data retrieval performance. Indexes allow the database server to quickly locate and access specific rows based on the indexed column(s), reducing the need for full table scans.
Answer:
A transaction in SQL is a logical unit of work that comprises one or more SQL statements. Transactions ensure data integrity by guaranteeing that all operations within the transaction are completed successfully (committed) or rolled back (aborted) as a unit. They are managed using `COMMIT`, `ROLLBACK`, and `SAVEPOINT` statements.
Answer:
`INNER JOIN` returns rows when there is a match in both tables based on the join condition. `LEFT JOIN` returns all rows from the left table and the matched rows from the right table, with unmatched rows in the right table filled with NULLs. `RIGHT JOIN` is similar to `LEFT JOIN` but returns all rows from the right table and the matched rows from the left table.
Example Code:
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
Answer:
The `CASE` statement in SQL is used to create conditional logic within a query. It evaluates a list of conditions and returns one of several possible result expressions based on those conditions.
Example Code:
SELECT Name, Salary, CASE WHEN Salary > 50000 THEN 'High' WHEN Salary > 30000 THEN 'Medium' ELSE 'Low' END AS SalaryCategory FROM Employees
Answer:
SQL normalization is the process of organizing data in a database to reduce redundancy and dependency, improving data integrity and efficiency. It eliminates data anomalies such as insertion, update, and deletion anomalies.
Answer:
SQL joins are used to combine rows from two or more tables based on a related column between them. Types of SQL joins include `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, `FULL JOIN`, `CROSS JOIN`, and `SELF JOIN`.
Answer:
`UNION` combines and returns the results of two or more `SELECT` statements without duplicates, while `UNION ALL` combines and returns all rows, including duplicates, from two or more `SELECT` statements.
Example Code:
SELECT City FROM Customers UNION SELECT City FROM Suppliers
Answer:
`GROUP BY` clause in SQL is used to group rows that have the same values into summary rows, such as finding the total sales by region.
Example Code:
SELECT Region, SUM(Sales) AS TotalSales FROM Orders GROUP BY Region
Answer:
SQL indexes are data structures that improve the speed of data retrieval operations on a database table by providing quick access to rows. Types of SQL indexes include primary keys, unique constraints, clustered indexes, and non-clustered indexes.
Answer:
A clustered index in SQL determines the physical order of data rows in a table based on the indexed column(s). A table can have only one clustered index. A non-clustered index does not affect the physical order of data rows and can be created on multiple columns.
Answer:
`ROLLBACK` undoes all changes made in the current transaction and restores the database to its state before the transaction began. `COMMIT` saves all changes made in the current transaction permanently to the database.
Answer:
`WHERE` clause filters rows before any groupings are made, whereas `HAVING` clause filters rows after groups are applied. `HAVING` is used with `GROUP BY` to filter groups based on a specified condition.
Example Code:
SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10
Answer:
SQL triggers are special types of stored procedures that automatically execute in response to specific events (e.g., `INSERT`, `UPDATE`, `DELETE`) on a particular table or view in a database. Triggers are used to enforce business rules, validate data, or perform actions like updating other tables.
Answer:
The `LIKE` operator in SQL is used in a `WHERE` clause to search for a specified pattern in a column. It is often used with wildcard characters (`%` for zero or more characters, `_` for a single character) to perform flexible string matching.
Example Code:
SELECT * FROM employees WHERE last_name LIKE 'S%'
Answer:
SQL injection is a technique used to exploit vulnerabilities in an application's software that interacts with SQL databases. Attackers inject malicious SQL code into input fields to manipulate the database or gain unauthorized access. To prevent SQL injection, use prepared statements (parameterized queries), input validation, and escape characters.
Example Code:
SELECT * FROM Users WHERE Username = 'admin' AND Password = '' OR '1'='1'