I85PREP
⌘ K
EXIN SQL Foundation study materials
EXIN SQL Foundation study resources

EXIN SQL Foundation practice questions

One question at a time. Build your EXIN SQL Foundation understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1SQL Fundamentals

Which SQL clause is used to filter individual rows before any grouping takes place?

  1. ORDER BY
  2. WHERE Correct answer.
  3. HAVING
  4. GROUP BY

Explanation

The WHERE clause evaluates a condition on each row of the source tables and discards rows that fail it, so it runs before grouping. HAVING filters groups after aggregation, while ORDER BY and GROUP BY do not filter rows at all.

Question 2SQL Fundamentals

A table CUSTOMER has a column EMAIL that may be empty for some rows. Which predicate returns only rows where EMAIL holds a value?

  1. EMAIL = ''
  2. EMAIL <> NULL
  3. EMAIL = NULL
  4. EMAIL IS NOT NULL Correct answer.

Explanation

NULL represents an unknown value, so any comparison using = or <> against NULL yields UNKNOWN and returns no rows. The only reliable test is IS NOT NULL, which is true only when the column actually contains a value.

Question 3SQL Fundamentals

Which statement about the primary key of a table is correct?

  1. It uniquely identifies each row and cannot contain NULL. Correct answer.
  2. It must always be the first column defined in the table.
  3. It may contain NULL values if the column is numeric.
  4. It allows duplicate values provided an index exists.

Explanation

A primary key enforces two constraints at once: uniqueness across all rows and the absence of NULL values. Because it must be able to identify every row, a NULL is never permitted regardless of data type or column position.

Question 4SQL Fundamentals

What does the DISTINCT keyword do in the query SELECT DISTINCT CITY FROM CUSTOMER?

  1. Counts how many customers live in each city.
  2. Sorts the city values in ascending order.
  3. Returns each different city value only once. Correct answer.
  4. Removes rows where CITY is NULL.

Explanation

DISTINCT eliminates duplicate rows from the result set, so each unique combination of the selected columns appears once. In this query that means one row per different city value, with no sorting, counting, or NULL removal implied.

Question 5SQL Fundamentals

Which operator pattern performs a wildcard match on a character column?

  1. CITY = 'Ber%'
  2. CITY IN ('Ber%')
  3. CITY LIKE 'Ber%' Correct answer.
  4. CITY BETWEEN 'Ber%' AND 'Ber%'

Explanation

LIKE is the pattern-matching operator, and the percent sign stands for any sequence of characters, so 'Ber%' matches values beginning with Ber. The equality, IN, and BETWEEN operators treat the string literally and look for an actual percent character.

Question 6SQL Fundamentals

Which statement adds a new row to the table PRODUCT?

  1. CREATE TABLE PRODUCT (ID INT, NAME VARCHAR(50));
  2. UPDATE PRODUCT SET NAME = 'Lamp';
  3. ALTER TABLE PRODUCT ADD NAME VARCHAR(50);
  4. INSERT INTO PRODUCT (ID, NAME) VALUES (10, 'Lamp'); Correct answer.

Explanation

INSERT INTO names the target table and columns, then VALUES supplies one row of data, which is exactly how a new row is added. UPDATE changes existing rows, ALTER modifies the table structure, and CREATE builds a new table.

Question 7SQL Fundamentals

Which aggregate function returns the number of rows in a group, including rows where a column is NULL?

  1. AVG(column)
  2. MAX(column)
  3. COUNT(*) Correct answer.
  4. SUM(column)

Explanation

COUNT(*) counts rows regardless of their contents, so NULL values do not reduce the total. The other aggregates operate on column values, and SUM, AVG, and MAX all ignore NULLs in their calculation.

Question 8SQL Fundamentals

Which clause combination is used to sort a result set by the column TOTAL in descending order?

  1. HAVING TOTAL DESC
  2. GROUP BY TOTAL DESC
  3. SORT BY TOTAL DESC
  4. ORDER BY TOTAL DESC Correct answer.

Explanation

ORDER BY controls the sequence of the final result, and the DESC keyword reverses the default ascending order. GROUP BY and HAVING relate to aggregation, and SORT BY is not a standard SQL clause.

Question 9SQL Fundamentals

A query joins ORDERS and CUSTOMER and selects CUSTOMER.NAME. The join condition is written in the WHERE clause as ORDERS.CUST_ID = CUSTOMER.ID. What happens to orders whose CUST_ID matches no customer?

  1. They cause the query to fail.
  2. They are excluded from the result. Correct answer.
  3. They are returned once with a blank name.
  4. They appear with a NULL name.

Explanation

An inner join keeps only row pairs that satisfy the join condition, so orders without a matching customer are dropped. To retain such orders with NULL customer data, an outer join such as LEFT JOIN would be required.

Question 10SQL Fundamentals

Which query returns each department together with the number of employees in it, showing only departments having more than five employees?

  1. SELECT DEPT, COUNT(*) FROM EMP WHERE COUNT(*) > 5 GROUP BY DEPT;
  2. SELECT DEPT, COUNT(*) FROM EMP GROUP BY DEPT WHERE COUNT(*) > 5;
  3. SELECT DEPT, COUNT(*) FROM EMP HAVING COUNT(*) > 5;
  4. SELECT DEPT, COUNT(*) FROM EMP GROUP BY DEPT HAVING COUNT(*) > 5; Correct answer.

Explanation

Aggregates are computed per group, so the group-level condition belongs in HAVING after GROUP BY. The WHERE clause cannot contain aggregate functions and is evaluated before grouping, and HAVING cannot precede GROUP BY.

Question 11SQL Fundamentals

The table EMP has columns ID, NAME, and SALARY. Which query lists employees earning more than the average salary of all employees?

  1. SELECT NAME, AVG(SALARY) FROM EMP WHERE SALARY > AVG(SALARY);
  2. SELECT NAME FROM EMP WHERE SALARY > (SELECT AVG(SALARY) FROM EMP); Correct answer.
  3. SELECT NAME FROM EMP WHERE SALARY > AVG(SALARY);
  4. SELECT NAME FROM EMP GROUP BY SALARY HAVING SALARY > AVG(SALARY);

Explanation

A subquery can compute the overall average once and return a single value that the outer WHERE compares against each row. Aggregates cannot appear directly in a WHERE clause, so the average must be obtained through a nested SELECT.

Question 12SQL Fundamentals

Which join returns every row from CUSTOMER plus matching rows from ORDERS, with NULLs where no order exists?

  1. CROSS JOIN
  2. INNER JOIN
  3. RIGHT JOIN
  4. LEFT JOIN Correct answer.

Explanation

A LEFT JOIN preserves all rows of the table named on the left, filling columns from the unmatched right table with NULLs. A RIGHT JOIN preserves the right table instead, and inner and cross joins do not preserve unmatched rows.

Question 13SQL Fundamentals

A table has a FOREIGN KEY on CUST_ID referencing CUSTOMER.ID. Which insert into the child table will be rejected?

  1. A row whose CUST_ID matches an existing CUSTOMER.ID.
  2. A row whose CUST_ID matches no existing CUSTOMER.ID. Correct answer.
  3. A row whose CUST_ID is NULL.
  4. A row whose CUST_ID matches the same customer as another child row.

Explanation

The foreign key requires every non-NULL child value to exist in the referenced parent column, so a value with no parent is rejected. Multiple child rows may reference the same parent, and NULL values are normally allowed unless a NOT NULL constraint is also present.

Question 14SQL Fundamentals

Which expression returns the current date in a way that is portable across most SQL implementations?

  1. CURRENT_DATE Correct answer.
  2. GETDATE()
  3. NOW
  4. SYSDATE

Explanation

CURRENT_DATE is defined in the SQL standard and is supported by most database products without parentheses. GETDATE() and SYSDATE are vendor-specific functions, and NOW without parentheses is not a standard date keyword.

Question 15SQL Fundamentals

What is the result of the expression COALESCE(PHONE, MOBILE, 'unknown') when PHONE is NULL and MOBILE contains '555-0100'?

  1. 'unknown'
  2. An error because two columns are supplied.
  3. '555-0100' Correct answer.
  4. NULL

Explanation

COALESCE returns the first argument that is not NULL, scanning left to right. PHONE is NULL so it is skipped, MOBILE holds a value and is returned, and the literal fallback is never reached.

Question 16SQL Fundamentals

The table SALE has columns REGION and AMOUNT. Which query returns the total amount per region, ordered from largest total to smallest?

  1. SELECT REGION, AMOUNT FROM SALE GROUP BY REGION ORDER BY AMOUNT DESC;
  2. SELECT REGION, SUM(AMOUNT) FROM SALE GROUP BY REGION ORDER BY SUM(AMOUNT) DESC; Correct answer.
  3. SELECT REGION, SUM(AMOUNT) FROM SALE ORDER BY REGION DESC;
  4. SELECT REGION, SUM(AMOUNT) FROM SALE HAVING SUM(AMOUNT) > 0 ORDER BY REGION;

Explanation

The query groups rows by REGION, aggregates AMOUNT with SUM, and sorts the aggregated result by that sum in descending order. ORDER BY may reference the aggregate expression, which yields the largest regional total first.

Question 17SQL Fundamentals

Which statement about the UNION operator is correct?

  1. It combines columns side by side into one wider result.
  2. It keeps duplicate rows from the combined result.
  3. It requires both queries to have the same number of columns with compatible types. Correct answer.
  4. It ignores the column order of the two queries.

Explanation

UNION stacks result sets vertically, which requires matching column counts and compatible data types in corresponding positions. By default it removes duplicates, and side-by-side combination is the job of a join rather than UNION.

Question 18SQL Fundamentals

A developer writes UPDATE EMP SET SALARY = SALARY * 1.1; with no WHERE clause. What is the effect?

  1. Only rows with a non-NULL salary are skipped.
  2. The statement fails because WHERE is mandatory.
  3. Every row in EMP has its salary raised. Correct answer.
  4. Only the first employee row is raised.

Explanation

Without a WHERE clause the UPDATE applies to all rows of the table, so every salary is increased by ten percent. WHERE is optional, and the statement succeeds silently, which is why such statements are risky in production.

Question 19SQL Fundamentals

Which data type is most appropriate for storing a precise monetary amount such as 1234.56?

  1. FLOAT
  2. DECIMAL(10,2) Correct answer.
  3. VARCHAR(10)
  4. BOOLEAN

Explanation

DECIMAL stores exact numeric values with a fixed number of decimal places, which avoids the rounding errors of binary floating point. The precision 10 and scale 2 allow up to eight integer digits and two decimal digits, suitable for currency.

Question 20SQL Fundamentals

Which statement removes all rows from LOG but keeps the table structure intact?

  1. TRUNCATE COLUMN LOG;
  2. DELETE FROM LOG; Correct answer.
  3. ALTER TABLE LOG DROP COLUMN ID;
  4. DROP TABLE LOG;

Explanation

DELETE FROM without a WHERE clause removes every row while leaving the table definition and its constraints in place. DROP TABLE removes the table itself, and ALTER TABLE DROP COLUMN changes the structure rather than clearing data.

Question 21SQL Fundamentals

Consider SELECT NAME FROM EMP ORDER BY NAME; where NAME holds values 'Anna', 'bob', 'Carl'. Which statement about the ordering is true?

  1. The order is always Anna, bob, Carl regardless of collation.
  2. The order is undefined because NAME is a text column.
  3. The exact order depends on the collation and case sensitivity of the database. Correct answer.
  4. The order is always Anna, Carl, bob because case is ignored.

Explanation

ORDER BY on a character column uses the database collation, which determines whether uppercase and lowercase letters are treated as equal and how they sort. Different implementations may therefore place 'bob' before or after 'Carl'.

Question 22SQL Fundamentals

A table ORDER_LINE has a composite primary key on (ORDER_ID, LINE_NO). Which insert is rejected?

  1. A row with ORDER_ID 2, LINE_NO 1 when a row with 1, 1 already exists.
  2. A row with ORDER_ID 1, LINE_NO 3 when a row with 1, 2 already exists.
  3. A row with ORDER_ID 1, LINE_NO 1 when a row with 2, 1 already exists.
  4. A row with ORDER_ID 1, LINE_NO 2 when a row with 1, 2 already exists. Correct answer.

Explanation

A composite primary key enforces uniqueness on the combination of all its columns, so repeating the exact pair (1, 2) violates the constraint. Reusing an order number with a different line number, or a line number with a different order, creates a new unique combination.

Question 23SQL Fundamentals

Which query correctly finds customers who have placed no orders, given CUSTOMER(ID) and ORDERS(CUST_ID)?

  1. SELECT ID FROM CUSTOMER JOIN ORDERS ON ID = CUST_ID;
  2. SELECT ID FROM CUSTOMER WHERE COUNT(CUST_ID) = 0;
  3. SELECT ID FROM CUSTOMER WHERE ID IN (SELECT CUST_ID FROM ORDERS);
  4. SELECT ID FROM CUSTOMER WHERE ID NOT IN (SELECT CUST_ID FROM ORDERS); Correct answer.

Explanation

The subquery lists every customer identifier that appears in ORDERS, and NOT IN keeps customers whose ID is absent from that list. Joins and IN return only customers who do have orders, and an aggregate cannot appear directly in WHERE.

Question 24SQL Fundamentals

A LEFT JOIN between CUSTOMER and ORDERS is used, and the WHERE clause contains ORDERS.AMOUNT > 100. How does this affect the result?

  1. The query behaves like an inner join and drops customers without matching orders. Correct answer.
  2. Customers without orders are kept with a NULL amount.
  3. The query returns duplicate rows for each customer.
  4. The query fails because a LEFT JOIN column is used in WHERE.

Explanation

For unmatched customers the ORDERS columns are NULL, and the comparison NULL > 100 evaluates to UNKNOWN, so those rows are filtered out. The outer join is effectively reduced to an inner join; to preserve unmatched rows the condition must move into the ON clause.

Question 25SQL Fundamentals

Which statement about correlated subqueries is correct?

  1. They must appear only in the FROM clause.
  2. They are evaluated for each candidate row of the outer query and may reference outer columns. Correct answer.
  3. They cannot reference columns of the outer query.
  4. They are evaluated once and their result is reused for every outer row.

Explanation

A correlated subquery references a column from the outer query, so its result can differ for each outer row and it is logically re-evaluated per row. This differs from a non-correlated subquery, which is independent of the outer row and can be computed once.

Question 26SQL Fundamentals

In SQL, which category of statement is used to define the structure of database objects such as tables and views?

  1. DML
  2. DCL
  3. TCL
  4. DDL Correct answer.

Explanation

DDL (Data Definition Language) covers statements such as CREATE, ALTER, and DROP that define or change the structure of schema objects. DML manipulates row data, DCL manages privileges, and TCL controls transaction boundaries, so none of those categories describes structural definition.

Question 27SQL Fundamentals

Which SQL statement retrieves only the columns NAME and CITY from the table CUSTOMER?

  1. SELECT * FROM CUSTOMER WHERE NAME, CITY;
  2. SELECT CUSTOMER.NAME + CITY;
  3. EXTRACT NAME, CITY FROM CUSTOMER;
  4. SELECT NAME, CITY FROM CUSTOMER; Correct answer.

Explanation

A SELECT statement lists the desired columns in its select list, separated by commas, followed by the FROM clause naming the table. The select list is the only place where the projected columns are specified.

Question 28SQL Fundamentals

Which operator is used in a WHERE clause to test whether a column value falls within an inclusive range of two boundary values?

  1. IN
  2. LIKE
  3. BETWEEN Correct answer.
  4. IS

Explanation

BETWEEN ... AND ... tests whether a value lies within an inclusive range, so both boundary values satisfy the condition. IN tests membership in a list, LIKE performs pattern matching, and IS tests for NULL or other predicates.

Question 29SQL Fundamentals

The table ITEM has a column PRICE that is NULL for some rows. Which comparison in a WHERE clause correctly tests for those rows?

  1. WHERE PRICE == NULL
  2. WHERE PRICE IS NULL Correct answer.
  3. WHERE PRICE = NULL
  4. WHERE NULL = PRICE

Explanation

NULL represents an unknown value, so any comparison with = against NULL evaluates to unknown and never returns a row. The IS NULL predicate is the dedicated test for the absence of a value.

Question 30SQL Fundamentals

Which statement permanently removes the table TEMP_LOG, including its definition, from the database?

  1. DELETE FROM TEMP_LOG;
  2. ALTER TABLE TEMP_LOG DROP COLUMN;
  3. TRUNCATE TABLE TEMP_LOG;
  4. DROP TABLE TEMP_LOG; Correct answer.

Explanation

DROP TABLE removes both the rows and the table definition, so the object no longer exists afterward. DELETE and TRUNCATE only remove rows while leaving the table itself in place.

Question 31SQL Fundamentals

In a SELECT statement, which clause is evaluated to filter individual rows before any grouping takes place?

  1. WHERE Correct answer.
  2. GROUP BY
  3. HAVING
  4. ORDER BY

Explanation

The WHERE clause filters individual rows before grouping, so it cannot contain aggregate functions. HAVING filters groups after aggregation, and ORDER BY only sorts the final result.

Question 32SQL Fundamentals

Which clause is used to combine the result sets of two SELECT statements and keep every row, including duplicate rows?

  1. UNION ALL Correct answer.
  2. INTERSECT
  3. EXCEPT
  4. JOIN

Explanation

UNION ALL concatenates the rows of two compatible result sets and preserves duplicates because it performs no duplicate elimination. INTERSECT and EXCEPT return only common or differing rows, and JOIN combines columns from related tables.

Question 33SQL Fundamentals

Which statement correctly creates a new table STAFF with two columns, ID of an integer type and NAME of a variable-length character type?

  1. CREATE TABLE STAFF (ID, NAME);
  2. MAKE TABLE STAFF (ID INTEGER, NAME VARCHAR(50));
  3. INSERT TABLE STAFF (ID INTEGER, NAME VARCHAR(50));
  4. CREATE TABLE STAFF (ID INTEGER, NAME VARCHAR(50)); Correct answer.

Explanation

CREATE TABLE followed by the table name and a parenthesized column list, each column with its name and data type, is the standard way to define a new table. Each column definition requires both a name and a type.

Question 34SQL Fundamentals

The table PRODUCT has a column CATEGORY and a column PRICE. Which query returns, for each category, the highest price in that category?

  1. SELECT CATEGORY, MAX(PRICE) FROM PRODUCT;
  2. SELECT CATEGORY, MAX(PRICE) FROM PRODUCT GROUP BY CATEGORY; Correct answer.
  3. SELECT CATEGORY, PRICE FROM PRODUCT ORDER BY PRICE DESC;
  4. SELECT CATEGORY, MAX(PRICE) FROM PRODUCT WHERE PRICE > 0;

Explanation

Combining a non-aggregated column with an aggregate function requires a GROUP BY clause on that column, so GROUP BY CATEGORY produces one row per category with its maximum price. Without grouping, the query would mix a single aggregate result with per-row values.

Question 35SQL Fundamentals

The table EMP has columns DEPT and SALARY. Which query returns the average salary for each department, showing only departments whose average salary exceeds 3000?

  1. SELECT DEPT, AVG(SALARY) FROM EMP GROUP BY DEPT WHERE AVG(SALARY) > 3000;
  2. SELECT DEPT, AVG(SALARY) FROM EMP GROUP BY DEPT HAVING AVG(SALARY) > 3000; Correct answer.
  3. SELECT DEPT, AVG(SALARY) FROM EMP HAVING AVG(SALARY) > 3000;
  4. SELECT DEPT, AVG(SALARY) FROM EMP WHERE AVG(SALARY) > 3000 GROUP BY DEPT;

Explanation

Conditions on aggregated values must be expressed in HAVING, which is applied after GROUP BY forms the groups. WHERE runs before grouping and therefore cannot reference an aggregate result.

Question 36SQL Fundamentals

The table EMP has columns ID, NAME, and MGR_ID, where MGR_ID references EMP.ID. Which query lists each employee together with the name of that employee's manager?

  1. SELECT E.NAME, M.NAME FROM EMP E INNER JOIN EMP M ON E.ID = M.MGR_ID;
  2. SELECT E.NAME, M.NAME FROM EMP E CROSS JOIN EMP M;
  3. SELECT NAME, MGR_ID FROM EMP ORDER BY MGR_ID;
  4. SELECT E.NAME, M.NAME FROM EMP E INNER JOIN EMP M ON E.MGR_ID = M.ID; Correct answer.

Explanation

A self-join uses two aliases for the same table, and the condition links each employee row to the manager row whose ID equals that employee's MGR_ID. This produces one output row per employee with the matching manager name.

Question 37SQL Fundamentals

The table ORDER_LINE has columns ORDER_ID, LINE_NO, and QTY. Which query returns only the order lines whose quantity is above the average quantity of all order lines?

  1. SELECT * FROM ORDER_LINE WHERE QTY > AVG(QTY);
  2. SELECT * FROM ORDER_LINE WHERE QTY > (SELECT AVG(QTY) FROM ORDER_LINE); Correct answer.
  3. SELECT * FROM ORDER_LINE GROUP BY QTY HAVING QTY > AVG(QTY);
  4. SELECT * FROM ORDER_LINE WHERE AVG(QTY) > QTY;

Explanation

An aggregate cannot appear directly in a WHERE clause, so the average is computed by a scalar subquery and the outer query compares each row against that single value. The subquery returns exactly one value, making the comparison valid.

Question 38SQL Fundamentals

The table PAYMENT has columns CUST_ID and AMOUNT. Which query returns each customer's ID together with the total amount that customer has paid, using the column name TOTAL?

  1. SELECT CUST_ID, SUM(AMOUNT) AS TOTAL FROM PAYMENT GROUP BY CUST_ID; Correct answer.
  2. SELECT CUST_ID, SUM(AMOUNT) AS TOTAL FROM PAYMENT;
  3. SELECT CUST_ID, TOTAL FROM PAYMENT GROUP BY CUST_ID;
  4. SELECT CUST_ID, SUM(AMOUNT) AS TOTAL FROM PAYMENT ORDER BY CUST_ID;

Explanation

The aggregate SUM must be grouped by CUST_ID so that each customer yields one row, and the AS clause assigns the alias TOTAL to the computed column. Without GROUP BY the aggregate would collapse the whole table into one row.

Question 39SQL Fundamentals

The table EMP has columns ID, NAME, and DEPT. Which query returns employees whose DEPT is either 'SALES' or 'MARKETING'?

  1. SELECT * FROM EMP WHERE DEPT IN ('SALES', 'MARKETING'); Correct answer.
  2. SELECT * FROM EMP WHERE DEPT LIKE 'SALES' OR 'MARKETING';
  3. SELECT * FROM EMP WHERE DEPT = 'SALES' AND DEPT = 'MARKETING';
  4. SELECT * FROM EMP WHERE DEPT BETWEEN 'SALES' AND 'MARKETING';

Explanation

The IN predicate tests membership in a list of values and returns rows matching any listed value. Combining two equality tests with AND would require a single row to hold both values at once, which is impossible for one column.

Question 40SQL Fundamentals

The table CUSTOMER has columns ID and CITY. Which query returns the number of distinct cities represented in the table?

  1. SELECT DISTINCT COUNT(CITY) FROM CUSTOMER;
  2. SELECT COUNT(DISTINCT CITY) FROM CUSTOMER; Correct answer.
  3. SELECT COUNT(CITY) FROM CUSTOMER;
  4. SELECT COUNT(*) FROM CUSTOMER;

Explanation

COUNT(DISTINCT CITY) counts each different non-null city once, which is exactly the number of distinct cities. COUNT(CITY) counts all non-null occurrences and therefore repeats duplicates.

Question 41SQL Fundamentals

The table SHIPMENT has columns ID, SHIPPED_ON of a date type, and CARRIER. Which query returns shipments made in the year 2023?

  1. SELECT * FROM SHIPMENT WHERE SHIPPED_ON LIKE '2023';
  2. SELECT * FROM SHIPMENT WHERE SHIPPED_ON = 2023;
  3. SELECT * FROM SHIPMENT WHERE EXTRACT(YEAR FROM SHIPPED_ON) = 2023; Correct answer.
  4. SELECT * FROM SHIPMENT WHERE YEAR = 2023;

Explanation

EXTRACT(YEAR FROM SHIPPED_ON) returns the year component of the date, which can then be compared with 2023. Comparing a full date value with a bare number would not match complete dates.

Question 42SQL Fundamentals

The table EMP has columns FIRST_NAME and LAST_NAME. Which expression produces a single character value combining the last name, a comma and space, and the first name?

  1. CONCAT(FIRST_NAME, LAST_NAME) || ','
  2. FIRST_NAME + ', ' + LAST_NAME
  3. FIRST_NAME || ', ' || LAST_NAME Correct answer.
  4. SUBSTRING(FIRST_NAME, LAST_NAME)

Explanation

The concatenation operator || joins character values in the order written, so LAST_NAME followed by ', ' and FIRST_NAME yields the required format. The order of operands determines the order of the output text.

Question 43SQL Fundamentals

A table ACCOUNT has a PRIMARY KEY on ID. Which statement about inserting a new row with an ID value that already exists in the table is correct?

  1. The insert succeeds and both rows are stored with the same ID.
  2. The insert is rejected because the primary key must be unique. Correct answer.
  3. The insert succeeds and the existing row is replaced.
  4. The insert succeeds only if the other columns are NULL.

Explanation

A PRIMARY KEY enforces both uniqueness and non-null values, so a duplicate key value violates the constraint and the statement fails. The database does not silently overwrite the existing row.

Question 44SQL Fundamentals

The table EMP has columns ID, NAME, and DEPT. Which query returns employees sorted by DEPT alphabetically, and within each department by NAME alphabetically?

  1. SELECT * FROM EMP ORDER BY DEPT, NAME; Correct answer.
  2. SELECT * FROM EMP ORDER BY DEPT DESC, NAME DESC;
  3. SELECT * FROM EMP ORDER BY NAME, DEPT;
  4. SELECT * FROM EMP GROUP BY DEPT, NAME;

Explanation

Multiple sort keys are listed in ORDER BY in priority order, so DEPT first groups the output by department and NAME then orders employees within each department. Both keys default to ascending order.

Question 45SQL Fundamentals

A table EVENT_LOG grows continuously. A developer wants to delete rows older than one year while keeping all newer rows. Which statement achieves this?

  1. DELETE FROM EVENT_LOG;
  2. UPDATE EVENT_LOG SET EVENT_DATE = NULL WHERE EVENT_DATE < CURRENT_DATE;
  3. DELETE FROM EVENT_LOG WHERE EVENT_DATE < CURRENT_DATE - INTERVAL '1' YEAR; Correct answer.
  4. DROP TABLE EVENT_LOG;

Explanation

A DELETE with a WHERE condition removes only the rows that satisfy the condition, leaving newer rows untouched. The condition compares each row's date with a computed cutoff one year before the current date.

Question 46SQL Fundamentals

The table EMP has columns ID and SALARY. A statement runs UPDATE EMP SET SALARY = SALARY + 100 WHERE SALARY < 2000; and then the transaction is rolled back. What is the effect on the stored data?

  1. The rows with SALARY below 2000 are deleted.
  2. The salary increases are undone and the original values remain. Correct answer.
  3. The salary increases are permanently saved.
  4. The statement fails because UPDATE cannot use a column in its own expression.

Explanation

ROLLBACK undoes all changes made since the start of the transaction, restoring the values that existed before the UPDATE. The changes only become permanent after a COMMIT.

Question 47SQL Fundamentals

The table CUSTOMER has columns ID and NAME, and ORDERS has columns ID, CUST_ID, and AMOUNT. Which query returns the names of customers who have at least one order with an amount greater than 500?

  1. SELECT NAME FROM CUSTOMER WHERE AMOUNT > 500;
  2. SELECT NAME FROM CUSTOMER WHERE ID IN (SELECT CUST_ID FROM ORDERS WHERE AMOUNT > 500); Correct answer.
  3. SELECT NAME FROM CUSTOMER WHERE ID = (SELECT CUST_ID FROM ORDERS WHERE AMOUNT > 500);
  4. SELECT NAME FROM CUSTOMER, ORDERS WHERE AMOUNT > 500;

Explanation

The IN predicate compares each customer ID with the set of customer IDs returned by the subquery, so a customer qualifies when any of its orders exceeds 500. Using IN also tolerates the subquery returning several rows.

Question 48SQL Fundamentals

The table EMP has columns ID, DEPT, and SALARY. Which query returns, for each department, only the employee with the highest salary in that department?

  1. SELECT DEPT, ID, SALARY FROM EMP ORDER BY SALARY DESC;
  2. SELECT DEPT, ID, SALARY FROM EMP WHERE SALARY > ALL (SELECT SALARY FROM EMP);
  3. SELECT DEPT, MAX(SALARY) FROM EMP GROUP BY DEPT;
  4. SELECT DEPT, ID, SALARY FROM EMP E WHERE SALARY = (SELECT MAX(SALARY) FROM EMP WHERE DEPT = E.DEPT); Correct answer.

Explanation

A correlated subquery re-evaluates the maximum salary for each row's department, so the outer row matches only when its salary equals that department's maximum. This returns the employee row itself, not just the aggregate value.

Question 49SQL Fundamentals

The table EMP has columns ID, DEPT, and SALARY, and some rows have a NULL DEPT. Which statement about grouping by DEPT is correct?

  1. Rows with NULL DEPT form one group and appear as a single group in the result. Correct answer.
  2. The query fails because GROUP BY cannot process NULL values.
  3. Rows with NULL DEPT are excluded from the result.
  4. Each row with NULL DEPT forms its own separate group.

Explanation

GROUP BY treats all NULL values in the grouping column as equal to each other, so they are collected into a single group. That group appears in the output with NULL shown for the grouped column.

Question 50SQL Fundamentals

The table EMP has columns ID and DEPT, and DEPT has no NOT NULL constraint. Which query returns employees whose DEPT is neither 'HR' nor NULL?

  1. SELECT * FROM EMP WHERE DEPT <> 'HR' AND DEPT IS NOT NULL; Correct answer.
  2. SELECT * FROM EMP WHERE NOT DEPT = 'HR' OR DEPT IS NULL;
  3. SELECT * FROM EMP WHERE DEPT <> 'HR';
  4. SELECT * FROM EMP WHERE DEPT = 'HR' OR DEPT IS NULL;

Explanation

The comparison DEPT <> 'HR' evaluates to unknown for NULL rows, so those rows are excluded by the WHERE clause, but the second condition makes the intent explicit. Adding DEPT IS NOT NULL guarantees that only non-null departments other than HR are returned.