--create database database_name
create database CompanyDB
use CompanyDB
--create table table_name(column_name1 size,coli.....)
create table Employee(ieid int,vename varchar(25),vdesignation varchar(25),mSalary
money,vemail varchar(30),nContact numeric(10))
--to display table structure
sp_help Employee
--to display structure of the database
sp_helpdb CompanyDB
--To insert record
insert into Employee values(101,'Nidhi','Software
Engineer','20000','
[email protected]',8987655678)
insert into Employee(ieid,vename,vdesignation,mSalary)
values(102,'Sakshi','Software Engineer','19000')
insert into Employee
values(106,'Ajay','Deisgner','25000','
[email protected]',8987655678,'Vashi'),
(107,'Prasad','Developer','48000','
[email protected]',8987655678,'Turbhe'),
(105,'Snehal','Developer','60000','
[email protected]',8987655678,'Panvel'),
(108,'Anil','Tester','30000','
[email protected]',8987655678,'Vashi'),
(109,'Payal','Designer','34000','
[email protected]',8987655678,'Thane'),
(110,'Ashwini','Tester','24000','
[email protected]',8987655678,'Thane')
select * from Employee
select vename,msalary from Employee
--alter
--1.Add new column
alter table employee add City varchar(20)
update Employee set city='Vashi' where ieid=101
update Employee set mSalary=mSalary+2000
--2.Remove existing column
alter table employee drop column city
--3.Change datatype of the column
alter table employee alter column city varchar(40)
sp_help employee
--rename column name
sp_rename 'Employee.city','vCity','COLUMN'
--rename table
sp_rename 'old_table','New_table_name'
--drop table
drop table table_name
--truncate table
truncate table table_name
--delete records
delete from Employee
delete from Employee where ieid=105
select * from Employee
--grant
grant insert,update ,delete on employee to user_name
--revoke
revoke insert,update,delete,select on employee from user_name
--select with alias
select vename as 'Ename',mSalary as Salary from Employee
select vename 'Ename' from Employee
select '[Ename]'=vename from Employee
--Relational operators
use master
select * from Employee where mSalary>10000
select * from Employee where mSalary<25000
select * from Employee where mSalary<>25000
select * from Employee where mSalary!=25000
select * from Employee where msalary in(22000,21000)
select * from Employee where msalary not in(22000,21000)
select * from employee where msalary between 20000 and 23000
select * from employee where vdesignation='Software Engineer' and msalary=25000
select * from employee where vdesignation='Software Engineer' or msalary=25000
select * from employee where vemail is null
select * from employee where vemail is not null
select * from employee order by msalary desc
--Aggregate functions
select min(msalary) from employee
select max(msalary) from employee
select sum(msalary) from employee
select avg(msalary) from employee
select count(vemail) from employee
--group by
select count(ieid),vdesignation from employee group by vdesignation
--having clause
select count(ieid),vdesignation from employee group by vdesignation having
vdesignation='Developer'
--top
select top 2 * from employee
select top 2 vename from employee
