practice sql_12AUG_Upddated, Sql Server

practice sql_12AUG_Upddated

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

Create table Family(
ID integer not null,
name varchar(255) not null,
City varchar(255) not null);

Insert into Family(ID,name,City)
Values(1,'iqbal','Sangli'), (2,'mumtaz','sangli'),(3,'kari','sangli'),(4,'kajal','sangli');

Insert into Family(ID,name,City)
Values(5,'sankar','Chennai');

Select * from Family;

Select distinct City from Family;

Select name from Family where City='sangli';

Select name from Family where City='sangli' AND ID=2;

Select name from Family where City='sangli' or City='Chennai';

Select name from Family Where City='Sangli' OR City='Chennai' Order by ID;

Select name from Family Where City='Sangli' OR City='Chennai' Order by ID DESC;

Alter table Family
ADD Salary integer;

Select * from Family;

Update Family Set Salary=1000 where id=1;
Select * from Family;

Update Family Set Salary=10000 where City='Sangli';

Select * From Family;

Select * from Family where Salary is not null;

Insert into Family(ID,name,City,Salary) Values(6,'Manvitha','HYD',5000000),(7,'Sudha','Trichy',9000);

Select * From Family;

Alter table Family
Add Age integer;

Select Salary as fam_sal from Family;

Select * from Family;
Select * from family;

Alter table Family
Drop column ID;

Select * from Family;

Alter table Family
Drop Column Salary;

Select * from Family;

Delete from Family where City = 'Trichy';
Select * from Family;

create table Stud (ID integer, Name varchar(255), city varchar(255), C_nm varchar(255));

insert into Stud(ID, Name, city, C_nm) values (1,'Prathmesh','Mumbai','BCS2'),(2,'Riddhi','sangli','MCA2'),(3,'Saloni','sangli','BCS3');
select * from Stud;

Alter table Stud 
Add Age integer;

Select * from Stud;
 
update Stud set Age=23;

select * from Stud;

Select Name,Age from Stud where exists (Select City From Stud where ID between 1 and 3);

select * from Stud;

select min(ID) as min_id  from Stud;

ALter table Family
Add Salary integer;

update Family Set Salary = 5000 where City = 'Sangli';

Select * from Family;
Select Max(Salary) as HighestSalary from Family;

Select AVG(SALARY) as averageSal from Family;

Select name from Family where  City in ('HYD','Chennai');

Select City from Family where name in ('sankar','kari');

Select * from Family;
--select City into Stud from Family where id BETWEEN 1 and 3;

Alter table Family
Add id integer not null default 1;

Select * from family;

Update Family set id=2 where name ='mumtaz';
Update Family set id=3 where name ='kajal';
Update Family set id=4 where name ='kari';
Update Family set id=5 where name ='sankar';

Select * from Family;
select City into Stud1 from Family where id BETWEEN 1 and 3;
Select * from Stud1;

Insert into Family(name,City,id) 
select Name,City,ID from Stud;

Select * into Details from Stud where ID between 1 and 6;

Select * from Details;
 
/*ALTER TABLE Stud
Alter column  ID Drop not null;*/

Select * from Stud;
Select * from Family;

Select * from Details;
Select * from Stud;

Select * into FamiDetails from Family where City in ('Sangli','Chennai');
Select * from FamiDetails;

Drop table Details;

Create table Details( ID integer Unique, Name Varchar(255), Age integer Check(Age>18) );

Insert into Details(ID, Name,Age) Values(1,'Sankar',19);


run \| edit \| history \| help	0