SQL SERVER BASIC QUERIES

SQL SERVER BASIC QUERIES

SQL commands can be used to search the database and to do other functions like creating tables, adding data to tables, modifying data, and dropping tables.

Here is a list of basic SQL commands (sometimes called clauses) you should know if you are going to work with SQL.

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

Syntax

create database databasename

Example

create database TestDB

The SQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop an existing SQL database.

Syntax

DROP DATABASE databasename

Example

DROP DATABASE TestDB

The SQL ALTER DATABASE Statement

The ALTER DATABASE statement is used to modify the existing SQL database name.

Syntax

Alter Database Databasename Modify name = NewDatabaseName;

Example

Alter Database LearnSQL modify name = TestDB1;

Another way to change database name

Syntax

execute sp_renamedb ' OldDatabaseName', 'NewDatabaseName'

Example

execute sp_renamedb 'TestDB','TestDB1'

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

Syntax

SELECT column1, column2, column2
FROM table_name;

Example

SELECT First_Name, Last_Name, salary
FROM Employee;

Here, First_Name, Last_Name, Salary, are the field names of the table you want to select data from.

If you want to select all the fields available in the table, use the below Example:

Example

Select * from Employee

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    column4 datatype   
);

Create Table Example

Below example creates a table called "Employee" that contains six columns: EmployeeID, FirstName, LastName, Address, and City:

CREATE TABLE Employee (
    EmployeeID int,
    FirstName varchar(255),
    LastName varchar(255), 
    Salary int
    Address varchar(255),
    City varchar(255)
);

The EmployeeID and Salary column is of type int and will hold an integer.

The FirstName, LastName,  Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

Syntax

INSERT INTO table_name
VALUES (value1, value2, value3, value4, value5, value6)

Example

INSERT INTO Employee
VALUES (2, 'Ravi','Kumar', 20000 , 'Prem Nagar', 'Mumbai')

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

Another way to insert data into table

It is also possible to only insert data in specific columns.

If you want to insert data in specific columns,you need to specify the column names in the SQL query.

Syntax

INSERT INTO table_name (column1, column2, column3,)
VALUES (value1, value2, value3);

Example

INSERT INTO Employee (EmployeeID, FirstName, City)
VALUES (1, 'Ravi', 'Mumbai');

Change Table  Name

Syntax

execute sp_rename 'OldTableName', 'OldTableName'

Example

execute sp_rename 'Employee','EmployeeAA'

The SQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

DROP TABLE table_name;

Example

DROP TABLE Employee;

Note: Be careful before dropping a table. Deleting a table will result in loss of complete information stored in the table!

The SQL UPDATE TABLE Statement

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

Syntax

UPDATE table_name SET column1 = value WHERE condition;

Example

update Employee set LastName = 'AAA' where EmployeeID=1;

Note: Be careful when updating records in a table! If you omit the WHERE clause, all records in the table will be updated.

Below query change the values of salay 20,000 whose employee id 1 or 4.

update Employee set salary =20000
where EmployeeID =4 or EmployeeID = 1;

Use the WHERE condition to specify which records you want to update. It is possible to update one or more columns at a time.

The SQL DELETE TABLE Statement

DELETE statement is used to delete existing records in a table.

Syntax

DELETE FROM table_name WHERE condition;

Example

DELETE FROM Employee where EmployeeID=1

Note: Be careful when deleting records in a table. If you omit the WHERE clause, all records in the table will be deleted.

Below code delete all the details of Employee ( if you not use where clause)

delete from Employee 

The SQL ALTER TABLE Statement

ALTER TABLE statement is used to Add, Delete, or Modify columns in an existing table

Syntax of ADD Column

ALTER TABLE table_name
ADD column_name datatype;

Example of ADD Column
Below query is ADD State column in Employee table

ALTER TABLE Employee add State varchar (255);

Syntax Of DELETE Column

ALTER TABLE table_name DROP COLUMN column_name;

Example of DELETE Column

Below query is DELETE State column from Employee table.

ALTER TABLE Employee DROP COLUMN State;

Syntax of MODIFY Column

ALTER TABLE table_name ALTER COLUMN column_name datatype;

Example of MODIFY Column

Below query is change the Data type of the State column.

ALTER TABLE Employee ALTER COLUMN State varchar(100);

CHANGE Column Name

It is not possible to rename a column using the ALTER TABLE statement in SQL Server. Use sp_rename instead.

Syntax

sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

Example

Below query is change the column name from First_name to Name.

sp_RENAME 'EmployeeAA.FirstName' , 'Name', 'COLUMN'