problem_solving_query_practice.sql

/* Write an SQL query to display the correct message (meaningful message) from the input
comments_and_translation table. */

create table comments_and_translations
(
	id				int,
	comment			varchar(100),
	translation		varchar(100)
);

insert into comments_and_translations values
(1, 'very good', null),
(2, 'good', null),
(3, 'bad', null),
(4, 'ordinary', null),
(5, 'cdcdcdcd', 'very bad'),
(6, 'excellent', null),
(7, 'ababab', 'not satisfied'),
(8, 'satisfied', null),
(9, 'aabbaabb', 'extraordinary'),
(10, 'ccddccbb', 'medium');
commit;

-- Solution using CASE statement

SELECT CASE WHEN translation IS NULL THEN comment ELSE translation END AS output
FROM comments_and_translations;

-- Solution using a built in function

SELECT COALESCE(translation, comment) AS output
FROM comments_and_translations;



/*Using the Source and Target table, write a query to arrive at the Output table as shown in below image. 
Provide the solution without using subqueries. */

CREATE TABLE source
    (
        id      int,
        name    varchar(1)
    );

CREATE TABLE target
    (
        id      int,
        name    varchar(1)
    );

INSERT INTO source VALUES (1, 'A');
INSERT INTO source VALUES (2, 'B');
INSERT INTO source VALUES (3, 'C');
INSERT INTO source VALUES (4, 'D');

INSERT INTO target VALUES (1, 'A');
INSERT INTO target VALUES (2, 'B');
INSERT INTO target VALUES (4, 'X');
INSERT INTO target VALUES (5, 'F');

COMMIT;

SELECT s.id, 'Mismatch' AS Comment
FROM source s
JOIN target t ON s.id = t.id AND s.name <> t.name
	UNION
SELECT s.id, 'New in source' AS Comment
FROM source s
LEFT JOIN target t ON s.id = t.id
WHERE t.id IS NULL
	UNION
SELECT t.id, 'New in target' AS Comment
FROM source s
RIGHT JOIN target t ON s.id = t.id
WHERE s.id IS NULL;



/* There are 10 IPL team. write an sql query such that each team play with every other team just once. */

drop table teams;
create table teams
    (
        team_code       varchar(10),
        team_name       varchar(40)
    );

insert into teams values ('RCB', 'Royal Challengers Bangalore');
insert into teams values ('MI', 'Mumbai Indians');
insert into teams values ('CSK', 'Chennai Super Kings');
insert into teams values ('DC', 'Delhi Capitals');
insert into teams values ('RR', 'Rajasthan Royals');
insert into teams values ('SRH', 'Sunrisers Hyderbad');
insert into teams values ('PBKS', 'Punjab Kings');
insert into teams values ('KKR', 'Kolkata Knight Riders');
insert into teams values ('GT', 'Gujarat Titans');
insert into teams values ('LSG', 'Lucknow Super Giants');
commit;

-- Each team plays with every other team JUST ONCE.

WITH matches AS
	(SELECT row_number() over(order by team_name) AS id, t.*
	 FROM teams t)
SELECT team.team_name AS team, opponent.team_name AS opponent
FROM matches team
JOIN matches opponent ON team.id < opponent.id
ORDER BY team;

-- Each team plays with every other team TWICE.

WITH matches AS
	(SELECT row_number() over(order by team_name) AS id, t.*
	 FROM teams t)
SELECT team.team_name AS team, opponent.team_name AS opponent
FROM matches team
JOIN matches opponent ON team.id <> opponent.id
ORDER BY team;