Question 1

Single Table Query
The SELECT command

Accepted Answer

is used to retrieve data from a database.

Question 2

SELECT-FROM-WHERE

Accepted Answer

basic form of the SELECT command

Question 3

Basic Queries
Select All Columns (*)

Accepted Answer

To retrieve all columns from a table, use an asterisk *

Question 4

SELECT * FROM table_name;

Accepted Answer

Syntax of retrieving all columns

Question 5

SELECT * FROM PART;

Accepted Answer

To list the complete PART table

Question 6

SELECT column1, column2 FROM table_name;

Accepted Answer

Syntax of specific tables:

Question 7

Select Specific Columns

Accepted Answer

List the column names you want to see

Question 8

SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE FROM CUSTOMER;

Accepted Answer

To list the number, name, and balance for all customers

Question 9

Filtering with WHERE

Accepted Answer

the WHERE clause restricts the rows returned by a query

Question 10

Simple Condition

Accepted Answer

Uses a comparison operator (=, >, <, <>, !=, etc.)

Question 11

SELECT CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_NUM = ‘148’;

Accepted Answer

Find the name of customer number of number 148

Question 12

Compound Condition (AND, OR, NOT)

Accepted Answer

Connects two or more simple conditions

Question 13

Example (AND):

Accepted Answer

SELECT DESCRIPTION FROM PART WHERE WAREHOUSE = ‘3’;
AND ON_HAND > 25;

Question 14

Example (OR):

Accepted Answer

SELECT DESCRIPTION FROM PART WHERE WAREHOUSE = ‘3’;
OR ON_HAND > 25;

Question 15

BETWEEN Operator

Accepted Answer

Selects values within a given range, inclusive

Question 16

SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE FROM CUSTOMER WHERE BALANCE BETWEEN 2000 AND 5000;

Accepted Answer

List customers with balances between $2,000 and $5,000.

Question 17

LIKE Operator

Accepted Answer

Used for pattern matching in character strings. % represents any
collection of characters, and _ represents any single character.

Question 18

List customers on a street containing ‘Central’

Accepted Answer

SELECT CUSTOMER_NUM, CUSTOMER_NAME, STREET FROM CUSTOMER WHERE STREET LIKE ‘%Central%’;

Question 19

IN Operator

Accepted Answer

Checks if a value matches any value in a list.

Question 20

List customers with a credit limit of $5,000, $10,000, or $15,00049

Accepted Answer

SELECT CUSTOMER_NUM, CUSTOMER_NAME, CREDIT_LIMIT FROM CUSTOMER WHERE CREDIT_LIMIT IN (5000, 10000, 15000);

Question 21

IS NULL Operator

Accepted Answer

Finds rows where a column’s value is unknown.

Question 22

List customers with an unknown street value

Accepted Answer

SELECT CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER WHERE STREET IS NULL;

Question 23

Computed Columns

Accepted Answer

you can perform calculations on columns directly in the SELECT clause

Question 24

AS

Accepted Answer

Used to assign a name to the resulting column.

Question 25

Syntax for Computed Columns

Accepted Answer

SELECT column1, (column2 - column3) AS new_column_name FROM table_name;

Question 26

Find the available credit for each customer

Accepted Answer

SELECT CUSTOMER_NUM, CUSTOMER_NAME, (CREDIT_LIMIT - BALANCE) AS AVAILABLE_CREDIT FROM CUSTOMER;

Question 27

Sorting with ORDER BY

Accepted Answer

the ORDER BY clause sorts the results. The default is ascending (ASC). Use DESC for descending order.

Question 28

Syntax for sorting results

Accepted Answer

SELECT column1, column2 FROM table_name ORDER BY column_to_sort [ASC|DESC];

Question 29

List customers ordered by their balance

Accepted Answer

SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE FROM CUSTOMER ORDER BY BALANCE;

Question 30

Grouping and Aggregate Functions 
Aggregate functions

Accepted Answer

perform a calculation on a set of rows and return a single value.

Question 31

COUNT()

Accepted Answer

Counts the number of rows

Question 32

SUM()

Accepted Answer

Calculates the total of values in a numeric column.

Question 33

AVG()

Accepted Answer

Calculates the average value in a numeric column.

Question 34

MAX()

Accepted Answer

Determines the maximum value in a column.

Question 35

MIN()

Accepted Answer

Determines the minimum value in a column.

Question 36

GROUP BY Clause

Accepted Answer

Groups rows that have the same values into summary rows.

Question 37

For each sales rep, find their number and the average balance of their customers.

Accepted Answer

SELECT REP_NUM, AVG(BALANCE) FROM CUSTOMER GROUP BY REP_NUM;

Term	Definition
Single Table Query The SELECT command	is used to retrieve data from a database.
SELECT-FROM-WHERE	basic form of the SELECT command
Basic Queries Select All Columns (*)	To retrieve all columns from a table, use an asterisk *
SELECT * FROM table_name;	Syntax of retrieving all columns
SELECT * FROM PART;	To list the complete PART table
SELECT column1, column2 FROM table_name;	Syntax of specific tables:
Select Specific Columns	List the column names you want to see
SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE FROM CUSTOMER;	To list the number, name, and balance for all customers
Filtering with WHERE	the WHERE clause restricts the rows returned by a query
Simple Condition	Uses a comparison operator (=, >, <, <>, !=, etc.)
SELECT CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_NUM = ‘148’;	Find the name of customer number of number 148
Compound Condition (AND, OR, NOT)	Connects two or more simple conditions
Example (AND):	SELECT DESCRIPTION FROM PART WHERE WAREHOUSE = ‘3’; AND ON_HAND > 25;
Example (OR):	SELECT DESCRIPTION FROM PART WHERE WAREHOUSE = ‘3’; OR ON_HAND > 25;
BETWEEN Operator	Selects values within a given range, inclusive
SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE FROM CUSTOMER WHERE BALANCE BETWEEN 2000 AND 5000;	List customers with balances between $2,000 and $5,000.
LIKE Operator	Used for pattern matching in character strings. % represents any collection of characters, and _ represents any single character.
List customers on a street containing ‘Central’	SELECT CUSTOMER_NUM, CUSTOMER_NAME, STREET FROM CUSTOMER WHERE STREET LIKE ‘%Central%’;
IN Operator	Checks if a value matches any value in a list.
List customers with a credit limit of $5,000, $10,000, or $15,00049	SELECT CUSTOMER_NUM, CUSTOMER_NAME, CREDIT_LIMIT FROM CUSTOMER WHERE CREDIT_LIMIT IN (5000, 10000, 15000);
IS NULL Operator	Finds rows where a column’s value is unknown.
List customers with an unknown street value	SELECT CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER WHERE STREET IS NULL;
Computed Columns	you can perform calculations on columns directly in the SELECT clause
AS	Used to assign a name to the resulting column.
Syntax for Computed Columns	SELECT column1, (column2 - column3) AS new_column_name FROM table_name;
Find the available credit for each customer	SELECT CUSTOMER_NUM, CUSTOMER_NAME, (CREDIT_LIMIT - BALANCE) AS AVAILABLE_CREDIT FROM CUSTOMER;
Sorting with ORDER BY	the ORDER BY clause sorts the results. The default is ascending (ASC). Use DESC for descending order.
Syntax for sorting results	SELECT column1, column2 FROM table_name ORDER BY column_to_sort [ASC\|DESC];
List customers ordered by their balance	SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE FROM CUSTOMER ORDER BY BALANCE;
Grouping and Aggregate Functions Aggregate functions	perform a calculation on a set of rows and return a single value.
COUNT()	Counts the number of rows
SUM()	Calculates the total of values in a numeric column.
AVG()	Calculates the average value in a numeric column.
MAX()	Determines the maximum value in a column.
MIN()	Determines the minimum value in a column.
GROUP BY Clause	Groups rows that have the same values into summary rows.
For each sales rep, find their number and the average balance of their customers.	SELECT REP_NUM, AVG(BALANCE) FROM CUSTOMER GROUP BY REP_NUM;

"Know" box contains:
Time elapsed:
Retries:

fundata2