Introduction to SQL Developing Applications Using SQLPlus 16122021

Introduction to SQL Developing Applications Using SQL*Plus 16/12/2021 Slide : 1 -1 Introduction to SQL

RDBMS "A Relational Model of Data for large Shared Data Banks” by Dr. E. F. Codd History of SQL SEQUEL Structured English Query Language Relational Corporation First commercially available implementation of SQL 16/12/2021 Slide : 1 -2 Introduction to SQL

Schema Objects Elements of SQL clusters, database links, database triggers, indexes, packages, sequences, snapshot logs, stored functions, stored procedures, synonyms, tables, views Non - Schema Objects profiles, rollback segments, tablespaces, users 16/12/2021 Slide : 1 -3 Introduction to SQL

Character Values Char(size), Varchar 2(size), Long Internal Data Types Numeric Values Number(precision, scale) Date Values Century, Year, Month, Day, Hour, Minute, Second Binary Values Raw, Long Raw, ROWID 16/12/2021 Slide : 1 -4 Introduction to SQL

Arithmetic Operators Character Comparison Logical Set 16/12/2021 Slide : 1 -5 Introduction to SQL

Positive / Negative (+-) Arithmetic Operators SELECT * FROM orders WHERE qtysold= 1 SELECT * FROM emp WHERE -sal < 0 Multiply / Devide (*/) UPDATE emp SET sal = sal * 1. 1 Add / Substract (+-) SELECT sal + comm FROM emp WHERE SYSDATE - hiredate > 365 16/12/2021 Slide : 1 -6 Introduction to SQL

Character Operators Concatenation (||) 16/12/2021 Slide : 1 -7 Introduction to SQL SELECT 'Name is ' || ename FROM emp

Equality / Non-Equality (=, !=) SELECT * FROM emp WHERE sal = 1500 Comparision Operators SELECT * FROM emp WHERE sal != 1500 Greater / Less than (>, <, >=, <=) SELECT * FROM emp WHERE sal > 1500 SELECT * FROM emp WHERE sal < 1500 SELECT * FROM emp WHERE sal >= 1500 SELECT * FROM emp WHERE sal <= 1500 16/12/2021 Slide : 1 -8 Introduction to SQL

Subset (IN, NOT IN, ANY, ALL, EXISTS) SELECT * FROM emp WHERE job IN ('CLERK', 'ANALYST') SELECT * FROM emp WHERE sal NOT IN (SELECT sal FROM emp WHERE deptno= 30) Comparision Operators SELECT * FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE deptno= 30) SELECT * FROM emp WHERE sal>= ALL(1, 5) SELECT dname, deptno FROM dept WHERE EXISTS (SELECT * FROM emp WHERE deptno = emp. deptno) 16/12/2021 Slide : 1 -9 Introduction to SQL

Null testing (IS [not] NULL) SELECT dname, deptno FROM emp WHERE comm IS NULL Comparision Operators Range ([not] BETWEEN) SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000 Character matching ([not] LIKE) SELECT * FROM tab 1 WHERE col 1 LIKE 'A_C/%E%' ESCAPE '/' 16/12/2021 Slide : 1 -10 Introduction to SQL

NOT SELECT * FROM emp WHERE NOT (job IS NULL) Logical Operators AND SELECT * FROM emp WHERE job = 'CLERK' AND deptno = 10 OR SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10 16/12/2021 Slide : 1 -11 Introduction to SQL

UNION Set Operators SELECT part, partnum, to_date(null) date_in FROM orders_list 1 UNION SELECT part, to_null(null), date_in FROM orders_list 2 UNION ALL SELECT part FROM orders_list 1 UNION ALL SELECT part FROM orders_list 2 16/12/2021 Slide : 1 -12 Introduction to SQL

INTERSECT Set Operators SELECT part FROM orders_list 1 INTERSECT SELECT part FROM orders_list 2 MINUS SELECT part FROM orders_list 1 MINUS SELECT part FROM orders_list 2 16/12/2021 Slide : 1 -13 Introduction to SQL

Single row SQL Functions Number Character Date Conversion Group User 16/12/2021 Slide : 1 -14 Introduction to SQL

DRL Data Retrieve Language Select SQL Commands DDL Data Definition Language Create, Alter, Drop, Truncate DML Data Manipulation Language Insert, Update, Delete, Commit, Rollback, Savepoint DCL Data Control Language Grant, Revoke 16/12/2021 Slide : 1 -15 Introduction to SQL