Notes

///////////////////////QUESTION 1,2/////////////////////////////

CREATE TABLE Client(
client_id int PRIMARY KEY,
first_name nvarchar(50),
last_name nvarchar(50),
age int

)
CREATE TABLE Country(
country_id int PRIMARY KEY,
name nvarchar(50)
)
CREATE TABLE Address(
address_id int PRIMARY KEY,
client_id int FOREIGN KEY REFERENCES Client(client_id),
street nvarchar(50),
number char(10),
zip_code int,
state nvarchar(50),
country int FOREIGN KEY REFERENCES Country(country_id)


)
CREATE TABLE [Order](
order_id int PRIMARY KEY,
client_id int FOREIGN KEY REFERENCES Client(client_id),
date datetime

)
CREATE TABLE Phone(
phone_id int PRIMARY KEY,
client_id int FOREIGN KEY REFERENCES Client(client_id),
country_code nvarchar(10),
phone nvarchar(15)
)

CREATE TABLE Item(
item_id int PRIMARY KEY,
order_id int FOREIGN KEY REFERENCES [Order](order_id),
description nvarchar(400),
name nvarchar(100),
value int,
amount int
)
///////////////////////QUESTION 3/////////////////////////////


INSERT INTO Client values(1,'Jon','Doe',29);
INSERT INTO Client values(2,'Ram','Sign',30);
INSERT INTO Client values(3,'Shyam','Singh',29);
INSERT INTO Client values(4,'Shely','Shah',29);
INSERT INTO Client values(5,'Barsa','Paul',223);
INSERT INTO Client values(6,'Sujoy','Das',45);
INSERT INTO Client values(7,'Aniket','Ray',29);
INSERT INTO Client values(8,'Sagarika','Dasgupta',29);
INSERT INTO Client values(9,'Jeet','Halder',29);
INSERT INTO Client values(10,'Mohini','Deb',26);

INSERT INTO [Address] values(21,1,'44/1 Camac Street','297',123948,'West Bengal',51);
INSERT INTO [Address] values(22,2,'Abbey Road','29',123948,'London',52);
INSERT INTO [Address] values(23,3,'Lombard Road','256',123976,'San Francisco',53);
INSERT INTO [Address] values(24,4,'Hollywood Walk of Fame','20',128748,'Los Angeles',54);


INSERT INTO [Address] values(25,5,'Le Rambla','297',123948,'Barcelona',55);
INSERT INTO [Address] values(26,6,'Orchard Road','256',123948,'Singapore',56);
INSERT INTO [Address] values(27,7,'Khao San Road','297',123948,'Bangkok',57);


INSERT INTO [Address] values(28,8,'Wall Street','287',123948,'New York',58);
INSERT INTO [Address] values(29,9,'Via Dolorosa','297',123878,'Abc',59);
INSERT INTO [Address] values(30,10,'Champ-Elysees','7',1283948,'France',60);

INSERT INTO Country values(51,'India');
INSERT INTO Country values(52,'US');
INSERT INTO Country values(53,'Canada');
INSERT INTO Country values(54,'Germany');
INSERT INTO Country values(55,'France');
INSERT INTO Country values(56,'Brazil');
INSERT INTO Country values(57,'Indonesia');
INSERT INTO Country values(58,'Italy');
INSERT INTO Country values(59,'Belgium');
INSERT INTO Country values(60,'Austria');

INSERT INTO [Order] values(101,1,DATEADD(yyyy,-2,GETDATE()));
INSERT INTO [Order] values(102,2,DATEADD(yyyy,-1,GETDATE()));
INSERT INTO [Order] values(103,3,DATEADD(yyyy,-3,GETDATE()));
INSERT INTO [Order] values(104,4,DATEADD(yyyy,-4,GETDATE()));
INSERT INTO [Order] values(105,5,DATEADD(yyyy,-2,GETDATE()));
INSERT INTO [Order] values(106,6,DATEADD(yyyy,-2,GETDATE()));
INSERT INTO [Order] values(107,7,DATEADD(yyyy,-5,GETDATE()));
INSERT INTO [Order] values(108,8,DATEADD(yyyy,-6,GETDATE()));
INSERT INTO [Order] values(109,9,DATEADD(yyyy,-2,GETDATE()));
INSERT INTO [Order] values(110,10,DATEADD(yyyy,-1,GETDATE()));

INSERT INTO Item values(201,101,'This is dummy desc1','iPone',20,1000);
INSERT INTO Item values(202,102,'This is dummy desc2','Samsung',10,1000);
INSERT INTO Item values(203,104,'This is dummy desc1','Realme',2,100);
INSERT INTO Item values(204,105,'This is dummy desc1','Charger',35,1000);
INSERT INTO Item values(205,101,'This is dummy desc1','Headphone',25,5000);
INSERT INTO Item values(206,106,'This is dummy desc1','Nothing',28,1000);


INSERT INTO Item values(207,107,'This is dummy desc1','OnePlus',29,1000);
INSERT INTO Item values(208,108,'This is dummy desc1','Shampoo',2000,1000);
INSERT INTO Item values(209,109,'This is dummy desc1','Cream',20,1000);
INSERT INTO Item values(210,110,'This is dummy desc1','Abc',20,1000);


INSERT INTO Item values(211,111,'This is dummy desc3','Consitioner',2,1000);
INSERT INTO Item values(213,110,'This is dummy desc3','AC',30,50000);

///////////////////////QUESTION 4/////////////////////////////

UPDATE Item set description='Desc Updated' where item_id=210;
DELETE FROM Item where item_id=213;
SELECT * FROM Item;
SELECT * FROM Country;
SELECT * FROM Client;
SELECT * FROM [Address];
SELECT * FROM [Order];


///////////////////////QUESTION 5/////////////////////////////
CREATE PROCEDURE JoinTheeTableNew
AS BEGIN
SELECT * FROM [Address] a JOIN Client c ON a.client_id=c.client_id
JOIN [Order] o ON O.client_id=c.client_id
END;
EXEC JoinTheeTableNew;

///////////////////////QUESTION 6/////////////////////////////

CREATE PROCEDURE JoinTwoTable
@amount int
AS BEGIN
SELECT * from Item i JOIN [Order] o ON i.order_id=o.order_id where i.amount=@amount;
END;

EXEC JoinTwoTable 1000;











Notes
I'm Feeling Lucky

If you like notes.io, please support us via Cryptocurrency(Ether,Doge,Monero)

Direct Message for Whatsapp

Paste Keyboard iOS - Quick Replies

v 2.7.3

We'd love to hear from you. Please email us at [email protected]

Copyright 2022 Metromedya

ios uygulama geliştirme