Few days ago I got a series of RDBMS related problems for practice. I thought that I should solve them and return. After write some code I change my idea and now I am sharing them with all of my readers. It is always best to write a blog post than mail. The blog post is public and you can share your ideas publicly and if needed you can share the post link to some one privately.

This is a very simple problem and though you can work with any RDBMS, I am using Microsoft SQL Server here. Feel free to download the code from GitHub and give comments or contribute.

1. Create table named ‘Products1’ with the following attributes.
   SerialNumber (primary key), Name, Rate, Quantity and Vender

   Now add some data into the 'Products1' table.

   SerialNumber  Name        Rate  Quantity  Vendor
        1        TV          2300     55     Sony
        2        HDD         2500     30     LG
        3        COMPUTER    18000    15     HCL
        4        MICRO OVEN  5000     15     IFB  

2. Create another table 'Products2' with the following attributes.
   SerialNumber (foreign key), Name, Quantity, Vendor

   Now add some data into the 'Products2' table.

   SerialNumber  Name       Quantity  Vendor
        1        CD Player    150     Sony
        2        DVD Player    80     LG
        3        iPOD          55     Panasonic

3. Create relationship between 'Products1' and 'Products2'.
4. Add the data of 'Name', 'Quantity' and 'Vendor' 
   from 'Products2' to 'Products1'.
5. Add the data of 'SerialNumber' and 'Name' only from 'Products1'
   to 'Product2' whose 'Rate' is greater than 15000.

I have created the solution code in four parts. Each part is responsible for a specific job.

USE [master]
GO

CREATE DATABASE Lession4Db ON PRIMARY
(
    NAME = N'Lession4Db',
    FILENAME = N'C:\Lession4\Lession4Db.mdf',
    SIZE = 3MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
LOG ON
(
    NAME = N'Lession4DbLog',
    FILENAME = N'C:\Lession4\Lession4DbLog.ldf',
    SIZE = 2MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
GO

USE [Lession4Db]
GO

CREATE TABLE Products1
(
    SerialNumber INT NOT NULL IDENTITY(1, 1),
    Product VARCHAR(20) NOT NULL,
    Rate DECIMAL(10, 2) NULL,
    Quantity INT NOT NULL,
    Vendor VARCHAR(15) NOT NULL,

    CONSTRAINT pkProducts1 PRIMARY KEY (SerialNumber)
)
GO

CREATE TABLE Products2
(
    Product VARCHAR(20) NOT NULL,
    Quantity INT NULL,
    Vendor VARCHAR(15) NULL,

    SerialNumber INT NOT NULL,

    CONSTRAINT fkProducts2WithProducts1 FOREIGN KEY (SerialNumber) REFERENCES Products1 (SerialNumber)
)
GO

USE [Lession4Db]
GO

INSERT INTO Products1 (Product, Rate, Quentity, Vendor)
VALUES ('TV', 23000, 55, 'Sony'),
       ('HDD', 2500, 30, 'LG'),
       ('Computer', 18000, 15, 'HCL'),
       ('Micro oven', 5000, 15, 'IFB')
GO

INSERT INTO Products2 (Product, Quantity, Vendor, SerialNumber)
VALUES ('CD player', 150, 'Sony', 1),
       ('DVD player', 80, 'LG', 2),
       ('Ipod', 35, 'Panasonic', 3)
GO

USE [Lession4Db]
GO

-- Answer to question 4.
INSERT INTO Products1 (Product, Quantity, Vendor)
SELECT Product, Quantity, Vendor
FROM Products2
GO

-- Answer to question 5.
INSERT INTO Products2 (Product, SerialNumber)
SELECT Product, SerialNumber
FROM Products1
WHERE Rate > 15000
GO

Please follow this code on GitHub here.