Codewars Flashcards
This kata is about multiplying a given number by eight if it is an even number and by nine otherwise. Db: multiplication
SELECT number,
CASE
WHEN number%2=0 THEN number * 8
ELSE number * 9
END
AS res
FROM multiplication
In your application, there is a section for adults only. You need to get a list of names and ages of users from the users table, who are 18 years old or older.
users table schema
name
age
SELECT name, age
FROM users
WHERE age >= 18
You are a border guard sitting on the Canadian border. You were given a list of travelers who have arrived at your gate today. You know that American, Mexican, and Canadian citizens don’t need visas, so they can just continue their trips. You don’t need to check their passports for visas! You only need to check the passports of citizens of all other countries!
Select names, and countries of origin of all the travelers, excluding anyone from Canada, Mexico, or The US.
travelers table schema
name
country
SELECT name, country
FROM travelers
WHERE country NOT IN (‘Canada’, ‘Mexico’, ‘USA’);
You received an invitation to an amazing party. Now you need to write an insert statement to add yourself to the table of participants.
participants table schema
name (string)
age (integer)
attending (boolean)
INSERT into participants (name, age, attending) values (‘Eric’, 21, true);
SELECT * FROM participants;
Write a select statement to get a list of all students who haven’t paid their tuition yet. The list should include all the data available about these students.
students table schema
name (string)
age (integer)
semester (integer)
mentor (string)
tuition_received (Boolean)
SELECT *
FROM students
WHERE tuition_received = FALSE;
You work at a book store. It’s the end of the month, and you need to find out the 5 bestselling books at your store. Use a select statement to list names, authors, and number of copies sold of the 5 books which were sold most.
books table schema
name
author
copies_sold
select name, author, copies_sold
from books
order by copies_sold desc
limit 5;
from the 3 African countries (might also be called Afrika) that start with the character E, get the names of their capitals ordered alphabetically.
Schema of the countries table:
country (String)
capital (String)
continent (String)
SELECT capital
FROM countries
WHERE continent IN (‘Africa’, ‘Afrika’)
AND country LIKE ‘E%’
ORDER BY capital
LIMIT 3
In SQL, you will be given a table years with a column yr for the year. Return a table with a column century.
The first century spans from the year 1 up to and including the year 100, the second century - from the year 101 up to and including the year 200, etc.
select yr, ((yr-1)/100)+1 as century
from years
You will be given a table numbers, with one column number.
Return a dataset with two columns: number and is_even, where number contains the original input value, and is_even containing “Even” or “Odd” depending on number column values
SELECT number,
CASE
WHEN number % 2 = 0 THEN ‘Even’
ELSE ‘Odd’
END AS is_even
FROM numbers;
Given a month as an integer from 1 to 12, return to which quarter of the year it belongs as an integer number.
For example: month 2 (February), is part of the first quarter; month 6 (June), is part of the second
select month, cast(ceiling(month/3.0)as int) as res
from quarterof
Return a table with two columns (number1, number2), the value in number1 should be rounded down and the value in number2 should be rounded up.
** decimals table schema **
id
number1
number2
select floor(number1) as number1,
ceiling(number2) as number2
from decimals
Your classmates asked you to copy some paperwork for them. You know that there are ‘n’ classmates and the paperwork has ‘m’ pages.
Your task is to calculate how many blank pages do you need. db:paperwork
SELECT n, m,
CASE WHEN n < 0 OR m < 0 THEN 0
ELSE n*m
END AS res
FROM paperwork;
Clock shows h hours, m minutes and s seconds after midnight.
Your task is to write a function which returns the time since midnight in milliseconds. db:past
SELECT ((h60 + m)60+ s )*1000 as res
FROM past
Write a function that removes the spaces from the string (x), then return the resultant string as res. db:nospace
SELECT x, REPLACE(x, ‘ ‘, ‘’) AS res
FROM nospace
Return a table with one column (mod) which is the output of number1 modulus number2 from the decimals table
select mod(number1, number2) as mod
from decimals
find duplicates
SELECT
column_name,
COUNT() AS count
FROM
your_table_name
GROUP BY
column_name
HAVING
COUNT() > 1;
write a function that takes an argument and returns the square of it. db:square, n:1
SELECT n, n * n AS res FROM square;
use the applications table, where each application has a status represented by the application_status column. A status value of 5 or lower indicates a rejected application, while a status value greater than 5 indicates an approved application.
expected output: statusgroup, applicationnum
SELECT
‘Approved’ AS status_group,
COUNT(application_id) FILTER (WHERE application_status > 5) AS application_num
FROM applications
UNION
SELECT
‘Rejected’ AS status_group,
COUNT(application_id) FILTER (WHERE application_status <= 5) AS application_num
FROM applications
ORDER BY status_group DESC;
Find the total number of interview failures grouped by the failure_reason in the interview_failures table without using the COUNT() function or relying on any auto-incrementing id column.
The result set should contain the following columns:
failure_reason (varchar): reason for the interview failure
cnt (int): count of the employees who failed because of that reason
Order the results by cnt in descending order. If the count is the same for multiple reasons, order them alphabetically by the reason.
select
failure_reason,
sum(1) as cnt
from interview_failures
group by failure_reason
order by 2 desc, 1
Returns records that have matching values in both tables
SQL INNER JOIN
Returns all records from the left table, and the matched records from the right table
Left Join
Returns all records when there is a match in either left or right table
Full Join
Table years has two columns: id, and year.
Your query has to return rows with two columns: year, and is_leap. Find the leap years
Returned rows have to be sorted ascending by the year.
Years divisible by 4 are leap years,
but years divisible by 100 are not leap years,
but years divisible by 400 are leap years.
select
year,
case
when year%400=0 then true
when year%100=0 then false
when year%4=0 then true
else false
end as is_leap
from years
order by year;
or
select
year,
(year % 4 = 0 and year % 100 <> 0 ) or ( year % 400 = 0 ) as is_leap – your code here
from years
order by year asc;
the nearest pump is 50 miles away! You know that on average, your car runs on about 25 miles per gallon. There are 2 gallons left.
Considering these factors, write a function that tells you if it is possible to get to the pump or not.
Function should return true if it is possible and false if not.
db: zerofuel, distance_to_pump, fuel_left
SELECT distance_to_pump, mpg, fuel_left,
CASE
WHEN mpg * fuel_left < distance_to_pump THEN False
ELSE True
END
AS res
FROM zerofuel
join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables.
SELECT *
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID
=
Customers.CustomerID
