SQL in 15 days Flashcards
Udemy course.
What is a subquery in SQL?
A subquery is a query nested inside another query.
What is the syntax for a subquery in SELECT?
SELECT column1, (SELECT column2 FROM table2 WHERE condition) FROM table1;
What is the syntax for a subquery in WHERE?
SELECT column1 FROM table1 WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
What is the syntax for a subquery in FROM?
SELECT subquery_table.column1 FROM (SELECT column1 FROM table1) AS subquery_table;
What is a correlated subquery?
A correlated subquery is a subquery that depends on values from the outer query.
What is the syntax for a correlated subquery?
SELECT column1 FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);
What is the UNION operator used for?
UNION combines results from two SELECT statements, removing duplicates.
What is the difference between UNION and UNION ALL?
UNION removes duplicates, while UNION ALL includes all records.
What is the syntax for an INNER JOIN?
SELECT table1.column, table2.column FROM table1 INNER JOIN table2 ON table1.id = table2.id;
What is an OUTER JOIN?
An OUTER JOIN returns matching rows plus unmatched rows from one or both tables.
What is the difference between LEFT and RIGHT JOIN?
LEFT JOIN returns all records from the left table and matching records from the right. RIGHT JOIN does the opposite.
What is the syntax for a LEFT JOIN?
SELECT column1 FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
What is the syntax for a RIGHT JOIN?
SELECT column1 FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
How do you join multiple tables?
Use multiple JOIN clauses: SELECT column FROM table1 INNER JOIN table2 ON condition INNER JOIN table3 ON condition;
How do you join with multiple conditions?
Use AND: SELECT column FROM table1 JOIN table2 ON table1.id = table2.id AND table1.date = table2.date;
What does the REPLACE function do?
REPLACE substitutes a string with another: SELECT REPLACE(‘text’, ‘e’, ‘a’);
What does CAST do in SQL?
CAST converts data types: SELECT CAST(column AS datatype) FROM table;
What does COALESCE do?
COALESCE returns the first non-null value: SELECT COALESCE(NULL, ‘default’);
What is the syntax for a CASE statement?
SELECT CASE WHEN condition THEN result ELSE default END FROM table;
How does CASE work with SUM?
SELECT SUM(CASE WHEN condition THEN value ELSE 0 END) FROM table;
What are common mathematical operators in SQL?
+, -, *, /, % are commonly used for arithmetic calculations.
What does the LENGTH function do?
LENGTH returns the number of characters in a string.
How do you convert text to lowercase?
Use LOWER: SELECT LOWER(‘TEXT’);
How do you convert text to uppercase?
Use UPPER: SELECT UPPER(‘text’);
What does the LEFT function do?
Returns the leftmost characters: SELECT LEFT(‘text’, 2);
What does the RIGHT function do?
Returns the rightmost characters: SELECT RIGHT(‘text’, 2);
How do you concatenate strings?
Use CONCAT: SELECT CONCAT(‘Hello’, ‘ ‘, ‘World’);
How do you concatenate strings using ||?
SELECT ‘Hello’ || ‘ ‘ || ‘World’; (PostgreSQL, Oracle)
What does POSITION do in SQL?
Finds a substring position: SELECT POSITION(‘x’ IN ‘text’);
What does SUBSTRING do?
Extracts part of a string: SELECT SUBSTRING(‘text’ FROM 2 FOR 2);
What does EXTRACT do?
Extracts parts of a date: SELECT EXTRACT(YEAR FROM date_column);
How do you format a date as text?
Use TO_CHAR: SELECT TO_CHAR(date_column, ‘YYYY-MM-DD’);
What are intervals in SQL?
Intervals represent time spans: SELECT CURRENT_DATE + INTERVAL ‘1 year’;
What are aggregate functions?
Functions that compute a value over a set of rows: SUM, COUNT, AVG, MIN, MAX.
How do you group data in SQL?
Use GROUP BY: SELECT column, COUNT(*) FROM table GROUP BY column;
What is the HAVING clause used for?
HAVING filters aggregated data: SELECT column, COUNT() FROM table GROUP BY column HAVING COUNT() > 5;
What is the difference between WHERE and HAVING?
WHERE filters before aggregation; HAVING filters after aggregation.
What does BETWEEN do?
Checks if a value is in a range: SELECT * FROM table WHERE column BETWEEN 10 AND 20;
What does IN do?
Checks if a value matches a list: SELECT * FROM table WHERE column IN (1, 2, 3);
What does LIKE do?
LIKE searches for patterns: SELECT * FROM table WHERE column LIKE ‘A%’;
What is an alias in SQL?
An alias renames a column or table: SELECT column AS alias_name FROM table;
How do you add comments in SQL?
– for single-line comments, /* */ for multi-line.
What does LIMIT do?
LIMIT restricts the number of results: SELECT * FROM table LIMIT 10;
What does DISTINCT do?
DISTINCT removes duplicate values: SELECT DISTINCT column FROM table;
What is the basic structure of a SELECT statement?
SELECT columns FROM table WHERE condition ORDER BY column;
How do you count rows in a table?
Use COUNT: SELECT COUNT(*) FROM table;
What does ORDER BY do?
Sorts results: SELECT * FROM table ORDER BY column ASC/DESC;
What is a subquery in SQL?
A subquery is a query nested inside another query.
What is the syntax for a subquery in SELECT?
SELECT column1, (SELECT column2 FROM table2 WHERE condition) FROM table1;
What is the syntax for a subquery in WHERE?
SELECT column1 FROM table1 WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
What is the syntax for a subquery in FROM?
SELECT subquery_table.column1 FROM (SELECT column1 FROM table1) AS subquery_table;
What is a correlated subquery?
A correlated subquery is a subquery that depends on values from the outer query.
What is the syntax for a correlated subquery?
SELECT column1 FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);
What is the UNION operator used for?
UNION combines results from two SELECT statements, removing duplicates.
What is the difference between UNION and UNION ALL?
UNION removes duplicates, while UNION ALL includes all records.
What is the syntax for an INNER JOIN?
SELECT table1.column, table2.column FROM table1 INNER JOIN table2 ON table1.id = table2.id;
What is an OUTER JOIN?
An OUTER JOIN returns matching rows plus unmatched rows from one or both tables.
What is the difference between LEFT and RIGHT JOIN?
LEFT JOIN returns all records from the left table and matching records from the right. RIGHT JOIN does the opposite.
What is the syntax for a LEFT JOIN?
SELECT column1 FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
What is the syntax for a RIGHT JOIN?
SELECT column1 FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
How do you join multiple tables?
Use multiple JOIN clauses: SELECT column FROM table1 INNER JOIN table2 ON condition INNER JOIN table3 ON condition;
How do you join with multiple conditions?
Use AND: SELECT column FROM table1 JOIN table2 ON table1.id = table2.id AND table1.date = table2.date;
What does the REPLACE function do?
REPLACE substitutes a string with another: SELECT REPLACE(‘text’, ‘e’, ‘a’);
What does CAST do in SQL?
CAST converts data types: SELECT CAST(column AS datatype) FROM table;
What does COALESCE do?
COALESCE returns the first non-null value: SELECT COALESCE(NULL, ‘default’);
What is the syntax for a CASE statement?
SELECT CASE WHEN condition THEN result ELSE default END FROM table;
How does CASE work with SUM?
SELECT SUM(CASE WHEN condition THEN value ELSE 0 END) FROM table;
What are common mathematical operators in SQL?
+, -, *, /, % are commonly used for arithmetic calculations.
What does the LENGTH function do?
LENGTH returns the number of characters in a string.
How do you convert text to lowercase?
Use LOWER: SELECT LOWER(‘TEXT’);
How do you convert text to uppercase?
Use UPPER: SELECT UPPER(‘text’);
What does the LEFT function do?
Returns the leftmost characters: SELECT LEFT(‘text’, 2);
What does the RIGHT function do?
Returns the rightmost characters: SELECT RIGHT(‘text’, 2);
How do you concatenate strings?
Use CONCAT: SELECT CONCAT(‘Hello’, ‘ ‘, ‘World’);
How do you concatenate strings using ||?
SELECT ‘Hello’ || ‘ ‘ || ‘World’; (PostgreSQL, Oracle)
What does POSITION do in SQL?
Finds a substring position: SELECT POSITION(‘x’ IN ‘text’);
What does SUBSTRING do?
Extracts part of a string: SELECT SUBSTRING(‘text’ FROM 2 FOR 2);
What does EXTRACT do?
Extracts parts of a date: SELECT EXTRACT(YEAR FROM date_column);
How do you format a date as text?
Use TO_CHAR: SELECT TO_CHAR(date_column, ‘YYYY-MM-DD’);
What are intervals in SQL?
Intervals represent time spans: SELECT CURRENT_DATE + INTERVAL ‘1 year’;
What are aggregate functions?
Functions that compute a value over a set of rows: SUM, COUNT, AVG, MIN, MAX.
How do you group data in SQL?
Use GROUP BY: SELECT column, COUNT(*) FROM table GROUP BY column;
What is the HAVING clause used for?
HAVING filters aggregated data: SELECT column, COUNT() FROM table GROUP BY column HAVING COUNT() > 5;
What is the difference between WHERE and HAVING?
WHERE filters before aggregation; HAVING filters after aggregation.
What does BETWEEN do?
Checks if a value is in a range: SELECT * FROM table WHERE column BETWEEN 10 AND 20;
What does IN do?
Checks if a value matches a list: SELECT * FROM table WHERE column IN (1, 2, 3);
What does LIKE do?
LIKE searches for patterns: SELECT * FROM table WHERE column LIKE ‘A%’;
What is an alias in SQL?
An alias renames a column or table: SELECT column AS alias_name FROM table;
How do you add comments in SQL?
– for single-line comments, /* */ for multi-line.
What does LIMIT do?
LIMIT restricts the number of results: SELECT * FROM table LIMIT 10;
What does DISTINCT do?
DISTINCT removes duplicate values: SELECT DISTINCT column FROM table;
What is the basic structure of a SELECT statement?
SELECT columns FROM table WHERE condition ORDER BY column;
How do you count rows in a table?
Use COUNT: SELECT COUNT(*) FROM table;
What does ORDER BY do?
Sorts results: SELECT * FROM table ORDER BY column ASC/DESC;
What is a Common Table Expression (CTE)?
A CTE is a temporary result set defined using WITH that can be referenced within a SQL query.
What is the syntax for a CTE?
WITH cte_name AS (SELECT column FROM table) SELECT * FROM cte_name;
What are the advantages of using a CTE over a subquery?
CTEs improve readability, reusability, and performance in complex queries.
When should you use a subquery instead of a CTE?
Use a subquery for simple queries that don’t require multiple references or recursion.
Can CTEs be recursive?
Yes, recursive CTEs allow hierarchical data retrieval.
What is the syntax for a recursive CTE?
WITH RECURSIVE cte_name AS (SELECT … UNION ALL SELECT …) SELECT * FROM cte_name;
What are the limitations of CTEs?
CTEs may have performance overhead compared to indexed views or temp tables.
How does a subquery differ from a CTE?
A subquery is nested inside another query, whereas a CTE is defined separately and can be referenced multiple times.
When is a subquery more efficient than a CTE?
Subqueries can be optimized in simple cases where defining a temporary structure is unnecessary.
What are some use cases for CTEs?
CTEs are useful for recursive queries, simplifying complex joins, and breaking down long queries.
