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.

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.

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.

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.

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.

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.

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'.

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.

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.

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.

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.

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;

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;

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);

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.

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.