SQL-Queries

Create Table: To create a new table in SQL Server, use the following syntax:

CREATE TABLE TableName (
    Id INT,
    Name NVARCHAR(500),
    Age INT
);

Create Table with Primary Key: To create a table with a primary key, use:

CREATE TABLE TableName (
    Id INT PRIMARY KEY,
    Name NVARCHAR(500),
    Age INT
);

Create Table with Primary Key and Identity Column: For an auto-incrementing identity column:

CREATE TABLE TableName (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(500),
    Age INT
);

Insert Values into SQL Server Table: To add data to a table:
```
INSERT INTO TableName (Name, Age)
VALUES ('Max', 30);
```

Insert Multiple Rows in a Single Query: Insert multiple records at once:

INSERT INTO TableName (Name, Age)
VALUES ('Max', 30), ('John', 28), ('Jack', 31);

Update Query:

Update a single record:

UPDATE TableName
SET Name = 'Max Payne'
WHERE Id = 1;

Update all records:
```
UPDATE TableName
SET Age = 31;
```

Delete Query:

Delete a single record:
```
DELETE FROM TableName
WHERE Id = 1;
```
Delete all records:
```
DELETE FROM TableName;
```

Select:

Select all columns from a table:
```
SELECT * FROM TableName;
```

Select specific columns:

SELECT ColumnName1, ColumnName2
FROM TableName;

Create View: A view is a virtual table based on a SQL statement:
```
CREATE VIEW view_name AS
SELECT Id, Name, Age
FROM TableName;
```
Usage: SELECT * FROM view_name;

Create Stored Procedure:

CREATE PROCEDURE getInfoFromTable AS
SELECT * FROM TableName;

Get All Column Names from Table:

Retrieve column names:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName';

Get column names with data types:

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName';

Search Column Name from Database:

Find matching column names:

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id;

SELECT Query:
- Retrieve all rows and columns from the Product table:
```
SELECT *
FROM Production.Product;
```

INSERT INTO Query:

Add a new product to the Product table:

INSERT INTO Production.Product (Name, ListPrice)
VALUES ('New Product', 99.99);

UPDATE Query:

Update the price of an existing product:

UPDATE Production.Product
SET ListPrice = 109.99
WHERE ProductID = 123;

DELETE Query:

Remove a product from the Product table:

DELETE FROM Production.Product
WHERE ProductID = 456;

CREATE TABLE Statement:

Create a new table named Customers:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

ALTER TABLE Statement:
- Add a new column to the Customers table:
```
ALTER TABLE Customers
ADD Email NVARCHAR(100);
```

CREATE VIEW Statement:

Create a view that displays customer names:

CREATE VIEW vw_CustomerNames AS
SELECT CustomerID, CONCAT(FirstName, ' ', LastName) AS FullName
FROM Customers;

Stored Procedure:

Create a stored procedure to retrieve order details:

CREATE PROCEDURE GetOrderDetails
@OrderID INT
AS
BEGIN
    SELECT * FROM Sales.OrderDetail WHERE OrderID = @OrderID;
END;

JOIN Query:

Retrieve product details along with order quantities:

SELECT p.Name, sod.OrderQty
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID;

DISTINCT Query:
- Get unique job titles from the Employee table:
```
SELECT DISTINCT JobTitle
FROM HumanResources.Employee;
```

SELECT INTO Query:

Create a temporary table with bicycle data:

USE tempdb;
IF OBJECT_ID(N'#Bicycles', N'U') IS NOT NULL
    DROP TABLE #Bicycles;
SELECT *
INTO #Bicycles
FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 1;

(1) SELECT examples (Transact-SQL) - SQL Server | Microsoft Learn. https://learn.microsoft.com/en-us/sql/t-sql/queries/select-examples-transact-sql?view=sql-server-ver16. (2) Queries - SQL Server | Microsoft Learn. https://learn.microsoft.com/en-us/sql/t-sql/queries/queries?view=sql-server-ver16. (3) SQL Commands: The Complete List (w/ Examples) – Dataquest. https://www.dataquest.io/blog/sql-commands/. (4) 20 Basic SQL Query Examples for Beginners | LearnSQL.com. https://learnsql.com/blog/basic-sql-query-examples/.

Jagadeesh-tamizh/SQL-Queries

SQL-Queries