# Need some help with mySQL

Posted by Dude558 under Others on 5/21/2013 | Points: 10 | Views : 950 | 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
 0 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
 0 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 ENDDude558, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bubaibanerjee on: 5/22/2013 [Member] Starter | Points: 25
 0 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',40Dude558, if this helps please login to Mark As Answer. | Alert Moderator
Latest Posts