SQL Bangla Tutorial .pdf


•

e u (SQL)

o

SQL 


o

SQL 

o
o

SQL sn
 

o

 

o
o

i (Insert)
pi 

o



o
o

 (Query)
 WHERE

o

QUERY IN

o
o

SQL Join
Left Join

o

Right Join





 u  |   ( Dat abase t ut or i al i n Bangl a)
 :
 я  

oi   !  a#।  e %&' (  e ) e( e
*  %&' я  ) + u
i  я +  ' ,,  o  -#*  .d  я
।'  + e0  oi   ) ,я ,я +t +t -# - ,  2  -i '  0
 & -# e 3 ( ,3,яn ,+3n i#(, ei -#*  2   6k 3 e )( u 
 u+ k     a- ii i#( 
 k ( 
   2     )  3  *
i   ii  я   e6  -# (  ,я ।e e я*  +  , ) e0 
,e - -# o +я ( i#(।
 eя# ( 3 2o# * e 2o#  RDBMS (Relational Database
Management Software)  e MySQL database software (      ,  (
e oi    #: ,c।e3<o 3 ORACLE,SQL Server,MS ACCESS i#(।ic  e* o
= + я  e = '  ' -o =  -o । )( MySQL   = + , e*  =
  >n я +। ) MySQL e6 ORACLE e  n p ei,20/25% +-#।

e u u  u  - %& ( SQL Tut or i al i n Bangl a)
 :
 яo  

SQL=Standard Query Language(uc k  ek ess-cuell)   e6 a n(query)я#

e e =k= #6* я.ei #6* я ( )  s  +
(Manipulate) e6 a# 
+ )-MySQL,SQL Server,Oracle i#(। e 4thgeneration language. e  +! G pg6
#6* я    s
 +  ) loop,branch.
e+& e u ,  MySQL Dat abase a0  1 2 3 %&  4।

e u ,    1
  e    )
     )
Query  )
 -     )
    J  )
 +  )
  ) i#(


1 e u 5
+ 6r &
 :
 я  

XAMPP Control Panel - Apache e6 MySQL
  + buя   e eL
 http://localhost/xampp/ 2 
   e e ( 
Tools e a' phpMyadmin e k
 

e ) uin0  e - MySQL   - =   3 i  +।ei uin  + 
 (
phpMyAdmin e u+ k  ।e e0 & я   , ei uin - + u ( k  
, 3,  J  i  +  += e ),+ n   eя*    )  

  ,
e  + +ei
+  skp   ' ,।
e ) uin   ( 
 -e eu # u+ k  ।

eu # k  + e k  ei  SQL n  ,।

e u
s5 u  ( SQL St at ement Tut or i al i n Bangl a)
 :
 я  

 = * я eu sn e d  )।) 
 eu sn 0 ( friends  e 
 k ,।
SELECT*FROM friends
eu n case sensitive  a-: ‘select*from friends’e6 SELECT*FROM friends ( i e
я o u+  ,।
SQL DML o DDM
e u
 di  =  1
DDL-Data Defination Language:e (  , i#(  ,।index(keys) G  ,,( 0
 '# 6)*   ,,  '# constraints R  ( , i#(।* t+! DDL statement *  ,
CREATE DATABASE-    ,
ALTER DATABASE-  m(  ,
CREATE TABLE-    ,

ALTER TABLE-  m(  ,
CREATE INDEX- INDEX  ,
DROP INDEX- INDEX  3 )
DML-Data Manipulation Language
Query (a n) e6 update SQL e DML a6= (  ,।
SELECT-  -    
UPDATE-  +(,*()
DELETE-  - -#  3 (
INSERT INTO-  -# J 

e u ,
 >2? ( SQL Dat abase Cr eat i on Tut or i al i n
Bangl a)
 :
 я  

MySQL e я =  * e     ,।CREATE DATABASE ei sn ( e0 
  ,।XAMPP
  3? -
  e6 buя   e eL   http://localhost/ 
en ( e e ( 
Tools e a' phpMyadmin e k   =& SQL # k  ।
e   
  (CREATE DATABASE designer) e6 go  k   # e   , *

e  ( (  designer  e  3।


  k 
e    e'  - + e - e0 k  #, 
i 
 sn 0 #,  
USE database_name( ) 0 #, 
 database_name e я* i 0 ( (।)


B
e apя   3 
 sn
DROP DATABASE database_name( ) 0  3
 database_name e я* i 0 ( (।)
e    3  ,  
 )  2 (undo) m ,i   - я0  ,
) pя0  3  (।

e u ,

  >2? u  (
u  ( SQL Tabl e Cr eat i on Tut or i al
i n Bangl a)
 :
 я  

  sn 0 m +
CREATE TABLE table_name( )    
 table_name e я* i 0 ( (।)u(,:
designer  ) 0  3  k  ( ( ( )e6 SQL # k  
 n0  
1.CREATE TABLE `designer`.`test` (
2.`id` INT( 10 )NOT NULL AUTO_INCREMENT PRIMARYKEY ,
3.`first name` VARCHAR( 15 ) NOT NULL ,
4.`last name` VARCHAR( 15 ) NOT NULL ,
5.`email` VARCHAR( 20 ) NOT NULL,
6.`course name` VARCHAR( 20 ) NOT NULL DEFAULT'WDAD',
7.`mobile` INT( 12 ) NOT NULL
8.)

e GO  k   # test  e   , ) designer database e a'।
 

u+  ##
p- i0 (  ,c test  e   ।2 i (  ,c  id  e 
i ) INT(10)
a-#: +! 6# , (id  +! 6#i , ) o 1,o 20 o 230 )e6 100 = 6# , e+ 3 NOT
NULL )  ei 0 2  )।+ AUTO_INCREMENT e  ,c + )( id  ( ,
 1 Y o  ।=& PRIMARY KEY e  e s   e * t+! e6 +o я
*।

e u i&C
s5 u  ( SQL I nser t St at ement i n Bangl a)
 : яo
 яo  

eu (SQL) ( +  e  e' (row) J  +।e e (row)e  , J 
+।):
INSERT INTO test (id, first name, last name, email, course name,mobile)
VALUES
(8, 'Rahman', 'Abidur', ' a_rahman@yahoo.com', ‘WDAD’, ‘01195229300’)

=  +d 3  J   ) ( e  u+ ) p0 2l я# # -।( id, first
name, last name e*  , e e0 2l)
2l
)( p0 2l # - , 
    J  ,
INSERT INTO test VALUES
‘WDAD’, ‘01195229300’)

(8,

'Rahman',

'Abidur',

' a_rahman@yahoo.com',

+
i = ' ( \    J  +।)
1.INSERT INTO test(firstname, lastname) VALUES (‘Harun', ‘Rashid')

 +d 3
i  e ( \  e   J  +।
1.INSERT INTO test SET id = 9

e  a## 2l*  2  2l # -।

e u pi  ? u  ( SQL Pr i mar y Key Tut or i al i n Bangl a)
 :
 я  

1.e (  p0 entries  a#/( (uniquely) 
h/=k  ,।
2.+ e  e'  pi  ,   ( +।)i  ei pi  i +  p0
(row)  a#/( (uniquely) 
h ।
3.)( e'  pi  ,   ,  *   e pi  .e  ei pi
 -।

5.pi  2 -।
5.  * 0u ) 0   )( i  first name  pi     e )(
 ,я  - ,  -o  -o e  -    )o m 3  ,
+ first name  last name  pi   e  16  +)n  e PRIMARY
KEY  )   o ) a# (unique) ,।eя# e  ) id number  PRIMARY
KEY ,   , ( iя id  o e ,।e   a + )  (first name,last name e
= = ,email e = = ii eL i#( -)  J ।

e u ,
 
,+
B ( SQL Tabl e Del et e Show Tut or i al i n
Bangl a)
 :
 я  

SHOW TABLE ei sn 0 ( e    ( )।
DESCRIBE TABLE ei sn (  s я+t ( )।) DESCRIBE test e0 
 0
(
TABLE ALTERATI ON
  +=+= MYSQL e +  2i  a a+= + )
1.alter table table_name add PRIMARY KEY (col_name,...)

2.alter table table_name ADD UNIQUE (col_name,...)
3.alter table table_name DROP PRIMARY KEY
4.alter table table_name ALTER [COLUMN] col_name {SET DEFAULT literal
|DROP DEFAULT}

e d +    )* , ,PRIMARY KEY   -  i#(  +।
1.alter table table_name drop column column_name

e table_name e я*    column_name e я*   ( oi  - 0  3
( +।
1.alter table table_name add column column_name data_type [NULL/not NULL]

e ( e     )*  +।e  * o।

e u
  u  (
u  ( SQL Quer y Tut or i al i n Bangl a)
 :
 я  

 -    e ( SELECT sn 0 #, ,।)
select

* from

test

e +  test  0 +  ,я  2।
*e я* )(    ( , = ' oi *  (।)
SELECT firstname,lastname FROM

test

SELECT sn (   ( \ a6=o   e ( + a-#: R  ( +  k + (

।)
select JOB, SAL from EMPLOYEES Limit 10

'  + e  employees  e  3   ,я  -i + kt u+ n0 10
0   (।

e u WHERE Clause ,
 
 :
 я  

e (    n    )।)
1.select JOB, SAL from EMPLOYEES where SAL > 1500

2 employees  )( salary 1500 e u+ ( -# e (।
1.SELECT * FROM Persons
2.WHERE City='Sandnes'

'  +  person   3 e + = ' ( -#
 ) sandnes =, - , u+ 

 ,।
SQL kk single quote e    e - - = *    - ।
6#(Numeric)    =    )।
e
SELECT * FROM Persons WHERE FirstName='Tove
e
SELECT * FROM Persons WHERE FirstName=Tove
6# kt e ,
e
SELECT * FROM Persons WHERE Year=1965
e
SELECT * FROM Persons WHERE Year='1965'
WHERE clause e condition R  ( 
 a+*  #,  )

R


R


AND e u(,:
1.SELECT * FROM Persons
2.WHERE FirstName='Tove'
3.AND LastName='Svendson'

OR e u(,:
1.SELECT * FROM Persons
2.WHERE FirstName='Tove'
3.OR FirstName='Ola'

AND,OR Operator e- #,  condition  ) +।)
u(,:
u(,
1.SELECT * FROM Persons WHERE
2.LastName='Svendson'
3.AND (FirstName='Tove' OR FirstName='Ola')

QUERY DI STI NCT
e  3   p # - +।
 n0 ( ei  p eY +
1.select distinct DEPTNO from EMPLOYEE

a-#: employee  - dept e m    ei + n  a  - i ei + n m n
e a - i? ei n0 ( ei  p eY m।
QUERY ORDERBY
1.select ENAME, DEPTNO, HIREDATE from EMPLOYEE
2.order by DEPTNO [asc], HIREDATE desc

e  ) *  ( *  3 - Y(ascending)e6 Y - 3(descending) я ( +।
QUERY BETWEEN
1.select EMPNO, ENAME, SAL from EMP where SAL between 1500 and 2500
2.select ENAME from EMP where HIREDATE between ’02-APR-81’ and ’08-SEP-81

e ( ( \ e + '#   a6= ( +।
1.SELECT productCode, FROM products
2.WHERE buyPrice NOT BETWEEN 20 AND 100

e AND i#( #,  QUERY  o (k  +।

e u
  i& ( SQL Quer y I N Tut or i al i n Bangl a)
 :
 я  

1.select *

from DEPT where DEPTNO in (20,30)

 ( + n (o )( + n m 20  30।
1.SELECT officeCode, city FROM offices
2.WHERE country IN ('USA','France')
3.
4.
5.SELECT officeCode, city FROM offices
6.WHERE country NOT IN ('USA','France')

e*  = s   ,  statement *  (i  a )।

a0n QUERY ( SQL Joi n Tut or i al i n Bangl a)
 :
 я  

a0n QUERY
U+
ek = )    SELECT statement ( e  -    e ( )।n  s
pg6 e kt   = '  e  e  -  (  e  a*  3 3 
 k -।i + SELECT statement e - SQL JOIN  m!  ( +।
e *d0 m +
1.SELECT column_list
2.FROM table_1
3.[INNER |LEFT |RIGHT] table_2 ON conditions_2
4.[INNER |LEFT |RIGHT] table_3 ON conditions_3
5.…
6.WHERE conditions

JOIN keyword 0 SQL statement e - #: , ( i  '  -    я# ) oi
*  '# ( \   '# m  u+  t  ,।*   '# e0 0 Keys(e*  Foreign key ) e '# m ) k -।
Primary Key ,c e ( 3  \))  p0 (row)я# a#(unique).

“Persons” 0 ( 
P_Id

LastName

FirstName

Address

City

1

Hansen

Ola

Timoteivn 10

Sandnes

2

Svendson

Tove

Borgvn 23

Sandnes

3

Pettersen

Kari

Storgt 20

Stavanger

e “Persons”  pi  ,c “P_Id”e  ,c ( 0 (row) “P_Id”o ei ,।ei “P_Id”i
( 0 row  (  )(o ( 0 row e  ei ,।e + 0 ( 
O_Id

OrderNo

P_Id

1

77895

3

2

44678

3

3

22456

1

4

24562

1

5

34764

15

k#   “O_Id”0 “Orders”  Primary Key e6 e “P_Id” ( “Persons”  #k(
p(=  ,3 (   i।ei  ( 0 '# “P_Id”0 '# e0 m 3।

u(, *  ' JOIN 3  e (
JOIN,LEFT JOIN,RIGHT JOIN,FULL JOIN
SQL I NNER JOI N
INNER JOIN   -i      ) = (  u+  t ।)
1.SELECT column_name(s)
2.FROM table_name1
3.INNER JOIN table_name2
4.ON table_name1.column_name=table_name2.column_name

u(,:
Persons table
P_Id

LastName

FirstName

Address

City

1

Hansen

Ola

Timoteivn 10

Sandnes

2

Svendson

Tove

Borgvn 23

Sandnes

3

Pettersen

Kari

Storgt 20

Stavanger

Orders table
O_Id

OrderNo

P_Id

1

77895

3

2

44678

3

3

22456

1

4

24562

1

5

34764

15

“P_Id”( ( 0 m ) k e - )(  #k(  (
i ( order )i ,  , INNER
JOIN #,     +।
1.SELECT Persons.LastName,Persons.FirstName,Orders.OrderNo
2.From Persons

3.INNER JOIN Orders
4.ON Persons.P_Id=Orders.P_Id
5.Order BY Persons.LastName

Result , e
LastName

FirstName

OrderNo

Hansen

Ola

22456

Hansen

Ola

24562

Pettersen

Kari

77896

Pettersen

Kari

44678

INNER JOIN  (row) 2: +d ) ( 0  '# +k e  -।)( Persons table e Orders table e  e (row)   -  kt i 0 (।

e u
V я& u  ( SQL Lef t Joi n Tut or i al i n Bangl a)
 :
 я  

e   (table1) -     )(o a# (table_2,table_3...) - e    -।)
1.SELECT column_name(s)
2.FROM table_name1
3.LEFT JOIN table_name2
4.ON table_name1.column _name=table_name2.column_name

'   u+  - #k e6 ( k (
i  
   ,
1.SELECT Persons.Lastname,FirstName,Orders.OrderNo
2.FROM Persons
3.LEFT JOIN Orders
4.ON Persons.P_Id=Orders.P_Id
5.ORDER BY Persons.LastName

Result , 
 
LastName

FirstName

OrderNo

Hansen

Ola

22456

Hansen

Ola

24562

Pettersen

Kari

77896

Pettersen

Kari

Svendson

Tove

44678

LEFT JOIN o 0 Left table(Persons) -  *   3 )(o Right table(Orders) e - e
  i।

e u i я& u  ( SQL Ri ght Joi n Tut or i al i n Bangl a)
 :
 я  

e0 a##  (table_2,table_3) -   )(o    - e    -।)
1.SELECT Persons.Lastname,FirstName,Orders.OrderNo
2.FROM Persons
3.RIGHT JOIN Orders
4.ON Persons.P_Id=Orders.P_Id
5.ORDER BY Persons.LastName

e6 e 22 
  (।
LastName

FirstName

OrderNo

Hansen

Ola

22456

Hansen

Ola

24562

Pettersen

Kari

77896

Pettersen

Kari

44678
34764

SQL FULL JOI N
e )(  #k k, e6 k ( - #k (
i , 
  SELECT statement #,  ,।
1.SELECT Persons.Lastname,FirstName,Orders.OrderNo
2.FROM Persons
3.FULL JOIN Orders
4.ON Persons.P_Id=Orders.P_Id
5.ORDER BY Persons.LastName

Result:
LastName

FirstName

OrderNo

Hansen

Ola

22456

Hansen

Ola

24562

Pettersen

Kari

77896

Pettersen

Kari

44678

Svendson

Tove
34764