SQL Server Database and Tables

Level : Beginner
Mentor: Shailendra Chauhan
Duration : 00:04:00

CREATE TABLE using T-SQL command

This command is used to create a new table in a SQL Server database.

Example

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10,2)
);

DROP TABLE using T-SQL command

This command is used to delete a table from a SQL Server database.

Example

DROP TABLE Employees;

INSERT statement in SQL

This statement is used to insert new records into an existing table.

Example

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (1, 'John', 'Doe', 50000);

INSERT with SELECT statement

This statement allows you to insert data into a table from the result of a SELECT query.

Example

INSERT INTO NewEmployees (FirstName, LastName, Salary)
SELECT FirstName, LastName, Salary FROM OldEmployees WHERE Salary > 60000;

UPDATE statement in SQL Server

This statement is used to modify existing records in a table.

Example

UPDATE Employees
SET Salary = Salary * 1.1
WHERE LastName = 'Doe';

SQL Server DELETE Data

This statement is used to delete specific records from a table.

Example

DELETE FROM Employees WHERE EmployeeID = 2;

SQL Server DELETE Top Statement

This statement is used to delete a specified number of top records from a table.

Example

DELETE TOP(5) FROM Employees WHERE Salary < 40000;

DELETE using TOP keyword

This is an alternative way to delete a specific number of records from a table using the TOP keyword.

Example

DELETE TOP 3 FROM Employees WHERE LastName = 'Smith';

DELETE using TOP Percent Keyword

This allows you to delete a specified percentage of records from a table using the TOP keyword.

Example

DELETE TOP 10 PERCENT FROM Employees WHERE Salary < 50000;

SQL Server Add New Column

This command is used to add a new column to an existing table.

Example

ALTER TABLE Employees ADD Email VARCHAR(100);

SQL Server Delete Column

This command is used to delete a column from an existing table.

Example

ALTER TABLE Employees DROP COLUMN Email;

SQL Server Modify Column Data Type

This command is used to change the data type of an existing column in a table.

Example

ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);

Add Constraint on the Column

This is used to add a constraint, such as a foreign key or check constraint, to a column in a table.

Example

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Drop Constraint on the Column

This is used to remove a previously added constraint from a column.

Example

ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerID;

Add a NOT NULL Constraint to a NULLABLE Column

This is used to enforce that a column, which originally allowed NULL values, must now have non-null values.

Example

ALTER TABLE Employees
ALTER COLUMN FirstName VARCHAR(50) NOT NULL;

Create view Using T-SQL Query

This command is used to create a virtual table (view) based on the result of a SQL query.

Example

CREATE VIEW HighSalaryEmployees AS
SELECT FirstName, LastName
FROM Employees
WHERE Salary > 60000;

Rename views in SQL Server

This command is used to change the name of an existing view.

Example

EXEC sp_rename 'OldViewName', 'NewViewName';

Update views in SQL Server

This is not a direct operation; you update the underlying tables, and the view reflects those changes.

Example

-- Update the salary of an employee
UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 123;

List views in SQL Server

This is typically done by querying the system catalog or using a GUI tool to see the list of views in a database.

Example

SELECT name
FROM sys.views;

Drop Views in SQL Server

This command is used to delete an existing view from the database.

Example

DROP VIEW HighSalaryEmployees;
Self-paced Membership
  • 22+ Video Courses
  • 800+ Hands-On Labs
  • 400+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A Courses
  • 10+ Real-world Projects
  • Career Coaching Sessions
  • Email Support
Upto 60% OFF
Know More
Still have some questions? Let's discuss.
CONTACT US
Accept cookies & close this