Leveling up your mind

Logic: And, Like, O...
 
Notifications
Clear all

Logic: And, Like, Or, Between, In and Not In, Case Statement  

 
Sohaib
(@sohaib-ops)
Member Admin

 

AND: 

  1. SELECT title, author_lname, released_year FROM books
  2. WHERE author_lname='Eggers';
  3.  
  4. SELECT title, author_lname, released_year FROM books
  5. WHERE released_year > 2010;
  6.  
  7. SELECT
  8. title,
  9. author_lname,
  10. released_year FROM books
  11. WHERE author_lname='Eggers'
  12. AND released_year > 2010;
  13.  
  14. SELECT 1 < 5 && 7 = 9;
  15. -- false
  16.  
  17. SELECT -10 > -20 && 0 <= 0;
  18. -- true
  19.  
  20. SELECT -40 <= 0 AND 10 > 40;
  21. --false
  22.  
  23. SELECT 54 <= 54 && 'a' = 'A';
  24. -- true
  25.  
  26. SELECT *
  27. FROM books
  28. WHERE author_lname='Eggers'
  29. AND released_year > 2010
  30. AND title LIKE '%novel%';
This topic was modified 1 month ago 3 times by Sohaib
Quote
Posted : 03/07/2020 7:58 pm
Sohaib
(@sohaib-ops)
Member Admin

Or:

 

 

  1. SELECT
  2. title,
  3. author_lname,
  4. released_year
  5. FROM books
  6. WHERE author_lname='Eggers' || released_year > 2010;
  7.  
  8.  
  9. SELECT 40 <= 100 || -2 > 0;
  10. -- true
  11.  
  12. SELECT 10 > 5 || 5 = 5;
  13. -- true
  14.  
  15. SELECT 'a' = 5 || 3000 > 2000;
  16. -- true
  17.  
  18. SELECT title,
  19. author_lname,
  20. released_year,
  21. stock_quantity
  22. FROM books
  23. WHERE author_lname = 'Eggers'
  24. || released_year > 2010
  25. OR stock_quantity > 100;
ReplyQuote
Posted : 03/07/2020 8:10 pm
Sohaib
(@sohaib-ops)
Member Admin

Between:

 

  1. SELECT title, released_year FROM books WHERE released_year >= 2004 && released_year <= 2015;
  2.  
  3. SELECT title, released_year FROM books
  4. WHERE released_year BETWEEN 2004 AND 2015;
  5.  
  6. SELECT title, released_year FROM books
  7. WHERE released_year NOT BETWEEN 2004 AND 2015;
  8.  
  9. SELECT CAST('2017-05-02' AS DATETIME);
  10.  
  11. show databases;
  12.  
  13. use new_testing_db;
  14.  
  15. SELECT name, birthdt FROM people WHERE birthdt BETWEEN '1980-01-01' AND '2000-01-01';
  16.  
  17. SELECT
  18. name,
  19. birthdt
  20. FROM people
  21. WHERE
  22. birthdt BETWEEN CAST('1980-01-01' AS DATETIME)
  23. AND CAST('2000-01-01' AS DATETIME);
ReplyQuote
Posted : 04/07/2020 12:14 am
Sohaib
(@sohaib-ops)
Member Admin

In and Not In:

 

 

  1. show databases();
  2. use book_shop;
  3.  
  4. SELECT
  5. title,
  6. author_lname
  7. FROM books
  8. WHERE author_lname='Carver' OR
  9. author_lname='Lahiri' OR
  10. author_lname='Smith';
  11.  
  12. SELECT title, author_lname FROM books
  13. WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');
  14.  
  15. SELECT title, released_year FROM books
  16. WHERE released_year IN (2017, 1985);
  17.  
  18. SELECT title, released_year FROM books
  19. WHERE released_year != 2000 AND
  20. released_year != 2002 AND
  21. released_year != 2004 AND
  22. released_year != 2006 AND
  23. released_year != 2008 AND
  24. released_year != 2010 AND
  25. released_year != 2012 AND
  26. released_year != 2014 AND
  27. released_year != 2016;
  28.  
  29. SELECT title, released_year FROM books
  30. WHERE released_year NOT IN
  31. (2000,2002,2004,2006,2008,2010,2012,2014,2016);
  32.  
  33. SELECT title, released_year FROM books
  34. WHERE released_year >= 2000
  35. AND released_year NOT IN
  36. (2000,2002,2004,2006,2008,2010,2012,2014,2016);
  37.  
  38. SELECT title, released_year FROM books
  39. WHERE released_year >= 2000 AND
  40. released_year % 2 != 0;
  41.  
  42. SELECT title, released_year FROM books
  43. WHERE released_year >= 2000 AND
  44. released_year % 2 != 0 ORDER BY released_year;
ReplyQuote
Posted : 04/07/2020 12:34 am
Sohaib
(@sohaib-ops)
Member Admin

Case Statements:

 

 

  1. SELECT title, released_year,
  2. CASE
  3. WHEN released_year >= 2000 THEN 'Modern Lit'
  4. ELSE '20th Century Lit'
  5. END AS GENRE
  6. FROM books;
  7.  
  8. SELECT title, stock_quantity,
  9. CASE
  10. WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
  11. WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
  12. ELSE '***'
  13. END AS STOCK
  14. FROM books;
  15.  
  16. SELECT title,
  17. CASE
  18. WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
  19. WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
  20. ELSE '***'
  21. END AS STOCK
  22. FROM books;
  23.  
  24. SELECT title, stock_quantity,
  25. CASE
  26. WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
  27. WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
  28. WHEN stock_quantity BETWEEN 101 AND 150 THEN '***'
  29. ELSE '****'
  30. END AS STOCK
  31. FROM books;
  32.  
  33. SELECT title, stock_quantity,
  34. CASE
  35. WHEN stock_quantity <= 50 THEN '*'
  36. WHEN stock_quantity <= 100 THEN '**'
  37. ELSE '***'
  38. END AS STOCK
  39. FROM books;
ReplyQuote
Posted : 04/07/2020 12:52 am