-> Use of Pattern matching is verifying data format.
-> we can do pattern matching while searching using the 'like' keyword
-> like keyword uses regular expression for searching.
|%||any kind of string containing zero and more characters|
|_ [underscore]||Any single character|
|||any single character with specified range|
|[^]||Any single character not within the specified range|
Query to display employees name whose fname starts with 'a'
select * from employees where fname like 'a%'
Query to display employees name whose fname is having char 'a' in last place
select * from employees where fname like '%a'
Query to display employees name whose lname is having having 'u' in second position
select * from employees where lname like '_u%'
Query to display employees name whose lname is having having '_'
select * from employees where lname like '%[_]%'
query to display employee name whose lnames are starting character a or b or c or d or e
select * from employees where lname like '[a-e]%'
query to display employee name whose fnames are not starting character a or b or c or d or e
select * from employees where fname like '[^a-e]%'
Generic insert, update and delete Queries:
insert into tablename(col1,col2,...) values(value1,value2,...)
upadate tablename set col1=value1,col2=value2,...where condition.
delete[from] tablename[where condition]
-> if we don't specify where condition then all the data present in table will be deleted.
truncate table <tablename>
-> it will always delete the complete data present in the source table
-> it is not a logged operation.
-> it is not an reversible operation whereas all DML statements are reversible operation.
-> truncate is faster than delete statement without where clause.
drop table <tablename>
-> will delete complete table structure along with data.
Difference between Delete,truncate and drop statements:
-> Delete statement will delete all or few of the rows based on the where clause.
-> truncate will delete all rows from the given table.
-> Delete statement is a logged operation.
-> truncate is not a logged operation,hence it is faster than delete.
-> truncate will delete only table data not table structure but in case of drop table data as well as table structure will be deleted.