6/30/2023 0 Comments Tableau prep pivot aliases![]() WHERE 2 >= (SELECTCOUNT(DISTINCT Salary)FROM EmployeePosition E2 SELECT DISTINCT Salary FROM EmployeePosition E1 To retrieve two minimum salaries, you can write a query as below: Write a query to retrieve two minimum and maximum salaries from the EmployeePosition table. (SELECT * FROM EmployeePosition P WHERE E.EmpId = P.EmpId) Write a SQL query to retrieve employee details from EmployeeInfo table who have a date of joining in the EmployeePosition table. SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=1 Similarly, to retrieve the odd records from a table, you can write a query as follows: SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=0 To retrieve the even records from a table, you have to use the MOD() function as follows: Write a query to calculate the even and odd records from a table. SELECT Department, count(EmpID) AS EmpDeptCount Write a query to fetch the department -wise count of employees sorted by department’s count in ascending order. SELECT E.EmpFname, E.EmpLname, P.EmpPositionįROM EmployeeInfo E INNER JOIN EmployeePosition P ONĮ.EmpID = P.EmpID AND P.EmpPosition IN ('Manager') Write a query to fetch all employees who also hold the managerial position. SELECT * FROM EmployeeInfo WHERE Address LIKE 'DELHI(DEL)%' Write a query to fetch details of employees with the address as “DELHI(DEL)”. SELECT * FROM EmployeeInfo WHERE EmpFname NOT IN ('Sanjay','Sonia') Write a query to fetch details of all employees excluding the employees with first names, “Sanjay” and “Sonia” from the EmployeeInfo table. SELECT * FROM EmployeeInfo WHERE EmpLname LIKE '_a' To fetch details mathcing a certain value, you have to use the LIKE operator in SQL. Write a query to fetch details of employees whose EmpLname ends with an alphabet ‘A’ and contains five alphabets. SELECT * FROM EmployeeInfo ORDER BY EmpFname desc, Department asc To order the records in ascending and descnding order, you have to use the ORDER BY statement in SQL. Write a query to fetch all the records from the EmployeeInfo table ordered by EmpLname in descending order and Department in the ascending order. SELECT COUNT(*), Gender FROM EmployeeInfo WHERE DOB BETWEEN ' ' AND '' GROUP BY Gender Write a query find number of employees whose DOB is between to and are grouped according to gender SELECT CONCAT(EmpFname, ' ', EmpLname) AS 'FullName' FROM EmployeeInfo The first name and the last name must be separated with space. Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”. SELECT * FROM EmpPosition ORDER BY Salary DESC LIMIT N SELECT MID(Address, 0, LOCATE('(',Address)) FROM EmployeeInfo Write a query to fetch only the place name(string before brackets) from the Address column of EmployeeInfo table. SELECT SUBSTRING(EmpLname, 1, 4) FROM EmployeeInfo Write a query to retrieve the first four characters of EmpLname from the EmployeeInfo table. You can write a query as follows in MySQL: You can write a query as follows in SQL Server: SELECT COUNT(*) FROM EmployeeInfo WHERE Department = 'HR' ![]() Write a query to fetch the number of employees working in the department ‘HR’. SELECT UPPER(EmpFname) AS EmpName FROM EmployeeInfo Write a query to fetch the EmpFname from the EmployeeInfo table in upper case and use the ALIAS name as EmpName. ![]() Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”.Write a query to find the names of employees that begin with ‘S’. ![]() Write q query to find all the employees whose salary is between 50000 to 100000.Write a query to create a new table that consists of data and structure copied from the other table. ![]()
0 Comments
Leave a Reply. |