Contents

SQL Tutorial

Importance of SQL in Database Management

Description: SQL is crucial for database management as it provides a consistent and efficient way to interact with relational databases. It is used across various industries for managing large volumes of data, performing complex queries, and ensuring data integrity.

In SQL, basic concepts include the database, tables, rows, and columns. A database is a structured collection of data stored electronically, often organized into tables. Each table represents an entity (such as “Employees” or “Products”) and consists of rows and columns. Rows (also known as records) represent individual entries or data points, while columns represent the attributes or fields (such as “Name,” “Age,” or “Salary”) that define the structure of the data stored. SQL allows users to query and manipulate this data efficiently through commands like SELECT, INSERT, UPDATE, and DELETE. These core components provide the foundation for managing large volumes of structured data in relational databases.  (See More)

In SQL, data types refer to the specific kind of data that a column can hold in a database table. Data types define the nature of the values stored in each column, ensuring data consistency, integrity, and efficiency in the database. For example, a column storing a person’s age would use a numeric data type (INT), while a column for storing names would use a string data type (VARCHAR). Data types ensure that data is stored appropriately and can be manipulated in predictable ways, such as performing calculations on numeric types or sorting dates in chronological order.  (See More)

Basic SQL Operations

1.SELECT

Description : The SELECT statement is used to query the database and retrieve data from one or more tables. The result is stored in a result table, sometimes called the result set.

1.1 Basic SELECT Statement

Description : The most basic form of the SELECT statement retrieves all columns from a specified table.

				
					SELECT * FROM Customers;

				
			

This statement retrieves all columns and rows from the Customers table.

1.2 SELECT DISTINCT

Description: The SELECT DISTINCT statement is used to return only distinct (different) values.

				
					SELECT DISTINCT Country FROM Customers;

				
			

This statement retrieves a list of unique countries from the Customers table.

1.3 SELECT INTO

Description: The SELECT INTO statement copies data from one table into a new table.

				
					SELECT * INTO CustomersBackup FROM Customers;

				
			

This statement creates a new table CustomersBackup and copies all data from the Customers table into it.

1.4 SELECT TOP

Description: The SELECT TOP statement is used to specify the number of records to return.

				
					SELECT TOP 10 * FROM Customers;

				
			

This statement retrieves the first 10 records from the Customers table.

2.INSERT

Description : The INSERT statement is used to add new records to a table.

2.1 Basic INSERT Statement

Description: The basic INSERT statement specifies the table name and the columns to insert data into.

				
					INSERT INTO Customers (CustomerID, Name, Address)
VALUES (1, 'John Doe', '123 Elm Street');


				
			

This statement inserts a new record into the Customers table with the specified values.

2.2 INSERT INTO SELECT

Description: The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

				
					INSERT INTO CustomersBackup (CustomerID, Name, Address)
SELECT CustomerID, Name, Address FROM Customers;

				
			

This statement inserts data from the Customers table into the CustomersBackup table.

3. UPDATE

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

3.1 Basic UPDATE Statement

Description: The basic UPDATE statement specifies the table name, the column to update, and the new value.

				
					UPDATE Customers
SET Address = '456 Oak Street'
WHERE CustomerID = 1;


				
			
 

This statement updates the address of the customer with CustomerID 1 to ‘456 Oak Street’.

3.2 UPDATE with JOIN

Description: The UPDATE statement can also involve a JOIN to update records based on a relationship between tables.

				
					UPDATE Customers
SET Address = '789 Pine Street'
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate = '2023-01-01';



				
			
 

This statement updates the address of customers who placed orders on ‘2023-01-01’ to ‘789 Pine Street’.

4. DELETE

Description : The DELETE statement is used to delete records from a table. 

4.1 Basic DELETE Statement

Description: The basic DELETE statement specifies the table name and the condition for deleting records.

				
					DELETE FROM Customers
WHERE CustomerID = 1;


				
			
This statement deletes the customer with CustomerID 1 from the Customers table.
4.2 DELETE with JOIN

Description: The DELETE statement can also involve a JOIN to delete records based on a relationship between tables.

				
					DELETE Customers
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate = '2023-01-01';


				
			
This statement deletes customers who placed orders on ‘2023-01-01’ from the Customers table.

Functions

				
					CREATE INDEX idx_name
ON Customers(Name);

				
			
				
					CREATE VIEW NewYorkCustomers AS
SELECT * FROM Customers
WHERE Address LIKE '%New York%';

				
			
				
					BEGIN TRANSACTION;

UPDATE Customers
SET Address = '789 Pine Street'
WHERE CustomerID = 1;

COMMIT;


				
			
				
					CREATE PROCEDURE AddCustomer (
    @CustomerID int,
    @Name varchar(255),
    @Address varchar(255)
)
AS
BEGIN
    INSERT INTO Customers (CustomerID, Name, Address)
    VALUES (@CustomerID, @Name, @Address);
END;

				
			
				
					CREATE TRIGGER LogUpdate
ON Customers
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditLog (CustomerID, OldAddress, NewAddress, ChangeDate)
    SELECT CustomerID, deleted.Address, inserted.Address, GETDATE()
    FROM inserted
    JOIN deleted ON inserted.CustomerID = deleted.CustomerID;
END;

				
			
				
					-- Using a prepared statement to update a customer's name in a 'Customers' table

-- Prepare a parameterized SQL statement
PREPARE stmt FROM 'UPDATE Customers SET Name = ? WHERE CustomerID = ?';

-- Set parameters and execute the statement
SET @NewName = 'Jane Doe';
SET @CustomerID = 1;
EXECUTE stmt USING @NewName, @CustomerID;

-- Deallocate prepared statement
DEALLOCATE PREPARE stmt;

				
			

This code snippet uses a prepared statement with placeholders for parameters (?). This method separates the data inputs from the SQL command structure, effectively preventing SQL injection by ensuring that the input data cannot alter the structure of the SQL command.

				
					-- Creating an index to optimize queries on the 'CustomerID' column in the 'Orders' table
CREATE INDEX idx_CustomerID ON Orders(CustomerID);

-- Example query that benefits from the newly created index
SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = 12345;

				
			

Explanation:

  • The CREATE INDEX statement adds an index named idx_CustomerID on the CustomerID column of the Orders table.
  • This index helps the database engine quickly locate and retrieve all orders related to a specific customer, significantly improving the speed of queries filtering on the CustomerID column.
				
					-- Creating a full backup of the 'YourDatabase' database to a disk file
BACKUP DATABASE YourDatabase TO DISK = 'D:\Backups\YourDatabase.bak';

				
			

Explanation:

  • This command creates a full backup of YourDatabase.
  • The backup is stored as a file named YourDatabase.bak in the D:\Backups directory on the disk.
  • This backup file contains all data and can be used to restore the database to the point in time when the backup was taken, ensuring that data can be recovered in case of a failure or other data loss incidents.