Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

MySQL Queries

memorize.ai (lvl 286)

Section 1

Preview this deck

Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?

Front

1 / 19

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

1 year ago

Date created

Mar 1, 2020

Cards (19)

Section 1

(19 cards)

Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?

Front

SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno) then delete: DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);

Back

Write an SQL Query to check whether date passed to Query is the date of given format or not.

Front

SELECT ISDATE('1/08/13') AS "MM/DD/YY";

Back

SQL Query to find Max Salary from each department id.

Front

SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.

Back

SQL Query to find second highest salary of Employee

Front

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

Back

Write an SQL Query to find the year from date.

Front

SELECT YEAR(GETDATE()) as "Year";

Back

Write SQL Query to display the current date.

Front

SELECT GetDate();

Back

Write an SQL Query to find name of employee whose name Start with 'M'

Front

SELECT * FROM Employees WHERE EmpName like 'M%';

Back

find products whose product codes contain the string _20 , you can use the pattern %\_20%

Front

SELECT productCode, productName FROM products WHERE productCode LIKE '%\_20%';

Back

search for employees whose last names don't start with the character B, you can use the NOT LIKE with a pattern as shown in the following query:

Front

SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';

Back

Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

Front

SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975';

Back

SQL Query to find Max Salary from each department Name.

Front

SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;

Back

Write an SQL Query to find an employee whose Salary is equal or greater than 10000.

Front

SELECT EmpName FROM Employees WHERE Salary>=10000;

Back

Write an SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.

Front

SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;

Back

To find employees whose first names start with T , end with m, and contain any single character between e.g., Tom , Tim, you use the underscore (_) wildcard to construct the pattern as follows:

Front

SELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';

Back

What is different using percentage ( % ) wildcard and underscore ( _ ) wildcard in MySQL?

Front

The percentage ( % ) wildcard matches any string of zero or more characters. The underscore ( _ ) wildcard matches any single character.

Back

find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

Front

SELECT * from Employees WHERE UPPER(EmpName) like '%JOE%';

Back

There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students.

Front

SELECT student, marks from table where marks > SELECT AVG(marks) from table)

Back

find all employees whose last names contain on , you use the following query with the pattern %on%

Front

SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';

Back

How do you find all employees which are also manager? You have given a standard employee table with an additional column mgr_id, which contains employee id of the manager. , mgr_id

Front

Answer: You need to know about self-join to solve this problem. In Self Join, you can join two instances of the same table to find out additional details as shown below SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

Back