1 / 75

SQL Data Manipulation Language(DML) in Oracle's Dialect

SQL Data Manipulation Language(DML) in Oracle's Dialect. SQL DDL, DML, & DCL. Data Definition Language (DDL) Define the database. CREATE, ALTER, or DROP a base TABLE or VIEW. Data Manipulation Language (DML) Manipulate the data in an existing database.

giles
Download Presentation

SQL Data Manipulation Language(DML) in Oracle's Dialect

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 Data Manipulation Language(DML)in Oracle's Dialect

  2. SQL DDL, DML, & DCL • Data Definition Language (DDL) • Define the database. • CREATE, ALTER, or DROP a base TABLE or VIEW. • Data Manipulation Language (DML) • Manipulate the data in an existing database. • SELECT, INSERT, UPDATE, or DELETE data. • Data Control Language (DCL) • Control user access to an existing database. • GRANT or REVOKE user privileges.

  3. SQL DML • Query (read-only) • SELECT • Update (write) • INSERT • DELETE • UPDATE • Attention: SQL uses the keyword "UPDATE" to mean "change" specifically. However, the term "update" is also used to refer to the three write operations as a group.

  4. Relations vs. SQL Tables • A relation is a set of tuples. • There are no duplicate tuples. • Tuples are unordered, top to down. • Attributes are unordered, left to right. • Each tuple contains exactly on value for each attribute. • A SQL table is a bag (multi-set) of rows. • Duplicate rows are allowed. • Rows are unordered, top to down. • Columns are ordered, left to right. • Each row contains exactly one value for each column. • SQL tables are not relations!

  5. SQL Tables • SQL tables are not sets, but bags (multi-sets), of rows. Duplicated rows are allowed. • Duplicated rows will not be automatically removed from the result unless DISTINCT is specified. • SQL tables are not relations. • SQL is not truly relational. • SQL is far from “perfect”.

  6. Assumptions • This lecture will use the suppliers-parts database. • We'll translate the sample queries we have used from relational algebra into SQL SELECT statements. • Suppose the tables are owned by schema "min". • We'll use all-uppercase letters for SQL keywords.

  7. The Suppliers-Parts DB: ERD S# SNAME SUPPLIER STATUS M CITY TOTQTY SP QTY P# PNAME M COLOR PART WEIGHT CITY TOTQTY

  8. The Suppliers-Parts DB: Tables S# SNAME STATUS CITY S# P# QTY S SP S1 S2 S3 S4 S5 Smith Jones Blake Clark Adams 20 10 30 20 30 London Paris Paris London Athens S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 300 200 400 200 100 100 300 400 200 200 300 400 P# PNAME COLOR WEIGHT CITY P P1 P2 P3 P4 P5 P6 Nut Bolt Screw Screw Cam Cog Red Green Blue Red Blue Red 12 17 17 14 12 19 London Paris Rome London Paris London

  9. SELECT SELECT FROM [WHERE] [GROUP BY] [HAVING] [ORDER BY] • [ optional clause] • Set operators: UNION, INTERSECT, MINUS.

  10. Q0 - Base Table • Q0: List all suppliers. S SELECT * FROM min.s; • Column names are listed in the SELECT clause. • * stands for all columns. • Table names are listed in the FROM clause. • The schema name "min" can be omitted if the query is posed by the owner of the table.

  11. Q6 - Restrict • Q6: Which suppliers are located in London? S WHERE CITY = 'London’ SELECT * FROM min.s WHERE city = 'London'; • The restriction condition is specified in the WHERE clause. • SQL is case-insensitive, e.g., SELECT or select, min.s or MIN.S. • But string values are case-sensitive, e.g., 'London'.

  12. Q14 - Join • Q14: Which suppliers supply part ‘P1’? ((S JOIN SP) WHERE P#= 'P1') {ALL BUT P#, QTY} SELECT s.* FROM min.s, min.sp WHERE s.s# = sp.s# AND p#='P1';

  13. Q14 (Cont.) • If multiple tables are specified in the FROM clause, the result is always the Cartesian product of the tables. • If you need a join, specify the join condition in the WHERE clause, even if it's a natural join. • Join is done by Cartesian product + restrict. • Logical operators: AND, OR, NOT. • Attribute names must be qualified when confusion rises, e.g., s.s# = sp.s#

  14. Q1 - Union • Q1: Which suppliers are located in London OR supply part P1 (or both)? (S WHERE CITY = 'London’) UNION (((S JOIN SP) WHERE P#= 'P1') {ALL BUT P#, QTY}) (SELECT * FROM min.s WHERE city='London') UNION (SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1');

  15. Q1 (Cont.) SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1' OR city='London'; • UNION and OR are logically related. • There are often many different ways to formulate any given query. • However, the result of the above two statements may be different. • Why?

  16. Q1 (Cont.) SELECT DISTINCT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1' OR city='London'; • SQL tables are not sets, but bags (multi-sets). • Duplicated rows are not automatically removed. • To remove duplicated rows, use DISTINCT.

  17. Q7 - OR vs. UNION • Q7: Which suppliers are located in London or Athens? S WHERE CITY = 'London' OR CITY = 'Athens' (S WHERE CITY = 'London') UNION (S WHERE CITY = 'Athens') SELECT * FROM min.s WHERE city='London' OR city='Athens'; (SELECT * FROM min.s WHERE city='London') UNION (SELECT * FROM min.s WHERE city='Athens');

  18. Q1 (Cont.) - IN SELECT * FROM min.s WHERE city='London' OR s# IN (SELECT s.s# FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1'); • Nested Query: Query within another query. • IN • 'S1' IN {'S1', 'S2'} is true; 'S5' IN {'S1', 'S2'} is false. • 'S1' NOT IN {'S1', 'S2'} is false.

  19. Q1 (Cont.) - EXISTS SELECT * FROM min.s WHERE city='London' OR EXISTS (SELECT * FROM min.sp WHERE s.s#=sp.s# AND p#='P1'); • Nested Query: Query within another query. • EXISTS

  20. "Uncorrelated" Nested Query SELECT * FROM min.s WHERE city='London' OR s# IN (SELECT s.s# FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1'); • "Inner" query is independent of the "outer" query. • "Inner" query is a "complete" query. • "Inner" query can be executed independently. • "Inner" query needs to be evaluated only once.

  21. "Correlated" Nested Query SELECT * FROM min.s WHERE city='London' OR EXISTS (SELECT * FROM min.sp WHERE s.s#=sp.s# AND p#='P1'); • "Inner" query uses data from "outer" query. • "Inner" query can't be executed independently. • "Inner" query must be evaluated for each row returned from the outer query.

  22. Q2 - Intersect • Q2: Which suppliers are located in London AND supply part P1? (S WHERE CITY = 'London’) INTERSECT (((S JOIN SP) WHERE P#= 'P1') {ALL BUT P#, QTY}) (SELECT * FROM min.s WHERE city='London') INTERSECT (SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1');

  23. Q2 (Cont.) SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1' AND city='London'; • INTERSECT and AND are logically related.

  24. Q2 (Cont.) - IN SELECT * FROM min.s WHERE city='London' AND s# IN (SELECT s.s# FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1');

  25. Q2 (Cont.) - EXISTS SELECT * FROM min.s WHERE city='London' AND EXISTS (SELECT * FROM min.sp WHERE s.s#=sp.s# AND p#='P1'); • EXISTS

  26. Q2 (Cont.) - Subquery in FROM SELECT * FROM (SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1') S_P1 WHERE city='London';

  27. A Query may not return a table • The result of a query may contain duplicated columns and is no longer a table. • The following SELECT does not work. • "ORA-00918: column ambiguously defined." SELECT S_P1.s# FROM (SELECT * FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1') S_P1 WHERE city='London';

  28. Q3 - Difference • Q3: Which suppliers are located in London AND DO NOT supply part P1? (S WHERE CITY = 'London') MINUS (((S JOIN SP) WHERE P#= 'P1') {ALL BUT P#, QTY}) (SELECT * FROM min.s WHERE city='London') MINUS (SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1');

  29. Q3 (Cont.) - NOT IN SELECT s.* FROM min.s WHERE city='London' AND s.s# NOT IN (SELECT s.s# FROM min.s, min.sp WHERE s.s#=sp.s# AND p# = 'P1'); • MINUS and NOT are logically related. • NOT IN

  30. Q3 (Cont.) - NOT EXISTS SELECT s.* FROM min.s WHERE city='London' AND NOT EXISTS (SELECT * FROM min.sp WHERE s.s#=sp.s# AND p# = 'P1'); • NOT EXISTS

  31. Q4 - Difference • Q4: Which suppliers supply part P1 AND ARE NOT located in London? (((S JOIN SP) WHERE P#= 'P1') {ALL BUT P#, QTY}) MINUS (S WHERE CITY = 'London’) (SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1') MINUS (SELECT * FROM min.s WHERE city='London');

  32. Q4 (Cont.) SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p# = 'P1' AND city <> 'London'; • Comparison: =, >, <, >=, <=, <>

  33. Q4 (Cont.) - NOT IN SELECT s.* FROM min.s, min.sp WHERE s.s#=sp.s# AND p#='P1' AND s.s# NOT IN (SELECT s# FROM min.s WHERE city = 'London');

  34. Q20 • Q20: Get supplier names for suppliers who do not supply part P2. ((S {S#} MINUS(SP WHERE P#='P2') {S#}) JOIN S) {SNAME} SELECT sname FROM min.s WHERE s# IN (SELECT s# FROM min.s MINUS SELECT s# FROM min.sp WHERE p#='P2');

  35. Q20 (Cond.) SELECT sname FROM min.s WHERE s# NOT IN (SELECT s# FROM min.sp WHERE p#='P2');

  36. Q9 - Project • Q9: What are the current supplier numbers? S {S#} SELECT s# FROM min.s; • The attributes to be displayed are listed in the SELECT clause.

  37. Q10 - Project • Q10: What are the current part numbers? P {P#} SELECT p# FROM min.p;

  38. Q5 - Product • Q5: What are all current supplier-number/part-number PAIRS? (S {S#}) TIMES (P {P#}) SELECT s#, p# FROM min.s, min.p; • If multiple tables are specified in the FROM clause, the result is the Cartesian product of the tables.

  39. Q8 - Duplicates • Q8: Which cities are suppliers located in? S {CITY} S {ALL BUT S#, SNAME, STATUS} SELECT city FROM min.s; SELECT DISTINCT city FROM min.s; • SQL SELECT do not automatically remove duplicated rows in the result. • Use DISTINCT to remove duplicated rows.

  40. Q11 - Join • Q11: What suppliers supply what parts? List the info. of each supplier and the parts it supplies. S JOIN SP SELECT s.*, sp.p#, sp.qty FROM min.s, min.sp WHERE s.s# = sp.s#; • If multiple tables are specified in the FROM clause, the result is always the Cartesian product of the tables. • If you need a join, specify the join condition in the WHERE clause, even if it's a natural join. • Join is done by Cartesian product + restrict.

  41. Q15 - Restrict, Project, & Join • Q15: Get supplier names for suppliers who supply part P2. ((S JOIN SP) WHERE P#='P2') {SNAME} (S JOIN (SP WHERE P#='P2')) {SNAME} SELECT sname FROM min.s, min.sp WHERE s.s# = sp.s# AND p#='P2';

  42. Q16 - Join of More than Two Tables • Q16: Get supplier names for suppliers who supply at least one red part. ((((P WHERE COLOR='Red') JOIN SP) {S#}) JOIN S) {SNAME} SELECT sname FROM min.s, min.p, min.sp WHERE s.s# = sp.s# AND p.p# = sp.p# AND color = 'Red';

  43. Q16 (Cont.) SELECT DISTINCT sname FROM min.s, min.p, min.sp WHERE s.s# = sp.s# AND p.p# = sp.p# AND color = 'Red';

  44. Q19 - Self-Join • Q19: Get all pairs of supplier numbers such that the two suppliers concerned are "colocated" (i.e., located in the same city). (((S RENAME S# AS SA) {SA, CITY} JOIN (S RENAME S# AS SB) {SB, CITY}) WHERE SA < SB) {SA, SB} SELECT s1.s#, s2.s# FROM min.s s1, min.s s2 WHERE s1.s# < s2.s# AND s1.city = s2.city;

  45. Q19 - Self-Join (Cont.) • "Self-Join": Two occurrences of the same table are joined. • Tables can be given correlation names, e.g., min.s s1 • For long table names, save some writing. • e.g., min.suppliers_parts_projects spj • Obviously, in "self-join", at least one occurrence must be renamed.

  46. Q13 - Division • *Q13: Which suppliers supply all parts? (S {S#}) DIVIDEBY (P {P#}) PER SP {S#, P#} • Division is very hard to express in SQL. • Rephrase: Get the suppliers exceptthose who do notsupply some parts.

  47. Q13 - Division (Cont) SELECT s# FROM min.s WHERE NOT EXISTS (SELECT p# FROM min.p MINUS SELECT p.p# FROM min.p, min.sp WHERE p.p# = sp.p# AND s.s# = sp.s#);

  48. Q12 - Division • *Q12: Which suppliers supply all parts named ‘Nut’? (S {S#}) DIVIDEBY ((P WHERE PNAME='Nut') {P#}) PER SP {S#, P#} • Rephrase: Get the suppliers exceptthose who do notsupply some parts named 'Nut'.

  49. Q12 - Division (Cont) SELECT s# FROM min.s WHERE NOT EXISTS (SELECT p# FROM min.p WHERE pname='Nut' MINUS SELECT p.p# FROM min.p, min.sp WHERE pname='Nut' AND p.p# = sp.p# AND s.s# = sp.s#);

  50. Q17 - Division • *Q17: Get supplier names for suppliers who supply all parts ((S {S#} DIVIDEBY P {P#} PER SP {S#, P#}) JOIN S) {SNAME} • Exercise

More Related