Break the Query Round-1

Write the SQL query to get the third maximum salary of an employee from a table named employees.

• SELECT * FROM(

  SELECT employee_name, salary, DENSE_RANK() 

  OVER(ORDER BY salary DESC)r FROM Employee) 

  WHERE r=3;

• SELECT * FROM(

  SELECT employee_name, salary, DENSE_RANK() 

  OVER(ORDER BY salary ASC)r FROM Employee) 

  WHERE r=5;

• SELECT * FROM(

  SELECT employee, salary, DENSE_RANK() 

  OVER(GROUP BY salary )a FROM Employee) 

  WHERE r=2;

• None of these

If the SQL table has duplicate rows, the duplicate rows must be removed.

Let’s assume the following table as our dataset:

• DELETE table WHERE ID IN (SELECT ID, COUNT(ID) FROM   table ORDER BY  ID HAVING COUNT (ID) > 5); 

• REMOVE FROM table where ID IN (SELECT ID, COUNT(ID) FROM   table BY  ID HAVING COUNT (ID) > 1); 

• DELETE FROM table WHERE ID IN (SELECT ID, COUNT(ID) FROM   table GROUP BY  ID HAVING COUNT (ID) > 1); 

• None of these

Syntax for SQL CAST function

• CAST(expression TO [data type])

• CAST(expression AS [data type])

• CAST(expression IN [data type])

• CAST(expression FOR [data type])

What are the operators available in SQL?

• Arithmetic Operators

• Relational Operator

• Logical

  Operations

• Comparison

  Operators

The ways to get the count of records in a table?

• SELECT * FROM table1

• SELECT COUNT(*) FROM table1

• None of these

• SELECT rows FROM

  sysindexes

  WHERE id =

  OBJECT_ID(table1) AND indid

  < 2

Write a SQL query to find the names of employees that begin with ‘A’?

Consider Table name and column name are emp_details and EmpName

Consider a table named "employees" with columns "id", "name", "department", and "salary". Write a SQL query to display the names of employees who earn more than the average salary.

• SELECT emp_name FROM employee WHERE salary > (SELECT AVG(salary) FROM employees);

• SELECT emp_name FROM employee WHERE salary > (SELECT AVG(salary) FROM employees);

• All of these

• SELECT name FROM employee WHERE salary < (SELECT AVG(salary) FROM employee);

Consider a table named "orders" with columns "id", "date", "customer_id", "product_id", and "quantity". Write a SQL query to display the top 3 products that have been ordered the most.

• None of these

• SELECT product_id, SUM(quantity) as total_ordered

  FROM orders

  GROUP BY product_id

  ORDER BY total_ordered DESC

  LIMIT 3;

• SELECT product_id, SUM(quantity) in total_orderedFROM ordersGROUP BY product_idORDER BY total_ordered ASC LIMIT 3;

• SELECT product_id, SUM(quantity) as total_ordered FROM orders GROUP BY product_idORDER BY total_ordered DESC LIMIT BY 3;

Why are SQL functions used?

• To perform some calculations on the data

• To manipulate the output

• To modify individual data itemsa

• To convert the data types

How can you select unique records from a table?

• Select * from Student WHERE studentID = UNIQUE

• Select DISTINCT studentID from Student

• None of these

• Select UNIQUE studentID from Student