SQL Interview Preparation

Overview

• SQL is a crucial topic for technical interviews, especially for roles like TCS Digital Ninja Prime or SQL Developer.
• This guide covers top 70 SQL questions, curated from previous interviews from both service-based and product-based companies.
• Key areas include data manipulation, querying, joins, and subqueries.

Basic Commands

Switching and Listing Databases and Tables

• Switch Database: USE IMDb; selects the IMDb database.
• List Tables: SHOW TABLES; lists all tables in the current database.
• Describe Table: DESCRIBE movies; displays the schema of the movies table.

Querying Data

Basic SELECT Statements

• Selecting All Columns: SELECT * FROM movies; selects all columns from the movies table.
• Selecting Specific Columns: SELECT name, year FROM movies; selects only the name and year columns.*

Filtering with WHERE Clause

• Condition-based Selection: SELECT * FROM movies WHERE rank_score > 9; selects movies with a rank_score greater than 9.
• Combining Conditions: SELECT * FROM movies WHERE rank_score > 9 AND year > 2000; selects movies with a rank_score greater than 9 and released after the year 2000.

Using LIMIT and OFFSET

• Limit Results: SELECT * FROM movies LIMIT 20; limits the result to the first 20 rows.
• Offset Results: SELECT * FROM movies OFFSET 40 LIMIT 20; starts results at the 41st row and limits to 20 rows.

Sorting with ORDER BY

• Most Recent Movies: SELECT * FROM movies ORDER BY year DESC LIMIT 10; gets the top 10 most recent movies.
• Oldest Movies: SELECT * FROM movies ORDER BY year ASC LIMIT 10; gets the top 10 oldest movies.

Removing Duplicates with DISTINCT

• Unique Values: SELECT DISTINCT genre FROM movie_genres; lists all unique genres from the movie_genres table.
• Unique Combinations: SELECT DISTINCT first_name, last_name FROM directors; lists unique combinations of first and last names from directors.

Pattern Matching with LIKE

• Starting with Pattern: SELECT * FROM movies WHERE name LIKE 'T%'; selects movies whose names start with 'T'.
• Ending with Pattern: SELECT * FROM actors WHERE first_name LIKE '%es'; selects actors whose first name ends with 'es'.
• Contains Pattern: SELECT * FROM actors WHERE first_name LIKE '%es%'; selects actors whose first name contains 'es'.*

Aggregate Functions

• Finding Minimum and Maximum: SELECT MIN(year), MAX(year) FROM movies; finds the earliest and most recent years of movie releases.
• Count Rows: SELECT COUNT(*) FROM movies; counts the total number of rows in the movies table.*

Grouping Data with GROUP BY and HAVING

• Group By Year: SELECT year, COUNT(*) FROM movies GROUP BY year; counts movies released each year.
• Having Clause: SELECT year, COUNT(*) FROM movies GROUP BY year HAVING COUNT(*) > 1000; finds years with more than 1000 movies.
• Combining Conditions: SELECT year, COUNT(*) FROM movies WHERE rank_score > 9 GROUP BY year HAVING COUNT(*) > 20; finds years with more than 20 movies that have a rank score greater than 9.*

Joining Tables

• Inner Join: SELECT m.name, g.genre FROM movies m INNER JOIN movie_genres g ON m.id = g.movie_id; joins movies and movie_genres tables on movie ID.
• Left Join: SELECT m.name, g.genre FROM movies m LEFT JOIN movie_genres g ON m.id = g.movie_id; includes all movies, whether they have genres or not.
• Using Common Columns: SELECT * FROM t1 INNER JOIN t2 ON t1.common_col = t2.common_col; joins t1 and t2 tables using common columns.*

Subqueries

• Subquery Example: SELECT * FROM actors WHERE id IN (SELECT actor_id FROM roles WHERE movie_id IN (SELECT id FROM movies WHERE name = 'Schindler's List')); nested query to find actors in a specific movie.
• Exists Clause: SELECT * FROM movies WHERE EXISTS (SELECT 1 FROM roles WHERE movies.id = roles.movie_id AND rank_score = (SELECT MAX(rank_score) FROM movies)); checks if the movie exists with the highest rank score.

DML (Data Manipulation Language)

• Insert Single Row: INSERT INTO movies (id, name, year) VALUES (1, 'Thor', 2011);
• Insert Multiple Rows: INSERT INTO movies (id, name, year) VALUES (1, 'Thor', 2011), (2, 'Avengers', 2012);
• Copy Rows: INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE condition;
• Update Rows: UPDATE movies SET rank_score = 9 WHERE id = 412321;
• Delete Particular Row: DELETE FROM movies WHERE id = 412321;
• Delete All Rows: TRUNCATE TABLE movies;

DDL (Data Definition Language)

• Create Table: CREATE TABLE movies (id INT PRIMARY KEY, name VARCHAR(100), year INT);
• Alter Table: ALTER TABLE movies ADD COLUMN genre VARCHAR(50);, ALTER TABLE movies MODIFY COLUMN genre VARCHAR(100);, ALTER TABLE movies DROP COLUMN genre;
• Drop Table: DROP TABLE IF EXISTS movies;

Constraints

• Ensure Unique Values: ALTER TABLE users ADD CONSTRAINT UNIQUE (email);
• Not Null Constraint: ALTER TABLE users MODIFY COLUMN name VARCHAR(100) NOT NULL;
• Foreign Key Constraint: ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (id);

Conclusion

• SQL covers a wide array of functionalities essential for technical roles in software engineering.
• Above key points give a broad and comprehensive understanding required to crack SQL-related interview questions.

Tips

• Practice writing SQL queries based on given question statements.
• Understand the flow of query execution, especially in nested and join operations.
• Revise basic theory related to SQL and DML/DDL operations.

Additional Resources

• W3Schools for SQL syntax reference
• LeetCode for SQL practice questions