The SQL LIKE Operator

The SQL LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

SQL LIKE Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Examples

SELECT * FROM tbsample WHERE EmployeeName LIKE '%'                          --  selects all EmployeeName

SELECT * FROM tbsample WHERE EmployeeName LIKE 's%'           --  selects all from tbsample  with a EmployeeName Starting with the letter "s"  

SELECT * FROM tbsample WHERE EmployeeName LIKE '%m'         --  selects all from tbsample  with a EmployeeName ending with the letter "m"

SELECT * FROM tbsample WHERE EmployeeName LIKE '_a%'         --  selects all from tbsample  with a EmployeeName Starting with any letter and the next letter must be "a" followed by any letters.

SELECT * FROM tbsample WHERE EmployeeName LIKE '[a-k]%'    --  selects all from tbsample  with a EmployeeName Starting from "a"  to "k" followed by any letters.

SELECT * FROM tbsample WHERE EmployeeName LIKE '_[a-k]%'   --  selects all from tbsample  with a EmployeeName Starting from any letter ,next letter should be between "a"  to "k" followed by any letters.

SELECT * FROM tbsample WHERE EmployeeName LIKE '[^a-k]%'   -- selects all  from tbsample  excluding ( EmployeeName Starting from "a"  to "k" ) followed by any letters.

SELECT * FROM tbsample WHERE EmployeeName LIKE '_[^a-k]%'  -- selects all from tbsample  with a EmployeeName Starting from any letter ,next letter should exclude ( EmployeeName Starting from "a"  to "k" ) followed by any letters.

The ALTER TABLE Statement

The ALTER TABLE Statement

ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

To add a column in a table

Syntax : 

alter tablename add column columnname datatype 

Example: 

alter empdetails add column city varchar (50)

To delete a column in a table

Syntax : 

alter table tablename drop column columnname

Example: 

alter table empdetails drop column city 

To change the data type of a column in a table

Syntax : 

alter table tablename alter column columnname datatype

Example: 

alter table empdetails alter column salary varchar(50)

Note:

 We cannot change a character datatype of a column to int , varchar , nvarchar after the table has been inserted with values.

SQL UPDATE Statement

The SQL UPDATE Statement

          The UPDATE statement is used to update existing records in a table.

Syntax:

update tablename set columnname1 = value where columnname = value.

(or)

update tablename set columnname1 = value,columnname2 = value,.... where columnname = value.

Example :

update empdetails set name = peter where id = 1

The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Deleting and Truncating a Table

Deleting a Table 

The DELETE statement is used to delete rows in a table.

Delete All Data 

Query :

delete from tablename  

Example :

delete from empdetails

Delete Particular Row - using Where clause

Query :

delete from tablename where columnname = value

Example :

delete from empdetails where id = 1

Be very careful when deleting records. You cannot undo this statement!

Tuncating a Table

The TRUNCATE statement is used to removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

Query :

truncate table tablename  

Example :

truncate table empdetails.

What is SQL and types of SQL Statements ?

What is Sql ?

     It is specific type of Structured query Language , used to manage the DataBase , by retrieving , updating and modifying the database.. 

           

    Types of SQL Statements : 

   

    DML : Data Manipulation Language is used for managing data with in schema Objects. 

            > Select , Insert , Update , Delete.

    DDL : Data Definition Language is used to define db structure or schema. 

           > Alter , Create , Truncate , Drop.Comment,Rename. 

    DCL : Data Control Language is used to control access to data stored in the database. 

          

           > Grant , Revoke. 

     TCL : Transition Control Language is used to control set of actions called transactions                   like insert,update,delete. 

          > Commit , Rollback . 

Sql Server Concepts.

SQL - RDBMS Concepts

 <-----------SQL SERVER--------------------->

    SQL is a standard language for accessing and manipulating databases.

'What is SQL?'

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standard.

'What Can SQL do?'

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

'Semicolon after SQL Statements?'

 *Some database systems require a semicolon at the end of each SQL  statement.

*Semicolon is the standard way to separate each SQL statement in

 database systems that allow more than one SQL statement to be  executed in the same call to the server.

 *We are using MS Access and SQL Server 2000 and we do not    have to put a semicolon after each SQL statement,but some  database programs force you to use it.

 <---------------------------------------------------------------->

'Character or Strings'

   The characters or strings must enclosed only in single Quatation.

    ex:'siva','sakthi','Imrankhan','Ranbhirkapoor'

<----------------------------------------------------->

'Not case sensitive'

     SQL is not case sensitive. SELECT is the same as select.

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

What is table?

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

Remember, a table is the most common and simplest form of data storage in a relational database. Following is the example of a CUSTOMERS table:

What is field?

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

A field is a column in a table that is designed to maintain specific information about every record in the table.

What is record or row?

A record, also called a row of data, is each individual entry that exists in a table. For example there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table:

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would consist of the following:

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

• NOT NULL Constraint: Ensures that a column cannot have NULL value.
• DEFAULT Constraint: Provides a default value for a column when none is specified.
• UNIQUE Constraint: Ensures that all values in a column are different.
• PRIMARY Key: Uniquely identified each rows/records in a database table.
• FOREIGN Key: Uniquely identified a rows/records in any another database table.
• CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
• INDEX: Use to create and retrieve data from the database very quickly.

Below are the Constraints we are going to study :

SQL Select
SQL Distinct
SQL Where
SQL And & Or
SQL Order By
SQL Insert Into
SQL Update
SQL Delete
SQL Injection
SQL Select Top
SQL Like
SQL Wildcards
SQL In
SQL Between
SQL Aliases
SQL Joins
SQL Inner Join
SQL Left Join
SQL Right Join
SQL Full Join
SQL Union
SQL Select Into
SQL Into Select
SQL Create DB
SQL Create Table
SQL Constraints
SQL Not Null
SQL Unique
SQL Primary Key
SQL Foreign Key
SQL Check
SQL Default
SQL Create Index
SQL Drop
SQL Alter
SQL Auto Increment
SQL Views
SQL Dates
SQL Null Values
SQL Null Functions
SQL Data Types
SQL DB Data Types

SQL Functions
SQL Functions
SQL Avg()
SQL Count()
SQL First()
SQL Last()
SQL Max()
SQL Min()
SQL Sum()
SQL Group By
SQL Having
SQL Ucase()
SQL Lcase()
SQL Mid()
SQL Len()
SQL Round()
SQL Now()
SQL Format()

SQL Quick Ref
SQL Hosting