1 / 37

Advanced SQL

Advanced SQL. Overview Advanced DDL, DML, and DCL Commands. Lecture Objectives. Review basic DDL, DML, and DCL SQL commands used in Oracle Learn advanced SQL commands useful for DBA’s and database programmers. Oracle SQL. DDL Basic Object Manipulation Set Create Table (Alter, Drop)

astro
Download Presentation

Advanced 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. Advanced SQL Overview Advanced DDL, DML, and DCL Commands BACS 485—Database Management

  2. Lecture Objectives • Review basic DDL, DML, and DCL SQL commands used in Oracle • Learn advanced SQL commands useful for DBA’s and database programmers BACS 485—Database Management

  3. Oracle SQL • DDL • Basic Object Manipulation Set • Create Table (Alter, Drop) • Create View (Alter, Drop) • Create Index (Alter, Drop) • Create Sequence (Alter, Drop) BACS 485—Database Management

  4. Create Table Commands CREATE TABLE name (col-name type [(size)][CONSTRAINT],...); CONSTRAINT name {PRIMARY KEY | UNIQUE | REFERENCES foreign_table [(foreignfield)] } Basic Create Table: CREATE TABLE STUDENT (STUID CHAR (5) PRIMARY KEY, SSN CHAR (9), LNAME VARCHAR2 (25), FNAME VARCHAR2 (15), MAJOR VARCHAR2 (7), CREDITS NUMBER (1)); BACS 485—Database Management

  5. Create Table Commands CREATE TABLE scott.emp (empno NUMBER CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL CONSTRAINT upper_ename CHECK (ename = UPPER(ename)), job VARCHAR2(9), mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno) on delete cascade, hiredate DATE DEFAULT SYSDATE, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500), comm NUMBER(9,0) DEFAULT NULL, deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL CONSTRAINT fk_deptno REFERENCES scott.dept(deptno)); BACS 485—Database Management

  6. Alter Table Commands ALTER TABLE emp ADD (CONSTRAINT sal_com_cc CHECK (sal + comm <= 5000)) DISABLE CONSTRAINT sal_com_cc; ALTER TABLE EMP DROP (SSN) CASCADE CONSTRAINTS; ALTER TABLE dept ADD CONSTRAINT manager_fk FOREIGN KEY (manager) REFERENCES emp (mgr); ALTER TABLE emp ADD (thriftplan NUMBER(7,2), loancode CHAR(1) NOT NULL); BACS 485—Database Management

  7. Create View Commands CREATE VIEW dept20 AS SELECT ename, sal*12 annual_salary FROM emp WHERE deptno = 20; CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = ’CLERK’ WITH READ ONLY; ALTER VIEW customer_view COMPILE; DROP VIEW dept20; BACS 485—Database Management

  8. Index Commands CREATE INDEX emp_idx ON scott.emp (ename); CREATE INDEX emp_i ON emp (UPPER(ename)); ALTER INDEX emp_ix REBUILD REVERSE; DROP INDEX monolith; BACS 485—Database Management

  9. Sequence Command CREATE SEQUENCE seq1 INCREMENT BY 10; CREATE SEQUENCE acct_seq INCREMENT BY 10 START WITH 100 NOMAXVALUE NOCYCLE CACHE 10; ALTER SEQUENCE seq1 MAXVALUE 1500; DROP SEQUENCE elly.seq1; BACS 485—Database Management

  10. Oracle SQL • DML • Select • Insert • Update • Delete BACS 485—Database Management

  11. Select Commands SELECT {field-list | * | DISTINCT field} FROM table-list WHERE expression GROUP BY group-fields HAVING group-expression ORDER BY field-list; Basic Select Example: Select * From Emp Where Salary > 45000 and Status = “Full Time” Order By SSN; BACS 485—Database Management

  12. More Complex Selects SELECT deptno, MIN(sal), MAX (sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno HAVING MIN(sal) < 1000; Would print: DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 20 800 1100 30 950 950 BACS 485—Database Management

  13. SQL Aggregate Functions • AVGAverage of non-null values • NUMBER  • COU[NT]Count of non-null values • All types  • MIN[IMUM]Minimum value • NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)  • MAX[IMUM]Maximum value • NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)  • NUM[BER]Count of rows • All types  • SUMSum of non-null values • NUMBER  • STDStandard deviation of non-null values • NUMBER  • VAR[IANCE]Variance of non-null values • NUMBER  BACS 485—Database Management

  14. Other Select Operators • Logical Operators • And • Or • Not • Comparison Operator • =,<>,<,<=,>,>= • IS NULL • BETWEEN…AND • IN • LIKE BACS 485—Database Management

  15. Multi-Table Joins • Data from 2 or more tables can be combined into a single select by several methods. • Use ‘where’ clause to combine all data • Use Suq-query • Equi-join • Non-Equi-join • Outer Join • Self-Join BACS 485—Database Management

  16. Select Sub-Query • Sub-queries allow a component of a simple select to be an embedded select statement. SELECT Ename, sal FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'ACCOUNTING'); BACS 485—Database Management

  17. Sub-query operators • The following operators can be used to select one or more tuples in a sub-query. • Single row queries • =, <>, >,>=,<,<= • Multiple row queries • IN – equal to any values in a list • ALL – compare to all values in list • ANY – compare to each value in list BACS 485—Database Management

  18. Single Row Sub-Query SELECT deptno, UPPER(ename), sal FROM emp x WHERE sal > (SELECT AVG(sal) FROM emp WHERE x.deptno = deptno) ORDER BY deptno; BACS 485—Database Management

  19. Multiple Row Query Operators • >ALL means more than the maximum value in list • <ALL means less than the minimum value in list • >ANY means less than the maximum value in list • <ANY means more than the minimum value in list • Note, from the diagram below, it is obvious that overlap is possible. The key to understanding is to remember that these operators are used to determine how a specific value relates to a list. Different operators would produce different relations. BACS 485—Database Management

  20. Multiple Row Sub-queries SELECT SSN, Lname FROM student WHERE FacID IN (SELECT FacID FROM faculty WHERE deptID = 1); SELECT empno, lname, fname, salary FROM employee WHERE salary >ANY (SELECT salary FROM employee WHERE posID = 2) AND posID <> 2; SELECT empno, lname, fname, salary FROM employee WHERE salary <ALL (SELECT AVG(salary) FROM employee GROUP BY deptID); BACS 485—Database Management

  21. Character Functions • Upper (col) – changes all to uppercase • Lower (col) – changes all to lowercase • Initcap (col) – first character of each word uppercase • Concat (col1,col2) – joins 1st value to 2nd value • Substr (col, x, y) – returns substring starting at ‘x’ for ‘y’ characters • Instr (col, c) – returns position of 1st ‘c’ character • Trim(‘c’ FROM col) – removes ‘c’ leading and trailing characters • Length (col) – returns length • Lpad(col,n,str) – pads value with ‘str’ to the left to total width of ‘n’ BACS 485—Database Management

  22. Date Manipulation • Months_Between (date1,date2) – Number months between 2 dates • Add_Months (date, m) – Add calendar months to a date • Next_Day (date, ‘day’) – Find next day after a date • Last_Day (date) – Find last day of the month • Round (date) – Round to nearest day, month, or year • Trunc (date) – Truncate date to nearest day, month, or year • Date + number – Add days to a date • Date – number – Subtract days from a date • Date + number/24 – Add hours to a date • Date1 – Date 2 – Find number of days between 2 dates BACS 485—Database Management

  23. Math Manipulation • Round (col, n) – Round column to n decimal places • Trunc (col, n) – Truncate the column to n decimal places • Power (n,p) – returns np • Abs (n) – Absolute value of n • Mod (x,y) – integer remainder of x/y • +, -, *, / - perform basic mathematical operations BACS 485—Database Management

  24. Complex Select Sub-Queries SELECT a.deptno "Department", a.num_emp/b.total_count "%Employees", a.sal_sum/b.total_sal "%Salary" FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum FROM scott.emp GROUP BY deptno) a, (SELECT COUNT(*) total_count, SUM(sal) total_sal FROM scott.emp) b ; BACS 485—Database Management

  25. Complex Select Sub-Queries SELECT Tablespace_Name, Max_Blocks, Block_Count, Total_Blocks_Free, ((Total_Blocks_Free/Total_Allocated_Blocks)*100) AS Pct_Free FROM (SELECT Tablespace_Name, SUM(Blocks) Total_Allocated_Blocks FROM DBA_DATA_FILES GROUP BY Tablespace_Name), (SELECT Tablespace_Name Free_Space, MAX(Blocks) AS Max_Blocks, COUNT(Blocks) AS Count_Blocks, SUM(Blocks) AS Total_Free_Blocks FROM DBA_FREE_SPACE GROUP BY Tablespace_Name) WHERE Tablespace_Name = Free_Space; BACS 485—Database Management

  26. Complex Select Sub-Queries Find the top 2 salaries in each department: SELECT m.ename, m.sal, m.deptno FROM emp m WHERE m.sal >= (SELECT DISTINCT o.sal FROM emp 0 WHERE (o.sal, 2) IN (SELECT i.sal, ROWNUM FROM (SELECT DSTINCT i2.sal r_sal, i2.deptno, i2.sal, i2.ROWID FROM emp i2) I WHERE I.deptno = m.deptno)) ORDER BY deptno, sal DESC; ENAME SAL DEPTNO ----- --- ----- KING 5000 10 CLARK 2450 10 SCOTT 3000 20 FORD 3000 20 BLAKE 2850 30 BACS 485—Database Management

  27. Complex Select Sub-Queries Find top 3 travel agents based on tickets sold: SELECT id, ticket_price, agent_rank FROM (SELECT agent_id AS id, SUM(ticket_price) AS ticket_price, RANK() OVER (ORDER BY SUM (ticket_price) DESC) AS agent_rank FROM invoice GROUP BY agent_id) WHERE agent_rank < 4; ID Ticket_Price Agent_rank -- ------------ ---------- 1234 8141 1 5675 6708 2 4434 5140 3 BACS 485—Database Management

  28. Select Self-Join SELECT e1.ename || ‘ works for ’ || e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno; Would Print: Employees and their Managers ------------------------------- BLAKE works for KING CLARK works for KING JONES works for KING FORD works for JONES SMITH works for FORD ALLEN works for BLAKE WARD works for BLAKE … BACS 485—Database Management

  29. Select Outer Join SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno; SELECT custname, TO_CHAR(orderdate, ’MON-DD-YYYY’) "ORDERDATE", partno, quantity FROM customers, orders, lineitems WHERE customers.custno = orders.custno (+) AND orders.orderno = lineitems.orderno (+); BACS 485—Database Management

  30. Decode Statement SELECT ename, deptno, DECODE (deptno, 10, ‘ACCOUNTING’, 20, ‘RESEARCH’, 30, ‘SALES’, ‘NOT INDICATED’) FROM emp; ENAME DEPTNO DECODE(DEPTNO ----- ------ ------------- TURNER 30 SALES ALLEN 20 RESEARCH JONES 10 ACCOUNTING WARD 20 RESEARCH BACS 485—Database Management

  31. Insert Commands INSERT INTO dept VALUES (50, ’PRODUCTION’, ’SAN FRANCISCO’); INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (7890, ’JINKS’, ’CLERK’, 1.2E3, NULL, 40); INSERT INTO (SELECT ename, deptno FROM emp WHERE deptno < 10) VALUES (’Taylor’, 20); INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal OR job IN (’PRESIDENT’, ’MANAGER’); INSERT INTO emp VALUES (empseq.nextval, ’LEWIS’, ’CLERK’, 7902, SYSDATE, 1200, NULL, 20); BACS 485—Database Management

  32. Update Commands UPDATE emp SET comm = NULL WHERE job = ’TRAINEE’; UPDATE emp SET job = ’MANAGER’, sal = sal + 1000, deptno = 20 WHERE ename = ’JONES’; UPDATE emp SET sal = sal * 1.1 WHERE empno NOT IN (SELECT empno FROM bonus); BACS 485—Database Management

  33. Complex Update Command UPDATE emp a SET deptno = (SELECT deptno FROM dept WHERE loc = ’BOSTON’), (sal, comm) = (SELECT 1.1*AVG(sal), 1.5*AVG(comm) FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = ’DALLAS’ OR loc = ’DETROIT’); BACS 485—Database Management

  34. Delete Command DELETE FROM temp_assign; DELETE FROM emp WHERE JOB = ’SALESMAN’ AND COMM < 100; DELETE FROM (select * from emp) WHERE JOB = ’SALESMAN’ AND COMM < 100; BACS 485—Database Management

  35. Oracle SQL • DCL • Commit • Rollback BACS 485—Database Management

  36. Commit INSERT INTO dept VALUES (50, ’MARKETING’, ’TAMPA’); COMMIT; COMMIT COMMENT ’In-doubt transaction Code 36, Call x1122’; BACS 485—Database Management

  37. Rollback ROLLBACK; ROLLBACK TO SAVEPOINT sp5; BACS 485—Database Management

More Related