Thursday, September 20, 2012

SQL Update with inner joins


Important points in SQL Update:

(1) We can not update more than one table at a time.

Customer places an Order

(2) We can use INNER JOIN with UPDATE in following way (w.r.t database schema above):

UPDATE Ref_Order_Status_Codes
SET 
order_status_description = 'Paid'

FROM Products AS PROD


INNER JOIN Customer_Orders_Products AS CUST_PROD ON PROD.product_id = CUST_PROD.product_id

INNER JOIN Customer_Orderss AS CUST_ORD ON CUST_PROD.order_id = CUST_ORD.order_id
INNER JOIN Ref_Order_Status_Codes AS REF ON REF.order_status_code = CUST.order_status_code

WHERE PROD.product_id = 'WKSO00045'



Monday, September 10, 2012

Quick review of SQL Joins

TYPE OF JOIN
SQL QUERY
RESULT
REMARK

SELECT *
FROM [HKS].[dbo].[MERIT_LIST]






Rank
Marks
RollNo
1
95
2002
2
95
1001
3
91
3002
4
89
2004
5
88
2001

Note that Roll No - 1002, 1004 don't exist in this table.

SELECT *
FROM [HKS].[dbo].[STUDENT_RECORD]







RollNo
Name
Gender
Class
1001
ANITA
F
9TH
1002
MIKE
M
9TH
2001
ROBIN
M
10TH
3002
SMITH
M
12TH
1004
SUE
F
9TH
2002
RAMSE
M
10TH

Note that Roll No 2004 doesn't exist in this table.
INNER JOIN
SELECT LIST.RANK, LIST.MARKS, RECORD.NAME, RECORD.GENDER
FROM STUDENT_RECORD AS RECORD
INNER JOIN MERIT_LIST AS LIST ON LIST.RollNo = RECORD.RollNo
ORDER BY LIST.RANK


RANK
MARKS
NAME
GENDER
1
95
RAMSE
M
2
95
ANITA
F
3
91
SMITH
M
5
88
ROBIN
M

At least one match in both tables
LEFT JOIN
OR
LEFT OUTER JOIN

SELECT LIST.RANK, LIST.MARKS, RECORD.NAME, RECORD.GENDER
FROM STUDENT_RECORD AS RECORD
LEFT JOIN MERIT_LIST AS LIST ON LIST.RollNo = RECORD.RollNo
ORDER BY LIST.RANK


RANK
MARKS
NAME
GENDER
NULL
NULL
MIKE
M
NULL
NULL
SUE
F
1
95
RAMSE
M
2
95
ANITA
F
3
91
SMITH
M
5
88
ROBIN
M



Returns all rows from the left table,

even if there are no matches in the right table

Here
LEFT TABLE - STUDENT_RECORD
RIGHT TABLE -
MERIT_LIST

So Roll No 1002 (Mike) and 1004 (Sue) exist here with null marks.
RIGHT JOIN
OR
RIGHT OUTER JOIN
SELECT LIST.RANK, LIST.MARKS, RECORD.NAME, RECORD.GENDER
FROM STUDENT_RECORD AS RECORD
RIGHT JOIN MERIT_LIST AS LIST ON LIST.RollNo = RECORD.RollNo
ORDER BY LIST.RANK

RANK
MARKS
NAME
GENDER
1
95
RAMSE
M
2
95
ANITA
F
3
91
SMITH
M
4
89
NULL
NULL
5
88
ROBIN
M


Returns all rows from the right table,

even if there are no matches in the left table

Here
LEFT TABLE - STUDENT_RECORD
RIGHT TABLE -
MERIT_LIST

So Roll No 2004 exists here without name and gender information.
FULL JOIN
SELECT LIST.RANK, LIST.MARKS, RECORD.NAME, RECORD.GENDER
FROM STUDENT_RECORD AS RECORD
FULL JOIN MERIT_LIST AS LIST ON LIST.RollNo = RECORD.RollNo
ORDER BY LIST.RANK
RANK
MARKS
NAME
GENDER
NULL
NULL
MIKE
M
NULL
NULL
SUE
F
1
95
RAMSE
M
2
95
ANITA
F
3
91
SMITH
M
4
89
NULL
NULL
5
88
ROBIN
M

If there is a match in one of the tables