MYSQL
ORACLE- DEVELOPED BY ORACLE
CORPORATION
IT IS AN OBJECT RELATIONAL
DATABASE MANAGEMNT SYSTEM
I.
SQL - STRUCTURED QUERY ANALYSER
II.
RDBMS - RELATION DATABASE MANAGEMENT SYSTEM
III.
DBMS - DATABASE
MANAGEMENT SYSTEM
Structured Query Language - Introduced By IBM .First
Commercially Available SQL RDBMS Was Introduced In 1979 , By Oracle
Corporation.
BENIFITS
OF SQL
1)
Flexible , Powerful , Easy To Learn
2)
Non-Procedural Language
3)
Querying Data, Creating, Updating, Replacing Objects
& Inserting , Updating And Deleting Rows
Division
Of SQL
Data
Definition Language (DDL)
A.
create
B.
alter - add , delete
C.
drop
Data
Manipulation Language (DML)
A.
select
B.
insert
C.
delete
D.
update
Data
Control Language (DCL)
A.
grant
B.
revoke
C.
commit
D.
roll back
working
with database and tables
Data
Definition Language (DDL)
create
database
ex : create database amir
drop
database
ex : drop database amir
Table
creation
ex : create table kumar(sno int,name
char(15),place char(15))
Alter
table
ex : alter table kumar add district char(20)
drop
table
ex : alter table kumar drop column district
Data
Manipulation Language (DML)
Insert
: (adding rows to a table)
ex : insert into kumar values(1,'lavanya','theni')
Select
: ( all the rows and column from a table)
ex : select * from kumar
Deleting
data from a table
To
delete entire rows in a table
ex : delete
from kumar
TO delete a specified rows in a table
ex : delete from kumar where name = 'lavanya'
Data
Control Language (DCL)
Commit
This command is used to commit(write to
db) the transaction done by the DML
Rollback
It will rollback the transaction and will not commit the
change to the DB
EXAMPLE
:1
CREATE DATABASE AMIR
CREATE
TABLE KUMAR(SNO INT,NAME CHAR(15),PLACE CHAR(15))
INSERT
INTO KUMAR VALUES(1,'LAVANYA','THENI')
INSERT
INTO KUMAR VALUES(2,'ARJUN','ANDIPATTI')
INSERT
INTO KUMAR VALUES(3,'BALA','MADURAI')
|
1 |
SNO |
NAME |
PLACE |
|
2 |
1 |
LAVANYA |
THENI |
|
3 |
2 |
ARJUN |
ANDIPATTI |
|
4 |
3 |
BALA |
MADURAI |
SELECT*FROM KUMAR
CREATE
TABLE RAGU(SNO INT,NAME CHAR(15),SALARY INT)
INSERT
INTO RAGU VALUES(1,'JOY','65000')
INSERT
INTO RAGU VALUES(2,'PUGAL','38000')
INSERT
INTO RAGU VALUES(3,'KALAI','89000')
SELECT*FROM
RAGU
|
1 |
SNO |
NAME |
SALARY |
|
2 |
1 |
JOY |
65000 |
|
3 |
2 |
PUGAL |
38000 |
|
4 |
3 |
KALAI |
89000 |
CALCULATING
RESULT
SELECT NAME,SALARY,SALARY*12 AS 'ANNUAL SALARY' FROM RAGU
ISNULL
(EXPRESSION,VALUE)
SELECT NAME,SALARY,ISNULL
(SALARY,0)+500 FROM RAGU
|
SNO |
NAME |
SALARY |
(NO COLUMN NAME) |
|
1 |
JOY |
65000 |
65500 |
|
2 |
PUGAL |
38000 |
38500 |
AGGREGATE FUNCTION
SELECT SUM(SALARY) AS SUM,AVG(SALARY) AS AVERAGE,MAX(SALARY) AS
MAXIMUM,MIN(SALARY) AS MINIMUM FROM RAGU
|
SUM |
AVERAGE |
MAXIMUM |
MINIMUM |
|
65000 |
30000 |
35000 |
30000 |
T - SQL FUNCTION
(TRANSACT STRUCTURED QUERY LANGUAGE)
SCALAR FUNCTION
v
NUMERIC FUNCTION
v
STRING
FUNCTION
v
DATE&TIME
FUNCTION
v
SYSTEM
FUNCTION
v
CALCULATING
FUNCTION
v
CONVERSION
FUNCTION
v
OTHER
FUNCTION
NUMERIC FUNCTION
1) ABS(NUMERIC-EXPRESSION)
SELECT ABS(-25.75) = 25.75
2) COS,SIN,COT,TAN
SELECT COS(0) = 1.0
3) EXP (FLOAT-EXPR)
SELECT EXP(1) = 2.7182818284590451
4) LOG (FLOAT- EXPR)
SELECT LOG(10)=2.3025850929940459
5) PI()
SELECT PI() = 3.1415926535899793
6) SIGN (NUMERIC - EXPR)
SELECT SIGN(123) = 1
SELECT SIGN(-123) = -1
SELECT SIGN(0) = 0
7) SQRT(NUMERIC - EXPR)
SELECT SQRT(4) =2
8) CEILING (NUMERIC - EXPR)
SELECT CEILING(123.65) =124
9) FLOOR (NUMERIC - EXPR)
SELECT FLOOR(123.65) = 123
10) DEGREES (NUMERIC - EXPR)
SELECT DEGREES (9) = 515
(DEGREES TO RADIUS)
11) RADIUS(NUMERIC - EXPR)
SELECT RADIUS (515) = 8 (RADIUS
TO DEGREES)
12)ROUND(NUMBER,LENGTH,[FUNCTION])
SELECT ROUND(12.5,0) = 13.0
SELECT ROUND(12.499,1) = 12.500
13) POWER (NUMERIC -EXPR)
SELECT POWER(5,3) = 125
STRING FUNCTION
1) ASCII (CHAR - EXPR)
SELECT ASCII('a') = 97
2) CHAR(INTEGER - EXPR)
SELECT CHAR(97) = a
3)
CHARINDEX('PATTERN',-EXPR)
SELECT CHARINDEX('N','MONITER') = 3
4) LOWER (CHAR-EXPR)
SELECT LOWER('APPLE')= apple
5) UPPER (CHAR-EXPR)
SELECT UPPER('apple') = APPLE
|
SELECT
LTRIM('DEVICE') =
7)
RTRIM(CHAR-EXPR)
|
8) REVERSE (CHAR-EXPR)
SELECT REVERSE('HARDWARE') = ERAWDRAH
9) REPLICATE(CHAR-EXPR,INTEGER-EXPR)
SELECT REPLICATE('HELLO',3) =HELLO HELLO HELLO
10) LEFT(CHAR-EXPR,INTEGER-EXPR)
SELECT LEFT('HAI COMPUTER',8) = HAI COMP
11) RIGHT(CHAR-EXPR,INTEGER-EXPR)
SELECT LEFT('HAI COMPUTER',5) = PUTER
12)REPLACE('STRING EXPRESSION)
SELECT REPLACE('ROHITH','H','K')=ROKITH
13)LEN(CHAR-EXPR)
SELECT (LEN,'COMPUTER') = 8
14)SUBSTRING(CHAREXPR,START,LENGTH)
SELECT SUBSTRING('HAI WELCOME',5,9)
=
DATE FUNCTION
1)
DATEPART(DATEPART,DATE)
SELECT DATEPART (DD,GETDATE()) = 12
SELECT DATEPART (MM,GETDATE()) = 08
SELECT DATEPART (YY,GETDATE()) = 2013
SELECT DATEPART (DD,'10-5-2010') = 5
SELECT DATEPART (MM,'10-5-2010') = 10
SELECT DATEPART (YY,'10-5-2010') = 2010
2) DATENAME(DATEPART,DATE)
SELECT DATENAME(DD,'10-5-2010')=5
SELECT DATENAME(MM,'10-5-2010')=OCTOBER
SELECT DATENAME(YY,'10-5-2010')=2010
3) ISDATE (EXPRESSION)
SELECT ISDATE('13-3-2010')=0
SELECT ISDATE('12-3-2010')=1
4) DATEDIFF(DATEPART,DATE1,DATE2)
SELECT DATEDIFF (DD,'5-10-2000','5-10-2011') = 4017
SELECT DATEDIFF(MM,'5-10-2000','5-10-2011')=132
SELECT
DATEDIFF(YY,'5-10-2000','5-10-2011') = 11
SYSTEM FUNCTION
1) DB-NAME()
SELECT DB-NAME() = MASTER
2) DB-ID([DBNAME])
SELECT DB-ID('MASTER')
= 1
3) DATALENGTH('EXPRESSION')
SELECT DATALENGTH('MASTER')=6
ADVANCED QUERY CONCEPT
UNION OPERATOR
IT ALLOWS US TO COMBINE THE RESULT OF
TWO OR MORE QURIES INA SINGLE RESULT SET
EX : SELECT NAME FROM RAGU UNION SELECT NAME FROM KUMAR
UNION ALL
IT SELECT ALL VALUES
JOINS
JOINS ARE USED TO RETRIEVE AND MANIPULATE
DATA FROM MORE THAN ONE TABLE IN A SINGLE SELECT STATEMENT
EQUI JOIN
AN EQUIJOIN IS A JOIN IN WHICH THE COLUMN
VALUES BEING COMPARED ARE EQUAL.IT PRODUCED REDUNDANT COLUMN INFORMATION
BECAUSE THE JOIN COLUMN IS DISPLAYED TWICE.
RAGU
|
SNO |
NAME |
PLACE |
|
1 |
ARUNYA |
TRICHY |
|
2 |
MADHU |
THENI |
|
3 |
KAVI |
ANDIPATTI |
KAVIN
|
SNO |
NAME |
PLACE |
REGNO |
PHONE |
|
1 |
SARANYA |
THENI |
111 |
8932648763 |
|
2 |
DHIBAKAR |
ANDIPATTY |
222 |
7436464646 |
|
3 |
NAGARAJ |
MADURAI |
333 |
6456457647 |
EX : SELECT * FROM RAGU INNER JOIN KAVIN ON
RAGU.SNO=KAVIN.SNO
|
SNO |
NAME |
PLACE |
SNO |
NAME |
PLACE |
REGNO |
PHONE |
|
1 |
ARUNYA |
TRICHY |
1 |
SARANYA |
TRICHY |
111 |
8932648763 |
|
2 |
MADHU |
THENI |
2 |
DHIBAKAR |
THENI |
222 |
7436464646 |
|
3 |
KAVI |
ANDIPATTI |
3 |
NAGARAJ |
ANDIPATTI |
333 |
6456457647 |
NATURAL JOIN
EX: C1.SNO,C2.NAME,C1.NAME,C2.SNO
FROM RAGU C1 JOIN KAVIN C2 ON C1.SNO=C2.SNO
|
RAGU |
KAVIN |
RAGU |
KAVIN |
|
SNO |
NAME |
NAME |
SNO |
|
1 |
SARANYA |
ARUNYA |
1 |
|
2 |
DHIBAKAR |
MADHU |
2 |
|
3 |
NAGARAJ |
KAVI |
3 |
SELF JOIN
EX:SELECT C1.SNO,C2.NAME FROM
RAGU C1 INNER JOIN RAGU C2 ON C1.SNO=C2.SNO
|
SNO |
NAME |
|
1 |
ARUNYA |
|
2 |
MADHU |
|
3 |
KAVI |
EX:SELECT C1.SNO,C2.NAME FROM
RAGU C1 INNER JOIN RAGU C2 ON C1.SNO=C2.SNO WHERE C1.NAME='ARUNYA'
T -
SQL PROGRAMMING
DECLARE STATEMENT
THIS
IS USED TO CREATE A LOCAL VARIABLE
SYNTAX
DECLARE @ VARIABLE_NAME DATA_TYPE
EX: DECLARE @ NAME CHAR(20),@
tamil int
SET STATEMENT
TO SET OR ASSIGN A VALUE TO
A VARIABLE
SYNTAX
SET @ VARIABLE_NAME =
expression
EX: SET @ NAME='JEEVA'
SET @ TAMIL=100
EXAMPLE 1:
Declare
@x int,@y int,@z int
Set @x=10
Set @y=20
Set @z=@x+@y
Print 'addition
='+convert(varchar,@z)
EXAMPLE 2:
Declare
@x char(20),@y
char(20)
Set @x='jai'
Set
@y='theni'
Print
'name='+@x
Print 'place='+@y
CONDITIONAL
PROCESSING
IF STATEMENT
EXAMPLE 3:
Declare
@x int,@y int
Set @x=10
Set @y=10
If @x=@y
Print
'result='+'equal'
IF ELSE STATEMENT
EXAMPLE 4:
Declare
@x int,@y int
Set @x=10
Set @y=20
If(@x>@y)
Begin
Print'result='+'x
is big'
End
Else
Print'result'='+'y
is big'
IF ELSE IF STATEMENT
EXAMPLE 5:
Declare
@n int
Set @n=1
If(@n=1)
Print 'one'
Else if (@n=2)
Print 'two'
Else if (@n=3)
Print 'three'
Else
Print 'invalid
name'
WHILE STATEMENT
EXAMPLE 6:
Declare
@i int
Set @i=0
While @i>=15
Begin
select @i=@i+1
If @i=7 break
Print @i
End
CONTINUE STATEMENT
EXAMPLE 7:
Declare
@i int
Set @i=0
While @i<=15
Begin
select @i=@i+1
if @i/2=0
continue
print @i
end
0 comments:
Post a Comment