What is SQL?
SQL (Structured Query Language) is a standard programming language designed for managing and manipulating relational databases.
What are the different types of SQL commands?
There are three types of SQL commands:
Data Definition Language (DDL) commands: used for creating, modifying, and deleting database objects like tables, views, and indexes.
Data Manipulation Language (DML) commands: used for retrieving, inserting, updating, and deleting data in a database.
Data Control Language (DCL) commands: used for granting or revoking access permissions to database users.
What is a primary key?
A primary key is a unique identifier for a record in a table. It is used to ensure that each record in a table is unique and can be easily accessed and updated.
What is a foreign key?
A foreign key is a field in a table that refers to the primary key of another table. It is used to establish a relationship between two tables in a database.
What is a view in SQL?
A view is a virtual table that is based on the result of a SQL query. It is used to simplify complex queries and to hide the underlying complexity of the database schema.
What is a stored procedure?
A stored procedure is a precompiled SQL program that can be executed multiple times with different parameters. It is used to improve performance and to simplify complex database operations.
What is a trigger?
A trigger is a special type of stored procedure that is automatically executed in response to certain events, such as an insert, update, or delete operation on a table.
What is normalization?
Normalization is the process of organizing data in a database to minimize data redundancy and improve data integrity.
What is denormalization?
Denormalization is the process of intentionally adding redundant data to a database to improve performance.
What is a join in SQL?
A join is a method used to combine data from two or more tables in a database. It is used to retrieve data that is spread across multiple tables.
What is a subquery in SQL?
A subquery is a query that is embedded within another query. It is used to retrieve data that meets a specific condition.
What is the difference between a join and a subquery?
A join is used to combine data from two or more tables, while a subquery is used to retrieve data that meets a specific condition.
What is a self-join?
A self-join is a join where a table is joined with itself. It is used to combine data from different rows within the same table.
What is a left join?
A left join is a join that returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table.
What is a right join?
A right join is a join that returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the left table.
What is a full outer join?
A full outer join is a join that returns all the rows from both the left and right tables, including any unmatched rows. If there is no match, NULL values are returned.
What is a cross join?
A cross join is a join that returns the Cartesian product of the two tables. It returns all possible combinations of rows from both tables.
What is a group by clause?
A group by clause is used to group rows based on one or more columns in a table. It is typically used with aggregate functions like SUM, COUNT, AVG, MIN, and MAX.
What is a self-join in SQL? Provide an example.
A self-join is when a table is joined to itself. It can be used to compare data within the same table. For example, to retrieve a list of employees and their managers from the same table, we can use the following query:
SELECT e.employee_name, m.employee_name AS manager_name
FROM employee e
JOIN employee m ON e.manager_id = m.employee_id;
How do you find the second highest salary in a table?
We can use the following query to find the second highest salary in a table:
SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);
What is a subquery in SQL? Provide an example.
A subquery is a query nested inside another query. It is used to retrieve data that will be used in the main query. For example, to retrieve a list of employees whose salaries are higher than the average salary, we can use the following query:
SELECT employee_name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);
How do you find duplicates in a table?
We can use the following query to find duplicates in a table:
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
How do you delete duplicate rows in a table?
We can use the following query to delete duplicate rows in a table:
DELETE FROM table_name
WHERE column_name NOT IN (SELECT MIN(column_name) FROM table_name GROUP BY duplicate_column_name);
How do you select a random row from a table?
We can use the following query to select a random row from a table:
SELECT column_name FROM table_name ORDER BY RAND() LIMIT 1;
What is a join in SQL? Provide an example.
A join is used to combine data from two or more tables into a single result set. For example, to retrieve a list of customers and their orders, we can use the following query:
SELECT customer_name, order_number, order_date
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
What is a left join in SQL? Provide an example.
A left join is used to retrieve all rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, NULL values are returned. For example, to retrieve a list of all customers and their orders (including customers who have not placed an order), we can use the following query:
SELECT customer_name, order_number, order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
What is a right join in SQL? Provide an example.
A right join is used to retrieve all rows from the right table and the matching rows from the left table. If there are no matching rows in the left table, NULL values are returned. For example, to retrieve a list of all orders and their customers (including orders without a customer), we can use the following query:
SELECT order_number, order_date, customer_name
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.customer_id;
What is a full outer join in SQL?
A full outer join is used to retrieve all rows from both tables. If there are no matching rows in one of the tables, NULL values are returned.
What is a subquery in SQL?
A subquery is a query that is nested inside another query. It can be used to retrieve data that will be used in the main query as a condition to filter the data. Subqueries can be used in SELECT, UPDATE, DELETE, and INSERT statements.
Example:
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
What is a self-join in SQL?
A self-join is a join in which a table is joined with itself. It is useful when you have a table that contains hierarchical data, such as an organizational chart. In a self-join, you need to use table aliases to distinguish between the two copies of the same table.
Example:
SELECT e1.name, e2.name as manager_name FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;
What is the difference between the UNION and UNION ALL operators in SQL?
The UNION operator is used to combine the results of two or more SELECT statements into a single result set. It removes duplicates from the result set. On the other hand, the UNION ALL operator combines the results of two or more SELECT statements, including duplicates.
Example:
SELECT name FROM employees WHERE department_id = 1UNIONSELECT name FROM employees WHERE department_id = 2;
SELECT name FROM employees WHERE department_id = 1UNION ALLSELECT name FROM employees WHERE department_id = 2;
What is the difference between a join and a subquery in SQL?
A join is used to combine data from two or more tables based on a related column between them. A subquery is a query that is nested inside another query and can be used to retrieve data that will be used in the main query as a condition to filter the data.
What is the difference between the WHERE and HAVING clauses in SQL?
The WHERE clause is used to filter rows based on a condition applied to one or more columns, whereas the HAVING clause is used to filter groups based on a condition applied to one or more aggregate functions.
Example:
SELECT department_id, COUNT(*) as count FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
This query will return the department IDs and the number of employees in each department, but it will only return the groups where the count is greater than 5.
What is a view in SQL?
A view is a virtual table that is based on the result of a SELECT statement. It is stored in the database and can be used like a regular table. Views are useful when you need to retrieve data from multiple tables, but you don't want to create a physical table to store the result.
Example:
CREATE VIEW employee_view AS SELECT name, department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
SELECT * FROM employee_view;
What is normalization in SQL?
Normalization is a process of organizing data in a database in order to reduce redundancy and improve data integrity. It involves breaking down a table into smaller tables and defining relationships between them. Normalization helps to avoid data inconsistencies and improve query performance.
What is denormalization in SQL?
Denormalization is the process of intentionally introducing redundancy into a database in order to improve query performance. It involves adding redundant data to a table to eliminate the need for joins or subqueries.
What is a self-join and how is it performed?
A self-join is a join in which a table is joined with itself. It can be used to find relationships within a table, such as hierarchical data or to find data that has a certain relationship with itself.
For example, consider the following table named "employees":
To find the name of the manager of each employee, we can perform a self-join on the "employees" table using the "manager_id" column:
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.id;
The output would be:
What is a correlated subquery and how is it used?
A correlated subquery is a subquery that references a column from the outer query, and is used to filter data based on the outer query's results. The subquery is executed for each row returned by the outer query.
For example, consider the following "orders" and "customers" tables:
To find the order with the latest date for each customer, we can use a correlated subquery:
SELECT o.order_id, o.customer_id, o.order_date
FROM orders o
WHERE o.order_date = (
SELECT MAX(order_date)
FROM orders
WHERE customer_id = o.customer_id
);
The output would be:
order_id customer_id order_date
3 1 2022-01-03
2 2 2022-01-02
4 3 2022-01-04
What is the difference between a UNION and a UNION ALL in SQL?
Both UNION and UNION ALL are used to combine the results of two or more SELECT statements. However, there is a difference between the two:
UNION: The UNION operator is used to combine the results of two or more SELECT statements, and removes any duplicate rows. The column names in the result set are determined by the column names in the first SELECT statement.
UNION ALL: The UNION ALL operator is used to combine the results of two or more SELECT statements, and does not remove any duplicate rows. The column names in the result set are determined by the column names in the first SELECT statement.
Thanks for reading, and happy coding!