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.