MySQL - Logical Operator 중간문제

Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

문제는 194강에

-- Q1 Evaluate the following...
SELECT 10 != 10; -- 0(false)
SELECT 15 > 14 && 99 -5 <= 94; -- 1(true)
SELECT 1 IN(5,3) || 9 BETWEEN 8 AND 10; -- 1(true)

-- Q2 Select All Books Written Before 1980 (non inclusive)
SELECT *
FROM books
WHERE released_year < 1980;

-- Q3 Select All Books Written By Eggers Or Chabon
SELECT *
FROM books
WHERE author_lname IN ('Eggers','Chabon');

SELECT *
FROM books
WHERE author_lname = 'Eggers' OR author_lname = 'Chabon';

SELECT * 
FROM books
WHERE author_lname = 'Eggers' || author_lname = 'Chabon';

-- Q4 Select All books Written By Lahiri, Published after 2000
SELECT *
FROM books
WHERE author_lname = 'Lahiri' 
	AND released_year >= 2000;
    
-- Q5 Select All books with page counts between 100 and 200
SELECT * 
FROM books
WHERE pages BETWEEN 100 AND 200;

SELECT * 
FROM books
WHERE pages >= 100 AND pages <= 200;

-- Q6 Select all books where author_lname starts with a 'C' or an 'S'
SELECT *
FROM books
WHERE author_lname LIKE 'C%' 
	OR author_lname LIKE 'S%';
    
SELECT * 
FROM books
WHERE SUBSTRING(author_lname,1,1) = 'C'
	OR SUBSTRING(author_lname,1,1) = 'S';
    
SELECT * 
FROM books 
WHERE SUBSTRING(author_lname,1,1) IN ('C','S');
    
-- Q7
SELECT title, author_lname,
		CASE
			WHEN title LIKE '%stories%' THEN 'Short Stories'
            WHEN title LIKE '%Just Kids%' OR title LIKE '%A Heartbreaking Work%' THEN 'Memoir'
            ELSE 'Novel'
            END AS 'TYPE'
FROM books;

-- Bonus Question
SELECT author_lname, 
	CASE
		WHEN COUNT(*) > 1 THEN CONCAT(COUNT(*), ' books')
        ELSE CONCAT(COUNT(*), ' book')
	END AS 'COUNT'
FROM books
GROUP BY author_lname, author_fname;