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;
obrigado por postar , no Khan tem hora que a gente enrosca
ResponderExcluirDEBERIAS SUBIR LOS PROYECTOS IGUAL.
ResponderExcluir