SQL Query: Select from Two separate Query

SELECT S.STYR_Student_ID, S.StartDate, E.EndDate, S.FirstYear, E.LastYear

From

(SELECT                STYR_Student_ID, MIN(STYR_Enrolled_On) AS StartDate, MIN(STYR_Year) AS FirstYear

FROM                   NG.dbo.STYRstudentYR

GROUP BY           STYR_Student_ID) AS S

INNER JOIN

(SELECT                STYR_Student_ID, MAX(STYR_Drop_Out_Date) AS EndDate, MAX(STYR_Year) AS LastYear

FROM                   NG.dbo.STYRstudentYR

GROUP BY           STYR_Student_ID) AS E

ON S.STYR_Student_ID = E.STYR_Student_ID

ORDER BY S.STYR_Student_ID

SQL Query: A Simple Use of SQL CASE Expression

What is CASE Expression 

CASE is the special scalar expression in SQL language. CASE expression is widely used to facilitate determining / setting a new value from user input values. CASE expression can be used for various purposes which depends on the business logic.

CASE expression is mostly used in SQL stored procedure or as a formula for a particular column, which optimizes the SQL statements.

Syntax of CASE Expression 

SQL CASE expression is used as a type of IF-THEN-ELSE statement. It is similar to switch statement in recent programming languages such as C# and Java. The syntax of the CASE statement is simple as follows:

 Collapse | Copy Code

1.    CASE column_name  
2.      WHEN condition1 THEN result1  
3.      WHEN condition2 THEN result2  
4.      ...  
5.      ELSE result  
6.    END

SQL Query: Insert Into statement - Select data from different table

Scenario:
I want to insert a record into table A that I will select from different table B if it is not already exist in table A.

Solution:

INSERT INTO tGymReg ( AdmissionNumber, Surname, Forename, Called, Induction )
     SELECT dbo_vwStudCYAll.AdmissionNumber, dbo_vwStudCYAll.Surname,
     dbo_vwStudCYAll.Forename, dbo_vwStudCYAll.Called, dbo_vwStudCYAll.Induction
     FROM dbo_vwStudCYAll
     LEFT JOIN tGymReg ON dbo_vwStudCYAll.AdmissionNumber = tGymReg.AdmissionNumber
WHERE (((tGymReg.AdmissionNumber) Is Null));

CREATE procedure [dbo].[ImportNewStudentToSTUDStudentCurrent]

as

insert into STUDStudentCurrent (STUD_AdmissionNumber,STUD_Surname,STUD_Forename,STUD_Called)

      select test.[Column 0], test.[Column 1], test.[Column 2], test.[Column 3]

      from test

      left join STUDStudentCurrent

            on test.[Column 0]=STUDStudentCurrent.STUD_AdmissionNumber

      where (STUDStudentCurrent.STUD_AdmissionNumber is null)

SQL Query: Delete record if it is exist in sub query

Scenario:
I have created a table tGymReg where I import all the current students from the live updated table tStudCYAll. When a student leave tStudCYAll table get updated but my table tGymReg does not get updated. So I need to delete all the old student record.

Solution:
In this case the student who has left will not be available in tStudCYAll but it will be available in tGymReg.
Query:

DELETE * FROM tGymReg WHERE tGymReg.AdmissionNumber IN
(SELECT tGymReg.AdmissionNumber
FROM tGymReg LEFT JOIN dbo_vwStudCYAll ON tGymReg.AdmissionNumber = dbo_vwStudCYAll.AdmissionNumber
WHERE (((dbo_vwStudCYAll.AdmissionNumber) Is Null)));


DELETE * FROM `products` WHERE `products_id` IN
(SELECT p.`products_id`
   FROM `products` p, `products_suppliers` ps
   WHERE p.`products_id` = ps.`products_id`
   AND (p.`products_id` <> 215 AND p.`products_id` <> 305))

SQL Query: Select only values from one table that do not exist in another table

Scenario:
I want to select some record from table2 when it is not exist in table1

Solution:
SELECT
  table2.column_name
FROM
    table1
  LEFT JOIN
    table2
  ON
    table1.id = table2.id
WHERE
  table1.id IS NULL