Tag Archives: SQL

Day 92 – 100 – What is SQL and what is it used for?

SQL stands for Structured Query Language, and it is a programming language used to manage relational databases. SQL is used to create, modify, and query databases, as well as to perform various tasks such as data manipulation, data definition, and data control.

Day 93 – What is the difference between SQL and NoSQL databases?

SQL databases are relational databases that store data in tables with predefined schema, while NoSQL databases are non-relational databases that store data in flexible document formats. SQL databases are better suited for structured data that requires transactions, while NoSQL databases are better suited for unstructured data that requires scalability and flexibility.

Day 94 – What is a primary key and how is it used in SQL?

A primary key is a unique identifier for a record in a table, and it is used to enforce data integrity and facilitate data retrieval. A primary key is defined as a column or a combination of columns that uniquely identifies each row in a table.

Day 95 – What is a foreign key and how is it used in SQL?

A foreign key is a column or a combination of columns in a table that references the primary key of another table. A foreign key is used to establish relationships between tables and enforce referential integrity.

Day 96 – What is a stored procedure and how is it used in SQL?

A stored procedure is a precompiled set of SQL statements that are stored in a database and can be executed repeatedly. Stored procedures can be used to improve performance, enhance security, and simplify database management.

Day 97 – What is a trigger and how is it used in SQL?

A trigger is a database object that is executed automatically in response to certain events, such as data modification, insertion, or deletion. Triggers can be used to enforce business rules, audit data changes, and maintain data consistency.

Day 98 – What is a view and how is it used in SQL?

A view is a virtual table that is based on the result of a SQL query, and it is used to simplify complex queries and hide the underlying complexity of the database schema. Views can be used to retrieve data from multiple tables, filter and aggregate data, and enforce security policies.

Day 99 – What is a view and how is it used in SQL?

A view is a virtual table that is based on the result of a SQL query, and it is used to simplify complex queries and hide the underlying complexity of the database schema. Views can be used to retrieve data from multiple tables, filter and aggregate data, and enforce security policies.

Day 100 – What is normalization and why is it important in SQL?

Normalization is the process of organizing data in a database in a way that reduces redundancy and dependency, and ensures data consistency and integrity. Normalization is important in SQL because it helps to eliminate data inconsistencies, reduces data redundancy, and facilitates data retrieval and maintenance.

Day 91 – Cursors

DECLARE @OrderID INT
DECLARE @ProductID INT
DECLARE @Quantity INT

DECLARE order_cursor CURSOR FOR
SELECT OrderID, ProductID, Quantity FROM OrderDetails

OPEN order_cursor

FETCH NEXT FROM order_cursor INTO @OrderID, @ProductID, @Quantity

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Quantity > 10
    BEGIN
        PRINT 'Order ID: ' + CAST(@OrderID AS VARCHAR(10)) + ', Product ID: ' + CAST(@ProductID AS VARCHAR(10)) + ', Quantity: ' + CAST(@Quantity AS VARCHAR(10))
    END
    FETCH NEXT FROM order_cursor INTO @OrderID, @ProductID, @Quantity
END

CLOSE order_cursor
DEALLOCATE order_cursor

In this example, a cursor named order_cursor is used to retrieve the OrderID, `

Day 90 – User-Defined Functions

CREATE FUNCTION fnGetProductPrice (@ProductID INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @Price DECIMAL(18,2)
    SELECT @Price = Price * (1 - Discount) FROM Products WHERE ProductID = @ProductID
    RETURN @Price
END

SELECT ProductName, fnGetProductPrice(ProductID) AS DiscountedPrice
FROM Products

In this example, a user-defined function named fnGetProductPrice is created to retrieve the discounted price of a product based on the product ID. The function is then used in a query to retrieve the discounted price for each product.

Day 89 – Views

CREATE VIEW vwProductSales
AS
SELECT Products.ProductName, SUM(OrderDetails.Quantity) AS TotalSales
FROM Products
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
GROUP BY Products.ProductName
HAVING SUM(OrderDetails.Quantity) > 1000

SELECT * FROM vwProductSales

In this example, a view named vwProductSales is created to retrieve the total sales for each product and filter the results to only include products with total sales greater than 1000. The view is then queried using a standard SELECT statement.

Day 88 – Triggers

CREATE TRIGGER trUpdateProductInventory
ON ProductInventory
AFTER UPDATE
AS
BEGIN
    UPDATE Products
    SET UnitsInStock = UnitsInStock + (INSERTED.Quantity - DELETED.Quantity)
    FROM Products
    INNER JOIN INSERTED ON Products.ProductID = INSERTED.ProductID
    INNER JOIN DELETED ON Products.ProductID = DELETED.ProductID
END

In this example, a trigger named trUpdateProductInventory is created to update the UnitsInStock column in the Products table whenever the ProductInventory table is updated. The INSERTED and DELETED tables are used to retrieve the old and new values of the Quantity column in the ProductInventory table.

Day 87 – Stored Procedures

CREATE PROCEDURE spGetSalesByRegion (@RegionName NVARCHAR(50))
AS
BEGIN
    SELECT SUM(SalesAmount) AS TotalSales, YEAR(SalesDate) AS SalesYear
    FROM Sales
    WHERE RegionName = @RegionName
    GROUP BY YEAR(SalesDate)
END

EXEC spGetSalesByRegion 'North America'

In this example, a stored procedure named spGetSalesByRegion is created to retrieve the total sales for a specified region and group the results by year. The procedure accepts a parameter @RegionName which is used in the WHERE clause to filter the data.

Day 86 – Indexes

These are database objects that are used to improve the performance of queries by providing faster access to data. Indexes can be created on one or more columns of a table, and can be used to speed up searches, sorts, and joins.

CREATE INDEX idxCustomerName ON Customers (CustomerName)

SELECT * FROM Customers WHERE CustomerName = 'ABC Company'

In this example, an index named idxCustomerName is created on the CustomerName column of the Customers table. The index is then used to speed up a query that retrieves all customers with the name “ABC Company”.

Day 85 – Transactions

These are a series of SQL statements that are executed as a single, atomic operation. Transactions are used to ensure data integrity by ensuring that either all of the SQL statements in the transaction are executed, or none of them are.

BEGIN TRANSACTION
UPDATE Customers SET CreditLimit = CreditLimit + 5000 WHERE CustomerID = 1
INSERT INTO Orders (CustomerID, OrderDate, OrderAmount) VALUES (1, GETDATE(), 1000)
COMMIT TRANSACTION

In this example, a transaction is used to update the credit limit of a customer and insert a new order record into the Orders table. The BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION statements are used to control the transaction.

Day 84 – Cursors

These are database objects that allow you to traverse through the result set of a query one row at a time. Cursors can be useful when you need to perform complex processing on each row of a result set.

DECLARE @CustomerID INT
DECLARE @CustomerName VARCHAR(50)

DECLARE customer_cursor CURSOR FOR
SELECT CustomerID, CustomerName FROM Customers

OPEN customer_cursor

FETCH NEXT FROM customer_cursor INTO @CustomerID, @CustomerName

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Customer ID: ' + CAST(@CustomerID AS VARCHAR(10)) + ', Name: ' + @CustomerName
    FETCH NEXT FROM customer_cursor INTO @CustomerID, @CustomerName
END

CLOSE customer_cursor
DEALLOCATE customer_cursor

In this example, a cursor named customer_cursor is used to retrieve the CustomerID and CustomerName from the Customers table. The cursor is then used in a loop to print out the ID and name of each customer.

Day 83 – User-Defined Functions

These are custom functions that can be created to perform specific tasks in SQL. User-defined functions can be used to simplify complex queries or to perform calculations on data.

CREATE FUNCTION fnGetOrderTotal (@OrderID INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @Total DECIMAL(18,2)
    SELECT @Total = SUM(OrderAmount) FROM OrderDetails WHERE OrderID = @OrderID
    RETURN @Total
END

SELECT OrderID, fnGetOrderTotal(OrderID) AS OrderTotal FROM Orders

In this example, a user-defined function named fnGetOrderTotal is created to retrieve the total amount of an order based on the order ID. The function is then used in a query to retrieve the total amount for each order.