SQL ORDER BY and Alias MCQs: 11 Solved Questions with Explanations

Practise 11 SQL MCQs covering aliases, sort direction, multiple sort keys, DISTINCT, explicit null placement and zero-based LIMIT offsets.

KnowledgeGate Team

Exam prep & CS education

Updated 30 Oct 20267 min read
SQL ORDER BY and Alias MCQs: 11 Solved Questions with Explanations

ORDER BY questions look easy until distractors put DESC before a column, reverse a later key, make an alias permanent, or confuse an nth row with an nth distinct value. Choose before each explanation, and trace keys from left to right. Master direction, tie-breaks and aliases here, then use DBMS SQL Query MCQs for SELECT, joins, GROUP BY and subqueries. The GATE course practice area contains solved questions for exam and company preparation.

1. ORDER BY and aliases: four rules to apply

Three-key ORDER BY flow sorts marks and breaks the 92 tie, producing Ravi, Asha, Meera, then Kabir.
  1. Use ORDER BY expression ASC|DESC. The direction follows its expression.

  2. An omitted direction means ascending order.

  3. Later expressions break ties left by earlier ones.

  4. AS changes a result-column or table reference label, not the stored schema.

Use these rows:

Code
RESULTS
student_id | name  | marks
105        | Asha  | 92
101        | Ravi  | 92
108        | Meera | 81
104        | Kabir | NULL
sql
SELECT student_id, name AS student, marks
FROM RESULTS
ORDER BY
  CASE WHEN marks IS NULL THEN 1 ELSE 0 END ASC,
  marks DESC,
  student_id ASC;

The output is:

Code
101 | Ravi  | 92
105 | Asha  | 92
108 | Meera | 81
104 | Kabir | NULL

The CASE gives non-null marks 0 and the null 1, placing Kabir last. marks DESC puts 92 before 81, then student_id ASC breaks the 92 tie as 101 before 105. The heading is student, but the stored column remains name. Review SQL Queries and Joins in DBMS to place sorting after selection, filtering and joins.

Left-to-right three-stage sort diagram showing CASE 0/1 ASC, marks DESC, student_id ASC, and final student rows 101 Ravi, 105 Asha, 108 Meera, 104 Kabir.

2. ORDER BY and AS basics

Question 1: the purpose of AS

‘AS’ clause is used in SQL for

  • A. Selection operation.

  • B. Rename operation.

  • C. Join operation.

  • D. Projection operation.

Answer: B. AS gives a temporary alias: SELECT name AS student FROM RESULTS; displays student without renaming the stored column. It performs none of the other operations.

Question 2: the purpose of ORDER BY

What is the purpose of the "ORDER BY" clause in SQL?

  • A. To filter data in a table

  • B. To sort data in a table

  • C. To combine data from multiple tables

  • D. To aggregate data in a table

Answer: B. ORDER BY sorts result rows; WHERE filters, joins combine rows, and functions like SUM aggregate. In ORDER BY marks DESC, marks is the key and DESC its direction.

Question 3: Oracle's sorting keyword

Which of the following keyword is used in Oracle SQL to sort the result-set in ascending or descending order?

  • A. MANGE

  • B. SORT

  • C. ARRANGE

  • D. ORDER BY

Answer: D. Oracle uses ORDER BY, followed by expressions and optional directions. The other choices are distractors, not ordering clauses.

Question 4: the default direction

If we have not specified ASC or DESC after an SQL ORDER BY clause, which of the following is used by default?

  • A. DESC

  • B. ASC

  • C. There is no default value

  • D. More than one of the above

  • E. None of the above

Answer: B. An omitted direction means ASC; ORDER BY student_id and its explicit form both give 101, 104, 105, 108 on that key.

3. ASC and DESC syntax

Question 5: descending order in MySQL

Which clause is used in a MySQL query to sort the result set in descending order based on a specific column?

  • A. SORT BY... DESC

  • B. ORDER BY... DESC

  • C. SORT BY... DSC

  • D. ORDER BY... DSC

Answer: B. ORDER BY column_name DESC is valid; SORT BY and DSC are not. Thus ORDER BY marks DESC places 92 before 81.

Question 6: direction placement

Which of the following Oracle SQL statements is not correct?

  • A.

sql
  SELECT * FROM Customers
  ORDER BY Country;
  • B.

sql
  SELECT * FROM Customers
  ORDER BY Country DESC;
  • C.

sql
  SELECT * FROM Customers
  ORDER BY Country ASC, DESC CustomerName;
  • D.

sql
  SELECT * FROM Customers
  ORDER BY Country ASC, CustomerName DESC;

Answer: C. A direction follows its expression, so DESC CustomerName is reversed. The correction ORDER BY Country ASC, CustomerName DESC sorts countries ascending, then names descending within ties.

4. ORDER BY clause recall and multi-column merit lists

Question 7: identify the sorting clause (TPSC 2026)

Which SQL clause is used to sort rows in a query result?

  • A. SORT BY

  • B. ORDER BY

  • C. GROUP BY

  • D. FILTER BY

Answer: B. Standard SQL uses ORDER BY to arrange result rows by one or more expressions. GROUP BY forms groups for aggregation, while SQL row filtering uses WHERE or HAVING.

Question 8: a visible tie-break

Code
This question is based on the following SQL table.
Mr. Aman, a Computer Science teacher, maintains the details of his Class 12 students in an SQL table named MYSTUDENT.

ROLLNO | NAME     | SEC | MARKS
80105  | MARIA    | A   | 83.0
80108  | AMAR     | B   | 44.0
80109  | MANPREET | A   | 92.0
80112  | SAMEER   | A   | 81.0
80115  | AKBAR    | B   | NULL

Identify the most suitable SQL command to display the merit list of students such that:
Details are displayed in descending order of MARKS
If some students have the same MARKS, their details are displayed in ascending order of ROLLNO
  • A. SELECT * FROM MYSTUDENT ORDER BY DESC MARKS, ASC ROLLNO;

  • B. SELECT * FROM MYSTUDENT ORDER BY MARKS DESC, ROLLNO ASC;

  • C. SELECT * FROM MYSTUDENT ORDER BY MARKS; ROLLNO DESC, ASC;

  • D. SELECT * FROM MYSTUDENT ORDER BY MARKS DESC; ROLLNO ASC;

Answer: B. A comma keeps both keys together; semicolons in C and D end the statement. If 80102 | PRIYA | A | 83.0 were added, PRIYA would precede MARIA at 83.0 because 80102 is less than 80105 under ROLLNO ASC.

5. Tuple precedence from left to right

Question 9: which tuple necessarily precedes?

Consider the following Relational Schema and the SQL Query that follows:

Code
R(A, B, C)
SELECT A, B, C From R ORDER BY C DESC, B ASC;
What conditions must a tuple t satisfy so that t necessarily precedes the tuple (7, 7, 7)? Identify one such tuple from the list below.
  • A. (5, 9, 7)

  • B. (5, 8, 5)

  • C. (9, 4, 7)

  • D. (3, 7, 7)

Answer: C. Compare C first and B only on a tie. Option C ties at C = 7 and has the earlier B = 4; A has B = 9, B has C = 5, and D ties on both keys, so its position is not guaranteed.

6. DISTINCT, null placement and nth-row retrieval

Question 10: unique values with nulls first

What will be the output of the following query if the table dept has duplicate department names?

Code
SELECT DISTINCT dept_name
FROM dept
ORDER BY dept_name DESC NULLS FIRST;
  • A. All department names with duplicates removed, but order is random

  • B. All unique department names sorted descending, NULLS first

  • C. Same as GROUP BY dept_name

  • D. All department names sorted ascending, NULLS at bottom

Answer: B. DISTINCT removes repeats; supported dialects then sort unique names descending with nulls first. [NULL, Sales, HR, Sales, Finance, NULL] becomes [NULL, Sales, HR, Finance]. This syntax is not portable, so never assume a default.

Question 11: the nth row after sorting

Code
In MySQL, you want the salary that appears in the nth row after sorting all salaries from the Employees table in descending (highest-first) order, where n is a positive integer chosen at run time.
Several of the options compute an offset into a session variable and then run a prepared statement that binds that value into the LIMIT clause (MySQL allows LIMIT parameters as ? placeholders in a prepared statement, but not as bare arithmetic expressions). Which option returns exactly the nth-highest salary?
  • A.

sql
  SET @off = n;
  PREPARE stmt FROM
    'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1';
  EXECUTE stmt USING @off;
  • B.

sql
  SET @off = n - 1;
  PREPARE stmt FROM
    'SELECT SALARY FROM Employees ORDER BY SALARY ASC LIMIT ?, 1';
  EXECUTE stmt USING @off;
  • C.

sql
  SET @off = n - 1;
  PREPARE stmt FROM
    'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT 1, ?';
  EXECUTE stmt USING @off;
  • D.

sql
  SET @off = n - 1;
  PREPARE stmt FROM
    'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1';
  EXECUTE stmt USING @off;
  • E.

sql
  SELECT MAX(SALARY)
  FROM Employees
  WHERE SALARY < n;

Answer: D. Because LIMIT offset, row_count is zero-based, row n needs offset n - 1 and count 1. For [95000, 88000, 88000, 74000, 62000] with n = 4, offset 3 returns 74000; duplicates count because this is the nth row, not nth distinct salary. Try SQL Queries for Placement Interviews for salary-ranking alternatives, joins and HAVING exercises beyond ORDER BY.

7. Answer key, traps and next step

Answer key: 1 B, 2 B, 3 D, 4 B, 5 B, 6 C, 7 B, 8 B, 9 C, 10 B, 11 D.

Check these traps:

  • Put an expression before its ASC or DESC direction.

  • Treat an omitted direction as ASC.

  • Use later keys only to break earlier ties.

  • Remember that a semicolon ends the statement.

  • Treat AS as a temporary alias, not a schema change.

  • Specify null placement instead of assuming a dialect default.

  • Convert row n to zero-based offset n - 1.

Rewrite Questions 6, 8, 9 and 11 from memory. Check that the RESULTS order is 101 Ravi 92, 105 Asha 92, 108 Meera 81, 104 Kabir NULL; (9, 4, 7) precedes (7, 7, 7) after the C tie; and n = 4 means offset 3 and returns 74000.

Continue with GATE Guidance by Sanchit Sir for structured DBMS study, or compare GATE CS courses and test series. Repeat the four hard questions tomorrow, explaining the deciding key aloud to test understanding rather than option recall.