Operators and Statements

Question 0

_

Underscore

Answer 0

Wildcard in LIKE statements, good for matching all characters but one.

SELECT * FROM FRIENDS WHERE last_name LIKE ‘_T’;

Question 1

%

Answer 1

Modulo - In math calculations it gives remainder of a division problem.

Serves as wildcard in LIKE statements: SELECT * FROM —- WHERE —- LIKE ‘%_%’;

Question 2

|| / CONCAT

Answer 2

Double Pipe: Concatenates two strings.

SELECT LASTNAME || ‘,’ || FIRSTNAME NAME FROM EMPLOYEES;

Question 3

If employees accrue 12 days/yr, find all employees whose names start with B and who have more than 50 days of leave.

Lastname Years Leavetaken

Answer 3

SELECT LASTNAME, YEARS * 12 - LEAVETAKEN REMAINING
FROM VACATION
WHERE LASTNAME LIKE ‘B%’
AND YEARS * 12 - LEAVETAKEN > 50;

Question 4

Logical Operators

Answer 4

AND, OR, NOT

Question 5

UNION / UNION ALL

Answer 5

UNION: returns results of two queries and removes duplicate rows.UNION ALL: doesn’t remove duplicates.
SELECT * FROM –
UNION
SELECT * FROM –;

Question 6

INTERSECT

Answer 6

Returns rows found in both queries.

SELECT * FROM –
INTERSECT
SELECT * FROM –;

Question 7

MINUS

Answer 7

Returns rows from first query that were not present in second:

SELECT * FROM –
MINUS
SELECT * FROM –;

Question 8

IN

Answer 8

Two ways:

SELECT * FROM – WHERE STATE = ‘TX’ OR STATE = ‘CA’;

SELECT * FROM – WHERE STATE IN (‘TX’, ‘CA’);

Question 9

BETWEEN

Answer 9

Provides range. Two ways:

SELECT * FROM – WHERE PRICE > 10 AND PRICE < 20;

SELECT * FROM – WHERE PRICE BETWEEN 10 AND 20;
(Inclusive)

Question 10

COUNT

Answer 10

Returns number of rows that satisfy condition in WHERE clause.

SELECT COUNT(*) alias FROM – WHERE –;

Question 11

SUM

Answer 11

Returns sum of all values in a column. Only works on numbers.

SELECT SUM(COST) TOTAL FROM GROCERYLIST;

Question 12

AVG

Answer 12

Average to take average of two columns:

SELECT AVG(HITS) / AVG(AB) TEAM_AVERAGE FROM TEAMSTATS;

Question 13

MAX / MIN

Answer 13

MAX: Will return highest number in a column or word closest to Z.

MIN: opposite

Question 14

ABS

Answer 14

Returns absolute value of a number.

Question 15

CEIL / FLOOR

Answer 15

CEIL returns the smallest integer greater than or equal to its argument. 5.3 -> 6

FLOOR does opposite. 5.3 -> 5

Question 16

INITCAP / LOWER / UPPER

Answer 16

INITCAP: Capitalizes first letter of a word, lowercases the rest.

LOWER/UPPER: Lowercases/uppercases all letters.

Question 17

LPAD / RPAD

Answer 17

Left or right padding.

SELECT LASTNAME LPAD (LASTNAME, 1, ‘• ‘) FROM –;

Returns -> • Felts

Question 18

LTRIM / RTRIM

Answer 18

Trims characters or blanks off a word.

SELECT LASTNAME, RTRIM (LASTNAME) FROM TABLE;

Then concatenate w ‘,’ Firstname

Question 19

REPLACE

Answer 19

Arguments: (string to search, search key, replacement string)

If replacement argument is null, will remove each occurrence of the search string.

SELECT LASTNAME, REPLACE(LASTNAME, ‘F’, ‘E’) REPLACEMENT FROM TABLE;

Question 20

SUBSTR

Answer 20

Allows you to take out a piece of the target string.

2-3 arguments: (target string, position of first character to be output, # of characters to show)

SELECT LASTNAME, SUBSTR (LASTNAME, 1, 3) FROM TABLE;

Shows first three letters of last name.

Negative second argument counts from right end of data.

Question 21

LENGTH

Answer 21

Returns length of data input.

SELECT LASTNAME, LENGTH(RTRIM(LASTNAME)) FROM TABLE;

Question 22

STARTING WITH

Answer 22

Clause just like: LIKE (‘A%’);

SELECT NAME FROM TABLE
WHERE NAME STARTING WITH (‘A’)
OR AGE LIKE(‘3_’);

Question 23

ORDER BY

Answer 23

Sorts column in ascending order. Use DESC at end of statement to sort data in descending order.

SELECT NAME, AGE FROM TABLE
ORDER BY NAME ASC, AGE DESC;

Question 24

GROUP BY

Answer 24

Allows you to group data, such as grouping payroll information by employee.

SELECT * SUM(AMOUNT)
FROM SALARIES
GROUP BY EMPLOYEE;

Question 25

HAVING

Answer 25

WHERE does not work in conjunction with aggregate clauses, so use HAVING when using sum, avg, min, max, count.

SELECT DEPARTMENT, AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT
HAVING AVG(SALARY) < 38000;

Question 26

<>

!=

Answer 26

Not equal to

Question 27

CREATE TABLE

Answer 27

CREATE TABLE –

(ITEM CHAR(20), COST NUMBER, ROOM CHAR(20), REMARKS CHAR(40));

Question 28

INSERT INTO . . . VALUES

INSERT INTO . . . SELECT

Answer 28

INSERT INTO TABLE2
(COL1, COL2, COL3)
SELECT/VALUES (COL1, COL2, COL3)
FROM TABLE1;

Question 29

UPDATE . . . SET . . . WHERE

Answer 29

Statement checks WHERE clause first, the makes the change where the WHERE clause evaluates to TRUE.

UPDATE TABLE
SET PRICE = 90.00
WHERE ITEM = ‘PLATTER’;

Question 30

DELETE

Answer 30

DELETE FROM TABLE

WHERE PRICE < 0;