Answers to SQL Challenges - Khan Academy / Respostas dos desafios de SQL - Khan Academy

1°)  Challenge: Database with list of books / Desafio: Banco de dados com lista de livros

CREATE TABLE books (id INTEGER PRIMARY KEY, name TEXT, rating TEXT);

INSERT INTO books VALUES (1," book's name", "rating");

INSERT INTO books VALUES (2," book's name", "rating");

INSERT INTO books VALUES (3," book's name", "rating");

SELECT * FROM books;

2°) Challenge: Box office hits database/ Desafio: Banco de dados de sucessos de bilheteria

SELECT * FROM movies; 

SELECT * FROM movies WHERE release_year >= 2000 ORDER BY release_year ;

3°) Challenge: To-do list database statistics / Desafio: Estatísticas do banco de dados de lista de afazeres

INSERT INTO todo_list VALUES (4, "subject", time); 

Select  SUM(minutes) FROM todo_list;

4°) Challenge: Karaoke music selector / Desafio: Seletor de músicas de karaokê

SELECT title FROM songs;

SELECT title

From songs WHERE mood = "epic" OR  released > 1990;

SELECT title FROM songs WHERE mood = "epic" AND  released > 1990 AND duration < 240;

5°)  Challenge: Playlist Creator / Desafio: Criador de lista de reprodução

SELECT title FROM songs WHERE artist = "Queen";

SELECT name From artists WHERE genre = "Pop";

SELECT title FROM songs WHERE artist IN (

Select name From artists WHERE genre = "Pop");

6°) Challenge: The author with more words / Desafio: O autor com mais palavras

SELECT author, SUM(words) as total_words FROM books GROUP BY author 

HAVING total_words > 1000000;

SELECT author, AVG(words) as avg_words FROM books GROUP BY author 

HAVING avg_words > 150000;

7°) Challenge: Activity Book / Desafio: Livro de atividades

SELECT name,number_grade, ROUND(fraction_completed*100) AS percent_completed FROM student_grades;

SELECT COUNT(*),

CASE

WHEN number_grade > 90 THEN "A"

WHEN number_grade > 80 THEN "B"

WHEN number_grade > 70 THEN "C"

ELSE "F"

END AS "letter_grade"

FROM student_grades

GROUP BY (letter_grade);

8°) Challenges: Bobby's hobbies / Desafios: Os hobbies de Bobby

In the table persons:

INSERT INTO Persons (name, age) VALUES ("name",age);

In the table hobbies:

INSERT INTO hobbies (person_id, name) VALUES (id, "hobby's name");

--------------------------------------------------------------------------------------------------------------------------

SELECT persons.name, hobbies.name FROM persons

JOIN hobbies

ON persons.id= hobbies.person_id;

SELECT persons.name, hobbies.name FROM persons

JOIN hobbies

ON persons.id= hobbies.person_id

WHERE persons.name = 'Bobby McBobbyFace'

9°) Challenge: Customer Requests / Desafio: Pedidos dos clientes

SELECT customers.name, customers.email, orders.item, orders.price FROM customers

LEFT OUTER JOIN orders

ON customers.id= orders.customer_id;

SELECT customers.name, customers.email, SUM(price) as orders

FROM customers 

LEFT OUTER JOIN orders 

ON customers.id = orders.customer_id 

GROUP BY name 

ORDER BY price DESC;

10°) Challenge: Sequences in SQL / Desafio: Sequências em SQL

SELECT movies.title, sequel.title as sequel

FROM movies 

LEFT OUTER JOIN movies sequel 

ON movies.sequel_id = sequel.id; 

11°) Challenge: Friend list / Desafio: Lista de amigos

SELECT persons.fullname, hobbies.name 

FROM persons

JOIN hobbies

ON persons.id = hobbies.person_id;

SELECT a1.fullname, a2.fullname FROM friends

JOIN persons a1

ON friends.person1_id = a1.id

JOIN persons a2

ON friends.person2_id = a2.id

12°) Challenge: Dynamic Documents / Desafio: Documentos dinâmicos

UPDATE documents SET author = "Jackie Draper" WHERE author = "Jackie Paper";

SELECT * FROM documents;

DELETE FROM documents WHERE title = "Things I'm Afraid Of";

SELECT * FROM documents;

13°) Challenge: Change of Attire / Desafio: Alterações de vestimenta

ALTER TABLE clothes ADD price INTEGER;

SELECT*FRom clothes;

UPDATE clothes SET price = 10 WHERE id=1;

UPDATE clothes SET price = 20 WHERE id=2;

UPDATE clothes SET price = 30 WHERE id=3;

SELECT*FRom clothes;

INSERT INTO clothes(type, design, price) VALUES ("short", "red and green", 15);

SELECT*FRom clothes;