SQL Questions

Imagine you're a data scientist, and you're on a mission to find all employees named after the letter 'J' in the vast database of 'Mystery Inc.' Which SQL statement would you use with the LIKE operator to uncover these hidden gems?

• SELECT * FROM Employees WHERE Name STARTS WITH 'J';

• SELECT * FROM Employees WHERE Name = 'J%';

• SELECT * FROM Employees WHERE Name MATCH 'J*';

• SELECT * FROM Employees WHERE Name LIKE 'J%';

Answer explanation

The correct SQL statement using the LIKE operator to find employees whose names start with 'J' is: SELECT * FROM Employees WHERE Name LIKE 'J%';

Imagine Harry is on a quest to find unique city names in a vast database of customer information. How would he write a SQL query to select distinct city names from the Customers table?

• SELECT DISTINCT City FROM Customers;

• SELECT UNIQUE City FROM Customers;

• SELECT City FROM Customers DISTINCT;

• LIST DISTINCT Cities FROM Customers;

Answer explanation

The correct choice is 'SELECT DISTINCT City FROM Customers;' which retrieves unique city names from the Customers table.

What is the purpose of the `JOIN` statement in SQL?

• To delete records from a table

• To insert new records into a table

• To update records in a table

• To combine rows from two or more tables, based on a related column between them

Answer explanation

The purpose of the JOIN statement in SQL is to combine rows from two or more tables based on a related column between them, allowing for data retrieval from multiple tables simultaneously.

Which SQL statement correctly represents an INNER JOIN between the tables Employees and Departments where the DepartmentID matches?

• SELECT * FROM Employees JOIN Departments USING DepartmentID;

• SELECT * FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

• SELECT * FROM Employees AND Departments WHERE Employees.DepartmentID = Departments.DepartmentID;

• SELECT * FROM Employees, Departments WHERE Employees.DepartmentID = Departments.DepartmentID;

Answer explanation

The correct SQL statement for INNER JOIN between Employees and Departments is SELECT * FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Which SQL statement uses a LEFT JOIN with aliases to list all employees and their orders, including those who have not placed any orders?

• SELECT E.LastName FROM E Employees LEFT JOIN O Orders ON E.EmployeeID = O.EmployeeID;

• SELECT E.LastName, O.OrderID FROM E Employees FULL OUTER JOIN O Orders ON E.EmployeeID = O.EmployeeID WHERE O.OrderID IS NULL;

• SELECT E.LastName, O.OrderID FROM Employees E LEFT JOIN Orders O ON E.EmployeeID = O.EmployeeID;

Answer explanation

The correct SQL statement uses a LEFT JOIN to list all employees and their orders, including those who have not placed any orders, by joining the Employees table with the Orders table on the EmployeeID column.

Imagine you're an intern in the HR department! Your mission is to find out those employees in the 'HR' department who are earning more than a golden number: 50000. Which SQL statement would you use?

• SELECT * FROM Employees WHERE Department == 'HR' AND Salary >> 50000;

• SELECT * FROM Employees WHERE Department = 'HR' & Salary > 50000;

• SELECT * FROM Employees WHERE Department = 'HR' AND Salary > 50000;

• SELECT * FROM Employees WHERE Department = 'HR' THEN Salary > 50000;

Answer explanation

The correct SQL statement is 'SELECT * FROM Employees WHERE Department = 'HR' AND Salary > 50000;'. It correctly finds employees in the 'HR' department with salaries greater than 50000.

Which SQL statement correctly uses an alias for a column name?

• SELECT LastName AS Surname FROM Employees;

• SELECT LastName NAME 'Surname' FROM Employees;

• DEFINE LastName AS Surname FROM Employees;

• SELECT LastName SET AS Surname FROM Employees;

Answer explanation

The correct SQL statement that uses an alias for a column name is 'SELECT LastName AS Surname FROM Employees;'.

Which SQL statement correctly uses the HAVING clause to find departments with more than 10 employees?

• SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(EmployeeID) > 10;

• SELECT DepartmentID FROM Employees WHERE COUNT(EmployeeID) > 10 GROUP BY DepartmentID;

• SELECT DepartmentID, COUNT(EmployeeID) FROM Employees GROUP BY DepartmentID IF COUNT(EmployeeID) > 10;

• SELECT DepartmentID FROM Employees COUNT(EmployeeID) > 10 GROUP BY DepartmentID;

Answer explanation

The correct SQL statement uses the HAVING clause to filter departments with more than 10 employees by grouping and counting EmployeeIDs. The correct choice is: SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(EmployeeID) > 10;

Which SQL statement correctly represents an INNER JOIN between the 'Employees' and 'Orders' tables using aliases, where the EmployeeID matches?

• SELECT E.LastName, O.OrderID FROM Employees E INNER JOIN Orders O ON E.EmployeeID = O.EmployeeID;

• SELECT E.LastName, O.OrderID FROM Employees AS E, Orders AS O WHERE E.EmployeeID = O.EmployeeID;

• SELECT E.LastName, O.OrderID FROM E Employees INNER JOIN O Orders ON E.EmployeeID = O.EmployeeID;

• SELECT E.LastName, O.OrderID FROM Employees E JOIN Orders O WITH E.EmployeeID = O.EmployeeID;

Answer explanation

The correct SQL statement for an INNER JOIN between 'Employees' and 'Orders' tables using aliases is: SELECT E.LastName, O.OrderID FROM Employees E INNER JOIN Orders O ON E.EmployeeID = O.EmployeeID;

Which SQL statement correctly calculates the average salary and the number of employees in the Employees table?

• SELECT AVG(Salary), COUNT(EmployeeID) FROM Employees;

• CALCULATE AVG(Salary), COUNT(EmployeeID) IN Employees;

• SELECT AVERAGE(Salary) AND TOTAL(EmployeeID) FROM Employees;

• SELECT MEAN(Salary), SUM(EmployeeID) FROM Employees;

Answer explanation

The correct SQL statement to calculate the average salary and number of employees is 'SELECT AVG(Salary), COUNT(EmployeeID) FROM Employees;'. This statement uses the AVG() function to calculate the average salary and the COUNT() function to count the number of employees.