Need some help with mySQL

Posted by Dude558 under Others on 5/21/2013 | Points: 10 | Views : 972 | Status : [Member] | Replies : 3
1. Create a function named age that receives a parameter that is a person's date of birth. The function calculates the person's age by subtracting the date of birth from the current date. Format the age in years by dividing the result, which will be in days, by 365.25. Then remove the decimal portion without rounding the age up.

2. Create a trigger named dept_trg that will be used to audit updates to the dept table. If someone updates a column in the table, insert their userid (user() ), the current date (curdate() ), and the old and new values for the columns dname and loc into a new table you create named dept_audit. Use the following UPDATE statement to test your trigger:

update dept
set dname = 'EDUCATION', loc = 'OMAHA'
where deptno = 20;

THEN

select * from dept_audit;

********** Do you guys think you could help? If you need any payment I can pay in Bitcoin!!
Let me know what you think!! ********




Responses

Posted by: Learningtorise on: 5/22/2013 [Member] Starter | Points: 25

Up
0
Down
Have you tried Google ?

Code to calculate Age can be found in Google easily...

http://hashtagakash.wordpress.com/

Dude558, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bubaibanerjee on: 5/22/2013 [Member] Starter | Points: 25

Up
0
Down
Answer for question no 1:
CREATE FUNCTION [dbo].[ufn_Age]
(
@DateOfBirth DATETIME
)
RETURNS INT
AS
BEGIN

DECLARE @Years INT,@GapYears INT,@NoOfDays DECIMAL(13,2),@WithoutDecimalDays INT

SELECT @Years= YEAR(@DateOfBirth)

SELECT @GapYears= YEAR(GETDATE())-@Years

SELECT @NoOfDays=@GapYears*365

select @WithoutDecimalDays= floor(@NoOfDays)

RETURN @WithoutDecimalDays

END

Dude558, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bubaibanerjee on: 5/22/2013 [Member] Starter | Points: 25

Up
0
Down
Answer for Question No 2:
CREATE TABLE [dbo].[dept]
(
[deptno] [int] NULL,
[dname] [varchar](50) NULL,
[location] [varchar](50) NULL
)

INSERT INTO dept(deptno,dname,location ) VALUES(20,'Mechanical','Nilanga')
INSERT INTO dept(deptno,dname,location ) VALUES(30,'Electronics','Mumbai')
INSERT INTO dept(deptno,dname,location ) VALUES(40,'Computer','Akola')
INSERT INTO dept(deptno,dname,location ) VALUES(50,'Civil','Nagpur')


CREATE TABLE [dbo].[dept_audit]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[DeptId] [int] NULL,
[ColumnName] [varchar](50) NULL,
[OldValue] [varchar](50) NULL,
[NewValue] [varchar](50) NULL
)

CREATE PROCEDURE sp_dept_Update
(
@dname VARCHAR(50),
@loc VARCHAR(50),
@deptno INT
)
AS
BEGIN
DECLARE @deptname VARCHAR(50),@location VARCHAR(50)

SELECT @deptno=deptno,@deptname=dname,@location=location FROM dept WHERE deptno=@deptno

UPDATE dept SET dname = @dname, location = @loc
WHERE deptno =@deptno

IF(@deptname<>@dname)
BEGIN
INSERT INTO dept_audit(DeptId,ColumnName,OldValue,NewValue) values(@deptno,'dname',@deptname,@dname)
END

IF(@location<>@loc)
BEGIN
INSERT INTO dept_audit(DeptId,ColumnName,OldValue,NewValue) values(@deptno,'location',@location,@loc)
END
END



SELECT deptno,dname,location FROM dept
exec sp_dept_Update 'Computer2','Akola2',40

Dude558, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response