60 Comprehensive SQL and Best Practices for Interview and Upskilling

1. SQL Keywords

• Case-Insensitive: SQL keywords like SELECT, FROM, WHERE, CREATE, etc., are not case-
sensitive. However, it's common practice to write them in uppercase for readability.

• Common Keywords: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, JOIN, WHERE,
ORDER BY, GROUP BY, HAVING, etc.

2. Statements End with a Semicolon (;)

• Semicolon: SQL statements should be terminated with a semicolon to indicate the end of the
command, especially in databases like PostgreSQL, MySQL, and Oracle.

SELECT * FROM students;

3. Table and Column Names

• Alphanumeric: Table and column names can contain letters, numbers, and underscores (_).

• Start with a Letter: They should start with a letter (not a number or symbol).

• Avoid Reserved Words: Do not use SQL reserved words as names unless enclosed in quotes.

• Case Sensitivity: Table and column names are usually case-insensitive, but it’s good practice
to stick to a consistent case (typically lowercase).

4. String Literals

• Single Quotes: Strings are enclosed in single quotes (' '). Double quotes are used for
identifiers in some databases.

SELECT * FROM students WHERE name = 'John';

5. Data Types

• Match the Data: Use appropriate data types like INTEGER, VARCHAR, CHAR, DATE, FLOAT,
etc., based on the data you expect to store.

• Character Data: VARCHAR for variable-length strings, CHAR for fixed-length strings.

• Numeric Data: INTEGER, FLOAT, DECIMAL, etc.

• Date and Time: DATE, TIME, TIMESTAMP.

[Link]
6. Constraints

• Primary Key: Uniquely identifies a row in the table.

• Foreign Key: References a primary key in another table.

• Not Null: Ensures that a column cannot have a NULL value.

• Unique: Ensures all values in a column are unique.

• Check: Ensures that values in a column meet a specific condition.

CREATE TABLE students

rollno INTEGER PRIMARY KEY,

name VARCHAR(200) NOT NULL,

department VARCHAR(200),

gender CHAR(1) CHECK (gender IN ('M', 'F'))

);

7. Comments

• Single-Line Comment: Use -- for single-line comments.

• Multi-Line Comment: Use /* ... */ for multi-line comments.

-- This is a single-line comment

/* This is a

multi-line comment */

8. Joins

• INNER JOIN: Returns rows when there is a match in both tables.

• LEFT JOIN: Returns all rows from the left table and matched rows from the right table.

• RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.

• FULL JOIN: Returns rows when there is a match in one of the tables.

SELECT [Link], departments.dept_name

FROM students

INNER JOIN departments ON [Link] = [Link];

[Link]
9. Order of Execution

• SQL Statement Order: The typical order of SQL command execution is:

1. FROM clause

2. WHERE clause

3. GROUP BY clause

4. HAVING clause

5. SELECT clause

6. ORDER BY clause

7. LIMIT clause

10. White Space and Formatting

• Ignored: SQL ignores extra spaces and line breaks.

• Readable Formatting: Use indentation and line breaks to make queries more readable.

SELECT name, department

FROM students

WHERE gender = 'M'

ORDER BY name;

11. Operators

• Arithmetic: +, -, *, /

• Comparison: =, !=, <, >, <=, >=

• Logical: AND, OR, NOT

SELECT * FROM students WHERE rollno >= 100 AND department = 'CSE';

12. Aliases

• Use for Renaming: Use AS to give a table or column an alias, making the query easier to
read.

SELECT name AS student_name FROM students AS s;

[Link]
13. Subqueries

• Nested Queries: You can use subqueries (queries within queries) to perform complex
operations.

SELECT name FROM students WHERE rollno IN (SELECT rollno FROM enrollments WHERE course =
'Math');

14. Case Statements

• Conditional Logic: Use CASE for conditional expressions.

SELECT name,

CASE

WHEN gender = 'M' THEN 'Male'

WHEN gender = 'F' THEN 'Female'

ELSE 'Other'

END AS gender_desc

FROM students;

15. Handling NULL Values

• IS NULL / IS NOT NULL: Use these to check for NULL values.

SELECT name FROM students WHERE department IS NULL;

16. Grouping and Aggregation

• GROUP BY: Used with aggregate functions (COUNT, SUM, AVG, MAX, MIN) to group rows.

• HAVING: Used to filter groups after aggregation.

SELECT department, COUNT(*) FROM students GROUP BY department HAVING COUNT(*) > 5;

17. Indexes

• Performance: Indexes speed up queries but can slow down INSERT, UPDATE, and DELETE
operations.

CREATE INDEX idx_department ON students(department);

[Link]
18. Views

• Virtual Table: A view is a saved query that can be treated as a table.

CREATE VIEW student_names AS SELECT name FROM students;

19. Transactions

• Atomic Operations: Use BEGIN, COMMIT, and ROLLBACK to control [Link];

INSERT INTO students (rollno, name, department) VALUES (101, 'Alice', 'CSE');

COMMIT;

20. Permissions

• Granting and Revoking Access: Control access to tables and other database objects.

GRANT SELECT ON students TO user_name;

REVOKE INSERT ON students FROM user_name;

Best Practices

• Consistent Naming Conventions: Use consistent and descriptive names for tables and
columns.

• Avoid * in SELECT: Avoid using SELECT *; instead, specify the columns you need.

• Backup and Test: Always backup your database and test queries in a development
environment before running them in production.

21. Normalization

• Normalization: This is the process of organizing data in a database to reduce redundancy and
improve data integrity. The key forms of normalization are:

o 1NF (First Normal Form): Ensure each column contains atomic (indivisible) values
and that each column contains only one type of data.

o 2NF (Second Normal Form): Ensure the table is in 1NF and that all non-key attributes
are fully functionally dependent on the primary key.

o 3NF (Third Normal Form): Ensure the table is in 2NF and that all attributes are
dependent only on the primary key, not on other non-key attributes.

[Link]
22. Denormalization

• Denormalization: In some cases, for performance reasons, data may be denormalized

23. Partitioning

• Partitioning: Split a large table into smaller, more manageable pieces (partitions) based on a
column value (e.g., date). Common types include:

o Range Partitioning: Divide rows based on a range of values (e.g., by year).

o List Partitioning: Divide rows based on specific values in a column.

o Hash Partitioning: Rows are assigned to partitions based on a hash function.

CREATE TABLE sales (

sale_id INT,

sale_date DATE,

amount DECIMAL(10,2)

) PARTITION BY RANGE (YEAR(sale_date)) (

PARTITION p_2023 VALUES LESS THAN (2024),

PARTITION p_2024 VALUES LESS THAN (2025)

);

24. Advanced Indexing

• Composite Indexes: Create indexes on multiple columns to improve performance for queries
involving those columns.

• Unique Indexes: Enforce uniqueness on columns other than the primary key.

• Full-Text Indexing: Allows for efficient text searching in large text fields.

CREATE INDEX idx_name_dept ON students(name, department);

[Link]
25. Stored Procedures

• Stored Procedures: Precompiled SQL code that can be executed as a single command. Useful
for encapsulating logic and reducing network traffic.

CREATE PROCEDURE AddStudent(IN rollno INT, IN name VARCHAR(200), IN department

BEGIN

INSERT INTO students (rollno, name, department, gender) VALUES (rollno, name, department,
gender);

END;

26. Triggers

• Triggers: Special procedures that are automatically executed in response to certain events on
a table (e.g., INSERT, UPDATE, DELETE).

CREATE TRIGGER before_student_insert

BEFORE INSERT ON students

FOR EACH ROW

SET [Link] = UPPER([Link]);

27. Views (Advanced)

• Updatable Views: In some databases, views can be updatable, meaning you can INSERT,
UPDATE, or DELETE data through the view.

• Materialized Views: Unlike regular views, a materialized view stores the result set of a query
physically, allowing for faster access but requiring manual or automatic refreshes.

CREATE MATERIALIZED VIEW student_view AS

SELECT name, department FROM students;

[Link]
28. Advanced Joins

• Self Join: A join where a table is joined with itself. Useful for hierarchical or recursive data.

SELECT [Link], [Link] AS mentor_name

FROM students a

INNER JOIN students b ON a.mentor_id = [Link];

• Cross Join: Returns the Cartesian product of two tables.

SELECT * FROM students CROSS JOIN departments;

• Natural Join: A join based on columns with the same name in both tables, automatically
using them as join [Link] * FROM students NATURAL JOIN departments;

29. Window Functions

• Window Functions: Perform calculations across a set of table rows related to the current
row, but unlike aggregate functions, do not group rows into a single output row.

• Common Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), etc.

SELECT name, department,

ROW_NUMBER() OVER (PARTITION BY department ORDER BY rollno) AS dept_rank

FROM students;

30. Common Table Expressions (CTEs)

• CTEs: Temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or
DELETE statement.

• Recursive CTEs: Useful for hierarchical data like organizational charts or tree structures.

WITH dept_count AS (

SELECT department, COUNT(*) AS num_students

FROM students

GROUP BY department

SELECT * FROM dept_count WHERE num_students > 10;

[Link]