1 / 87

SQL:-

SQL:-. SQL:-SQL is a standard computer language for accessing and manipulating databases. and manipulate data in Oracle, Sybase, SQL Server, DB2, Access, and other database systems. What is SQL?:- SQL stands for S tructured Q uery L anguage SQL allows you to access a database

tibor
Download Presentation

SQL:-

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL:- SQL:-SQL is a standard computer language for accessing and manipulating databases. and manipulate data in Oracle, Sybase, SQL Server, DB2, Access, and other database systems. What is SQL?:- SQL stands for Structured Query Language SQL allows you to access a database SQL is an ANSI standard computer language SQL can execute queries against a database SQL can retrieve data from a database SQL can insert new records in a database SQL can delete records from a database SQL can update records in a database SQL is easy to learn

  2. (Data retrieval) • SQL Commands are divided into following categories SQL COMMANDS • SELECT • INSERT • UPDATE • DELETE • MERGE • CREATE • ALTER • DROP • RENAME • TRUNCATE • COMMIT • ROLLBACK • SAVEPOINT • GRANT • REVOKE Data manipulation language (DML) Data definition language (DDL) Transaction control Data control language (DCL)

  3. Data Manipulation Language:- Data manipulation languages (DML) is That part of SQL which consists of a set of commands that determine which value are present in the table at any given time Data Manipulation languages is divided into three categories:- • Retrieving Data • Manipulating data and • Updating data

  4. a) Data retrieval:-retrieving data means getting information out of a table . A selection of data items stored in a table is presented on the screen. Example of data retrieving is Select * from table name; b) Manipulation Data:-DML features that allow us to perform statistical function on data namely averaging and summing columns and other arithmetic functions like multiplying values in two or more columns of a table C) Updating Data:- updating data refers to inserting and deleting rows in tables and changing values in the columns. DML has commands for dealing with retrieving, manipulating and updating a database. For manipulation and updating, the commands are update Insert and Delete.

  5. Data control languages:- data control languages (DCL) is another portion of SQL Which allows definition of a security mechanism or schema for protecting data from unauthorized access. DCL consists of features that determine whether a user is permitted to perform a particular action. It contains commands like Grant, revoke etc. These are also known as Transaction control commands

  6. Data manipulation Languages (DML):- SELECT:-Use to display the data from a table INSERT:- Use to insert a row in a table DELETE:-Use to delete a row from table UPDATE:- Use to change the content of a table MERGE:- Use to merge the data one table to another table.

  7. Data Definition languages:- CREATE TABLE:- use to crate a table ALTER TABLE:-Changes the definition of table DROP TABLE:- use to delete a table. CREATING INDEX:- use to create an index DROP INDEX:- use to delete an index RENAME TABLE:-use to change name of table TRUNCATE:-Use to Delete record form a table

  8. COMMIT:- Use to save work done • ROLLBACK:- Use to restore database to original since the last commit • SAVEPOINT:- use to identify a point in a transaction to which you can later roll back • GRANT:-The Grand command is used to permit users access to the database. • REVOKE:-The Revoke command is used to cancel database privileges from users

  9. SQL DATA TYPES Data type:- • CHAR (size) • VRACHAR2(Size) • NUMBER (p,s) • DATE • LONG

  10. CHAR:- This data type is used for fixed length character string or maximum length 255 characters. • VRACHAR2:- This data type is used for fixed variable length character strings or maximum length 2000 characters. • NUMBER:-This data type used for number either fixed or floating point. Number up to 38 digits can be stored. • DATE:- the standard DATE format is DD-MM-YY Example:- 24-JAN-02.

  11. LONG:-This data type is used to store variable length character strings containing up to 65,535 character up to 2GB). Long data is generally used to store arrays of binary data in ASCII format. LOB DATATYPE:- BLOB:-binary LOB ,4 GB In length stored in the database CLOB:- Character LOB,4GB character data stored. Bfile:- Binary files, read only binary data stored out side the database. NLOB:-A NLOB column that support a multi byte character set (e.g. Different Languages , Marathi, Hindi)

  12. Integrity constraint:- While creating a table, you can place certain limitation on the values stored in the table. Different constraints applicable to SQL are • NOT NULL • UNIQUE • PRAMARY KEY • CHECK • DEFAULT • REFERENCES

  13. NOT NULL:- Constraint prevents a column from accepting null values. If you try to insert a null value in such a column, it will be rejected. Note that NULL value does not means a zero value. It just means absences of any data in that column. e.g. Create table cust15 ( Cust_name varchar2(15) NOT NULL, Cust_no Number(3) NOT NULL, Cust_address varchar2(20) NOT NULL );

  14. UNIQUE:- Unique constraints ensures that value entered into a column are all different, i.e unique. A column with this constraints will not accept any duplicate values. Unique key can not be LONG or LONG RAW data type. A table can have more than one unique key which is not possible in primary key e.g. --------------UNIQUE contraint:-------------- Create table temp15 (city VARCHAR2(15) NOT NULL, Sampledate DATE NOT NULL, Noon NUMBER(4,1), Midnight NUMBER(4,1), constraint TROUBLE UNIQUE (city, sampledate) )

  15. Candidate Key:- A candidate key is a combination of one or more columns, the values of which uniquely identify each row of a table Unique constraint :- e.q. Create table temp1 (ename Varchar2(15) Not null, empno Number(3) Not null, Join_Date Date Salary Number (4,1), Location Varchar2(20), Constraints rr Unique (ename, empno)); The key of this table is the combination of ename and empno. NOT NULL can be defined to ensure that value has to be input (By default all column in table allow NULLS) UNIQUE KEY CONSTRAINT is used to check that no two rows have duplication values in a specified column or set of column

  16. PRIMARY KEY:- 1)Primary key help to identify one record from another record and also help in relating table with one another. 2)Primary key is a column or set of columns that uniquely Identifies a row. Its main purpose is the Record Uniqueness. 3)Primary key will not allow duplicate values 4)Primary key will also not allow null values 5)Primary key can not be LONG or LONG RAW data type. 6)Only one primary key is allowed per table. 7)Unique index is created automatically if there is a primary

  17. Primary key constraint:- The primary key of a table is one of the candidate key that you give some special characteristics. You can have only one primary key & primary key column cannot contain null. e.g. Create table temp (city varchar2(15), Sampledate DATE NOT NULL, Noon NUMBER(4,1) Constraint RRR Primary key (City, sampledate)); For single-column primary or candidate keys, you can define the key on the column with a column constraint instead of a table constraints e.g. Create table cust Cust_no NUMBER PRIMARY KEY, Name Varchar2(20), Address Varchar2(30));

  18. CHECH:- Check constraints is used to control the vales entered into a field. A condition is specified along with the Check constraints which must be satisfied by all the values being entered into the column otherwise the vale will be rejected For Example, CHECH(SALARY<=100000) If the vale of salary entered is more than 100000. Then it will be rejected .here SALARY column name. e.g. -----------------Check constraints---------------------- create table emp16 ( empno NUMBER(5), ename VARCHAR2(20), Age NUMBER(3,2) CHECK (Age Between 18 AND 60), deptno NUMBER(5), constraints emp15 PRIMARY KEY(empno), foreign key (deptno) References dept15(deptno) )

  19. DEFAULT:- DEFAULT constraints is used to assign default values to a column, before any value is assigned to it For Example:- SALARY DEFAULT will assign the default value of the as 0.0 even if User Dose not enter any value into the SALARY column. Here SALARY is the column name e.g. Create table cust15 ( Cust_name varchar2(15) NOT NULL, Cust_no Number(3) NOT NULL, Salary Number(5) DEFAULT );

  20. --------------UNIQUE contraint:-------------- Create table temp15 (city VARCHAR2(15) NOT NULL, Sampledate DATE NOT NULL, Noon NUMBER(4,1), Midnight NUMBER(4,1), constraint TROUBLE UNIQUE (city, sampledate) ) ------------------------------Primary Key----------------------- Create table temp12 ( city varchar2(15), sampledate DATE NOT NULL, Noon Number(4,1), Midnight NUMBER(4,1), Constraints temp primary key (city, sampledate) )

  21. Foreign Keys:- 1)Foreign key is a column that references a column of a table and it can be the same table also. 2)Parent that is being referenced has to be unique or primary 3)Child may have duplicates and null but unless it is specified 4)Foreign key constraints can be specified on child but not on parent 5) Parent record can be delete provided no child record exist 6)Master table cannot be updated if child record exit

  22. REFERENCE:- Foreign Key:- A foreign key is a combination of columns with value based on the primary key value from another table. A foreign key constraints also known as a referential integrity constraints. To define foreign key constraints REFERENCE clause is used since it specification the value of the column corresponding to actual values of primary key in the other table e.g. Create table dept15 (deptno number(5) PRIMARY KEY, dname Varchar2(15), loc Varchar2(10) ) Create table emp15 ( empno NUMBER(5), ename VARCHAR2(20), Salary NUMBER(10,2), deptno NUMBER(5), constraints emp14 PRIMARY KEY(empno), foreign key (deptno) References dept15(deptno) )

  23. e.g. --------------PRIMARY KEY & FOREIGN KEY------------------ Create table dept15 (deptno number(5) PRIMARY KEY, dname Varchar2(15), loc Varchar2(10) ) Create table emp15 ( empno NUMBER(5), ename VARCHAR2(20), Salary NUMBER(10,2), deptno NUMBER(5), constraints emp14 PRIMARY KEY(empno), foreign key (deptno) References dept15(deptno) )

  24. ------------------ALTERING THE CONSTRAINTS-------------- Create table dept006 ( deptno number(5), deptname Varchar2(15), loc Varchar2(10) ) desc dept006 ------------------------------------------------------ ALTER table dept006 Add primary key (deptno) desc dept006

  25. ----Create table from A table----(From existing table) Create table rd ( empno,ename,job) AS Select empno,ename,job from emp select * from rd Create table r AS select * from emp --- select all column

  26. Insert record, Delete record , update record, Rename table, ------------------------------------------------------- Create table siom ( sr_no number(5), S_name Varchar2(15), Age number(5), Address varchar2(30), city varchar2(10), Phone number(10) ) desc siom ----------------------------------- Insert into siom Values(01,'rajesh',21, 'sadhashiv peth','pune',992210125) Delete from siom delete from siom where sr_no= 01;

  27. --------------------------------------------------- Create table siom1 ( sr_no number(5), S_name Varchar2(15), Age number(5), Address varchar2(30), city varchar2(10), Phone number(10) ) Insert into siom1 select * from siom ---------------------------------------------------- Update siom set sr_no=01 update siom set sr_no=02 where city='bombay' RENAMEING TABLES Rename siom to siom1

  28. The Create table command:- Rule for Creating Tables:- 1) A table name can have maximum up to 30 characters. 2) Alphabets from A-Z, a-z and number from 0-9 are allowed. 3) A name should begin with an alphabet. 4) The use of the special character like _ is allowed and also recommended. (special character like $, # are allowed only in oracle). 5) Each column of the table uniquely. :- e.g. Name, Wrong:-Name_address 6) Each column has a minimum of three attributes, a name data type and size. 7) Each table column definition is separated from the other by a comma. 8) Finally the SQL statement is terminated with a semi colon. Syntax:- CREATE TABLE < Table name> (<column name1> <Data type>(<size>), <column name2> <Data type>(<size>));

  29. e.g. Create table PGDM (Student_name Varchar2(15), Roll_Number number(3), Class_name Varchar2(10)); Displaying the table structure To display information about the columns defined in a table Syntax:- Describe <Table name>; e.g. Desc PGDM;

  30. Insert Data Into Table:- Once a table is created, the most natural thing to do insert record through the insert command. Syntax:- INSERT INTO <table name> (columname1>,<columname2>) Values(<expression1>,<expression2>); e.g. Insert Into PGDM (Student_name , Roll_Number,Class_name ) Values ('Rajesh', 01, 'MBA'); e.g. Insert Into PGDM (Student_name ,Roll_Number,Class_name,collage_name,city ) Values ('Rajesh', 01, 'MBA','siom','Bombay');

  31. Insert into PGDM Values('&Student_name' ,&Roll_Number, '&Class_name'); Insert into PGDM Values('&Student_name' ,&Roll_Number, '&Class_name','&collage_name','&city'); SELECT:- Once data has been inserted into a table the most logical operation would be to view what has been inserted. Then select command is used to retrieve rows selected from one or more tables. Syntax:- Select * from <table name>; e.g. Select * from PGDM Syntax:- Select <column name1>,<column name2> from <table name>; Select * from <Tablename> where <condition>;

  32. DISTINCT:- The distinct clause allows removing duplicates from the result set. The distinct clause can only be used with select statement. Syntax:- Select DISTINCT <column name1>, <column name2> from <table name>; Select DISTINCT * from <table name>; e.g Select distinct student_name from PGDM; SORTING DATA IN A TABLE:- Oracle allow data from a table to be viewed in a sorted order. The table will be sorted in either ascending or descending order depending on the condition specified in the select sentence.

  33. Syntax:- Select * from <table name> Order by <column name1>, <column name2> <[sort order]>; e.g. select * from pgdm order by student_name; Select * from pgdm order by student_name desc; DELETE:- Removal of all rows or removal of specific Rows Syntax:- Delete from <table name>; Delete from <table name> where <condition>; e.g. Delete from pgdm where student_name like ‘Ra%’;

  34. Update:- The update command is used to change or modify data values in a table. Either all the rows from a table or A Select set of rows from a table Syntax: Update all row Update <table name> Set <column name1> = <Expression1>, <column name2> = <Expression2>; Update conditionally Update <table name> Set <column name1> = <Expression1>, <column name2> = <Expression2> Where <condition>;

  35. Modifying the structure of table:- The structure of a table can be modified by using the ALTER TABLE command. alter table allows changing the structure of an existing table. It is possible to add or delete column, change the data type of existing columns. Syntax:- Adding new column:- Alter table <table name> Add (<new column name> <Data type>(<size>). <new column name> <Data type> (Size>)…); Dropping A column from a table:- Alter table <table name> drop column <column name>; Modifying existing columns:- Alter table <table name> Modify (<column name> < new data type>(<New size>));

  36. ------CREATING A TABLE---------- Create Table emp12 ( emp_no number(3), ename varchar2(15), salary number(10,2) ) Desc emp12 select * from emp12 -------MODIFYING THE STRUCTURE OF TABLE------ -------Adding new Column------ ALTER TABLE EMP12 ADD(address varchar2(15), city varchar2(10) ) desc emp12

  37. -------Modifying exisiting column----------- Alter table emp12 MODIFY (Address Varchar(30) ) -------Dropping an Existing column--------- ALTER table emp12 set unused column address ALTER table emp12 drop column city --------------------------------------------------------------------------- ALTER TABLE EMP12 ADD(address varchar2(15), city varchar2(10) ) Desc emp12 ALTER table emp12 drop (city, address) desc emp12

  38. Restriction on the ALTER TABLE:- The following task cannot be perform when using the alter table clause; Change the name of the table Change the name of the column Decrease the size of a column if table data exists. RENAMEING TABLES:- Oracle allows renaming of tables. Syntax:- Rename <table name> to <new table name>; e.g. Rename siom to siom1

  39. TRUNCATING TABLES:- Truncate table empties a table completely. logically this is equivalent to a delete statement that deletes all rows, but there are practical differences under some circumstances. 1)Truncate operation drop and recreate the table, which is much faster than deleting rows one by one. 2)Truncate operation are not transition-safe. 3) The number of deleted rows are not returned. Syntax:- Truncate table <Table name>; Drop Table:- Use to delete table Syntax:- Drop table <table name>;

  40. DUAL:- • Dual is a table owned by sys. sys owns the data dictionary, and DUAL is part of the data dictionary. Dual is a small oracle worktable, which consists of only one row and one column and contains the x values in that column. Beside arithmetic Calculation, it also supports Data retrieval and it’s formatting. oracle provides a dummy table called DUAL. • e.g. Desc Dua; • Select * from dual; • Select 2*2 from dual; • Select sysdate from dual;

  41. Aggregate function:- SQL aggregate function such as SUM,AVG,MAX,MIN,COUNT ect. 1)SUM:-Returns the sum of the value of ‘n’ Syntax:- SUM([<distinct>|<All>]<n>) e.g. Select SUM (sal) "Total salary" from emp; 2)AVG:-Returns an average value of ‘n’, ignoring null values in a column Syntax:- AVG([<distinct>|<All>]<n>) e.g. Select AVG (sal) "Total average " from emp; 3)MAX:- Returns the maximum value of expr Syntax:-MAX([<distinct>|<All>]<expr>) e.g. select MAX(sal) "maximumsalary " from emp;

  42. 4)MIN:- Returns a minimum value of expr. Syntax:-MIN([<distinct>|<All>]<expr>) e.g. select MIN(sal) "miminum salary" from emp; 5)COUNT:- Returns the number of rows where expr is not null. Syntax:-COUNT([<distinct>|<All>]<expr>) e.g. Select count(*) "No. of Records " from emp;

  43. Numeric Function:- ABS:- Returns the absolute value of ‘n’. e.g. select ABS(-15)"Absolute" from dual; POWER:- Returns m raised to the nth power e.g.Select POWER(16,2) "Raised" from dual; ROUND:- Returns n, rounded to m places to right of a decimal point. e.g. Select Round(15.19,1) "Round" from dual; SQRT: Returns square root of n. e.g. Select SQRT(25) "square Root" from dual; EXP:- Returns e raised to the nth power where e =2.71828183 e.g. select EXP(5) “exponent” from dual;

  44. GREATEST:- Returns the greatest value in a list of expression. e.g. select greatest(4,5,17) "Number" from dual; LEAST:- Returns the least value in a list of expression. e.g. select least(4,5,17) "Number" from dual; MOD:- Returns the remainder of a first number divided by second number passed a parameter. e.g. select mod(15,7) "Mod" from dual; TRUNC:-

  45. String Funcation:- LOWER:-Returns char, with all letters in lowercase. e.g. select LOWER('MANISH SHARMA') "Lower" from Dual; UPPER:- Returns char, with all letter forced to uppercase e.g. select UPPER ('manish sharma') "Upper" from Dual; INITCAP:- Returns a string with the first letter of each word in upper case. e.g. select INITCAP('MANISH SHARMA') "Total case" from Dual; SUBSTR:- e.g. select SUBSTR('MANISH',3,4) "Substring" from Dual; ASCII:- e.g. select ASCII('a') "ASCII value" from Dual; INSTR:- Returns the location of a substring in a string. e.g. select INSTR ('Manish','h') from Dual;

  46. LENGTH:- Returns the length of a word e.g. select LENGTH ('SHARMA') from dual; LTRIM:- Remove characters from the left of char with initial character removed upto the first character not in set. e.g. select LTRIM('MANISH','M') "LTRIM" from dual; RTRIM:-Returns char, with final characters removed after the last character not in the set. e.g. select RTRIM('MANISH','H') "RTRIM" from dual; TRIM:- Remove all specified character either from the beginning or ending of a string. e.g. select TRIM(LEADING 'x' FROM 'xxxManishxxx') "TRIM" from dual; e. g. select TRIM(BOTH 'x' FROM 'xxxManishxxx') "TRIM" from dual; select TRIM(TRAILING 'x' FROM 'xxxManishxxx') "TRIM" from dual;

  47. LPAD:- e.g. select lpad('page 1',21,'*')"LPAD" from dual; RPAD:- e.g. select RPAD('page 1',21,'*')"LPAD" from dual; VSIZE:-Returns he number of bytes in the internal representation of an expresssion. e.g. select VSIZE('Mansh Kumar') "size" from dual;

  48. Character Conversion function:- TO_CHAR:- convert a value of a number data type to a Character data type. e.g. select TO_char(17145,'$099,999') from dual; e.g. select TO_CHAR(SYSDATE,'DD-MM-YY') from dual; select to_char(hiredate,'day-Month-year') from emp; select TO_CHAR(SYSDATE,'Dd-MM YY') from dual; select * from emp where to_char(hiredate,'mon')='dec'; select to_char(hiredate,'day') from emp; select ename,hiredate,to_char(hiredate,'day') from emp where ENAME='KING'; select ename,hiredate,to_char(hiredate,'day') from emp; select * from emp order by hiredate; select * from emp order by to_char(hiredate,'yy'); select to_char(hiredate,'ddth-Month-yyyy') from emp;

  49. Date Conversion function:- TO_Date:- e.g. select TO_DATE('06/07/02','DD/MM/YY') from dual; ADD_MONTHS:- e.g. select sysdate from dual; e.g. select ADD_MONTHS(sysdate,4) "Add Month" from dual; LAST_DAY: Returns the last date of the month specified with the function. e.g. select sysdate,last_day(sysdate) "lastday" from dual; MONTHS_BETWEEN:- Returns number of months between d1 and d2. e.g. select Months_Between('02-feb-92','02-Jan-92') "Months" from dual; select Months_Between('02-feb-93','02-Jan-92') "Months" from dual; NEXT _DAY:- e.g. select NEXT_DAY('01-september-08','saturaday') "NEXT DAY" from dual;

  50. ROUND:- e.g. select Round(TO_DATE('01-Jul-04'),'YYYY')"YEAR" from dual; e.g. select Round(TO_DATE('01-Jul-05'),'YYYY')"YEAR" from dual; NEW_TIME:- Syntax:-NEW_TIME(Date, Zone1, Zone2) e.g. select Round(TO_DATE('01-Jul-04'),'YYYY')"YEAR" from dual; e.g. select New_Time(TO_DATE('2008/08/30 01:45','yyyy/mm/dd HH24:MI'),'BST','HST')"HST" from dual e.g. select New_Time(TO_DATE('2008/08/30 01:45','yyyy/mm/dd HH24:MI'),‘CST','HST')"HST" from dual e.g. select New_Time(TO_DATE('2008/08/30 01:45','yyyy/mm/dd HH24:MI'),'MDT','NST')"NST" from dual

More Related