[SQL]: Refining Selections

SQL

09/25/2019

Prepare data

Insert more data from file

Insert the data on top of students.sql file

students_2.sql

SQL

INSERT INTO students
(fname, lname, major, age, credit)
VALUES ('Dan', 'Joyce', 'no_major', 27, 1000),
    ('Mary', 'Fletcher','Japanese 10%', 16, 25),
    ('Lena', 'Shah', 'Food Science', 31, 57)
    ('Ellis', 'Min', 'Economics', 18, 5555);

Output:

TEXT

mysql> SELECT * FROM students;
+-------------+----------+----------+------------------+------+--------+
| students_id | fname    | lname    | major            | age  | credit |
+-------------+----------+----------+------------------+------+--------+
|           1 | Ellis    | Min      | Computer Science |   18 |     13 |
|           2 | Tenzin   | Pema     | Biology          |   22 |    317 |
|           3 | Sam      | Smith    | Computer Science |   25 |   1211 |
|           4 | John     | Johnson  | Linguistics      |   19 |     21 |
|           5 | Dave     | Smith    | Chemistry        |   23 |     54 |
|           6 | Brent    | Lang     | Accounting       |   22 |     11 |
|           7 | Jerimiah | Tucker   | Business         |   20 |     66 |
|           8 | Lamont   | Marks    | Biochemistry     |   18 |     37 |
|           9 | Diego    | Miller   | Physics          |   17 |      6 |
|          10 | Angelina | Randall  | Art              |   26 |     41 |
|          11 | Brad     | Johnson  | Biology          |   22 |     13 |
|          12 | Dan      | Joyce    | no_major         |   27 |   1000 |
|          13 | Mary     | Fletcher | Japanese 10%     |   16 |     25 |
|          14 | Lena     | Shah     | Food Science     |   31 |     57 |
|          15 | Ellis    | Min      | Economics        |   18 |   5555 |
+-------------+----------+----------+------------------+------+--------+
15 rows in set (0.01 sec)

DISTINCT

Returns a distinct or unique data without duplicates

SQL

SELECT DISTINCT major
FROM students;

Output:

TEXT

+------------------+
| major            |
+------------------+
| Computer Science |
| Biology          |
| Linguistics      |
| Chemistry        |
| Accounting       |
| Business         |
| Biochemistry     |
| Physics          |
| Art              |
| no_major         |
| Japanese 10%     |
| Food Science     |
+------------------+
12 rows in set (0.00 sec)

Multiple majors such as Computer science and Biology are omitted

TODO

Try displaying all columns that have non distinct majors

DISTINCT for fullname

SQL

SELECT DISTINCT CONCAT(fname,' ',lname)
FROM students;

Output:

TEXT

+-------------------------+
| CONCAT(fname,' ',lname) |
+-------------------------+
| Ellis Min               |
| Tenzin Pema             |
| Sam Smith               |
| John Johnson            |
| Dave Smith              |
| Brent Lang              |
| Jerimiah Tucker         |
| Lamont Marks            |
| Diego Miller            |
| Angelina Randall        |
| Brad Johnson            |
| Dan Joyce               |
| Mary Fletcher           |
| Lena Shah               |
+-------------------------+
14 rows in set (0.00 sec)

SQL

SELECT DISTINCT fname, lname
FROM students;

Output:

TEXT

+----------+----------+
| fname    | lname    |
+----------+----------+
| Ellis    | Min      |
| Tenzin   | Pema     |
| Sam      | Smith    |
| John     | Johnson  |
| Dave     | Smith    |
| Brent    | Lang     |
| Jerimiah | Tucker   |
| Lamont   | Marks    |
| Diego    | Miller   |
| Angelina | Randall  |
| Brad     | Johnson  |
| Dan      | Joyce    |
| Mary     | Fletcher |
| Lena     | Shah     |
+----------+----------+
14 rows in set (0.00 sec)

ORDER BY

Useful to sort the result

SQL

SELECT lname FROM students
ORDER BY lname;

Query results are ordered alphabetically
Ascending by default
- Can be used explicitly with ORDER BY columnName ASC

SQL

SELECT DISTINCT lname FROM students
ORDER BY lname;

Select unique last names and sort alphabetically
Output:

TEXT

+----------+
| lname    |
+----------+
| Fletcher |
| Johnson  |
| Joyce    |
| Lang     |
| Marks    |
| Miller   |
| Min      |
| Pema     |
| Randall  |
| Shah     |
| Smith    |
| Tucker   |
+----------+
12 rows in set (0.00 sec)

SQL

SELECT fname, age FROM students
ORDER BY age;

Show first names in order of age

ORDER BY ~ DESC

SQL

SELECT age FROM students
ORDER BY age DESC;

Show age in descending order
Note: DESC here is for descending. DESC can also be used as alias for DESCRIBE

ORDER BY 2

SQL

SELECT CONCAT(fname, ' ', lname) AS fullname, age
FROM students
ORDER BY 2;

ORDER BY 2nd argument

ORDER BY multiple columns

SQL

SELECT fname, lname FROM students
ORDER BY lname, fname;

Sort by last name first then sort by first name

LIMIT

Shows specified number of results

SQL

SELECT fname FROM students
LIMIT 5;

Shows first 5 limits regardless sorted or not

LIMIT & ORDER BY

SQL

SELECT DISTINCT age FROM students
ORDER BY age DESC LIMIT 5;

Display 5 unique ages in descending order

LIMIT 2, 5 (not so common)

SQL

SELECT fname, lname FROM students
LIMIT 2, 3;

Display 3 rows starting from *3rd row
- 1st arguments start from index 0!

SQL

SELECT major FROM students
LIMIT 10, 1;

Selects 11th book
- i.e. selecting 1 row starting from 11th row

SQL

SELECT major FROM students
LIMIT 3, 18446744073709551615;

Starting from 4th row show data till the end of table

SQL

SELECT major FROM students
LIMIT 3, 12;

This will do the same since we have 15(3+12) rows total

LIKE (wildcard %)

Useful for better searching

SQL

SELECT fname, lname FROM students
WHERE lname LIKE '%mi%';

Displays first and last name of students that have or contains "mi" in their last name
- Case insensitive
% is a wild card
Output:

TEXT

+-------+--------+
| fname | lname  |
+-------+--------+
| Ellis | Min    |
| Sam   | Smith  |
| Dave  | Smith  |
| Diego | Miller |
| Ellis | Min    |
+-------+--------+

SQL

SELECT lname FROM students
WHERE lname LIKE 'mi%';

Similarly, it displays last name of students whose last name starts with "mi"
Output:

TEXT

+--------+
| lname  |
+--------+
| Min    |
| Miller |
| Min    |
+--------+

SQL

SELECT CONCAT(fname, ' ', lname) AS fullname, major FROM students
WHERE major LIKE "%science%";

Shows fullname of students whose major contains word "science"
Output:

TEXT

+-----------+------------------+
| fullname  | major            |
+-----------+------------------+
| Ellis Min | Computer Science |
| Sam Smith | Computer Science |
| Lena Shah | Food Science     |
+-----------+------------------+

LIKE (whilecard ___ )

SQL

SELECT lname FROM students
WHERE lname LIKE '____'; -- Four underscores

Shows last names that are 4 character length
Output:

TEXT

+-------+
| lname |
+-------+
| Pema  |
| Lang  |
| Shah  |
+-------+

SQL

SELECT fname FROM students
WHERE fname LIKE '_a__';

Output:

TEXT

+-------+
| fname |
+-------+
| Dave  |
| Mary  |
+-------+

SQL

SELECT fname, lname, age FROM students
WHERE age LIKE '1_';

Display teenagers

Searching majors that have ' % ' or ' _ ' in them

SQL

SELECT major FROM students
WHERE major LIKE "%\%%";

Escape it with back slash

SQL

SELECT major FROM students
WHERE major LIKE "%\_%";

Similar for underscore

WRITTEN BY

Ellis Min

Keeping a record

GitHub