DATABASE CASE | TRANSACTION | HAVING

 SELECT City, SUM(Salary) as TotalSalary

FROM Employee

GROUP BY City

HAVING City = 'MUMBAI'


=======================================================================================


CREATE TABLE Product

(

ProductID INT PRIMARY KEY,

Name VARCHAR(40),

Price INT,

Quantity INT

)


INSERT INTO Product VALUES(101, 'Product-1', 100, 10)

INSERT INTO Product VALUES(102, 'Product-2', 200, 15)

INSERT INTO Product VALUES(103, 'Product-3', 300, 20)

INSERT INTO Product VALUES(104, 'Product-4', 400, 25)



Transaction Statement


BEGIN TRANSACTION

INSERT INTO Product VALUES(105,'Product-5',500, 30)

UPDATE Product SET Price =350 WHERE ProductID = 103

DELETE FROM Product WHERE ProductID = 103

COMMIT TRANSACTION


rollback 


=========================================================================================


SELECT Name, Age,

 CASE


 WHEN Age < 18 THEN 'Minor'

 WHEN Age >= 18 AND Age < 60 THEN 'Adult'

 ELSE 'Senior'


 END AS AgeGroup


FROM Students;






Comments

Post a Comment

Popular posts from this blog

Python Introduction

PYTHON PROGRAMING https://thonny.org/ Python supports multiple programming paradigms, including object-oriented, procedural, and functional programming . It provides a rich standard library and a large ecosystem of third-party packages, which helps developers to build applications faster and more efficiently. Python is platform-independent, meaning Python programs can run on Windows, Linux, and macOS without modification. Due to its flexibility and powerful features, Python is widely used in fields such as: ·          Web Development ·          Data Science & Data Analytics ·          Artificial Intelligence & Machine Learning ·          Automation & Scripting ·          Desktop Application Development · ...
Read more

STORE PROCEDURE BASIC PROGRAMMING - PART 1

  1. Create a Stored Procedure CREATE PROCEDURE sp_GreetUser AS BEGIN PRINT 'Welcome to SQL Server Stored Procedure Programming' ; END ; ================================================================================ Stored Procedure with Parameters Create Procedure CREATE PROCEDURE sp_AddNumbers @Num1 INT , @Num2 INT AS BEGIN DECLARE @ Result INT ; SET @ Result = @Num1 + @Num2; PRINT 'Addition Result = ' + CAST (@ Result AS VARCHAR ( 10 )); END ; Execute Procedure EXEC sp_AddNumbers 10 , 20 ; ========================================================================== Stored Procedure with Parameters Create Procedure CREATE PROCEDURE sp_AddNumbers @Num1 INT , @Num2 INT AS BEGIN DECLARE @ Result INT ; SET @ Result = @Num1 + @Num2; PRINT 'Addition Result = ' + CAST (@ Result AS VARCHAR ( 10 )); END ; Execute Procedure EXEC sp_AddNumbers 10 , 20 ; ======================...
Read more

STORE PROCEDURE ADVANCE CONCEPT - PART 2

  List All Stored Procedures SELECT name FROM sys.procedures; ================================================================================ Stored Procedure Calling Another Stored Procedure with Parameters in SQL Server You cannot create a stored procedure inside another stored procedure, but you can call one stored procedure from another and pass parameters . Step 1: Create Table CREATE TABLE Employee ( EmpID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , EmpName VARCHAR ( 100 ), Salary DECIMAL ( 10 , 2 ) ); Step 2: Insert Sample Data INSERT INTO Employee(EmpName, Salary) VALUES ( 'Nilesh Gupta' , 50000 ), ( 'Rahul Sharma' , 45000 ), ( 'Priya Patel' , 55000 ); ====================================================================================== Procedure 1: Get Employee by ID CREATE PROCEDURE sp_GetEmployeeByID ( @EmpID INT ) AS BEGIN SELECT * FROM Employee WHERE EmpID = @EmpID; END ; GO Execute EXEC sp_GetEmployeeByI...
Read more