Database

select  customers.*,
(select top 1 users.username from users where users.username=customers.forename) as UsersTableUserName
 from customers where username='John' and Email='xxxx@yahoo.com' 

SELECT U.USERNAME,C.*

FROM Customers C

INNER JOIN Users U

ON U.ID = C.ID

AND C.FORENAME = 'JOHN'

AND C.EMAIL = 'xxxx@yahoo.com'

ALTER PROCEDURE dbo.UpdateTableY
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    
	DECLARE @MYCURSOR CURSOR
	DECLARE @ROWID INT
	DECLARE @COL2_X INT
	DECLARE @COL3_X INT
	DECLARE @COL2_Y INT
	DECLARE @COL3_Y INT
    
	
	SET @MYCURSOR = CURSOR FOR

	SELECT A.ROWID,A.COL2,A.COL3
    FROM (SELECT 
				ROW_NUMBER() OVER (ORDER BY COL1) AS ROWID,
				COL2 , COL3 FROM tblX) A 

	OPEN @MYCURSOR
	FETCH NEXT
	FROM @MYCURSOR INTO @ROWID,@COL2_X,@COL3_X

	WHILE @@FETCH_STATUS = 0

	BEGIN

		-- GET THE COL2 VALUES BASED ON ROW ID'S FROM tblY
		SELECT @COL2_Y = COL2 
		FROM tblY
		WHERE COL1 = @ROWID

		--MATCH THE COL2 RECORDS OF BOTH TABLES
		IF(@COL2_Y = @COL2_X)

         BEGIN 
				-- GET THE COL3 VALUES BASED ON ROW ID'S FROM tblY
				SELECT @COL3_Y = COL3 
				FROM tblY
				WHERE COL1 = @ROWID

				--IF A MISMATCH BETWEEN THE COL3 RECORDS OF BOTH TABLES
				-- THEN UPDATE COL4 OF TABLE Y
				IF(@COL3_Y <> @COL3_X)

				BEGIN

					UPDATE tblY

					SET COL3 = @COL3_X,
						COL4 = 1

					WHERE COL1 = @ROWID
				 
				END

		 END	

	FETCH NEXT
	FROM @MYCURSOR INTO @ROWID,@COL2_X,@COL3_X

	END

	CLOSE @MYCURSOR
	DEALLOCATE @MYCURSOR
	
END
GO

SELECT 

		P.ROLENAME,
		P.ROLEDESC, 
		P.ROLEUSER AS 'PRIMARY',
		B.ROLEUSER AS 'BACKUP'

FROM    (	
        SELECT ROLENAME,ROLEDESC,ROLETYPE ,ROLEUSER 
	FROM TBLROLE
	WHERE ROLETYPE = 'PRIMARY') P 

INNER JOIN (
	SELECT ROLEUSER ,ROLEDESC
	FROM TBLROLE
	WHERE ROLETYPE = 'BACKUP')B

ON      P.ROLEDESC = B.ROLEDESC

SELECT *
  FROM sysobjects
 WHERE NAME LIKE ('%name%')

I are troll

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager needed in this project (mandatory)%')

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager %')

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%(mandatory)%')

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%ProjectManager needed in this project (mandatory)%')

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('% (mandatory) %')

1) a)How to list and view the system tables like sys.databases, sysobjects, ...etc in the SQL 2005 management studio?<br />

SELECT * FROM SYS.DATABASES

     SELECT * FROM SYS.OBJECTS

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

<code>CREATE VIEW sys.objects</code> AS   SELECT name, object_id, principal_id, schema_id, parent_object_id,    type, type_desc, create_date, modify_date,    is_ms_shipped, is_published, is_schema_published   FROM sys.objects$  

CREATE TRIGGER SOME_TRIGGER ON spt_fallback_dev

FOR UPDATE AS

SELECT * FROM spt_fallback_dev

CREATE TABLE SAMPLE_TABLE 
(
	EID INT,
	ENAME VARCHAR (12)
);

CREATE TRIGGER SAMPLE_TRIGGER_A ON SAMPLE_TABLE

FOR UPDATE AS

SELECT * FROM SAMPLE_TABLE

CREATE TRIGGER SAMPLE_TRIGGER_B ON SAMPLE_TABLE

FOR UPDATE AS

SELECT * FROM SAMPLE_TABLE

SELECT NAME 
FROM SYSOBJECTS
WHERE DELTRIG = (
SELECT ID 
FROM SYSOBJECTS 
WHERE NAME = 'SAMPLE_TABLE')

NAME
------------------
SAMPLE_TRIGGER_A
SAMPLE_TRIGGER_B

select p.rolename, p.roledesc, p.roleuser, b.roleuser
from RoleTable p, RoleTable b
where p.roletype = 'Primary'
and p.rolename = b.rolename
and b.roletype = 'Backup'