SQL-SET2 NEW

There are many ways to fetch first 5 characters of the string.

In the following options, which command is used to fetch first 5 characters of the string?

1. Select SUBSTRING(StudentName,1,5) as studentname from student

2. Select RIGHT(Studentname,5) as studentname from student

• option 1 only

• option 2 only

• both the options are correct

• both the options are incorrect

Write an SQL query to fetch the no. of workers for each department in the descending order.

• SELECT DEPARTMENT, count(WORKER_ID) No_Of_ Workers FROM worker GROUP BY DEPARTMENT SORT BY No_Of_Workers DESC;

• SELECT DEPARTMENT, count(DEPART_ID) No_Of_Depart,count(WORKER_ID) No_Of_Workers GROUP BY DEPARTMENT SORT BY No_Of_Workers DESC;

• SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers FROM worker GROUP BY DEPARTMENT ORDER BY No_Of_Workers DESC

• SELECT DEPARTMENT, count(DEPART_ID) No_Of_Depart,count(WORKER_ID) No_Of_Workers,FROM worker GROUP BY DEPARTMENT SORT BY No_Of_Workers DESC;

Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name).

If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following

SQL query: Select * From Emp, Dept

• The query will result in 25 rows as a “cartesian product” or “cross join”

• The query will result in 35 rows as a “cartesian product” or “cross join”

• The query will result in 15 rows as a “cartesian product” or “cross join”

• The query will result in 50 rows as a “cartesian product” or “cross join”

Write a SQL query to find the 10th highest employee salary from an Employee table.

• SELECT TOP (1) Salary FROM ( SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC ) AS Emp ORDER BY Salary

• SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC AS Emp ORDER BY Salary

• SELECT TOP (10) Salary FROM ( SELECT DISTINCT TOP (1) Salary FROM Employee ORDER BY Salary DESC ) AS Emp ORDER BY Salary

• ELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary ASC AS Emp ORDER BY Salary

How to find a duplicate records with one field?

• SELECT name, COUNT(email) FROM users GROUP BY email HAVING COUNT(email) > 1

• SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) >1

• SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) =1

• SELECT name, COUNT(email) FROM users GROUP BY email HAVING COUNT(email) < 1

Write an SQL query to fetch all the employees who either live in California or work under a manager with ManagerId – 321.

• SELECT EmpId, City, ManagerId FROM EmployeeDetails WHERE City='California' AND ManagerId='321';

• SELECT EmpId, City, ManagerId FROM EmployeeDetails WHERE City=California OR ManagerId='321';

• SELECT EmpId, City, ManagerId FROM EmployeeDetails WHERE City='California' OR ManagerId='321';

• SELECT EmpId, City, ManagerId FROM EmployeeDetails WHERE City='California' AND ManagerId=321;

Write an SQL query to update the employee names by removing leading and trailing spaces.

• UPDATE EmployeeDetails SET FullName = RTRIM(LTRIM(FullName));

• UPDATE EmployeeDetails SET FullName = LTRIM(RTRIM(FullName));

• UPDATE EmployeeDetails SET FullName = RTRIM(FullName);

• UPDATE EmployeeDetails SET FullName <>LTRIM(RTRIM(FullName));

Which of the following operations requires the relations to be union compatible?

• UNION

• INTERSECTION

• DIFFERENCE

• ALL OF THESE

The virtual table that its created by data from the result of an SQL 'Select' statement is called _________

• View

• Synonym

• Sequence

• Transaction

A SQL query will not work if there are no indexes on the relations - Is it true ?

• True

• False