Need some help with mySQL

Posted by Dude558 under Others on 5/21/2013 | Points: 10 | Views : 2313 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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!! ********

Reply | Reply with Attachment

Alert Moderator

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=@GapYears365 select @WithoutDecimalDays= floor(@NoOfDays) RETURN @WithoutDecimalDays END Dude558, if this helps please login to Mark As Answer*. \| Alert Moderator

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

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',40 Dude558, if this helps please login to Mark As Answer. \| Alert Moderator

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

Latest Posts