add string functions

xxhh1112 · May 23, 2019 · 5457746 · 5457746
1 parent 4fbc37a
commit 5457746
Show file tree

Hide file tree

Showing 2 changed files with 155 additions and 0 deletions.
diff --git a/sql/udemy-mysql-bootcamp/05-crud-challenges.sql b/sql/udemy-mysql-bootcamp/05-crud-challenges.sql
@@ -0,0 +1 @@
+/* todo */
diff --git a/sql/udemy-mysql-bootcamp/06-string-functions.sql b/sql/udemy-mysql-bootcamp/06-string-functions.sql
@@ -0,0 +1,154 @@
+/* running SQL files */
+CREATE TABLE cats
+    (
+        cat_id INT NOT NULL AUTO_INCREMENT,
+        name VARCHAR(100),
+        age INT,
+        PRIMARY KEY(cat_id)
+    );
+
+mysql-ctl cli
+
+use cat_app;
+
+source first_file.sql
+
+DESC cats;
+
+
+
+INSERT INTO cats(name, age)
+VALUES('Charlie', 17);
+
+INSERT INTO cats(name, age)
+VALUES('Connie', 10);
+
+SELECT * FROM cats;
+
+source testing/insert.sql
+
+/* Load in Book data */
+CREATE TABLE books 
+    (
+        book_id INT NOT NULL AUTO_INCREMENT,
+        title VARCHAR(100),
+        author_fname VARCHAR(100),
+        author_lname VARCHAR(100),
+        released_year INT,
+        stock_quantity INT,
+        pages INT,
+        PRIMARY KEY(book_id)
+    );
+
+INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
+VALUES
+('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
+('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
+('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
+('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
+('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
+('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
+('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
+('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
+('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
+('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
+('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
+("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
+('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
+('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
+('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
+('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);
+
+
+SELECT database();
+
+CREATE DATABASE book_shop;
+
+use book_shop;
+
+show tables;
+
+source book_data.sql
+
+DESC books;
+
+SELECT * FROM books;
+
+/* CONCAT */
+SELECT
+  CONCAT(author_fname, ' ', author_lname)
+  AS 'full name'
+FROM books;
+
+SELECT author_fname AS first, author_lname AS last, 
+  CONCAT(author_fname, ' ', author_lname) AS full
+FROM books;
+
+SELECT 
+    CONCAT_WS(' - ', title, author_fname, author_lname) 
+FROM books;
+
+/* SUBSTRING */
+SELECT SUBSTRING(title, 1, 10) AS 'short title' FROM books;
+
+SELECT SUBSTR(title, 1, 10) AS 'short title' FROM books;
+
+SELECT CONCAT
+    (
+        SUBSTRING(title, 1, 10),
+        '...'
+    ) AS 'short title'
+FROM books;
+
+/* REPLACE */
+SELECT REPLACE('Hello World', 'Hell', '%$#@');
+
+SELECT REPLACE(title, 'e ', '3') FROM books;
+
+/* REVERSE */
+SELECT REVERSE(author_fname) FROM books;
+
+/* CHAR_LENGTH */
+SELECT author_lname, CHAR_LENGTH(author_lname) AS 'length' FROM books;
+
+SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long') FROM books;
+
+/* UPPER and LOWER */
+SELECT UPPER(title) FROM books;
+
+SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;
+
+SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books;
+
+
+/* Challenge solutions */
+
+
+SELECT 
+   author_lname AS forwards,
+   REVERSE(author_lname) AS backwards 
+FROM books;
+
+
+SELECT
+   UPPER
+   (
+      CONCAT(author_fname, ' ', author_lname)
+   ) AS 'full name in caps'
+FROM books;
+
+
+SELECT
+   CONCAT(title, ' was released in ', released_year) AS blurb
+FROM books;
+SELECT
+   title,
+   CHAR_LENGTH(title) AS 'character count'
+FROM books;
+
+
+SELECT
+   CONCAT(SUBSTRING(title, 1, 10), '...') AS 'short title',
+   CONCAT(author_lname, ',', author_fname) AS author,
+   CONCAT(stock_quantity, ' in stock') AS quantity
+FROM books;