AnjaliKolambkar_1048_advancesql.sql

create database advsql
use advsql

1. Create a procedure to get orders by City, and add parameters for StartDate and
EndDate so users can filter by a specific timeframe.

create procedure order_by_city
as 
begin 
select 
c.city,
sum(o.sales) as total orders 
from employee e 
join orders o 
on e.employeeid = o.employeeid
group by c.city
end
---wanted to calculate total sales grouped by city, so joined orders with employee/customer table and grouped by city. then i planned to add date filters using parameters  

2. Create a procedure to find total employees per department, but only return
departments that have more than 10 employees (using a parameter).

create procedure sp_employee
as 
begin 
select 
departmentid,
sum(employee) over (partition by departmentid) as avg_employee
from employerdetails
where department (sum(employee) > 10 ) 
end 
---to calculate department-wise employee count using partition by, so that i can get totals per department. then i tried to filter departments having more than 10 employees.
3. Create a product search procedure that uses wildcard searching (e.g., searching
"Phone" finds "iPhone" and "Headphones") and returns an "Availability" status
based on stock levels.

create procedure product_search 
varchar(20) @search
as
begin
select * 
from products
where phone = '@iphone' or '@headphone'
end

--- wanted to implement search functionality where users can search for products like ‘phone’ and it should match similar names like iphone or headphones.
4. Create a procedure to adjust product prices by a percentage for a specific category,
ensuring that if any error occurs, the entire update is rolled back.



5. Create a procedure that returns the maximum salary per department, but also
includes the name of the specific employee who earns that salary.

create procedure sp_salary
as
begin
select *
from (
select 
*,
row_number() over (partition by employeeid order by salary desc) as ms
from employee
) 
end

---wanted to find highest salary employee per department using row_number window function

6. Create an INSERT/UPDATE trigger to ensure the Salary column is never negative,
even if 10 rows are updated at once.

create procedure insert_order
@id int,
@date date,
@cid int,
@sales decimal(10,2)
as
begin
insert into orders (orderid, orderdate, customerid, sales)
values (@id, @date, @cid, @sales);
end;

---wanted to prevent invalid data by ensuring salary cannot be negative, even during bulk insert or update operations.

7. Create an UPDATE trigger that logs the EmployeeID, OldSalary, NewSalary, the User
who changed it, and the exact timestamp into a separate SalaryAudit table.


8. Create a trigger that prevents a Product Price from exceeding 9999, but allows the
update to proceed for all other products in the same batch that are under the limit.

create trigger trg_exceeding
on products
instead of update
as
begin
if exists (
select 9999 
from inserted 
where price < 0
)
begin
print 'invalid prize';
end
---wanted to restrict product prices from exceeding a certain limit, but still allow valid rows in the same update to proceed.   

9. Create a trigger that detects if a DELETE statement affects more than 5 rows; if it
does, roll back the transaction to prevent accidental mass-deletion.

create trigger trg_delete_statement
on employee
after delete
as
begin
print '5 rows deleted'
end

---wanted to prevent accidental large deletions by checking number of affected rows and rolling back if it exceeds a limit.

10.Create a trigger at the Database level to prevent any user from dropping or altering
tables, effectively "locking" the schema.