SQL Server Advance Update with Joins

 SQL Server Advance Update with Joins

Student
id	first_name	last_name
1	Shreya	Bain
2	Rianna	Foster
3	Yosef	Naylor
student_course
student_id	course_id
1	2
1	3
2	1
2	2
2	3
3	1
course
id	name	teacher_id
1	Database design	1
2	English literature	2
3	Python programming	1
Teacher
id	name	Rating	Exp
1	Hari	3.4	2
2	Vina	4.2	4
3	Kishan Lal	4.4	6
4	Gupta	4.6	5

 

Create table Student( id int, first_name varchar(50), last_name varchar(50))
	Insert into Student values (1,'Shreya','Bain')
	Insert into Student values (2,'Rianna','Foster')
	Insert into Student values (3,'Yosef','Naylor')
Create table student_course( student_id int, course_id varchar(50))
	Insert into student_course values(1,2)
	Insert into student_course values(1,3)
	Insert into student_course values(2,1)
	Insert into student_course values(2,2)
	Insert into student_course values(2,3)
	Insert into student_course values(3,1)
Create table course( id int, name varchar(50), teacher_id int)
	Insert into course values (1,'Database design',1)
	Insert into course values (2,'English literature',2)
	Insert into course values (3,'Python programming',1)
Create table Teacher( id int, name varchar(50), Rating decimal(2,1),Exp int)
	Insert into Teacher values (1,'Hari',3.4,2)
	Insert into Teacher values (2,'Vina',4.2,4)
	Insert into Teacher values (3,'Kishan Lal',4.4,6)
	Insert into Teacher values (4,'Gupta',4.6,5)

 

Expected Output
first_name	last_name	name
Shreya	Bain	English literature
Shreya	Bain	Python programming
Rianna	Foster	Database design
Rianna	Foster	English literature
Rianna	Foster	Python programming
Yosef	Naylor	Database design
Output script:
select first_name,last_name,name		from Student as s
inner join student_course as sc ON student_id=id
inner join course as c ON c.id=sc.course_id

 =================================================================

Person
person_id	first_name	account_number
1	John	(null)
2	Sarah	(null)
3	Mark	(null)
Account
account_id	account_number	person_id
1	100298	2
2	103557	3
3	108956	1
4	109703	4

 

CREATE TABLE person (

person_id INT,

first_name VARCHAR(100),

account_number INT

);

CREATE TABLE account (

account_id INT,

account_number INT,

person_id INT

);

INSERT INTO person (person_id, first_name) VALUES

(1, 'John'),

(2, 'Sarah'),

(3, 'Mark');

INSERT INTO account (account_id, account_number, person_id) VALUES

(1, 100298, 2),

(2, 103557, 3),

(3, 108956, 1),

(4, 109703, 4);

UPDATE p

SET p.account_number = a.account_number

FROM person p

INNER JOIN account a

ON p.person_id = a.person_id;

UPDATE person

SET account_number = account.account_number

FROM account

WHERE person.person_id = account.person_id;

using subquery

UPDATE person

SET account_number = (

SELECT account_number

FROM account

WHERE account.person_id = person.person_id

);

using CTE

WITH subquery AS (

SELECT

account_id,

account_number,

person_id

FROM account

)

UPDATE person

SET account_number = subquery.account_number

FROM subquery

WHERE person.person_id = subquery.person_id;

--=================================================================================

--Create a table 'tbEmployeeMaster'
CREATE TABLE tbEmployeeMaster
(
EmployeeId    INT IDENTITY(1,1) PRIMARY KEY,
Name              VARCHAR(100),
Salary              DECIMAL(18,2),
Designation    VARCHAR(50)
)
--Insert some sample data in this table
INSERT INTO tbEmployeeMaster
VALUES
('Aman',34000,NULL),
('Rohan',48000,NULL),
('Varun',80000,NULL),
('Arjun',37000,NULL),
('Raghav',22000,NULL),
('Sameer',12000,NULL);
CREATE TABLE tbEmployeeExperience
(
EmployeeId                INT,
YearsOfExperience     INT,
WorkedAs                   VARCHAR(50)
)
--Insert some sample data in this table
INSERT INTO tbEmployeeExperience
VALUES
(1,4,'Quality Analyst'),
(2,7,'Tester'),
(3,12,'Developer'),
(4,5,'Developer'),
(5,3,'Support'),
(6,1,'Tester');
SELECT * FROM tbEmployeeMaster
SELECT * FROM tbEmployeeExperience
Result will be:
EmployeeId	YearsOfExperience	WorkedAs
1	4	Quality Analyst
2	7	Tester
3	12	Developer
4	5	Developer
5	3	Support
6	1	Tester
UPDATE T1
SET T1.Column1 = T2.Column1
FROM Table1 AS T1 INNER JOIN Table2 AS T2
ON T1.Id = T2.Id;

 

 

Expectation:

using the Above both table update the net_price by substracting Discount Percentage

CREATE TABLE product_segment (						select ps.id,name,price,net_price,discount,(price - price * discount) as netprice,
id int PRIMARY KEY identity,
segment VARCHAR(50) NOT NULL,						left join product as p
discount decimal (4, 2)						on
);						p.segment_id=ps.id
INSERT INTO
product_segment (segment, discount)
VALUES
('Grand Luxury', 0.05),
('Luxury', 0.06),
('Mass', 0.1);
CREATE TABLE product(
id int PRIMARY KEY identity,
name VARCHAR(50) NOT NULL,
price decimal(10,2),
net_price decimal(10,2),
segment_id INT NOT NULL,
FOREIGN KEY(segment_id) REFERENCES product_segment(id)
);
INSERT INTO
product (name, price, segment_id)
VALUES
('diam', 804.89, 1),
('vestibulum aliquet', 228.55, 3),
('lacinia erat', 366.45, 2),
('scelerisque quam turpis', 145.33, 3),
('justo lacinia', 551.77, 2),
('ultrices mattis odio', 261.58, 3),
('hendrerit', 519.62, 2),
('in hac habitasse', 843.31, 1),
('orci eget orci', 254.18, 3),
('pellentesque', 427.78, 2),
('sit amet nunc', 936.29, 1),
('sed vestibulum', 910.34, 1),
('turpis eget', 208.33, 3),
('cursus vestibulum', 985.45, 1),
('orci nullam', 841.26, 1),
('est quam pharetra', 896.38, 1),
('posuere', 575.74, 2),
('ligula', 530.64, 2),
('convallis', 892.43, 1),
('nulla elit ac', 161.71, 3);
select * from product_segment
select * from product
Expectation
select ps.id,name,price,net_price,discount,(price - price * discount) as netprice,
		segment_id from product_segment ps
left join product as p
on
p.segment_id=ps.id

 

OR

select ps.id,name,price,net_price,discount,(price - price * discount) as netprice,
		segment_id from product_segment ps
left join product as p
on
p.segment_id=ps.id

 

=========================================================================

Customers
CustomerId	CustomerName	PostalCityId	PhoneNumber
1	Homer McKenzie	19586	(308) 555-0100
2	Marge Pratt	33475	(406) 555-0100
3	Vlad Bernanke	NULL	(480) 555-0100
4	Bart Pitt	21692	(316) 555-0100
5	Lisa McQueen	12748	(212) 555-0100
6	Steve Simpson	17054	(701) 555-0100
7	Vinn Allen	12152	(423) 555-0100
8	Veejay Smith	3673	(303) 555-0100
9	Kasey Chin	23805	(201) 555-0100
10	Borat Lee	37403	(701) 555-0100
Cities
CityId	CityName	StateProvinceId	Population
3673	Bow Mar	6	866
12152	Frankewing	44	NULL
12748	Gasport	33	1248
21692	Medicine Lodge	17	2009
26483	Peeples Valley	3	428
33475	Sylvanite	27	103
17054	Jessie	35	25
19586	Lisco	28	NULL
37403	Wimbledon	35	216
StateProvinces
StateProvinceId	StateProvinceCode	StateProvinceName	CountryId	Population
3	AZ	Arizona	230	6891688
6	CO	Colorado	230	5698265
17	KS	Kansas	230	2893957
28	NE	Nebraska	230	1943256
31	NJ	NewJersey	230	8899339
33	NY	NewYork	230	20437172
35	ND	NorthDakota	230	723393
44	TN	Tennessee	230	6495978

 

Create Table Customers(CustomerId int, CustomerName varchar(50), PostalCityId int,PhoneNumber varchar(50))
Insert into Customers values(1,'Homer McKenzie',19586,'(308) 555-0100')
Insert into Customers values(2,'Marge Pratt',33475,'(406) 555-0100')
Insert into Customers values(3,'Vlad Bernanke',NULL,'(480) 555-0100')
Insert into Customers values(4,'Bart Pitt',21692,'(316) 555-0100')
Insert into Customers values(5,'Lisa McQueen',12748,'(212) 555-0100')
Insert into Customers values(6,'Steve Simpson',17054,'(701) 555-0100')
Insert into Customers values(7,'Vinn Allen',12152,'(423) 555-0100')
Insert into Customers values(8,'Veejay Smith',3673,'(303) 555-0100')
Insert into Customers values(9,'Kasey Chin',23805,'(201) 555-0100')
Insert into Customers values(10,'Borat Lee',37403,'(701) 555-0100')
Create Table Cities(CityId int, CityName varchar(50), StateProvinceId int,Population int)
Insert into Cities values(3673  ,'Bow Mar',6,'866')
Insert into Cities values(12152 ,'Frankewing',44,NULL)
Insert into Cities values(12748 ,'Gasport',33,'1248')
Insert into Cities values(21692 ,'Medicine Lodge',17,'2009')
Insert into Cities values(26483 ,'Peeples Valley',3,'428')
Insert into Cities values(33475 ,'Sylvanite ',27,'103')
Insert into Cities values(17054 ,'Jessie',35,'25')
Insert into Cities values(19586 ,'Lisco  ',28,NULL)
Insert into Cities values(37403 ,'Wimbledon ',35,'216')
Create Table StateProvinces(StateProvinceId   int, StateProvinceCode   varchar(50), StateProvinceName   varchar(100),CountryId   int, Population  int)
Insert into StateProvinces values(3,'AZ','Arizona','230',6891688)
Insert into StateProvinces values(6,'CO','Colorado','230',5698265)
Insert into StateProvinces values(17,'KS','Kansas','230',2893957)
Insert into StateProvinces values(28,'NE','Nebraska','230',1943256)
Insert into StateProvinces values(31,'NJ','NewJersey ','230',8899339)
Insert into StateProvinces values(33,'NY','NewYork','230',20437172)
Insert into StateProvinces values(35,'ND','NorthDakota','230',723393)
Insert into StateProvinces values(44,'TN','Tennessee ','230',6495978)

 

Expectation:
StateProvinceName	CityName	CustomerName
Nebraska	Lisco	Homer McKenzie
Kansas	Medicine Lodge	Bart Pitt
NewYork	Gasport	Lisa McQueen
NorthDakota	Jessie	Steve Simpson
Tennessee	Frankewing	Vinn Allen
Colorado	Bow Mar	Veejay Smith
NorthDakota	Wimbledon	Borat Lee
SELECT
s.StateProvinceName,
ci.CityName,
cu.CustomerName
FROM StateProvinces s
INNER JOIN Cities AS ci
ON ci.StateProvinceID = s.StateProvinceID
INNER JOIN Customers cu
ON cu.PostalCityId = ci.CityId;

 

Expectation:
StateProvinceName	CityName	CustomerName
Colorado	Bow Mar	Veejay Smith
Tennessee	Frankewing	Vinn Allen
NewYork	Gasport	Lisa McQueen
Kansas	Medicine Lodge	Bart Pitt
Arizona	Peeples Valley	NULL
NorthDakota	Jessie	Steve Simpson
Nebraska	Lisco	Homer McKenzie
NorthDakota	Wimbledon	Borat Lee
SELECT
s.StateProvinceName,
ci.CityName,
cu.CustomerName
FROM StateProvinces s
INNER JOIN Cities AS ci
ON ci.StateProvinceID = s.StateProvinceID
LEFT JOIN Customers cu
ON cu.PostalCityId = ci.CityId;

 

 ====================================================================

Homework

=========================================================

 

PetTypes
PetTypeId	PetType
1	Bird
2	Cat
3	Dog
4	Rabbit

 

Pets
PetId	PetTypeId	OwnerId	PetName	DOB
1	2	3	Fluffy	11/20/2020
2	3	3	Fetch	8/16/2019
3	2	2	Scratch	10/1/2018
4	3	3	Wag	3/15/2020
5	1	1	Tweet	11/28/2020
6	3	4	Fluffy	9/17/2020
7	3	2	Bark	NULL
8	2	4	Meow	NULL

 

	Owners
OwnerId	FirstName	LastName	Phone	Email
1	Homer	Connery	(308) 555-0100	homer@example.com
2	Bart	Pitt	(231) 465-3497	bart@example.com
3	Nancy	Simpson	(489) 591-0408	NULL
4	Boris	Trump	(349) 611-8908	NULL
5	Woody	Eastwood	(308) 555-0112	woody@example.com

 

Expectation
PetName	PetType	PetOwner
Tweet	Bird	Homer Connery
Scratch	Cat	Bart Pitt
Bark	Dog	Bart Pitt
Fluffy	Cat	Nancy Simpson
Fetch	Dog	Nancy Simpson
Wag	Dog	Nancy Simpson
Fluffy	Dog	Boris Trump
Meow	Cat	Boris Trump
NULL	NULL	Woody Eastwood

 

SELECT
p.PetName,
pt.PetType,
CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Owners o LEFT JOIN Pets p
ON p.OwnerId = o.OwnerId
LEFT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;

 

Expectation
PetName	PetType	PetOwner
Tweet	Bird	Homer Connery
Fluffy	Cat	Nancy Simpson
Scratch	Cat	Bart Pitt
Meow	Cat	Boris Trump
Fetch	Dog	Nancy Simpson
Wag	Dog	Nancy Simpson
Fluffy	Dog	Boris Trump
Bark	Dog	Bart Pitt
NULL	Rabbit

 

SELECT
p.PetName,
pt.PetType,
CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Pets p RIGHT JOIN Owners o
ON p.OwnerId = o.OwnerId
RIGHT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;

 

Expectation
PetName	PetType	PetOwner
Tweet	Bird	Homer Connery
Scratch	Cat	Bart Pitt
Bark	Dog	Bart Pitt
Fluffy	Cat	Nancy Simpson
Fetch	Dog	Nancy Simpson
Wag	Dog	Nancy Simpson
Fluffy	Dog	Boris Trump
Meow	Cat	Boris Trump
NULL	NULL	Woody Eastwood
NULL	Rabbit

 

SELECT
p.PetName,
pt.PetType,
CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Owners o FULL JOIN Pets p
ON p.OwnerId = o.OwnerId
FULL JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;

 

Expectation
PetName	PetType	PetOwner
Tweet	Bird	Homer Connery
Scratch	Cat	Bart Pitt
Bark	Dog	Bart Pitt
Fluffy	Cat	Nancy Simpson
Fetch	Dog	Nancy Simpson
Wag	Dog	Nancy Simpson
Fluffy	Dog	Boris Trump
Meow	Cat	Boris Trump
NULL	NULL	Woody Eastwood

 

SELECT
p.PetName,
pt.PetType,
CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Owners o
LEFT JOIN    (Pets p
LEFT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId)
ON p.OwnerId = o.OwnerId;