hi-Urgent query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

hi-Urgent query

Author

Topic

rohitT
Starting Member

2 Posts

Posted - 2008-05-20 : 05:41:38

Hi Folks,

Appreciable the fast responses of the below query,which use the below 3 tables,

1. Principal

CREATE TABLE `principal` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(250) default NULL,
`surname` varchar(250) default NULL,
`email` varchar(250) default NULL,
`propfk` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

2. Property

CREATE TABLE `property` (
`id` int(11) NOT NULL auto_increment,
`street_name` varchar(250) default NULL,
`street_no` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

3. Emaillog

CREATE TABLE `emaillog` (
`id` int(11) NOT NULL auto_increment,
`principalfk` int(11) default NULL,
`datesent` datetime default NULL,
`msg` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The foreign key of principal is “propfk”, the foreign key for emaillog is “principalfk”

3)Return a list of all principals, list the following fields…

Name , surname , email , street_name , street_no
----Return the same fields as above except add another two fields…

3.1.) A count of the number of emails sent by the principal in the year 2008.

Plese treat this as Very urgent,

Thanks..
Rohit.T

3.2.)The overall percentage of emails sent by the principals. So for example if a principal sent 2 emails out of 10 the percentage of emails sent would be 20 percent.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 05:45:49

Are you using MySQL?

E 12°55'05.25"
N 56°04'39.16"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-20 : 05:47:15

Is this homework or an exam?

rohitT
Starting Member

2 Posts

Posted - 2008-05-20 : 05:53:41

yes i m using mysql..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 06:40:07

[code]1. SELECT p.Name , p.surname , p.email , pr.street_name ,pr.street_no
FROM Pricipal p
INNER JOIN Property pr
ON pr.id=p.propfk

2.SELECT p.id,COUNT(el.datesent)
FROM Pricipal p
INNER JOIN emaillog el
ON el.principalfk=p.id
WHERE YEAR(el.datesent)=2008
GROUP BY p.id

3.SELECT p.id,COUNT(el.datesent)*100.0/COUNT(el.id)
FROM Pricipal p
INNER JOIN emaillog el
ON el.principalfk=p.id[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources