MS SQL Server 2008 (basic statement codes)

MS SQL SERVER 2008

CREATING TABLE

create table customerss
(
c_id int not null,
c_fname varchar(50) not null,
c_lname varchar(50) not null,                                                            
c_middle varchar(50) not null,
c_gender varchar(10) not null
)



CREATING TABLE with PRIMARY KEY

create table customerss
(
c_id int primary key not null,
c_fname varchar(50) not null,
c_lname varchar(50) not null,                                                            
c_middle varchar(50) not null,
c_gender varchar(10) not null
)

INSERT MULTIPLE ROWS

insert into customerss(c_id,c_fname,c_lname,c_middle,c_gender)
values 
(01,'Myname','Mysurname','Mymiddlename','male'),                            
(02,'Myname2','Mysurname2','Mymiddlename2','male2')


UPDATE STATEMENT

update table_name
set column_name='value'
where column_name='value'


DELETE STATEMENT

delete from table_name
where column_name='value'


SELECT STATEMENT (using AND / OR)

select * from table_name
where column_name='value' AND column_name='value'


select * from table_name
where column_name='value' OR column_name='value'



****************************************************************


2011 IT DEPARTMENT
2022 HTM DEPARTMENT
2033 BUSINESS ADMINISTRATION DEPARTMENT
2044 ENGINEERING DEPARTMENT
2055 NURSING DEPARTMENT
2066 ACCOUNTING
2077 STAFF


****************************************************************


1011 2500 1500 500
1012 500   1000 250
1013 4000 750   300
1014 3750 900   450
1015 1400 400   450
1016 500   550   4500
1017 750   345   670
1018 600   789   300
1019 2350 580   6000
1020 7500 200   150


****************************************************************

1012 SAMANTHA,FERLIN,FLORES,ACCOUNTANT,30000,ACCOUNTING
1013 JENNICA,GARCIA,JOSE,TENANT,10000,ACCOUTING
1014 JESSICA,BUENAFE,JAMLIG,CLERCK,15000,STAFF
1015 JOSELITO,FERNANDO,MADRIA,INSTRUCTOR,40000, IT DEPARTMENT
1016 JOANNE,MATIAS,OLIVER,DEAN,50000,BUSINESS ADMINISTRATION DEPARTMENT
1017 XIENNA-ANN,ALFARO,DEL ROSARIO,PROFESSOR,35000,HRM DEPARTMENT
1018 NICETO,GONZALES,ENRIQUEZ,DEAN,30000,ENGINEERING DEPARTMENT
1019 REYNALDO, JOSE, GUANIO,STAFF,30000,STAFF
1020 SAMIEL,DE VERA, SURATOS,DEAN,30000,NURSING DEPARTMENT

select TBLEMPLOYEE.EMP_LNAME,TBLEMPLOYEE.EMP_SALARY,TBLDEDUCTION.SSS,TBLDEDUCTION.PAG_IBIG,TBLDEDUCTION.PHILHEALTH,

((TBLEMPLOYEE.EMP_SALARY)-(TBLDEDUCTION.SSS+TBLDEDUCTION.PAG_IBIG+TBLDEDUCTION.PHILHEALTH)) as

total_salary

from TBLEMPLOYEE,TBLDEDUCTION

where TBLEMPLOYEE.EMP_ID = TBLDEDUCTION.EMP_ID