SQL Queries

1.FIND THE EMPLOYEES WHO BELONG TO THE SAME CITY AS PRAKASH

 SELECT * FROM EMPLOYEES WHERE CITY=(SELECT
CITY FROM EMPLOYEES WHERE TITLE='SALES MANAGER')

2.FIND THE EMPLOYEES WHO JOINED THE SAME DAY AS PREM

SELECT * FROM EMPLOYEES WHERE BIRTHDATE=(SELECT
BIRTHDATE FROM EMPLOYEES WHERE EMPLOYEEID=9)
 

3.FINDING THE EMPLOYEES WHERE INNER JOIN IS RETURNING MORE THAN ONE ROW

SELECT * FROM EMPLOYEES WHERE CITY IN(SELECT
CITY FROM EMPLOYEES WHERE TITLE='SALES REPRESENTATIVE')

4.CORELLATED SUBQUERY

SELECT EMP_NO, NAME, HIRE_DATE, MGR_NO FROM EMPLOYEES SUBORDINATES
WHERE HIRE_DATE < (SELECT HIRE_DATE FROM EMPLOYEES BOSSES
WHERE BOSSES.EMP_NO = SUBORDINATES.MGR_NO)

 

5.Left and Right outer join

SELECT titles.title_id, titles.title, publishers.pub_name
FROM titles right OUTER JOIN publishers
ON titles.pub_id = publishers.pub_id

Right join -- All rows in the second-named table (the "right" table, which appears rightmost in the JOIN clause) are included.

Left join --  All rows from the first-named table (the "left" table, which appears leftmost in the JOIN clause) are included

6.@@ERROR

Returns the error number for the last Transact-SQL statement executed.

 

7.@@FETCH_STATUS

Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.
 

8.@@CONNECTIONS

Returns the number of connections, or attempted connections, since Microsoft® SQL Server™ was last started.

 

9.Self Join
Select SUBORD.NAME SUBORDINATE,  BOSS.NAME BOSS
from EMPLOYEES SUBORD, EMPLOYEES BOSS
where SUBORD.MGR_NO = BOSS.EMP_NO;