A simple query...plz help - 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

General SQL Server Forums

New to SQL Server Programming

A simple query...plz help

Author

Topic

ryan123
Starting Member

1 Post

Posted - 2007-09-04 : 12:10:13

Hi All,

The following table is entitled "class".

ID Number Name Join Date Sex
101 Jason 01/02/1996 M
117 Robert 06/25/1999 M
113 Doug 08/12/1999 M
161 Marian 07/30/1998 F

Please let me know the SQL queries for the following questions for the above table:

1) Write a Select statement that selects the persons whose Type is "M" and who have joined in the last four years and place the result in an array. Today's date is 12/31/1999.

2) Write a Select statement that selects the persons who do not have unique ID Number and place the results in an array.

3) Write code to sort the array by Name + Join Date. Assume that the array has already been created and is sorted in the same manner as the table above. The Join Date is of type smalldatetime. The Name field should be sorted case insensitively.

Thanks for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 12:30:39

May I have the number for your professor, please?

-- Prepare sample data
DECLARE	@Class TABLE (ID INT, Name SYSNAME, JoinDate SMALLDATETIME, Sex CHAR(1))

SET DATEFORMAT MDY

INSERT	@Class
SELECT	101, 'Jason', '01/02/1996', 'M' UNION ALL
SELECT	117, 'Robert', '06/25/1999', 'M' UNION ALL
SELECT	113, 'Doug', '08/12/1999', 'M' UNION ALL
SELECT	161, 'Marian', '07/30/1998', 'F'

DECLARE	@Today SMALLDATETIME
SET @Today = '12/31/1999'

-- Exam 1
SELECT	ID,
	Name,
	JoinDate,
	Sex
FROM	@Class
WHERE	Sex = 'M'
	AND JoinDate >= DATEADD(YEAR, -4, @Today)

-- Exam 2
SELECT		c.ID,
		c.Name,
		c.JoinDate,
		c.Sex
FROM		@Class AS c
INNER JOIN	(
			SELECT		ID
			FROM		@Class
			GROUP BY	ID
			HAVING		COUNT(*) > 1
		) AS d ON d.ID = c.ID

-- Exam 3
SELECT		ID,
		Name,
		JoinDate,
		Sex
FROM		@Class
ORDER BY	Name,
		JoinDate

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-05 : 04:36:54

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources