SQL OPERATOR WEEK 2 21ST-22ND NOVEMBER

SQL (Structured Query Language) allows communication with databases to facilitate easy extraction and manipulation of data. Data Query Language (DQL) is a sublanguage in SQL that lets us filter, organize, and retrieve specific data based on a given condition. The SELECT keyword forms the foundation of querying and data extraction. When using SELECT, we are not creating new tables; we are just temporarily retrieving read-only data based on requested results.
Basic query syntax is:
​The FROM keyword always specifies which table to query.

​SELECT * retrieves data from all available columns in the table.

​LIMIT restricts the number of rows returned by a query.
Filtering & Operators
​The WHERE clause is used to retrieve data conditionally and filter data where a specific condition is met or true.

​It is used alongside comparison operators to determine whether a condition is true or false:

​Equals (=)

​Not equal to (<> or !=)

​Less than (<)

​Greater than (>)

​Less than or equal to (<=)

​Greater than or equal to (>=)
Removing Duplicates
​SELECT DISTINCT eliminates rows where data is duplicated or exact duplicates exist.

​It does not change the original table in the database; it only affects the query results by returning unique values.
​Aliasing uses the AS keyword to temporarily rename a table or column in a query for easy readability and simplification.

​Syntax: SELECT col_name AS new_col_name FROM db.table;.

​Always use descriptive names while aliasing
To save query results in the database without interfering with the original dataset, we create a new table first, then insert the query results into it.
Example:
CREATE TABLE No_Duplicates (name VARCHAR, age INT);
SELECT DISTINCT name, age FROM company.employees;
INSERT INTO No_Duplicates (name, age);
Combining Conditions with Logical Operators (AND, OR, NOT)
​Often, a single condition isn't specific enough. This is where logical operators come in:

​AND-Returns records only if all conditions separated by AND are true.
​OR- Returns records if at least one of the conditions is true.
​NOT- Reverses the result of the condition (excludes matches).
Working with Ranges and Lists (BETWEEN and IN)
​Writing multiple OR statements can make your queries messy. SQL provides cleaner shorthand tools for ranges and lists:

​BETWEEN-Filters data within a specific range (inclusive of the boundary values).
​IN- Checks if a column matches any value inside a specified list.
Dealing with Missing Data (IS NULL and IS NOT NULL)
​In data science and database administration, missing or incomplete data is a daily hurdle. In SQL, missing data is represented by NULL.
​You cannot use standard comparison operators like = to find NULL values (more on that in the mistakes section!). Instead, you must use IS NULL or IS NOT NULL.
Text Pattern Matching with LIKE
​Exact matches are helpful, but what if you only remember part of a patient's name or a drug code? The LIKE operator allows you to search for patterns using wildcards:

​% represents zero, one, or multiple characters.
​_ represents a single character.
Common Mistakes to Avoid
​Every beginner runs into these roadblocks when starting out with SQL filtering. Keep these in mind to save yourself hours of debugging:

i)​Using = NULL instead of IS NULL: Because NULL represents the absence of a value, writing WHERE column = NULL will always evaluate to unknown and return zero rows. Always use IS NULL or IS NOT NULL.

​