SQL EssentialsQueries, Joins & Interview Questions

1-- This is a single-line comment
226. MySQL also supports block comments.

1/* This is
2   a block comment */
327. SQL identifiers include database names, table names, column names, and aliases.
428. Avoid using reserved keywords as table or column names.
529. A table’s degree is the number of columns.
630. A table’s cardinality is the number of rows.
7The booklet’s SQL worksheets specifically test NULL, table rows and columns, WHERE, GROUP BY, DISTINCT, UNION, transactions, and database structure.Complete-Placement-Preparation-Booklet-DSA-Core-Subjects-Interview-Guide.pdf

1CREATE DATABASE company;
235. USE selects a database.

1USE company;
236. CREATE TABLE creates a table.

1CREATE TABLE employees (
2    employee_id INT PRIMARY KEY,
3    employee_name VARCHAR(50),
4    salary DECIMAL(10, 2),
5    department_id INT
6);
737. ALTER TABLE modifies the structure of an existing table.
838. Add a column:

1ALTER TABLE employees
2ADD email VARCHAR(100);
339. Modify a column in MySQL:

1ALTER TABLE employees
2MODIFY employee_name VARCHAR(100);
340. Rename a column in MySQL:

1ALTER TABLE employees
2RENAME COLUMN employee_name TO full_name;
341. Drop a column:

1ALTER TABLE employees
2DROP COLUMN email;
342. DROP TABLE removes both table structure and table data.

1DROP TABLE employees;
243. TRUNCATE TABLE removes all rows while retaining the table structure.

1TRUNCATE TABLE employees;
244. DML means Data Manipulation Language.
345. Common DML commands:
4   * INSERT.
5   * UPDATE.
6   * DELETE.
746. INSERT adds rows.
847. UPDATE modifies existing rows.
948. DELETE removes rows.
1049. DQL means Data Query Language.
1150. SELECT is the primary DQL command.
1251. DCL means Data Control Language.
1352. GRANT gives privileges.
1453. REVOKE removes privileges.
1554. TCL means Transaction Control Language.
1655. Common TCL commands:
17   * COMMIT.
18   * ROLLBACK.
19   * SAVEPOINT.

1CREATE TABLE departments (
2    department_id INT PRIMARY KEY,
3    department_name VARCHAR(50) UNIQUE NOT NULL
4);
570. Example with a foreign key:

1CREATE TABLE employees (
2    employee_id INT PRIMARY KEY,
3    employee_name VARCHAR(50) NOT NULL,
4    salary DECIMAL(10, 2) CHECK (salary >= 0),
5    department_id INT,
6    FOREIGN KEY (department_id)
7        REFERENCES departments(department_id)
8);
971. A foreign key may contain NULL if the relationship is optional.
1072. A foreign key cannot contain a non-NULL value that does not exist in the referenced table.
1173. ON DELETE CASCADE deletes dependent rows automatically.
1274. ON UPDATE CASCADE updates dependent foreign-key values automatically.
1375. ON DELETE SET NULL sets the foreign key to NULL, provided the column allows NULL.
1476. ON DELETE RESTRICT prevents deletion of a referenced row if dependent rows exist.

1INSERT INTO departments (department_id, department_name)
2VALUES (10, 'IT');
394. Insert multiple rows:

1INSERT INTO departments (department_id, department_name)
2VALUES
3    (20, 'HR'),
4    (30, 'Sales'),
5    (40, 'Finance');
695. Select all columns:

1SELECT *
2FROM departments;
396. Select specific columns:

1SELECT department_id, department_name
2FROM departments;
397. Use an alias for a column:

1SELECT department_name AS department
2FROM departments;
398. Filter rows with WHERE:

1SELECT *
2FROM employees
3WHERE salary > 50000;
499. Update rows:

1UPDATE employees
2SET salary = salary * 1.10
3WHERE department_id = 10;
4100. Always use a proper WHERE condition in UPDATE unless every row should change.
5101. Delete selected rows:

1DELETE FROM employees
2WHERE employee_id = 101;
3102. Omitting WHERE from DELETE may remove every row.
4103. SELECT does not permanently modify data.
5104. INSERT, UPDATE, and DELETE modify table data.

1SELECT *
2FROM employees
3WHERE salary > 40000
4  AND department_id = 10;
5107. OR requires at least one condition to be true.

1SELECT *
2FROM employees
3WHERE department_id = 10
4   OR department_id = 20;
5108. Use parentheses to control logical precedence.

1SELECT *
2FROM employees
3WHERE department_id = 10
4  AND (salary > 40000 OR employee_name = 'Amit');
5109. IN checks membership in a list.

1SELECT *
2FROM employees
3WHERE department_id IN (10, 20, 30);
4110. NOT IN excludes values from a list.
5111. BETWEEN checks an inclusive range.

1SELECT *
2FROM employees
3WHERE salary BETWEEN 30000 AND 60000;
4112. BETWEEN includes both boundary values.
5113. LIKE performs pattern matching.
6114. % represents zero or more characters.
7115. _ represents exactly one character.

1SELECT *
2FROM employees
3WHERE employee_name LIKE 'A%';
4This returns names beginning with A.

1SELECT *
2FROM employees
3WHERE employee_name LIKE '_mit';
4This may match Amit.
5116. IS NULL checks for missing values.

1SELECT *
2FROM employees
3WHERE department_id IS NULL;
4117. IS NOT NULL checks for available values.
5118. CASE performs conditional logic.

1SELECT employee_name,
2       CASE
3           WHEN salary >= 80000 THEN 'High'
4           WHEN salary >= 50000 THEN 'Medium'
5           ELSE 'Low'
6       END AS salary_level
7FROM employees;