Регистрируемся на сайте HackerRank

Зарегился на https://www.hackerrank.com/ , email: mishaikon@gmail.com

Задачи на знание SQL

1. Revising the Select Query I

https://www.hackerrank.com/challenges/revising-the-select-query/problem?isFullScreen=true

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

The CITY table is described as follows:

Мое решение:

SELECT *
FROM CITY
WHERE POPULATION > 100000
AND countrycode = 'USA';

Принято: “You have earned 10.00 points!

You are now 70 points away from the 1st star for your sql badge.“

2. Revising the Select Query II

Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

The CITY table is described as follows:

Мое решение:

SELECT name
FROM CITY
WHERE POPULATION > 120000
AND countrycode = 'USA';

3. Query all columns (attributes) for every row in the CITY table.

The CITY table is described as follows:

Решение:

SELECT * FROM CITY;

“Select by ID“

https://www.hackerrank.com/challenges/select-by-id/problem?isFullScreen=true

Query all columns for a city in CITY with the ID 1661.

The CITY table is described as follows: …

OK:

select * 
from city
where id = 1661

SQL (Basic) Skills Certification Test

https://www.hackerrank.com/skills-verification/sql_basic

Test task: “Count the empployyes”

https://www.hackerrank.com/test/74ea7mk9cpp/questions/8i50d4dm339

Solution:

select id
from company
where employees > 10000;

Task 1. Student advisor

Solution:

select distinct roll_number, name
from student_information
where advisor in (
   select employee_ID
   from faculty_information
   where (gender = "M" and salary > 15000)
   OR (gender = "F" and salary > 20000)  
);

Task 2. Profitable stocks

https://www.hackerrank.com/test/dq598o79cei/questions/9c1fnhmrt93

Solution:

select distinct p1.stock_code
from price_tomorrow p1
join price_today p2
on p1.stock_code = p2.stock_code
where p1.price > p2.price;

We are evaluating your submitted code. This might sometimes take up to 30 minutes.
We will send you an email when your results are ready.

Ура! Пришло подтверждение, что сертификация пройдена: https://mail.google.com/mail/u/0/#search/hackerrank/FMfcgzGllVtKBcplqpdfmRqlPgtsrSnK