1. Query the list of CITY names from a table that do not start with vowels and do not end with vowels. And your result cannot contain duplicates.
SELECT DISTINCT CITY FROM table
WHERE SUBSTR(CITY,1,1) NOT IN ('a','e','i','o','u','A','E','I','O','U') AND
SUBSTR(CITY,-1,1) NOT IN ('a','e','i','o','u','A','E','I','O','U');2. Create a table(only its structure) from a existing table.
SELECT * INTO newtable
FROM existingtable
WHERE 1=0; #Doesn't take any rows3. Write a SQL query to find highest salary in department.
SELECT Dept_Id, MAX(Salary)
FROM Employees
GROUP BY Dept_Id;4. Write a SQL query to find highest salary and department name where employee and department are different tables.
SELECT
employee.name
department.departmant_name as Dept_Name
MAX(employee.salary) as Max_Salary
FROM employee
INNER JOIN department
ON employee.Dept_Id = department.Id
GROUP BY Dept_Name;5. Query to find all duplicate emails in a table.
SELECT email
FROM person
GROUP BY email
HAVING COUNT(email) > 1;6. Fetch emp name in upper case from the Employee table and use alias.
SELECT UPPER(emp_name) AS emp_first_name
FROM Employees;7. Fetch top N records and sort by Salary.
SELECT TOP N * FROM Employees
ORDER BY Salary DESC;8. Retreive employee first name and emp last name in a single column as "fulll name" seperated by space.
SELECT CONCAT(empfname,' ',emplname) AS fullname
FROM employees;9. Retrieve emp_position along with total salaries paid for each of them.
SELECT emp_position, SUM(salary)
FROM employees
GROUP BY emp_position;
10. Fetch details of employees with address as "DELHI"
SELECT * FROM employees
WHERE address LIKE 'DELHI%'
11. Fetch only the first name from full name column
SELECT SUBSTRING(fullname,0, Charindex(' ', fullname)) # charindex gives index
FROM employees;
12. Fetch duplicate records from a a table
SELECT emp_id, dept, COUNT(*)
FROM employees
GROUP BY emp_id, dept
HAVING COUNT(*) > 1;
13. Remove Duplicates
DELETE FROM employees
WHERE emp_id IN (SELECT emp_id FROM employees
GROUP BY dept
HAVING COUNT(*)>1);
14. Create empty table with same structure as some other table
SELECT * INTO Newtable FROM oldtable
WHERE 1=0;
15. Fetch common records between 2 tables.
SELECT * FROM table1
INTERSECT
SELECT * FROM table2
16. Increase Income of all employees by 5% in a table.
UPDATE Employees SET income = income + (income*0.05)
17. Find names of employee starting with 'A"
SELECT firstname FROM employees
WHERE firstname LIKE 'A%'
18. Find number of employees working in department 'ABC'
SELECT COUNT(*) FROM employees
WHERE department = 'ABC'
19. Print details of employees whose first name ends with A and contains 6 Alphabets.
SELECT * FROM employees
WHERE firstname LIKE '_ _ _ _ _ A'
20. Print details of employees whose salary lies between 10000 and 50000.
SELECT * FROM employees
WHERE salary BETWEEN 10000 AND 50000
21. Second Highest Salary
Method : 1
SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee
WHERE salary) # inner query
Method : 2
SELECT salary FROM (SELECT salary FROM employee
ORDER BY salary DESC LIMIT 2) AS emp
ORDER BY salary ASC
LIMIT 1;
Method : 3
SELECT TOP 1 salary FROM (SELECT TOP 2 Salary FROM Employee
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
22. How to remove duplicate rows from a table
Method : 1 : Using a temporary table
SELECT DISTINCT * INTO new_table
FROM old_table;
DELETE * FROM old_table;
INSERT INTO old_table SELECT * from new_table;
DROP TABLE new_table;
Method : 2 : row_numbers()
DELETE FROM (SELECT *, row_numbers() OVER (
PARTITION BY emp_id ORDER BY id) as rn FROM employee)
WHERE rn>1;
Method : 3 : CTE (common table expression)
* Used as a temporary result set.
* lasts only for the duration of the query.
WITH cte
AS (SELECT *, row_numbers() OVER (
PARTITION BY emp_id ORDER BY id) as rn FROM employee)
DELETE FROM cte WHERE rn>1;
23. Query the name of students who scored higher than 75 marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e Kavya, navya etc..), secondary sort then by ascending ID.
SELECT name FROM students
WHERE marks > 75
Order BY SUBSTR(name, -3, 3), ID ;
24. Query an alphabetically ordered list of all the names, immediately followed by the first letter of each profession enclosed in paranthesis.
Eg: Jake Doctor = Jake(D)
SELECT CONCAT(Name, "(" , SUBSTR(profession,1,1) , ")" )
FROM occupations;
25. Query to find all duplicates in a table.
SELECT Id FROM employees
GROUP BY Id
HAVING COUNT(Id) > 1;
26. Query to find Nth highest salary
Eg: 3
SELECT TOP 1 FROM (SELECT TOP 3 salaries FROM employees
ORDER BY Salary DESC)
ORDER BY salary ASC;
27. Query to fetch all the employees who are also managers from the employee table.(SELF JOIN)
FROM employees AS e
INNER JOIN employees AS m
On e.Id = m.Id;
28. Select the job and the minimum and maximum salaries for each group of rows with same job code, but only for groups with more than one row and with a maximum salary greater than or equal to 27000.
SELECT job_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY job_id
HAVING MAX(salary)>=27000 AND COUNT(*)>1;
29. How to add unique row no?
SELECT row_number() OVER() AS row_no, car_code, car_name, units_sold
FROM sales;
30. How to find first, second and third largest marks obtained in science.
SELECT TOP 3 marks FROM students
WHERE subject = "Science"
ORDER BY marks DESC;
31. Query the number of occurences of each occupation. Sort in ascending order and output them in the format. 0/p : There are total of [occupation.count][occupation].
SELECT CONCAT("There is a total of ", COUNT(*), " " , LOWER(occupation), "s")
FROM Occupations
GROUP BY occupation
ORDER BY COUNT(occupation), LOWER(occupation);
32. Salaries - min max avg
SELECT MAX(salary), MIN(salary), AVG(salary), dept_id
FROM emp
GROUP BY dept_id
33. Get the current date
SELECT sysdate();
34. Query to retreive the first two characters of emp last name from the employees table.
SELECT SUBSTR(lastname, 1 , 2) FROM employees;
35. Query to create a new table with consists of data and structure copied from other table.
CREATE TABLE tmp AS SELECT * FROM employees;
SELECT * FROM tmp
36. Query to create a new table with only the structure copied from the other table.
CREATE TABLE tmp AS SELECT * FROM employees
WHERE 1=0;
37. Query to fetch first N records.
SELECT * FROM employees LIMIT 5;
38. Query to retrieve the emp first name and emp last name in a asingale column as fullname. The first name and the last name must be seperated with space.
SELECT CONCAT(firstname, " ", lastname) AS 'Full name' FROM employees;
39. Query to fetch details of employees whose emp last name end with an alphabet "s" and contains five alphabets.
SELECT * FROM employees
WHERE lastname LIKE "_ _ _ _S";
40. Query to fetch details of all employees ecluding the employees with first_name "SMITH" and "BLAKE".
SELECT * FROM employees
WHERE FIRST_NAME NOT IN ("SMITH", "BLAKE");
41. Fetch department wise count from employees sorted by department count in ascending order.
SELECT COUNT(empid) AS c FROM employees
GROUP BY department
ORDER BY c;
42. Query to retreive duplicate records from a table.
SELECT * , COUNT(empid) FROM employees
GROUP BY empid
HAVING COUNT(empid) >1;
Comments