Practice, Sql Server

Practice

create table customers(customerID int,customerName varchar(30),contactName varchar(20),address varchar(20),city varchar(20),postalcode int,country varchar(30),salary int);
insert into customers(customerID,customerName,contactName,address,city,postalcode,country,salary)values('1','Anmol','Gupta','Awasvikas','Bareily','4004','India','21000');
insert into customers(customerID,customerName,contactName,address,city,postalcode,country,salary)values('2','Pari','Singh','GnadhiMurti','Patna','4005','India','22900');
insert into customers(customerID,customerName,contactName,address,city,postalcode,country,salary)values('3','Sona','Sinha','Rajpurroad','Dehradun','4006','Germany','24000');
insert into customers(customerID,customerName,contactName,address,city,postalcode,country,salary)values('4','Mona','Sharma','Laxminagar','Delhi','4007','Mexico','24789');
insert into customers(customerID,customerName,contactName,address,city,postalcode,country,salary)values('5','Rimpy','Lakhanpal','Mgroad','Delhi','4008','London','35000');
insert into customers(customerID,customerName,contactName,address,city,postalcode,country,salary)values('6','Piyush','Kumar','DasaiKunj','Pune','4009','America','46000');

create table suppliers(supplierID int,supplierName varchar(30),contactName varchar(20),address varchar(20),city varchar(20),postalcode int,country varchar(30),phone varchar(10));
insert into suppliers(supplierID,supplierName,contactName,address,city,postalcode,country,phone)values('1','Exotic','Cooper','Gilbert','London','40041','India','2100067898');
insert into suppliers(supplierID,supplierName,contactName,address,city,postalcode,country,phone)values('2','Cajun','Burke','PoBox','Tokyo','40052','Germany','2290087698');
insert into suppliers(supplierID,supplierName,contactName,address,city,postalcode,country,phone)values('3','Kelly','Murphy','Oxford','Melbourn','40063','UK','2400098667');
insert into suppliers(supplierID,supplierName,contactName,address,city,postalcode,country,phone)values('4','Tokyo','Yoshi','Sekimal','Osaka','40074','Mexico','2478978963');
insert into suppliers(supplierID,supplierName,contactName,address,city,postalcode,country,phone)values('5','Las','Valle','Calle','Manchester','40085','USA','3500012345');

select * from suppliers;

select * from customers;
select * from customers where customerID='1';
select * from customers where country='India';
select customerName,city from customers;
select distinct country from customers;
select count(distinct customerId) from customers;
select * from customers where city='Delhi';
select * from customers where city='Patna' and postalcode='4005';
select * from customers where city='Bareily' or city='Delhi';
select * from customers order by country;
select * from customers order by country desc;
select * from customers order by country desc,city desc;
insert into customers(customerID,customerName,contactName,address,city,postalcode,country)values('7','Punam','Kumari','PatelNagar','Ranchi','4010','UAE');
select * from customers;
update customers
set contactName='Bhatiya',city='Dhanbad' where customerID='7';
select * from customers;
delete from customers where customerID='1';
select * from customers;
select Min(salary) from customers;
select Max(salary) from customers;
select count(customerID) from customers;
select Avg(salary) from customers;
select sum(salary) from customers;
select count(*) from customers where salary='46000';
select * from customers where country IN('India','Germany','Mexico');
SELECT * FROM customers
WHERE Country NOT IN ('Germany', 'India', 'Mexico');

SELECT * FROM customers
WHERE Country IN(select country from suppliers);


run \| edit \| history \| help	0