Scenario-1:
-----------
Write an SQL query to join these two tables in a way that the output includes all
employees, comparing their
details from both tables. The result should indicate:
• Employees who have the same details in both tables.
• Employees whose Name or Dept_Code has changed.
• Employees who exist in one table but not in the other.
Create/Insert:
--------------
DROP TABLE IF EXISTS Table_A;
DROP TABLE IF EXISTS Table_B;
CREATE TABLE Table_A (
emp_no INT PRIMARY KEY,
name VARCHAR(50),
dept_code INT
);
INSERT INTO Table_A (emp_no, name, dept_code) VALUES
(1, 'Raj', 111),
(2, 'Kumar', 222),
(3, 'Suresh', 111),
(4, 'Arun', 333),
(5, 'Ramesh', 333);
CREATE TABLE Table_B (
emp_no INT PRIMARY KEY,
name VARCHAR(50),
dept_code INT
);
INSERT INTO Table_B (emp_no, name, dept_code) VALUES
(1, 'Raj', 111),
(2, 'Gopi', 444),
(3, 'Suresh', 111),
(4, 'Ram', 555),
(5, 'Ramesh', 333);
Solution:
----------
SELECT
COALESCE(a.Emp_no, b.Emp_no) as Emp_no
[Link] as Name_A,
a.Dept_code as Dept_Code_A,
[Link] as Name_B,
b.Dept_code as Dept_Code_B
FROM
Table_A as a
FULL JOIN Table_A as b
ON a.Emp_No = b.Emp_No
Scenario-2:
----------
-- Get the unmatched rows from the Table_A
-- It can be achived using Left Join or EXCEPT
select * from Table_A EXCEPT select * from Table_B;
Scenario-3:
------------
Write an SQL query to find the total marks obtained by each student by summing up
the marks of the top two
highest-scoring subjects.
CREATE TABLE StudentMarks (
StudentID CHAR(1),
SubjectID CHAR(1),
MarksObtained INT,
-- Define a composite primary key for the combination of StudentID and
SubjectID
PRIMARY KEY (StudentID, SubjectID)
);
INSERT INTO StudentMarks (StudentID, SubjectID, MarksObtained) VALUES
('A', 'X', 80),
('A', 'Y', 70),
('A', 'Z', 75),
('B', 'X', 90),
('B', 'Y', 91),
('B', 'Z', 75),
('C', 'X', 60),
('C', 'Y', 93),
('C', 'Z', 81);
SOLUTION1:
----------
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER(Partition by StudentID order by MarksObtained DESC) as
top_marks
FROM StudentMarks
)
SELECT
StudentID,
SUM(MarksObtained) as TotalMarksObtained
FROM CTE
WHERE top_marks < 3
GROUP BY StudentID
SOLUTION2:
-----------
Select
Studentid,
SUM(MarksObtained) as TotalMarksObtained
FROM
(
SELECT *,
ROW_NUMBER() OVER(Partition by StudentID order by MarksObtained DESC) as
top_marks
FROM StudentMarks
) as sub
where top_marks <=2
GROUP BY Studentid
Scenario-4:
------------
What will be the output of the below query?
SELECT PATINDEX('%wo_id','greeting the wide world');
-- Note, PATINDEX is not available Postgresql
Scenario-5 (CGI):
-----------------
How do you create a temporary in SQL?
create temp table temp_table
(
name varchar(50),
age integer
);
insert into temp_table values ('Anand', 15);
select * from temp_table;
-- Temporary tables are visible only to the session that created them.
-- They override any permanent table with the same name in queries within that
session.
Scenario-6 (CGI):
-----------------
You are given a table with a column storing dates in the format YYYY-MM-DD.
Write an SQL query to extract and format the month and day from the OrderDate
column in two separate
columns:
1. Month - in full text format (e.g., February instead of 02)
2. Day - with leading zero if it's a single digit (e.g., 01 instead of 1)
create table orders (OrderID numeric, OrderDate date);
insert into orders values (1,'2024-02-01'),(2,'2024-06-15'),(3,'2024-12-25');
| **Pattern** | **Description** | **Example Output** |
| ----------- | ----------------------------------- | ------------------ |
| `YYYY` | 4-digit year | `2025` |
| `YY` | Last 2 digits of year | `25` |
| `MONTH` | Full month name, padded with spaces | `January ` |
| `Month` | Capitalised month name, padded | `January ` |
| `Mon` | Abbreviated month name | `Jan` |
| `MM` | Month number with leading zero | `01` to `12` |
| `DAY` | Full day name, padded | `Monday ` |
| `Day` | Capitalised day name | `Monday` |
| `Dy` | Abbreviated day name | `Mon` |
| `D` | Day of week (1=Sunday, 7=Saturday) | `1` to `7` |
| `DD` | Day of month with leading zero | `01` to `31` |
| `DDD` | Day of year | `001` to `366` |
| `HH24` | Hour in 24-hour format | `00` to `23` |
| `HH12` | Hour in 12-hour format | `01` to `12` |
| `MI` | Minutes with leading zero | `00` to `59` |
| `SS` | Seconds with leading zero | `00` to `59` |
| `AM` / `PM` | Meridian indicator | `AM` or `PM` |
SOLUTION:
----------
select to_char(orderdate,'Month') as Month,to_char(orderdate,'DD') as Day from
orders;
Scenario-7:
------------
Requirement:
1. Rename the emp table to product.
2. Rename the column ProdID to Prod.
3. Write a query to fetch the top product from each department according to the
total sold amount. The
total sold amount is calculated as the Price of the product multiplied by the
Quantity sold.
SOLUTION:
---------
alter table emp rename to product;
alter table product rename column prodid to prod;
CREATE TABLE Department (
DeptID INTEGER PRIMARY KEY,
DeptName VARCHAR(100)
);
INSERT INTO Department (DeptID, DeptName) VALUES
(1, 'Delhi Store'),
(2, 'Noida Mall'),
(3, 'Gurgaon Mall');
CREATE TABLE Product (
ProdID INTEGER PRIMARY KEY,
ProdName VARCHAR(100),
Price NUMERIC
);
INSERT INTO Product (ProdID, ProdName, Price) VALUES
(1, 'Pencil', 10),
(2, 'Pen', 20),
(3, 'Copy', 50),
(4, 'Printer', 5000);
CREATE TABLE Sales (
ProdID INTEGER REFERENCES Product(ProdID),
DeptID INTEGER REFERENCES Department(DeptID),
Date DATE,
Qty INTEGER
);
INSERT INTO Sales (ProdID, DeptID, Date, Qty) VALUES
(1, 1, '2023-08-20', 5),
(2, 1, '2023-08-20', 10),
(4, 2, '2023-08-20', 2);
044-22671575