SQLSERVER BASIC INTERVIEW QNS PART 5

Using LIKE

The following SQL statement will return persons with first names that start with an 'O':

SELECT * FROM Persons WHERE FirstName LIKE 'O%'

 The following SQL statement will return persons with first names that end with an 'a':

 SELECT * FROM Persons  WHERE FirstName LIKE '%a'

 The following SQL statement will return persons with first names that contain the pattern 'la':

 SELECT * FROM Persons  WHERE FirstName LIKE '%la%'

 The INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table.

Syntax

 INSERT INTO table_name  VALUES (value1, value2,....)

 You can also specify the columns for which you want to insert data:

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

 Insert a New Row

This "Persons" table:

LastName FirstName Address City

Pettersen Kari Storgt 20 Stavanger

 And this SQL statement:

 INSERT INTO Persons VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

 Will give this result:

 LastName FirstName Address City

 Pettersen Kari Storgt 20 Stavanger

Hetland Camilla Hagabakka 24 Sandnes

 Insert Data in Specified Columns

This "Persons" table:

LastName FirstName Address City

Pettersen Kari Storgt 20 Stavanger

Hetland Camilla Hagabakka 24 Sandnes

 And This SQL statement:

 INSERT INTO Persons (LastName, Address) VALUES ('Rasmussen', 'Storgt 67')

Will give this result:

 LastName FirstName Address City

 Pettersen Kari Storgt 20 Stavanger

 Hetland Camilla Hagabakka 24 Sandnes

 RasmussenStorgt 67

 SQL The UPDATE Statement

The Update Statement

The UPDATE statement is used to modify the data in a table.

Syntax

UPDATE table_name

SET column_name = new_value

WHERE column_name = some_value  from Person

LastName FirstName Address City

Nilsen Fred Kirkegt 56 Stavanger

RasmussenStorgt 67

 Update one Column in a Row

We want to add a first name to the person with a last name of "Rasmussen":

 UPDATE Person SET FirstName = 'Nina' WHERE LastName = 'Rasmussen'

 Result:

 LastName FirstName Address City

 Nilsen Fred Kirkegt 56 Stavanger

 Rasmussen Nina Storgt 67

 Update several Columns in a Row

We want to change the address and add the name of the city:

 UPDATE Person

SET Address = 'Stien 12', City = 'Stavanger'

WHERE LastName = 'Rasmussen'

Result:

 LastName FirstName Address City

Nilsen Fred Kirkegt 56 Stavanger

Rasmussen Nina Stien 12 Stavanger

 Product Spotlight

 SQL The Delete Statement

 The Delete Statement

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

 Syntax

 DELETE FROM table_name WHERE column_name = some_value from  Person

LastName FirstName Address City

Nilsen Fred Kirkegt 56 Stavanger

Rasmussen Nina Stien 12 Stavanger

 Delete a Row

 "Nina Rasmussen" is going to be deleted:

 DELETE FROM Person WHERE LastName = 'Rasmussen'

 Result

 LastName FirstName Address City

Nilsen Fred Kirkegt 56 Stavanger

 Delete All Rows

 It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_nameorDELETE * FROM table_name