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;
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';
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 namedidx_CustomerID
on theCustomerID
column of theOrders
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 theD:\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.