• Stars
    star
    1,116
  • Rank 41,621 (Top 0.9 %)
  • Language
  • Created about 6 years ago
  • Updated 12 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Exercises for beginners to learn SQL

Learn SQL

If you haven't already make sure you watch this video which will teach you all the basics of SQL in 60 minutes.

After watching the video try to complete the exercises listed below using the data provided in this repository.

All of the solutions are available in the repository, and this video goes over all of the solutions.

Setup

First drop your existing database that was created in the tutorial. DROP DATABASE record_company;

Copy the code inside the schema.sql file, paste it into MySQL Workbench, and run it. (This file contains the code necessary to create and add the tables from the tutorial video)

Exercises

1. Create a Songs Table

Solution

This table should be called songs and have four properties with these exact names.

  1. id: An integer that is the primary key, and auto increments.
  2. name: A string that cannot be null.
  3. length: A float that represents the length of the song in minutes that cannot be null.
  4. album_id: An integer that is a foreign key referencing the albums table that cannot be null.

After successfully creating the table copy the code from data.sql into MySQL Workbench, and run it to populate all of the data for the rest of the exercises. If you do not encounter any errors, then your answer is most likely correct.

2. Select only the Names of all the Bands

Solution

Change the name of the column the data returns to Band Name

Band Name
Seventh Wonder
Metallica
The Ocean
Within Temptation
Death
Van Canto
Dream Theater

3. Select the Oldest Album

Solution

Make sure to only return one result from this query, and that you are not returning any albums that do not have a release year.

id name release_year band_id
5 ...And Justice for All 1988 2

4. Get all Bands that have Albums

Solution

There are multiple different ways to solve this problem, but they will all involve a join.

Return the band name as Band Name.

Band Name
Seventh Wonder
Metallica
The Ocean
Within Temptation
Death
Van Canto

5. Get all Bands that have No Albums

Solution

This is very similar to #4 but will require more than just a join.

Return the band name as Band Name.

Band Name
Dream Theater

6. Get the Longest Album

Solution

This problem sounds a lot like #3 but the solution is quite a bit different. I would recommend looking up the SUM aggregate function.

Return the album name as Name, the album release year as Release Year, and the album length as Duration.

Name Release Year Duration
Death Magnetic 2008 74.76666593551636

7. Update the Release Year of the Album with no Release Year

Solution

Set the release year to 1986.

You may run into an error if you try to update the release year by using release_year IS NULL in the WHERE statement of your UPDATE. This is because MySQL Workbench by default will not let you update a table that has a primary key without using the primary key in the UPDATE statement. This is a good thing since you almost never want to update rows without using the primary key, so to get around this error make sure to use the primary key of the row you want to update in the WHERE of the UPDATE statement.

8. Insert a record for your favorite Band and one of their Albums

Solution

If you performed this correctly you should be able to now see that band and album in your tables.

9. Delete the Band and Album you added in #8

Solution

The order of how you delete the records is important since album has a foreign key to band.

10. Get the Average Length of all Songs

Solution

Return the average length as Average Song Duration.

Average Song Duration
5.352472513259112

11. Select the longest Song off each Album

Solution

Return the album name as Album, the album release year as Release Year, and the longest song length as Duration.

Album Release Year Duration
Tiara 2018 9.5
The Great Escape 2010 30.2333
Mercy Falls 2008 9.48333
Master of Puppets 1986 8.58333
...And Justice for All 1988 9.81667
Death Magnetic 2008 9.96667
Heliocentric 2010 7.48333
Pelagial 2013 9.28333
Anthropocentric 2010 9.4
Resist 2018 5.85
The Unforgiving 2011 5.66667
Enter 1997 7.25
The Sound of Perseverance 1998 8.43333
Individual Thought Patterns 1993 4.81667
Human 1991 4.65
A Storm to Come 2006 5.21667
Break the Silence 2011 6.15
Tribe of Force 2010 8.38333

12. Get the number of Songs for each Band

Solution

This is one of the toughest question on the list. It will require you to chain together two joins instead of just one.

Return the band name as Band, the number of songs as Number of Songs.

Band Number of Songs
Seventh Wonder 35
Metallica 27
The Ocean 31
Within Temptation 30
Death 27
Van Canto 32

More Repositories

1

useful-custom-react-hooks

JavaScript
1,927
star
2

Zoom-Clone-With-WebRTC

JavaScript
1,515
star
3

Face-Detection-JavaScript

JavaScript
937
star
4

Introduction-to-Web-Development

HTML
814
star
5

React-Firebase-Auth

JavaScript
641
star
6

react-folder-structure

JavaScript
588
star
7

Vanilla-JavaScript-Calculator

Pure vanilla JavaScript calculator using modern ES6 syntax and classes
JavaScript
510
star
8

Nodejs-Passport-Login

JavaScript
404
star
9

Whatsapp-Clone

JavaScript
388
star
10

JWT-Authentication

JavaScript
335
star
11

Design-Patterns

A collection of popular design patterns with video and text explanations
JavaScript
261
star
12

React-Infinite-Scrolling

JavaScript
251
star
13

spotify-clone

JavaScript
249
star
14

Markdown-Blog

HTML
245
star
15

Realtime-Simple-Chat-App

JavaScript
240
star
16

Learn-React-Today

JavaScript
236
star
17

react-ts-shopping-cart

TypeScript
230
star
18

Face-Recognition-JavaScript

JavaScript
217
star
19

google-docs-clone

JavaScript
205
star
20

youtube-video-player-clone

JavaScript
204
star
21

Learn-GraphQL

JavaScript
201
star
22

JavaScript-Quiz-App

JavaScript
195
star
23

next-js-ecommerce-mvp

TypeScript
188
star
24

React-Simplified-Beginner-Projects

JavaScript
176
star
25

nodejs-user-permissions

JavaScript
175
star
26

React-CodePen-Clone

JavaScript
175
star
27

wordle-clone

JavaScript
172
star
28

Learn-React-In-30-Minutes

JavaScript
163
star
29

Mybrary

JavaScript
162
star
30

Web-Dev-Simplified-Official-Blog

MDX
157
star
31

Noob-Vs-Pro-Code

JavaScript
157
star
32

url-shortener

HTML
143
star
33

css-tutorials

CSS
142
star
34

Your-First-Node-REST-API

JavaScript
141
star
35

GitHub-Jobs-API-React-App

JavaScript
137
star
36

js-util-functions

JavaScript
137
star
37

react-todo-list

135
star
38

Nodejs-User-Authentication

JavaScript
134
star
39

Spotify-Landing-Page-Redesign

CSS
132
star
40

Realtime-Chat-App-With-Rooms

JavaScript
131
star
41

Javascript-Snake-Game

JavaScript
129
star
42

chat-gpt-api

JavaScript
128
star
43

react-query-crash-course-example

JavaScript
126
star
44

JavaScript-Tic-Tac-Toe

CSS
124
star
45

react-multistep-form

TypeScript
124
star
46

firebase-google-drive-clone

JavaScript
123
star
47

react-calculator

JavaScript
122
star
48

Node.js-Stripe-Shopping-Cart

Example integration of Stripe's Checkout API into a Node.js application
HTML
119
star
49

React-Flashcard-App

JavaScript
116
star
50

JavaScript-Simplified

HTML
116
star
51

css-parallax

HTML
115
star
52

next-13-todo-list

112
star
53

twitter-clone

TypeScript
111
star
54

react-note-taking-app

TypeScript
111
star
55

Paginated-API-Express

JavaScript
111
star
56

postman-clone

JavaScript
111
star
57

Learn-CSS-Today

HTML
109
star
58

nested-comments

JavaScript
109
star
59

react-budget-app

JavaScript
109
star
60

React-Currency-Converter

JavaScript
109
star
61

JavaScript-Clock

CSS
105
star
62

chrome-dino-game-clone

JavaScript
105
star
63

JavaScript-Piano

HTML
104
star
64

skeleton-loading

HTML
100
star
65

Drag-And-Drop

JavaScript
93
star
66

React-Simplified-Advanced-Projects

JavaScript
91
star
67

stripe-checkout-simple

JavaScript
91
star
68

JavaScript-Simplified-Advanced-Projects

JavaScript
91
star
69

logical.so-scroll-animation

CSS
88
star
70

express-crash-course

JavaScript
86
star
71

youtube-react-ts-tailwind-home-page

TypeScript
84
star
72

react-select

TypeScript
81
star
73

Google-Maps-Clone

HTML
80
star
74

Vanilla-JavaScript-Modal

HTML
80
star
75

react-navbar

HTML
79
star
76

file-sharing-node-js

JavaScript
78
star
77

js-search-bar

JavaScript
74
star
78

js-weather-app

71
star
79

js-css-carousel

CSS
68
star
80

React-Photoshop-Clone

JavaScript
67
star
81

React-Modal-Popup

JavaScript
66
star
82

netflix-sliding-movie-ui

JavaScript
65
star
83

js-promise-library

JavaScript
64
star
84

typescript-todo-list

TypeScript
63
star
85

advanced-dropdown

HTML
63
star
86

js-2048

JavaScript
62
star
87

javascript-text-to-speech

JavaScript
62
star
88

react-hangman

TypeScript
62
star
89

css-only-hamburger-menu

CSS
62
star
90

storybook-react-crash-course

JavaScript
62
star
91

live-toast-notification-library

JavaScript
62
star
92

YouTube-Design-Clone

HTML
61
star
93

vscode-extension-pizza

TypeScript
61
star
94

JavaScript-Text-Adventure

JavaScript
61
star
95

facebook-messenger-clone

TypeScript
57
star
96

War-Card-Game

JavaScript
56
star
97

custom-select-dropdown

JavaScript
55
star
98

JavaScript-Simplified-Bonus-Projects

JavaScript
55
star
99

debounce-throttle-js

JavaScript
55
star
100

JavaScript-Password-Generator

JavaScript
55
star