COMP3278 2025
TA: Tianxing Chen
chentianxing@[Link]
SQL Question 1
• An employees table
ID (Primary key) Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
• How to find the name of highest salary in SQL?
• How to find the name of 2nd highest salary in SQL?
• How to find the name of nth highest salary in SQL?
SQL Question 1
• An employees table
ID (Primary key) Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
• We can first create the table with the following sql.
CREATE TABLE employees ( ID INT PRIMARY KEY, Name
VARCHAR(50), Age INT, Salary INT);
INSERT INTO employees (ID, Name, Age, Salary) VALUES (1, 'Alice',
24, 20000), (2, 'Bob', 23, 21000), (3, 'Carol', 30, 30000);
SQL Question 1.1
• An employees table
ID (Primary key) Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
• How to find the name of highest salary
in SQL?
• MAX() function
• SELECT Name, MAX(Salary)
FROM employees;
SQL Question 1.1
• An employees table
ID (Primary key) Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
• How to find the name of highest salary in
SQL?
• MAX() function
• SELECT Name FROM employees
WHERE Salary = (SELECT
MAX(Salary) FROM employees);
SQL Question 1.2
• An employees table
ID (Primary key) Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
• How to find the name of 2nd highest salary in SQL?
• MAX() function
• SELECT name, salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees
WHERE salary < (
SELECT MAX(salary) FROM employees));
SQL Question 1.2
• An employees table
Id (Pri key) Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
• How to find N-th highest salary in SQL?
• MAX() function
• select name, salary from employees
order by salary desc limit 1 offset N-1;
SQL Question 2
• An employees table
Id Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
4 Bob 23 21000
• How to delete duplicate rows?
SQL Question 2
• A employees table
Id Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
4 Bob 23 21000
• How to delete duplicate rows?
• [Link] duplicate rows by SELECT
• 2. delete duplicate rows by DELETE
SQL Question 2
• A employees table
Id Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
4 Bob 23 21000
• Method 1:
• DELETE JOIN
SQL Question 2
• A employees table
Id Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
4 Bob 23 21000
• Method 1:
• DELETE t1 FROM employees t1, employees t2
WHERE [Link] < [Link] AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link];
SQL Question 2
• A employees table
Id Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
4 Bob 23 21000
• Method 2: Create a new table which do not have the same row
SQL Question 2
• A employees table
Id Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
4 Bob 23 21000
• Method 2: Create a new table which do not have the same row
• CREATE TABLE employees_copy AS SELECT DISTINCT Name, Age, Salary
FROM employees;
• DROP TABLE employees;
• ALTER TABLE employees_copy RENAME TO employees;
SQL Question 2
• A employees table
Id Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
4 Bob 23 21000
• Method 3: using the ROW_NUMBER() function (MySQL >=8.0)
SQL Question 2
• A employees table
Id Name Age Salary
1 Alice 24 20000
2 Bob 23 21000
3 Carol 30 30000
4 Bob 23 21000
• Method 3: using the ROW_NUMBER() function (MySQL >=8.0)
• ROW_NUMBER:
• Allocate each row a now id according to partition by (…) .
SQL Question 2
• Method 3: using the ROW_NUMBER() function (MySQL >=8.0)
• ROW_NUMBER:
• Allocate each row a now id according to partition by (…) .
• SELECT *, row_number() OVER (
PARTITION BY Name, Age, Salary
) as row_num FROM employees;
Remove all
row_number() >1 rows
SQL Question 2
• Method 3: using the ROW_NUMBER() function (MySQL >=8.0)
• ROW_NUMBER:
• Allocate each row a now id according to partition by (…) .
• SELECT id FROM(
SELECT *, row_number() OVER (
PARTITION BY Name, Age, Salary
) as row_num FROM employees
) t WHERE row_num > 1;
Remove all
row_number() >1 rows
SQL Question 2
• Method 3: using the ROW_NUMBER() function (MySQL >=8.0)
• DELETE FROM employees
WHERE id IN (
SELECT id FROM(
SELECT *, row_number() OVER (
PARTITION BY Name, Age, Salary
) as row_num FROM employees
) t WHERE row_num > 1
Remove all
) row_number() >1 rows
);
SQL Question 3
• A number table
id num
1 1
2 1
3 1
4 2
5 1
6 2
7 2
• Find all numbers that appear at least three times consecutively.
SQL Question 3
• A number table
id num
• Find all numbers that appear at least 1 1
three times consecutively. 2 1
3 1
4 2
• Equals to:
5 1
• (id, num) 6 2
• (id+1, num) 7 2
• (id+2, num)
• All exist in this table
SQL Question 3
• A number table
id num
• Find all numbers that appear at least
1 1
three times consecutively.
2 1
3 1
• SELECT * FROM Number l1, 4 2
Number l2, 5 1
6 2
Number l3,
7 2
WHERE
[Link] = [Link] - 1 AND [Link] = [Link] -1
AND [Link] = [Link]
AND [Link] = [Link];