Skip to content

Instantly share code, notes, and snippets.

@crisbal
Created June 26, 2017 14:43
  • Star 18 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save crisbal/f072ecb8d43a8ecc58dda968e88417bb to your computer and use it in GitHub Desktop.
DROP DATABASE IF EXISTS ๐Ÿ“š;
CREATE DATABASE ๐Ÿ“š;
USE ๐Ÿ“š;
CREATE TABLE ๐Ÿ‘ค(
๐Ÿ”‘ INTEGER PRIMARY KEY,
๐Ÿ—ฃ varchar(64), -- name
๐Ÿ—“ DATE -- date of registration
);
CREATE TABLE โœ(
๐Ÿ”‘ INTEGER PRIMARY KEY,
๐Ÿ—ฃ varchar(64)
);
CREATE TABLE ๐Ÿ“•(
๐Ÿ”‘ INTEGER PRIMARY KEY,
๐Ÿ’ฌ varchar(64), -- title
๐Ÿ”– varchar(64), -- genre/tag
โœ INTEGER REFERENCES โœ(๐Ÿ”‘) -- who wrote the book?
);
CREATE TABLE ๐Ÿ‘ค๐Ÿ ๐Ÿ“•(
๐Ÿ‘ค INTEGER REFERENCES ๐Ÿ‘ค(๐Ÿ”‘),
๐Ÿ“• INTEGER REFERENCES ๐Ÿ“•(๐Ÿ”‘)
);
INSERT INTO ๐Ÿ‘ค VALUES
(1, 'Jeff', CURRENT_DATE),
(2, 'Annie', CURRENT_DATE);
INSERT INTO โœ VALUES
(1, 'Herman Melville'),
(2, 'Lewis Carroll');
INSERT INTO ๐Ÿ“• VALUES
(1, 'Alice in Wonderland', '๐Ÿ”ฎ', 2), -- genre is fantasy
(2, 'Moby Dick', '๐Ÿ“–', 1), -- genre is novel
(3, 'Through the Looking-Glass', '๐Ÿ”ฎ', 2); -- genre is fantasy
INSERT INTO ๐Ÿ‘ค๐Ÿ ๐Ÿ“• VALUES
(1, 1), -- Jeff took home 'Alice in Wonderland'
(1, 2), -- Jeff took home 'Moby Dick'
(2, 2); -- Annie took home 'Moby Dick'
-- get the book taken home by each people
SELECT ๐Ÿ‘ค.๐Ÿ—ฃ AS ๐Ÿ‘ค, ๐Ÿ“•.๐Ÿ’ฌ AS ๐Ÿ“•
FROM ๐Ÿ‘ค JOIN ๐Ÿ‘ค๐Ÿ ๐Ÿ“• ON ๐Ÿ‘ค.๐Ÿ”‘ = ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.๐Ÿ‘ค
JOIN ๐Ÿ“• ON ๐Ÿ“•.๐Ÿ”‘ = ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.๐Ÿ“•;
-- define an enum for the book length
CREATE TYPE ๐Ÿ“„ AS ENUM ('๐Ÿ“„', '๐Ÿ“„๐Ÿ“„', '๐Ÿ“„๐Ÿ“„๐Ÿ“„', '๐Ÿ“„๐Ÿ“„๐Ÿ“„๐Ÿ“„');
ALTER TABLE ๐Ÿ“•
ADD COLUMN ๐Ÿ”ข๐Ÿ“„ ๐Ÿ“„ DEFAULT '๐Ÿ“„';
INSERT INTO ๐Ÿ“• VALUES
(4, 'Example Book', '๐Ÿ“–', 1, '๐Ÿ“„๐Ÿ“„๐Ÿ“„');
-- select the most common genre
SELECT MODE() WITHIN GROUP (ORDER BY ๐Ÿ”–) AS MostCommonGenre FROM ๐Ÿ“•;
-- select books longer than 2 unit length
SELECT * FROM ๐Ÿ“• WHERE ๐Ÿ“•.๐Ÿ”ข๐Ÿ“„ > '๐Ÿ“„๐Ÿ“„';
CREATE TYPE โญ AS ENUM ('๐Ÿคข', 'โ˜น', '๐Ÿ˜', '๐Ÿ™‚', '๐Ÿ˜');
ALTER TABLE ๐Ÿ‘ค๐Ÿ ๐Ÿ“•
ADD COLUMN โญ โญ;
INSERT INTO ๐Ÿ‘ค๐Ÿ ๐Ÿ“• VALUES
(1, 4, '๐Ÿ˜'), -- Jeff took home 'Example Book' and rated it ๐Ÿ˜
(2, 3, '๐Ÿ™‚'), -- Annie took home 'Through the Looking-Glass' and rated it ๐Ÿ™‚
(2, 4, '๐Ÿ˜'), -- Annie took home 'Example Book' and rated it ๐Ÿ˜
(2, 4, '๐Ÿคข'); -- Annie took home 'Example Book' (yes again) and rated it ๐Ÿคข
-- select the ratings for 'Example Book'
SELECT ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.โญ
FROM ๐Ÿ‘ค๐Ÿ ๐Ÿ“• JOIN ๐Ÿ“• ON ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.๐Ÿ“• = ๐Ÿ“•.๐Ÿ”‘
WHERE ๐Ÿ“•.๐Ÿ’ฌ = 'Example Book';
-- select the most common rating for each book
SELECT ๐Ÿ“•.๐Ÿ’ฌ, MODE() WITHIN GROUP (ORDER BY ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.โญ) AS MostCommonRating
FROM ๐Ÿ‘ค๐Ÿ ๐Ÿ“• JOIN ๐Ÿ“• ON ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.๐Ÿ“• = ๐Ÿ“•.๐Ÿ”‘
GROUP BY ๐Ÿ“•.๐Ÿ”‘;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment