Interview Questions
The basic structure of a SELECT statement is SELECT columns FROM table [WHERE condition]. It allows you to retrieve data from one or more tables, with SELECT specifying which columns to retrieve, FROM indicating the table, and WHERE (optional) filtering the results.
To select all columns from a table, use the asterisk (*) wildcard in the SELECT statement. For example: SELECT * FROM table_name. This retrieves all columns and rows from the specified table.
The WHERE clause is used to filter records based on specific conditions. It allows you to retrieve only the rows that meet the specified criteria, reducing the amount of data returned and helping to pinpoint exact information.
SQL comparison operators include: = (equal), <> or != (not equal), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), BETWEEN (range), LIKE (pattern matching), IN (multiple possible values), and IS NULL (null value check).
The DISTINCT keyword is used to remove duplicate rows from the result set. For example: SELECT DISTINCT column_name FROM table_name. It returns only unique values in the specified column(s).
The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order. For example: SELECT * FROM table_name ORDER BY column_name DESC. By default, sorting is in ascending order if not specified.
The LIMIT clause restricts the number of rows returned in a query result. For example: SELECT * FROM table_name LIMIT 10 returns only the first 10 rows. Some databases use TOP or FETCH FIRST instead of LIMIT.
AND requires all conditions to be true, while OR requires at least one condition to be true. For example, WHERE age > 30 AND salary < 50000 returns rows meeting both conditions, while WHERE age > 30 OR salary < 50000 returns rows meeting either condition.
The LIKE operator is used for pattern matching with wildcard characters. % represents zero or more characters, _ represents a single character. For example: WHERE name LIKE 'A%' finds names starting with A, WHERE name LIKE '_oh%' finds names with second and third letters 'oh'.
The IN operator allows you to specify multiple values in a WHERE clause. It provides a shorthand for multiple OR conditions. For example: WHERE column_name IN (value1, value2, value3) is equivalent to WHERE column_name = value1 OR column_name = value2 OR column_name = value3.
The BETWEEN operator selects values within a given range. It is inclusive of both boundary values. For example: WHERE age BETWEEN 20 AND 30 returns rows where age is 20, 30, or any value in between.
NULL values are handled using IS NULL and IS NOT NULL operators. For example: WHERE column_name IS NULL finds rows with null values, while WHERE column_name IS NOT NULL finds rows with non-null values. Standard comparison operators do not work with NULL.
CHAR is a fixed-length string type that pads shorter strings with spaces, while VARCHAR is a variable-length string type that only uses the space needed. CHAR(10) always uses 10 characters, but VARCHAR(10) can use 1 to 10 characters.
Aliases provide alternative names for tables or columns in a query. Column aliases use AS keyword: SELECT first_name AS name. Table aliases are used for readability and in joins: FROM employees AS e.
A subquery is a query nested inside another query. It can be used in SELECT, FROM, WHERE, and HAVING clauses. For example: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) returns employees with above-average salary.
Common SQL data types include: INTEGER/INT (whole numbers), DECIMAL/NUMERIC (precise decimal numbers), FLOAT/REAL (approximate numeric), CHAR/VARCHAR (fixed/variable strings), DATE/DATETIME (date and time values), BOOLEAN (true/false), and BLOB (binary large objects).
The GROUP BY clause groups rows with the same values in specified columns into summary rows. It is typically used with aggregate functions like COUNT, SUM, AVG. For example: SELECT department, AVG(salary) FROM employees GROUP BY department calculates average salary per department.
The HAVING clause filters groups created by GROUP BY, similar to WHERE but applied after grouping. For example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000 shows departments with average salary above 50000.
Aggregate functions perform calculations on a set of values and return a single result. Common aggregate functions include COUNT() (number of rows), SUM() (total), AVG() (average), MAX() (maximum value), and MIN() (minimum value).
The CASE statement allows conditional logic in queries. It works like an IF-THEN-ELSE statement. Example: SELECT name, CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_category FROM employees.
WHERE filters individual rows before grouping, while HAVING filters groups after grouping. WHERE works with individual row conditions, HAVING works with aggregate function conditions in grouped queries.
Common string functions include CONCAT() (combine strings), SUBSTRING() (extract part of string), LENGTH() (string length), UPPER() (uppercase), LOWER() (lowercase), TRIM() (remove spaces), and REPLACE() (replace substring).
SQL provides functions for date and time manipulation like CURRENT_DATE, DATE_ADD(), DATE_SUB(), DATEDIFF() to perform calculations and comparisons. Specific functions vary between database systems.
SQL supports two types of comments: single-line comments (-- comment text) and multi-line comments (/* comment text */). Comments are used to explain code and are ignored by the database engine.
Wildcard characters are used with LIKE operator: % matches zero or more characters, _ matches a single character, [] matches any single character in brackets, [^] matches any character not in brackets. They enable flexible pattern matching in queries.
DELETE removes rows one at a time and can be rolled back, TRUNCATE removes all rows at once and cannot be rolled back. DELETE can use a WHERE clause, TRUNCATE removes all data from a table.
Set operators like UNION (removes duplicates), UNION ALL (includes duplicates), INTERSECT (common rows), and EXCEPT (rows in first query not in second) combine results from multiple SELECT statements.
The typical SQL query execution order is: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. This means conditions are applied before grouping, aggregations, sorting, and limiting results.
The basic structure of a SELECT statement is SELECT columns FROM table [WHERE condition]. It allows you to retrieve data from one or more tables, with SELECT specifying which columns to retrieve, FROM indicating the table, and WHERE (optional) filtering the results.
To select all columns from a table, use the asterisk (*) wildcard in the SELECT statement. For example: SELECT * FROM table_name. This retrieves all columns and rows from the specified table.
The WHERE clause is used to filter records based on specific conditions. It allows you to retrieve only the rows that meet the specified criteria, reducing the amount of data returned and helping to pinpoint exact information.
SQL comparison operators include: = (equal), <> or != (not equal), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), BETWEEN (range), LIKE (pattern matching), IN (multiple possible values), and IS NULL (null value check).
The DISTINCT keyword is used to remove duplicate rows from the result set. For example: SELECT DISTINCT column_name FROM table_name. It returns only unique values in the specified column(s).
The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order. For example: SELECT * FROM table_name ORDER BY column_name DESC. By default, sorting is in ascending order if not specified.
The LIMIT clause restricts the number of rows returned in a query result. For example: SELECT * FROM table_name LIMIT 10 returns only the first 10 rows. Some databases use TOP or FETCH FIRST instead of LIMIT.
AND requires all conditions to be true, while OR requires at least one condition to be true. For example, WHERE age > 30 AND salary < 50000 returns rows meeting both conditions, while WHERE age > 30 OR salary < 50000 returns rows meeting either condition.
The LIKE operator is used for pattern matching with wildcard characters. % represents zero or more characters, _ represents a single character. For example: WHERE name LIKE 'A%' finds names starting with A, WHERE name LIKE '_oh%' finds names with second and third letters 'oh'.
The IN operator allows you to specify multiple values in a WHERE clause. It provides a shorthand for multiple OR conditions. For example: WHERE column_name IN (value1, value2, value3) is equivalent to WHERE column_name = value1 OR column_name = value2 OR column_name = value3.
The BETWEEN operator selects values within a given range. It is inclusive of both boundary values. For example: WHERE age BETWEEN 20 AND 30 returns rows where age is 20, 30, or any value in between.
NULL values are handled using IS NULL and IS NOT NULL operators. For example: WHERE column_name IS NULL finds rows with null values, while WHERE column_name IS NOT NULL finds rows with non-null values. Standard comparison operators do not work with NULL.
CHAR is a fixed-length string type that pads shorter strings with spaces, while VARCHAR is a variable-length string type that only uses the space needed. CHAR(10) always uses 10 characters, but VARCHAR(10) can use 1 to 10 characters.
Aliases provide alternative names for tables or columns in a query. Column aliases use AS keyword: SELECT first_name AS name. Table aliases are used for readability and in joins: FROM employees AS e.
A subquery is a query nested inside another query. It can be used in SELECT, FROM, WHERE, and HAVING clauses. For example: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) returns employees with above-average salary.
Common SQL data types include: INTEGER/INT (whole numbers), DECIMAL/NUMERIC (precise decimal numbers), FLOAT/REAL (approximate numeric), CHAR/VARCHAR (fixed/variable strings), DATE/DATETIME (date and time values), BOOLEAN (true/false), and BLOB (binary large objects).
The GROUP BY clause groups rows with the same values in specified columns into summary rows. It is typically used with aggregate functions like COUNT, SUM, AVG. For example: SELECT department, AVG(salary) FROM employees GROUP BY department calculates average salary per department.
The HAVING clause filters groups created by GROUP BY, similar to WHERE but applied after grouping. For example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000 shows departments with average salary above 50000.
Aggregate functions perform calculations on a set of values and return a single result. Common aggregate functions include COUNT() (number of rows), SUM() (total), AVG() (average), MAX() (maximum value), and MIN() (minimum value).
The CASE statement allows conditional logic in queries. It works like an IF-THEN-ELSE statement. Example: SELECT name, CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_category FROM employees.
WHERE filters individual rows before grouping, while HAVING filters groups after grouping. WHERE works with individual row conditions, HAVING works with aggregate function conditions in grouped queries.
Common string functions include CONCAT() (combine strings), SUBSTRING() (extract part of string), LENGTH() (string length), UPPER() (uppercase), LOWER() (lowercase), TRIM() (remove spaces), and REPLACE() (replace substring).
SQL provides functions for date and time manipulation like CURRENT_DATE, DATE_ADD(), DATE_SUB(), DATEDIFF() to perform calculations and comparisons. Specific functions vary between database systems.
SQL supports two types of comments: single-line comments (-- comment text) and multi-line comments (/* comment text */). Comments are used to explain code and are ignored by the database engine.
Wildcard characters are used with LIKE operator: % matches zero or more characters, _ matches a single character, [] matches any single character in brackets, [^] matches any character not in brackets. They enable flexible pattern matching in queries.
DELETE removes rows one at a time and can be rolled back, TRUNCATE removes all rows at once and cannot be rolled back. DELETE can use a WHERE clause, TRUNCATE removes all data from a table.
Set operators like UNION (removes duplicates), UNION ALL (includes duplicates), INTERSECT (common rows), and EXCEPT (rows in first query not in second) combine results from multiple SELECT statements.
The typical SQL query execution order is: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. This means conditions are applied before grouping, aggregations, sorting, and limiting results.