1 / 84

Structured Query Language – The Basics

Structured Query Language – The Basics. By Hitesh Sahni www.hiteshsahni.com. What We’re Going to Cover. Overview of SQL (This may be review for some of you) Data Definition Language Creating tables (we’ll just talk about this) Data Manipulation Language Inserting/Updating/Deleting data

jaegar
Download Presentation

Structured Query Language – The Basics

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. Structured Query Language – The Basics By Hitesh Sahni www.hiteshsahni.com

  2. What We’re Going to Cover • Overview of SQL (This may be review for some of you) • Data Definition Language • Creating tables (we’ll just talk about this) • Data Manipulation Language • Inserting/Updating/Deleting data • Retrieving data • Single table queries • Where • Joins • Grouping

  3. SQL • SQL is a data manipulation language. • SQL is not a programming language. • SQL commands are interpreted by the DBMS engine. • SQL commands can be used interactively as a query language within the DBMS. • SQL commands can be embedded within programming languages.

  4. 3 Types of SQL Commands • Data Definition Language (DDL): • Commands that define a database - Create, Alter, Drop • Data Manipulation Language (DML) • Commands that maintain and query a database. • Data Control Language (DCL) • Commands that control a database, including administering privileges and committing data.

  5. Data Manipulation Language (DML) Four basic commands: • INSERT • UPDATE • DELETE • SELECT

  6. Inserting Data into a Table INSERT INTO tablename (column-list) VALUES (value-list) PUTS ONE ROW INTO A TABLE INSERT INTO COURSE (COURSE_CODE, COURSE_NAME, CREDIT_HOURS) VALUES (‘MIS499’,’ADVANCED ORACLE’,4);

  7. More on Inserting Data INSERT INTO COURSEVALUES (‘MIS499’,’ADVANCED ORACLE’,4); INSERT INTO COURSE(COURSE_NAME, COURSE_CODE, CREDIT_HOURS)VALUES (’ADVANCED ORACLE’,‘MIS499’,4); COLUMN LIST IS OPTIONAL IF YOU PLAN TO INSERT A VALUE IN EVERY COLUMN AND IN THE SAME ORDER AS IN THE TABLE COLUMN LIST IS NEEDED TO CHANGE THEORDER - MUST MATCH VALUE LIST NOTE - TABLE STILL HAS THE ORIGINAL COLUMN ORDER

  8. Inserting Null Data INSERT INTO COURSE (COURSE_CODE, CREDIT_HOURS) VALUES (‘MIS499’,4); INSERT INTO COURSE VALUES (‘MIS499’,’’,4); INSERT INTO COURSE VALUES (‘MIS499’,NULL,4); COLUMN LIST IS NEEDED IF YOU PLAN TO LEAVE OUT A VALUE IN THE VALUE LIST COLUMN LIST CAN BE OMITTED IF YOU PUT IN A BLANK VALUE THE NULL KEYWORD CAN BE USED TO CREATE A BLANK COLUMN ALL OF THESE ASSUME THAT THE DATABASE ALLOWS THE COLUMN TO BE NULL. YOU CANNOT LEAVE PRIMARY KEYS AND FOREIGN KEYS BLANK

  9. Inserting and Integrity Constraints SQL> INSERT INTO SECTION VALUES ('1234','MIS333','10-12','MW','COPE101','200000000'); INSERT INTO SECTION VALUES ('1234','MIS333','10-12','MW','COPE101', * ERROR at line 1: ORA-02291: integrity constraint (ORA40.SYS_C00337) violated - parent key not found COURSE COURSE_CODE KEY COURSE_NAME CREDIT_HOURS SECTION CALL_NUMBER KEY COURSE_CODE SECTION_TIME SECTION_DAYS SECTION_ROOM INSTRUCTOR_ID INSTRUCTOR INSTRUCTOR_ID KEY INSTRUCTOR_NAME INSTRUCTOR_OFFICE

  10. Entity Integrity Problems SQL> INSERT INTO COURSE VALUES ('MIS220','NEW',4); insert into course values ('MIS220','NEW',4) * ERROR at line 1: ORA-00001: unique constraint (ORA40.SYS_C00335) violated

  11. Deleting Data Be careful!! This deletes ALL of the rows in your table. If you use this command in error, you can use ROLLBACK to undo the changes. DELETE COURSE; DELETES ALL ROWS DELETE COURSE WHERE COURSE_CODE = ‘MIS220’; DELETES SPECIFIC ROWS (MORE TYPICAL) DELETE COURSE WHERE HOURS=4; DELETES A GROUP OF ROWS DELETE COURSE WHERE HOURS<4;

  12. Deleting and Integrity Constraints SQL> DELETE COURSE WHERE COURSE_CODE='MIS220'; DELETE COURSE WHERE COURSE_CODE='MIS220' * ERROR at line 1: ORA-02292: integrity constraint (ORA40.SYS_C00341) violated - child record found

  13. Updating Data UPDATE COURSE SET HOURS=5; CHANGES EVERY ROW UPDATE COURSE SET HOURS=5 WHERE COURSE_CODE=‘MIS220’ CHANGES ONE ROW (MORE TYPICAL) UPDATE COURSE SET HOURS=3 WHERE COURSE_CODE LIKE ‘MIS%’ CHANGES A GROUP OF ROWS

  14. Updating and Integrity Constraints YOU CAN CHANGE THE VALUE OF A FOREIGN KEY AS LONG AS THE NEW VALUE ALSO COMPLIES WITH REFERENTIAL INTEGRITY CONSTRAINTS. PRIMARY KEY VALUES CAN BE UPDATED AS LONG AS THERE ARE NO ROWS IN OTHER TABLES WITH FOREIGN KEYS WITH THE SAME VALUE DOES NOT MATTER IF CONSTRAINT IS RESTRICTED OR CASCADED

  15. Integrity Error SQL> UPDATE COURSE SET COURSE_CODE='MIS221‘ WHERE COURSE_CODE='MIS220'; UPDATE COURSE * ERROR at line 1: ORA-02292: integrity constraint (ORA40.SYS_C00341) violated - child record found

  16. Rollback and Commit CHANGES TO DATA ARE TEMPORARY DURING YOUR SQLPLUS SESSION DOES NOT APPLY TO CHANGES IN DATABASE STRUCTURE - ALTER... BEFORE LEAVING SQLPLUS, YOU CAN REVERSE THEM APPLIES TO INSERTS, UPDATES, AND DELETES

  17. Rollback and Commit • SQL>ROLLBACK; • Rollback complete. • REVERSES ALL CHANGES TO DATA MADE DURING YOUR SESSION • SQL>COMMIT; • MAKES ALL CHANGES TO THIS POINT PERMANENT • POINTS AT WHICH COMMIT IS ISSUED, DEFINE EXTENT OF ROLLBACK • ROLLBACK REVERSES EVERY CHANGE SINCE THE LAST COMMIT • EXITING SQLPLUS ISSUES A COMMIT

  18. SQL for Retrieving Data from One Table SELECT column_name, column_name, … FROM table_name WHERE condition/criteria; • This statement will retrieve the specified field values for all rows in the specified table that meet the specified conditions. • Every SELECT statement returns a recordset.

  19. Conceptual Evaluation Strategy • Semantics of an SQL query defined in terms of the following conceptual evaluation strategy: • Compute the cross-product of relation-list. • Discard resulting tuples if they fail qualifications. • Delete attributes that are not in target-list. • If DISTINCT is specified, eliminate duplicate rows. • This strategy is probably the least efficient way to compute a query! An optimizer will find more efficient strategies to compute the same answers.

  20. WHERE Conditions SELECT * FROM COURSE WHERE COURSE_CODE LIKE ‘MIS%’; SELECT * FROM COURSE WHERE CREDIT HOURS BETWEEN 3 AND 5; SELECT * FROM CUSTOMER WHERE BALANCE < CREDIT_LIMIT; USE % TO SUBSTITUTE FOR ANY STRING 3 AND 5 ARE INCLUDED YOU CAN COMPARE TWO COLUMNS

  21. More WHERE Conditions SELECT * FROM CUSTOMERWHERE STATE IN (‘OH’,’WV’,’KY’); SELECT * FROM CUSTOMERWHERE (CREDIT_LIMIT - BALANCE) <1000; LIST OF SPECIFIC VALUES TO LOOK FOR CAN MANIPULATE NUMBER VALUES MATHMATICALLY

  22. AND/OR/NOT Conditions SELECT * FROM CUSTOMER WHERE BALANCE >=500 AND BALANCE<=1000; SELECT * FROM CUSTOMER WHERE STATE = ‘OH’ OR CREDIT_LIMIT>1000; SELECT * FROM CUSTOMER WHERE NOT (STATE=‘OH’); TWO COMPARISONS ON THE SAME COLUMN TWO COMPARISONS ON THE DIFFERENT COLUMNS SAME AS STATE<>‘OH’

  23. More on AND/OR/NOT SELECT * FROM CUSTOMERWHERE STATE = ‘OH’ OR (CREDIT_LIMIT=1000 AND BALANCE <500); Use parentheses to make complex logic more understandable. CUST STATE LIMIT BAL A OH 1000 600 B WV 1000 200 C OH 500 300 D OH 1000 200 E KY 1300 800 F KY 1000 700 G MA 200 100 H NB 1000 100 Who will be selected??

  24. SQL - Other Features • * - All columns in a table • Aliases • SELECT EmployeeID, LastName, FirstName, BirthDate AS DOB FROM Employee; • SELECT EmployeeID, LastName, FirstName, FROM Employee AS E; • Dot Notation - ambiguous attribute names • SELECT Customer.LName, E.Lname FROM Customer, Employee AS E WHERE ...

  25. SQL - Other Features • DISTINCT • Arithmetic operators: +, -, *, / • Comparison operators: =, >, >=, <, <=, <> • Concatenation operator: || • Substring comparisons: %, _ • BETWEEN • AND, OR

  26. SQL - Other Features • ORDER BY Clause • UNION, EXCEPT, INTERSECT • IN

  27. SQL for Retrieving Data from Two or More Tables SQL provides two ways to retrieve data from related tables: • Join - When two or more tables are joined by a common field. • Subqueries - When one Select command is nested within another command.

  28. SQL - Joins Joins: • The WHERE clause is used to specify the common field. • For every relationship among the tables in the FROM clause, you need one WHERE condition (2 tables - 1 join, 3 tables - 2 joins…) SELECT C.Cust_ID, Comp_Name, Country,OrderID FROM Customer AS C, Order AS O WHERE C.Cust_ID = O.Cust_ID AND Country = ‘USA’;

  29. SQL - Joins • Inner Join - records from two tables are selected only when the records have the same value in the common field that links the tables (the default join). • Outer Join - A join between two tables that returns all the records from one table and, from the second table, only those records in which there is a matching value in the field on which the tables are joined.

  30. Multi-Table Queries & Views Getting tables into 3NF eliminates unnecessary redundancy, BUT now we need data from multiple tables to create some forms and reports. TRANSCRIPT STUDENT#: 444-44-4444 NAME: JOE STUDENT CODE NAME HOURS GRADE QUAR/YR MIS220 FILE PROC 4 A 389 ZOO100 BIOLOGY 3 B 288 PSY280 EXP PSY 4 B+ 190

  31. Two-Table Query DESIRED OUTPUT: CALL-NUMBER 1234 COURSE_CODE MIS380 SECTION_ROOM COPE012 INSTRUCTOR_ID 111111111 INSTRUCTOR_NAME DAY REQUIRED NAVIGATION: SECTION CALL_NUMBER COURSE_CODE SECTION_TIME SECTION_DAYS SECTION_ROOM INSTRUCTOR_ID INSTRUCTOR INSTRUCTOR_ID INSTRUCTOR_NAME INSTRUCTOR_OFFICE JOIN SECTION 1234MIS380 8-10 WF COPE012 111111111 INSTRUCTOR 111111111 DAY COPE290A

  32. SELECT Command with Join SELECT CALL_NUMBER, COURSE_CODE, SECTION_ROOM, SECTION.INSTRUCTOR_ID, INSTRUCTOR_NAME FROM SECTION, INSTRUCTOR WHERE SECTION.INSTRUCTOR_ID = INSTRUCTOR.INSTRUCTOR_ID; Two Tables  One Join THE WHERE CLAUSE IS USED TO TELL ORACLE HOW TO MATCH ROWS BETWEEN THE TWO TABLES – REQUIRES A COMMON KEY FOR COLUMN NAMES WHOSE LOCATION IS AMBIGUOUS, YOU MUST SPECIFY A TABLE NAME - SEE INSTRUCTOR_ID

  33. Results CALL COURS SECTION INSTRU INSTR_NAME ---- -------- -------- ---------- ------------------- 0030 MIS300 COPE112 500000000 SUTHERLAND 0031 MIS300 COPE112 260000000 CHEN 0032 MKT301 COPE633 180000000 KIRCH 0033 MKT301 COPE107 180000000 KIRCH 0034 BUSL255 COPE001 260000000 CHEN 0035 OPN310 COPE107 190000000 CUTRIGHT 0036 OPN310 COPE108 240000000 JDAY

  34. Joining More Than Two Tables STUDENT#: 444-44-4444 NAME: JOE STUDENT CODE NAME HOURS GRADE QUAR/YR MIS220 FILE PROC 4 A 389 BIO100 BIOLOGY 3 B 288 PSY280 EXP PSY 4 B+ 190 TRANSCRIPT STUDENT_NUMBER COURSE_CODE GRADE QUAR_YR STUDENT STUDENT_NUMBER STUDENT_NAME STUDENT_ADDRESS STUDENT_PHONE MAJOR COURSE COURSE_CODE COURSE_NAME CREDIT_HOURS

  35. SELECT Command SELECT STUDENT.STUDENT_NUMBER, STUDENT_NAME, TRANSCRIPT.COURSE_CODE, GRADE, QUAR_YR, COURSE_NAME, CREDIT_HOURS FROM STUDENT, TRANSCRIPT, COURSE WHERE STUDENT.STUDENT_NUMBER =TRANSCRIPT.STUDENT_NUMBER AND TRANSCRIPT.COURSE_CODE =COURSE.COURSE_CODE; Three Tables  Two Joins The number of joins is always one less than the number of tables involved in the query

  36. Results STUD STUDENT_NAME COUR GR QUA COURSE_NAME CREDIT 1121 TRENT RAZEK MIS320 A 1/91 SYSTEMS I 4 1121 TRENT RAZEK MIS420 C 2/92 SYSTEMS II 4 1121 TRENT RAZEK MIS495 B 3/93 MGT INFO SYSTEMS 4 NOTICE HOW YOU GET “LOGICAL” ROWS BACK FROM THE SELECT AS IF THEY CAN FROM A SINGLE TABLE WHEN IN FACT THE DATA COMES FROM THREE SEPARATE TABLES

  37. Using Aliases Aliases for table names can be created in the FROM part of the SELECT statement. Then you can use the alias in place of the full table name when referring to columns in that table. Sometimes this can save you considerable typing!

  38. Alias Example SELECT S.STUDENT_NUMBER, STUDENT_NAME, T.COURSE_CODE, GRADE, QUAR_YR, COURSE_NAME, CREDIT_HOURS FROM STUDENT S, TRANSCRIPT T, COURSE C WHERE S.STUDENT_NUMBER =T.STUDENT_NUMBER AND T.COURSE_CODE=C.COURSE_CODE;

  39. Hints for Successful Joins • Plan your joins • Draw a mini-ERD to show what tables are involved. • Count the number of tables involved in the SELECT query. • The number of joins is always one less than the number of tables in the query. • Watch out for ambiguous column names.

  40. SQL - Aggregate Functions • These functions are applied to a set(s) of records/rows and return one value for each set. • Count (…) • Min (…) • Max (…) • Sum (…) • Avg (…) • These functions thus aggregate the rows to which they are applied.

  41. SQL - Aggregation • If one field in a Select clause is aggregated, all fields in the clause must be aggregated. • Aggregation: The process of transforming data from a detail to a summary level. • You can aggregate a field by including it after the GROUP BY clause or by making it the argument of an aggregating function. SELECT Region, SUM(UnitPrice * Quantity) FROM [Order_Details] GROUP BYRegion;

  42. SQL - Aggregation • When you use GROUP BY, every field in your recordset must be aggregated in some manner. • The same rule applies when you use an aggregating function such as SUM, COUNT, AVERAGE …. If one field in the Select clause is aggregated, then every other field in the Select clause must be aggregated in some manner.

  43. SQL - Aggregation Additional SQL Clause - HAVING: • The HAVING clause is only used after the GROUP BY clause. • The HAVING clause specifies criteria for a GROUP, similar to how the WHERE clause specifies criteria for individual rows.

  44. GROUP BY USED WITH FUNCTIONS FOR SUBTOTALING SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFING GROUP BY INSTRUCTOR_ID; INSTRUCTO SUM(SALARY) --------- ----------- 100000000 5900 200000000 5900 300000000 4500 400000000 4600 500000000 1900 ORIGINAL DATA IN TABLE INSTRUCTOR SALARY 100000000 2500 100000000 3400 200000000 3500 200000000 2400 300000000 4500 400000000 3400 400000000 1200 500000000 1900

  45. GROUP BY Columns SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFING GROUP BY CALL_NUMBER; DOES NOT MAKE SENSE TO DISPLAY SALARY OR CALL_NUMBER ON A GROUP BY INSTRUCTOR_ID BECAUSE THEY VARY DOES MAKE SENSE TO DISPLAY INSTRUCTOR_ID SINCE IT IS THE SAME ACROSS THE GROUP INSTRUCTO CALL SALARY ----------------- ------- ---------- 100000000 0001 2500 100000000 0002 3400 200000000 0003 3500 200000000 0004 2400 300000000 0005 4500 400000000 0006 3400 400000000 0007 1200

  46. GROUP BY and WHERE SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFING WHERE SALARY>2500 GROUP BY INSTRUCTOR_ID; INSTRUCTO SUM(SALARY) ----------------- -------------------- 100000000 6000 200000000 6100 300000000 4500 400000000 3400 INSTRUCTO CALL SALARY --------- ---- ---------- 100000000 0001 2600 100000000 0002 3400 200000000 0003 3500 200000000 0004 2600 300000000 0005 4500 400000000 0006 3400 400000000 0007 1200 WHERE CAN RESTRICT WHICH ROWS ARE PUT INTO THE GROUP

  47. GROUP BY and HAVING SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFING HAVING SUM(SALARY)>4000 GROUP BY INSTRUCTOR_ID INSTRUCTO SUM(SALARY) --------- ----------- 100000000 5900 200000000 5900 300000000 4500 INSTRUCTO CALL SALARY --------- ---- ---------- 100000000 0001 2500 100000000 0002 3400 200000000 0003 3500 200000000 0004 2400 300000000 0005 4500 400000000 0006 2400 400000000 0007 1200 HAVING DETERMINES WHICH GROUPS WILL BE DISPLAYED

  48. SQL statement processing order (adapted from van der Lans, p.100) An intermediate recordset is developed after each clause.

  49. Summary of Select Statements • SELECT - list of attributes and functions • FROM - list of tables • WHERE - conditions / join conditions • GROUP BY - attributes not aggregated in select clause • HAVING - group condition • ORDER BY - list of attributes

  50. SQL – Advanced Topics ISM6217 - Advanced Database

More Related