SQL: Finding rows containing phrases regardless of case

Question

Here is the database I'm using: https://drive.google.com/file/d/1ArJekOQpal0JFIr1h3NXYcFVngnCNUxg/view?usp=sharing

List the deptnum and total number of academics for CS departments, in alphabetical order of deptname. CS departments are departments whose deptname contains the phrase "Computer ... Science" or “Computing … Science” in upper case or lower case letters. You must use the NATURAL JOIN operator.

My attempt at this question:

SELECT deptnum
FROM academic NATURAL JOIN department
WHERE UPPER(deptname) LIKE ('%computer science%') OR UPPER(deptname) LIKE 
('%computing science%')
ORDER BY deptnum;

It keeps coming up with no values, I'm not sure what I'm doing wrong.

Thank you

EDIT:

Thanks everyone. This is what I'm using now (although inefficient):

SELECT distinct deptnum
FROM academic NATURAL JOIN department
WHERE UPPER(deptname) LIKE ('%COMPUTER%') AND UPPER(deptname) LIKE 
('%SCIENCE%')OR UPPER(deptname) LIKE ('%COMPUTING%') AND UPPER(deptname) 
LIKE ('%SCIENCE%')
ORDER BY deptnum;

`UPPER(deptname) LIKE ('%COMPUTER%') OR UPPER(deptname) LIKE ('%COMPUTING SCIENCE%')` since you want to match against UPPER CASE — RoMEoMusTDiE, May 15 '18 at 02:13
Your database (google drive link) need access permission, share it before post link to it — Pham X. Bach, May 15 '18 at 02:15
I updated my original post with a shareable link: https://drive.google.com/file/d/1ArJekOQpal0JFIr1h3NXYcFVngnCNUxg/view?usp=sharing — user24529, May 15 '18 at 02:28
@maSTAShuFu Thanks that worked, however I'm missing a few queries. It doesn't seem to output deptnames such as "School of Computer and Information Science", even though it contains "computer science" — user24529, May 15 '18 at 02:30
nvm, I found a way to output it, although it seems a bit long and inefficient: SELECT distinct deptnum FROM academic NATURAL JOIN department WHERE UPPER(deptname) LIKE ('%COMPUTER%') AND UPPER(deptname) LIKE ('%SCIENCE%')OR UPPER(deptname) LIKE ('%COMPUTING%') AND UPPER(deptname) LIKE ('%SCIENCE%') ORDER BY deptnum; — user24529, May 15 '18 at 02:32
Check your database collations i guess they are set as case sensitive, Have a look at [Case Sensitive collation in MySQL](https://stackoverflow.com/questions/4558707/case-sensitive-collation-in-mysql) and [10.2 Character Sets and Collations in MySQL](https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html) — M Khalid Junaid, May 15 '18 at 05:57

score 0 · Answer 1 · answered May 15 '18 at 02:13

The LIKE operator in MySQL is already case insensitive, so the following should work:

SELECT deptnum
FROM academic
NATURAL JOIN department
WHERE deptname LIKE '%computer%' OR deptname LIKE '%computing science%'
ORDER BY deptnum;

We can tidy this up a bit by using REGEXP, which is also case insensitive:

SELECT deptnum
FROM academic
NATURAL JOIN department
WHERE deptname REGEXP 'computer|computing science'
ORDER BY deptnum;

SQL: Finding rows containing phrases regardless of case

1 Answers1