Scribd
Upload
5 views9 pages

SQL Queries for Product Management

Uploaded by

B-63 Arun Kumar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views9 pages

SQL Queries for Product Management

Uploaded by

B-63 Arun Kumar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SELECT stock_quantity,quantity_sold, product_name,(t1.stock_quantity - t2.

quantity_sold ) as
Remaining

FROM tbl_Products t1 ,tbl_Sales t2

WHERE t2.product_id = t1.product_id;

--SALE FOR DATE------------------------------------------------------------------------------------------

SELECT sale_date, quantity_sold,product_name,(t1.stock_quantity - t2.quantity_sold )as


Remaining

FROM tbl_Products t1 ,tbl_Sales t2

WHERE t2.product_id = t1.product_id AND sale_date ='2024-03-21'

SELECT sale_date, quantity_sold,product_name,(t1.stock_quantity - t2.quantity_sold )as


Remaining

FROM tbl_Products t1 ,tbl_Sales t2

WHERE t2.product_id = t1.product_id AND sale_date BETWEEN '2024-03-18' AND '2024-03-


21'

-----------------------------------------------------------------------------------------------------------------

join 2 tables for no of sold

SELECT t1.quantity_sold, t2.product_name

FROM tbl_Sales t1

JOIN tbl_Products t2 ON t1.product_id = t2.product_id

WHERE t1.quantity_sold = 0;

--------------------------------------------------------------------------------------------------------------

--------------function----------------------------------------------------------------------------------------

create FUNCTION CalculateTotalSalePrice

@UnitPrice DECIMAL(10, 2),

@Quantity INT
)

RETURNS DECIMAL(10, 2)

AS BEGIN

DECLARE @TotalPrice DECIMAL(10, 2);

SET @TotalPrice = @UnitPrice * @Quantity;

RETURN @TotalPrice;

END;

DECLARE @UnitPrice DECIMAL(10, 2) = 10.00;

DECLARE @Quantity INT = 5;

SELECT [Link](55,5) AS TotalPrice

---------------------------------------------------------------------------------------------------------------

---------trigger----------------------------------

create trigger choco

on tbl_Products

for

insert,update ,delete

as

print 'you can not insert,update and delete this table i'

rollback;

------------------index-------------------------------------------------

CREATE INDEX IX_ProductName

ON tbl_Products (product_name);

ALTER INDEX IX_ProductName ON tbl_Products REBUILD;

ALTER INDEX IX_ProductName ON tbl_Products REORGANIZE;

----------------------------------------------------------------------------------------------------------------
------store procedure----------------

Alter Procedure USP_LoginCheck

@UserName varchar(50)

As

Begin

select * from tbl_Products where product_id=@UserName

End

Execute USP_LoginCheck '11'

Alter Procedure USP_Insert_Products

@product_id Int,

@product_name varchar(500),

@stock_quantity Int

As

Begin

if (@product_id >= 5000)

Begin

Insert into tbl_Products values(@product_id,@product_name,@stock_quantity)

End

Else

Begin
Select 'Invalid Prod ID' As Output

End

End

Exec USP_Insert_Products 5005,'Rin Liq',150

--------------------------trigger--------------------

create trigger tbl_products1 on tbl_products

after insert

as

begin

declare @product_id int

declare @product_name varchar

declare @stock_quantity int

select @product_id = productlist.product_id from inserted productlist;

select @product_name =productlist.product_name from inserted productlist;

select @stock_quantity = productlist.stock_quantity from inserted productlist;

insert into tbl_products1 (product_id,product_name,stock_quantity)

values (@product_id,@product_name,@stock_quantity)

end

go
Tables: Insert, Delete, update, truncate, drop, select
TABLES
SELECT
SELECT * FROM Table_Name;

 Count Distinct
SELECT COUNT(DISTINCT Column_Name) FROM Table_Name;

 WHERE Clause- used to filter records.


Syntax: SELECT Column1, Column2,…

From table_name

Where condition;

Operators
ORDER BY: sort the result set
Syntax: SELECT Column1, Column2, …

From table_name

Order by Column1, Column2, … asc|desc ;

AND Operator
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

AND vs OR

The AND operator displays a record if all the conditions are TRUE.

The OR operator displays a record if any of the conditions are TRUE.

OR Operator
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Operator
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

NOT LIKE
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';

NOT BETWEEN
SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;

NOT IN
SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');

NOT Greater Than


SELECT * FROM Customers
WHERE NOT CustomerID > 50;

INSERT INTO
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Aggregate Functions = calculation on a set of values, and returns a single value.


often used with the GROUP BY clause

 MIN() - returns the smallest value within the selected column


 MAX() - returns the largest value within the selected column
 COUNT() - returns the number of rows in a set
 SUM() - returns the total sum of a numerical column
 AVG() - returns the average value of a numerical column

Aggregate functions ignore null values (except for COUNT()).


Views
TRIGGER

Common questions

Powered by AI

The NOT IN operator is beneficial when excluding specific values from a result set in a SQL query. For example, if a business wants to retrieve customer records for all cities except 'Paris' and 'London', using NOT IN ('Paris', 'London') achieves this by preventing those rows from being included. This operator effectively focuses analysis or reporting efforts on locations of interest, impacting data relevance positively .

SQL triggers like 'tbl_products1' automate actions in response to specific events on a table, such as after insertions. This trigger captures inserted data and replicates it into another table, in this case, moving data from 'tbl_Products' to 'tbl_products1', ensuring redundant data storage for recovery or analysis purposes. Benefits include automatic enforcement of business rules, data consistency, and reduction in manual intervention .

The purpose of the CalculateTotalSalePrice function is to compute the total sales price by multiplying the unit price of a product by the quantity sold. It takes two arguments: @UnitPrice of decimal type and @Quantity of integer type, then returns the product as a decimal value. This function can be used to perform price calculations consistently in queries .

Using the INSERT INTO statement in SQL to modify a database allows for adding new records to a table. It aligns data within columns according to the table structure, increasing data volume. However, excessive or improper use without constraints or checks can lead to data integrity issues or bloating. Ensuring unique and meaningful data entries is crucial to maintain database quality .

In SQL queries, the AND operator is used when all specified conditions must be true for a record to be selected, while the OR operator is used when any one of the conditions can be true for a record to be selected. For example, AND is suitable for scenarios like filtering orders for a specific customer in a specific month, requiring both conditions true. OR is appropriate for filtering data like retrieving customers from either 'New York' or 'San Francisco', where meeting either location condition suffices .

The WHERE clause in SQL enhances query utility by allowing users to filter records based on specified conditions. This makes it possible to retrieve only relevant data from a database, thus narrowing down results to meet specific criteria and increasing the efficiency and precision of data retrieval operations .

The USP_Insert_Products stored procedure would reject a product insertion into the tbl_Products table if the product ID provided is less than 5000. In such cases, the procedure outputs 'Invalid Prod ID' instead of performing the insertion. This condition ensures that only products with an ID of 5000 or greater are added to the table .

The SQL trigger named 'choco' is designed to prevent data manipulation (insert, update, delete) on the 'tbl_Products' table by printing a message that these operations cannot be performed and then rolling back any attempted transaction. This effectively nullifies any changes to the table from such operations .

Creating and maintaining an index on the 'product_name' column in the tbl_Products table enhances query performance, particularly for search and retrieval operations involving product names. It speeds up data retrieval by allowing the SQL engine to quickly locate and access the rows associated with matched product names. Regular maintenance through rebuilds and reorganizations keeps the index optimized to further ensure that query performance remains high .

Aggregate functions in SQL, such as COUNT(), SUM(), MIN(), and MAX(), operate on a set of values to perform calculations and return a single value. COUNT() returns the number of rows, SUM() calculates the total of a numerical collection, MIN() gives the smallest value, and MAX() provides the largest value within a specified column. They are often used with the GROUP BY clause to perform calculations across groups of data .

You might also like