MySQL practical Tutorials part 11- MySQL IN, CASE statement and some practical exercises on logical operators.
=========================================================================
In And Not In
show databases();
use book_shop;
SELECT
title,
author_lname
FROM books
WHERE author_lname='Carver' OR
author_lname='Lahiri' OR
author_lname='Smith';
SELECT title, author_lname FROM books
WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');
SELECT title, released_year FROM books
WHERE released_year IN (2017, 1985);
SELECT title, released_year FROM books
WHERE released_year != 2000 AND
released_year != 2002 AND
released_year != 2004 AND
released_year != 2006 AND
released_year != 2008 AND
released_year != 2010 AND
released_year != 2012 AND
released_year != 2014 AND
released_year != 2016;
SELECT title, released_year FROM books
WHERE released_year NOT IN
(2000,2002,2004,2006,2008,2010,2012,2014,2016);
SELECT title, released_year FROM books
WHERE released_year >= 2000
AND released_year NOT IN
(2000,2002,2004,2006,2008,2010,2012,2014,2016);
SELECT title, released_year FROM books
WHERE released_year >= 2000 AND
released_year % 2 != 0;
SELECT title, released_year FROM books
WHERE released_year >= 2000 AND
released_year % 2 != 0 ORDER BY released_year;
=======================================================================================================================
Case Statements
SELECT title, released_year,
CASE
WHEN released_year >= 2000 THEN 'Modern Lit'
ELSE '20th Century Lit'
END AS GENRE
FROM books;
SELECT title, stock_quantity,
CASE
WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
ELSE '***'
END AS STOCK
FROM books;
SELECT title,
CASE
WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
ELSE '***'
END AS STOCK
FROM books;
SELECT title, stock_quantity,
CASE
WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
WHEN stock_quantity BETWEEN 101 AND 150 THEN '***'
ELSE '****'
END AS STOCK
FROM books;
SELECT title, stock_quantity,
CASE
WHEN stock_quantity <= 50 THEN '*'
WHEN stock_quantity <= 100 THEN '**'
ELSE '***'
END AS STOCK
FROM books;
=======================================================================================================================
Logical Operators Exercises Solution
SELECT 10 != 10;
-- false
SELECT 15 > 14 && 99 - 5 <= 94;
-- true
SELECT 1 IN (5,3) || 9 BETWEEN 8 AND 10;
-- true
SELECT title, released_year FROM books WHERE released_year < 1980;
SELECT title, author_lname FROM books WHERE author_lname='Eggers' OR author_lname='Chabon';
SELECT title, author_lname FROM books WHERE author_lname IN ('Eggers','Chabon');
SELECT title, author_lname, released_year FROM books WHERE author_lname = 'Lahiri' && released_year > 2000;
SELECT title, pages FROM books WHERE pages >= 100 && pages <=200;
SELECT title, pages FROM books WHERE pages BETWEEN 100 AND 200;
SELECT
title,
author_lname
FROM books
WHERE
author_lname LIKE 'C%' OR
author_lname LIKE 'S%';
SELECT
title,
author_lname
FROM books
WHERE
SUBSTR(author_lname,1,1) = 'C' OR
SUBSTR(author_lname,1,1) = 'S';
SELECT title, author_lname FROM books
WHERE SUBSTR(author_lname,1,1) IN ('C', 'S');
SELECT
title,
author_lname,
CASE
WHEN title LIKE '%stories%' THEN 'Short Stories'
WHEN title = 'Just Kids' OR title = 'A Heartbreaking Work of Staggering Genius' THEN 'Memoir'
ELSE 'Novel'
END AS TYPE
FROM books;
SELECT author_fname, author_lname,
CASE
WHEN COUNT(*) = 1 THEN '1 book'
ELSE CONCAT(COUNT(*), ' books')
END AS COUNT
FROM books
GROUP BY author_lname, author_fname;
=======================================================================================================================
Comments
Post a Comment