Podcast
Questions and Answers
How can you select employees with salaries between 500 and 700 using a SQL WHERE clause?
How can you select employees with salaries between 500 and 700 using a SQL WHERE clause?
You can use: SELECT VARDAS FROM DARBUOTOJAI WHERE ATLYGINIMAS BETWEEN 500 AND 700.
What SQL syntax would you use to find employee IDs for names 'Algis' and 'Grigas'?
What SQL syntax would you use to find employee IDs for names 'Algis' and 'Grigas'?
You would use: SELECT ASMENS_KODAS FROM DARBUOTOJAI WHERE VARDAS IN ('Algis', 'Grigas').
When would you use the LIKE operator in a SQL query?
When would you use the LIKE operator in a SQL query?
You use it when you want to search for a pattern in a string, like: WHERE PAVARDE LIKE '%l_'.
How can you query for employees whose store ID is not recorded in the database?
How can you query for employees whose store ID is not recorded in the database?
Signup and view all the answers
Explain how to use the AND operator in SQL to combine conditions.
Explain how to use the AND operator in SQL to combine conditions.
Signup and view all the answers
What will the query SELECT * FROM DARBUOTOJAI WHERE ATLYGINIMAS > 300 OR PARDUOTUVES_ID = 2 return?
What will the query SELECT * FROM DARBUOTOJAI WHERE ATLYGINIMAS > 300 OR PARDUOTUVES_ID = 2 return?
Signup and view all the answers
How can you specify a condition that negates a set of values in SQL?
How can you specify a condition that negates a set of values in SQL?
Signup and view all the answers
What role do comparison operators play in SQL WHERE conditions?
What role do comparison operators play in SQL WHERE conditions?
Signup and view all the answers
Study Notes
SQL WHERE Clause
- The
WHEREclause filters data based on conditions. - Conditions can be arithmetic expressions or strings combined with comparison operators like
=,>,<,>=,<=,<>(or!=). - Example:
SELECT * FROM EMPLOYEES WHERE SALARY >= 600 - Example:
SELECT NAME FROM PRODUCTS WHERE CATEGORY <> 'Electronics' - Allows comparisons of values in multiple columns.
SQL WHERE (BETWEEN ... AND)
-
BETWEEN ... AND ...selects values within a specific range (inclusive). - Example:
SELECT NAME FROM PRODUCTS WHERE PRICE BETWEEN 10 AND 50
SQL WHERE (IN)
-
IN (...)selects values from a list of specified values. - Example:
SELECT * FROM STUDENTS WHERE GRADE IN ('A', 'B')
SQL WHERE (LIKE)
-
LIKEis used for pattern matching. - Wildcard characters
%(any sequence of characters) and_(any single character) are used withLIKE. - Example:
SELECT NAME FROM PRODUCTS WHERE NAME LIKE '%phone%'(finds names containing "phone"). - Example:
SELECT NAME FROM PRODUCTS WHERE NAME LIKE '_phone'(finds names starting with a single character followed by "phone").
SQL WHERE (NULL)
-
NULLrepresents missing or unknown data. -
IS NULLchecks for missing values in a column. - Example:
SELECT * FROM EMPLOYEES WHERE DEPARTMENT IS NULL
SQL WHERE (NOT, AND, OR)
-
NOT,AND, andORcombine multiple conditions. -
ANDrequires all conditions to be true. -
ORrequires at least one condition to be true. -
NOTreverses the condition. - Example:
SELECT * FROM ORDERS WHERE STATUS = 'Shipped' AND CUSTOMER_ID = 101. - Example:
SELECT * FROM ORDERS WHERE STATUS = 'Shipped' OR PAYMENT_TYPE = 'Credit Card'.
SQL WHERE (Prioritization)
- SQL operators have an order of precedence.
- Comparison operators (
=,>,<, etc.) come beforeAND,OR. - Parenthesis can change order.
- Example:
SELECT * FROM ORDERS WHERE (STATUS = 'Shipped' OR STATUS = 'Delivered') AND CUSTOMER_ID = 101.
SQL ORDER BY
-
ORDER BYclause sorts data in ascending or descending order. - Example:
SELECT NAME FROM EMPLOYEES ORDER BY SALARY ASC. - Example:
SELECT NAME FROM EMPLOYEES ORDER BY SALARY DESC.
SQL Other Functions
-
DISTINCT: selects unique values from a column. -
LIKE(case insensitive) :ILIKE. -
LOWER/UPPER: converts text to lower/upper case. -
CONCAT: combines data from multiple columns. - Examples:
SELECT DISTINCT CITY FROM CUSTOMERS,SELECT * FROM PRODUCTS WHERE NAME ILIKE '%shirt%'.
SQL Aggregate Functions (used with GROUP BY)
-
AVG: calculates the average of a column. -
COUNT: counts rows matching a condition. -
MAX: finds the maximum value in a column. -
MIN: finds the minimum value in a column. -
SUM: sums values in a column.
SQL GROUP BY
-
GROUP BYgroups rows with the same values in a specified column. - Used with aggregate functions to perform calculations on groups, not individual rows.
SQL HAVING
-
HAVINGclause is used withGROUP BYto filter groups of data based on aggregate functions.
SQL Syntax Summary
-
SELECT+column_names(or*) -
FROM+table_name -
WHERE+conditions (filter data) -
GROUP BY+column_name(s)(group data) -
HAVING+condition(filter grouped data) -
ORDER BY+column_name asc/desc(sort data)
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Related Documents
Description
This quiz explores the SQL WHERE clause, detailing its various functionalities, including comparisons, ranges, lists, and pattern matching. You'll learn how to filter data effectively using conditions in multiple columns with practical examples. Test your understanding of using expressions, operators, and wildcards.
