Font size
WorksheetsIVY SQL Internship Assessment
Total questions: 13
Worksheet time: 59mins
Full Name
Email ID
Student Id
ID is the primary key in the adjacent table.
Each row of this table contains an email. The emails will not contain uppercase letters.
Which SQL query shall delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.
DELETE * FROM Person WHERE Id NOT IN
(SELECT * FROM (
SELECT MIN (Id) FROM Person GROUP BY Email);
DELETE FROM Person WHERE Id NOT IN
(SELECT * FROM (
SELECT MIN (Id) FROM Person) as p);
DELETE FROM Person WHERE COUNT(Id)>1;
DELETE FROM Person WHERE Id NOT IN
(SELECT MIN (Id) FROM Person GROUP BY Email) as p);
Id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.
Which SQL query shall return the second highest salary from the Employee table. If there is no second highest salary, the query should report null.
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 2) AS SecondHighestSalary;
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary ASC
LIMIT 1 OFFSET 1) AS SecondHighestSalary;
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary;
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 2) AS SecondHighestSalary;
Customer Table: id is the primary key column for
this table. Each row of this table indicates the ID and name of a customer.
Orders Table: id is the primary key column for this table.
customerId is a foreign key of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.
Which SQL query will report all customers who never order anything.
Return the result table in any order.
SELECT customers.name AS 'Customers'
FROM customers
WHERE customers.id NOT IN Orders;
SELECT customers.name AS 'Customers'
FROM customers
WHERE customers.id NOT IN
(SELECT customerid FROM orders);
SELECT customers.id AS 'Customers'
FROM customers
WHERE customers.id <> Orders;
SELECT customers.name AS 'Customers'
FROM customers
WHERE customers.id IN
(SELECT customerid FROM orders);
The station table has been defined alongside. Where LAT_N is the northern latitude and LONG_W is the western longitude. Write a Query to list CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.
SELECT DISTINCT CITY FROM STATION WHERE SUM(STATION.ID,2)=0 ORDER BY CITY;
SELECT DISTINCT CITY FROM STATION WHERE MEDIAN(STATION.ID,2)=0 ORDER BY CITY;
SELECT DISTINCT CITY FROM STATION WHERE MOD(STATION.ID,2)=0 ORDER BY CITY;
SELECT DISTINCT CITY FROM STATION WHERE STATION.ID=2 ORDER BY CITY;
The TRIANGLES table is described as follows: Each row in the table denotes the lengths of each of a triangle's three sides.
Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:
· Equilateral: It's a triangle with sides of equal length.
· Isosceles: It's a triangle with sides of equal length.
· Scalene: It's a triangle with sides of differing lengths.
· Not A Triangle: The given values of A, B, and C don't form a triangle.
SELECT CASE
WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
WHEN A = B AND B = C THEN 'Isosceles'
WHEN A = B OR B = C OR A = C THEN 'Equilateral'
ELSE 'Scalene'
END
FROM TRIANGLES;
SELECT CASE
WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
WHEN A = B AND B = C THEN 'Equilateral'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
ELSE 'Scalene'
END
FROM TRIANGLES;
SELECT
A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
CASE
WHEN A = B AND B = C THEN 'Equilateral'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
ELSE 'Scalene'
END
FROM TRIANGLES;
SELECT
WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
WHEN A = B AND B = C THEN 'Equilateral'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
ELSE 'Scalene'
END
FROM TRIANGLES;
id is the primary key for this table.
This table contains information about the temperature on a certain day.
Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday). Return the result table in any order.
SELECT weather.id AS 'Id'
FROM weather
JOIN weather w ON TIMESTAMPDIFF(weather.recordDate, w.recordDate) = 1
AND weather.Temperature > w.Temperature;
SELECT weather.id AS 'Id'
FROM weather
JOIN weather w ON DATEDIFF(weather.recordDate, w.recordDate) > 1
AND weather.Temperature > w.Temperature;
SELECT weather.id AS 'Id'
FROM weather
JOIN weather w ON DATE(weather.recordDate, w.recordDate) = 1
AND weather.Temperature > w.Temperature;
SELECT weather.id AS 'Id'
FROM weather
JOIN weather w ON DATEDIFF(weather.recordDate, w.recordDate) = 1
AND weather.Temperature > w.Temperature;
name is the primary key column for this table.
Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.
A country is big if:
it has an area of at least three million (i.e., 3000000 km2), or
it has a population of at least twenty-five million (i.e., 25000000).
Write an SQL query to report the name, population, and area of the big countries.
Return the result table in any order.
The query result format is in the example given alongside.
SELECT name, population, area FROM world
WHERE area >= 3000000 OR population >= 25000000;
SELECT name, population, area
FROM world
WHERE population >= 3000000 OR area >= 25000000;
SELECT name, population, area FROM world WHERE area <= 3000000 OR population >= 25000000;
SELECT name, population, area FROM world WHERE area >= 3000000 OR population <= 25000000;
(Student, class) is the primary key column for this table.
Each row of this table indicates the name of a student and the class in which they are enrolled.
Write an SQL query to report all the classes that have at least five students.
SELECT class
FROM courses
GROUP BY class
HAVING COUNT (DISTINCT student) <= 5;
SELECT class
FROM courses
HAVING COUNT (DISTINCT student) >= 5;
SELECT class
FROM courses
GROUP BY class
HAVINGCOUNT (student) >= 5;
SELECT class
FROM courses
GROUP BY class
HAVING COUNT (DISTINCT student) >= 5;
id is the primary key for this table.
Each row contains information about the name of a movie, its genre, and its rating. Rating is a 2 decimal places float in the range [0, 10]
Write an SQL query to report the movies with an odd-numbered ID and a description that is not "boring".
Return the result table ordered by rating in descending order.
The query result format is in the adjacent example.
select *
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating ASC;
select *
from cinema
where mod(id, 0) = 1 and description != 'boring'
order by rating DESC;
select *
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating DESC;
select *
from cinema
where mod(id, 2) = 1 and description != 'not boring'
order by rating DESC;
personId is the primary key column for Person's table.
This table contains information about the ID of some persons and their first and last names.
addressId is the primary key column for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.
Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
Return the result table in any order.
select FirstName, LastName, City, State
from Person Having Address
on Person.PersonId = Address.PersonId;
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId;
select FirstName, LastName, City, State
from Person left join Id
on Person.PersonId = Address.PersonId;
select FirstName, LastName, City, State
from Person union Address
on Person.PersonId = Address.PersonId;
