SQLQuery1.sql

create database DbConnection_1
use dbconnection_1
CREATE TABLE COUNTRY
(
	COUNTRY_ID INT PRIMARY KEY IDENTITY(1,1),
	COUNTRY_NAME VARCHAR(20)
);

CREATE TABLE PROVINCE
(
	PROVINCE_ID INT PRIMARY KEY IDENTITY(1,1),
	PROVINCE_NAME VARCHAR(20),
	C_ID INT FOREIGN KEY REFERENCES COUNTRY(COUNTRY_ID)
);

CREATE TABLE CITY
(
	CITY_ID INT PRIMARY KEY IDENTITY(1,1),
	CITY_NAME VARCHAR(20),
	PRO_ID INT FOREIGN KEY REFERENCES PROVINCE(PROVINCE_ID)
);

SELECT * FROM CITY;
SELECT * FROM PROVINCE;
SELECT * FROM COUNTRY;

DROP TABLE COUNTRY;
DROP TABLE PROVINCE;
DROP TABLE CITY;

INSERT INTO COUNTRY 
VALUES ('INDIA'),('USA'),('CANADA');

INSERT INTO PROVINCE
VALUES ('UTTAR PRADESH', 1), ('HARYANA', 1), ('DELHI', 1),
	   ('NEW YORK', 2), ('LOS ANGELES', 2), ('TEXAS', 2),
	   ('ONTARIO', 3), ('QUEBEC', 3), ('MONTREAL', 3);

INSERT INTO CITY
VALUES ('NOIDA', 1), ('GREATER NOIDA',1), ('GHAZIABAD',1), ('ALIGARH', 1), ('GORAKHPUR', 1), ('LUCKNOW', 1), ('ALLAHABAD', 1),
       ('GURGAON', 2), ('FARIDABAD', 2), ('HARYANA 3', 2),
	   ('NEW DELHI', 3), ('SOUTH DELHI', 3), ('NORTH DELHI', 3), ('EAST DELHI', 3), ('WEST DELHI', 3),
	   ('QUEENS', 4), ('BROOKLYN', 4), ('MANHATTAN', 4),
	   ('LA 1', 5), ('LA 2', 5), ('LA 3', 5),
	   ('ODESSA', 6), ('TX 2', 6), ('TX 3', 6),
	   ('TORONTO', 7), ('ONTARIO 2', 7), ('ONTARIO 2', 7),
	   ('QUEBEC 1', 8), ('QUEBEC 2', 8), ('QUEBEC 3', 8),
	   ('MONTREAL 1', 9), ('MONTREAL 2', 9), ('MONTREAL 3', 9);

CREATE TABLE FORM
(
	FORM_ID INT PRIMARY KEY IDENTITY(1,1),
	FIRST_NAME VARCHAR(15),
	LAST_NAME VARCHAR(10),
	FATHER_FIRST_NAME VARCHAR(15),
	FATHER_LAST_NAME VARCHAR(10),
	SEX VARCHAR(6),
	DATE_OF_BIRTH VARCHAR(10),
	EMAIL VARCHAR(50),
	MOBILE VARCHAR(20),
	MAILING_ADDRESS VARCHAR(100),
	CITY_ID_2 INT FOREIGN KEY REFERENCES CITY(CITY_ID),
	PROVINCE_ID_2 INT FOREIGN KEY REFERENCES PROVINCE(PROVINCE_ID),
	COUNTRY_ID_2 INT FOREIGN KEY REFERENCES COUNTRY(COUNTRY_ID),
);

SELECT * FROM FORM;

INSERT INTO FORM
VALUES (('MOHD NAVED'), ('ALAM'), ('ZIA'), ('ALAM'), ('MALE'), ('02-10-1987'), ('alam00star@gmail.com'), ('9971863812'), ('ZAKIR NAGAR, OLKHLA'), (11), (3), (1)),
       (('CALRKE'), ('KENT'), ('JONATHAN'), ('KENT'), ('MALE'), ('01-01-1930'), ('clarke.kent@dailyplanet.com'), ('1234567890'), ('ABCDE, XYZ'), (24), (6), (2));

TRUNCATE TABLE FORM;

--APPLYING INNER JOIN TO DISPLAY THE VALUES OF THE CITY, PROVINCE AND COUNTRY IDs--

SELECT FORM.FORM_ID, FORM.FIRST_NAME, FORM.LAST_NAME, FORM.FATHER_FIRST_NAME, FORM.FATHER_LAST_NAME, FORM.SEX, FORM.DATE_OF_BIRTH, FORM.EMAIL, FORM.MOBILE, FORM.MAILING_ADDRESS, CITY.CITY_NAME, PROVINCE.PROVINCE_NAME, COUNTRY.COUNTRY_NAME
FROM FORM
INNER JOIN CITY
ON
FORM.CITY_ID_2 = CITY.CITY_ID
INNER JOIN PROVINCE
ON
FORM.PROVINCE_ID_2 = PROVINCE.PROVINCE_ID
INNER JOIN COUNTRY
ON
FORM.COUNTRY_ID_2 = COUNTRY.COUNTRY_ID

	--Inner Join Syntax:-
	--  - SELECT column_name(s)
	--    FROM table1
	--    INNER JOIN table2 ON table1.column_name = table2.column_name;
	
	--The INNER JOIN keyword selects records that have matching values in both tables.
	-- -The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. 
	-- -If there are records in the "Orders" table that do not have matches in "Customers", 
	--  these orders will not be shown.