1 / 23

SQL

SQL. DDL (Data Definition Language) Create Alter Drop Truncate Comment. SQL. DML (Data Manipulation Language) Insert Update Delete Call Explain plan Lock Table. SQL. DCL (Data Control Language) Commit Savepoint Rollback Set transaction Grant Revoke. SQL.

zenda
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 • DDL (Data Definition Language) • Create • Alter • Drop • Truncate • Comment

  2. SQL • DML (Data Manipulation Language) • Insert • Update • Delete • Call • Explain plan • Lock Table

  3. SQL • DCL (Data Control Language) • Commit • Savepoint • Rollback • Set transaction • Grant • Revoke

  4. SQL • DQL (Data Query Language) • Select

  5. The Comment Statement You can associate a comment with a table, view, index or column using ‘comment’ command • COMMENT ON TABLE CUSTOMER IS 'Represents domestic customers‘; • COMMENT ON COLUMN CUSTOMER.ADDRESS IS 'Customer address‘; TAB_COMMENTS, COL_COMMENTS, OPERATOR_COMMENTS, MVIEW_COMMENTS, INDEXTYPE_COMMENTS

  6. Select * --slection clause from customer where age >25 /* selecting all customers whose age>25*/;

  7. The LOCK Statement LOCK TABLE [schema.] table [options] IN lockmode MODE [NOWAIT] LOCK TABLE [schema.] view [options] IN lockmode MODE [NOWAIT] Options: PARTITION (partition) SUBPARTITION (subpartition) @dblink lockmodes: EXCLUSIVE SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE ROW SHARE* | SHARE UPDATE*

  8. The Select Statement SELECT FROM WHERE GROUP BY ORDER BY

  9. CONSTRAINTS NOT NULL UNIQUE CHECK – Syntax CHECK IN(Values) PRIMARY KEY FOREIGN KEY Constraint information is stored in: -- USER_CONSTRAINTS -- ALL_CONSTRAINTS Default Constraint Name : SYS_Cn

  10. Alter ALTER TABLE Table Name ADD (ColumnNamedatatype); ALTER TABLE Table NameMODIFY(columnnamenew datatype); ALTER TABLE Table NameADD CONSTRAINT Constraint Name Constraint;

  11. Cascading Referential Integrity Constraints ON DELETE CASCADE ON DELETE SET NULL ON DELETE SET DEFAULT ON UPDATE CASCADE

  12. DROP, TRUNCATE AND DELETE • DROP TABLE TableName; • TRUNCATE TABLE TableName ; • DELETE FROM Table Name [WHERE condn];

  13. COMMIT AND ROLLBACK COMMIT; ROLLBACK; Implicit Commit quit, Exit, any DDL Command

  14. String Functions • UPPER(String) LOWER(String) • CONCAT(String1,String2)  String1||String2 • RPAD (String, Tot. Num. Of Chara. in o/p String, pattern) • LPAD (String, Tot. Num. Of Chara. in o/p String, pattern) • RTRIM (String, pattern) LTRIM (String, pattern) • INITCAP (String) LENGTH (String) • SUBSTR (String, from) SOUNDEX(String) • SUBSTR (String, from, number of characters needed) • INSTR (String, pattern, search start from, which occurrence)

  15. Pattern Matching String LIKE ‘pattern’ String Like ‘amri%’ String Like ‘%ta’ String Like ‘%mri%’ String Like ‘am_ri%’ String Like ‘_\_amri%\%’ escape ‘\’

  16. Number Functions • MAX, MIN • LEAST(Column Name 1, Column Name 2)  Least No. • GREATEST (Column Name 1, Column Name 2) • COUNT (DISTINCT Column Name) • COUNT (Column Name) • Count(*) = counts all rows of the table irrespective of null values.

  17. Number Functions • ABS(146)=146 Exp(3)=20.08 • ABS(-30)=30 Ln(3)-1.098 • Ceil(2)=2 Log(10,100)=2 • Ceil(1.3)=2 Round(66.666,2)=66.67 • Ceil(-1.3)=1 Trunc(66.666,2)=66.66 • Floor(2)=2 Sign(-3)=-1 • Floor(1.3)=1 Trignometricfunctions • Floor(-1.3)=2 Stddev,variance • Mod(10,3)=1 AVG(sal) • Power(3,2)=9 Sum(sal) • Sqrt(64)=8

  18. Date Functions • TO_DATE(valid date in varchar2, date format) • TO_CHAR ( valid date, date format) • ADD_MONTHS(sysdate,6) • ADD_MONTHS (sysdate,-6) • GREATEST (sysdate,holidaydate) • LEAST (TO_DATE (’20-jan-02’), SYSDATE) • NEXT_DAY (cycledate,’Friday’) • LAST_DAY(cycledate) – last day of the month • MONTHS_BETWEEN(DATE1, DATE2) • DATE +n  n days are added to the DATE

  19. DATE FORMAT Default  dd – mon – yy if century is not specified it assumes the current century. Date format : ‘dd/ day : mm/mon/month :yy/yyyyhh: mi: ss AC/BC’ ALTER SESSION SET NLS_DATE_FORMAT=date format

  20. SET OPERATIONS • UNION • INTERSECT • MINUS

  21. The DECODE Function SELECT EmpName, DECODE (salary, 1000, ‘peon’, 2000, ‘clerk’, ’manager’) designation FROM employees;

  22. JOINS Types of joins: • Equi Join • Outer Join (Left, Right, Full) • Cross Join • Self Join

More Related