Basic SQL Interview Questions Select All Columns, Select Specific Columns, Where Clause, AND etc

Опубликовано: 14 Июнь 2024
на канале: CodeWis Technologies by Nuhman Paramban

Basic SQL Interview Questions
Select All Columns, Select Specific Columns, Where Clause, AND, OR, NOT, Order By, Insert Into, Update, Delete, Aggregate, Group By, Having, Join, Left Join, Subquery, Case Statement, Union, intersect, Except, Limit, Offset, Self join

1. Create Employee table and Department table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
salary DECIMAL(10 , 2 )
);

1. Create Employee table and Department table
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);

1. Select All Columns from a Table
SELECT * FROM employees;

2. Select Specific Columns from a Table
SELECT employee_id, first_name, last_name FROM employees;

3. Where Clause
Select employees from the "employees" table where the last name is "Smith".
SELECT * FROM employees WHERE last_name = 'Smith';

4. AND, OR, and NOT Operators
Select employees who are in the "Sales" department and have a salary greater than 50000.
SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

5. Order By
Select all employees and sort the results by the last name in ascending order.
SELECT * FROM employees ORDER BY last_name ASC;

6. Insert Into
Insert a new employee into the "employees" table.
INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES (123, 'John', 'Doe', 'Engineering', 60000);

7. Update
Update the salary of the employee with employee_id 123 to 65000.
UPDATE employees SET salary = 65000 WHERE employee_id = 123;

8. Delete
Delete the employee with employee_id 123.
DELETE FROM employees WHERE employee_id = 123;

9. Aggregate Functions
Calculate the average salary of all employees.
SELECT AVG(salary) FROM employees;

10. Group By
Find the number of employees in each department.
SELECT department, COUNT(*) as num_employees FROM employees GROUP BY department;

11. Having Clause
Find departments with more than 10 employees.
SELECT department, COUNT(*) as num_employees FROM employees GROUP BY department HAVING COUNT(*) > 10;

12. Join
Get a list of employees along with their department names. Assume there is a "departments" table with "department_id" and "department_name".
SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

13. Left Join
Get a list of all employees along with their department names, including employees who do not have a department.
SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

14. Subquery
Select employees who have the highest salary.
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

15. Case Statement
Assign a grade to each employee based on their salary: 'A' for salary >= 70000, 'B' for salary between 50000 and 69999, 'C' for salary < 50000.
SELECT employee_id, first_name, last_name, salary, CASE WHEN salary >= 70000 THEN 'A' WHEN salary BETWEEN 50000 AND 69999 THEN 'B' ELSE 'C' END as grade FROM employees;

16. Union
Combine results from two queries: one that selects all employees from the "employees" table and another that selects all managers from the "managers" table.
SELECT employee_id, first_name, last_name FROM employees UNION SELECT manager_id as employee_id, first_name, last_name FROM managers;

17. Intersect
Find the common employee IDs between two tables: "current_employees" and "former_employees".
SELECT employee_id FROM current_employees INTERSECT SELECT employee_id FROM former_employees;

18. Except
Find employee IDs that are in "current_employees" but not in "former_employees".
SELECT employee_id FROM current_employees EXCEPT SELECT employee_id FROM former_employees;

19. Limit and Offset
Select the first 10 employees from the "employees" table.
SELECT * FROM employees LIMIT 10;

Select the next 10 employees after skipping the first 10.
SELECT * FROM employees LIMIT 10 OFFSET 10;

20. Self Join
Find pairs of employees who work in the same department.
SELECT e1.employee_id, e1.first_name, e1.last_name, e2.employee_id, e2.first_name, e2.last_name FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id AND e1.employee_id <> e2.employee_id;

Смотрите видео Basic SQL Interview Questions Select All Columns, Select Specific Columns, Where Clause, AND etc онлайн, длительностью часов минут секунд в хорошем качестве, которое загружено на канал CodeWis Technologies by Nuhman Paramban 14 Июнь 2024. Делитесь ссылкой на видео в социальных сетях, чтобы ваши подписчики и друзья так же посмотрели это видео. Данный видеоклип посмотрели 34 раз и оно понравилось 2 посетителям.

373