REVIEWER [MYSQL]
SELECT FIND RECORDS WHERE THE NAME IS EXACTLY 4
is used to retrieve rows selected from one or more CHARACTERS LONG AND STARTS WITH 'A':
tables.
SELECT DISTINCT ‘column_name’ SELECT * FROM STUDENTS WHERE NAME LIKE 'A___';
FROM table_name;
EXPLANATION: THE THREE UNDERSCORES (___)
REPRESENT EXACTLY THREE CHARACTERS
1.1 SELECT WITH DISTINCT AFTER A, MEANING THE RESULT WILL INCLUDE NAMES
DISTINCT WORKS ACROSS ALL COLUMNS TO LIKE "ALEX", "ALAN", ETC.
DELIVER THE RESULTS, NOT INDIVIDUAL COLUMNS.
THE LATTER IS OFTEN A MISCONCEPTION OF NEW
SQL DEVELOPERS. IN SHORT, IT IS THE
1.5 SELECT WITH CASE
DISTINCTNESS AT THE ROW-LEVEL OF THE RESULT THE SELECT STATEMENT WITH THE CASE OR IF
SET THAT MATTERS, NOT DISTINCTNESS AT THE EXPRESSION IN SQL IS USED TO INTRODUCE
COLUMN-LEVEL. CONDITIONAL LOGIC WITHIN QUERIES. BOTH
QUERY: ALLOW YOU TO RETURN DIFFERENT VALUES BASED
SELECT DISTINCT column_name FROM ON SPECIFIED CONDITIONS, SIMILAR TO IF-ELSE
stack; LOGIC IN PROGRAMMING LANGUAGES.
THE CASE EXPRESSION IN SQL EVALUATES A LIST
1.2 SELECT WITH ALL COLUMNS (*) OF CONDITIONS AND RETURNS ONE OF SEVERAL
POSSIBLE RESULTS BASED ON WHICH CONDITION
QUERY: IS TRUE. IT’S OFTEN USED TO TRANSFORM DATA IN
A QUERY OR TO RETURN DIFFERENT VALUES
SELECT * FROM table_name; BASED ON CERTAIN CONDITIONS.
SELECT column_name FROM table_name;
CASE: WHEN YOU NEED MULTIPLE CONDITIONAL
PROS CHECKS OR MORE COMPLEX LOGIC, SUCH AS
1. WHEN YOU ADD/REMOVE COLUMNS, YOU DON'T ASSIGNING LETTER GRADES BASED ON NUMERICAL
HAVE TO MAKE CHANGES WHERE YOU DID USE SCORES.
SELECT *
2. IT'S SHORTER TO WRITE
3. YOU ALSO SEE THE ANSWERS, SO CAN SELECT *-
USAGE EVER BE JUSTIFIED?
CONS:
1. YOU ARE RETURNING MORE DATA THAN YOU
NEED. SAY YOU ADD A
VARBINARY COLUMN THAT CONTAINS 200K PER
ROW.
YOU ONLY NEED THIS DATA IN ONE PLACE FOR A
SINGLE RECORD - USING
SELECT * YOU CAN END UP RETURNING 2MB PER 10
ROWS THAT YOU DON'T NEED
2. EXPLICIT ABOUT WHAT DATA IS USED
3. SPECIFYING COLUMNS MEANS YOU GET AN
ERROR WHEN A COLUMN IS REMOVED
4. THE QUERY PROCESSOR HAS TO DO SOME MORE
WORK - FIGURING OUT WHAT COLUMNS EXIST ON
THE TABLE
1.3 SELECT BY COLUMN NAME
QUERY:
SELECT column_name FROM table_name;
1.4 SELECT WITH LIKE (%)
QUERY:
SELECT * FROM table_name WHERE
1.6 SELECT WITH A LIMIT CLAUSE
column_name LIKE pattern; THE SELECT STATEMENT WITH THE LIMIT CLAUSE IN
SQL IS USED TO RESTRICT THE NUMBER OF ROWS
THE SELECT STATEMENT WITH THE LIKE CLAUSE IS RETURNED BY A QUERY. THIS IS ESPECIALLY
USED IN SQL TO RETRIEVE DATA FROM A TABLE USEFUL WHEN WORKING WITH LARGE DATASETS,
WHERE A SPECIFIC COLUMN MATCHES A GIVEN AS IT ALLOWS YOU TO RETRIEVE ONLY A SUBSET
PATTERN. THE LIKE CLAUSE ALLOWS YOU TO OF THE DATA.
SEARCH FOR A SPECIFIED PATTERN IN A COLUMN, QUERY:
TYPICALLY USED WITH WILDCARD CHARACTERS TO
FIND SIMILAR OR PARTIAL MATCHES. SELECT column1, column2, ... FROM table_name
WILDCARD CHARACTERS USED WITH LIKE: LIMIT number_of_rows;
1. % (PERCENT SIGN): REPRESENTS ZERO, ONE, OR
MULTIPLE CHARACTERS. LIMIT: Specifies the maximum number of rows to be
2. _ (UNDERSCORE): REPRESENTS A SINGLE returned.
CHARACTER. number_of_rows: The number of rows you want to
retrieve.
EXAMPLE:
FIND RECORDS WHERE THE NAME STARTS WITH 'J':
SELECT * FROM STUDENTS WHERE NAME LIKE 'J%';
EXPLANATION: THE % AFTER THE LETTER J MEANS
THAT ANY NUMBER OF CHARACTERS CAN FOLLOW J.
THIS WILL RETURN RESULTS WHERE THE NAME
STARTS WITH "J", SUCH AS "JOHN", "JENNY", "JACOB",
ETC.
FIND RECORDS WHERE THE NAME ENDS WITH 'N':
SELECT * FROM STUDENTS WHERE NAME LIKE '%N';
EXPLANATION: THE % BEFORE THE LETTER N MEANS
ANY NUMBER OF CHARACTERS CAN
COME BEFORE N. THIS WILL RETURN NAMES LIKE
"JOHN", "MEGAN", OR "ETHAN".
