Table in SQL Server
1. SQL server CREATE TABLE statement.
It is used to create a table using SQL Server database. Click on the new query in the tool bar and open a new window.
Example: Create a table titled 'Students' in SQL server.
CREATE TABLE Students
(
Stud_ID int IDENTITY (1,1) NOT NULL PRIMARY KEY,
Stud_Name varchar(50) NOT NULL
);
It is used to load a data in table using SQL Server database.
Example: Insert data in table titled 'Students' using SQL Server INSERT INTO statement.
INSERT INTO Students
values('1', 'Albert');
It is used to update existing records in table using SQL server database.
Example: Update data in table titled 'Clients' using SQL Server UPDATE statement.
Consider the following table titled 'Clients'
| Client_ID | Last_Name | First_Name | Contact | Country |
|---|
| 1 | Thomas | Alex | 2400000 | USA |
| 2 | Cruise | Martin | 5600000 | USA |
| 3 | Pandit | Prajakta | null | India |
Following are the queries used to carry this out:
A. Write a query to update contact information of a particular row where condition is given as, client id = 3.
UPDATE Clients
SET Contact = 34542892
WHERE Client_ID = 3 AND LAST_Name = 'Pandit'
| Client_ID | Last_Name | First_Name | Contact | Country |
|---|
| 1 | Thomas | Alex | 2400000 | USA |
| 2 | Cruise | Martin | 5600000 | USA |
| 3 | Pandit | Prajakta | 34542892 | India |
B. Write a query to perform update operation on multiple fields from given table (titled as Clients) below.
| Client_ID | Last_Name | First_Name | Contact | Country |
|---|
| 1 | Thomas | Alex | 2400000 | USA |
| 2 | Cruise | Martin | 5600000 | USA |
| 3 | Pandit | Prajakta | 34542892 | India |
UPDATE Clients
SET LAST_Name = 'Brown', First_name = 'Albert', Contact = 923849, Country = 'UK'
WHERE Client_ID = 3
| Client_ID | Last_Name | First_Name | Contact | Country |
|---|
| 1 | Thomas | Alex | 2400000 | USA |
| 2 | Cruise | Martin | 5600000 | USA |
| 3 | Brown | Albert | 923849 | UK |
4. SQL server DELETE statement
Using SQL server DELETE Statement, user can delete one or multiple rows from table.
Syntax
DELETE FROM table_name
WHERE [Condition];
Delete data in table titled 'Clients' using SQL Server DELETE statement.
Consider the following table titled 'Clients'.
| Client_ID | Last_Name | First_Name | Contact | Country |
|---|
| 1 | Thomas | Alex | 2400000 | USA |
| 2 | Cruise | Martin | 5600000 | USA |
| 3 | Pandit | Prajakta | 34542892 | India |
Following are the queries used to carry this out:
A. Write a query to delete row where, Client_ID = 2.
DELETE FROM Clients
WHERE
Client_ID = 2
| Client_ID | Last_Name | First_Name | Contact | Country |
|---|
| 1 | Thomas | Alex | 2400000 | USA |
| 3 | Pandit | Prajakta | 34542892 | India |
B. Write a query to delete all rows from the table.
The result is shown in the following table.
| Client_ID | Last_Name | First_Name | Contact | Country |
|---|
