DATA ARE RAW FACTS
EX : CUSTOMER NAME IN BANK
TOTAL MARKS OBTAINED BY STUDENT
INFORMATION: AN ORGANIZED DATA IS CALLED INFORMATION
EX : CUSTOMER NAME WITH BALANCES.
STUDENT REG NO WITH TOTAL MARKS.
DATA HIERARCHY:
BIT IT MAY BE EITHER 0 OR 1
IT REPRESENTS THE SMALLEST PART OF INFORMATION THAT COMPUTER CAN PROCESS.
BYTE : IT IS GROUP OF 8 BITS IT REPRESENTS A DIGIT, LETTERS OR SPECIAL SYMBOL.
FILED: IT IS A GROUP OF CHARACTERS.
EMPNO ENAME SAL JOB
100 SMITH 800 CLERK
RECORD: IT IS A GROUP OF LOGICALLY RELATED FIELD.
FILE : GROUP OF LOGICALLY RELATED RECORDS OR IT IS A FLOW OF CHARACTERS.
DATABASE: IT IS A GROUP OF LOGICALLY RELATED DATA.
(OR)
IT IS THE COLLECTION OF DATA STORED IN A FILE ON DISC
CLASSIFICATION OF DATA BASE BASED ON SIZE
· NORMAL DATABASE:<10 GB
· LARGE DATABASE : 10 GB TO 100 GB
· VERY LARGE DATABASE : 100 GB TO 1000 GB
· EXTREMELY LARGE DATABASE :> 1000 GB
FILE MANAGEMENT SYSTEM
FILE : IT IS A STREAM OF CHARACTERS
DISADVANTAGES :
1. NOT SUPPORT VARIABLES DECLARATION TO STORE (OR) HOLD DATA.
2. WASTAGE OF MEMORY DUE TO DATA REDUNDANCY.
3. MULTIPLE USERS CANNOT ACCESS THE SINGLE FILE AT A TIME.
4. SEARCHING IS A DIFFICULT TASK.
5. INFORMATION ACCESS IS SLOW.
DATABASE MANAGEMENT SYSTEM (DBMS)
IT IS A SUIT OF SOFTWARE PROGRAM FOR CREATING, MAINTAINING & MANIPULATING THE DATA IN DATABASE. (OR)
IT IS A COLLECTION OF INTER RELATED DATA AND SET OF PROGRAM TO ACCESS THE DATA
IT ALLOWS THE ORGANIZATION TO STORE THE DATA IN ONE LOCATION FLOW, WHICH MULTIPLE USERS CAN ACCESS, THE DATA.
DIFFERENT DATABASE MODELS:
1. HIERARCHICAL MODEL
2. NETWORK MODEL
3. RELATIONAL MODEL
DATA IS ORGANIZED IN THE FORM OF TREE STRUCTURE WITH ONE LIMITATION THAT IS
“EVERY SUB NODE SHOULD HAVE ONLY ONE ROOT NODE
DRAWBACKS:
1. DATA REDUNDANCY
2. WASTAGE OF MEMORY
3.CROSS COMMUNICATION IS NOT POSSIBLE
2. NET WORK MODEL:
DATA IS ORGANIZED IN THE FORM OF ARBITRARY GRAPHS.
THERE IS NO GUARANTEE FOR ACCESS OF DATABASE WHENEVER THE DATABASE SIZE INCREASES.
CROSS COMMUNICATION IS POSSIBLE IN NDBMS.
IT CANNOT PROVIDE PROPER QUERY FACILITY, SO THAT WE HAVE TO BIG PROGRAMS EVEN FOR SMALL OPERATION.
3. RELATIONAL MODEL
IT USES THE COLLECTION OF TABLES TO REPRESENT BOTH THE DATA AND RELATIONSHIP AMONG THE DATA.
FEATURES OF RELATIONAL MODEL:
1. DATA IS STORED IN TABLES.
2. INTERSECTION OF ROWS AND COLUMNS WILL GIVE ONLY ONE VALUE.
3. RELATION AMONG DATA IS ESTABLISHED LOGICALLY.
4. THERE IS NO PHYSICAL LINK AMONG DATA
5. THERE IS NO DATA REDUNDANCY
6. HIGH SECURITY FOR DATA
7. IT SUPPORTS ANY TYPE OF DATA (EX: NUMBERS, NUMERIC, DATA, CHARACTER, DATE IMAGES ETC).
8. IT SUPPORTS NULL VALUES.
9. SUPPORT CODD RULES
10. IT SUPPORTS INTEGRITY CONSTRAINTS
11. MULTIPLE USERS CAN ACCESS DATA FROM ANY LOCATION.
NULL VALUE: IT IS UNKNOWN, UNASSIGNED AND UN COMPARABLE VALUE.
EX: 500 + NULL=NULL
ANY ARITHMETIC EXPRESSION CONTAINING NULL IS EVALUATED TO NULL
NULL IS NOT EQUAL TO ZERO OR NOT EQUAL TO SPACE.
RELATIONAL DATABSE: A DATABASE BASED ON RELATIONAL MODEL IS CALLED RELATIONAL DATABASE.
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS): IT IS A SUIT OF SOFTWARE PROGRAMS FOR CREATING, MARINATING AND MANIPULATING THE DATA IN THE RELATIONAL DATABASE.
DIFFERENT RDBMS PACKAGES:
Ø ORACLE
Ø SQL SERVER
Ø DB2
Ø SYBASE
SQL SERVER:
SQL SERVER 2000 IS AN RDBMS THAT USES TRANSACT-SQL TO SEND REQUESTS BETWEEN A CLIENT
COMPUTER AND A SQL SERVER 2000 COMPUTER. AN RDBMS INCLUDES DATABASES, THE DATABASE
ENGINE, AND THE APPLICATIONS THAT ARE NECESSARY TO MANAGE THE DATA AND THE COMPONENTS OF
THE RDBMS. THE RDBMS ORGANIZES DATA INTO RELATED ROWS AND COLUMNS WITHIN THE DATABASE.
THE RDBMS IS RESPONSIBLE FOR ENFORCING THE DATABASE STRUCTURE, INCLUDING THE FOLLOWING
TASKS:
■ MAINTAINING THE RELATIONSHIPS AMONG DATA IN THE DATABASE
■ ENSURING THAT DATA IS STORED CORRECTLY AND THAT THE RULES DEFINING DATA RELATIONSHIPS
ARE NOT VIOLATED
■ RECOVERING ALL DATA TO A POINT OF KNOWN CONSISTENCY IN CASE OF SYSTEM FAILURES
VERSIONS OF SQL SERVER:
1. IN 1993 MICRO SOFT AND SYBASE RELEASES 4.2 VERSION OF SQL SERVER FOR WINDOWS NT ENVIRONMENT.
2. 1995à MICROSOFT RELEASES SQL SERVER 6.0
3. 1996à MICROSOFT RELEASES SQL SERVER 6.5
4. 2000à MICROSOFT RELEASES SQL SERVER 2000
LATEST VERSION SQL SERVER 2005 NOT YET RELEASED.
FEATURES OF SQL SERVER 2000:
1. INTRODUCING SUPPORT FOR XML
2. USER DEFINED FUNCTION ARE INTRODUCED.
3. OLAP (ONLINE ANALYTICAL PROCESS) SERVICES AVAILABLE IN SQL SERVER 7.0 ARE NOW CALLED AS SQL SERVER 2000 ANALYSIS SERVICES
4. REPOSITORY COMPONENTS AVAILABLE IN SQL SERVER NOW CALLED META DATA SERVICES.
DATABASE SIZES:
· MS ACCESS à 2 GB
· SQL SERVER – 6.0 à 1 TB
· SQL SERVER – 7.0 à 1,048,516 TB 3
· SQL SERVER – 2000 à 1,048,516 TB 3
DATABASE IN SQL SERVER:
1) SYSTEM DATABASES
2) USERS DEFINED DATABASES
SYSTEM DATABASES: COMPLETE SYSTEM LEVEL INFORMATION SQL SERVER IS STORED IN SYSTEM DATABASES. SQL SERVER USES SYSTEM DATABASES TO OPERATE USER DATABASES.
USER DATABASES: THE DATABASES CREATED BY USER.
NORMALIZATION
ENTITY : A PERSON, PLACE OR EVENT ABOUT WHICH THE INFORMATION IS STORED IS CALLED ENTITY.
EX: EMPLOYEE, DEPARTMENT, STUDENT, CUSTOMER.
ATTRIBUTE: THE CHARACTERISTIC OR PROPERTY THAT DESCRIBES AN ENTITY IS CALLED ATTRIBUTE.
EX : EMPNO, ENAME, JOB ETC ARE ATTRIBUTE OF ENTITY EMPLOYEE
CUSTID, NAME, ADDRESS ARE ATTRIBUTE OF ENTITY CUSTOMER
FUNCTIONAL DEPENDENCY: IN A RELATION R, AN ATTRIBUTE B IS SAID TO BE FUNCTIONALLY DEPENDENT AN ATTRIBUTE A IF THE VALUE IN B IS UNIQUELY IDENTIFY BY THE VALUE IN A.
THE FUNCTION DEPENDENCY OF ‘B’ AN ‘A’ IS REPRESENTED AS FOLLOWS
R: AàB
EX: 1. SSN à NAME, ADDRESS, DATE OF BIRTH
IE A PERSON NAME, ADDRESS, DATE OF BIRTH
FUNCTIONALLY DEPENDENT ON SOCIAL SECURITY NUMBER(SSN)
2. VIN à MAKE, MODEL, COLOR
IE MAKE, MODEL & COLOR OF VEHICLE FUNCTIONALLY
DEPENDS ON VIN (VEHICLE IDENTIFICATION NUMBER)
PARTIAL FUNCTIONAL DEPENDENCY:
PARTIAL FUNCTIONAL DEPENDENCY IS THE FUNCTIONAL DEPENDENCY IN WHICH SOME NON-KEY COLUMNS DEPENDS ON PART OF KEY COLUMN OR PRIMARY COLUMN.
IN THE ABOVE TABLE EMP NAME, SAL DEPENDS ONLY ON EMP NO BUT THE COURSE COMPLETED DAT WILL DEPENDS ON BOTH EMP NO & COURSE TITLE TO IDENTIFY THE ROW UNIQUELY IN A TABLE EMP NO, COURSE TITLE SHOULD BE DECLARED AS PRIMARY KEY COLUMNS. BUT THE NON-KEY ATTRIBUTES NAME, SQL DEPENDS ONLY EMP NO, IE PART OF A KEY COLUMN THIS TYPE OF FUNCTIONAL DEPENDENCY IS CALLED IT PARTIAL FUNCTIONAL DEPENDENCY.
TRANSITIVE DEPENDENCY
FUNCTIONAL DEPENDENCY BETWEEN THE TWO NON-KEY ATTRIBUTES IS CALLED THE TRANSITIVE DEPENDENCY.
Ø IN THE ABOVE TABLE CUSTID IS IDENTIFIED AS KEY COLUMN
Ø BUT REGION DEPENDS ONLY ON SALESMAN, IS CALLED TRANSITIVE DEPENDENCY.
COLUMNS REGION AND SALESMAN IS CALLED TRANSITIVE DEPENDENCY.
NORMALIZATION ; IT IS THE PROCESS OF DECOMPOSING THE RELATIONS (TABLES) IN TO SMALLER AND WELL STRUCTERD RELATIONS TO MINIMIZE THE DATA REDUNDANCY.
(OR)
IT IS THE PROCESS OF DECIDING WHICH ATTRIBUTED HAS TO GROUPED TO AVOID DATA REDUNDANCY.
THERE ARE SOME RULES IN NORMALIZATION EACH RULE IS CALLED NORMAL FORM.
NORMAL FORMS:
1. FIRST NORMAL FORM (1NF)
2. SECOND NORMAL FORM (2NF)
3. THIRD NORMAL FORM (3NF)
FIRST NORMAL FORMS (1NF): MULTI VALUED ATTRIBUTED SHOULD BE REMOVED FROM THE TABLE.
EXPLANATION: IN THE ABOVE TABLE EMP COURSE TITLE, COURSE COMPLETED DATE ARE MULTI VALUED ATTRIBUTES SINCE AT THE INTERSECTIONS OF ROW AND COLUMN THEY WILL GIVE MOVE THAN ON VALUE. TO BRING THE ABOVE TABLE TO INF MULTI VALUED ATTRIBUTED SHOULD BE REMOVED BY FILLING THE EMPTY FIELD WITH SUITABLE DATA AS SHOWN BELOW.
SECOND NORMAL FORM (2NF) : THE RELATION SHOULD BE IN INF AND THE PARTIAL FUNCTIONAL DEPENDENCY SHOULD BE REMOVED.
IN THE ABOVE TABLE EMP NO, COURSE TITLE ARE IDENTIFIED AS KEY COLUMN BUT NON-KEY COL LIKE NAME AND SQL WILL DEPENDS.
TO BRING THE TABLE TO SECOND NORMAL FORM THE NORMAL FUNCTIONAL DEPENDENCY SHOULD BE REMOVED BY DIVIDING THE TABLE INTO TWO TABLES AS SHOWN ABOVE.
THIRD NORMAL FORM (3NF): THE TABLE SHOULD BE IN THE 2NF AND TRANSITIVE DEPENDENCY SHOULD BE REMOVED.
EX: CUSTOMER
· CUSTID IS IDENTIFIED AS KEY-COLUMN BUT REGION DEPENDS ON SALESMAN.
· THE FUNCTIONAL DEPENDENCY BETWEEN NON-KEY ATTRIBUTES SALES MAN & REGION IS CALLED TRANSITIVE DEPENDENCY.
· TO BRING THE TABLE TO THIRD NORMAL FORM, TRANSITIVE DEPENDENCY SHOULD BE REMOVED BY DIVIDING THE TABLE INTO TWO TABLE AS SHOWN BELOW
SYSTEM AND SAMPLE DATABASE IN SQL SERVER:
WHEN SQL SERVER IS INSTALLED IN A SYSTEM SETUP WILL CREATES THE FOLLOWING SYSTEM AND SAMPLE DATABASE.
SYSTEM DATABASES:
1. MASTER
2. MODEL
3. TEMPDB
4. MSDB
SAMPLE DATABASES:
1. PUBS
2. NORTH WIND
1. MASTER DATABASE : IT CONTAINS SYSTEM LEVEL INFORMATION OF SQL SERVER. ALL LOGINS AND DATABASE INFORMATION IS STORED IN MASTER DATABASE.
2. MODEL DATABASE : IT IS A TEMPLATE FOR USER DATABASES. WHEN USES IS CREATED A DATABASE THE COMPLETE INFORMATION WHICH IS THERE IN MODEL DATA WILL BE COPIED INTO THE USER DATABASE.
3. TEMPDB: IT FOR HOLDING TEMPORARY OBJECTS WHICH ARE CREATED WHEN SQL SERVER STARTS.
4. MSDB: SQL SERVER AGENT FOR SCHEDULING JOBS AND ALERTS USES IT.
PUBS & NORTH WIND: SAMPLE DATABASES
SUB LANGUAGES IN T-SQL COMMANDS:
1. DDL (DATA DEFINITION LANGUAGE)
2. DML (DATA MANIPULATION LANGUAGE)
3. DCL (DATA CONTROL LANGUAGE)
Ø DDL : THESE ARE USED TO CREATE, ALTER AND DROP THE DATABASE OBJECTS.
COMMANDS 1.CREATE
2. ALTER
3. DROP
Ø DML : THESE ARE USED TO INSERT, MODIFY AND DELETE THE DATA IN DATABASE OBJECTS.
à THESE COMMANDS ARE ALSO USED TO RETRIEVE THE DATA IN DB OBJECTS
COMMANDS 1.INSERT
2. UPDATE
3. DELETE
4. SELECT
Ø DCL : THESE ARE USED TO GRAND OR REVOKE THE PERMISSIONS ON DATABASE OBJECTS TO
OTHER USERS
COMMANDS 1.GRANT
2. REVOKE
TABLE IS THE PRIMARY STORAGE UNIT FOR DATA IN DB. MAX NO OF COLUMNS ALLOWED FOR A TABLES ARE 1024, MAX AMOUNT OF DATA ALLOWED PER A ROW 8060 BYTES.
DATA TYPE : THE DATA TYPE SPECIFIES THE TYPE OF DATA THAT CAN BE STORED IN A VARIABLE OR COLUMN OF A TABLE.
DATA TYPE IN SQL SERVER ARE CLASSIFIED INTO TWO TYPES:
1. PREDEFINED DATA TYPES.
2. USER DEFINED DATA TYPES.
PRE DEFINED DATA TYPES:
(a) CHAR [(N)]àFIXED LENGTH DATA TYPE USED TO STORE CHARACTER DATA, STATIC MEMORY ALLOCATION
à IT CAN STORE DATA UP TO 8000CHARACTERS.
à DEFAULT SIZE OF N IS 1 BYTE.
EX: 1.@V1 CHAR (5)
2.@V2 CHAR (10)
(b) VARCHAR (N)àVARIABLE LENGTH DATA TYPE. DYNAMIC MEMORY ALLOCATION
à IT CAN STORE DATA UP TO 8000CHARACTERS.
EX: 1. @ A VARCHAR (5)
MEMORY WILL BE ALLOCATED DYNAMICALLY
(c) NCHAR (N)àFIXED LENGTH OF CHARACTER DATA TYPE
à IT CAN STORE DATA UP TO 4000CHARACTERS.
(d) NVARCHAR (N)àVARIABLE LENGTH CHARACTER DATA TYPE
à IT CAN STORE DATA UP TO 4000 CHARACTERS.
NUMERIC DATA TYPE : USED TO STORE THE NUMERIC VALUES
DATATYPE SIZE
BIGINT 8 BYTES
INT 4 BYTES
SMALLINT 2 BYTES
TINYINT 1 BYTES
FLOAT 8 BYTES
REAL 4 BYTES
DATE DATA TYPE: USED TO STORE DATE AND TIME VALUES.
1. DATE TIME: à USED TO STORE DATE VALUES FROM 1ST JANUARY -1753 TO 31ST DECEMBER - 9999
à DEFAULT SIZE IS 8 BYTES
EX : IS DOJ DATE TIME.
2. DATE TIME: à USED TO STORE DATE VALUES FROM 1ST JANUARY -1990 TO 6TH JUNE - 1979
à DEFAULT SIZE IS 4 BYTES
SQL SERVER SERVICE MANAGER: BY USING THIS DBA CAN STOP, PAUSE OR START THE SERVER.
ENTERPRISE MANAGER: IT IS THE PRIMARY ADMINISTRATIVE TOOL FOR THE ADMINISTRATIVE TASK.
CREATING LOGINS: IN ENTERPRISE MANAGE.
SECURITYà LOGINS
↓ RIGHT CLICK ON THIS
à CLICK ON NEW LOGIN
à SELECT THE SQL SERVER AUTHENTICATION
NAME OF LOGIC
PASSWORD****
CLICK OK
VERIFYING THE PROPERTIES OF LOGIN: CLICK RIGHT CLICK ON LOGIN AND CLICK ON PROPERTIES
DEMO TABLES:
1. EMP TABLE
2. DEPT TABLE
EMP TABLE
EMPNO ENAME JOB MGR SAL COMM. HIREDATE DPTN
7369 SMITH CLERK 7902 800 NULL ----- 20
--- ---- --- --- --- --- ----- ---
--- ---- --- --- --- --- ---- ---
7934 MILLER CLERK 7506 1300 NULL --- 10
DEPT TABLE:
DEPT NO DNAME LOC
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
DDL COMMANDS
CREATE : IT IS USED TO CREATE DATABASE AND ITS OBJECTS LIKE TABLE, VIEWS ETC
à CREATE TABLE STUDENTS ( RNO INT, NAME VARCHAR (20), ADDRESS VARCHAR(50))
[SHIFT+HOME]—COMMANDS IS SELECT
F5à EXECUTE THE COMMAND
DESCRIBING THE STRUCTURE OF A TABLE:
SYNTAX : SP_HELP
EX: SP_HELP STUDENT
àCREATE TABLE DEPT (DEPTNO INT, DNAME VARCHAR(20), LOC VARCHAR(10))
à CREATE TABLE EMP (EMP INT, ENAME VARCHAR(20), JOB VARCHAR(10),SAL MONEY , DEPTNO INT )
à SP_HELP ‘EMP’ (DESCRIBES, STRUCTURE OF EMP TABLE)
à CREATE TABLE[CUST] (CUST ID INT, ENAME VARCHAR(2), SALESMAN CHAR(2))
ALTER: IT IS USED TO ALTER THE STRUCTURE OF EXISTING TABLES, IT IS MAINLY USED IN FOLLOWING CASES.
(1). WHEN USER WANTS TO ADD A COLUMN TO THE EXISTING COLUMN
(2). WHEN USER WANTS TO MODIFY THE COLUMN DEFINITION.
(3). WHEN USER WANTS TO DROP THE COLUMN IN THOSE SITUATIONS ARE USE ALTER TABLE
CREATE TABLE EMP(EMP NO INT, ENAME VARCHAR(20), JOB VARCHAR (10), MGR INT)
ADDING COLUMNS FOR EXISTING TABLE:
SYNTAX : ALTER TABLE ADD DATATYPE (SIZE), DATATYPE(SIZE)
· ALTER TABLE EMPL ADD HIRE_DATE DATE TIME
· ALTER TABLE EMPL ADD SAL INT COMM. IN, DEPTNO IN
· SP_HELP ‘EMP’
MOST RECENTLY ADDED COLUMN IS LAST COLUMN
MODIFYING THE COLUMN DEFINITION:
SYNTAX: ALTER TABLE ALTER COLUMN < COLUMN NAME> DATA TYPE (SIZE)
· ALTER TABLE EMPL ALTER COLUMN ENAME VARCHAR (30)
· ALTER TABLE EMPL ALTER COLUMN JOB VARCHAR (5)
· IT IS POSSIBLE IF COLUMN IS EMPTY (OR) SIZE IS SUFFICIENT FOR EXISTING OF COLUMNS
· ALTER TABLE EMPL ALTER COLUMN ENAME CHAR (20)
· IT IS VALID ENEN IF COLUMN CONSTAINS DATA.
· ALTER TABLE EMPL ALTER COLUMN EMPNO VARCHAR (20)
· IT IS VALID ONLY WHEN COLUMN IS EMPTY
· ALTER TABLE EMPL ALTER COLUMN JOB
· VALID WHEN COLUMN IS EMPTY IT IS VALID
DROPPING THE COLUMN FROM THE TABLE:
SYNTAX :
ALTER TABLE < TABLE_NAME> DROP COLUMN
à ONLY ONE COLUMN CAN BE DROPPED AT A TIME ONCE COLUMN IS DROPPED, THE DATA IN THE COLUMN WILL ALSO REMOVED.
à TABLE SHOULD HAVE AT LEAST ONE COLUMN IE IT IS NOT POSSIBLE TO DROP THE LAST COLUMN FROM THE TABLE.
à ALTER TABLE EMP DROP COLUMN EMPNO
àALTER TABLE DEPT DROP COLUMN LOC
àALTER TABLE DEPT DROP COLUMN DEPTNO
ERROR à SINCE LAST COLUMN CANNOT BE DROPPED FROM THE TABLE
RENAMING THE COLUMN OF TABLE
SYNTAX: SP_RENAME ‘TABLE. OLD-COLUMN-NAME’,’NEW-COLUMN-NAME’
EX: SP_RENAME ‘DEPT.DEPTNO’, ‘DEPT_NUMBER’
. SP_RENAME ‘EMP.SAL’, ‘SALARY’
RENAMING THE TABLE:
SYNTAX: SP_RENAME ‘OLD_TABLE_NAME’, ‘NEW_TABLE_NAME’
EX: SP_RENAME ‘DEPT’, ‘DEPT_TABLE’
DROPPING THE TABLE:
SYNTAX
DROP TABLE
DROP TABLE STUDENT
DROP TABLE EMP
DML COMMANDS:
1. INSERT: IT IS USED TO INSERT THE DATA INTO THE TABLES.
SYNTAX: INSERT INTO VALUES (VALUE1, VALUE2,….VALUE N)
DATE, VARCHAR, CHAR VALUES SHOULD BE IN SINGLE QUOTATION MARKS.
SINGLE QUOTATION MARKS ARE INVALID PER THE NUMERIC DATA.
àINSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK’)
àINSERT INTO DEPT VALUES(20,’RESEARCH’, ‘DALLAS’)
àINSERT INTO DEPT VALUES(30,’SALES’, ‘CHICAGO’)
àINSERT INTO DEPT VALUES(40,’OPERATIONS’, ‘BOSTON’)
DISPLAYING THE DATA IN THE TABLE:
à SELECT * FROM DEPT
DEPT NO DNAME 10C
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
à INSERT INTO EMP VALUES(7369,’SMITH’, ‘CLERK’, 7902,’15-MAR-98’,800,100,20)
INSERTING NULL VALUES INTO A TABLE
SYNTAX:
METHOD1:
IMPLICIT METHOD:
SYNTAX
INSET INTO (COLUMN1, COLUMN2,COLUMN3) VALUES
(VALUES1, VALUES2, VALUES3)
EX: INSERT INTO DEPT(DEPTNO, DANME, LOC)VALUES(10, NEWYORK)
INSERT INTO DEPT(DEPT NO) VALUES (20)
SELECT * FROM DEPT
DEPT NO DNAME 10C
10 NULL NEWYORK
20 NULL NULL
EXPLICIT METHOD:
.EXPLICITLY USER SHOULD PROVIDE THE NULL VALUES FOR THE COLUMNS
SYNTAX:
INSERT INTO VALUES (VALUE1, VALUE2, VALUE3)
EX: INSERT INTO DEPT VALUES(30,NULL, NULL)
SELECT * FROM DEPT
DEPT NO DNAME 10C
10 NULL NULL
20 NULL NULL
30 NULL NULL
40 OPERATIONS NULL
OPERATORS IN SQL SERVER:
· ARITHMETIC OPERATORS
· COMPARISON OPERATORS
· LOGICAL OPERATORS
· ASSIGNMENT OPERATORS
ARITHMETIC OPERATORS: IT IS USED TO FORM AN EXPRESSION WITH NUMERIC AND DATE VALUES.
OPERATOR + - * / %
MEANING ADD SUB MUL DIV MODULAR
COMPARISON OPERATORS: THESE ARE USED TO CO,PARE ARE EXPRESSION WITH ANOTHER EXPRESSION.
OPERATOR MEANING
> GREATER THAN
>= GREATER THAN OR EQUAL
< LESS THAN
<= LESS THAN OR EQUAL TO
!< NOT LESS THAN
!> NOT GREATER THAN
<> (OR) != NOT EQUAL TO
LOGICAL OPERATORS: THERE ARE USED TO COMBINE THE RESULTS OF 2 EXPRESSIONS INTO SINGLE ONE.
OPERATOR MEANING
AND RETURNS TRUE IF BOTH THE EXPRESSIONS ARE TRUE
OR RETURNS TRUE IF ONE OF THE TWO EXPRESSIONS IS TRUE
NOT RETURNS TRUE IF RESULT IS FALSE OTHER WISE RETURN FALSE.
P Q P AND Q P OR Q NOT P NOT Q
1 1 1 1 0 0
1 0 0 1 0 1
0 1 0 1 1 0
0 0 0 0 1 1
1à TRUE
0à FALSE
ASSIGNMENT OPERATOR: ‘=’ IS THE ONLY ASSIGNMENT OPERATOR AVAILABLE IN SQL SERVER
‘WHERE’ CLAUSE: IT IS A CONDITIONAL CLAUSE USED TO IMPOSE THE CONDITIONS. IT IS USED WITH UPDATE AND SELECT STATEMENT.
UPDATE: USED TO MODIFY THE DATA IN THE TABLE.
SYNTAX: UPDATE SET COLUMN 1=VALUE 1[, COLUMN2= VALUE2….]
* MODIFY THE SALARIES OF ALL THE EMPLOYEES IN EMP TABLE
àUPDATE EMP SCT SAL=SAL+1000
à ALL EMPLOYEES SALARIES ARE UPDATED
à THIS TYPE OF UPDATION IS CALLED HIGH LEVEL UPDATION
*MODIFY THE SALARIES OF ALL MANAGERS
à UPDATE EMP SET SAL=SAL+500 WHERE JOB= ‘SALES MAN’ OR JOB=’ANALYST’
*MODIFY THE SALARIES OF ALL THE CLERKS WORKING IN 20TH DEPARTMENT
àUPDATE EMP SET SAL= SAL+300 WHERE JOB=’CLERK’ AND DEPTNO=20
*MODIFY THE SALARIES OF ALL THE ANALYSTS IN THE BOTH DEPT AND ALL THE MANAGERS IN 30TH DEPT
àUPDATES EMP SET SAL = SAL + 3000 WHERE JOB = ‘ANALYST’ AND DEPTNO = 20 OR JOB= ‘MANAGER’ AND DEPTNO= 30.
DELETE: IT IS USED TO DELETE THE DATA FROM THE TABLE.
SYNTAX:
DELETE FROM [WHERE]
àDELETE FROM DEPT
à ALL RECORDS IN DEPT TABLE ARE DELETED
à THIS IS CALLED HIGH LEVEL DELETION
àDELETE FROM EMP
à ALL RECORD IN EMP TABLE ARE DELETED.
*DELETE ALL CLERKS DETAILS FROM EMP TABLE:
à DELETE FROM EMP WHERE JOB=’CLERK’
· DELETE ‘SMITH’ DETAILS FROM EMP TABLE
àDELETE FROM EMP WHERE ENAME=’SMITH’
· DELETE ALL EMPLOYEE WORKING IN 20 DEPT
* DELETE FROM EMP WHERE DEPT NO=20.
TRUNCATE: IT IS ALSO USED TO DELETE THE DATA FROM THE TABLE .BY USING THIS COMMAND DATA CANNOT BE DELETED CONDITIONALLY. THIS STATEMENT DELETES THE DATA IN PAGE BY PAGE MANNER.
SYNTAX: TRUNCATE
àTRUNCATE EMP
àTRUNCATE DEPT
àTRUNCATE EMP WHERE DEPT NO= 30
ERROR
QUERY: IT IS A REQUEST FOR INFORMATION
SIMPLE SELECT: SELECT COMMAND IS USED TO RETRIEVE THE DATA FROM TABLE(S)
IT CANNOT MODIFY THE DATA IN THE TABLE.
SYNTAX:
SELECT COLUMN 1, COLUMN2, COLUMN 3, ……………. COLUMN N FROKM
SELECT * FROM
‘*’ MEANS ALL COLUMNS AND ALL ROWS IN TABLE
à SELECT EMPO, DEPT NO, ENAME, FROM EMP.
EMPNO DEPTNO ENAME
7369 20 SMITH
7499 30 ALLEN
7521 30 WORD
7934 10 MILLER
NOTE: THE ORDER OF THE COLUMNS IN THE OUTPUT DEPENDS ON THE ORDER OF THE COLUMNS IN THE SELECT STATEMENT
WRITE A QUERY TO DISPLAY THE COMPLETE DATA FROM EMP TABLE
à SELECT * FROM EMP
SELECT WITH WHERE CLAUSE: WHERE CLAUSE IS USED WITH SELECT TO RETRIEVE THE DATA ON THE CONDITION
SYNTAX: SELECT COLUMN 1, COLUMN 2, COLUMN FROM WHERE
(OR)
SELECT * FROM WHERE
WRITE A QUERY TO DISPLAY EMPNO, ENAME ,JOB FOR ALL MANAGERS.
àSELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB=’MANAGER’
WRITE A QUERY TO DISPLAY ALL DETAILS OF 30 DEPT SALES MANS
à SELECT * FROM EMP WHERE JOB= ‘SALES MAN’ AND DEPTNO = 30
WRITE A QUERY TO DISPLAY ALL DETAILS OF ALL EMPLOYEE WHO ARE GETTING MORE THAN 3000
à SELECT * FROM EMP WHERE SAL>3000
‘ORDER BY’ CLAUSE: IT IS USED TO DISPLAY THE DATA EITHER IN ASCENDING (OR) DESCENDING ORDER.
DEFAULT ORDER IS ASCENDING
IT ALWAYS APPEARS AT THE AND OF SELECT STATEMENT
IT CAN BE USED WITH ANY COLUMN
SELECT WITH ’WHERE’ AND ‘ORDER BY’ CLAUSES :
SYNTAX:
SELECT COL1, COL2 – COLUMN FROM [WHERE CONDITION] ORDER BY COLUMN[ASC]/DESC.
SELECT * FROM [WHERE] ORDER BY COLUMN[ASC] / DESC
WRITE A QUERY TO DISPLAY EMPNO ENAME, SAL FOR EMPLOYEES IN EMP TABLE IN ASCENDING ORDER SALARIES
à SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL [ASC]
WRITE A QUERY TO DISPLAY ALL EMPLOYEE DETAILS IN DESCENDING ORDER OF SALARIES
à SELECT * FROM EMP ORDER BY SAL DESC
WRITE A QUERY TO DISPLAY ALL ENAMES IN ASCENDING ORDER
à SELECT ENAME FROM EMP ORDER BY ENAME
ENAME
ADAMS
ALLEN
BLAKE
----
-----
-----
WARD
WRITE A QUERY TO DISPLAY ALL DETAILS OF ALL EMPLOYEES IN DESCENDING ORDER OF HIREDATE
à SELECT * FROM EMP ORDER BY HIREDATE
WRITE A QUERY ENAME, SAL, JOB OF ALL 10 DEPT EMPLOYEES IN ASCENDING ORDER OF SALARIES
à SELECT EMPNO, JOB, SAL FROM EMP WHERE DEPTNO=10 ORDER BY SAL
ENAME JOB SAL
MILLER CLERK 1300
OLARK MANAGER 2450
KING PRESIDENT 5000
WRITE A QUERY TO DISPLAY ENAME , JOB, SAL, DEPTNO FOR ALL MANAGER IN DESCENDING ORDER OF SALARIES
à SELECT ENAME, JOB,DEPTNO, SAL FROM EMP WHERE JOB= ‘MANAGER’ ORDER BY SAL DESC
COLUMN ALIAS: IT IS A METHOD OF ASSIGNING THE TEMPORARY NAMES FOR THE COLUMNS WHILE DISPLAYING THE DATA.
ALIAS NAME IMMEDIATELY FOLLOWS THE COLUMN NAME WITH OPTIONAL ‘AS’ KEYWORD BETWEEN COLUMN NAME AND ALIAS NAME.
ALIAS NAME SHOULD BE IN SINGLE QUOTATION MARKS IF IT CONTAINS SPACES AND CHARACTERS OTHER THAN A TO Z, A TO Z, 0 TO 9, $, #, _(UNDER SCORE).
EX: SELECT EMPNO, SAL, COMM. AS COMMISSION FROM EMP
EMPNO SALARY COMMISSION
-------- ---------- ------------------
à SELECT DEPTNO, DNAME AS ‘DEPTNAME’, LOC LOCATION FROM DEPT
DEPTNO DEPTNAME LOCATION
SELECT STATEMENT WITH ARITHMETIC EXPRESSIONS:
WRITE A QUERY TO DISPLAY EMPNO, SAL AS SALARY PER MONTH, SALARY PER YEAR FOR ALL EMPLOYEES
à SELECT EMPNO, SAL ‘SALARY PER YEAR’ FROM AMP
WRITE A QUERY TO DISPLAY EMPNO HRA, DA, PLF & GROSS FOR EMPLOYEES IN EMP TABLE BY SAL AS BASIC.
à SELECT EMPNO, SAL, BASIC SAL *0.45 HRA, SAL*0.35 DA, SAL * 0.15 PF SAL + SAL *0.45+SAL * 0.35 – SAL*0.15 GROSS FROM EMP
EMPNO BASIC HRA DA PF GROSS
FUNCTIONS: FUNCTION IS A PRE DEFINED PROGRAM SEGMENT THAT CARRIES SPECIFIC AND WELL DEFINED TASK.
CLASSIFICATIONS OF FUNCTIONS:
1. SCALAR FUNCTIONS
2. GROUP OR AGGREGATE FUNCTIONS
3. ROW SET FUNCTIONS
1. SCALAR FUNCTIONS: THIS FUNCTIONS WILL ACTS AN SINGLE VALUE & RETURNS A SINGLE VALUE
CLASSIFICATION OF SCALAR FUNCTIONS:
1. MATHEMATICAL FUNCTIONS
2. STRING FUNCTIONS
3. DATE & TIME FUNCTIONS
4. SYSTEM FUNCTIONS
5. SECURITY FUNCTIONS
6. CURSOR FUNCTIONS
7. SYSTEM STASTICAL FUNCTIONS
8. TEXT AND IMAGE FUNCTIONS
9. CONFIGURATION FUNCTION.
MATHEMATICAL FUNCTIONS:
i) ABSOLUTE: IT RETURN THE ABSOLUTE VALUE OF N
à SELECT ABS(-10.65)
10.65
à SELECT ABS(18.69)
18.67
ii) POWER (M, N): IT RETURN THE M POWER N VALUE
à SELECT POWER(3,2)
9
à SELECT POWER(6,2)
36
iii) SQRT(N): IT RETURN THE SQUARE ROOT VALUE OF N
à SELECT SQRT(2)
1.414
iv) SQUARE (N): IT RETURN THE SQUARE VALUE OF N
à SELECT ABS(9)
81
à SELECT ABS(5)
25
v) ROUND(M, N): IT WILL ROUND THE VALUE OF M TO NEAREST WHOLE NUMBER OF IT WILL AROUND.
à SELECT ROUND(15.143)
15
à SELECT ROUND(16.513)
16
à SELECT ROUND(16.816)
17
à SELECT ROUND(21.132,1)
21.1
à SELECT ROUND(25.143)
25
vi) CEILING: IT (CEILING) RETURNS THE SMALLEST INTEGER GREATER THAN ‘N’
à SELECT CEILING(15,13)
16
vii) FLOOR(N): IT RETURN THE LARGEST INTEGER LESS THAN ‘N’
à SELECT FLOOR(15.13)
14
STRING FUNCTIONS:
i) ASCII (CH): IT RETURNS THE ASCII VALUE OF GIVEN CHARACTER.
à SELECT ASCII(‘A’)
65
à SELECT ASCII(‘A’)
97
à SELECT ASCII(‘0’)
48
à SELECT ASCII(‘’)
32
ii) CHAR(N): IT RETURNS THE CHARACTER FOR GIVEN ASCII VALUE
à SELECT CHAR(65)
A
à SELECT CHAR(98)
B
iii) LOWER (STRING): IT CONVERTS THE UPPER CASE LETTERS IN STRING INTO LOWER CASES
à SELECT LOWER(‘ORACLE’)
ORACLE
iv) UPPER(STRING): IT CONVERTS THE LOWER CASES INTO UPPER CASES
à SELECT UPPER(ORACLE)
ORACLE
à SELECT ENAME, LOWER(ENAME) LOWER_ENAMES FROM EMP
ENAME LOWER-ENAME
SMITH SMITH
ALLEN ALLEN
v) LEN(STRING): IT RETURNS THE LENGTH OF GIVEN STRING
à SELECT LEN(‘ORACLE’)
6
à SELECT LEN(‘ORACLE APPS’) SPACE ALSO CHARACTER
11
à SELECT ENAME, LEN(‘ENAME’) LENGTH FROM EMP
ENAME LENGTH
SMITH 5
ALLEN 5
MILLER 6
REVERSE (STRING) : IT WILL REVERSE THE GIVEN STRING
à SELECT REVERSE(‘ORACLE’)
ELCARO
àSELECT ENAME, REVERSE(ENAME) REV_ENAME FROM EMP
ENAME LENGTH
SMITH HTIMS
ALLEN NELLA
MILLER RELLIM
REPLACE (STRING, ‘SEARCHING STRING’, ‘REPLACING STRING’)
à IT WILL REPLACE A SUB STRING IN ASTRING WITH GIVEN STRING
à SELECT REPLACE (‘WRONG’, ‘WR’, ‘R’)
LONG
à SELECT REPLACE (‘JACK AND JUE’, ‘J’, ‘BI’)
BLACK AND BLUE
SUB STRING (‘STRING’, ‘STARTING POSITION’, NO OF CHARACTERS REQUIRED);
IT WILL ENTRACT A SUBSTRING FLOW MAIN STRING
EX:à SELECT SUBSTRING (‘ORACLE’, 1, 3’)
ORA
à SELECT SUBSTRING (‘ORACLE’, 2, 2)
RA
àSELECT ENAME, SUBSTRING(ENAME,1,2) SUN_ENAME FROM EMP
ENAME SUB_ENAME
SMITH SM
ALLEN AL
DATE FUNCTIONS:
GET DATE (); IT RETURNS THE CURRENT DATE AND TIME
à SELECT GETDATE()
2005-4-5: 4:30.15.416
DATE PART ABBREVIATION
YEAR YY
QUARTER QQ
MONTH MM
WEEK WW
DAY DD
HOUR HH
MINUTE MM
SIZE SS
MILLISEC MS
DATE ADD (DATE PART, NUMBER, DATE): IT RETURNS THE DATE ACCORDING TO DATE PART.
à SELECT DATE (YY,1,’2005-3-15’)
2006-3-15
à SELECT DATEADD (MM,1,’1998-4-18’)
1998-4-18
à SELECT DATE ADD(DAY,1,’1999-04-26’)
2006-3-15
DATE DIFF(DATEPART, DATE 1, DATE 2): IT RETURNS THE DIFFERENCE BETWEEN THE DATES ACCORDING TO THE DATE PART
àSELECT DATE DIFF(YEAR, ‘1999-11-5’,’2000-11-15’)
SPECIAL OPERATORS IN SQL SERVER:
1. IN
2. NOT
3. BETWEEN
4. NOTBETWEEN
5. LIKE
6. NOT LIKE
7. IS NULL
1) IN(LIST OF VALUES): IT PICKS THE VALUES FROM THE LIST
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF CLERK, MANAGER AND ANALYST
A) SELECT * FROM EMP WHERE JOB IN(‘CLERK’, ‘MANAGER’,’ANALYST’)
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF 7369,7839,7466
A) SELECT * FROM EMP WHERE JOB IN(‘7369’,’7839’,’7466’)
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF SMITH, ALLEN, KING
A) SELECT * FROM EMP WHERE JOB IN(‘SMITH’, ‘ALLEN’, ‘KING’)
2) NOT IN(LIST OF VALUES): IT IS JUST OPPOSITE TO IN. IT DISPLAY ALL THE VALUES IN TABLE, OTHER THAN VALUES SPECIFIED IN LIST
àSELECT * FROM EMP WHERE JOB NOT IN (‘MANAGER’,CLERK’, ‘PRESEDENT’)
à OTHER THAN ‘MANAGER’, ‘CLERK’, ‘PRESEDENT’ JOBS DETAILS WILL BE DISPLAYED
3) BETWEEN: IT WILL DISPLAY THE VALUES SPECIFIED IN THE RANGE
EX: SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 4000
IT WILL DISPLAY ALL THE EMPLOYEES DETAILS WHO ARE GETTING SALARY MORE THAN OR EQUAL TO 2000 AND LESS THAN OR EQUAL TO 4000.
IT INCLUDES THE RANGE SPECIFIED, SO IT IS CALLED INCLUSIVE OPERATOR.
4) NOT BETWEEN: IT IS OPPOSITE TO ‘BETWEEN’ OPERATOR IT WILL DISPLAY THE VALUES OTHER THAN RANGE SPECIFIED.
Q) WRITE A QUERY TO DISPLAY AU DETAILS WHO ARE GETTING SALARIES LESS THAN 2000 AND MORE THAN 4000
A) SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 4000.
NOTE: NOT BETWEEN OPERATOR WILL NOT INCLUDE THE RANGE SPECIFIED, SO IT IS CALLED EXCLUSIVE OPERATOR.
5) LIKE:IT IS USED TO SEARCH FOR PATTERN IN CHARACTERS. IT ASSOCIATED WITH TWO CHARACTERS
(I) _ (UNDERSCORE)
(II) %
HERE % REPRESENTS ZERO OR MORE CHARACTERS AND UNDER SCORE REPRESENTS A SINGLE CHARACTER.
WAQ TO DISPLAY THE NAMES BEGIN WITH ‘S’
(B) SELECT ENAME FROM EMP WHERE ENAME LIKE ‘S’
ENAME
SMITH
SCOTT
WAQ TO DISPLAY NAMES END WITH ‘S’
(A) SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%S’
ENAME
JAMES
JONES
WAQ TO DISPLAY NAMES CONTAINING ‘LL’ OR TT
àSELECT ENAME FROM EMP WHERE ENAME LIKE %LL% (OR) ENAME LIKE ‘%TT%’
SCOTT
MILLER
ALLEN
WAQ TO DISPLAY NAMES CONTAINING ‘A’ AS SECOND CHARACTER
(A) SELECT ENAME LIKE ‘_A%’
WAQ TO DISPLAY NAME CONTAINING 5 CHARACTERS
à SELECT ENAME FROM EMP WHERE ENAME LIKE’------‘
6) NOT LIKE: OPPOSITE TO LIKE OPERATOR.
7)IS NULL:IT IS USED TO TEST FOR NULL VALUES.
WAQ TO DISPLAY ALL EMPLOYEES WHO ARE HAVING NULL COMMISSION
(A) SELECT * FROM EMP WHERE COMM. IS NULL
WAQ TO DISPLAY THE EMPLOYEE WHO ARE NOT HAVING MANAGERS.
(A) SELECT * FROM EMP WHERE MGR IS NULL
8)IS NOT NULL:
WAQ TO DISPLAY ALL EMPLOYEES WHO ARE GETTING COMMISSION
(A) SELECT * FROM EMP WHERE COMM. IS NOT NULL
WAQ TODISPLAY THE EMPLOYEES WHO ARE HAVING THE MANAGERS
(a) SELECT * FROM EMP WHERE MGR IS NOT NULL
GROUP FUNCTIONS: THESE FUNCTIONS WILL ACTS ON GROUP OF VALUES AT A TIME AND RETURNS A SINGLE VALUES. GROUP FUNCTIONS WILL IGNORE THE NULL VALUES.
GROUP: SET OF RECORDS IN A TABLE
ENTIRE TABLE IS CONSIDERED AS A GROUP OR A TABLE CAN BE DIVIDED INTO NO., OF GROUPS
· SUM: RETURNS SUM OF VALUES IN COLUMN
· AVG: RETURNS THE AVERAGE VALUE FOR ALL VALUES IN COLUMB
· MIN: RETURNS THE MINIMUM VALUE FROM GIVEN COLUMN
· MAX: RETURNS THE MAXIMUM VALUE FROM GIVEN COLUMN
· COUNT: IT COUNT THE NUMBER OF VALUE OR RECORDS IN A GROUP. IT WILL TAKE THREE DIFFERENT PARAMETERS
COUNT(*)àIT CANOT NO OF RECORDS IN GROUP
COUNT(COLUMN_NAME): IT COUNTS NO., OF NOT NULL VALUES IN GIVEN COLUMN, INCLUDING DUPLICATE VALUES.
COUNT( DISTRICT COLUMN_NAME)à IT WILL COUNT NO., OF NOT NULL VALUES IN GIVEN COLUMN BUT IGNORE DUPLICATE VALUES.
TOTAL-SALARY AVERAGE MINIMUM MAXIMUM
29075 2073.21429 800 5000
EX: SELECT SUM (SAL) TOTAL_SAL, MIN(SAL) MIN_SAL MAX(SAL) MAX_SAL FROM EMP WHERE JOB=’CLERK’
TOTAL-SALARY MINIMUM MAXIMUM
29075 800 5000
SELECT SUM(SAL) SUM_SAL MAX(SAL) MAX_SAL FROM EMP WHERE DEPTNO=20
SUM_SAL MAX_SAL
10875 2975
SELECT MIN(SAL) MIN_SAL MAX(SAL), DEPT NO FROM EMP WHERE DEPTNO=30 (INVALID)
NOTE: SUM,.AVG FUNCTIONS WILL ACTS ONLY ON NUMERIC DATA BUT MIN, MAX FUNCTIONS WILL ACTS AN ANY TYPE OF DAYA
à SELECT MIN(ENAME) MIN_NAME, MAX(ENAME) MAX_ENAME FROM EMP
MIN_ENAME MAX_ENAME
ADAMS WARD
à SELECT MIN(HIRE DATE) MIN_DATE, MAX(HIREDATE) MAX_DATE FROM EMP
à SELECT COUNT (*) COUNT (DEPTNO ), COUNT (DISTINCT DEPT NO) FROM EMP
OUTPUT: 14 14 3
GROUP BY CLAUSE: IT IS USED TO DIVIDE THE TABLE INTO GROUPS
SYNTAX: SELECT , FROM [WHERE] GROUP BY GROUP_BY-EXPRESS[ORDER BY COLUMN ASC/DES]
WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON DEPT NO
(A) SELECT DEPT NO, SUM(SAL) TOTAL_SAL, MIN(SAL) MM_SAL, MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO.
WAQ TO DIVIDE THE TABLE INTO GROUP BASED ON DEPTNO AND DISPLAY 20,30 TH GROUP
(A) SELECT DEPTNO, SUM(SAL), SUM_SAL, AVG(SAL) AVG)SAL, COUNT(*) FROM EMP WHERE JOB IN (‘MANAGER’,’ANALYST’,’CLERK’) GROUP BY JOB
WRITE A QUERY TO DIVIDE THE TABLE INTO GROUPS BASED ON JOB AND DEPTNO
(A) SELECT JOB, DEPTNO, MIN(SAL), MIN_SAL, MAX_SAL COUNT(*) FROM EMP GROUP BY JOB, DEPTNO.
HAVING CLAUSE: USED TO IMPOSE CONDITION ON GROUPED DATA
SYNTAX:à SELECT , GROUP FUNCTIONS FROM WHERE GROUP BY GROUP_BY_EXP HAVING ORDER BY COLUMN ASC/DES
WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON JOB AND DEPTNO. DISPLAY THE GROUP IF NO., OF RECORDS ARE MORE THAN 1
à SELECT JOB, DEPTNO, MIN(SAL) MIN_SAL, MAX(SAL), MAX_SAL, COUNT_*) FROM EMP GROUP BY JOB, DEPTNO HAVING COUNT(*)>1
JOB DEPTNO MIN-SAL MAX-SAL COUNT(*)
CLERK 20 800 1100 2
WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON DEPTNO & DISPLAY THE GROUPS IF SUM (SAL)> 500
(A) SELECT DEPTNO, SUM(SAL), AVG(SAL) FROM EMP GROUPE BY DEPTNO HAVING SUM(SAL) > 500.
WAQ TO DIVIDE THE TABLE IN GROUP BASED ON JOB& XXX MANAGER, CLERK, SALESMAN IF SUM(SAL)> 4000
(A) SELECT JOB, SUM(SAL) FROM EMP WHERE JOB IN (MANAGER, CLERK, SALESMAN) GROUP BY JOB HAVING SUM (SAL)>4000
SET OPERATORS: SET OPERATORS ARE USED TO COMBINE THE OUTPUTS OF TWO OR MOR QUERIES AS A SINGLE ONE THE QUERIES COMBINED BY THE SET OPERATORS SHOULD HAVE SAME NUMBER OF COLUMSNS AND THEIR CORRESPONDING DATA TYPES SHOULD BE SAME
1. UNION ALL
2. UNION
1. UNION ALL: IT WILL COMBINE THE OUTPUTS OF TWO OR MORE QUERIES IT WILL NOT ELIMINATE DUPLICATE VALUES.
EX: à SELECT JOB FROM EMP WHERE DEPTNO = 10 UNION ALL SELECT JOB FROM EMP WHERE DEPTNO = 200
2. UNION: IT WIL COMBINE THE OUTPUTS OF TWO OR MORE QUERIES AND ELIMINATES THE DUPLICATE VALUES
EX: à SELECT JOB FROM EMP WHERE DEPTNO =10 UNION SELECT JOB FROM EMP WHERE DEPTNO =20.
DEPTNO DNAME, LOC , EMPNO ,ENAME ,JOB ,DEPTNO
JOIN: JOIN IS A QUERY THAT COMBINES THE ROWS FROM TWO OR MORE TABLE
TYPES OF JOIN
1. CROSS JOIN
2. INNER JOIN OR QUAL JOIN
3. OUTER JOIN
4. SELF JOIN
CROSS JOIN: IN CROSS JOIN EVERY ROW OF THE FIRST TABLE JOINS WITH EVERY ROW IN SECOND TABLE THE OUTPUT OF THE CROSS JOIN IS CALLED CARTESIAN PRODUCT
DEPT EMP
DNAME ENAME
ACCOUNTING SMITH
RESEARCH ALLEN
SALES
OPERATION MILLER
4 ROWS 14 ROWS
à SELECT ENAME, DNAME FROM EMP, DEPT
( OR)
à SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT
INNER JOIN OR EQUIJOIN:- A JOIN WHICH IS BASED ON EQUALITIES IS CALLED EQUL JOIN TABLES SHOULD HAVE COMMON COLUMN TO PERFORM EQUI JOIN
SYNTAX:-
SELECT TABLE 1, COLUMN 1, TABLE 2, COLUMN 2…… FROM TABLE, TABLE2
WHERE TABLE1 COLUMN = TABLE.COLUMN
(OR)
SELECT TABLE1, COLUMN1, TABLE 2, COLUMN 2,……….
INNER JOIN TABLE1, TABLE2, ON TABLE1. COLUMN = TABLE2. COLUMN
WAQ TO DISPLAY ENAME, JOB, DEPTNO DNAME, LOC FOR ALL EMPLOYEES IN EMP,DEPT TABLE,
A) SELECT ENAME, JOB, EMPDEPTNO, DNAME, LOC FROM EMP, DEPT, WHERE EMP DEPTNO = DET DEPTNO
WAQ TO DISPLAY ENAME, JOB,SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP. DEPTNO = DEPT. DEPTNO AND LOC= ‘NEWYORK’
3) OUTER JOIN: OUTER JOIN IS AN ENTENSION FOR THE EQUI JOIN. IT IS USEFUL TO RETRIVE UNMATCHED RECORDS ALONG WITH MATCHED RECORS.
I) LEFT OUTER JOIN:
SYNTAX:
SELECT TABLE1. COLUMN 1, TABLE 2. COLUMN2,.. FROM TABLE1, TABLE2 WHERE TABLE 1 COLUMN * + TABLE2.COLUMN.
à SELECT TABLE 1. COLUMN 1, TABLE 2. COLUMN2…..
FROM TABLE 1 LEFT OUTER JOIN TABLE 2 ON TABLE 1 COLUMN = TABLE2.COLUMN
IT WILL RETRIVES ALL THE MATCHED RECORDS FROM TABLE AND TABLE2 & UNMATCHED RECORDS FROM TABLE.
WAQ TO RETRIVE THE ALL MATCHED RECORDS AND UNMATCHED RECORDS FROM EMP TABLE
A) SELECT EMPNO, ENAME,EMP-DEPTNO, EDNO, DNAME, LOC ROM EMP, DEPT WHERE EMP DEPTNO *= DEPT.DEPTNO.
II) RIGHT OUTER JOIN:
à SELECT TABLE1.COLUMN1, TABLE.COLUMN2,-- FROM TABLE1, TABLE2
WHERE TABLE1. COLUMN *= TABLE2. COLUMN
à SELECT TABLE.COLUMN1, TABLE2.COLUMN2 TABLE 1 RIGHT OUTER JOIN TABLE ON TABLE1.COLUMN = TABLE 2. COLUMN.
IT WILL RETRIEVE ALL THE MATCHED RECORDS FROM TABLE 1 & TABLE2 AND UNMATCHED RECORDS FROM TABLE2 (TABLE RIGHT TO THE RIGHT OUTER JOIN KEY WORD)
WAQ TO RETRIEVE ALL MATCHED RECORDS FROM EMP AND DEPT TABLE AND UNMATCHED RECORDS FROM DEPT TABLE.
A) SELECT EMPNO, ENAME, EMP.DEPTNO.EDNO, DEPT.DEPTNO DNO, FROM EMP, DEPT WHERE EMP.DEPTNO=*DEPT.DEPTNO.
(OR)
SELECT EMPO,ENAME, EMP.DEPTNO EDNO, DEPT.DEPTNO DNO, DNAME FROM EMP RIGHT OUTER JOIN DEPT ON EMP. DEPTNO = DEPT.DEPTNO
EMPNO ENAME EDNO DNO DNAME
7369 SMITH 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
NULL NULL NULL 40 OPERATIONS
FULL OUTERJOIN: SELECT TABLE,.COLUMN, TABLE2.COLUMB
FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COLUMN = TABLE2.COLUMN
IT WILL RETRIEVE ALL THE MATCHED RECORDS FROM TABLE1 AND TABLE2 AND UNMATCHED RECORDS FROM BOTH TABLES
EX: SELECT EMPO, ENAME, EMP, DEPTNO ENDNO, DEPTNODNO, DNAME FROM EMP FULL OUTER JOIN DEPT ON EMP DEPTNO = DEPT DEPTMN.
EMPNO ENAME EDNO DNO DNAME
7369 SMITH 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
NULL NULL NULL 40 OPERATIONS
100 RAM 80 NULL NULL
SELF JOIN : JOINING THE TABLE TO ITSELF IS CALLED SELF JOIN.
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7698 JONES 7839
7839 KING ---
7902 FORD 7839
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7698 JONES 7839
7839 KING ---
7902 FORD 7839
WAQ TO DISPLAY EMPLOYEE NAME AND HIS MANAGER NAME
SELECT WORK ENAME EMPLOYEE, MANAGER ENAME BOSS FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO.
WRITE A QUERY TO DISPLAY ALL EMPLOYEES WHOSE SALARY IS MORE THAN ALLEN SALARY
SELECT B.* FROM EMP A, EMP B WHERE A.ENAME = ‘ALLEN’ AND A.SAL
WAQ TO DISPLAY ALL EMPLOYEE DETAILS WHOSE JOB SAME AS SMITH JOB?
A) SELECT B * FROM EMP A, EMP A, EMPBA WHERE A ENAME AND A JO = B JOB
WAQ TO DISPLAY ALL DETAILS OF ALL EMPLOYEES WHO ARE SENIOR THAN THEIR OWN MGR?
A) SELECT B * FROM EMP A EMPB WHERE B MGR = A.EMP NO. AND B HIRE DATE < A. HIREDATE.
WAQ TO DISPLAY THE DETAILS OF ALL EMPLOYEES WHO ARE WORKING IN SMITH DEPT.
A) SELECT * FROM EMP WHERE ENAME =’SMITH’
à SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’
à SELECT * FROM EMP WHERE DEPTNO = 20.
SUB QUARRIES: SUB QUERY IS A SELECT STATEMENT IN ANOTHER SELECT STATEMENT.
SYNTAX:
SELECT FROM < TABLE_NAME> WHERE < COLUMN> OPERATOR (SELECT FROM )
FIRST INNER QUERY WILL BE EXECUTED BASED ON THE RESULT OF INNER QUERY OUTER QUERY WILL BE EXECUTED.
TYPES OF SUB QUERIES: 1) SINGLE ROW QUERY 2) MULTI ROW SUB QUERY.
SINGLE ROW SUB QUERY : A SUB QUERY WHICH RETURN ONLY ONE VALUE IS CALLED SINGLE ROW SUB QUERY.
EXAMPLES OF SINGLE ROW SUB- QUERY
WAQ TO DISPLAY ALL EMPLOYEES WHOSE JOB IS SAME AS
SELECT * FROM EMP. WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME= ‘FORD’)
WAQ TO DISPLAY ALL EMPLOYEES WHERE SALARIES ARE MORE THAN ‘SCOTT SALARY
A) SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME= ‘SCOTT’)
WAQ TO DISPLAY ALL EMPLOYEES WHOSE SALARIES ARE EQUAL TO MIN SALARY OF EMPTABLE.
A) SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP’)
SELECT DEPTNO, AVG(SAL), AVG_SAL FROM EMP GROUP BY DEPTNO
DEPTNO AVG_SAL
10 2179.666
20 2125
030 1566.66
WRITE A QUERY TO DISPLAY DEPTNO WITH AVG_SAL OF AVG_SAL IS MORE THAN AVG_SAL OF 20TH DEPT
A) SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO =20)
WAQ TO DISPLAY ALL EMPLOYEE WHO ARE WORKING AS MANAGER TO OTHER EMPLOYEES
A) SELECT * FROM EMP WHER EMPNO= (SELECT DISTCT MGR FROM EMP)
SPECIAL OPERATORS WITH SUB_QUERIES: THE FOLLOWING SPECIAL OPERATIONS ARE USED WITH SUB_QUERIES, IF SUB QUERY RETURNS MORE THAN ONE VALUES.
1) IN
2) ANY
3) ALL
4) EXISTS
1) IN: (LIST OF VALUES): IT PICKS THE VALUES FROM THE LIST.
A) SELECT * FROM EMP WHERE EMPNO IN (SELECT.DISTRICTMAGR FROM EMP)
2) ANY:
I)> ANY (LIST OF VALUES): IT PICKS THE MINIMUM VALUE FROM THE LIST AND IT WILL DISPLAY ALL THE VALUES FROM TABLE WHICH ARE MORE THAN MIN VALUE IN THE LIST.
EX:
WAQ TO DISPLAY ALL EMPLOYEE DETAILS FROM EMP TABLE IF THEIR SAL MORE MIN SAL OF 10TH DEPT 1300
A) SELECT * FROM EMP MORE MIN SAL> ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10,2450,5000,1300)
II) < ANY(LIST OF VALUES): IT PICKS THE MAN VALUE FROM THE LIST AND IT DISPLAY ALL THE VALUES FROM TABLE WHICH ARE LESS THAN MAN VALUE IN THE LIST.
EX: WAQ TO DISPLAY ALL EMPLOYEES IF THEIR SALARY LESS THAN MANX SALARY OF 20TH DEPT.
A) SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 20) 800,1100,2975,3000
(OR)
SELECT * FROM EMP WHERE SAL < (SELECT MAX (SAL) FROM EMP WHERE DEPTNO =20)
3) ALL:
I) > ALL (LIST OF VALUES): IT PICKS THE MAX VALUES FROM THE LIST AND IT WILL DISPLAY ALL VALUES FROM TABLE WHICH ARE MORE THAN MAX VALUES IS THE LIST.
EX: WAQ TO DISPLAY ALL EMPLOYEE OF THEN SALARY IS MORE THAN MAX SAL OF 20 DEPT.
A) SELECT * FROM EMP WHERE SAL> ALL (SELECT SAL FROM EMP WHERE DEPTNO=20).
II) < ALL (LIST OF VALUES): IT PICK THE MIN VALUE FROM THE LIST AND (1300,2450,5000) AND IT WILL DISPLAY ALL THE VALUES FROM TABLE WHICH ARE LESS THAN MIN VALUES IN LIST.
WAQ TO DISPLAY ALL EMPLOYEE IF THE EMPLOYEE SAL IS LESS THAN MIN SAL OF 10TH DEPT
A) SELECT * FROM EMP WHERE SAL< ALL (SELECT SAL FROM EMP WHERE DEPTNO = 10) 2450,1300,5000
a. >ANY à GREATER THAN MIN
b. c. >ANY à MORE THAN MANY
d. EXISTS: IT IS A BOOLEAN AN OPERATOR RETURNS SUCCESS OR FAILURE OF A QUERY.
SELECT JOB, SUM(SAL) SUM_SAL, MIN(SAL)MIN_SAL FROM EMP WHERE JOB=’MANAGER’ GROUP BY JOB AND EXISTS SELECT COUNT(*) FROM EMP WHERE JOB =’MANAGER’ GROUP BY JOB HAVING COUNT (*) > 2.
CO_RELATED SUB QUERIES: IF A SUB QUERY EVALUATED REPEATEDLY ONCE FOR EACH ROW SELECTED BY THE OUTER QUERY SUCH SUB QUERIES ARE CALLED CORRELATED SUB QUERY.
HERE OUTER QUERY WILL BE EXECUTED FIRST, BASED AN THE RESULT OF OUTER QUERY INNER QUERY WILL BE EXECUTED.
IF OUTER QUERY HAS RETRIEVED N RECORDS THE INNER QUERY HAS TO EXECUTE N TIMES.
WRITE QUERY TO DISPLAY EMPLOYEES DETAILS IF THE SALARY OF EMPLOYEE IS MORE THAN AVG_SAL OF THIS DEPT.
SELECT E.EMPNO, E.ENAME. E.SAL, E.DEPTNO FROM EMP E WHERE E.SAL> (SELECT AVG(SAL) FROM EMP WHERE EMP.DEPTNO=E.DEPTNO)
WAQ TO DISPLAY ALL THE EMPLOYEES WHO ARE SENIORS THAN THEIR OWN MANAGERS.
SELECT E.EMPO, E.ENAME, E.SAL, E.HIREDATE, E.MGR FROM EMP E.
WHERE E.HIREDATE<(SELECT HIREDATE FROM EMP WHERE EMP.EMPNO=E.MGR)
WAQ TO DISPLAY FIRST HIGHEST SALARY IN EMP TABLE
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE 0=(SELECT COUNT(*)) FROM EMP E WHERE EMP SAL
WAQ TODISPLAY 2ND HIGHEST SALARY IN EMP TABLE
SELECT EMPNO, ENAME, JOB,SAL, FROM EMP WHERE 1=(SELECT COUNT (*) ) FROM EMP E WHERE EMP SAL
DATA INTEGRITY:THE RELIABILITY AND ACCURACYOF DATA IS CALLED DATA INTEGRITY.
TYPES OF DATA INTEGRITY:
· ENTITY INTEGRITY
· DOMAIN INTEGRITY
· REFERENTIAL INTEGRITY
· USER DEFINED INTEGRITY
1) ENTITY INTEGRITY: BASIC IDEA BEHIND ENTITY INTEGRITY IS EACH TABLE SHOULD HAVE A COLUMN, THAN UNIQUELY IDENTIFIES ALL REMAINING COLUMNS.
2) DOMAIN INTEGRITY: DOMAIN INTEGRITY REFERS TO THE RANGE OF VALUES ALLOWED PER COLUMN OF A TABLE
3) REFERENTIAL INTEGRITY: REFERENTIAL INTEGRITY REFERS TO THE MAINTENANCE OF RELATIONSHIP BETWEEN TABLES.
4) USER DEFINED INTEGRITY: IT WILL ALLOW THE USER OR ADMINISTRATOR TO ENFORCE A NEW BUSINESS RULE WHICH WILL NOT COMES UNDER ABOVE 3 RULES.
CONSTRAINTS: IT IS A MECHANISM AUTOMATICALLY ACTIVATED WHEN USER PERFORMS DML OPERATIONS ON THE TABLE.
TYPES OF CONSTRAINTS:
1) UNIQUEà IT WILL NOT ALLOW DUPLICATE VALUES BUT ALLOWS NULL VALUES
2) NOT NULLà IT WILL NOT ALLOW THE NULL VALUES.
3) PRIMARYà IT IS A COMBINATION OF UNIQUE, NOT NULL AND CLUSTERED INDEX.
4) CHECKà IT WILL CHECK VALUES PROVIDED FOR COLUMN
5) FOREIGN KEYà IT IS USEFUL TO ESTABLISH PARENT / CHILD OR MASTER / DETAILED RELATION SHIP BETWEEN THE TABLES, FOREIGN KEY COLUMN OF THE CHILD TABLE IS AWAYS LINKED TO EITHER PRIMARY KEY COLUMN OR UNIQUE CONSTRAINT COLUMN OF THE PARENT TABLE.
6) DEFAULTà IT IS USEFUL TO INSERT TO DEFAULT VALUE INTO A COLUMN WHEN USER WILL NOT PROVIDE ANY VALUE WHILE INSERTING THE DATA INTO THE TABLE.
CONSTRAINTS CAN BE ADDED TO TABLE IN TWO LEVELS
1) COLUMN LEVEL 2) TABLE LEVEL
1) COLUMN LEVEL CONSTRAINTS: HERE CONSTRAINTS ARE ADDED IMMEDIATELY OF FOR DEFINING THE COLUMN
à ONLY ONE PRIMARY KEY COLUMN IS ALLOWED PER TABLE.
EX:
CREATE TABLE DEPT (DEPTNO INT PRIMARY KEY, DNAME VARCHAR UNIQUE NO NULL, LOC VARCHAR(10) DEFAULT ‘HYD’)
CREATE TABLE EMP (EMPNO INT PRIMARY KEY, ENAME VARCHAR NOT NULL, JOB VARCHAR(10), MGR INT, HIREDATE DEFAULT GETDATE(), SAL INT CHECK (SAL>=1000), COMM INT, DEPTNO INT REFERENCES DEPT(DEPTNO) AN DEPT DELETE CASCADE.
ADVANTAGE OF ON DELETE CASCADE: WITH OUT SPECIFYING THE ON DELETE CASCADE IT IS NOT POSSIBLE TO DELETE THE RECORD IN THE BASE TABLE IF THERE ARE DEPENDENT RECORDS FROM THE CHILD TABLE FOR THAT RECORD
IF ON DELETE CASCADE IF USED WHEN THE RECORD IN THE BASE TABLE IS DELETED ALL THE DEPENDENT RECORDS IN THE CHILD TABLE WILL BE ALSO BE DROPPED.
à CREATE TABLE INCR(EMPNO INT REFERENCES EMP(EMPNO) ON DELETE CASCADE, AMOUNT DECIMAL (5,2), DATE DATETIME)
DISPLAYING THE NAMES OF THE CONSTRAINS:
SYNTAX: SP_HELP CONSTRAINT
ASSIGNING THE NAMES FOR THE CONSTRAINTS WHILE CREATING THE TABLE:
SYNTAX:à CREATE TABLE (COLUMN1 DATATYPE(SIZE)CONSTRAINT ,COLUMN2DATATYPE(SIZE) CONSTRAINT)
EX:
CREATE TABLE DEPT(DEPTNO INT CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY, DNAME VARCHAR(20) CONSTRAINT DEPT_DNM_UQ UNIQUE, LOC VARCHAR(10)).
à CREATE TABLE EMPL(EMPNO INT CONSTRAINT EMPL_EMPO_PK PRIMARY KEY, ENAME VARCHAR(20) NOT NULL, JOB VARCHAR(10) , MGR INT, SAL INT CONSTRAINT EMPL_SAL_CHK CHECK(SAL>=1000) COMM. INT, DEPTNO INT CONSTRAINT EMPL_DEPTNO_FK REFERENCES DEPT1(DEPTNO) ON DELETE CASCADE)
INSERTING VALUES INTO THE TABLE CONTAINING CONSTRAINTS:
INSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK)
INSERT INTO DEPT VALUES(10,’RESEARCH’, ‘DALLAS’)
ERROR DUE TO VIOLATION OF P.K. CONSTRAINT
INSERT INTO DEPT VALUES(10,’ RESEARCH’, ‘DALLAS’)
ERROR DUE TOVIOLATION OF P.K CONSTRAINT
INSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK)
ERROR DUE TO TO VIOLATION OF UNIQUE CONSTRAINT
ACTIVATING THE DEFAULT CONSTRAINTS:
INSERT INTO DEPT VALUES(20,’RESEARCH’, DEFAULT)
INSET INTO DEPT(DEPTNO, DNAME) VALUES (30, ‘SALES’)
SELECT * FROM DEPT
INSERT INTO EMP(EMPNO, ENAME, SAL) VALUES (7369,’SMITH’,800)
ERROR DUE TO TO VIOLATION OF CHECK CONSTRAINT
TABLE LEVEL CONSTRAINTS: HERE CONSTRAINTS ARE ADDED AFTER DECLARING ALL COLUMNS
CONSTRAINTS ADDED FOR EXISTING TABLE COMES UNDER TABLE LEVEL.
MORE THAN ON COLUMN PER TABLE CAN BE DECLARED AS EITHER PRIMARY KEY OR FOREIGN KEY COLUMNS
DEFAULT AND NOT NULL CONSTRAINTS ARE NOT ALLOWED.
CREATE TABLE DEPT(DEPTNO INT, DNAME, VARCHAR(20) NOT NULL , (10) VARCHAR(10) DEFAULT ‘HYD’ UNIQUE (DNAME), PRIMARY KEY(DEPTNO))
CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR 920) NOT NULL, JOB VARCHAR(10), MAGR INT, HIREDATE DATETIME, SAL INT COMM. INT,. PRIMARY KEY(EMPNO), CHECK(SAL>=1000), FOREIGN KEY(DEPTNO) REFERENCES DEPT (DEPTNO) AN DELETE CASCADE)
ADDING CONSTRAINTS FOR EXISTING TABLE:
SYNTAX : ALTER TABLE ADD CONSTRAINTS TYPE (COLUMN_NAME)
à CREATE EMP, DEPT TABLES WITHOUT CONSTRAINTS
à ALTER TABLE DEPT ADD PRIMARY KEY (DEPTNO)
à ALTER TABLE DEPT ADD UNIQUE (DNAME)
à ALTER TABLE EMP ADD PRIMARY KEY (DNAME)
à ALTER TABLE EMP ADD CHECK (SAL>=1000)
à ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE.
ASSIGNING THE CONSTRAINTS FOR EXISTING TABLE WITH USER NAMES:
à CREATE TABLE DEPT(DEPTNO INT, DNAME VARCHAR(20) NOT NULL, 10C VARCHAR(10) CONSTRAINT DEPT_LOC_DF DEFAULT ‘HYD’, CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY(DEPTNO), CONSTRAINT DEPT_DNM_UQ UNIQUE(DNAME).
à CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR(20), JOB VARCHAR(10), MGR INT, HIREDATE DATETIME, SAL INT COMM. INT, DEPTNO INT, CONSTRANT EMP_EMPNO_PK PRIMARY KEY(EMPNO), CONSTRAINT EMP SAL_CHK CHECK(SAL>=1000)
CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) ON DELETE CASCADE)
ADDING CONSTRAINTS FOR EXISTING TABLES WITH USER NAMES:
Q) CREATE EMP AND DEPT TABLE WITHOUT CONSTRAINTS
A) SYNTAX:
àALTER TABLE ADD CONSTRAINT CONSTRAINT TYPE (COLUMN)
à ALTER TABLE DEPT ADD CONSTRAINT DEPT_DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO).
à ALTER TABLE DEPT ADD CONSTRAINT DEPT_DNM_UQ UNIQUE (DNAME)
à ALTER TABLE DEPT ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO).
à ALTER TABLE EMP ADD CONSTRAINT EMP_SAL_CHK CHECK (SAL>=1000)
à ALTER TABLE EMP ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE.
DROPPING THE CONSTRAINT:
SYNTAX:
ALTER TABLE DROP CONSTRAINT < CONSTRAINT_NAME>
à ALTER TABLE EMP DROP CONSTRAINT EMP_EMPNO_PK
à CREATE TABLE BANK (ACNO INT, NAME VARCHAR(20) ACC_TYPE CHAR(1), OPEN_BALANCE INT, OPEN_DATE DATETIME, PRIMARY KEY (ACNO, NAME))
à CREATE TABLE FROM (ACNO INT, NAME VARCHAR(20) ACC_TYPE CHAR(1), AMOUNT INT, TRAN_DATE DATETIME FOREIGN KEY (ACNO, NAME) REFERENCES BANK (ACNO, NAME) AN DELETE CASCADE)
ACNO NAME
100 ****
200 ****
300 ****
400 ****
.SELF REFERENTIAL INTEGRITY: THE FOREIGN KEY COLUMN OF THE TABLE IS REFERENCE TO THE PRIMARY COLUMN IN THE SAME TABLE
EX: CREATE TABLE EMP_EMP(ENAME INT, ENAME VARCHAR (20), JOB VARCHAR (10), MAGR INT, PRIMARY KEY (EMPNO), FOREIGN KEY (MGR) REFERENCES EMP_EMP(EMPNO)
EMPNO ENAME JOB MGR
à FIRST INSERT DATA INTO EMPNO, ENAME & JOB COLUMNS LATER UPDATE ‘MGR’ COLUMN
à ADDING NOT NULL CONSTRAINT FOR COLUMN OF A TABLE.
SYNTAX: ALTER TABLE ,TABLE_NAME> ALTER COLUMN DATA TYPE (SIZE) NOT NOT NULL
à ALTER TABLE DEPT ALTER COLUMN LOC VARCHAR (10) NOT NULL
DATA STORAGE: DATA OF EACH TABLE IS STORED IN THE COLLECTION OF DIFFERENT DATA PAGES, EACH PAGE WILL HAVE THE 96 BYTES PAGE HEADER. PAGE HEADER CONTAINS PREVIOUS POINTER, NEXT POINTER, AND OBJECT ID.
PREVIOUS POINTER POINTING TO PREVIOUS PAGE, NEXT POINTER POINTING TO NEXT PAGE.
PREVIOUS POINTER VALUE FOR FIST PAGE IS NULL
NEXT POINTER VALUE FOR LAST PAGE IS NULL
DATA ACCESS: SQL SERVER USES FOLLOWING TWO METHODS TO ACCESS DATA
1) TABLE SCAN 2) BY USING INDEX
TABLE SCAN: IT HAS TO START FROM THE BEGINNING OF THE TABLE AND SCAN EACH AND EVERY PAGE FOR THE REQUIRED DATA.
BY USING INDEX: IT WILL TRAVERSE THOUGH THE INDEX TREE STRUCTURE TO FIND THE REQUIRED DATA AND EXTRACT THE DATA THAT SATISFY THE QUERY CRITERIA.
INDEXED: INDEXED IN SQL SERVER IS SIMILAR TO INDEX IN ABOOK. INDEXED ARE USED TO IMPROVE THE PERFORMANCE OF QUERIES.
INDEXES ARE GENERALLY CREATED FOR FOLLOWING COLUMNS
1) PRIMARY KEY COLUMN
2) FOREIGN KEY COLUMN: FREQUENTLY USED IN JOIN CONDITIONS.
3) COLUMN WHICH ARE FREQUENTLY USED IN WHERE CLAUSE
4) COLUMNS WHICH ARE USED TO RETRIEVE THE DATA IN SORTING ORDER.
INDEXED CANNOT BE CREATED FOR FOLLOWING COLUMNS:
THE COLUMNS WHICH ARE NOT USED FREQUENTLY USED IN WHERE CLAUSE.
COLUMNS CONTAINING THE DUPLICATE AND NULL VALUES
COLUMNS CONTAINING IMAGES, BINARY INFORMATION, AND TEXT INFORMATION.
TYPES OF INDEXEX:
1) CLUSTERED INDEX
2) NON-CLUSTERED INDEX
3) UNIQUE INDEX
4) COMPOSITE INDEX
1) CLUSTERED INDEX: ONLY ONE CLUSTERED INDEX IS ALLOWED FOR A TABLE. THE ORDER OF VALUES IN A TABLE ORDER OF VALUES IN INDEX IS ALSO SAME. WHEN CLUSTER INDEX IS CREATED ON TABLE DATA IS ARRANGED IN ASCENDING ORDER CLUSTER INDEX WILL OCCUPY 5% OF THE TABLE.
SYNTAX: CREATE CLUSTERED INDEX ON (COLUMNS)
à CREATE CLUSTERED INDEX EMP_CLINDEX ON EMP (EMPNO)
NON_CLUSTERED INDEX: IT IS THE DEFAULT INDEX CREATED BY THE SERVER THE PHYSICAL ORDER OF THE DATA IN THE TABLE IS DIFFERENT FROM THE ORDER OF THE VALUES IN INDEX.
MAX NO. OF NON-CLUSTERD INDEXED ALLOWED FOR TABLE IS 249
SYNTAX:
CREATE NON-CLUSTERED INDEX ON TABLE_NAME < COLUMNS>
CREATE NONCLUSTERED INDEX EMP_SAL ON EMP (DEPTNO, SAL)
UNIQUE INDEX : AN INDEX WITH UNIQUE CONSTRAINT. IT WILL NOT ALLOW DUPLICATE VALUES.
SYNTAX: CREATE UNIQUE INDEX ON < TABLENAME> (COLUMN)
à CREATE UNIQUE INDEX DEPT_INDEX ON DEPT (DNAME)
COMPOSITE INDEX: IF A UNIQUE INDEX IS CREATED ON MORE THAN ONE COLUMN IS CALLED COMPOSITE INDEX.
à CREATE UNIQUE INDEX DEPT_COMPOSITE ON DEPT (DEPTNO, DNAME)
TRANSCACTIONS:
1) AUTO COMMIT TRANSACTION
2) EXPLICIT TRANSACTION
3) IMPLICIT TRANSACATION
1) AUTO COMMIT TRANSATION: EVERY INDIVIDUAL TRANSACTION IS AN AUTO COMMIT TRANSACTION.
EX: UPDATE EMP SET SAL= SAL + 100 WHERE DEPTNO
2) EXPLICIT TRANSACTION: TRANSACTION BEGINS WITH BEGIN TRAN AND ENDS EXPLICITLY WITH EITHER COMMIT OF ROLLBACK
à BEGIN TRAN
INSET INTO DEPT VALUES (10,. ‘ACCOUNTING’, ‘NEWYORK’)
à ROLLBACK
INSERT OPERATION IS CANCELLED
3) IMPLICIT TRANSACTION:
à SET IMPLICIT TRANSACTIONS ON/OFF
àUPDATE EMP SET SAL = SAL + 1000 WHERE JOB = ‘MANAGER’
à DELETE FROM EMP WHERE DEPTNO = 10
à COMMIT
UPDATE AND DELETE OPERATIONS ARE CANCELLED
SAVE POINT: SAVE POINTS ARE LIKE PAPER MARKERS USED TO MARKER THE TRANSACTION.
SYNTAX: SAVE BEGIN FROM
à INSERT INTO EMP (EMPNO,ENAME) VALUES (100, ‘RAM’)
SAVE FROM A
UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO=7839
SAVE FROM B
INSET INTO EMP (EMPNO, ENAME) VALUES (200,’***’)
DELETE FROM EMP WHERE EMPNO=7369
ROLLBACK FROM B
à DELETE AND INSERT WILL BE CANCELLED
ROLLBACK FROM A
à UPDATE WILL BE CANCELLED
COMMIT / ROLLBACK
-à INSET WILL BE SAVED / INT WILL BE CANCELLED
PROGRAMMING CONSTRUCTS: PROGRAMMING CONSTRUCTS ARE USED TO EXECUTE THE SET OF SQL STATEMENTS AS A UNIT..
1) IF – ELSE
2) BEGIN – END
3) WHILE
DECLARING VARIABLE: DECLARE @ VARIABLE DATATYPE, @ VARIABLE 2 DATATYPE
DECLARE @A INT, @ VJOB VARCHAR (20)
EX: SET @ A =5
SET @ B =10
SET @ VJOB =’CLERK’
DISPLAYING THE MESSAGE OR VALUE IN THE VARIABLE:
PRINT ‘MESSAGE’
OR
PRINT @ VARIABLE
WRITE A PROGRAM TO FIND THE SUM OF 2 NUMBERS
à DECLARE @A INT, @B INT, @C INT
SET @ A =5
SET @ B =10
SET @ VJOB =@A+@B
PRINT THE SUM OF A&B IS MISTAKE CONVERT (VARCH, @C)
WRITE A PROGRAM TO FIND THE SIMPLE INTEREST
à DECLARE @ P INT, @N DECIMAL(5,2) @R DECIMAL (5,2), @ SI DECIMAL (5,2)
SET @ P =10000
SET @ N =15
SET @ R = 7.5
SET @ SL =(@P * @N * @R)/100
PRINT SIMPLE INTEREST IS + CONVERT (VARCHAR, @SI)
3) WRITE A PROGRAM TO SWAP THE VALUES IN THE VARIABLES.
à DECLARE @A INT, @B INT, @C INT
SET @ A =5
SET @ B =10
SET @ C =@A
SET @ A =@B
SET @ B =@C
PRINT ‘ A=:’+ CONVERT (VARCHAR, @A)
PRINT ‘ B=:’+ CONVERT (VARCHAR, @B)
4) WRITE THE ABOVE PROGRAM WITHOUT USING THE THIRD VARIABLE
IF- ELSE: IT IS USED TO EXECUTE THE STATEMENTS BASED ON CONDITION.
SYNTAX : IF
< STATEMENT 1>
ELSE
< STATEMENT 2>
IF CONDITION IS TRUE THEN STATEMENT1 IS EXECUTED OTHERWISE STATEMENT 2 WILL BE EXECUTED
DECLARE @A INT, @B INT,
SET @ A =5
SET @ B =10
IF(@A>@B)
PRINT ‘B IS BIG’
ELSE
PRINT ‘B IS BIG OR EQUAL TO A’
DECLARE @A INT
SELECT @=COUNT(*) FROM EMP
IF (@A>0)
PRINT ‘TABLE WILL HAVE RECORDS’
ELSE
PRINT ‘NO RECORD IN THE TABLE
DECLARE @A VARCHAR(10)
SELECT @A= JOB FROM EMP WHERE EMPNO =7369
IF (@A=’CLERK’)
PRINT ‘EMPLOYEE WORKING AS CLERK
ELSE
PRINT ‘EMPLOYEE IS :’ +@A
WHILE:
SYNTAX: WHILE(CONDITION)
IF THE CONDITION IS TRUE THEN STATEMENTS UNDER WHICH WILL BE EXCLUDED
WRITE A PROGRAM TO PRINT THE FIRST 10 NO’S USING WHILE
à DECLARE @N INT,
SET @ N =1
WHILE(@N<=10)
BEGIN
PRINT @N
SET @N = @N+1
END
WRITE A PROGRAM TO PRINT ALL EVEN NO’S FROM 1 TO 10
à DECLARE @N INT,
SET @ N =1
WHILE(@N<=10)
BEGIN
IF (@N/2=0)
PRINT @N
SET @N = @N+1
END
VIEWS: VIEWS IS A LOGICAL TABLE BASED ON TABLE OR A VIEW VIEW WILL NOT HOLD NAY DATA OF ITS. IT IS JUST LIKE A WINDOW THROUGH WHICH WE CAN VIEW DATA IN THE TABLE.
TYPES OF VIEWS:
1) SIMPLE VIEW
2) COMPLEX VIEW
SIMPLE VIEW: A VIEW BASED ON SINGLE TABLE IS CALLED A SIMPLE VIEW.
· ALL DML ARE ALLOWED ON THE SIMPLE VIEW
· IT WILL NOT HAVE ANY ARITHMETIC EXPRESSIONS AND GROUP FUNCTIONS.
SYNTAX:
CREATE VIEW AS SELECT STATEMENT [WITH CHECK OPTION]
EX: SELECT * FROM EMP_VIEW
à IT WILL DISPLAY ALL DETAILS EMP TABLES.
INSERT INTO EMP _ VIEW (EMPNO, ENAME, DEPTNO) VALUES (100, RAM, 20)
à UPDATE EMP_VIEW SET SAL = SAL + 100
à DELETE FROM EMP_VIEW WHERE DEPTNO = 10
à ALL 10 DEPT EMPLOYEE DETAILS ARE DELETED FROM TABLE.
THE DML OPERATIONS PERFORMED ON THE VIEW WITH AFFECTS THE TABLE.
EX: CREATE VIEW V10 AS SELECT * FROM EMP WHERE DEPTNO – 10
à INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (200, ‘JAMES’,10)
à INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (30, ‘MORTA’,20)
à SELECT * FROM V10
à IT WILL DISPLAY ALL 100 DEPT RECORDS. IT WILL NOT DISPLAY RECENTLY INSERTED 20 DEPT RECORD SINCE IT IS FOCUSING ONLY ON 10 DEPT.
à SELECT * FROM EMP
à ALONG WITH OLD RECORDS NEWLY INSERTED RECORDS WILL ALSO BE DISPLAYED.
à UPDATE VIO SET SAL= SAL+ 1000
à ALL 10 DEPT EMPLOYEES.
SALARIES ARE MODIFIED
CREATING VIEW BASED ON ANOTHER VIEW:
DEPT NO DNAME LOC
à CREATE VIEW DEPT_V1 AS SELECT * FROM DEPT
à SELECT * FROM DEPT_V1
à CREATE VIEW DEPT_V2 AS SELECT DEPTNO, DNAME FROM DEPT_V1
à SELECT * FROM DEPT_V1
DEPT NO DNAME
à CREATE VIEW V1 AS SELECT EMPNO, ENAME, JOB, DEPT FROM EMP
à SELECT * FROM DEPT_V1
EMPNO ENAME JOB DEPTNO
à CREATE VIEW V2 AS SELECT EMPNO, ENAME, SAL FROM V1
CREATE VIEW WITH CHECK OPTION:
à CREATE VIEW V20 AS SELECT * FROM EMP WHERE DEPTNO=20 WITH CHECK OPTION.
à SELECT * FROM V20
à IT WILL DISPLAY ONLY 20 DEPT EMPLOYEE DETAILS
à INSERT INTO V20 (EMPNO, ENAME,DEPTNO) VALUES (111,’ CHRIS’,10)
à THIS RECORDS WILL NOT BE INSERTED SINCE VIEW IS CREATED WILL CHECK OPTION
DISPLAYING THE SELECT STATEMENT ASSOCIATED WITH THE VIEW
SYNTAX:
SP_HELPTEXT
2) COMPLEX VIEWS: A VIEW BASED ON
I) ARITHMETIC EXPRESSIONS
II) GROUP FUNCTIONS
III) MORE THAN ONE TABLE IS CALLED COMPLEX VIEW
EX:
à CREATE VIEW EMP_GROUP AS SELECT DEPTNO, SUM(SAL) SUM_SAL, AVG(SAL) AVG_SAL, MIN(SAL), MIN_SAL, MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO
à SELECT * FROM EMP_GROUP
DEPTNO SUM_SAL AVG_SAL MIN_SAL MAX_SAL
VIEW BASED ON ARITHMETIC EXPRESSION:
à CREATE VIEW EMP_ARTH AS EMPNO, SAL, BASIC, SAL+0.35, HRA, SAL+0.25, DAA+ 0.15,PF, SAL +0.35+SAL*0.25- SAL*0.15 GROSS FROM EMP
SELECT * FROM EMP_ARTH
DEPTNO BASIC HRA DA PF GROSS
VIEW BASED ON MORE THAN ONE TABLE:
à CREATE VIEW EMP_DEPT AS SELECT ENAME, JOB, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO.
à SELECT * FROM EMP_DEPT
ENAME JOB SAL DNAME LOC
SMITH CLERK 800 RESEARCH DALLAS
ALLEN SALESMAN 1600 SALES CHICAGO
MILLER CLERK 1300 ACCOUNTING NEWYORK
à INSERT INTO EMP_DEPT(ENAME, JOB, SAL, DNAME, LOC) VALUES (‘***’, ‘JI’, 1000, ‘IMPORT’, ‘HYD’)
à ERROR SINCE IT IS NOT POSSIBLE TO INSERT DATA INTO TWO TABLES THOUGH SINGLE VIEW
à INSET INTO EMP_DEPT (ENAME.JOB.SAL) VALUES (‘***’, ‘JI’, 1000)
à THIS IS VALID STATEMENT.
DROPPING THE VIEW
DROP VIEW
à DROP VIEW EMP DEPT
ALTERING THE VIEW:
ALTER VIEW AS SELECT STATEMENT
CURSORS:
RESULTS SET: SELECT STATEMENT RETURNS ALL THE ROWS WHICH SATISFIED THE CONDITION IN WHERE CLAUSE THE SET OF ROWS RETURNED BY THE SELECT ARE CALLED RESULT SET.
CURSOR: CURSOR IS A LOGICAL AREA USED FOR STORING TRANSACTION INFORMATION TEMPORARILY CURSOR IS THE DATABASE OBJECT USED TO PROCESS THE SET OF RECORDS INDIVIDUALLY, ON RECORD AT A TIME.
TYPES OF CURSOR:
1) STATIC CURSOR
2) DYNAMIC CURSOR
3) FORWARD ONLY CURSOR
4) KEY SET DRIVEN CURSOR
NECESSARY STEPS TO PROCESS CURSOR
1) DECLARE CURSOR
2) OPEN CURSOR
3) FETCH RECORD FROM THE CURSOR
4) CLOSE CURSOR
5) DEAL LOCATE CURSOR
1) DECLARE THE CURSOR:
SYNTAX: DECLARE CURSOR CURSOR TYPE FOR SELECT STATEMENT
EX: DECLARE EMP_CUR CURSOR STATIC FOR SELECT * FROM EMP
à DECLARE AUTHORS_CUR CURSOR DYNAMIC FOR SELECT * FROM AUTHORS
CURSOR DECLARATION THE NAME OF THE CURSOR AND SELECT STATEMENT ASSOCIATED WITH IT.
2) OPENING THE CURSOR:
SYNTAX: OPEN
OPEN EMP_CUR
OPEN AUTHORS_CUR
3) FETCH DATA FROM THE CURSOR: ONCE CURSOR IS OPENED RECORDS CAN BE FETCHED FROM IT
SYNTAX: FETCH FROM (OR)
FETCH FROM INTO
FETCH STATEMENT IS USED TO DISPLAY DATA ON SECREEN (OR ) DUMP THE DATA INTO VARIABLES.
KEYWORDS:
1) FIRST: USED TO FETCH THE FIRST RECORD
2) NEXT: USED TO FETCH RECORDS NEXT TO THE PREVIOUSLY FETCHED RECORD
3) LAST: USED TO FETCH THE LAST RECORD FROM THE RESULT SET
4) PRIOR : USED TO FETCH RECORDS BEFORE THE PREVIOUSLY FETCHED RECORD
5) ABSOLUTE N : IF N IS POSITIVE, IT FETCHES NTH RECORD FROM THE BEGINNING, IF ‘N’ IS NEGATIVE IS FETCHES NTH RECORD FROM THE LAST IF N=0, NO RECORD WILL BE FETCHED.
6) RELATIVE N: IT FETCHES NTH RECORD FROM THE PREVIOUSLY FETCHED RECORD.
IF N IS POSITIVE IT FETCHES NTH RECORD FOR PREVIOUSLY FETCHED RECORD
IF N IS NEGATIVE IT FETCHES NTH RECORD BEFORE FOR PREVIOUSLY FETCHED RECORD
IF N=0 SAME RECORD WILL BE FETCHED.
FETCHING: THE PROCESS OF RETRIEVING THE SINGLE RECORD FROM THE RESULT SET IS CALLED FETCHING
SCROLLING: THE PROCESS OF PERFORMING THE SERIES OF FETCHES EITHER IN FORWARD OR BACKWARD DIRECTION IS CALLED SCROLLING
DEFAULT CURSOR IS THE FORWARD ONLY CURSOR
FETCH NEXT IS THE ONLY FETCH STATEMENT VALID WITH FORWARD ONLY CURSOR
EX: FORWARD ONLY CURSOR:
Þ USE PUBS
Þ DECLARE AUTHORS_CUR CURSOR FOR SELECT * FROM AUTHORS
Þ OPEN AUTHORS_CUR
Þ FETCH FIRST FROM AUTHORS_CUR
Þ ERROR BECAUSE FETCH NEXT IS THE ONLY FETCH OPTION VALID WITH FORWARD ONLY CURSOR
Þ FETCH NEXT FROM AUTHORS_CUR
Þ FIRST RECORD IN THE TABLE WILL BE DISPLAYED
4) CLOSING THE CURSOR: CLOSE
EX: CLOSE EMP_CUR
CLOSE AUTHOR_CUR
IT IS ILLEGAL TO FETCH THE DATA FROM THE CLOSED CURSOR, DATA CAN BE FETCHED FROM THE CURSOR AFTER IT WAS OPENED
5) DEALLOCATE THE CURSOR:
SYNTAX: DEALLOCATE
EX: DEALLOCATE AUTHORS_CURS
NOTE: IT IS NOT POSSIBLE TO OPEN THE DEALLOCATED CURSOR
CURSOR WITH SCROLL OPTION: IF CURSOR IS DECLARED WITH SCROLL OPTION ALL FETCH OPERATIONS ARE VALID ON THE CURSOR.
EX:
Þ USE PUBS
Þ DECLARE AUTHORS_CUR CURSOR SCROLL FOR SELECT * FROM AUTHORS
Þ OPEN AUTHOR_CUR
Þ FETCH NEXT FROM AUTHORS_CUR
Þ FIRST RECORD IS FETCHED
Þ FETCH NEXT FROM AUTHORS_CUR
Þ RECORD NEXT TO THE 1ST RECORD IS FETCHED
Þ FETCH LAST FROM AUTHORS_CUR
Þ LAST RECORD IS FETCHEDFETCH PRIOR FROM AUTHORDS_CUR
Þ RECORD BEFORE THE LAST RECORD WILL BE FETCHED
Þ FETCH ABSOLUTE 5 FROM AUTHORS_CUR
Þ FETCH ABSOLUTE 2 FROM AUTHORS_CUR
Þ FETCH RELATIVE 0 FROM AUTHORS_CUR
Þ FETCH RELATIVE 5 FROM AUTHORS_CUR
Þ FETCH FIRST FROM AUTHORS_CUR
Þ FETCH PRIOR FROM AUTHORS_CUR
STATIC CURSOR:
Þ USE PUBS
Þ DECLARE DEPT_CUR CURSOR STATIC FOR SELECT * FROM DEPT
Þ OPEN DEPT_CUR
Þ FETCH FIRST FROM DEPT_CUR
10 ACCOUNTING NEWYORK
Þ UPDATE DEPT SET LOC =’HYD’ WHERE DEPTNO =10
Þ FETCH FIRST FROM DEPT_CUR
10 ACCOUNTING NEWYORK
DML OPERATIONS PERFORMED ON THE ORIGINAL TABLE WILL NOT DISPLAYED BY CURSOR
WHEN CURSOR IS CLOSED AND REOPENED MODIFICATION WILL APPEARS
ALL FETCH OPTIONS ARE VALID WITH STATIC CURSOR
Þ SELECT * FROM DEPT
Þ MODIFICATIONS APPEARS
Þ CLOSE DEPT_CUR
Þ OPEN DEPT_CUR
Þ FETCH FIRST FROM DEPT_CUR
DYNAMIC CURSORS:
SYNTAX : DECLARE CURSOR DYNAMIC FOR SELECT STATEMENT.
IN CASE OF DYNAMIC CURSOR, THE OPERATIONS PEFORMED ON THE TABLE AFTER OPENING THE CURSOR WILL APPEARS THOUGH THE CURSOR.
Þ DECLARE DEPT_CUR CURSOR DYNAMIC FOR SELECT * FROM DEPT
Þ OPEN DEPT_CUR
Þ INSERT INTO DEPT VALUES (50, IMPORT’, ‘HYD’)
10 ACCOUNTING HYD
Þ FETCH FIRST FROM DEPT_CUR
Þ FETCH LAST FROM DEPT_CUR
10 IMPORT HYD
Þ FETCH PRIOR FROM DEPT_CUR
10 OPERATIONS BOSTON
*FETCH ABSOLUTE 2 FROM DEPT_CUR
KEY SET CURSOR:
Þ CREATE TABLE DEPT AND INSERT DATA INTO THE TABLE
Þ CREATE ON UNIQUE INDEX ON DEPTNO COLUMN
Þ DECLARE DEPT_KEYCUR CURSOR KEY SET FOR SELECT * FROM DEPT
KEY SET CURSOR:THE TABLE SHOULD HAVE AT LEAST ONE UNIQUE INDEX COLUMN TO CREATE THE KEY SET CURSOR
· WHEN CURSOR IS OPENED THE INDEX COLUMN OF THE TABLE IS TRANSFERRED TO THE NO-INDEX COLUMN CAN BE VISIBLE THOUGH CURSOR BUT THE MODIFICATION ON THE INDEX COLUMN WILL NOT APPEARS TO THE CURSOR
· THE MODIFICATION PEFORMED ON THE NON-INDEX COLUMNS CAN BE VISIBLE THROUGH CURSOR BUT THE MODIFICATION ON THE INDEX COLUMN WILL NOT APPEARS TO THE CURSOR
· IF THE TABLE WILL NOT HAVE ANY UNIQUE INDEX COLUMN IT AUTOMATICALLY CREATS THE STATIC CURSOR
· OPEN DEPT_KEYCUR
· UPDATE DEPT SET DNAME= ‘EXPORT’, LOC=’HYD’ WHERE DEPTNO = 10
· FETCH FIRST FROM DEPT_KEYCUR
10 EXPORT HYD
· UPDATE DEPT SET DEPTNO=50 WHERE DNAME = ‘OPERATIONS’ FETCH LAST FROM DEPT_KEYCUR
· NODATA IS DISPLAYED
SINCE THE MODIFICATION ON INDEX COLUMN WILL NOT BE VISIBLE THROUGH CUROSR.
TYPE WARNING: IF THIS KEYWORD IS USED WHILE DECLARING THE KEY SET CURSOR THE SERVER WILL DISPLAY MESSAGE IF IT FAILS TO CREATE KEYSET CURSOR
Þ DEPT TABLE WILL NOT HAVE ANY INDEX COLUMN
Þ DECLARE DEPT_KEYSET CURSOR KEYSET TYPE_ WARNING FOR SELECT * FROM DEPT
MESSAGE: CURSOR CREATED IS NOT OF REQUESTED TYPE I.E STATIC CURSOR IS CREATED.
NOTEL @@FETCH_STATUS: RETURNS THE STATUS OF LAST FETCH OPERATION THE POSSIBLE VALUES FOR @@ FETCH_STATUS ARE
1) 0à RECORD FETCHED SUCCESSFULLY
2) 1à FETCH OPERATIONS FAILS: YOU ARE BEYOND THE LAST RECORD OR BEFORE THE FIRST RECORD.
Ø FETCH FIRST FROM DEPT_CUR
Ø PRINT @@ FETCH_STATUS
0
Ø FETCH PRIOR FROM DEPT_CUR
1
SINCE NO RECORD IS FETCHED
Ø FETCH LAST FROM DEPT_CUR
Ø PRINT @@ FETCH_STATUS
0
Ø FETCH NEXT FROM DEPT_CUR
Ø PRINT @@FETCH_STATUS
1
SINCE NO RECORD IS FETCHED
Ø CREATE TABLEL (EMPNOT INT, ENAME VARCHAR(20))
Ø INSERT DATA
Ø DECLARE EMP1_CUR CURSOR FOR SELECT * FROM EMP1
Ø DECLARE @ VNO INT, @VNAME VARCHAR(20) OPE EMPL_CUR
FETCH NEXT FROM EMPL_CUR INTO @ VNO, @VNAME WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT @ VNO
PRINT @ VNAME
FETCH NEXT FROM EMPL_CUR INTO @VNO, @VANME
END.
IF (@@ FETCH _STATUS =1)
PRINT ‘ALL RECORDS ARE FETCHED’
PROCEDURES
PROCEDURES : PROCEDURES IS A PRE_COMPILED COLLECTION OF T_SQL STATEMENTS PROCESSED AS A UNIT PROCEDURE WILL HAVE THE HEADER, DECLARATIVE SECTION AND EXECUTABLE SECTIONS.
TYPES OF PROCEDURES: 1) SYSTEM STORED PROCEDURES
2) USER DEFINED STORED PROCEDURES.
USED DEFINED STORED PROCEDURES: THE PROCEDURES CREATED BY THE USER ARE CALLED USED DEFINED STORED PROCEDURES.
SYNTAX: CREATE PROCEDURE / PROC
@PARA 1 DATATYPE (SIZE)[=DEFAULT_VALUE][OUTPUT]
@PARA 2 DATATYPE (SIZE)[=DEFAULT_VALUE][VALUE]
AS SELECT STATEMENT
WRITE A SIMPLE PROCEDURE TO THE COMPLETE DATA FROM EMP TABLE
A) à CREATE PROCEDURE EMP_DATA AS SELECT * FROM EMP
à EXECUTING THE PROCEDURE:
SYNTAX: EXEC
à
EXEC EMP_DATA
ALL EMPLOYEES DETAILS ARE DISPLAYED IN THE EMP TABLE
EX:
CREATE PROC SAL_DATA AS SELECT EMPNO, SAL BASIC ,SAL * 0.35HRA, SAL*0.25 DA, SAL *0.15 PF, SAL+SAL*0.35+ SAL – 0.15 GROSS FROM EMP
à EXEC SAL_DATA
EMPNO BASIC HRA DA PF GROSS
WRITE A PROCEDURE TO FIND THE SUM OF TWO NO’S
A) à CREATE PROC ADD_NUM AS DECLARE @A INT, @B INT, @C INT
SET @A=5
SET @B=10
SET @C=@A+@B
PRINT THE SUM IS ‘+’ CONVERT (VARCHAR, @C)
à EXEC ADD_NUM
THE SUM IS =15
WRITE A PROCEDURE TO FIND SIMPLE INTEREST?
A) CREATE PROC SIMPLE_INTEREST AS DECLARE @P INT, @N DECIMAL (5,2), @R DECIMAL (5,2), @5I DECIMAL (5,2)
à SET @SI = (@P*@N*@R*)/100
PRINT “SIMPLE INTEREST:”+CONVERT (VARCHAR, @SI)
WRITE A PROCEDURE TO FIND THE SUM OF TWO STRINGS
A) à CREATE PROC ADD_STRINGS AS DECLARE @A VARCHAR(20), @B VARCHAR(20), @C VARCHAR(20),
SET @A=’ORACLE’
SET @B=’APPLICATIONS’
SET @C=@A+’ ‘ +@B
PRINT @C
à EXEC ADD_STRINGS
ORACLE APPLICATIONS
WRITE A PROCEDURE TO FIND THE SUM OF TWO NO’S
A) à CREATE PROC ADD_NUM @A INT, @B INT, AS DECLARE @C INT
SET @C=@A+@B
PRINT THE ‘SUM OF A AND B IS ‘ + CONVERT (VARCHAR, @C )
à EXEC ADD_NUM 3,5
OUTPUT:: THE SUM OF A AND B IS 8
DECLARE @M INT, @N INT,
SET @M=5
SET @N=10 EXECUTE ALL THE ONCE
EXEC ADD_NUM@M,@N
INSERTING DATA INTO A TABLE USING PROCEDURE:
à CREATE PROC INSERT DATA
@VNO INT, @ VNAME VARCHAR (20). @LOC VARCHAR (10) AS INSERT INTO DEPT VALUES[@VNO, @VNAME.@LOC].
DEPTNO DNAME LOC
10 ACC NEW
20 RESE HYD
à EXEC INSERT_DATA 10, ‘ACC’, ‘NEW’
à SELECT * FROM DEPT.
WRITE A PROCEDURE TO DISPLAY DETAILS OF ON EMPLOYEE TAKE EMPO THE
à CREATE PRO EMP DETAILS
@VNO INT
AS SELECT * FROM EMP WHERE EMPO = @ VNO
à EXECUTE EMP_DETAILS 7369
WRITE A PROCEDURE TO FIND THE NET SAL SAL OF AN EMP CREATE PROC NET_SAL
@VNO INT AS
DECLARE @ VSAL INT @VCON INT, @NET INT
SELECT @ VSAL = SAL, @ VCOM FROM EMP WHERE
EMPO = @ VNO
IF @VCOM IS NULL
BEGIN
PRINT ‘COMM IS NULL’
PRINT ‘NET SAL IS’ CONVERT (VARCHAR, @ VSAL)
END
WRITE A PROCEDURE TO FIND THE NET SAL SAL USING OUT PARAMETERS
A) à CREATE PROC NET SAL
@VNO INT , @NET INT OUTPUT
AS
DECLARE @ VSAL =SAL, @VCOM =COMM, FROM EMP WHERE EMPNO = @ VNO
IF @VCOM IS NULL
BEGIN
PRINT ‘COMM IS NULL’
SET @ NET = @VSAL
END
ELSE
BEGIN
SET @NET= @VSNL + @COMM
END
EXECUTING PROC:
DECLARE @K INT
EXEC NET_SAL 7839, @ K OUTPUT
PRINT @K
ADMINISTRATOR LOGIN
CREATE PROC EMP_DEPT
SELECT ENAME, DNMAE FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
GRANT EXEC ON EMP_DEPT TO RAM
CONNECT TO RAM LOGIN
EXEC EMP_DEPTà DATA IS DISPLAYED SINCE ADMINSTATOR HAS GIVEN EXECUTE PERMISSION ON EMP_DEP
ALTERING THE PROCEDURE:
SYNTAX: ALTER PROC @ PARA 1. DATATYPE @PARA 2 DATA TYPE (SIZE) AS SQL STATEMENT
DISPLAYING THE CODE OF EXISTING PROCEDURE:
SYNTAX: SP_TELPTAXT
EX: SP_TELP_TEXT EMP_DEPT
USER DEFINED FUNCTIONS: FUNCTIONS CREATED BY USER ARE CALLED USER DEFINED FUNCTIONS
TYPES OF USER DEFINED FUNCTIONS:
1) SCALAR VALUED FUNCTIONS
2) TABLE VALUED FUNCTIONS
SCALAR VALUED FUNCTIONS: THESE FUNCTIONS WILL RETURN A SCALAR VALUE TO THE CALLING ENVIRONMENT
SYNTAX: CREATE FUNCTION < FUNCTION_NAME> (PARA 1 DATA TYPE (SIZE), PARA 2 DATATYPE (SIZE)
RETURNS
AS
BEGIN
RETURN < SCALAR VALUE>
END
WRITE A FUNCTION TO FIND THE PRODUCT OF TWO NUMBERS
A) CREATE FUNCTION PROD_NUM
(@ A INT, @B INT)
RETURNS INT
DECLARE @ C INT
SET @C = @A * @B
RETURN(@C)
END
EXECUTING THE FUNCTION
à DECLARE @K INT
SET @K = DBO.PROD_NUM(3,5)
PRINT @K
WRITE FUNCTION TO FIND THE NET SALARY OF AN EMPLOYEE READ EMPNO THOUGH PARAMETER AND DISPLAY THE NET TO RETURN VALUE
A) CREATE FUNCTION NET_SAL
(@ VNO INT)
RETURNS INT
AS
BEGIN
DECLARE @ VSAL INT, @VCOM INT, @NET INT
SELECT @VSAL \ SAL, @VCOM=COM
FROM EMP WHERE EMPNO =@VNO
IF @ VCOM IS NULL
BEGIN
PRINT ‘COMMISION IS NULL’
SET @NET = @VSAL
END
ELSE
BEGIN
SET @ NET = @VSAL + @VCOM
END
RETURN (@NET)
DECLARE @ K INT
SET @K = NET_SAL (7839)
PRINT @K
2) TABLE VALUED FUNCTION: THESE FUNCTIONS WILL RETURN ENTIRE TABLE TO THE CALLING ENVIRONMENT.
SYNTAX:
CREATE FUNCTION
(PARA 1 DATA TYPE (SIZE)……….)
RETURNS TABLE
AS
BEGIN
RETURN (SELECT STATEMENT)
END
WRITE A FUNCTION TO RETURN ENTIRE DEPT TABLE
A) à CREATE FUNCTION DEPT_TAB()
RETURNS TABLE
AS
BEGIN
RETURN (SELECT * FROM DEPT)
END
à EXECUTING THE FUNCTION
à SELECT * FROM DEPT_TABLE()
DEPT DNAME LOC
à CREATE FUNCTION EMP_DEPT()
RETURN TABLE
AS BEGIN
RETURN(SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP DEPTNO = DEPT DEPTNO)
END
à SELECT * FROM EMP_DEPT()
ENAME DNAME
SMITH RESEARCH
MILLER ACCOUNTING
CREATING DEFAULT:
SYNTAX:
CREATE DEFAULT AS
EX:
à CREATE DEFAULT FILL_COLUMN AS ‘UNKNOWN’
BINDING THE DEFAULT FOR COLUMNS:
SYNTAX: SP_BINDDEFAULT FILL_COLUMN, ‘DEPT.LOC’
SP_BINDEFAULT FILL_COLUMN, ‘EMP.JOB’
à INSERT INTO DEPT(DEPTNO, DNAME)
VALUES(10,ACCOUNTING’)
à SELECT * FROM DEPT WHERE DEPTNO =10
DEPTNO DNAME LOC
10 ACCOUNTING UNKNOWN
à INSERT INTO EMP (EMPNO, ENAME, SAL)
VALUES (100, ‘PHILLIS’, 5000)
à SELECT EMPNO, ENAME, JOB FROM EMP WHERE EMPNO =100
EMPNO ENAME JOB
100 PHILLIS 5000
UNBINDING THE DEFAULT FROM THE BINDING COLUMN
SYNTAX: SP_UNBINDEFAULT
SP_UNBINDEFAULT ‘ DEPT.LOC’
SP_UNBINDEFAULT ‘ EMP.JOB’
DROPPING THE DEFAULT:
SYNTAX: DROP DEFAULT
DROP DEFAULT FILL_COLUMN
CREATING RULES:
RANGE RULE:
CREATE RULE RANGE_RULE AS @ RANGE >=10 AND @ RANGE <= 60
BINDING THE RULE:
SP_BINDEFAULT ,‘ TABLE.COLUMN’
SP_BINDEFAULT RANGE_RULE, ‘DEPT.DEPTNO’
INSERT INTO DEPT(DEPTNO) VALUES (70)
ERROR
LIST RULE:
à CREATE RULE LIST_RULE AS @ LIST IN (‘ACCOUNTING’, ‘SALES’, ‘OPERATIONS’, RESEARCH’, ‘EDU’)
à SP_BINDRULE LIST_RULE, ‘DEPT_DNAME’
à INSERT INTO DEPT(DEPTNO, DNAME) VALUES (20, IMPORT’)
ERROR SINCE DANME VALUES IS OUT OF LIST
UNBINDING THE RULES:
à SP_UNBIND RULE DEPT.DEPTNO
à SP_UNBIND RULE DEPT.DNAME
DROPPING THE RULES:
à DROP RULE
à DROP RULE ROLE_NAME
à DROP RULE LIST_NAME
TRIGGERS
TRIGGERS: TRIGGER IS A TYPE OF STORED PROCEDURE THAT IMPLICITLY THAT IMPLICITLY EXECUTED WHEN USER PEFORMS DML OPERATION ON THE TABLE.
IT WILL NOT ACCEPT ANY PARAMETERS
TYPES OF TRIGGERS:
!) INSERT TRIGGER
2) DELETE TRIGGER
3) UPDATE TRIGGER
SYNTAX: CREATE TRIGGER ON FOR/ AFTER, INSERT,/ UPDATE/ DELETE AS SQL STATEMENT.
I
INSERT TRIGGER: THIS TRIGGER FIRES WHEN USER PERFORMS INSERT OPERATION ON THE TABLE. WHEN USER INSERT A RECORD INTO THE TABLE THE TEMPORARY TABLE CALLED INSERTED IS CREATED
THE NEWLY INSERTED RECORD IS ALSO STORED IN INSERTED TABLE TEMPORARILY
à CREATE TRIGGER
FOR INSERT
AS
BEGIN
PRINT ‘TRIGGER T1 FIRE’
END
à CREATE TRIGGER2 ON DEPT
FOR
AS
PRINT ‘TRIGGER T2 FIRE’
END
à CREATE TRIGGER3 ON DEPT
FOR INSERT
AS
PRINT ‘TRIGGER T3 FIRE’
END
à INSERT INTO DEPT(DEPTNO) VALUES(50)
TRIGGER T1 FIRED
TRIGGER T2 FIRED
TRIGGER T3 FIRED
CHANGING THE FIRING ORDER OF TRIGGERS:
SYNTAX: SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘NAME_OF_TRIGGER’,
@ ORDER = ‘FIRST/LAST’
@ STMTYPE = ‘INSERT’/UPDATE/DELETE’
à SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘T3’
@ ORDER = ‘FIRST’
@ STMTYPE = ‘INSERT’
à SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘T1’
@ ORDER = ‘LAST’
@ STMTYPE = ‘INSERT’
à INSERT INTO DEPT (DEPTNO) VALUES (60)
TRIGGER T3 FIRED
ON DEPT
FOR UPDATE
AS
BEGIN
INSERT INTO DEPT_INSERT SELECT * FROM INSERT
INSERT INTO DEPT_DELETE SELECT * FROM DELETED
END
à UPDATE DEPT
SET DNAME = ‘EXPORT’
LOC = ‘SEC’ WHERE DEPTNO =10
à SELECT * FROM DEPT_INSERT
DEPTNO DNAME LOC
10 EXPORT SEC
à SELECT * FROM DEPT_DELETE
DEPTNO DNAME LOC
10 ACCOUNTING NEWYORK
INSTEAD OF TRIGGERS: THESE TRIGGER ARE MAINLY CREATED FOR VIEWS
SYNTAX: CREATE TRIGGER TRIGGER_NAME ON < VIEW_NAME>
INSTEAD OF INSERT/ UPDATE ./ DELETE
AS
SQL STATEMENT
à CREATE VIEW V10
AS
SELECT * FROM EMP WHERE DEPTNO =10
Þ SELECT * FROM V10
Þ IT WILL DISPLAY AN 10TH DEPT EMPLOYEES DETAILS
Þ CREATE TRIGGER V10_TRG
INSEAT OF INSERT
AS
UPDATE V10 SET SAL=SAL +500
Þ INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (100, ‘CHAD’, 10)
Þ SELECT * FROM V10
Þ INSTEAD RECORDS WILL NOT APPEARS BUT SALARIES ARE MODIFIED DUE INSTEAD OF TRIGGER
Þ CREATE VIEW DEPT_VIEW
AS
SELECT * FROM DEPT
Þ CREATE TRIGGER DEPT_TR
AS
ON DEPT_VIEW
FOR INSERT
AS
DELETE FROM DEPT_VIEW
Þ INSERT INTO DEPT_VIEW DATA DELETED DUE TO INSTEAD OF TRIGGER.
Þ CREATE VIEW EMP_DEPT
AS
SELECT EMPNO, ENAME, DEPT,.DEPTNO, DNAME FROM EMP,
DEPT WHERE EM.DEPTNO =DEPT.DEPTNO
EMPNO ENAME DEPTNO DNAME
à SELECT * FROM EMP_DEPT
ERROR
à INSERT INTO EMP_DEPT (EMPNO, ENAME, DEPTNO, DNAME) VALUES (100, ‘CHAD’, 50, ‘IMPORT’).
SINCE NOT POSSIBLE TO INSERT DATA INTO TWO TABLES THOUGH SINGLE VIEW.
à CREATE TRIGGER EMP DEPT_TRG
ON EMP_DEPT
FOR INSERT
AS
BEGIN
INSERT INTO EMP (EMPNO,ENAME) SELECT EMPNO, ENAME FROM INSERTED.
INSERT INTO DEPT(DEPTNO, DNAME) SELECT DEPTNO, DNAME FROM INSERTED
END.
à INSERT INTO EMP_DEPT (EMPNO, ENAME, DEPTNO, DNAME) VALUES (100, ‘CHAD’, 50, ‘IMPORT’).
à SELECT * FROM EMP
à NEW RECORD WILL IS DISPLAYED ALONG WITH OLD RECORD
à SELECT * FROM DEPT
à NEW RECORD WILL BE DISPLAYED ALONG WITH OLD RECORDS
DISPLAYING THE CODE OF TRIGGER:
SYNTAX: SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘EMP_DEPT’
DISPLAYING THE TRIGGERS INFORMATION FOR THE TABLE.
SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘DEPT’
DCL COMMANDS: THESE COMMANDS ARE USED GRANT OR REVOKE THE PERMISSION ON DATABASE OBJECTS TO OTHER USERS.
1) GRANT: USED TO GRANT THE PERMISSIONS ON DATA OBJECTS TO USERS ACCOUNTS
ADMINISTRATOR TOOLS
GRANT SELECT ON DEPT TO RAM
GRANT INSERT, SELECT ON EMP TO RAM123
RAM/ RAM 123 LOGIN
SELECT * FROM DEPT
à DEPT DATA IS DISPLAYED
INSERT INTO DEPT VALUES (50, ‘****’, ‘DDDD’)
à ERROR SINCE NO INSERT PERMISSION TO RAM
SELECT * FROM EMP
INSERT INTO EMP (EMPNO, ENAME) VALUES (400, ‘CHRIS’)
à 1 RECORD IN INSERTED
UPDATE EMP SET EMPNO =111 WHERE ENAME = ‘SMITH’
à ERROR SINCE NO UPDATE PERMISSION TO RAM
ADMINISTRATOR LOGIN:
REVOKE: USED TO TAKE BACK THE PERMISSIONS GIVEN TO THE USERS
REVOKE SELECT ON DEPT FROM RAM
REVOKE SELECT, INSERT AN EMP FROM RAM
ADMINISTRATOR LOGIN
GRANTING PERMISSION WITH GRANT OPTION:
GRANT SELECT ON DEPT TO RAM WITH GRANT OPTION
RAM LOGIN:
SELECT * FROM DEPT
à DATA WILL BE DISPLAYED
GRANT SELECT ON DEPT TO NTTS
NTTS LOGIN
SELECT * FROM DEPT
ADMINISTRATOR LOGIN
REVOKE SELECT ON DEPT FROM RAM CASCADE
à NOW BOTH RAM AND NTTS WILL LOOSE THE PERMISSION ON DEPT
à SP_WHO à IT WILL DISPLAY THE STATUS OF LOGINS.
DATA ARE RAW FACTS
EX : CUSTOMER NAME IN BANK
TOTAL MARKS OBTAINED BY STUDENT
INFORMATION: AN ORGANIZED DATA IS CALLED INFORMATION
EX : CUSTOMER NAME WITH BALANCES.
STUDENT REG NO WITH TOTAL MARKS.
DATA HIERARCHY:
BIT IT MAY BE EITHER 0 OR 1
IT REPRESENTS THE SMALLEST PART OF INFORMATION THAT COMPUTER CAN PROCESS.
BYTE : IT IS GROUP OF 8 BITS IT REPRESENTS A DIGIT, LETTERS OR SPECIAL SYMBOL.
FILED: IT IS A GROUP OF CHARACTERS.
EMPNO ENAME SAL JOB
100 SMITH 800 CLERK
RECORD: IT IS A GROUP OF LOGICALLY RELATED FIELD.
FILE : GROUP OF LOGICALLY RELATED RECORDS OR IT IS A FLOW OF CHARACTERS.
DATABASE: IT IS A GROUP OF LOGICALLY RELATED DATA.
(OR)
IT IS THE COLLECTION OF DATA STORED IN A FILE ON DISC
CLASSIFICATION OF DATA BASE BASED ON SIZE
· NORMAL DATABASE:<10 GB
· LARGE DATABASE : 10 GB TO 100 GB
· VERY LARGE DATABASE : 100 GB TO 1000 GB
· EXTREMELY LARGE DATABASE :> 1000 GB
FILE MANAGEMENT SYSTEM
FILE : IT IS A STREAM OF CHARACTERS
DISADVANTAGES :
1. NOT SUPPORT VARIABLES DECLARATION TO STORE (OR) HOLD DATA.
2. WASTAGE OF MEMORY DUE TO DATA REDUNDANCY.
3. MULTIPLE USERS CANNOT ACCESS THE SINGLE FILE AT A TIME.
4. SEARCHING IS A DIFFICULT TASK.
5. INFORMATION ACCESS IS SLOW.
DATABASE MANAGEMENT SYSTEM (DBMS)
IT IS A SUIT OF SOFTWARE PROGRAM FOR CREATING, MAINTAINING & MANIPULATING THE DATA IN DATABASE. (OR)
IT IS A COLLECTION OF INTER RELATED DATA AND SET OF PROGRAM TO ACCESS THE DATA
IT ALLOWS THE ORGANIZATION TO STORE THE DATA IN ONE LOCATION FLOW, WHICH MULTIPLE USERS CAN ACCESS, THE DATA.
DIFFERENT DATABASE MODELS:
1. HIERARCHICAL MODEL
2. NETWORK MODEL
3. RELATIONAL MODEL
DATA IS ORGANIZED IN THE FORM OF TREE STRUCTURE WITH ONE LIMITATION THAT IS
“EVERY SUB NODE SHOULD HAVE ONLY ONE ROOT NODE
DRAWBACKS:
1. DATA REDUNDANCY
2. WASTAGE OF MEMORY
3.CROSS COMMUNICATION IS NOT POSSIBLE
2. NET WORK MODEL:
DATA IS ORGANIZED IN THE FORM OF ARBITRARY GRAPHS.
THERE IS NO GUARANTEE FOR ACCESS OF DATABASE WHENEVER THE DATABASE SIZE INCREASES.
CROSS COMMUNICATION IS POSSIBLE IN NDBMS.
IT CANNOT PROVIDE PROPER QUERY FACILITY, SO THAT WE HAVE TO BIG PROGRAMS EVEN FOR SMALL OPERATION.
3. RELATIONAL MODEL
IT USES THE COLLECTION OF TABLES TO REPRESENT BOTH THE DATA AND RELATIONSHIP AMONG THE DATA.
FEATURES OF RELATIONAL MODEL:
1. DATA IS STORED IN TABLES.
2. INTERSECTION OF ROWS AND COLUMNS WILL GIVE ONLY ONE VALUE.
3. RELATION AMONG DATA IS ESTABLISHED LOGICALLY.
4. THERE IS NO PHYSICAL LINK AMONG DATA
5. THERE IS NO DATA REDUNDANCY
6. HIGH SECURITY FOR DATA
7. IT SUPPORTS ANY TYPE OF DATA (EX: NUMBERS, NUMERIC, DATA, CHARACTER, DATE IMAGES ETC).
8. IT SUPPORTS NULL VALUES.
9. SUPPORT CODD RULES
10. IT SUPPORTS INTEGRITY CONSTRAINTS
11. MULTIPLE USERS CAN ACCESS DATA FROM ANY LOCATION.
NULL VALUE: IT IS UNKNOWN, UNASSIGNED AND UN COMPARABLE VALUE.
EX: 500 + NULL=NULL
ANY ARITHMETIC EXPRESSION CONTAINING NULL IS EVALUATED TO NULL
NULL IS NOT EQUAL TO ZERO OR NOT EQUAL TO SPACE.
RELATIONAL DATABSE: A DATABASE BASED ON RELATIONAL MODEL IS CALLED RELATIONAL DATABASE.
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS): IT IS A SUIT OF SOFTWARE PROGRAMS FOR CREATING, MARINATING AND MANIPULATING THE DATA IN THE RELATIONAL DATABASE.
DIFFERENT RDBMS PACKAGES:
Ø ORACLE
Ø SQL SERVER
Ø DB2
Ø SYBASE
SQL SERVER:
SQL SERVER 2000 IS AN RDBMS THAT USES TRANSACT-SQL TO SEND REQUESTS BETWEEN A CLIENT
COMPUTER AND A SQL SERVER 2000 COMPUTER. AN RDBMS INCLUDES DATABASES, THE DATABASE
ENGINE, AND THE APPLICATIONS THAT ARE NECESSARY TO MANAGE THE DATA AND THE COMPONENTS OF
THE RDBMS. THE RDBMS ORGANIZES DATA INTO RELATED ROWS AND COLUMNS WITHIN THE DATABASE.
THE RDBMS IS RESPONSIBLE FOR ENFORCING THE DATABASE STRUCTURE, INCLUDING THE FOLLOWING
TASKS:
■ MAINTAINING THE RELATIONSHIPS AMONG DATA IN THE DATABASE
■ ENSURING THAT DATA IS STORED CORRECTLY AND THAT THE RULES DEFINING DATA RELATIONSHIPS
ARE NOT VIOLATED
■ RECOVERING ALL DATA TO A POINT OF KNOWN CONSISTENCY IN CASE OF SYSTEM FAILURES
VERSIONS OF SQL SERVER:
1. IN 1993 MICRO SOFT AND SYBASE RELEASES 4.2 VERSION OF SQL SERVER FOR WINDOWS NT ENVIRONMENT.
2. 1995à MICROSOFT RELEASES SQL SERVER 6.0
3. 1996à MICROSOFT RELEASES SQL SERVER 6.5
4. 2000à MICROSOFT RELEASES SQL SERVER 2000
LATEST VERSION SQL SERVER 2005 NOT YET RELEASED.
FEATURES OF SQL SERVER 2000:
1. INTRODUCING SUPPORT FOR XML
2. USER DEFINED FUNCTION ARE INTRODUCED.
3. OLAP (ONLINE ANALYTICAL PROCESS) SERVICES AVAILABLE IN SQL SERVER 7.0 ARE NOW CALLED AS SQL SERVER 2000 ANALYSIS SERVICES
4. REPOSITORY COMPONENTS AVAILABLE IN SQL SERVER NOW CALLED META DATA SERVICES.
DATABASE SIZES:
· MS ACCESS à 2 GB
· SQL SERVER – 6.0 à 1 TB
· SQL SERVER – 7.0 à 1,048,516 TB 3
· SQL SERVER – 2000 à 1,048,516 TB 3
DATABASE IN SQL SERVER:
1) SYSTEM DATABASES
2) USERS DEFINED DATABASES
SYSTEM DATABASES: COMPLETE SYSTEM LEVEL INFORMATION SQL SERVER IS STORED IN SYSTEM DATABASES. SQL SERVER USES SYSTEM DATABASES TO OPERATE USER DATABASES.
USER DATABASES: THE DATABASES CREATED BY USER.
NORMALIZATION
ENTITY : A PERSON, PLACE OR EVENT ABOUT WHICH THE INFORMATION IS STORED IS CALLED ENTITY.
EX: EMPLOYEE, DEPARTMENT, STUDENT, CUSTOMER.
ATTRIBUTE: THE CHARACTERISTIC OR PROPERTY THAT DESCRIBES AN ENTITY IS CALLED ATTRIBUTE.
EX : EMPNO, ENAME, JOB ETC ARE ATTRIBUTE OF ENTITY EMPLOYEE
CUSTID, NAME, ADDRESS ARE ATTRIBUTE OF ENTITY CUSTOMER
FUNCTIONAL DEPENDENCY: IN A RELATION R, AN ATTRIBUTE B IS SAID TO BE FUNCTIONALLY DEPENDENT AN ATTRIBUTE A IF THE VALUE IN B IS UNIQUELY IDENTIFY BY THE VALUE IN A.
THE FUNCTION DEPENDENCY OF ‘B’ AN ‘A’ IS REPRESENTED AS FOLLOWS
R: AàB
EX: 1. SSN à NAME, ADDRESS, DATE OF BIRTH
IE A PERSON NAME, ADDRESS, DATE OF BIRTH
FUNCTIONALLY DEPENDENT ON SOCIAL SECURITY NUMBER(SSN)
2. VIN à MAKE, MODEL, COLOR
IE MAKE, MODEL & COLOR OF VEHICLE FUNCTIONALLY
DEPENDS ON VIN (VEHICLE IDENTIFICATION NUMBER)
PARTIAL FUNCTIONAL DEPENDENCY:
PARTIAL FUNCTIONAL DEPENDENCY IS THE FUNCTIONAL DEPENDENCY IN WHICH SOME NON-KEY COLUMNS DEPENDS ON PART OF KEY COLUMN OR PRIMARY COLUMN.
IN THE ABOVE TABLE EMP NAME, SAL DEPENDS ONLY ON EMP NO BUT THE COURSE COMPLETED DAT WILL DEPENDS ON BOTH EMP NO & COURSE TITLE TO IDENTIFY THE ROW UNIQUELY IN A TABLE EMP NO, COURSE TITLE SHOULD BE DECLARED AS PRIMARY KEY COLUMNS. BUT THE NON-KEY ATTRIBUTES NAME, SQL DEPENDS ONLY EMP NO, IE PART OF A KEY COLUMN THIS TYPE OF FUNCTIONAL DEPENDENCY IS CALLED IT PARTIAL FUNCTIONAL DEPENDENCY.
TRANSITIVE DEPENDENCY
FUNCTIONAL DEPENDENCY BETWEEN THE TWO NON-KEY ATTRIBUTES IS CALLED THE TRANSITIVE DEPENDENCY.
Ø IN THE ABOVE TABLE CUSTID IS IDENTIFIED AS KEY COLUMN
Ø BUT REGION DEPENDS ONLY ON SALESMAN, IS CALLED TRANSITIVE DEPENDENCY.
COLUMNS REGION AND SALESMAN IS CALLED TRANSITIVE DEPENDENCY.
NORMALIZATION ; IT IS THE PROCESS OF DECOMPOSING THE RELATIONS (TABLES) IN TO SMALLER AND WELL STRUCTERD RELATIONS TO MINIMIZE THE DATA REDUNDANCY.
(OR)
IT IS THE PROCESS OF DECIDING WHICH ATTRIBUTED HAS TO GROUPED TO AVOID DATA REDUNDANCY.
THERE ARE SOME RULES IN NORMALIZATION EACH RULE IS CALLED NORMAL FORM.
NORMAL FORMS:
1. FIRST NORMAL FORM (1NF)
2. SECOND NORMAL FORM (2NF)
3. THIRD NORMAL FORM (3NF)
FIRST NORMAL FORMS (1NF): MULTI VALUED ATTRIBUTED SHOULD BE REMOVED FROM THE TABLE.
EXPLANATION: IN THE ABOVE TABLE EMP COURSE TITLE, COURSE COMPLETED DATE ARE MULTI VALUED ATTRIBUTES SINCE AT THE INTERSECTIONS OF ROW AND COLUMN THEY WILL GIVE MOVE THAN ON VALUE. TO BRING THE ABOVE TABLE TO INF MULTI VALUED ATTRIBUTED SHOULD BE REMOVED BY FILLING THE EMPTY FIELD WITH SUITABLE DATA AS SHOWN BELOW.
SECOND NORMAL FORM (2NF) : THE RELATION SHOULD BE IN INF AND THE PARTIAL FUNCTIONAL DEPENDENCY SHOULD BE REMOVED.
IN THE ABOVE TABLE EMP NO, COURSE TITLE ARE IDENTIFIED AS KEY COLUMN BUT NON-KEY COL LIKE NAME AND SQL WILL DEPENDS.
TO BRING THE TABLE TO SECOND NORMAL FORM THE NORMAL FUNCTIONAL DEPENDENCY SHOULD BE REMOVED BY DIVIDING THE TABLE INTO TWO TABLES AS SHOWN ABOVE.
THIRD NORMAL FORM (3NF): THE TABLE SHOULD BE IN THE 2NF AND TRANSITIVE DEPENDENCY SHOULD BE REMOVED.
EX: CUSTOMER
· CUSTID IS IDENTIFIED AS KEY-COLUMN BUT REGION DEPENDS ON SALESMAN.
· THE FUNCTIONAL DEPENDENCY BETWEEN NON-KEY ATTRIBUTES SALES MAN & REGION IS CALLED TRANSITIVE DEPENDENCY.
· TO BRING THE TABLE TO THIRD NORMAL FORM, TRANSITIVE DEPENDENCY SHOULD BE REMOVED BY DIVIDING THE TABLE INTO TWO TABLE AS SHOWN BELOW
SYSTEM AND SAMPLE DATABASE IN SQL SERVER:
WHEN SQL SERVER IS INSTALLED IN A SYSTEM SETUP WILL CREATES THE FOLLOWING SYSTEM AND SAMPLE DATABASE.
SYSTEM DATABASES:
1. MASTER
2. MODEL
3. TEMPDB
4. MSDB
SAMPLE DATABASES:
1. PUBS
2. NORTH WIND
1. MASTER DATABASE : IT CONTAINS SYSTEM LEVEL INFORMATION OF SQL SERVER. ALL LOGINS AND DATABASE INFORMATION IS STORED IN MASTER DATABASE.
2. MODEL DATABASE : IT IS A TEMPLATE FOR USER DATABASES. WHEN USES IS CREATED A DATABASE THE COMPLETE INFORMATION WHICH IS THERE IN MODEL DATA WILL BE COPIED INTO THE USER DATABASE.
3. TEMPDB: IT FOR HOLDING TEMPORARY OBJECTS WHICH ARE CREATED WHEN SQL SERVER STARTS.
4. MSDB: SQL SERVER AGENT FOR SCHEDULING JOBS AND ALERTS USES IT.
PUBS & NORTH WIND: SAMPLE DATABASES
SUB LANGUAGES IN T-SQL COMMANDS:
1. DDL (DATA DEFINITION LANGUAGE)
2. DML (DATA MANIPULATION LANGUAGE)
3. DCL (DATA CONTROL LANGUAGE)
Ø DDL : THESE ARE USED TO CREATE, ALTER AND DROP THE DATABASE OBJECTS.
COMMANDS 1.CREATE
2. ALTER
3. DROP
Ø DML : THESE ARE USED TO INSERT, MODIFY AND DELETE THE DATA IN DATABASE OBJECTS.
à THESE COMMANDS ARE ALSO USED TO RETRIEVE THE DATA IN DB OBJECTS
COMMANDS 1.INSERT
2. UPDATE
3. DELETE
4. SELECT
Ø DCL : THESE ARE USED TO GRAND OR REVOKE THE PERMISSIONS ON DATABASE OBJECTS TO
OTHER USERS
COMMANDS 1.GRANT
2. REVOKE
TABLE IS THE PRIMARY STORAGE UNIT FOR DATA IN DB. MAX NO OF COLUMNS ALLOWED FOR A TABLES ARE 1024, MAX AMOUNT OF DATA ALLOWED PER A ROW 8060 BYTES.
DATA TYPE : THE DATA TYPE SPECIFIES THE TYPE OF DATA THAT CAN BE STORED IN A VARIABLE OR COLUMN OF A TABLE.
DATA TYPE IN SQL SERVER ARE CLASSIFIED INTO TWO TYPES:
1. PREDEFINED DATA TYPES.
2. USER DEFINED DATA TYPES.
PRE DEFINED DATA TYPES:
(a) CHAR [(N)]àFIXED LENGTH DATA TYPE USED TO STORE CHARACTER DATA, STATIC MEMORY ALLOCATION
à IT CAN STORE DATA UP TO 8000CHARACTERS.
à DEFAULT SIZE OF N IS 1 BYTE.
EX: 1.@V1 CHAR (5)
2.@V2 CHAR (10)
(b) VARCHAR (N)àVARIABLE LENGTH DATA TYPE. DYNAMIC MEMORY ALLOCATION
à IT CAN STORE DATA UP TO 8000CHARACTERS.
EX: 1. @ A VARCHAR (5)
MEMORY WILL BE ALLOCATED DYNAMICALLY
(c) NCHAR (N)àFIXED LENGTH OF CHARACTER DATA TYPE
à IT CAN STORE DATA UP TO 4000CHARACTERS.
(d) NVARCHAR (N)àVARIABLE LENGTH CHARACTER DATA TYPE
à IT CAN STORE DATA UP TO 4000 CHARACTERS.
NUMERIC DATA TYPE : USED TO STORE THE NUMERIC VALUES
DATATYPE SIZE
BIGINT 8 BYTES
INT 4 BYTES
SMALLINT 2 BYTES
TINYINT 1 BYTES
FLOAT 8 BYTES
REAL 4 BYTES
DATE DATA TYPE: USED TO STORE DATE AND TIME VALUES.
1. DATE TIME: à USED TO STORE DATE VALUES FROM 1ST JANUARY -1753 TO 31ST DECEMBER - 9999
à DEFAULT SIZE IS 8 BYTES
EX : IS DOJ DATE TIME.
2. DATE TIME: à USED TO STORE DATE VALUES FROM 1ST JANUARY -1990 TO 6TH JUNE - 1979
à DEFAULT SIZE IS 4 BYTES
SQL SERVER SERVICE MANAGER: BY USING THIS DBA CAN STOP, PAUSE OR START THE SERVER.
ENTERPRISE MANAGER: IT IS THE PRIMARY ADMINISTRATIVE TOOL FOR THE ADMINISTRATIVE TASK.
CREATING LOGINS: IN ENTERPRISE MANAGE.
SECURITYà LOGINS
↓ RIGHT CLICK ON THIS
à CLICK ON NEW LOGIN
à SELECT THE SQL SERVER AUTHENTICATION
NAME OF LOGIC
PASSWORD****
CLICK OK
VERIFYING THE PROPERTIES OF LOGIN: CLICK RIGHT CLICK ON LOGIN AND CLICK ON PROPERTIES
DEMO TABLES:
1. EMP TABLE
2. DEPT TABLE
EMP TABLE
EMPNO ENAME JOB MGR SAL COMM. HIREDATE DPTN
7369 SMITH CLERK 7902 800 NULL ----- 20
--- ---- --- --- --- --- ----- ---
--- ---- --- --- --- --- ---- ---
7934 MILLER CLERK 7506 1300 NULL --- 10
DEPT TABLE:
DEPT NO DNAME LOC
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
DDL COMMANDS
CREATE : IT IS USED TO CREATE DATABASE AND ITS OBJECTS LIKE TABLE, VIEWS ETC
à CREATE TABLE STUDENTS ( RNO INT, NAME VARCHAR (20), ADDRESS VARCHAR(50))
[SHIFT+HOME]—COMMANDS IS SELECT
F5à EXECUTE THE COMMAND
DESCRIBING THE STRUCTURE OF A TABLE:
SYNTAX : SP_HELP
EX: SP_HELP STUDENT
àCREATE TABLE DEPT (DEPTNO INT, DNAME VARCHAR(20), LOC VARCHAR(10))
à CREATE TABLE EMP (EMP INT, ENAME VARCHAR(20), JOB VARCHAR(10),SAL MONEY , DEPTNO INT )
à SP_HELP ‘EMP’ (DESCRIBES, STRUCTURE OF EMP TABLE)
à CREATE TABLE[CUST] (CUST ID INT, ENAME VARCHAR(2), SALESMAN CHAR(2))
ALTER: IT IS USED TO ALTER THE STRUCTURE OF EXISTING TABLES, IT IS MAINLY USED IN FOLLOWING CASES.
(1). WHEN USER WANTS TO ADD A COLUMN TO THE EXISTING COLUMN
(2). WHEN USER WANTS TO MODIFY THE COLUMN DEFINITION.
(3). WHEN USER WANTS TO DROP THE COLUMN IN THOSE SITUATIONS ARE USE ALTER TABLE
CREATE TABLE EMP(EMP NO INT, ENAME VARCHAR(20), JOB VARCHAR (10), MGR INT)
ADDING COLUMNS FOR EXISTING TABLE:
SYNTAX : ALTER TABLE ADD DATATYPE (SIZE), DATATYPE(SIZE)
· ALTER TABLE EMPL ADD HIRE_DATE DATE TIME
· ALTER TABLE EMPL ADD SAL INT COMM. IN, DEPTNO IN
· SP_HELP ‘EMP’
MOST RECENTLY ADDED COLUMN IS LAST COLUMN
MODIFYING THE COLUMN DEFINITION:
SYNTAX: ALTER TABLE ALTER COLUMN < COLUMN NAME> DATA TYPE (SIZE)
· ALTER TABLE EMPL ALTER COLUMN ENAME VARCHAR (30)
· ALTER TABLE EMPL ALTER COLUMN JOB VARCHAR (5)
· IT IS POSSIBLE IF COLUMN IS EMPTY (OR) SIZE IS SUFFICIENT FOR EXISTING OF COLUMNS
· ALTER TABLE EMPL ALTER COLUMN ENAME CHAR (20)
· IT IS VALID ENEN IF COLUMN CONSTAINS DATA.
· ALTER TABLE EMPL ALTER COLUMN EMPNO VARCHAR (20)
· IT IS VALID ONLY WHEN COLUMN IS EMPTY
· ALTER TABLE EMPL ALTER COLUMN JOB
· VALID WHEN COLUMN IS EMPTY IT IS VALID
DROPPING THE COLUMN FROM THE TABLE:
SYNTAX :
ALTER TABLE < TABLE_NAME> DROP COLUMN
à ONLY ONE COLUMN CAN BE DROPPED AT A TIME ONCE COLUMN IS DROPPED, THE DATA IN THE COLUMN WILL ALSO REMOVED.
à TABLE SHOULD HAVE AT LEAST ONE COLUMN IE IT IS NOT POSSIBLE TO DROP THE LAST COLUMN FROM THE TABLE.
à ALTER TABLE EMP DROP COLUMN EMPNO
àALTER TABLE DEPT DROP COLUMN LOC
àALTER TABLE DEPT DROP COLUMN DEPTNO
ERROR à SINCE LAST COLUMN CANNOT BE DROPPED FROM THE TABLE
RENAMING THE COLUMN OF TABLE
SYNTAX: SP_RENAME ‘TABLE. OLD-COLUMN-NAME’,’NEW-COLUMN-NAME’
EX: SP_RENAME ‘DEPT.DEPTNO’, ‘DEPT_NUMBER’
. SP_RENAME ‘EMP.SAL’, ‘SALARY’
RENAMING THE TABLE:
SYNTAX: SP_RENAME ‘OLD_TABLE_NAME’, ‘NEW_TABLE_NAME’
EX: SP_RENAME ‘DEPT’, ‘DEPT_TABLE’
DROPPING THE TABLE:
SYNTAX
DROP TABLE
DROP TABLE STUDENT
DROP TABLE EMP
DML COMMANDS:
1. INSERT: IT IS USED TO INSERT THE DATA INTO THE TABLES.
SYNTAX: INSERT INTO VALUES (VALUE1, VALUE2,….VALUE N)
DATE, VARCHAR, CHAR VALUES SHOULD BE IN SINGLE QUOTATION MARKS.
SINGLE QUOTATION MARKS ARE INVALID PER THE NUMERIC DATA.
àINSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK’)
àINSERT INTO DEPT VALUES(20,’RESEARCH’, ‘DALLAS’)
àINSERT INTO DEPT VALUES(30,’SALES’, ‘CHICAGO’)
àINSERT INTO DEPT VALUES(40,’OPERATIONS’, ‘BOSTON’)
DISPLAYING THE DATA IN THE TABLE:
à SELECT * FROM DEPT
DEPT NO DNAME 10C
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
à INSERT INTO EMP VALUES(7369,’SMITH’, ‘CLERK’, 7902,’15-MAR-98’,800,100,20)
INSERTING NULL VALUES INTO A TABLE
SYNTAX:
METHOD1:
IMPLICIT METHOD:
SYNTAX
INSET INTO (COLUMN1, COLUMN2,COLUMN3) VALUES
(VALUES1, VALUES2, VALUES3)
EX: INSERT INTO DEPT(DEPTNO, DANME, LOC)VALUES(10, NEWYORK)
INSERT INTO DEPT(DEPT NO) VALUES (20)
SELECT * FROM DEPT
DEPT NO DNAME 10C
10 NULL NEWYORK
20 NULL NULL
EXPLICIT METHOD:
.EXPLICITLY USER SHOULD PROVIDE THE NULL VALUES FOR THE COLUMNS
SYNTAX:
INSERT INTO VALUES (VALUE1, VALUE2, VALUE3)
EX: INSERT INTO DEPT VALUES(30,NULL, NULL)
SELECT * FROM DEPT
DEPT NO DNAME 10C
10 NULL NULL
20 NULL NULL
30 NULL NULL
40 OPERATIONS NULL
OPERATORS IN SQL SERVER:
· ARITHMETIC OPERATORS
· COMPARISON OPERATORS
· LOGICAL OPERATORS
· ASSIGNMENT OPERATORS
ARITHMETIC OPERATORS: IT IS USED TO FORM AN EXPRESSION WITH NUMERIC AND DATE VALUES.
OPERATOR + - * / %
MEANING ADD SUB MUL DIV MODULAR
COMPARISON OPERATORS: THESE ARE USED TO CO,PARE ARE EXPRESSION WITH ANOTHER EXPRESSION.
OPERATOR MEANING
> GREATER THAN
>= GREATER THAN OR EQUAL
< LESS THAN
<= LESS THAN OR EQUAL TO
!< NOT LESS THAN
!> NOT GREATER THAN
<> (OR) != NOT EQUAL TO
LOGICAL OPERATORS: THERE ARE USED TO COMBINE THE RESULTS OF 2 EXPRESSIONS INTO SINGLE ONE.
OPERATOR MEANING
AND RETURNS TRUE IF BOTH THE EXPRESSIONS ARE TRUE
OR RETURNS TRUE IF ONE OF THE TWO EXPRESSIONS IS TRUE
NOT RETURNS TRUE IF RESULT IS FALSE OTHER WISE RETURN FALSE.
P Q P AND Q P OR Q NOT P NOT Q
1 1 1 1 0 0
1 0 0 1 0 1
0 1 0 1 1 0
0 0 0 0 1 1
1à TRUE
0à FALSE
ASSIGNMENT OPERATOR: ‘=’ IS THE ONLY ASSIGNMENT OPERATOR AVAILABLE IN SQL SERVER
‘WHERE’ CLAUSE: IT IS A CONDITIONAL CLAUSE USED TO IMPOSE THE CONDITIONS. IT IS USED WITH UPDATE AND SELECT STATEMENT.
UPDATE: USED TO MODIFY THE DATA IN THE TABLE.
SYNTAX: UPDATE SET COLUMN 1=VALUE 1[, COLUMN2= VALUE2….]
* MODIFY THE SALARIES OF ALL THE EMPLOYEES IN EMP TABLE
àUPDATE EMP SCT SAL=SAL+1000
à ALL EMPLOYEES SALARIES ARE UPDATED
à THIS TYPE OF UPDATION IS CALLED HIGH LEVEL UPDATION
*MODIFY THE SALARIES OF ALL MANAGERS
à UPDATE EMP SET SAL=SAL+500 WHERE JOB= ‘SALES MAN’ OR JOB=’ANALYST’
*MODIFY THE SALARIES OF ALL THE CLERKS WORKING IN 20TH DEPARTMENT
àUPDATE EMP SET SAL= SAL+300 WHERE JOB=’CLERK’ AND DEPTNO=20
*MODIFY THE SALARIES OF ALL THE ANALYSTS IN THE BOTH DEPT AND ALL THE MANAGERS IN 30TH DEPT
àUPDATES EMP SET SAL = SAL + 3000 WHERE JOB = ‘ANALYST’ AND DEPTNO = 20 OR JOB= ‘MANAGER’ AND DEPTNO= 30.
DELETE: IT IS USED TO DELETE THE DATA FROM THE TABLE.
SYNTAX:
DELETE FROM [WHERE]
àDELETE FROM DEPT
à ALL RECORDS IN DEPT TABLE ARE DELETED
à THIS IS CALLED HIGH LEVEL DELETION
àDELETE FROM EMP
à ALL RECORD IN EMP TABLE ARE DELETED.
*DELETE ALL CLERKS DETAILS FROM EMP TABLE:
à DELETE FROM EMP WHERE JOB=’CLERK’
· DELETE ‘SMITH’ DETAILS FROM EMP TABLE
àDELETE FROM EMP WHERE ENAME=’SMITH’
· DELETE ALL EMPLOYEE WORKING IN 20 DEPT
* DELETE FROM EMP WHERE DEPT NO=20.
TRUNCATE: IT IS ALSO USED TO DELETE THE DATA FROM THE TABLE .BY USING THIS COMMAND DATA CANNOT BE DELETED CONDITIONALLY. THIS STATEMENT DELETES THE DATA IN PAGE BY PAGE MANNER.
SYNTAX: TRUNCATE
àTRUNCATE EMP
àTRUNCATE DEPT
àTRUNCATE EMP WHERE DEPT NO= 30
ERROR
QUERY: IT IS A REQUEST FOR INFORMATION
SIMPLE SELECT: SELECT COMMAND IS USED TO RETRIEVE THE DATA FROM TABLE(S)
IT CANNOT MODIFY THE DATA IN THE TABLE.
SYNTAX:
SELECT COLUMN 1, COLUMN2, COLUMN 3, ……………. COLUMN N FROKM
SELECT * FROM
‘*’ MEANS ALL COLUMNS AND ALL ROWS IN TABLE
à SELECT EMPO, DEPT NO, ENAME, FROM EMP.
EMPNO DEPTNO ENAME
7369 20 SMITH
7499 30 ALLEN
7521 30 WORD
7934 10 MILLER
NOTE: THE ORDER OF THE COLUMNS IN THE OUTPUT DEPENDS ON THE ORDER OF THE COLUMNS IN THE SELECT STATEMENT
WRITE A QUERY TO DISPLAY THE COMPLETE DATA FROM EMP TABLE
à SELECT * FROM EMP
SELECT WITH WHERE CLAUSE: WHERE CLAUSE IS USED WITH SELECT TO RETRIEVE THE DATA ON THE CONDITION
SYNTAX: SELECT COLUMN 1, COLUMN 2, COLUMN FROM WHERE
(OR)
SELECT * FROM WHERE
WRITE A QUERY TO DISPLAY EMPNO, ENAME ,JOB FOR ALL MANAGERS.
àSELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB=’MANAGER’
WRITE A QUERY TO DISPLAY ALL DETAILS OF 30 DEPT SALES MANS
à SELECT * FROM EMP WHERE JOB= ‘SALES MAN’ AND DEPTNO = 30
WRITE A QUERY TO DISPLAY ALL DETAILS OF ALL EMPLOYEE WHO ARE GETTING MORE THAN 3000
à SELECT * FROM EMP WHERE SAL>3000
‘ORDER BY’ CLAUSE: IT IS USED TO DISPLAY THE DATA EITHER IN ASCENDING (OR) DESCENDING ORDER.
DEFAULT ORDER IS ASCENDING
IT ALWAYS APPEARS AT THE AND OF SELECT STATEMENT
IT CAN BE USED WITH ANY COLUMN
SELECT WITH ’WHERE’ AND ‘ORDER BY’ CLAUSES :
SYNTAX:
SELECT COL1, COL2 – COLUMN FROM [WHERE CONDITION] ORDER BY COLUMN[ASC]/DESC.
SELECT * FROM [WHERE] ORDER BY COLUMN[ASC] / DESC
WRITE A QUERY TO DISPLAY EMPNO ENAME, SAL FOR EMPLOYEES IN EMP TABLE IN ASCENDING ORDER SALARIES
à SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL [ASC]
WRITE A QUERY TO DISPLAY ALL EMPLOYEE DETAILS IN DESCENDING ORDER OF SALARIES
à SELECT * FROM EMP ORDER BY SAL DESC
WRITE A QUERY TO DISPLAY ALL ENAMES IN ASCENDING ORDER
à SELECT ENAME FROM EMP ORDER BY ENAME
ENAME
ADAMS
ALLEN
BLAKE
----
-----
-----
WARD
WRITE A QUERY TO DISPLAY ALL DETAILS OF ALL EMPLOYEES IN DESCENDING ORDER OF HIREDATE
à SELECT * FROM EMP ORDER BY HIREDATE
WRITE A QUERY ENAME, SAL, JOB OF ALL 10 DEPT EMPLOYEES IN ASCENDING ORDER OF SALARIES
à SELECT EMPNO, JOB, SAL FROM EMP WHERE DEPTNO=10 ORDER BY SAL
ENAME JOB SAL
MILLER CLERK 1300
OLARK MANAGER 2450
KING PRESIDENT 5000
WRITE A QUERY TO DISPLAY ENAME , JOB, SAL, DEPTNO FOR ALL MANAGER IN DESCENDING ORDER OF SALARIES
à SELECT ENAME, JOB,DEPTNO, SAL FROM EMP WHERE JOB= ‘MANAGER’ ORDER BY SAL DESC
COLUMN ALIAS: IT IS A METHOD OF ASSIGNING THE TEMPORARY NAMES FOR THE COLUMNS WHILE DISPLAYING THE DATA.
ALIAS NAME IMMEDIATELY FOLLOWS THE COLUMN NAME WITH OPTIONAL ‘AS’ KEYWORD BETWEEN COLUMN NAME AND ALIAS NAME.
ALIAS NAME SHOULD BE IN SINGLE QUOTATION MARKS IF IT CONTAINS SPACES AND CHARACTERS OTHER THAN A TO Z, A TO Z, 0 TO 9, $, #, _(UNDER SCORE).
EX: SELECT EMPNO, SAL, COMM. AS COMMISSION FROM EMP
EMPNO SALARY COMMISSION
-------- ---------- ------------------
à SELECT DEPTNO, DNAME AS ‘DEPTNAME’, LOC LOCATION FROM DEPT
DEPTNO DEPTNAME LOCATION
SELECT STATEMENT WITH ARITHMETIC EXPRESSIONS:
WRITE A QUERY TO DISPLAY EMPNO, SAL AS SALARY PER MONTH, SALARY PER YEAR FOR ALL EMPLOYEES
à SELECT EMPNO, SAL ‘SALARY PER YEAR’ FROM AMP
WRITE A QUERY TO DISPLAY EMPNO HRA, DA, PLF & GROSS FOR EMPLOYEES IN EMP TABLE BY SAL AS BASIC.
à SELECT EMPNO, SAL, BASIC SAL *0.45 HRA, SAL*0.35 DA, SAL * 0.15 PF SAL + SAL *0.45+SAL * 0.35 – SAL*0.15 GROSS FROM EMP
EMPNO BASIC HRA DA PF GROSS
FUNCTIONS: FUNCTION IS A PRE DEFINED PROGRAM SEGMENT THAT CARRIES SPECIFIC AND WELL DEFINED TASK.
CLASSIFICATIONS OF FUNCTIONS:
1. SCALAR FUNCTIONS
2. GROUP OR AGGREGATE FUNCTIONS
3. ROW SET FUNCTIONS
1. SCALAR FUNCTIONS: THIS FUNCTIONS WILL ACTS AN SINGLE VALUE & RETURNS A SINGLE VALUE
CLASSIFICATION OF SCALAR FUNCTIONS:
1. MATHEMATICAL FUNCTIONS
2. STRING FUNCTIONS
3. DATE & TIME FUNCTIONS
4. SYSTEM FUNCTIONS
5. SECURITY FUNCTIONS
6. CURSOR FUNCTIONS
7. SYSTEM STASTICAL FUNCTIONS
8. TEXT AND IMAGE FUNCTIONS
9. CONFIGURATION FUNCTION.
MATHEMATICAL FUNCTIONS:
i) ABSOLUTE: IT RETURN THE ABSOLUTE VALUE OF N
à SELECT ABS(-10.65)
10.65
à SELECT ABS(18.69)
18.67
ii) POWER (M, N): IT RETURN THE M POWER N VALUE
à SELECT POWER(3,2)
9
à SELECT POWER(6,2)
36
iii) SQRT(N): IT RETURN THE SQUARE ROOT VALUE OF N
à SELECT SQRT(2)
1.414
iv) SQUARE (N): IT RETURN THE SQUARE VALUE OF N
à SELECT ABS(9)
81
à SELECT ABS(5)
25
v) ROUND(M, N): IT WILL ROUND THE VALUE OF M TO NEAREST WHOLE NUMBER OF IT WILL AROUND.
à SELECT ROUND(15.143)
15
à SELECT ROUND(16.513)
16
à SELECT ROUND(16.816)
17
à SELECT ROUND(21.132,1)
21.1
à SELECT ROUND(25.143)
25
vi) CEILING: IT (CEILING) RETURNS THE SMALLEST INTEGER GREATER THAN ‘N’
à SELECT CEILING(15,13)
16
vii) FLOOR(N): IT RETURN THE LARGEST INTEGER LESS THAN ‘N’
à SELECT FLOOR(15.13)
14
STRING FUNCTIONS:
i) ASCII (CH): IT RETURNS THE ASCII VALUE OF GIVEN CHARACTER.
à SELECT ASCII(‘A’)
65
à SELECT ASCII(‘A’)
97
à SELECT ASCII(‘0’)
48
à SELECT ASCII(‘’)
32
ii) CHAR(N): IT RETURNS THE CHARACTER FOR GIVEN ASCII VALUE
à SELECT CHAR(65)
A
à SELECT CHAR(98)
B
iii) LOWER (STRING): IT CONVERTS THE UPPER CASE LETTERS IN STRING INTO LOWER CASES
à SELECT LOWER(‘ORACLE’)
ORACLE
iv) UPPER(STRING): IT CONVERTS THE LOWER CASES INTO UPPER CASES
à SELECT UPPER(ORACLE)
ORACLE
à SELECT ENAME, LOWER(ENAME) LOWER_ENAMES FROM EMP
ENAME LOWER-ENAME
SMITH SMITH
ALLEN ALLEN
v) LEN(STRING): IT RETURNS THE LENGTH OF GIVEN STRING
à SELECT LEN(‘ORACLE’)
6
à SELECT LEN(‘ORACLE APPS’) SPACE ALSO CHARACTER
11
à SELECT ENAME, LEN(‘ENAME’) LENGTH FROM EMP
ENAME LENGTH
SMITH 5
ALLEN 5
MILLER 6
REVERSE (STRING) : IT WILL REVERSE THE GIVEN STRING
à SELECT REVERSE(‘ORACLE’)
ELCARO
àSELECT ENAME, REVERSE(ENAME) REV_ENAME FROM EMP
ENAME LENGTH
SMITH HTIMS
ALLEN NELLA
MILLER RELLIM
REPLACE (STRING, ‘SEARCHING STRING’, ‘REPLACING STRING’)
à IT WILL REPLACE A SUB STRING IN ASTRING WITH GIVEN STRING
à SELECT REPLACE (‘WRONG’, ‘WR’, ‘R’)
LONG
à SELECT REPLACE (‘JACK AND JUE’, ‘J’, ‘BI’)
BLACK AND BLUE
SUB STRING (‘STRING’, ‘STARTING POSITION’, NO OF CHARACTERS REQUIRED);
IT WILL ENTRACT A SUBSTRING FLOW MAIN STRING
EX:à SELECT SUBSTRING (‘ORACLE’, 1, 3’)
ORA
à SELECT SUBSTRING (‘ORACLE’, 2, 2)
RA
àSELECT ENAME, SUBSTRING(ENAME,1,2) SUN_ENAME FROM EMP
ENAME SUB_ENAME
SMITH SM
ALLEN AL
DATE FUNCTIONS:
GET DATE (); IT RETURNS THE CURRENT DATE AND TIME
à SELECT GETDATE()
2005-4-5: 4:30.15.416
DATE PART ABBREVIATION
YEAR YY
QUARTER QQ
MONTH MM
WEEK WW
DAY DD
HOUR HH
MINUTE MM
SIZE SS
MILLISEC MS
DATE ADD (DATE PART, NUMBER, DATE): IT RETURNS THE DATE ACCORDING TO DATE PART.
à SELECT DATE (YY,1,’2005-3-15’)
2006-3-15
à SELECT DATEADD (MM,1,’1998-4-18’)
1998-4-18
à SELECT DATE ADD(DAY,1,’1999-04-26’)
2006-3-15
DATE DIFF(DATEPART, DATE 1, DATE 2): IT RETURNS THE DIFFERENCE BETWEEN THE DATES ACCORDING TO THE DATE PART
àSELECT DATE DIFF(YEAR, ‘1999-11-5’,’2000-11-15’)
SPECIAL OPERATORS IN SQL SERVER:
1. IN
2. NOT
3. BETWEEN
4. NOTBETWEEN
5. LIKE
6. NOT LIKE
7. IS NULL
1) IN(LIST OF VALUES): IT PICKS THE VALUES FROM THE LIST
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF CLERK, MANAGER AND ANALYST
A) SELECT * FROM EMP WHERE JOB IN(‘CLERK’, ‘MANAGER’,’ANALYST’)
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF 7369,7839,7466
A) SELECT * FROM EMP WHERE JOB IN(‘7369’,’7839’,’7466’)
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF SMITH, ALLEN, KING
A) SELECT * FROM EMP WHERE JOB IN(‘SMITH’, ‘ALLEN’, ‘KING’)
2) NOT IN(LIST OF VALUES): IT IS JUST OPPOSITE TO IN. IT DISPLAY ALL THE VALUES IN TABLE, OTHER THAN VALUES SPECIFIED IN LIST
àSELECT * FROM EMP WHERE JOB NOT IN (‘MANAGER’,CLERK’, ‘PRESEDENT’)
à OTHER THAN ‘MANAGER’, ‘CLERK’, ‘PRESEDENT’ JOBS DETAILS WILL BE DISPLAYED
3) BETWEEN: IT WILL DISPLAY THE VALUES SPECIFIED IN THE RANGE
EX: SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 4000
IT WILL DISPLAY ALL THE EMPLOYEES DETAILS WHO ARE GETTING SALARY MORE THAN OR EQUAL TO 2000 AND LESS THAN OR EQUAL TO 4000.
IT INCLUDES THE RANGE SPECIFIED, SO IT IS CALLED INCLUSIVE OPERATOR.
4) NOT BETWEEN: IT IS OPPOSITE TO ‘BETWEEN’ OPERATOR IT WILL DISPLAY THE VALUES OTHER THAN RANGE SPECIFIED.
Q) WRITE A QUERY TO DISPLAY AU DETAILS WHO ARE GETTING SALARIES LESS THAN 2000 AND MORE THAN 4000
A) SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 4000.
NOTE: NOT BETWEEN OPERATOR WILL NOT INCLUDE THE RANGE SPECIFIED, SO IT IS CALLED EXCLUSIVE OPERATOR.
5) LIKE:IT IS USED TO SEARCH FOR PATTERN IN CHARACTERS. IT ASSOCIATED WITH TWO CHARACTERS
(I) _ (UNDERSCORE)
(II) %
HERE % REPRESENTS ZERO OR MORE CHARACTERS AND UNDER SCORE REPRESENTS A SINGLE CHARACTER.
WAQ TO DISPLAY THE NAMES BEGIN WITH ‘S’
(B) SELECT ENAME FROM EMP WHERE ENAME LIKE ‘S’
ENAME
SMITH
SCOTT
WAQ TO DISPLAY NAMES END WITH ‘S’
(A) SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%S’
ENAME
JAMES
JONES
WAQ TO DISPLAY NAMES CONTAINING ‘LL’ OR TT
àSELECT ENAME FROM EMP WHERE ENAME LIKE %LL% (OR) ENAME LIKE ‘%TT%’
SCOTT
MILLER
ALLEN
WAQ TO DISPLAY NAMES CONTAINING ‘A’ AS SECOND CHARACTER
(A) SELECT ENAME LIKE ‘_A%’
WAQ TO DISPLAY NAME CONTAINING 5 CHARACTERS
à SELECT ENAME FROM EMP WHERE ENAME LIKE’------‘
6) NOT LIKE: OPPOSITE TO LIKE OPERATOR.
7)IS NULL:IT IS USED TO TEST FOR NULL VALUES.
WAQ TO DISPLAY ALL EMPLOYEES WHO ARE HAVING NULL COMMISSION
(A) SELECT * FROM EMP WHERE COMM. IS NULL
WAQ TO DISPLAY THE EMPLOYEE WHO ARE NOT HAVING MANAGERS.
(A) SELECT * FROM EMP WHERE MGR IS NULL
8)IS NOT NULL:
WAQ TO DISPLAY ALL EMPLOYEES WHO ARE GETTING COMMISSION
(A) SELECT * FROM EMP WHERE COMM. IS NOT NULL
WAQ TODISPLAY THE EMPLOYEES WHO ARE HAVING THE MANAGERS
(a) SELECT * FROM EMP WHERE MGR IS NOT NULL
GROUP FUNCTIONS: THESE FUNCTIONS WILL ACTS ON GROUP OF VALUES AT A TIME AND RETURNS A SINGLE VALUES. GROUP FUNCTIONS WILL IGNORE THE NULL VALUES.
GROUP: SET OF RECORDS IN A TABLE
ENTIRE TABLE IS CONSIDERED AS A GROUP OR A TABLE CAN BE DIVIDED INTO NO., OF GROUPS
· SUM: RETURNS SUM OF VALUES IN COLUMN
· AVG: RETURNS THE AVERAGE VALUE FOR ALL VALUES IN COLUMB
· MIN: RETURNS THE MINIMUM VALUE FROM GIVEN COLUMN
· MAX: RETURNS THE MAXIMUM VALUE FROM GIVEN COLUMN
· COUNT: IT COUNT THE NUMBER OF VALUE OR RECORDS IN A GROUP. IT WILL TAKE THREE DIFFERENT PARAMETERS
COUNT(*)àIT CANOT NO OF RECORDS IN GROUP
COUNT(COLUMN_NAME): IT COUNTS NO., OF NOT NULL VALUES IN GIVEN COLUMN, INCLUDING DUPLICATE VALUES.
COUNT( DISTRICT COLUMN_NAME)à IT WILL COUNT NO., OF NOT NULL VALUES IN GIVEN COLUMN BUT IGNORE DUPLICATE VALUES.
TOTAL-SALARY AVERAGE MINIMUM MAXIMUM
29075 2073.21429 800 5000
EX: SELECT SUM (SAL) TOTAL_SAL, MIN(SAL) MIN_SAL MAX(SAL) MAX_SAL FROM EMP WHERE JOB=’CLERK’
TOTAL-SALARY MINIMUM MAXIMUM
29075 800 5000
SELECT SUM(SAL) SUM_SAL MAX(SAL) MAX_SAL FROM EMP WHERE DEPTNO=20
SUM_SAL MAX_SAL
10875 2975
SELECT MIN(SAL) MIN_SAL MAX(SAL), DEPT NO FROM EMP WHERE DEPTNO=30 (INVALID)
NOTE: SUM,.AVG FUNCTIONS WILL ACTS ONLY ON NUMERIC DATA BUT MIN, MAX FUNCTIONS WILL ACTS AN ANY TYPE OF DAYA
à SELECT MIN(ENAME) MIN_NAME, MAX(ENAME) MAX_ENAME FROM EMP
MIN_ENAME MAX_ENAME
ADAMS WARD
à SELECT MIN(HIRE DATE) MIN_DATE, MAX(HIREDATE) MAX_DATE FROM EMP
à SELECT COUNT (*) COUNT (DEPTNO ), COUNT (DISTINCT DEPT NO) FROM EMP
OUTPUT: 14 14 3
GROUP BY CLAUSE: IT IS USED TO DIVIDE THE TABLE INTO GROUPS
SYNTAX: SELECT , FROM [WHERE] GROUP BY GROUP_BY-EXPRESS[ORDER BY COLUMN ASC/DES]
WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON DEPT NO
(A) SELECT DEPT NO, SUM(SAL) TOTAL_SAL, MIN(SAL) MM_SAL, MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO.
WAQ TO DIVIDE THE TABLE INTO GROUP BASED ON DEPTNO AND DISPLAY 20,30 TH GROUP
(A) SELECT DEPTNO, SUM(SAL), SUM_SAL, AVG(SAL) AVG)SAL, COUNT(*) FROM EMP WHERE JOB IN (‘MANAGER’,’ANALYST’,’CLERK’) GROUP BY JOB
WRITE A QUERY TO DIVIDE THE TABLE INTO GROUPS BASED ON JOB AND DEPTNO
(A) SELECT JOB, DEPTNO, MIN(SAL), MIN_SAL, MAX_SAL COUNT(*) FROM EMP GROUP BY JOB, DEPTNO.
HAVING CLAUSE: USED TO IMPOSE CONDITION ON GROUPED DATA
SYNTAX:à SELECT , GROUP FUNCTIONS FROM WHERE GROUP BY GROUP_BY_EXP HAVING ORDER BY COLUMN ASC/DES
WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON JOB AND DEPTNO. DISPLAY THE GROUP IF NO., OF RECORDS ARE MORE THAN 1
à SELECT JOB, DEPTNO, MIN(SAL) MIN_SAL, MAX(SAL), MAX_SAL, COUNT_*) FROM EMP GROUP BY JOB, DEPTNO HAVING COUNT(*)>1
JOB DEPTNO MIN-SAL MAX-SAL COUNT(*)
CLERK 20 800 1100 2
WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON DEPTNO & DISPLAY THE GROUPS IF SUM (SAL)> 500
(A) SELECT DEPTNO, SUM(SAL), AVG(SAL) FROM EMP GROUPE BY DEPTNO HAVING SUM(SAL) > 500.
WAQ TO DIVIDE THE TABLE IN GROUP BASED ON JOB& XXX MANAGER, CLERK, SALESMAN IF SUM(SAL)> 4000
(A) SELECT JOB, SUM(SAL) FROM EMP WHERE JOB IN (MANAGER, CLERK, SALESMAN) GROUP BY JOB HAVING SUM (SAL)>4000
SET OPERATORS: SET OPERATORS ARE USED TO COMBINE THE OUTPUTS OF TWO OR MOR QUERIES AS A SINGLE ONE THE QUERIES COMBINED BY THE SET OPERATORS SHOULD HAVE SAME NUMBER OF COLUMSNS AND THEIR CORRESPONDING DATA TYPES SHOULD BE SAME
1. UNION ALL
2. UNION
1. UNION ALL: IT WILL COMBINE THE OUTPUTS OF TWO OR MORE QUERIES IT WILL NOT ELIMINATE DUPLICATE VALUES.
EX: à SELECT JOB FROM EMP WHERE DEPTNO = 10 UNION ALL SELECT JOB FROM EMP WHERE DEPTNO = 200
2. UNION: IT WIL COMBINE THE OUTPUTS OF TWO OR MORE QUERIES AND ELIMINATES THE DUPLICATE VALUES
EX: à SELECT JOB FROM EMP WHERE DEPTNO =10 UNION SELECT JOB FROM EMP WHERE DEPTNO =20.
DEPTNO DNAME, LOC , EMPNO ,ENAME ,JOB ,DEPTNO
JOIN: JOIN IS A QUERY THAT COMBINES THE ROWS FROM TWO OR MORE TABLE
TYPES OF JOIN
1. CROSS JOIN
2. INNER JOIN OR QUAL JOIN
3. OUTER JOIN
4. SELF JOIN
CROSS JOIN: IN CROSS JOIN EVERY ROW OF THE FIRST TABLE JOINS WITH EVERY ROW IN SECOND TABLE THE OUTPUT OF THE CROSS JOIN IS CALLED CARTESIAN PRODUCT
DEPT EMP
DNAME ENAME
ACCOUNTING SMITH
RESEARCH ALLEN
SALES
OPERATION MILLER
4 ROWS 14 ROWS
à SELECT ENAME, DNAME FROM EMP, DEPT
( OR)
à SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT
INNER JOIN OR EQUIJOIN:- A JOIN WHICH IS BASED ON EQUALITIES IS CALLED EQUL JOIN TABLES SHOULD HAVE COMMON COLUMN TO PERFORM EQUI JOIN
SYNTAX:-
SELECT TABLE 1, COLUMN 1, TABLE 2, COLUMN 2…… FROM TABLE, TABLE2
WHERE TABLE1 COLUMN = TABLE.COLUMN
(OR)
SELECT TABLE1, COLUMN1, TABLE 2, COLUMN 2,……….
INNER JOIN TABLE1, TABLE2, ON TABLE1. COLUMN = TABLE2. COLUMN
WAQ TO DISPLAY ENAME, JOB, DEPTNO DNAME, LOC FOR ALL EMPLOYEES IN EMP,DEPT TABLE,
A) SELECT ENAME, JOB, EMPDEPTNO, DNAME, LOC FROM EMP, DEPT, WHERE EMP DEPTNO = DET DEPTNO
WAQ TO DISPLAY ENAME, JOB,SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP. DEPTNO = DEPT. DEPTNO AND LOC= ‘NEWYORK’
3) OUTER JOIN: OUTER JOIN IS AN ENTENSION FOR THE EQUI JOIN. IT IS USEFUL TO RETRIVE UNMATCHED RECORDS ALONG WITH MATCHED RECORS.
I) LEFT OUTER JOIN:
SYNTAX:
SELECT TABLE1. COLUMN 1, TABLE 2. COLUMN2,.. FROM TABLE1, TABLE2 WHERE TABLE 1 COLUMN * + TABLE2.COLUMN.
à SELECT TABLE 1. COLUMN 1, TABLE 2. COLUMN2…..
FROM TABLE 1 LEFT OUTER JOIN TABLE 2 ON TABLE 1 COLUMN = TABLE2.COLUMN
IT WILL RETRIVES ALL THE MATCHED RECORDS FROM TABLE AND TABLE2 & UNMATCHED RECORDS FROM TABLE.
WAQ TO RETRIVE THE ALL MATCHED RECORDS AND UNMATCHED RECORDS FROM EMP TABLE
A) SELECT EMPNO, ENAME,EMP-DEPTNO, EDNO, DNAME, LOC ROM EMP, DEPT WHERE EMP DEPTNO *= DEPT.DEPTNO.
II) RIGHT OUTER JOIN:
à SELECT TABLE1.COLUMN1, TABLE.COLUMN2,-- FROM TABLE1, TABLE2
WHERE TABLE1. COLUMN *= TABLE2. COLUMN
à SELECT TABLE.COLUMN1, TABLE2.COLUMN2 TABLE 1 RIGHT OUTER JOIN TABLE ON TABLE1.COLUMN = TABLE 2. COLUMN.
IT WILL RETRIEVE ALL THE MATCHED RECORDS FROM TABLE 1 & TABLE2 AND UNMATCHED RECORDS FROM TABLE2 (TABLE RIGHT TO THE RIGHT OUTER JOIN KEY WORD)
WAQ TO RETRIEVE ALL MATCHED RECORDS FROM EMP AND DEPT TABLE AND UNMATCHED RECORDS FROM DEPT TABLE.
A) SELECT EMPNO, ENAME, EMP.DEPTNO.EDNO, DEPT.DEPTNO DNO, FROM EMP, DEPT WHERE EMP.DEPTNO=*DEPT.DEPTNO.
(OR)
SELECT EMPO,ENAME, EMP.DEPTNO EDNO, DEPT.DEPTNO DNO, DNAME FROM EMP RIGHT OUTER JOIN DEPT ON EMP. DEPTNO = DEPT.DEPTNO
EMPNO ENAME EDNO DNO DNAME
7369 SMITH 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
NULL NULL NULL 40 OPERATIONS
FULL OUTERJOIN: SELECT TABLE,.COLUMN, TABLE2.COLUMB
FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COLUMN = TABLE2.COLUMN
IT WILL RETRIEVE ALL THE MATCHED RECORDS FROM TABLE1 AND TABLE2 AND UNMATCHED RECORDS FROM BOTH TABLES
EX: SELECT EMPO, ENAME, EMP, DEPTNO ENDNO, DEPTNODNO, DNAME FROM EMP FULL OUTER JOIN DEPT ON EMP DEPTNO = DEPT DEPTMN.
EMPNO ENAME EDNO DNO DNAME
7369 SMITH 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
NULL NULL NULL 40 OPERATIONS
100 RAM 80 NULL NULL
SELF JOIN : JOINING THE TABLE TO ITSELF IS CALLED SELF JOIN.
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7698 JONES 7839
7839 KING ---
7902 FORD 7839
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7698 JONES 7839
7839 KING ---
7902 FORD 7839
WAQ TO DISPLAY EMPLOYEE NAME AND HIS MANAGER NAME
SELECT WORK ENAME EMPLOYEE, MANAGER ENAME BOSS FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO.
WRITE A QUERY TO DISPLAY ALL EMPLOYEES WHOSE SALARY IS MORE THAN ALLEN SALARY
SELECT B.* FROM EMP A, EMP B WHERE A.ENAME = ‘ALLEN’ AND A.SAL
WAQ TO DISPLAY ALL EMPLOYEE DETAILS WHOSE JOB SAME AS SMITH JOB?
A) SELECT B * FROM EMP A, EMP A, EMPBA WHERE A ENAME AND A JO = B JOB
WAQ TO DISPLAY ALL DETAILS OF ALL EMPLOYEES WHO ARE SENIOR THAN THEIR OWN MGR?
A) SELECT B * FROM EMP A EMPB WHERE B MGR = A.EMP NO. AND B HIRE DATE < A. HIREDATE.
WAQ TO DISPLAY THE DETAILS OF ALL EMPLOYEES WHO ARE WORKING IN SMITH DEPT.
A) SELECT * FROM EMP WHERE ENAME =’SMITH’
à SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’
à SELECT * FROM EMP WHERE DEPTNO = 20.
SUB QUARRIES: SUB QUERY IS A SELECT STATEMENT IN ANOTHER SELECT STATEMENT.
SYNTAX:
SELECT FROM < TABLE_NAME> WHERE < COLUMN> OPERATOR (SELECT FROM )
FIRST INNER QUERY WILL BE EXECUTED BASED ON THE RESULT OF INNER QUERY OUTER QUERY WILL BE EXECUTED.
TYPES OF SUB QUERIES: 1) SINGLE ROW QUERY 2) MULTI ROW SUB QUERY.
SINGLE ROW SUB QUERY : A SUB QUERY WHICH RETURN ONLY ONE VALUE IS CALLED SINGLE ROW SUB QUERY.
EXAMPLES OF SINGLE ROW SUB- QUERY
WAQ TO DISPLAY ALL EMPLOYEES WHOSE JOB IS SAME AS
SELECT * FROM EMP. WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME= ‘FORD’)
WAQ TO DISPLAY ALL EMPLOYEES WHERE SALARIES ARE MORE THAN ‘SCOTT SALARY
A) SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME= ‘SCOTT’)
WAQ TO DISPLAY ALL EMPLOYEES WHOSE SALARIES ARE EQUAL TO MIN SALARY OF EMPTABLE.
A) SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP’)
SELECT DEPTNO, AVG(SAL), AVG_SAL FROM EMP GROUP BY DEPTNO
DEPTNO AVG_SAL
10 2179.666
20 2125
030 1566.66
WRITE A QUERY TO DISPLAY DEPTNO WITH AVG_SAL OF AVG_SAL IS MORE THAN AVG_SAL OF 20TH DEPT
A) SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO =20)
WAQ TO DISPLAY ALL EMPLOYEE WHO ARE WORKING AS MANAGER TO OTHER EMPLOYEES
A) SELECT * FROM EMP WHER EMPNO= (SELECT DISTCT MGR FROM EMP)
SPECIAL OPERATORS WITH SUB_QUERIES: THE FOLLOWING SPECIAL OPERATIONS ARE USED WITH SUB_QUERIES, IF SUB QUERY RETURNS MORE THAN ONE VALUES.
1) IN
2) ANY
3) ALL
4) EXISTS
1) IN: (LIST OF VALUES): IT PICKS THE VALUES FROM THE LIST.
A) SELECT * FROM EMP WHERE EMPNO IN (SELECT.DISTRICTMAGR FROM EMP)
2) ANY:
I)> ANY (LIST OF VALUES): IT PICKS THE MINIMUM VALUE FROM THE LIST AND IT WILL DISPLAY ALL THE VALUES FROM TABLE WHICH ARE MORE THAN MIN VALUE IN THE LIST.
EX:
WAQ TO DISPLAY ALL EMPLOYEE DETAILS FROM EMP TABLE IF THEIR SAL MORE MIN SAL OF 10TH DEPT 1300
A) SELECT * FROM EMP MORE MIN SAL> ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10,2450,5000,1300)
II) < ANY(LIST OF VALUES): IT PICKS THE MAN VALUE FROM THE LIST AND IT DISPLAY ALL THE VALUES FROM TABLE WHICH ARE LESS THAN MAN VALUE IN THE LIST.
EX: WAQ TO DISPLAY ALL EMPLOYEES IF THEIR SALARY LESS THAN MANX SALARY OF 20TH DEPT.
A) SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 20) 800,1100,2975,3000
(OR)
SELECT * FROM EMP WHERE SAL < (SELECT MAX (SAL) FROM EMP WHERE DEPTNO =20)
3) ALL:
I) > ALL (LIST OF VALUES): IT PICKS THE MAX VALUES FROM THE LIST AND IT WILL DISPLAY ALL VALUES FROM TABLE WHICH ARE MORE THAN MAX VALUES IS THE LIST.
EX: WAQ TO DISPLAY ALL EMPLOYEE OF THEN SALARY IS MORE THAN MAX SAL OF 20 DEPT.
A) SELECT * FROM EMP WHERE SAL> ALL (SELECT SAL FROM EMP WHERE DEPTNO=20).
II) < ALL (LIST OF VALUES): IT PICK THE MIN VALUE FROM THE LIST AND (1300,2450,5000) AND IT WILL DISPLAY ALL THE VALUES FROM TABLE WHICH ARE LESS THAN MIN VALUES IN LIST.
WAQ TO DISPLAY ALL EMPLOYEE IF THE EMPLOYEE SAL IS LESS THAN MIN SAL OF 10TH DEPT
A) SELECT * FROM EMP WHERE SAL< ALL (SELECT SAL FROM EMP WHERE DEPTNO = 10) 2450,1300,5000
a. >ANY à GREATER THAN MIN
b. c. >ANY à MORE THAN MANY
d. EXISTS: IT IS A BOOLEAN AN OPERATOR RETURNS SUCCESS OR FAILURE OF A QUERY.
SELECT JOB, SUM(SAL) SUM_SAL, MIN(SAL)MIN_SAL FROM EMP WHERE JOB=’MANAGER’ GROUP BY JOB AND EXISTS SELECT COUNT(*) FROM EMP WHERE JOB =’MANAGER’ GROUP BY JOB HAVING COUNT (*) > 2.
CO_RELATED SUB QUERIES: IF A SUB QUERY EVALUATED REPEATEDLY ONCE FOR EACH ROW SELECTED BY THE OUTER QUERY SUCH SUB QUERIES ARE CALLED CORRELATED SUB QUERY.
HERE OUTER QUERY WILL BE EXECUTED FIRST, BASED AN THE RESULT OF OUTER QUERY INNER QUERY WILL BE EXECUTED.
IF OUTER QUERY HAS RETRIEVED N RECORDS THE INNER QUERY HAS TO EXECUTE N TIMES.
WRITE QUERY TO DISPLAY EMPLOYEES DETAILS IF THE SALARY OF EMPLOYEE IS MORE THAN AVG_SAL OF THIS DEPT.
SELECT E.EMPNO, E.ENAME. E.SAL, E.DEPTNO FROM EMP E WHERE E.SAL> (SELECT AVG(SAL) FROM EMP WHERE EMP.DEPTNO=E.DEPTNO)
WAQ TO DISPLAY ALL THE EMPLOYEES WHO ARE SENIORS THAN THEIR OWN MANAGERS.
SELECT E.EMPO, E.ENAME, E.SAL, E.HIREDATE, E.MGR FROM EMP E.
WHERE E.HIREDATE<(SELECT HIREDATE FROM EMP WHERE EMP.EMPNO=E.MGR)
WAQ TO DISPLAY FIRST HIGHEST SALARY IN EMP TABLE
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE 0=(SELECT COUNT(*)) FROM EMP E WHERE EMP SAL
WAQ TODISPLAY 2ND HIGHEST SALARY IN EMP TABLE
SELECT EMPNO, ENAME, JOB,SAL, FROM EMP WHERE 1=(SELECT COUNT (*) ) FROM EMP E WHERE EMP SAL
DATA INTEGRITY:THE RELIABILITY AND ACCURACYOF DATA IS CALLED DATA INTEGRITY.
TYPES OF DATA INTEGRITY:
· ENTITY INTEGRITY
· DOMAIN INTEGRITY
· REFERENTIAL INTEGRITY
· USER DEFINED INTEGRITY
1) ENTITY INTEGRITY: BASIC IDEA BEHIND ENTITY INTEGRITY IS EACH TABLE SHOULD HAVE A COLUMN, THAN UNIQUELY IDENTIFIES ALL REMAINING COLUMNS.
2) DOMAIN INTEGRITY: DOMAIN INTEGRITY REFERS TO THE RANGE OF VALUES ALLOWED PER COLUMN OF A TABLE
3) REFERENTIAL INTEGRITY: REFERENTIAL INTEGRITY REFERS TO THE MAINTENANCE OF RELATIONSHIP BETWEEN TABLES.
4) USER DEFINED INTEGRITY: IT WILL ALLOW THE USER OR ADMINISTRATOR TO ENFORCE A NEW BUSINESS RULE WHICH WILL NOT COMES UNDER ABOVE 3 RULES.
CONSTRAINTS: IT IS A MECHANISM AUTOMATICALLY ACTIVATED WHEN USER PERFORMS DML OPERATIONS ON THE TABLE.
TYPES OF CONSTRAINTS:
1) UNIQUEà IT WILL NOT ALLOW DUPLICATE VALUES BUT ALLOWS NULL VALUES
2) NOT NULLà IT WILL NOT ALLOW THE NULL VALUES.
3) PRIMARYà IT IS A COMBINATION OF UNIQUE, NOT NULL AND CLUSTERED INDEX.
4) CHECKà IT WILL CHECK VALUES PROVIDED FOR COLUMN
5) FOREIGN KEYà IT IS USEFUL TO ESTABLISH PARENT / CHILD OR MASTER / DETAILED RELATION SHIP BETWEEN THE TABLES, FOREIGN KEY COLUMN OF THE CHILD TABLE IS AWAYS LINKED TO EITHER PRIMARY KEY COLUMN OR UNIQUE CONSTRAINT COLUMN OF THE PARENT TABLE.
6) DEFAULTà IT IS USEFUL TO INSERT TO DEFAULT VALUE INTO A COLUMN WHEN USER WILL NOT PROVIDE ANY VALUE WHILE INSERTING THE DATA INTO THE TABLE.
CONSTRAINTS CAN BE ADDED TO TABLE IN TWO LEVELS
1) COLUMN LEVEL 2) TABLE LEVEL
1) COLUMN LEVEL CONSTRAINTS: HERE CONSTRAINTS ARE ADDED IMMEDIATELY OF FOR DEFINING THE COLUMN
à ONLY ONE PRIMARY KEY COLUMN IS ALLOWED PER TABLE.
EX:
CREATE TABLE DEPT (DEPTNO INT PRIMARY KEY, DNAME VARCHAR UNIQUE NO NULL, LOC VARCHAR(10) DEFAULT ‘HYD’)
CREATE TABLE EMP (EMPNO INT PRIMARY KEY, ENAME VARCHAR NOT NULL, JOB VARCHAR(10), MGR INT, HIREDATE DEFAULT GETDATE(), SAL INT CHECK (SAL>=1000), COMM INT, DEPTNO INT REFERENCES DEPT(DEPTNO) AN DEPT DELETE CASCADE.
ADVANTAGE OF ON DELETE CASCADE: WITH OUT SPECIFYING THE ON DELETE CASCADE IT IS NOT POSSIBLE TO DELETE THE RECORD IN THE BASE TABLE IF THERE ARE DEPENDENT RECORDS FROM THE CHILD TABLE FOR THAT RECORD
IF ON DELETE CASCADE IF USED WHEN THE RECORD IN THE BASE TABLE IS DELETED ALL THE DEPENDENT RECORDS IN THE CHILD TABLE WILL BE ALSO BE DROPPED.
à CREATE TABLE INCR(EMPNO INT REFERENCES EMP(EMPNO) ON DELETE CASCADE, AMOUNT DECIMAL (5,2), DATE DATETIME)
DISPLAYING THE NAMES OF THE CONSTRAINS:
SYNTAX: SP_HELP CONSTRAINT
ASSIGNING THE NAMES FOR THE CONSTRAINTS WHILE CREATING THE TABLE:
SYNTAX:à CREATE TABLE (COLUMN1 DATATYPE(SIZE)CONSTRAINT ,COLUMN2DATATYPE(SIZE) CONSTRAINT)
EX:
CREATE TABLE DEPT(DEPTNO INT CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY, DNAME VARCHAR(20) CONSTRAINT DEPT_DNM_UQ UNIQUE, LOC VARCHAR(10)).
à CREATE TABLE EMPL(EMPNO INT CONSTRAINT EMPL_EMPO_PK PRIMARY KEY, ENAME VARCHAR(20) NOT NULL, JOB VARCHAR(10) , MGR INT, SAL INT CONSTRAINT EMPL_SAL_CHK CHECK(SAL>=1000) COMM. INT, DEPTNO INT CONSTRAINT EMPL_DEPTNO_FK REFERENCES DEPT1(DEPTNO) ON DELETE CASCADE)
INSERTING VALUES INTO THE TABLE CONTAINING CONSTRAINTS:
INSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK)
INSERT INTO DEPT VALUES(10,’RESEARCH’, ‘DALLAS’)
ERROR DUE TO VIOLATION OF P.K. CONSTRAINT
INSERT INTO DEPT VALUES(10,’ RESEARCH’, ‘DALLAS’)
ERROR DUE TOVIOLATION OF P.K CONSTRAINT
INSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK)
ERROR DUE TO TO VIOLATION OF UNIQUE CONSTRAINT
ACTIVATING THE DEFAULT CONSTRAINTS:
INSERT INTO DEPT VALUES(20,’RESEARCH’, DEFAULT)
INSET INTO DEPT(DEPTNO, DNAME) VALUES (30, ‘SALES’)
SELECT * FROM DEPT
INSERT INTO EMP(EMPNO, ENAME, SAL) VALUES (7369,’SMITH’,800)
ERROR DUE TO TO VIOLATION OF CHECK CONSTRAINT
TABLE LEVEL CONSTRAINTS: HERE CONSTRAINTS ARE ADDED AFTER DECLARING ALL COLUMNS
CONSTRAINTS ADDED FOR EXISTING TABLE COMES UNDER TABLE LEVEL.
MORE THAN ON COLUMN PER TABLE CAN BE DECLARED AS EITHER PRIMARY KEY OR FOREIGN KEY COLUMNS
DEFAULT AND NOT NULL CONSTRAINTS ARE NOT ALLOWED.
CREATE TABLE DEPT(DEPTNO INT, DNAME, VARCHAR(20) NOT NULL , (10) VARCHAR(10) DEFAULT ‘HYD’ UNIQUE (DNAME), PRIMARY KEY(DEPTNO))
CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR 920) NOT NULL, JOB VARCHAR(10), MAGR INT, HIREDATE DATETIME, SAL INT COMM. INT,. PRIMARY KEY(EMPNO), CHECK(SAL>=1000), FOREIGN KEY(DEPTNO) REFERENCES DEPT (DEPTNO) AN DELETE CASCADE)
ADDING CONSTRAINTS FOR EXISTING TABLE:
SYNTAX : ALTER TABLE ADD CONSTRAINTS TYPE (COLUMN_NAME)
à CREATE EMP, DEPT TABLES WITHOUT CONSTRAINTS
à ALTER TABLE DEPT ADD PRIMARY KEY (DEPTNO)
à ALTER TABLE DEPT ADD UNIQUE (DNAME)
à ALTER TABLE EMP ADD PRIMARY KEY (DNAME)
à ALTER TABLE EMP ADD CHECK (SAL>=1000)
à ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE.
ASSIGNING THE CONSTRAINTS FOR EXISTING TABLE WITH USER NAMES:
à CREATE TABLE DEPT(DEPTNO INT, DNAME VARCHAR(20) NOT NULL, 10C VARCHAR(10) CONSTRAINT DEPT_LOC_DF DEFAULT ‘HYD’, CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY(DEPTNO), CONSTRAINT DEPT_DNM_UQ UNIQUE(DNAME).
à CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR(20), JOB VARCHAR(10), MGR INT, HIREDATE DATETIME, SAL INT COMM. INT, DEPTNO INT, CONSTRANT EMP_EMPNO_PK PRIMARY KEY(EMPNO), CONSTRAINT EMP SAL_CHK CHECK(SAL>=1000)
CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) ON DELETE CASCADE)
ADDING CONSTRAINTS FOR EXISTING TABLES WITH USER NAMES:
Q) CREATE EMP AND DEPT TABLE WITHOUT CONSTRAINTS
A) SYNTAX:
àALTER TABLE ADD CONSTRAINT CONSTRAINT TYPE (COLUMN)
à ALTER TABLE DEPT ADD CONSTRAINT DEPT_DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO).
à ALTER TABLE DEPT ADD CONSTRAINT DEPT_DNM_UQ UNIQUE (DNAME)
à ALTER TABLE DEPT ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO).
à ALTER TABLE EMP ADD CONSTRAINT EMP_SAL_CHK CHECK (SAL>=1000)
à ALTER TABLE EMP ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE.
DROPPING THE CONSTRAINT:
SYNTAX:
ALTER TABLE DROP CONSTRAINT < CONSTRAINT_NAME>
à ALTER TABLE EMP DROP CONSTRAINT EMP_EMPNO_PK
à CREATE TABLE BANK (ACNO INT, NAME VARCHAR(20) ACC_TYPE CHAR(1), OPEN_BALANCE INT, OPEN_DATE DATETIME, PRIMARY KEY (ACNO, NAME))
à CREATE TABLE FROM (ACNO INT, NAME VARCHAR(20) ACC_TYPE CHAR(1), AMOUNT INT, TRAN_DATE DATETIME FOREIGN KEY (ACNO, NAME) REFERENCES BANK (ACNO, NAME) AN DELETE CASCADE)
ACNO NAME
100 ****
200 ****
300 ****
400 ****
.SELF REFERENTIAL INTEGRITY: THE FOREIGN KEY COLUMN OF THE TABLE IS REFERENCE TO THE PRIMARY COLUMN IN THE SAME TABLE
EX: CREATE TABLE EMP_EMP(ENAME INT, ENAME VARCHAR (20), JOB VARCHAR (10), MAGR INT, PRIMARY KEY (EMPNO), FOREIGN KEY (MGR) REFERENCES EMP_EMP(EMPNO)
EMPNO ENAME JOB MGR
à FIRST INSERT DATA INTO EMPNO, ENAME & JOB COLUMNS LATER UPDATE ‘MGR’ COLUMN
à ADDING NOT NULL CONSTRAINT FOR COLUMN OF A TABLE.
SYNTAX: ALTER TABLE ,TABLE_NAME> ALTER COLUMN DATA TYPE (SIZE) NOT NOT NULL
à ALTER TABLE DEPT ALTER COLUMN LOC VARCHAR (10) NOT NULL
DATA STORAGE: DATA OF EACH TABLE IS STORED IN THE COLLECTION OF DIFFERENT DATA PAGES, EACH PAGE WILL HAVE THE 96 BYTES PAGE HEADER. PAGE HEADER CONTAINS PREVIOUS POINTER, NEXT POINTER, AND OBJECT ID.
PREVIOUS POINTER POINTING TO PREVIOUS PAGE, NEXT POINTER POINTING TO NEXT PAGE.
PREVIOUS POINTER VALUE FOR FIST PAGE IS NULL
NEXT POINTER VALUE FOR LAST PAGE IS NULL
DATA ACCESS: SQL SERVER USES FOLLOWING TWO METHODS TO ACCESS DATA
1) TABLE SCAN 2) BY USING INDEX
TABLE SCAN: IT HAS TO START FROM THE BEGINNING OF THE TABLE AND SCAN EACH AND EVERY PAGE FOR THE REQUIRED DATA.
BY USING INDEX: IT WILL TRAVERSE THOUGH THE INDEX TREE STRUCTURE TO FIND THE REQUIRED DATA AND EXTRACT THE DATA THAT SATISFY THE QUERY CRITERIA.
INDEXED: INDEXED IN SQL SERVER IS SIMILAR TO INDEX IN ABOOK. INDEXED ARE USED TO IMPROVE THE PERFORMANCE OF QUERIES.
INDEXES ARE GENERALLY CREATED FOR FOLLOWING COLUMNS
1) PRIMARY KEY COLUMN
2) FOREIGN KEY COLUMN: FREQUENTLY USED IN JOIN CONDITIONS.
3) COLUMN WHICH ARE FREQUENTLY USED IN WHERE CLAUSE
4) COLUMNS WHICH ARE USED TO RETRIEVE THE DATA IN SORTING ORDER.
INDEXED CANNOT BE CREATED FOR FOLLOWING COLUMNS:
THE COLUMNS WHICH ARE NOT USED FREQUENTLY USED IN WHERE CLAUSE.
COLUMNS CONTAINING THE DUPLICATE AND NULL VALUES
COLUMNS CONTAINING IMAGES, BINARY INFORMATION, AND TEXT INFORMATION.
TYPES OF INDEXEX:
1) CLUSTERED INDEX
2) NON-CLUSTERED INDEX
3) UNIQUE INDEX
4) COMPOSITE INDEX
1) CLUSTERED INDEX: ONLY ONE CLUSTERED INDEX IS ALLOWED FOR A TABLE. THE ORDER OF VALUES IN A TABLE ORDER OF VALUES IN INDEX IS ALSO SAME. WHEN CLUSTER INDEX IS CREATED ON TABLE DATA IS ARRANGED IN ASCENDING ORDER CLUSTER INDEX WILL OCCUPY 5% OF THE TABLE.
SYNTAX: CREATE CLUSTERED INDEX ON (COLUMNS)
à CREATE CLUSTERED INDEX EMP_CLINDEX ON EMP (EMPNO)
NON_CLUSTERED INDEX: IT IS THE DEFAULT INDEX CREATED BY THE SERVER THE PHYSICAL ORDER OF THE DATA IN THE TABLE IS DIFFERENT FROM THE ORDER OF THE VALUES IN INDEX.
MAX NO. OF NON-CLUSTERD INDEXED ALLOWED FOR TABLE IS 249
SYNTAX:
CREATE NON-CLUSTERED INDEX ON TABLE_NAME < COLUMNS>
CREATE NONCLUSTERED INDEX EMP_SAL ON EMP (DEPTNO, SAL)
UNIQUE INDEX : AN INDEX WITH UNIQUE CONSTRAINT. IT WILL NOT ALLOW DUPLICATE VALUES.
SYNTAX: CREATE UNIQUE INDEX ON < TABLENAME> (COLUMN)
à CREATE UNIQUE INDEX DEPT_INDEX ON DEPT (DNAME)
COMPOSITE INDEX: IF A UNIQUE INDEX IS CREATED ON MORE THAN ONE COLUMN IS CALLED COMPOSITE INDEX.
à CREATE UNIQUE INDEX DEPT_COMPOSITE ON DEPT (DEPTNO, DNAME)
TRANSCACTIONS:
1) AUTO COMMIT TRANSACTION
2) EXPLICIT TRANSACTION
3) IMPLICIT TRANSACATION
1) AUTO COMMIT TRANSATION: EVERY INDIVIDUAL TRANSACTION IS AN AUTO COMMIT TRANSACTION.
EX: UPDATE EMP SET SAL= SAL + 100 WHERE DEPTNO
2) EXPLICIT TRANSACTION: TRANSACTION BEGINS WITH BEGIN TRAN AND ENDS EXPLICITLY WITH EITHER COMMIT OF ROLLBACK
à BEGIN TRAN
INSET INTO DEPT VALUES (10,. ‘ACCOUNTING’, ‘NEWYORK’)
à ROLLBACK
INSERT OPERATION IS CANCELLED
3) IMPLICIT TRANSACTION:
à SET IMPLICIT TRANSACTIONS ON/OFF
àUPDATE EMP SET SAL = SAL + 1000 WHERE JOB = ‘MANAGER’
à DELETE FROM EMP WHERE DEPTNO = 10
à COMMIT
UPDATE AND DELETE OPERATIONS ARE CANCELLED
SAVE POINT: SAVE POINTS ARE LIKE PAPER MARKERS USED TO MARKER THE TRANSACTION.
SYNTAX: SAVE BEGIN FROM
à INSERT INTO EMP (EMPNO,ENAME) VALUES (100, ‘RAM’)
SAVE FROM A
UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO=7839
SAVE FROM B
INSET INTO EMP (EMPNO, ENAME) VALUES (200,’***’)
DELETE FROM EMP WHERE EMPNO=7369
ROLLBACK FROM B
à DELETE AND INSERT WILL BE CANCELLED
ROLLBACK FROM A
à UPDATE WILL BE CANCELLED
COMMIT / ROLLBACK
-à INSET WILL BE SAVED / INT WILL BE CANCELLED
PROGRAMMING CONSTRUCTS: PROGRAMMING CONSTRUCTS ARE USED TO EXECUTE THE SET OF SQL STATEMENTS AS A UNIT..
1) IF – ELSE
2) BEGIN – END
3) WHILE
DECLARING VARIABLE: DECLARE @ VARIABLE DATATYPE, @ VARIABLE 2 DATATYPE
DECLARE @A INT, @ VJOB VARCHAR (20)
EX: SET @ A =5
SET @ B =10
SET @ VJOB =’CLERK’
DISPLAYING THE MESSAGE OR VALUE IN THE VARIABLE:
PRINT ‘MESSAGE’
OR
PRINT @ VARIABLE
WRITE A PROGRAM TO FIND THE SUM OF 2 NUMBERS
à DECLARE @A INT, @B INT, @C INT
SET @ A =5
SET @ B =10
SET @ VJOB =@A+@B
PRINT THE SUM OF A&B IS MISTAKE CONVERT (VARCH, @C)
WRITE A PROGRAM TO FIND THE SIMPLE INTEREST
à DECLARE @ P INT, @N DECIMAL(5,2) @R DECIMAL (5,2), @ SI DECIMAL (5,2)
SET @ P =10000
SET @ N =15
SET @ R = 7.5
SET @ SL =(@P * @N * @R)/100
PRINT SIMPLE INTEREST IS + CONVERT (VARCHAR, @SI)
3) WRITE A PROGRAM TO SWAP THE VALUES IN THE VARIABLES.
à DECLARE @A INT, @B INT, @C INT
SET @ A =5
SET @ B =10
SET @ C =@A
SET @ A =@B
SET @ B =@C
PRINT ‘ A=:’+ CONVERT (VARCHAR, @A)
PRINT ‘ B=:’+ CONVERT (VARCHAR, @B)
4) WRITE THE ABOVE PROGRAM WITHOUT USING THE THIRD VARIABLE
IF- ELSE: IT IS USED TO EXECUTE THE STATEMENTS BASED ON CONDITION.
SYNTAX : IF
< STATEMENT 1>
ELSE
< STATEMENT 2>
IF CONDITION IS TRUE THEN STATEMENT1 IS EXECUTED OTHERWISE STATEMENT 2 WILL BE EXECUTED
DECLARE @A INT, @B INT,
SET @ A =5
SET @ B =10
IF(@A>@B)
PRINT ‘B IS BIG’
ELSE
PRINT ‘B IS BIG OR EQUAL TO A’
DECLARE @A INT
SELECT @=COUNT(*) FROM EMP
IF (@A>0)
PRINT ‘TABLE WILL HAVE RECORDS’
ELSE
PRINT ‘NO RECORD IN THE TABLE
DECLARE @A VARCHAR(10)
SELECT @A= JOB FROM EMP WHERE EMPNO =7369
IF (@A=’CLERK’)
PRINT ‘EMPLOYEE WORKING AS CLERK
ELSE
PRINT ‘EMPLOYEE IS :’ +@A
WHILE:
SYNTAX: WHILE(CONDITION)
IF THE CONDITION IS TRUE THEN STATEMENTS UNDER WHICH WILL BE EXCLUDED
WRITE A PROGRAM TO PRINT THE FIRST 10 NO’S USING WHILE
à DECLARE @N INT,
SET @ N =1
WHILE(@N<=10)
BEGIN
PRINT @N
SET @N = @N+1
END
WRITE A PROGRAM TO PRINT ALL EVEN NO’S FROM 1 TO 10
à DECLARE @N INT,
SET @ N =1
WHILE(@N<=10)
BEGIN
IF (@N/2=0)
PRINT @N
SET @N = @N+1
END
VIEWS: VIEWS IS A LOGICAL TABLE BASED ON TABLE OR A VIEW VIEW WILL NOT HOLD NAY DATA OF ITS. IT IS JUST LIKE A WINDOW THROUGH WHICH WE CAN VIEW DATA IN THE TABLE.
TYPES OF VIEWS:
1) SIMPLE VIEW
2) COMPLEX VIEW
SIMPLE VIEW: A VIEW BASED ON SINGLE TABLE IS CALLED A SIMPLE VIEW.
· ALL DML ARE ALLOWED ON THE SIMPLE VIEW
· IT WILL NOT HAVE ANY ARITHMETIC EXPRESSIONS AND GROUP FUNCTIONS.
SYNTAX:
CREATE VIEW AS SELECT STATEMENT [WITH CHECK OPTION]
EX: SELECT * FROM EMP_VIEW
à IT WILL DISPLAY ALL DETAILS EMP TABLES.
INSERT INTO EMP _ VIEW (EMPNO, ENAME, DEPTNO) VALUES (100, RAM, 20)
à UPDATE EMP_VIEW SET SAL = SAL + 100
à DELETE FROM EMP_VIEW WHERE DEPTNO = 10
à ALL 10 DEPT EMPLOYEE DETAILS ARE DELETED FROM TABLE.
THE DML OPERATIONS PERFORMED ON THE VIEW WITH AFFECTS THE TABLE.
EX: CREATE VIEW V10 AS SELECT * FROM EMP WHERE DEPTNO – 10
à INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (200, ‘JAMES’,10)
à INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (30, ‘MORTA’,20)
à SELECT * FROM V10
à IT WILL DISPLAY ALL 100 DEPT RECORDS. IT WILL NOT DISPLAY RECENTLY INSERTED 20 DEPT RECORD SINCE IT IS FOCUSING ONLY ON 10 DEPT.
à SELECT * FROM EMP
à ALONG WITH OLD RECORDS NEWLY INSERTED RECORDS WILL ALSO BE DISPLAYED.
à UPDATE VIO SET SAL= SAL+ 1000
à ALL 10 DEPT EMPLOYEES.
SALARIES ARE MODIFIED
CREATING VIEW BASED ON ANOTHER VIEW:
DEPT NO DNAME LOC
à CREATE VIEW DEPT_V1 AS SELECT * FROM DEPT
à SELECT * FROM DEPT_V1
à CREATE VIEW DEPT_V2 AS SELECT DEPTNO, DNAME FROM DEPT_V1
à SELECT * FROM DEPT_V1
DEPT NO DNAME
à CREATE VIEW V1 AS SELECT EMPNO, ENAME, JOB, DEPT FROM EMP
à SELECT * FROM DEPT_V1
EMPNO ENAME JOB DEPTNO
à CREATE VIEW V2 AS SELECT EMPNO, ENAME, SAL FROM V1
CREATE VIEW WITH CHECK OPTION:
à CREATE VIEW V20 AS SELECT * FROM EMP WHERE DEPTNO=20 WITH CHECK OPTION.
à SELECT * FROM V20
à IT WILL DISPLAY ONLY 20 DEPT EMPLOYEE DETAILS
à INSERT INTO V20 (EMPNO, ENAME,DEPTNO) VALUES (111,’ CHRIS’,10)
à THIS RECORDS WILL NOT BE INSERTED SINCE VIEW IS CREATED WILL CHECK OPTION
DISPLAYING THE SELECT STATEMENT ASSOCIATED WITH THE VIEW
SYNTAX:
SP_HELPTEXT
2) COMPLEX VIEWS: A VIEW BASED ON
I) ARITHMETIC EXPRESSIONS
II) GROUP FUNCTIONS
III) MORE THAN ONE TABLE IS CALLED COMPLEX VIEW
EX:
à CREATE VIEW EMP_GROUP AS SELECT DEPTNO, SUM(SAL) SUM_SAL, AVG(SAL) AVG_SAL, MIN(SAL), MIN_SAL, MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO
à SELECT * FROM EMP_GROUP
DEPTNO SUM_SAL AVG_SAL MIN_SAL MAX_SAL
VIEW BASED ON ARITHMETIC EXPRESSION:
à CREATE VIEW EMP_ARTH AS EMPNO, SAL, BASIC, SAL+0.35, HRA, SAL+0.25, DAA+ 0.15,PF, SAL +0.35+SAL*0.25- SAL*0.15 GROSS FROM EMP
SELECT * FROM EMP_ARTH
DEPTNO BASIC HRA DA PF GROSS
VIEW BASED ON MORE THAN ONE TABLE:
à CREATE VIEW EMP_DEPT AS SELECT ENAME, JOB, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO.
à SELECT * FROM EMP_DEPT
ENAME JOB SAL DNAME LOC
SMITH CLERK 800 RESEARCH DALLAS
ALLEN SALESMAN 1600 SALES CHICAGO
MILLER CLERK 1300 ACCOUNTING NEWYORK
à INSERT INTO EMP_DEPT(ENAME, JOB, SAL, DNAME, LOC) VALUES (‘***’, ‘JI’, 1000, ‘IMPORT’, ‘HYD’)
à ERROR SINCE IT IS NOT POSSIBLE TO INSERT DATA INTO TWO TABLES THOUGH SINGLE VIEW
à INSET INTO EMP_DEPT (ENAME.JOB.SAL) VALUES (‘***’, ‘JI’, 1000)
à THIS IS VALID STATEMENT.
DROPPING THE VIEW
DROP VIEW
à DROP VIEW EMP DEPT
ALTERING THE VIEW:
ALTER VIEW AS SELECT STATEMENT
CURSORS:
RESULTS SET: SELECT STATEMENT RETURNS ALL THE ROWS WHICH SATISFIED THE CONDITION IN WHERE CLAUSE THE SET OF ROWS RETURNED BY THE SELECT ARE CALLED RESULT SET.
CURSOR: CURSOR IS A LOGICAL AREA USED FOR STORING TRANSACTION INFORMATION TEMPORARILY CURSOR IS THE DATABASE OBJECT USED TO PROCESS THE SET OF RECORDS INDIVIDUALLY, ON RECORD AT A TIME.
TYPES OF CURSOR:
1) STATIC CURSOR
2) DYNAMIC CURSOR
3) FORWARD ONLY CURSOR
4) KEY SET DRIVEN CURSOR
NECESSARY STEPS TO PROCESS CURSOR
1) DECLARE CURSOR
2) OPEN CURSOR
3) FETCH RECORD FROM THE CURSOR
4) CLOSE CURSOR
5) DEAL LOCATE CURSOR
1) DECLARE THE CURSOR:
SYNTAX: DECLARE CURSOR CURSOR TYPE FOR SELECT STATEMENT
EX: DECLARE EMP_CUR CURSOR STATIC FOR SELECT * FROM EMP
à DECLARE AUTHORS_CUR CURSOR DYNAMIC FOR SELECT * FROM AUTHORS
CURSOR DECLARATION THE NAME OF THE CURSOR AND SELECT STATEMENT ASSOCIATED WITH IT.
2) OPENING THE CURSOR:
SYNTAX: OPEN
OPEN EMP_CUR
OPEN AUTHORS_CUR
3) FETCH DATA FROM THE CURSOR: ONCE CURSOR IS OPENED RECORDS CAN BE FETCHED FROM IT
SYNTAX: FETCH FROM (OR)
FETCH FROM INTO
FETCH STATEMENT IS USED TO DISPLAY DATA ON SECREEN (OR ) DUMP THE DATA INTO VARIABLES.
KEYWORDS:
1) FIRST: USED TO FETCH THE FIRST RECORD
2) NEXT: USED TO FETCH RECORDS NEXT TO THE PREVIOUSLY FETCHED RECORD
3) LAST: USED TO FETCH THE LAST RECORD FROM THE RESULT SET
4) PRIOR : USED TO FETCH RECORDS BEFORE THE PREVIOUSLY FETCHED RECORD
5) ABSOLUTE N : IF N IS POSITIVE, IT FETCHES NTH RECORD FROM THE BEGINNING, IF ‘N’ IS NEGATIVE IS FETCHES NTH RECORD FROM THE LAST IF N=0, NO RECORD WILL BE FETCHED.
6) RELATIVE N: IT FETCHES NTH RECORD FROM THE PREVIOUSLY FETCHED RECORD.
IF N IS POSITIVE IT FETCHES NTH RECORD FOR PREVIOUSLY FETCHED RECORD
IF N IS NEGATIVE IT FETCHES NTH RECORD BEFORE FOR PREVIOUSLY FETCHED RECORD
IF N=0 SAME RECORD WILL BE FETCHED.
FETCHING: THE PROCESS OF RETRIEVING THE SINGLE RECORD FROM THE RESULT SET IS CALLED FETCHING
SCROLLING: THE PROCESS OF PERFORMING THE SERIES OF FETCHES EITHER IN FORWARD OR BACKWARD DIRECTION IS CALLED SCROLLING
DEFAULT CURSOR IS THE FORWARD ONLY CURSOR
FETCH NEXT IS THE ONLY FETCH STATEMENT VALID WITH FORWARD ONLY CURSOR
EX: FORWARD ONLY CURSOR:
Þ USE PUBS
Þ DECLARE AUTHORS_CUR CURSOR FOR SELECT * FROM AUTHORS
Þ OPEN AUTHORS_CUR
Þ FETCH FIRST FROM AUTHORS_CUR
Þ ERROR BECAUSE FETCH NEXT IS THE ONLY FETCH OPTION VALID WITH FORWARD ONLY CURSOR
Þ FETCH NEXT FROM AUTHORS_CUR
Þ FIRST RECORD IN THE TABLE WILL BE DISPLAYED
4) CLOSING THE CURSOR: CLOSE
EX: CLOSE EMP_CUR
CLOSE AUTHOR_CUR
IT IS ILLEGAL TO FETCH THE DATA FROM THE CLOSED CURSOR, DATA CAN BE FETCHED FROM THE CURSOR AFTER IT WAS OPENED
5) DEALLOCATE THE CURSOR:
SYNTAX: DEALLOCATE
EX: DEALLOCATE AUTHORS_CURS
NOTE: IT IS NOT POSSIBLE TO OPEN THE DEALLOCATED CURSOR
CURSOR WITH SCROLL OPTION: IF CURSOR IS DECLARED WITH SCROLL OPTION ALL FETCH OPERATIONS ARE VALID ON THE CURSOR.
EX:
Þ USE PUBS
Þ DECLARE AUTHORS_CUR CURSOR SCROLL FOR SELECT * FROM AUTHORS
Þ OPEN AUTHOR_CUR
Þ FETCH NEXT FROM AUTHORS_CUR
Þ FIRST RECORD IS FETCHED
Þ FETCH NEXT FROM AUTHORS_CUR
Þ RECORD NEXT TO THE 1ST RECORD IS FETCHED
Þ FETCH LAST FROM AUTHORS_CUR
Þ LAST RECORD IS FETCHEDFETCH PRIOR FROM AUTHORDS_CUR
Þ RECORD BEFORE THE LAST RECORD WILL BE FETCHED
Þ FETCH ABSOLUTE 5 FROM AUTHORS_CUR
Þ FETCH ABSOLUTE 2 FROM AUTHORS_CUR
Þ FETCH RELATIVE 0 FROM AUTHORS_CUR
Þ FETCH RELATIVE 5 FROM AUTHORS_CUR
Þ FETCH FIRST FROM AUTHORS_CUR
Þ FETCH PRIOR FROM AUTHORS_CUR
STATIC CURSOR:
Þ USE PUBS
Þ DECLARE DEPT_CUR CURSOR STATIC FOR SELECT * FROM DEPT
Þ OPEN DEPT_CUR
Þ FETCH FIRST FROM DEPT_CUR
10 ACCOUNTING NEWYORK
Þ UPDATE DEPT SET LOC =’HYD’ WHERE DEPTNO =10
Þ FETCH FIRST FROM DEPT_CUR
10 ACCOUNTING NEWYORK
DML OPERATIONS PERFORMED ON THE ORIGINAL TABLE WILL NOT DISPLAYED BY CURSOR
WHEN CURSOR IS CLOSED AND REOPENED MODIFICATION WILL APPEARS
ALL FETCH OPTIONS ARE VALID WITH STATIC CURSOR
Þ SELECT * FROM DEPT
Þ MODIFICATIONS APPEARS
Þ CLOSE DEPT_CUR
Þ OPEN DEPT_CUR
Þ FETCH FIRST FROM DEPT_CUR
DYNAMIC CURSORS:
SYNTAX : DECLARE CURSOR DYNAMIC FOR SELECT STATEMENT.
IN CASE OF DYNAMIC CURSOR, THE OPERATIONS PEFORMED ON THE TABLE AFTER OPENING THE CURSOR WILL APPEARS THOUGH THE CURSOR.
Þ DECLARE DEPT_CUR CURSOR DYNAMIC FOR SELECT * FROM DEPT
Þ OPEN DEPT_CUR
Þ INSERT INTO DEPT VALUES (50, IMPORT’, ‘HYD’)
10 ACCOUNTING HYD
Þ FETCH FIRST FROM DEPT_CUR
Þ FETCH LAST FROM DEPT_CUR
10 IMPORT HYD
Þ FETCH PRIOR FROM DEPT_CUR
10 OPERATIONS BOSTON
*FETCH ABSOLUTE 2 FROM DEPT_CUR
KEY SET CURSOR:
Þ CREATE TABLE DEPT AND INSERT DATA INTO THE TABLE
Þ CREATE ON UNIQUE INDEX ON DEPTNO COLUMN
Þ DECLARE DEPT_KEYCUR CURSOR KEY SET FOR SELECT * FROM DEPT
KEY SET CURSOR:THE TABLE SHOULD HAVE AT LEAST ONE UNIQUE INDEX COLUMN TO CREATE THE KEY SET CURSOR
· WHEN CURSOR IS OPENED THE INDEX COLUMN OF THE TABLE IS TRANSFERRED TO THE NO-INDEX COLUMN CAN BE VISIBLE THOUGH CURSOR BUT THE MODIFICATION ON THE INDEX COLUMN WILL NOT APPEARS TO THE CURSOR
· THE MODIFICATION PEFORMED ON THE NON-INDEX COLUMNS CAN BE VISIBLE THROUGH CURSOR BUT THE MODIFICATION ON THE INDEX COLUMN WILL NOT APPEARS TO THE CURSOR
· IF THE TABLE WILL NOT HAVE ANY UNIQUE INDEX COLUMN IT AUTOMATICALLY CREATS THE STATIC CURSOR
· OPEN DEPT_KEYCUR
· UPDATE DEPT SET DNAME= ‘EXPORT’, LOC=’HYD’ WHERE DEPTNO = 10
· FETCH FIRST FROM DEPT_KEYCUR
10 EXPORT HYD
· UPDATE DEPT SET DEPTNO=50 WHERE DNAME = ‘OPERATIONS’ FETCH LAST FROM DEPT_KEYCUR
· NODATA IS DISPLAYED
SINCE THE MODIFICATION ON INDEX COLUMN WILL NOT BE VISIBLE THROUGH CUROSR.
TYPE WARNING: IF THIS KEYWORD IS USED WHILE DECLARING THE KEY SET CURSOR THE SERVER WILL DISPLAY MESSAGE IF IT FAILS TO CREATE KEYSET CURSOR
Þ DEPT TABLE WILL NOT HAVE ANY INDEX COLUMN
Þ DECLARE DEPT_KEYSET CURSOR KEYSET TYPE_ WARNING FOR SELECT * FROM DEPT
MESSAGE: CURSOR CREATED IS NOT OF REQUESTED TYPE I.E STATIC CURSOR IS CREATED.
NOTEL @@FETCH_STATUS: RETURNS THE STATUS OF LAST FETCH OPERATION THE POSSIBLE VALUES FOR @@ FETCH_STATUS ARE
1) 0à RECORD FETCHED SUCCESSFULLY
2) 1à FETCH OPERATIONS FAILS: YOU ARE BEYOND THE LAST RECORD OR BEFORE THE FIRST RECORD.
Ø FETCH FIRST FROM DEPT_CUR
Ø PRINT @@ FETCH_STATUS
0
Ø FETCH PRIOR FROM DEPT_CUR
1
SINCE NO RECORD IS FETCHED
Ø FETCH LAST FROM DEPT_CUR
Ø PRINT @@ FETCH_STATUS
0
Ø FETCH NEXT FROM DEPT_CUR
Ø PRINT @@FETCH_STATUS
1
SINCE NO RECORD IS FETCHED
Ø CREATE TABLEL (EMPNOT INT, ENAME VARCHAR(20))
Ø INSERT DATA
Ø DECLARE EMP1_CUR CURSOR FOR SELECT * FROM EMP1
Ø DECLARE @ VNO INT, @VNAME VARCHAR(20) OPE EMPL_CUR
FETCH NEXT FROM EMPL_CUR INTO @ VNO, @VNAME WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT @ VNO
PRINT @ VNAME
FETCH NEXT FROM EMPL_CUR INTO @VNO, @VANME
END.
IF (@@ FETCH _STATUS =1)
PRINT ‘ALL RECORDS ARE FETCHED’
PROCEDURES
PROCEDURES : PROCEDURES IS A PRE_COMPILED COLLECTION OF T_SQL STATEMENTS PROCESSED AS A UNIT PROCEDURE WILL HAVE THE HEADER, DECLARATIVE SECTION AND EXECUTABLE SECTIONS.
TYPES OF PROCEDURES: 1) SYSTEM STORED PROCEDURES
2) USER DEFINED STORED PROCEDURES.
USED DEFINED STORED PROCEDURES: THE PROCEDURES CREATED BY THE USER ARE CALLED USED DEFINED STORED PROCEDURES.
SYNTAX: CREATE PROCEDURE / PROC
@PARA 1 DATATYPE (SIZE)[=DEFAULT_VALUE][OUTPUT]
@PARA 2 DATATYPE (SIZE)[=DEFAULT_VALUE][VALUE]
AS SELECT STATEMENT
WRITE A SIMPLE PROCEDURE TO THE COMPLETE DATA FROM EMP TABLE
A) à CREATE PROCEDURE EMP_DATA AS SELECT * FROM EMP
à EXECUTING THE PROCEDURE:
SYNTAX: EXEC
à
EXEC EMP_DATA
ALL EMPLOYEES DETAILS ARE DISPLAYED IN THE EMP TABLE
EX:
CREATE PROC SAL_DATA AS SELECT EMPNO, SAL BASIC ,SAL * 0.35HRA, SAL*0.25 DA, SAL *0.15 PF, SAL+SAL*0.35+ SAL – 0.15 GROSS FROM EMP
à EXEC SAL_DATA
EMPNO BASIC HRA DA PF GROSS
WRITE A PROCEDURE TO FIND THE SUM OF TWO NO’S
A) à CREATE PROC ADD_NUM AS DECLARE @A INT, @B INT, @C INT
SET @A=5
SET @B=10
SET @C=@A+@B
PRINT THE SUM IS ‘+’ CONVERT (VARCHAR, @C)
à EXEC ADD_NUM
THE SUM IS =15
WRITE A PROCEDURE TO FIND SIMPLE INTEREST?
A) CREATE PROC SIMPLE_INTEREST AS DECLARE @P INT, @N DECIMAL (5,2), @R DECIMAL (5,2), @5I DECIMAL (5,2)
à SET @SI = (@P*@N*@R*)/100
PRINT “SIMPLE INTEREST:”+CONVERT (VARCHAR, @SI)
WRITE A PROCEDURE TO FIND THE SUM OF TWO STRINGS
A) à CREATE PROC ADD_STRINGS AS DECLARE @A VARCHAR(20), @B VARCHAR(20), @C VARCHAR(20),
SET @A=’ORACLE’
SET @B=’APPLICATIONS’
SET @C=@A+’ ‘ +@B
PRINT @C
à EXEC ADD_STRINGS
ORACLE APPLICATIONS
WRITE A PROCEDURE TO FIND THE SUM OF TWO NO’S
A) à CREATE PROC ADD_NUM @A INT, @B INT, AS DECLARE @C INT
SET @C=@A+@B
PRINT THE ‘SUM OF A AND B IS ‘ + CONVERT (VARCHAR, @C )
à EXEC ADD_NUM 3,5
OUTPUT:: THE SUM OF A AND B IS 8
DECLARE @M INT, @N INT,
SET @M=5
SET @N=10 EXECUTE ALL THE ONCE
EXEC ADD_NUM@M,@N
INSERTING DATA INTO A TABLE USING PROCEDURE:
à CREATE PROC INSERT DATA
@VNO INT, @ VNAME VARCHAR (20). @LOC VARCHAR (10) AS INSERT INTO DEPT VALUES[@VNO, @VNAME.@LOC].
DEPTNO DNAME LOC
10 ACC NEW
20 RESE HYD
à EXEC INSERT_DATA 10, ‘ACC’, ‘NEW’
à SELECT * FROM DEPT.
WRITE A PROCEDURE TO DISPLAY DETAILS OF ON EMPLOYEE TAKE EMPO THE
à CREATE PRO EMP DETAILS
@VNO INT
AS SELECT * FROM EMP WHERE EMPO = @ VNO
à EXECUTE EMP_DETAILS 7369
WRITE A PROCEDURE TO FIND THE NET SAL SAL OF AN EMP CREATE PROC NET_SAL
@VNO INT AS
DECLARE @ VSAL INT @VCON INT, @NET INT
SELECT @ VSAL = SAL, @ VCOM FROM EMP WHERE
EMPO = @ VNO
IF @VCOM IS NULL
BEGIN
PRINT ‘COMM IS NULL’
PRINT ‘NET SAL IS’ CONVERT (VARCHAR, @ VSAL)
END
WRITE A PROCEDURE TO FIND THE NET SAL SAL USING OUT PARAMETERS
A) à CREATE PROC NET SAL
@VNO INT , @NET INT OUTPUT
AS
DECLARE @ VSAL =SAL, @VCOM =COMM, FROM EMP WHERE EMPNO = @ VNO
IF @VCOM IS NULL
BEGIN
PRINT ‘COMM IS NULL’
SET @ NET = @VSAL
END
ELSE
BEGIN
SET @NET= @VSNL + @COMM
END
EXECUTING PROC:
DECLARE @K INT
EXEC NET_SAL 7839, @ K OUTPUT
PRINT @K
ADMINISTRATOR LOGIN
CREATE PROC EMP_DEPT
SELECT ENAME, DNMAE FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
GRANT EXEC ON EMP_DEPT TO RAM
CONNECT TO RAM LOGIN
EXEC EMP_DEPTà DATA IS DISPLAYED SINCE ADMINSTATOR HAS GIVEN EXECUTE PERMISSION ON EMP_DEP
ALTERING THE PROCEDURE:
SYNTAX: ALTER PROC @ PARA 1. DATATYPE @PARA 2 DATA TYPE (SIZE) AS SQL STATEMENT
DISPLAYING THE CODE OF EXISTING PROCEDURE:
SYNTAX: SP_TELPTAXT
EX: SP_TELP_TEXT EMP_DEPT
USER DEFINED FUNCTIONS: FUNCTIONS CREATED BY USER ARE CALLED USER DEFINED FUNCTIONS
TYPES OF USER DEFINED FUNCTIONS:
1) SCALAR VALUED FUNCTIONS
2) TABLE VALUED FUNCTIONS
SCALAR VALUED FUNCTIONS: THESE FUNCTIONS WILL RETURN A SCALAR VALUE TO THE CALLING ENVIRONMENT
SYNTAX: CREATE FUNCTION < FUNCTION_NAME> (PARA 1 DATA TYPE (SIZE), PARA 2 DATATYPE (SIZE)
RETURNS
AS
BEGIN
RETURN < SCALAR VALUE>
END
WRITE A FUNCTION TO FIND THE PRODUCT OF TWO NUMBERS
A) CREATE FUNCTION PROD_NUM
(@ A INT, @B INT)
RETURNS INT
DECLARE @ C INT
SET @C = @A * @B
RETURN(@C)
END
EXECUTING THE FUNCTION
à DECLARE @K INT
SET @K = DBO.PROD_NUM(3,5)
PRINT @K
WRITE FUNCTION TO FIND THE NET SALARY OF AN EMPLOYEE READ EMPNO THOUGH PARAMETER AND DISPLAY THE NET TO RETURN VALUE
A) CREATE FUNCTION NET_SAL
(@ VNO INT)
RETURNS INT
AS
BEGIN
DECLARE @ VSAL INT, @VCOM INT, @NET INT
SELECT @VSAL \ SAL, @VCOM=COM
FROM EMP WHERE EMPNO =@VNO
IF @ VCOM IS NULL
BEGIN
PRINT ‘COMMISION IS NULL’
SET @NET = @VSAL
END
ELSE
BEGIN
SET @ NET = @VSAL + @VCOM
END
RETURN (@NET)
DECLARE @ K INT
SET @K = NET_SAL (7839)
PRINT @K
2) TABLE VALUED FUNCTION: THESE FUNCTIONS WILL RETURN ENTIRE TABLE TO THE CALLING ENVIRONMENT.
SYNTAX:
CREATE FUNCTION
(PARA 1 DATA TYPE (SIZE)……….)
RETURNS TABLE
AS
BEGIN
RETURN (SELECT STATEMENT)
END
WRITE A FUNCTION TO RETURN ENTIRE DEPT TABLE
A) à CREATE FUNCTION DEPT_TAB()
RETURNS TABLE
AS
BEGIN
RETURN (SELECT * FROM DEPT)
END
à EXECUTING THE FUNCTION
à SELECT * FROM DEPT_TABLE()
DEPT DNAME LOC
à CREATE FUNCTION EMP_DEPT()
RETURN TABLE
AS BEGIN
RETURN(SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP DEPTNO = DEPT DEPTNO)
END
à SELECT * FROM EMP_DEPT()
ENAME DNAME
SMITH RESEARCH
MILLER ACCOUNTING
CREATING DEFAULT:
SYNTAX:
CREATE DEFAULT AS
EX:
à CREATE DEFAULT FILL_COLUMN AS ‘UNKNOWN’
BINDING THE DEFAULT FOR COLUMNS:
SYNTAX: SP_BINDDEFAULT FILL_COLUMN, ‘DEPT.LOC’
SP_BINDEFAULT FILL_COLUMN, ‘EMP.JOB’
à INSERT INTO DEPT(DEPTNO, DNAME)
VALUES(10,ACCOUNTING’)
à SELECT * FROM DEPT WHERE DEPTNO =10
DEPTNO DNAME LOC
10 ACCOUNTING UNKNOWN
à INSERT INTO EMP (EMPNO, ENAME, SAL)
VALUES (100, ‘PHILLIS’, 5000)
à SELECT EMPNO, ENAME, JOB FROM EMP WHERE EMPNO =100
EMPNO ENAME JOB
100 PHILLIS 5000
UNBINDING THE DEFAULT FROM THE BINDING COLUMN
SYNTAX: SP_UNBINDEFAULT
SP_UNBINDEFAULT ‘ DEPT.LOC’
SP_UNBINDEFAULT ‘ EMP.JOB’
DROPPING THE DEFAULT:
SYNTAX: DROP DEFAULT
DROP DEFAULT FILL_COLUMN
CREATING RULES:
RANGE RULE:
CREATE RULE RANGE_RULE AS @ RANGE >=10 AND @ RANGE <= 60
BINDING THE RULE:
SP_BINDEFAULT ,‘ TABLE.COLUMN’
SP_BINDEFAULT RANGE_RULE, ‘DEPT.DEPTNO’
INSERT INTO DEPT(DEPTNO) VALUES (70)
ERROR
LIST RULE:
à CREATE RULE LIST_RULE AS @ LIST IN (‘ACCOUNTING’, ‘SALES’, ‘OPERATIONS’, RESEARCH’, ‘EDU’)
à SP_BINDRULE LIST_RULE, ‘DEPT_DNAME’
à INSERT INTO DEPT(DEPTNO, DNAME) VALUES (20, IMPORT’)
ERROR SINCE DANME VALUES IS OUT OF LIST
UNBINDING THE RULES:
à SP_UNBIND RULE DEPT.DEPTNO
à SP_UNBIND RULE DEPT.DNAME
DROPPING THE RULES:
à DROP RULE
à DROP RULE ROLE_NAME
à DROP RULE LIST_NAME
TRIGGERS
TRIGGERS: TRIGGER IS A TYPE OF STORED PROCEDURE THAT IMPLICITLY THAT IMPLICITLY EXECUTED WHEN USER PEFORMS DML OPERATION ON THE TABLE.
IT WILL NOT ACCEPT ANY PARAMETERS
TYPES OF TRIGGERS:
!) INSERT TRIGGER
2) DELETE TRIGGER
3) UPDATE TRIGGER
SYNTAX: CREATE TRIGGER ON FOR/ AFTER, INSERT,/ UPDATE/ DELETE AS SQL STATEMENT.
I
INSERT TRIGGER: THIS TRIGGER FIRES WHEN USER PERFORMS INSERT OPERATION ON THE TABLE. WHEN USER INSERT A RECORD INTO THE TABLE THE TEMPORARY TABLE CALLED INSERTED IS CREATED
THE NEWLY INSERTED RECORD IS ALSO STORED IN INSERTED TABLE TEMPORARILY
à CREATE TRIGGER
FOR INSERT
AS
BEGIN
PRINT ‘TRIGGER T1 FIRE’
END
à CREATE TRIGGER2 ON DEPT
FOR
AS
PRINT ‘TRIGGER T2 FIRE’
END
à CREATE TRIGGER3 ON DEPT
FOR INSERT
AS
PRINT ‘TRIGGER T3 FIRE’
END
à INSERT INTO DEPT(DEPTNO) VALUES(50)
TRIGGER T1 FIRED
TRIGGER T2 FIRED
TRIGGER T3 FIRED
CHANGING THE FIRING ORDER OF TRIGGERS:
SYNTAX: SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘NAME_OF_TRIGGER’,
@ ORDER = ‘FIRST/LAST’
@ STMTYPE = ‘INSERT’/UPDATE/DELETE’
à SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘T3’
@ ORDER = ‘FIRST’
@ STMTYPE = ‘INSERT’
à SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘T1’
@ ORDER = ‘LAST’
@ STMTYPE = ‘INSERT’
à INSERT INTO DEPT (DEPTNO) VALUES (60)
TRIGGER T3 FIRED
ON DEPT
FOR UPDATE
AS
BEGIN
INSERT INTO DEPT_INSERT SELECT * FROM INSERT
INSERT INTO DEPT_DELETE SELECT * FROM DELETED
END
à UPDATE DEPT
SET DNAME = ‘EXPORT’
LOC = ‘SEC’ WHERE DEPTNO =10
à SELECT * FROM DEPT_INSERT
DEPTNO DNAME LOC
10 EXPORT SEC
à SELECT * FROM DEPT_DELETE
DEPTNO DNAME LOC
10 ACCOUNTING NEWYORK
INSTEAD OF TRIGGERS: THESE TRIGGER ARE MAINLY CREATED FOR VIEWS
SYNTAX: CREATE TRIGGER TRIGGER_NAME ON < VIEW_NAME>
INSTEAD OF INSERT/ UPDATE ./ DELETE
AS
SQL STATEMENT
à CREATE VIEW V10
AS
SELECT * FROM EMP WHERE DEPTNO =10
Þ SELECT * FROM V10
Þ IT WILL DISPLAY AN 10TH DEPT EMPLOYEES DETAILS
Þ CREATE TRIGGER V10_TRG
INSEAT OF INSERT
AS
UPDATE V10 SET SAL=SAL +500
Þ INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (100, ‘CHAD’, 10)
Þ SELECT * FROM V10
Þ INSTEAD RECORDS WILL NOT APPEARS BUT SALARIES ARE MODIFIED DUE INSTEAD OF TRIGGER
Þ CREATE VIEW DEPT_VIEW
AS
SELECT * FROM DEPT
Þ CREATE TRIGGER DEPT_TR
AS
ON DEPT_VIEW
FOR INSERT
AS
DELETE FROM DEPT_VIEW
Þ INSERT INTO DEPT_VIEW DATA DELETED DUE TO INSTEAD OF TRIGGER.
Þ CREATE VIEW EMP_DEPT
AS
SELECT EMPNO, ENAME, DEPT,.DEPTNO, DNAME FROM EMP,
DEPT WHERE EM.DEPTNO =DEPT.DEPTNO
EMPNO ENAME DEPTNO DNAME
à SELECT * FROM EMP_DEPT
ERROR
à INSERT INTO EMP_DEPT (EMPNO, ENAME, DEPTNO, DNAME) VALUES (100, ‘CHAD’, 50, ‘IMPORT’).
SINCE NOT POSSIBLE TO INSERT DATA INTO TWO TABLES THOUGH SINGLE VIEW.
à CREATE TRIGGER EMP DEPT_TRG
ON EMP_DEPT
FOR INSERT
AS
BEGIN
INSERT INTO EMP (EMPNO,ENAME) SELECT EMPNO, ENAME FROM INSERTED.
INSERT INTO DEPT(DEPTNO, DNAME) SELECT DEPTNO, DNAME FROM INSERTED
END.
à INSERT INTO EMP_DEPT (EMPNO, ENAME, DEPTNO, DNAME) VALUES (100, ‘CHAD’, 50, ‘IMPORT’).
à SELECT * FROM EMP
à NEW RECORD WILL IS DISPLAYED ALONG WITH OLD RECORD
à SELECT * FROM DEPT
à NEW RECORD WILL BE DISPLAYED ALONG WITH OLD RECORDS
DISPLAYING THE CODE OF TRIGGER:
SYNTAX: SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘EMP_DEPT’
DISPLAYING THE TRIGGERS INFORMATION FOR THE TABLE.
SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘DEPT’
DCL COMMANDS: THESE COMMANDS ARE USED GRANT OR REVOKE THE PERMISSION ON DATABASE OBJECTS TO OTHER USERS.
1) GRANT: USED TO GRANT THE PERMISSIONS ON DATA OBJECTS TO USERS ACCOUNTS
ADMINISTRATOR TOOLS
GRANT SELECT ON DEPT TO RAM
GRANT INSERT, SELECT ON EMP TO RAM123
RAM/ RAM 123 LOGIN
SELECT * FROM DEPT
à DEPT DATA IS DISPLAYED
INSERT INTO DEPT VALUES (50, ‘****’, ‘DDDD’)
à ERROR SINCE NO INSERT PERMISSION TO RAM
SELECT * FROM EMP
INSERT INTO EMP (EMPNO, ENAME) VALUES (400, ‘CHRIS’)
à 1 RECORD IN INSERTED
UPDATE EMP SET EMPNO =111 WHERE ENAME = ‘SMITH’
à ERROR SINCE NO UPDATE PERMISSION TO RAM
ADMINISTRATOR LOGIN:
REVOKE: USED TO TAKE BACK THE PERMISSIONS GIVEN TO THE USERS
REVOKE SELECT ON DEPT FROM RAM
REVOKE SELECT, INSERT AN EMP FROM RAM
ADMINISTRATOR LOGIN
GRANTING PERMISSION WITH GRANT OPTION:
GRANT SELECT ON DEPT TO RAM WITH GRANT OPTION
RAM LOGIN:
SELECT * FROM DEPT
à DATA WILL BE DISPLAYED
GRANT SELECT ON DEPT TO NTTS
NTTS LOGIN
SELECT * FROM DEPT
ADMINISTRATOR LOGIN
REVOKE SELECT ON DEPT FROM RAM CASCADE
à NOW BOTH RAM AND NTTS WILL LOOSE THE PERMISSION ON DEPT
à SP_WHO à IT WILL DISPLAY THE STATUS OF LOGINS.
No comments:
Post a Comment