Start Coding

Subjects

JAVASCRIPTHTMLCSSPYTHONSQLJAVACPPCSHARPPHPTYPESCRIPTCRUBYGOSWIFTKOTLINRUSTRBASHSCALAOBJECTIVE-CDARTPERLLUAMATLABXMLJSONMARKDOWNASSEMBLYLATEXYAMLSOLIDITYBLOCKCHAIN

Topics

Introduction to SQL

What is SQL?SQL HistorySQL StandardsSQL vs NoSQLSQL Database Management Systems

SQL Basics

SQL SyntaxSQL CommentsSQL Data TypesSQL OperatorsSQL ExpressionsSQL LiteralsSQL IdentifiersSQL KeywordsSQL Case Sensitivity

SQL Database Fundamentals

Database ConceptsTables and FieldsPrimary KeysForeign KeysIndexesConstraintsNormalizationDenormalizationSQL Schema Design

SQL Data Manipulation

SQL SELECT StatementSQL INSERT StatementSQL UPDATE StatementSQL DELETE StatementSQL MERGE StatementSQL AliasesSQL WildcardsSQL LIKE OperatorSQL IN OperatorSQL BETWEEN OperatorSQL NULL ValuesSQL ORDER BY ClauseSQL LIMIT ClauseSQL OFFSET Clause

SQL Filtering and Sorting

SQL WHERE ClauseSQL AND, OR, NOT OperatorsSQL GROUP BY ClauseSQL HAVING ClauseSQL DISTINCT KeywordSQL TOP Clause

SQL Joins

SQL INNER JOINSQL LEFT JOINSQL RIGHT JOINSQL FULL OUTER JOINSQL CROSS JOINSQL Self JOINSQL Natural JOINSQL Multiple JOINs

SQL Functions

SQL Aggregate FunctionsSQL Scalar FunctionsSQL String FunctionsSQL Numeric FunctionsSQL Date FunctionsSQL Conversion FunctionsSQL System FunctionsSQL User-Defined Functions

SQL Subqueries

SQL Subquery BasicsCorrelated SubqueriesNested SubqueriesSQL EXISTS OperatorSQL ANY and ALL OperatorsSubqueries in SELECTSubqueries in FROMSubqueries in WHERE

SQL Views

Creating SQL ViewsAltering SQL ViewsDropping SQL ViewsUpdatable ViewsMaterialized ViewsView Limitations

SQL Transactions

SQL Transaction BasicsACID PropertiesBEGIN TRANSACTIONCOMMITROLLBACKSAVEPOINTTransaction Isolation Levels

SQL Data Definition

SQL CREATE TABLESQL ALTER TABLESQL DROP TABLESQL TRUNCATE TABLESQL CREATE INDEXSQL DROP INDEXSQL CREATE DATABASESQL ALTER DATABASESQL DROP DATABASE

SQL Data Control

SQL GRANTSQL REVOKESQL RolesSQL UsersSQL Privileges

SQL Performance Optimization

SQL Query OptimizationSQL Execution PlansSQL Index OptimizationSQL Query TuningSQL CachingSQL Partitioning

Advanced SQL Concepts

SQL Stored ProceduresSQL TriggersSQL CursorsSQL Common Table ExpressionsSQL Window FunctionsSQL Pivot and UnpivotSQL JSON OperationsSQL XML OperationsSQL Full-Text Search

SQL Best Practices

SQL Naming ConventionsSQL Code OrganizationSQL Error HandlingSQL Security Best PracticesSQL Backup and RecoverySQL Version Control

SQL LIKE Operator

The SQL LIKE operator is a versatile tool used for pattern matching in SQL queries. It allows you to search for specific patterns within string values, making it invaluable for filtering and searching data in databases.

Syntax and Usage

The basic syntax of the LIKE operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

The LIKE operator uses two wildcard characters:

  • % - Represents zero, one, or multiple characters
  • _ - Represents a single character

Common Use Cases

1. Finding names starting with a specific letter

SELECT first_name
FROM customers
WHERE first_name LIKE 'A%';

This query returns all first names starting with 'A'.

2. Searching for a pattern anywhere in the string

SELECT product_name
FROM products
WHERE product_name LIKE '%phone%';

This query finds all product names containing 'phone' anywhere in the string.

3. Matching a specific number of characters

SELECT city
FROM addresses
WHERE city LIKE '___ton';

This query matches cities ending with 'ton' and having exactly three characters before it (e.g., Boston).

Important Considerations

  • The LIKE operator is case-sensitive in some database systems. Use the ILIKE operator for case-insensitive matching in PostgreSQL.
  • Escaping special characters: Use the escape character (usually '\') to match literal % or _ characters.
  • Performance: LIKE operations, especially with leading wildcards, can be slow on large datasets. Consider using SQL Indexes to improve performance.

Advanced Usage

The LIKE operator can be combined with other SQL Operators for more complex queries:

SELECT email
FROM users
WHERE email LIKE '%@gmail.com'
AND username NOT LIKE 'admin%';

This query finds Gmail users whose usernames don't start with 'admin'.

Conclusion

The SQL LIKE operator is a powerful tool for pattern matching in databases. By mastering its usage, you can create more flexible and precise queries, enhancing your ability to search and filter data effectively. Remember to consider performance implications when using LIKE with large datasets, and explore other SQL String Functions for more advanced text manipulation capabilities.