Famous Singers and Hits

/* Create table about the people and what they do here */

CREATE TABLE singers
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fullname TEXT,
    age INTEGER,
    nationality TEXT);
    
INSERT INTO singers (fullname, age, nationality) VALUES ("Ariana Grande", 28, "US");
INSERT INTO singers (fullname, age, nationality) VALUES ("Taylor Swift", 32, "US");
INSERT INTO singers (fullname, age, nationality) VALUES ("Billie Eilish", 20, "US");
INSERT INTO singers (fullname, age, nationality) VALUES ("Ed Sheeran", 31, "UK");
INSERT INTO singers (fullname, age, nationality) VALUES ("Shawn Mendez", 23, "Canada");
INSERT INTO singers (fullname, age, nationality) VALUES ("Dua Lipa", 26, "UK");
INSERT INTO singers (fullname, age, nationality) VALUES ("Justin Beiber", 28, "Canada");
INSERT INTO singers (fullname, age, nationality) VALUES ("Lady Gaga", 36, "US");
INSERT INTO singers (fullname, age, nationality) VALUES ("Bruno Mars", 36, "US");
INSERT INTO singers (fullname, age, nationality) VALUES ("Camilla Cabello", 25, "Cuba");

CREATE table hits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    singer_id INTEGER,
    best_seller TEXT,
    number_one_hits INTEGER);
    
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (1, "Rain On Me", 6);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (2, "Shake It Off", 8);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (3, "Bad Guy", 2);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (4, "Shape of You", 6);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (5, "Stitches", 13);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (6, "Don't Start Now", 1);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (7, "Despacito", 8);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (8, "Shallow", 5);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (9, "Uptown Funk", 5);
INSERT INTO hits(singer_id, best_seller, number_one_hits) VALUES (10, "Havana", 0);

/**return singer and thier number of number one hits**/

SELECT singers.fullname, hits.number_one_hits
FROM singers
JOIN hits
ON singers.id = hits.singer_id;
© 2022 GitHub, Inc.
Terms
Privacy
Security
Status
Docs
Contact GitHub
Pricing
API
Training
Blog
About