Ch3

Question 1

[SQL] According to standard operator precedence in SQL, which of the following operators is evaluated before AND but after comparison operators like = or >?

Answer 1

The NOT operator.
WHY: Standard SQL evaluation order requires the database to process Mathematical Operators first, then Comparison Operators, then logical NOT, then logical AND, then logical OR.

Question 2

[SQL] Reinforcement: Fill in the evaluation order for logical operators in SQL: AND, OR, NOT.

Answer 2

1. NOT
2. AND
3. OR
   MNEMONIC: NAO (Not And Or).

Question 3

[SQL] Mastery: Why is it a best practice to use parentheses in complex SQL WHERE clauses even if you know the exact operator precedence?

Answer 3

Parentheses guarantee the intended logic is executed and make the SQL code much easier for other developers to read and maintain, rather than forcing them to rely on memorized precedence rules.

Question 4

[SQL] Which comparison operators are used to check for inequality in SQL?

Answer 4

The standard SQL operator for inequality is <> (less than and greater than symbols together). Many systems also accept != (exclamation point and equals sign).
WHY: <> means literally less than OR greater than, which equates to not equal.

Question 5

[SQL] Reinforcement: Write a SQL WHERE clause to filter out any products from a Products table where the Category is “Discontinued”.

Answer 5

WHERE Category <> “Discontinued”
NOTE: Using != is also acceptable in most modern relational database management systems.

Question 6

[SQL] Mastery: Between the <> operator and the != operator for inequality in SQL, which one is considered the ISO standard?

Answer 6

The <> operator is the official ISO standard for SQL. The != operator is a common vendor extension adopted from C-style programming languages.

Question 7

[SQL] A Departments table contains many employees in each department. If you run SELECT DepartmentName FROM Employees;, you will get many duplicate values. Which keyword should be added to the SELECT clause to return only the unique department names?

Answer 7

The DISTINCT keyword.
WHY: SELECT DISTINCT filters the result set to remove duplicate rows, returning only unique values for the specified columns.

Question 8

[SQL] Reinforcement: Provide the exact SQL syntax to retrieve only unique job titles from an Employees table.

Answer 8

SELECT DISTINCT JobTitle FROM Employees;
MNEMONIC: DISTINCT means different or unique.

Question 9

[SQL] Mastery: What happens if you use the DISTINCT keyword on multiple columns, such as SELECT DISTINCT City, State FROM Customers?

Answer 9

SQL evaluates the COMBINATION of the columns. It will return unique pairs of City and State, meaning the same state can appear multiple times as long as the city is different in that row.

Question 10

[SQL] What is the fundamental difference between the CURTIME() and TIME() functions in SQL?

Answer 10

CURTIME() returns the current time of the system clock. TIME(expression) extracts the time portion from a given datetime string or variable.
WHY: CURTIME generates new data based on right now. TIME parses existing data.

Question 11

[SQL] Reinforcement: Fill in the blank to extract just the time portion from an order timestamp: SELECT _______(OrderDate) FROM Orders;

Answer 11

TIME
The complete syntax is: SELECT TIME(OrderDate) FROM Orders;

Question 12

[SQL] Mastery: If a SQL database stores a timestamp as “2026-02-26 14:30:00”, what exactly will the TIME() function return?

Answer 12

The TIME() function will return “14:30:00”. It drops the year, month, and day.

Question 13

[SQL] A query needs to extract the first 4 characters from a ProductCode string. Which SQL function should be used?

Answer 13

The LEFT() function.
WHY: LEFT(ProductCode, 4) starts at the leftmost character of the string and extracts the specified number of characters.

Question 14

[SQL] Reinforcement: Write the exact SQL SELECT clause to pull the first 3 letters of a column named PhoneNumber.

Answer 14

SELECT LEFT(PhoneNumber, 3)
MNEMONIC: LEFT reads from the start of the word.

Question 15

[SQL] Mastery: What SQL function would you use to extract characters from the middle of a string instead of the left or right?

Answer 15

The SUBSTRING() function.
EXAMPLE: SUBSTRING(ProductCode, 3, 4) starts at the third character and pulls the next four characters.

Question 16

[SQL] A developer wants to return a list of unique combinations of City and State from a Customers table. Which SQL syntax is correct?

Answer 16

SELECT DISTINCT City, State FROM Customers;
WHY: Placing DISTINCT immediately after SELECT applies the uniqueness constraint to the entire row of results returned.

Question 17

[SQL] Reinforcement: Debug this SQL query that is failing to return unique pairs of regions: SELECT City, DISTINCT State FROM Locations;

Answer 17

The DISTINCT keyword must come immediately after SELECT, before any column names.
CORRECT: SELECT DISTINCT City, State FROM Locations;

Question 18

[SQL] Mastery: Does using SELECT DISTINCT in SQL slow down query performance?

Answer 18

Yes, DISTINCT often forces the database engine to sort the data or use a hash table behind the scenes to identify and remove duplicates, which requires extra processing time.

Question 19

[SQL] A query is written as SELECT * FROM Employees, Departments; without a WHERE or ON clause. What type of result will this produce?

Answer 19

A Cartesian Product, also known as a CROSS JOIN.
WHY: Without a join condition, every single row in the Employees table is blindly joined to every single row in the Departments table.

Question 20

[SQL] Reinforcement: Define a Cartesian Product in plain English.

Answer 20

A Cartesian Product occurs when a database multiplies every row from one table against every row from another table because no linking relationship was defined.
MNEMONIC: CROSS JOIN equals CROSS MULTIPLY.

Question 21

[SQL] Mastery: If an Employees table has 10 rows and a Departments table has 5 rows, how many rows will be returned by a query with no JOIN condition?

Answer 21

50 rows.
WHY: 10 employee rows multiplied by 5 department rows equals 50 total combinations.

Question 22

[SQL] An analyst needs to create a list of each DepartmentName and the number of employees in that department. Which SQL query correctly provides this information?

Answer 22

SELECT DepartmentName, COUNT() FROM Employees GROUP BY DepartmentName;
WHY: You must use GROUP BY with the non-aggregated column (DepartmentName) so the COUNT function knows exactly how to group the totals.

Question 23

[SQL] Reinforcement: Fill in the blank to count products per category: SELECT Category, COUNT() FROM Products ________ Category;

Answer 23

GROUP BY
The full clause is GROUP BY Category;

Question 24

[SQL] Mastery: What SQL error occurs if you include a column in the SELECT clause alongside an aggregate function like COUNT(), but forget to include that column in the GROUP BY clause?

Answer 24

The database will return a syntax error stating that the column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Question 25

[SQL] A manager wants to find the average salary for each job title in the company. Which SQL query is correct?

Answer 25

SELECT JobTitle, AVG(Salary) FROM Employees GROUP BY JobTitle; WHY: AVG() calculates the mathematical mean, and GROUP BY separates that average into isolated buckets for each specific JobTitle.

Question 26

[SQL] Reinforcement: Write a SQL query to find the maximum salary for each department in the Employees table.

Answer 26

SELECT DepartmentName, MAX(Salary) FROM Employees GROUP BY DepartmentName; MNEMONIC: Select it, Aggregate it, Group by the selection.

Question 27

[SQL] Mastery: How does the AVG() function handle NULL values in a Salary column?

Answer 27

The AVG() function completely ignores NULL values. It calculates the average only across rows that have actual numeric values.

Question 28

[SQL] An analyst wants to find the total number of employees in the 'Sales' department. Which SQL query should they use?

Answer 28

SELECT COUNT() FROM Employees WHERE Department = 'Sales'; WHY: The WHERE clause filters the dataset down to only Sales rows BEFORE the COUNT() aggregate function executes and does the counting.

Question 29

[SQL] Reinforcement: Write a SQL query to find the total number of customers located in 'Texas'.

Answer 29

SELECT COUNT() FROM Customers WHERE State = 'Texas';

Question 30

[SQL] Mastery: When counting employees in a specific department, what is the performance difference between COUNT() and COUNT(EmployeeID) if EmployeeID is the Primary Key?

Answer 30

There is generally no performance difference in modern relational databases. However, COUNT() is the standard industry convention for counting rows.

Question 31

[SQL] A manager wants to see a list of departments where the average employee salary is more than $60,000. Which query correctly provides this list?

Answer 31

SELECT DepartmentName FROM Employees GROUP BY DepartmentName HAVING AVG(Salary) > 60000; WHY: You must use the HAVING clause instead of WHERE because you are filtering based on the result of an aggregate function (AVG).

Question 32

[SQL] Reinforcement: Debug this SQL query: SELECT JobTitle FROM Employees GROUP BY JobTitle WHERE COUNT() > 5;

Answer 32

The WHERE clause cannot be used to filter aggregate functions like COUNT. CORRECT: SELECT JobTitle FROM Employees GROUP BY JobTitle HAVING COUNT() > 5;

Question 33

[SQL] Mastery: Can a single SQL query contain both a WHERE clause and a HAVING clause?

Answer 33

Yes. The WHERE clause filters individual rows BEFORE they are grouped, and the HAVING clause filters the aggregated groups AFTER they are formed.

Question 34

[SQL] What is the fundamental difference between the WHERE clause and the HAVING clause in SQL?

Answer 34

WHERE filters individual rows before aggregation occurs. HAVING filters groups after the GROUP BY clause has aggregated the data. WHY: The database engine logically processes WHERE first, then groups the data, then processes HAVING.

Question 35

[SQL] Reinforcement: Define the rule for when to use HAVING instead of WHERE in plain English.

Answer 35

If your filtering condition includes a math function like SUM, AVG, MIN, MAX, or COUNT, you must use HAVING. Otherwise, use WHERE. MNEMONIC: WHERE is for Rows, HAVING is for Groups.

Question 36

[SQL] Mastery: In the logical SQL order of execution, which clause is processed immediately after the GROUP BY clause?

Answer 36

The HAVING clause is processed immediately after GROUP BY, filtering out unqualified groups before the SELECT clause finally shapes the columns to be returned.

Question 37

[SQL] A Students table has a Major column that can contain NULL values for undecided students. Which query correctly counts only the students who have declared a major?

Answer 37

SELECT COUNT(Major) FROM Students; WHY: When you put a specific column name inside the COUNT() function, SQL automatically ignores any rows where that column contains a NULL value.

Question 38

[SQL] Reinforcement: Fill in the blank to count only rows where the PhoneNumber column is not null: SELECT ________(PhoneNumber) FROM Customers;

Answer 38

COUNT The full statement is SELECT COUNT(PhoneNumber) FROM Customers;

Question 39

[SQL] Mastery: What is the exact difference between COUNT() and COUNT(ColumnName) when dealing with missing data?

Answer 39

COUNT() counts every single row in the table, including rows with all NULL values. COUNT(ColumnName) counts only the rows where the specific specified column has a non-NULL value.

Question 40

[SQL] Some database systems like MySQL do not directly support the FULL OUTER JOIN syntax. How is the equivalent result typically achieved in these systems?

Answer 40

By using a LEFT JOIN, followed by the UNION operator, followed by a RIGHT JOIN. WHY: A FULL OUTER JOIN returns all records from both tables. Combining a LEFT JOIN (all left records) and a RIGHT JOIN (all right records) with UNION removes the overlapping duplicates and mimics this behavior.

Question 41

[SQL] Reinforcement: What SQL keyword is used to stack the results of two different SELECT queries on top of each other to simulate a FULL OUTER JOIN?

Answer 41

UNION. MNEMONIC: UNION unites two vertical query results into one single list.

Question 42

[SQL] Mastery: What is the difference between UNION and UNION ALL when simulating a FULL OUTER JOIN?

Answer 42

UNION automatically removes duplicate rows from the final combined result set. UNION ALL keeps every row, which would incorrectly duplicate the matched inner rows in the middle of a simulated FULL OUTER JOIN.

Question 43

[SQL] You need a list of all Students and the Course they are enrolled in, including students not yet enrolled. Complete this query: SELECT S.Name, C.CourseName FROM Courses C ________ Students S ON S.CourseID = C.CourseID;

Answer 43

RIGHT JOIN. WHY: Because the 'Students' table is listed on the RIGHT side of the join statement, and we want ALL students regardless of a match in Courses, we must use a RIGHT JOIN.

Question 44

[SQL] Reinforcement: Debug this SQL query trying to keep all Users even if they have no Orders: SELECT * FROM Orders o LEFT JOIN Users u ON o.UserID = u.UserID;

Answer 44

Since Orders is on the left and Users is on the right, LEFT JOIN keeps all Orders. To keep all Users, you must change it to RIGHT JOIN. CORRECT: SELECT * FROM Orders o RIGHT JOIN Users u ON o.UserID = u.UserID;

Question 45

[SQL] Mastery: If you swapped the order of the tables in the FROM clause so 'Students' came first, how would you rewrite the JOIN to achieve the same result?

Answer 45

You would change it to a LEFT JOIN. EXAMPLE: FROM Students S LEFT JOIN Courses C ON S.CourseID = C.CourseID;

Question 46

[SQL] An analyst wants to find all employees whose last names fall alphabetically between 'Garcia' and 'Jones', inclusive. The LastName column is a VARCHAR type. Which WHERE clause is valid and correct?

Answer 46

WHERE LastName BETWEEN 'Garcia' AND 'Jones' WHY: The BETWEEN operator works for alphabetical string comparisons just like it does for numbers and dates.

Question 47

[SQL] Reinforcement: Fill in the blanks to filter for product prices ranging from 10 to 50 inclusive: WHERE Price ________ 10 ________ 50;

Answer 47

BETWEEN, AND The full clause is WHERE Price BETWEEN 10 AND 50;

Question 48

[SQL] Mastery: In SQL, how does string comparison handle the boundary of BETWEEN 'Garcia' and 'Jones' if someone's name is 'Jonesy'?

Answer 48

'Jonesy' is alphabetically AFTER 'Jones'. Because 'Jones' technically evaluates as 'Jones ' (a strict boundary), 'Jonesy' will NOT be included in the results.

Question 49

[SQL] When you write SELECT ProductName, Category, Price FROM Products ORDER BY ProductName ASC; What determines the order of the columns from left to right in the final result set?

Answer 49

The order in which the columns are listed in the SELECT clause. WHY: The database engine outputs columns exactly in the sequence you type them after the word SELECT, ignoring the underlying table design.

Question 50

[SQL] Reinforcement: If a SQL table is designed with Price as the very first column, but you run SELECT Category, Price FROM Products;, which column will appear first in the output?

Answer 50

Category. MNEMONIC: The SELECT statement always overrides the underlying table design for presentation.

Question 51

[SQL] Mastery: Does changing the order of columns in the SELECT clause impact query execution speed or database performance?

Answer 51

No. The order of columns in the SELECT clause is purely for output formatting and presentation; it has no impact on the speed or processing logic of the database engine.

Question 52

[SQL] A manager wants to find any job titles that are held by only one employee. Which SQL query will return this list of job titles?

Answer 52

SELECT JobTitle FROM Employees GROUP BY JobTitle HAVING COUNT() = 1; WHY: We must group by the job title first, and then filter those groups using HAVING to keep only the ones with a count exactly equal to 1.

Question 53

[SQL] Reinforcement: Write a SQL query to find email addresses that appear more than once (duplicates) in a Users table.

Answer 53

SELECT Email FROM Users GROUP BY Email HAVING COUNT() > 1;

Question 54

[SQL] Mastery: Why would using WHERE COUNT(*) = 1 fail to find job titles held by only one employee?

Answer 54

The WHERE clause evaluates row-by-row before any counting or grouping happens. Aggregate functions like COUNT() do not exist at the WHERE stage, causing a fatal syntax error.

Question 55

[SQL] Which query correctly illustrates performing an outer self-join to list employees and their managers, including employees who have no manager?

Answer 55

SELECT e.Name as Employee, m.Name as Manager FROM Employees e LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID; WHY: A self-join connects a table to itself using aliases (e and m). The LEFT JOIN ensures that employees at the top (with a NULL ManagerID) are still included in the final list.

Question 56

[SQL] Reinforcement: Define a Self-Join in plain English.

Answer 56

A Self-Join is when a table is joined to a copy of itself, allowing you to compare rows within the exact same table. MNEMONIC: Aliases act like mirrors for the table to look at itself.

Question 57

[SQL] Mastery: In a self-join like Employees e LEFT JOIN Employees m, what is the specific purpose of the ON e.ManagerID = m.EmployeeID clause?

Answer 57

It acts as the bridge. It takes the ManagerID value from the regular employee's row and searches the copy of the table (m) to find the row where that ID matches the primary EmployeeID.

Question 58

[SQL] What is a key characteristic of a NATURAL JOIN in SQL?

Answer 58

It automatically joins tables by finding all columns with the exact same name in both tables and matching on those values. WHY: NATURAL JOIN assumes that identical column names indicate a foreign key relationship and implicitly writes the ON clause for you.

Question 59

[SQL] Reinforcement: What happens if you run a NATURAL JOIN on two tables, but they have absolutely no column names in common?

Answer 59

The query will result in a Cartesian Product (Cross Join), combining every single row from the first table with every single row from the second table. MNEMONIC: No natural match means a massive cross catch.

Question 60

[SQL] Mastery: Why do database administrators generally forbid the use of NATURAL JOIN in production code?

Answer 60

If someone alters the database structure and adds a new column to both tables that happens to share a name (like 'UpdatedDate'), the NATURAL JOIN will suddenly and silently start joining on that column, breaking the application logic.