STRUCTURE QUERY LANGUAGE(SQL)

STRUCTURE QUERY LANGUAGE (SQL)

SQLàSQL IS THE MOST POPULAR COMPUTER LANGUAGE USED TO CREATE, MODIFY, RETRIEVE AND MANIPULATE DATA FROM RELATIONAL DATABASE MANAGEMENT SYSTEMS.THE LANGUAGE HAS EVOLVED BEYOND ITS ORIGINAL PURPOSE TO SUPPORT OBJECT-RELATIONAL DATABASE MANAGEMENT SYSTEMS.SQL CONTRASTS WITH THE MORE POWERFUL DATABASE-ORIENTED FOURTH GENERATION PROGRAMMING LANGUAGE SUCH AS FOCUS IN ITS RALATIVE FUNCTIONAL SIMPLICITY AND SIMPLERCOMMNAD SET.

DATA TYPES IN SQL

[A] CHAR (n) OR CHARACTER (n)àA DATA ELEMENT CAN BE DECLARED OF TYPE  CHAR(n). A FIELD MAY STORE A CHARACTER STRING OF n NUMBERS TO A MAXIMUM OF 254 CHARACTERS.VALUES OF THIS TYPE MUST BE ENCLOSED IN SINGLE QUOTES SUCH AS ‘RAM’.

[B]NUMBER(n,d)àA DATA ELEMENT CAN BE DECLARD OF TYPE NUMBER WITH A MAXIMUM PRECISION OF 38 DIGITS,EXCLUDING DECIMAL POINT AND SIGN.THUS,A COLUMN MAY CONTAIN DECIMAL, INTEGER OR FLOATING POINT DATA WITH THE FOLLOWING ARGUMENTS.

     n- MAXIMUM NUMBER OF DIGITS

     d- NUMBER OF DIGITS TO RIGHT OF DECIMAL POINT

[C]INT OR INTEGERàIT REPRESENTS A NUMBER WITHOUT A DECIMAL POINT.HERE THE SIZE ARGUMENT IS NOT USED, IT IS AUTOMATICALLY SET TO AN IMPLEMENTATIONM, DEPENDENT VALUE.

[D]FLOAT (n)à IT REPRESENTS A NUMBER WITH A DECIMAL POINT.THE ARGUMENT CONSISTS OF A SINGLE NUMBER SPECIFYING MINIMUM PRECISION.

[E]DATEàIT IS USED TO STORE DATES. THE DEFAULT DATE FORMAT IS MM-DD-YY.TO INSERT DATE VALUE INTO A TABLE, USE CURLY BRACE{ }.

[F]LOGICALà LOGICAL DATA TYPES HOLDS A SINGLE CHARACTER WHICH SPECIFIES EITHER T OR F

SQL PROVIDES MANY DIFFERENT TYPES OF COMMANDS USED  FOR DIFFERENT PURPOSES.SQL COMMANDS CAN BE DIVIDED INTO FOLLOWING CATEGORIES.

[A] DATA DEFINITION LANGUAGE[DDL]àTHE FIRST GROUP OF KEYWORD IS THE DATA DEFINITION LANGUAGE, DDL ALLOWS THE USER TO DEFINE NEW TABLES AND ASSOCIATED ELEMENTS.MOST COMMERCIAL SQL DATABASES HAVE PROPRIETARY EXTENSIONS IN THEIR DDL,WHICH ALLOW CONTROL OVER NONSTANDARD FEATURES OF THE DATABASE SYTEM.THE MOST BASIC ITEMS OF DDL ARE THE CREATE,ALTER,RENAME,TRUNCATE AND DROP COMMANDS.

[B]DATA MANIPULATION LANGUAGE [DML]à THE SECOND GROUP OF KEYWORDS IS DATA MANIPULATION LANGUAGE ELEMENTS, DML IS THE SUBSET OF THE LANGUAGE USED TO ADD, UPDATE AND DELETE DATA.

[C]TRANSACTION CONTROL LANGUAGE[TCL]àA TRANSACTION CONTROL LANGUAGE IS SUCCESSFULLY COMPLETED IF AND ONLY IF ALL ITS CONSTITUENT STEPS ARE SUCCESSFULLY COMPLETED.TO MANAGE AND CONTROL THE TRANSACTION , THE TRANSACTION CONTROL COMMANDS ARE USED.THESE COMMANDS MANAGE CHANGES MADE BY DML COMMANDS. SOME EXAMPLE OF TCL COMMANDS IS COMMIT, ROLLBACK, SAVEPOINT ETC.

SQL COMMANDS

CREATE TABLEà A CREATE STATEMENT IN SQL CREATES AN OBJECT INSIDE OF A RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS). THE TYPES OF OBJECTS THAT CAN BE CREATED DEPENDS ON WHICH RDBMS IS BEING USED, BUT MOST SUPPORT THE CREATION TABLE, INDEX, USER AND DATABASE.

  THE SYNTAX OF CREATE TABLE COMMAND IS

 CREATE TABLE <TABLE NAME>

 (

  COLUMN NAME1     DATATYPE(SIZE),

  COLUMN NAME2     DATATYPE(SIZE),

  COLUMN NAME3     DATATYPE(SIZE)

};

EXAMPLE

CREATE TABLE ANUSKA

(

ROLL NUMBER (2), NAME CHAR (20), MATH NUMBER (2)

};

INSERT àAN SQL, INSERT STATEMENT ADDS ONE OR MORE RECORDS TO A TABLE IN A RELATIONAL DATABASE.AN INSERT STATEMENT CAN ONLY INSERT DATA INTO A SINGLE TABLE.

SYNTAX

INSERT INTO TABLE NAME VALUES (value1, value2, value3--);

EXAMPLE

INSERT INTO RAM VALUES (&ROLL,”&NAME’,&MATH);

SELECT à A SELECT STATEMENT IN SQL RETURNS  A RESULT SET OF RECORDS FROM ONE OR MORE TABLES.IT IS USED TO RETRIEVE ZERO OR MORE FROM ONE OR MORE BASE TABLES,TEMPORARY TABLES, OR VIEW IN A DATABASE.IN MOST APPLICATIONS,SELECT IS THE MOST COMMONLY USED DATA MANIPULATION LANGUAGE COMMAND.

COMMONLY AVAILABLE CLAUSES RELATED TO SELECT  INCLUDE

[a] WHERE à USED TO IDENTIFY WHICH ROW TO BE RETRIEVED,OR APPLIED TO GROUP BY.

[b] GROUP BYà USED TO COMBINE ROWS WITH RELATED VALUES INTO ELEMENTS OF A SMALLER SETOF ROWS.

[c] HAVING àUSED TO IDENTIFY WHICH ROWS, FOLLOWING A GROUP BY, ARE TO BE RETRIEVED.

[D] ORDER BYàUSED TO IDENTIFY WHICH COLUMNS ARE USED TO SORT THE RESULTING DATA

EXAMPLE

SELECT *FROM STUDENT;

SELECT ADDRESS FROM STUDENT;

DISTINCTàTHE DISTINCT KEYWORDS IS USED TO RETURN ONLY DISTINCT (DIFFERENT) VALUES.

  EXAMPLE

 SELECT DISTINCT ADDRESS FROM STUDENT;

AND or OR à AND or OR JOIN TWO OR MORE CONDITION IN A WHERE CLAUSE.THE AND OPERATOR DISPLAYS A ROW IF ALL, CONDITIONS LISTED ARE TRUE. THE OR OPERATORE DISPLAY A ROW IF ANY OF THE CONDITIONS LISTED ARE TRUE.

EXAMPLE

SELECT *FROM STUDENT WHERE ADDRESS=”BARH” AND AGE>30;

SELECT *FROM STUDENT WHERE ADDRESS=”BARH” OR AGE>30;

SELECT *FROM STUDENT WHERE (AGE=30 OR AGE=35 OR AGE=40) AND SEX=”F”;

WHERE  à  A WHERE STATEMENT IN SQL SPECIFIES A SQL COMMAND SHOULD ONLY BE RUN ON ROWS THAT MEET A SPECIFIED CONDITION.

THE WHERE STATEMENT HAS THE FOLLOWING FORM.

 COMMAND FORM table name WHERE condition

EXAMPLE

SELECT *FROM STUDENT WHERE SEX=”MALE”;

SELECT *FROM EMP WHERE DEPT=”ACCT”;

SELECT  *FROM EMP WHERE BASIC>8000 AND BASIC <10000;

SELECT *FROM TAB; à THIS COMMAND IS USED TO DISPLAY ALL THE PREVIOUS TABLE.

  EXAMPLE

    SELECT *FROM TAB;

CONSTRAINTSàIT IS THE PART OF TABLE  DEFINITION THAT LIMIT THE VALUES ENTERED INTO ITS COLUMNS.THE VARIOUS CONSTRAINTS  ARE

[a] PRIMARY KEYàSPECIFIES THAT THIS COLUMN IS A PRIMARY KEY, WHICH IMPLIES THAT UNIQUENESS IS ENFORCED BY THE SYSTEM AND THAT OTHER RELATIONS MAY RELY ON THIS COLUMN AS A UNIQUE IDENTIFIER FOR ROW,COLUMN

[b]CHECKàCONTROLS THE VALUE OF A COLUMN BEING INSERTED.

LAB EXERCISE

CREATE A TABLE HOSPITAL WHICH THE FOLLOWING COLUMNS.

COLUMN	TYPE	SIZE
NO	NUMBER	2
NAME	CHAR	15
AGE	NUMBER	2
DEPT	CHAR	8
DOA	DATE	8
CHARGE	NUMBER	7,2
SEX	CHAR	1

ANSWER THE FOLLOWING QUESTIONS

[a] DISPLAY ALL RECORDS OF HOSPITAL TABLE

[b] DISPLAY NAME, DEPT OF HOSPITAL TABLE

[c ] DISPLAY ALL RECORDS OF HOSPITAL TABLE WHOSE SEX=F

[d] DISPLAY NAME, DEPT WHOSE CHARGE>800

[e] DISPLAY ALL RECORDS WHOSE DEPT=SKIN

ALTER TABLEà THE ALTER TABLE COMMAND IS USED TO CHANGE DEFINITIONS OF EXISTING TABLES. USUALLY, IT CAN ADD COLUMNS TO A TABLE, SOMETIMES IT CAN DELETE COLUMN OR CHANGE THEIR SIZES. IN GENERAL,IN ORACLE  SQL, ALTER TABLE COMMAND IS USED [A] TO ADD A NEW  COLUMN

[B] TO MODIFY EXISTING COLUMN

[A] ADD NEW COLUMN à THE ALTER TABLE COMMAND ADDS  ONE OR MORE NEW COLUMNS BY USING AN ADD CLAUSE.

  SYNTAX

  ALTER TABLE  <TABLE NAME>

  ADD(NEW COLUMN  DATA TYPE (SIZE));

 FOR EXAMPLE

  ALTER TABLE HOSPITAL

ADD(ADDRESS  CHAR(13));

[B] MODIFY EXISTING COLUMN à THE ALTER COMMAND MODIFIES THE SPECIFIED COLUMN USING MODIFY CLAUSE.

 SYNTAX

  ALTER TABLE  <TABLE NAME>

  MODIFY(NEW COLUMN  DATA TYPE (SIZE));

 FOR EXAMPLE

  ALTER TABLE HOSPITAL

MODIFY(ADDRESS  CHAR(9));

UPDATE ----SET à THE  RELATIONAL DATABASE MANAGEMENT SYSTEM USES  SQL, UPDATE , STATEMENT TO CHANGE DATA IN ONE OR MORE RECORDS. EITHER ALL THE ROWS CAN BE UPDATED OR USING A CONDITION

UPDATE STATEMENT HAS THE FOLLOWING FORM.

UPDATE  TABLE NAME 

SET  COLUMN NAME =VALUE WHERE CONDITION;

EXAMPLE

UPDATE HOSPITAL

SET CHARGE=800 WHERE CHARGE<400;

DELETE à A DELETE STATEMENT IN SQL,REMOVES RECORDS IN A RELATIONAL DATABASE MANAGEMENT SYSTEM.

SYNTAX

DELETE  FROM TABLE NAME  WHERE CONDITION

EXAMPLE

DELETE FROM HOSPITAL WHERE SEX=”F”;

DROP TABLE à  A DROP STATEMENT IN SQL REMOVES AN OBJECT FROM A RELATIONAL DATABASE MANAGEMENT SYSTEM

SYNTAX

DROP TABLE NAME;

EXAMPLE

DROP TABLE HOSPITAL;

SELECT DISTINCT STATEMENT

DISTINCTàTHE DISTINCT KEYWORD IS USED TO RETURN ONLY DISTINCT VALUES.

 EXAMPLE

SELECT DISTINCT DEPT FROM HOSPITAL;

IN àTHE IN OPERATOR MAY BE USED IF YOU KNOW THE EXACT VALUE YOU WANT TO RETURN FOR AT LEAST ONE OF THE COLUMNS.

EXAMPLE

SELECT *FROM HOSPITAL WHERE AGE IN(30,45,25);

BETWEEN --- ANDàTHE BETWEEN --- AND OPERATOR SELECTS A RANGE OF DATA BETWEEN TWO VALUES.THESE VALUES CAN BE NUMBERS, TEXT OR DATES.

EXAMPLE

SELECT *FROM HOSPITAL  WHERE CHARGE BETWEEN 100 AND 400;

SELECT *FROM HOSPITAL WHERE CHARGE  NOT BETWEEN  100 AND 400;

GROUP BY à A GROUP BY STATEMENT IN SQL SPECIFIES THAT A SQL ,SELECT  STATEMENT RETURNS A LIST THAT IS GROUPED BY ONE OR MORE COLUMNS,USUALLY IN ORDER TO APPLLY SOME SORT OF AGGREGATE FUNCTION TO CERTAIN COLUMNS

EXAMPLE

SELECT DEPT ,SUM(CHARGE) FROM HOSPITAL WHERE SEX= “M” GROUP BY DEPT;

CREATE A TABLE HOSPITAL1

NAME	AGE	DEPT	DOA	CHARGE	SEX
ARPRIT	62	SURGERY	21/01/12	300	M
ZARINA	22	ENT	11/12/11	250	F
KAREEM	32	ORTHOPAEDIC	19/02/11	200	M
ARUN	12	SURGERY	11/01/10	300	M
ZUBIN	30	ENT	12/03/12	250	M
KATAKI	16	ENT	24/02/12	250	M
ANKITA	29	CARDIOLOGY	25/01/11	800	F
MANISHA	45	GYNAECOLOGY	31/01/12	700	F
KUSH	19	CARDIOLOGY	12/12/11	400	M
SHILPA	23	MEDICAL	11/11/11	300	F
KATRINA	26	ENT	02/02/11	150	F
SONAKSHI	27	SURGERY	03/04/12	100	F

[A] TO SELECT ALL THE INFORMATION OF PATIENTS OF CADIOLOGY DEPT.

[B] TO LIST THE  NAMES OF FEMALE PATIENTS WHO ARE IN ENT DEPT.

[C] TO LIST NAMES OF ALL PATIENTS WITH THEIR DATE OF ADD IN ASCENDING ORDER

[D] TO DISPLAY PATIENTS NAME ,CHARGE,AGE  FOR ONLY FEMALE PATIENTS.

[E] TO COUNT THE NUMBER OF PATIENTS WITH AGE<30

[F] TO INSERT  A NEW ROW IN THE HOSPITAL TABLE WITH FOLLOWING DATA

11    AFTAB   24   SURGERY  25/12/11   300  M