Piyush Jain CJ SQL Server

NAME - PIYUSH JAIN
ID - 2116079
DATE - 25-02-2022
ANSWERS - GENC_LEARN_SQL_SERVER
ANS1-
CREATE TABLE orders (
ORD_NO NUMERIC(5) PRIMARY KEY,
PURCH_AMT DECIMAL(8,2),
ORD_DATE date,
CUSTOMER_ID NUMERIC(5),
SALESMAN_ID NUMERIC(5) FOREIGN KEY REFERENCES salesman(SALESMAN_ID)
GO
ANS2-
ALTER TABLE Supplier
ADD CONSTRAINT MY_CONTACT_CK CHECK (LEN(contact) = 10)
ANS3-
SELECT distinct d.deptname as "Worker Department", d.Location ,e.deptname as "Manager

Department "
FROM Department d
JOIN Department e on e.deptno = d.admrdept
and d.deptno != d.admrdept
order by d.deptname
Go
ANS4-
with cte as(
select lastname,empno,mgrno
from Employee
join Department
on workdept = deptno
select concat(b.lastname," works for ",a.lastname) "Hierarchy"
from cte a,cte b
where a.empno=b.mgrno and a.lastname <> b.lastname
order by b.lastname
Go
ANS5-
CREATE PROCEDURE EmployeesDept
@DeptNo char(3)
AS
select lastname as "Name" from Employee
where workdept =@DeptNo
GO
ANS6-
create procedure AvailableDepartments
AS
begin
select deptname as "Name" from Department
end
GO
ANS7-
select e.firstname,e.lastname,e.salary from employee e inner join department d on e.workdept =

d.deptno where d.location = 'New York' order by e.firstname
GO
ANS8-
create procedure EmployeeCount
(
@deptno char(3),
@total_employees int output
as
begin
select @total_employees = count(*)
from Employee
inner join department
on employee.workdept = department.deptno
where deptno = @deptno
end
Go
ANS9-
create procedure RetrieveEmpDetails (@EmpNo char(6), @Name varchar(15) out, @Job char (9) out,
@Sal decimal(9,2) out)
as
Begin
select @Name=lastname, @Job=job, @Sal=salary from Employee where empno = @EmpNo
End
GO
ANS10-
create procedure DisplayPlayer (@rolename varchar(30))
AS
begin
select p.PlayerName as playername from Player p join Player_role pr on p.roleid = pr.roleid where
rolename = @rolename
end
Go
ANS11-
create trigger claim_audits
on claims
for insert
as
begin
insert into claim_audit (customer_name, amount_of_claim, [action])
select C.first_name, I.amount_of_claim + 122500, 'Updated customer claimed amount'
from Inserted I
inner join Customer_Policy CP on CP.id = I.customer_policy_id
inner join Customer C on C.id = CP.customer_id;
end
Go
Ans12-
DECLARE @dept_name varchar(100), @emp_count int;
DECLARE emp_cursor CURSOR FOR
select d.deptname, count(*) from Employee e
join Department d on e.workdept = d.deptno
group by d.deptname
having count(*) > 1;
OPEN emp_cursor
FETCH NEXT FROM emp_cursor
INTO @dept_name, @emp_count
WHILE @@FETCH_STATUS = 0
BEGIN
print @dept_name + ' department has ' + cast(@emp_count as varchar(40)) + ' employees'
INTO @dept_name, @emp_count
END
CLOSE emp_cursor;
DEALLOCATE emp_cursor;
GO
ANS13-
create trigger afinsert_policy
on customer_policy
for insert
as
declare @id int;
declare @policy_start_date date;
declare @policy_renewal_date date;
declare @premium_amount float;
declare @policy_value float;
declare @audit_action varchar(100);
declare @policy_id int;
declare @customer_id int;
declare @agent_id int;
select @id = i.id from inserted i;
select @policy_renewal_date = i.policy_renewal_date from inserted i;
select @policy_start_date = i.policy_start_date from inserted i;
select @premium_amount = i.premium_amount from inserted i;
select @policy_value = i.policy_value from inserted i;
select @policy_id = i.policy_id from inserted i;
select @customer_id = i.customer_id from inserted i;
select @agent_id = i.agent_id from inserted i;
set @audit_action = "New Record Inserted";

insert into customer_policy_audit
(id,policy_start_date,policy_renewal_date,premium_amount,policy_value,policy_id,customer_id,agent_
id,audit_action)
values(@id,@policy_start_date,@policy_renewal_date,@premium_amount,@policy_value,@policy_id,
@customer_id,@agent_id,@audit_action)
Go
ANS14-
create trigger trigger_customer_af_update
on customer
FOR update
as
declare @customer_id int;
declare @customer_name varchar(30);
declare @action varchar(200);
select @customer_id = i.id from inserted i;
select @customer_name = i.first_name from inserted i;
set @action = 'Updated phone_number';
insert into customer_log_history(customer_id,customer_name,action)

values(@customer_id,@customer_name,@action);
Go
ANS15-
DECLARE @emp_no char(6), @fname varchar(12), @lname varchar(15), @salary decimal(9,2);
DECLARE emp_cursor CURSOR FOR
select empno, firstname, lastname, salary from Employee
where workdept='D11';
OPEN emp_cursor
INTO @emp_no, @fname, @lname, @salary
WHILE @@FETCH_STATUS = 0
BEGIN
print 'ID is:'+@emp_no;
print 'Name is:'+concat(@fname, ' ', @lname);
print 'Salary is:' +cast(@salary as varchar(30));
INTO @emp_no, @fname, @lname, @salary
END
CLOSE emp_cursor;
DEALLOCATE emp_cursor;
GO
ANS16-
CREATE PROCEDURE delete_status
@status_id int
AS
declare @counter int;
begin try
select @counter=count(*) from claims;
delete from claims where status_id = @status_id;
insert into status_error_log(error_msg) values
('child records existing in claims table');
end try
begin catch
insert into status_error_log(error_msg) values
('child records existing in claims table');
end catch
GO

Piyush Jain CJ SQL Server

Uploaded by

Copyright:

Available Formats

Piyush Jain CJ SQL Server

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Piyush Jain CJ SQL Server

Uploaded by

Copyright:

Available Formats

NAME - PIYUSH JAIN

CREATE TABLE orders (

ORD_NO NUMERIC(5) PRIMARY KEY,

SALESMAN_ID NUMERIC(5) FOREIGN KEY REFERENCES salesman(SALESMAN_ID)

ALTER TABLE Supplier

ADD CONSTRAINT MY_CONTACT_CK CHECK (LEN(contact) = 10)

SELECT distinct d.deptname as "Worker Department", d.Location ,e.deptname as "Manager

JOIN Department e on e.deptno = d.admrdept

and d.deptno != d.admrdept

with cte as(

select concat(b.lastname," works for ",a.lastname) "Hierarchy"

from cte a,cte b

where a.empno=b.mgrno and a.lastname <> b.lastname

CREATE PROCEDURE EmployeesDept

select lastname as "Name" from Employee

where workdept =@DeptNo

create procedure AvailableDepartments

select deptname as "Name" from Department

select e.firstname,e.lastname,e.salary from employee e inner join department d on e.workdept =

create procedure EmployeeCount

@total_employees int output

select @total_employees = count(*)

inner join department

where deptno = @deptno

select @Name=lastname, @Job=job, @Sal=salary from Employee where empno = @EmpNo

create procedure DisplayPlayer (@rolename varchar(30))

create trigger claim_audits

insert into claim_audit (customer_name, amount_of_claim, [action])

select C.first_name, I.amount_of_claim + 122500, 'Updated customer claimed amount'

inner join Customer_Policy CP on CP.id = I.customer_policy_id

inner join Customer C on C.id = CP.customer_id;

DECLARE @dept_name varchar(100), @emp_count int;

DECLARE emp_cursor CURSOR FOR

select d.deptname, count(*) from Employee e

join Department d on e.workdept = d.deptno

having count(*) > 1;

FETCH NEXT FROM emp_cursor

INTO @dept_name, @emp_count

FETCH NEXT FROM emp_cursor

INTO @dept_name, @emp_count

create trigger afinsert_policy

declare @id int;

declare @policy_start_date date;

declare @policy_renewal_date date;

declare @premium_amount float;

declare @policy_value float;

declare @audit_action varchar(100);

declare @policy_id int;

declare @customer_id int;

declare @agent_id int;

select @id = i.id from inserted i;

select @policy_renewal_date = i.policy_renewal_date from inserted i;

select @policy_start_date = i.policy_start_date from inserted i;

select @premium_amount = i.premium_amount from inserted i;

select @policy_value = i.policy_value from inserted i;

select @policy_id = i.policy_id from inserted i;

select @customer_id = i.customer_id from inserted i;

select @agent_id = i.agent_id from inserted i;