create table pmyojana(eid int, ename varchar(50), age int default(18), detailby varchar(50) default('pmyojna') )
select *from pmyojana
insert into pmyojana (eid, ename) values(12, 'rana')
create table rasan(eid int primary key, ename varchar(50), age int default(18), members int default(5),details varchar(50) default('relife'))
select * from rasan
insert into rasan (eid, ename) values(101,'rama')
insert into rasan (eid, ename) values(202,'sama')
insert into rasan (eid, ename) values(303,'mama')
insert into rasan (eid, ename) values(404,'nama')
insert into rasan (eid, ename) values(505,'jama')
create table saman(orderno int,ordername varchar(50),anaj varchar(50) default('rice 5kg'),eid int foreign key references rasan(eid))
select *from saman
insert into saman (orderno, ordername, eid) values(1,'pahlalot',101)
insert into saman (orderno, ordername, eid) values(2,'dusralot',202)
insert into saman (orderno, ordername, eid) values(3,'tisralot',303)
insert into saman (orderno, ordername, eid) values(4,'chothalot',404)
insert into saman (orderno, ordername, eid) values(5,'pachmalot',505)
insert into saman (orderno, ordername, eid) values(6,'sixalot',606)
alter dbname emp modify name = employee
execute sp_renamedb 'oldDbname', 'newdbname'
execute sp_rename 'olcolumnname', 'newcolumnname'
select *from tablename
sp_help tablename
alter table tablename alter column columnname new datatype new size
alter table tablename add new columnname datatype
create table tablename()
select * from tablename
insert into tablename values()
update tablename set cn1= , cn2= '', where cn0=;
alter table tablename drop column columnname
constraints
default
primary key
unique
not null
foreign key
check
create table voter_table(voter_id int, voter_name varchar(50), voter_age int)
select *from voter_table
alter table voter_table alter column voter_name varchar(50) not null
alter table voter_table add unique(voter_id)
alter table voter_table drop constraint [UQ_voter_table index wala lete h system se]
alter table voter_table add foreign key (voter_id) references voter_table(voter_id)
alter table voter_table drop constraint foreign key (voter_id) references voter_table(voter_id)
alter table voter_table add check(voter_age>=18)
insert into voter_table values(2,'adil', 18)
insert into voter_table values(3,'raja', 22)
alter table voter_table add default 18 for voter_age;
alias
alias ko as likhte h sql me
select stdid as studentid from tablename
joins
inner join
left join
right outer join
full outer join
create table employee_tbl (empid int unique not null, emp_name varchar(50)not null, email varchar(50) not null, desgination varchar(50) not null)
select * from employee_tbl
select * from department
insert into employee_tbl values (11, 'amir','amir123@gmail.com','manager')
insert into employee_tbl values (12, 'amar','amar@gmail.com','it')
insert into employee_tbl values (13, 'usman','usman123@gmail.com','hr')
insert into employee_tbl values (14, 'umar','umar123@gmail.com','manager')
insert into employee_tbl values (11, 'anas','anas123@gmail.com','comp')
turncate table employee_tbl
create table department (dpt_id int unique not null, dpt_name varchar(50),salary varchar(50), empid int)
select * from department
insert into department values(111,'admin','5000',13)
--inner join
select *from
employee_tbl as a
inner join
department as b
on a.empid = b.empid;
select emp_name, desgination, salary from employee_tbl as a
inner join department as b
on a.empid = b.empid
No comments: