SQL Server Database and Tables
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;
Still have some questions? Let's discuss.
CONTACT US