structured query language – the basics
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/1.jpg)
Structured Query Language – The
Basics
By Hitesh Sahniwww.hiteshsahni.com
![Page 2: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/2.jpg)
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
![Page 3: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/3.jpg)
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.
![Page 4: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/4.jpg)
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.
![Page 5: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/5.jpg)
Data Manipulation Language (DML)Four basic commands: INSERT UPDATE DELETE SELECT
![Page 6: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/6.jpg)
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);
Inserting Data into a Table
![Page 7: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/7.jpg)
INSERT INTO COURSEVALUES (‘MIS499’,’ADVANCED ORACLE’,4);
INSERT INTO COURSE(COURSE_NAME, COURSE_CODE, CREDIT_HOURS)VALUES (’ADVANCED ORACLE’,‘MIS499’,4);
More on Inserting Data
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 NEEDEDTO CHANGE THEORDER - MUST MATCH VALUE LIST
NOTE - TABLE STILL HAS THE ORIGINAL COLUMN ORDER
![Page 8: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/8.jpg)
INSERT INTO COURSE(COURSE_CODE, CREDIT_HOURS)VALUES (‘MIS499’,4);
INSERT INTO COURSEVALUES (‘MIS499’,’’,4);
INSERT INTO COURSEVALUES (‘MIS499’,NULL,4);
COLUMN LIST IS NEEDED IF YOU PLAN TO LEAVE OUT A VALUE IN THE VALUE LIST
COLUMN LIST CAN BE OMITTEDIF YOU PUT IN A BLANK VALUE
THE NULL KEYWORD CANBE USED TO CREATE A BLANKCOLUMN
ALL OF THESE ASSUME THAT THE DATABASE ALLOWS THE COLUMN TOBE NULL. YOU CANNOT LEAVE PRIMARY KEYS AND FOREIGN KEYS BLANK
Inserting Null Data
![Page 9: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/9.jpg)
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 notfound SECTION
CALL_NUMBER KEYCOURSE_CODESECTION_TIMESECTION_DAYSSECTION_ROOMINSTRUCTOR_ID
COURSE
COURSE_CODE KEYCOURSE_NAMECREDIT_HOURS
INSTRUCTOR
INSTRUCTOR_ID KEYINSTRUCTOR_NAMEINSTRUCTOR_OFFICE
Inserting and Integrity Constraints
![Page 10: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/10.jpg)
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
![Page 11: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/11.jpg)
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;
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.
Deleting Data
![Page 12: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/12.jpg)
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 recordfound
Deleting and Integrity Constraints
![Page 13: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/13.jpg)
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=3WHERE COURSE_CODE LIKE ‘MIS%’
CHANGES A GROUP OF ROWS
Updating Data
![Page 14: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/14.jpg)
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
Updating and Integrity Constraints
![Page 15: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/15.jpg)
Integrity ErrorSQL> 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
![Page 16: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/16.jpg)
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
Rollback and Commit
![Page 17: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/17.jpg)
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
Rollback and Commit
![Page 18: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/18.jpg)
SQL for Retrieving Data from One Table SELECT column_name, column_name, …FROM table_nameWHERE 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.
![Page 19: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/19.jpg)
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.
![Page 20: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/20.jpg)
SELECT * FROM COURSE WHERE COURSE_CODE LIKE ‘MIS%’;
SELECT * FROM COURSE WHERE CREDIT HOURS BETWEEN 3 AND 5;
SELECT * FROM CUSTOMERWHERE BALANCE < CREDIT_LIMIT;
USE % TO SUBSTITUTE FORANY STRING
3 AND 5 ARE INCLUDED
YOU CAN COMPARE TWO COLUMNS
WHERE Conditions
![Page 21: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/21.jpg)
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 NUMBERVALUES MATHMATICALLY
![Page 22: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/22.jpg)
SELECT * FROM CUSTOMERWHERE BALANCE >=500 AND BALANCE<=1000;
SELECT * FROM CUSTOMERWHERE STATE = ‘OH’ OR CREDIT_LIMIT>1000;
SELECT * FROM CUSTOMER WHERE NOT (STATE=‘OH’);
TWO COMPARISONSON THE SAME COLUMN
TWO COMPARISONSON THE DIFFERENTCOLUMNS
SAME AS STATE<>‘OH’
AND/OR/NOT Conditions
![Page 23: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/23.jpg)
More on AND/OR/NOT
CUST STATE LIMIT BALA OH 1000 600B WV 1000 200C OH 500 300D OH 1000 200E KY 1300 800F KY 1000 700G MA 200 100H NB 1000 100
Use parentheses to make complex logic more understandable.
SELECT * FROM CUSTOMERWHERE STATE = ‘OH’ OR (CREDIT_LIMIT=1000 AND BALANCE <500);
Who will be selected??
![Page 24: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/24.jpg)
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 ...
![Page 25: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/25.jpg)
SQL - Other Features DISTINCT Arithmetic operators: +, -, *, / Comparison operators: =, >, >=, <, <=, <> Concatenation operator: || Substring comparisons: %, _ BETWEEN AND, OR
![Page 26: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/26.jpg)
SQL - Other Features ORDER BY Clause UNION, EXCEPT, INTERSECT IN
![Page 27: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/27.jpg)
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.
![Page 28: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/28.jpg)
SQL - JoinsJoins: 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,OrderIDFROM Customer AS C, Order AS O WHERE C.Cust_ID = O.Cust_IDAND Country = ‘USA’;
![Page 29: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/29.jpg)
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.
![Page 30: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/30.jpg)
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
Getting tables into 3NF eliminates unnecessary redundancy, BUT now we need data from multiple tables to create some forms and reports.
TRANSCRIPT
Multi-Table Queries & Views
![Page 31: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/31.jpg)
SECTION
CALL_NUMBERCOURSE_CODESECTION_TIMESECTION_DAYSSECTION_ROOMINSTRUCTOR_ID
INSTRUCTOR
INSTRUCTOR_IDINSTRUCTOR_NAMEINSTRUCTOR_OFFICE
DESIRED OUTPUT:CALL-NUMBER 1234COURSE_CODE MIS380SECTION_ROOM COPE012INSTRUCTOR_ID 111111111INSTRUCTOR_NAME DAY
REQUIRED NAVIGATION:
JOIN
SECTION
1234 MIS380 8-10 WF COPE012 111111111
INSTRUCTOR
111111111 DAY COPE290A
Two-Table Query
![Page 32: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/32.jpg)
SELECT CALL_NUMBER, COURSE_CODE, SECTION_ROOM, SECTION.INSTRUCTOR_ID, INSTRUCTOR_NAMEFROM SECTION, INSTRUCTORWHERE SECTION.INSTRUCTOR_ID = INSTRUCTOR.INSTRUCTOR_ID;
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
SELECT Command with Join
Two Tables One Join
![Page 33: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/33.jpg)
ResultsCALL COURS SECTION INSTRU INSTR_NAME---- -------- -------- ---------- -------------------0030 MIS300 COPE112 500000000 SUTHERLAND0031 MIS300 COPE112 260000000 CHEN0032 MKT301 COPE633 180000000 KIRCH0033 MKT301 COPE107 180000000 KIRCH0034 BUSL255 COPE001 260000000 CHEN0035 OPN310 COPE107 190000000 CUTRIGHT0036 OPN310 COPE108 240000000 JDAY
![Page 34: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/34.jpg)
STUDENT#: 444-44-4444
NAME: JOE STUDENT
CODE NAME HOURS GRADEQUAR/YR
MIS220 FILE PROC 4 A 389
BIO100 BIOLOGY 3 B 288
PSY280 EXP PSY 4 B+ 190
STUDENT
STUDENT_NUMBER STUDENT_NAMESTUDENT_ADDRESSSTUDENT_PHONEMAJOR
COURSE
COURSE_CODECOURSE_NAMECREDIT_HOURS
TRANSCRIPT
STUDENT_NUMBERCOURSE_CODEGRADEQUAR_YR
Joining More Than Two Tables
![Page 35: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/35.jpg)
SELECT STUDENT.STUDENT_NUMBER, STUDENT_NAME, TRANSCRIPT.COURSE_CODE, GRADE, QUAR_YR, COURSE_NAME, CREDIT_HOURSFROM STUDENT, TRANSCRIPT, COURSEWHERE STUDENT.STUDENT_NUMBER =TRANSCRIPT.STUDENT_NUMBER AND TRANSCRIPT.COURSE_CODE =COURSE.COURSE_CODE;
Three Tables Two JoinsThe number of joins is always
one less than the number of tables involved in the query
SELECT Command
![Page 36: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/36.jpg)
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
![Page 37: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/37.jpg)
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!
Using Aliases
![Page 38: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/38.jpg)
Alias ExampleSELECT S.STUDENT_NUMBER, STUDENT_NAME, T.COURSE_CODE, GRADE, QUAR_YR, COURSE_NAME, CREDIT_HOURSFROM STUDENT S, TRANSCRIPT T, COURSE CWHERE S.STUDENT_NUMBER =T.STUDENT_NUMBER AND T.COURSE_CODE=C.COURSE_CODE;
![Page 39: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/39.jpg)
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.
![Page 40: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/40.jpg)
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.
![Page 41: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/41.jpg)
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 BY Region;
![Page 42: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/42.jpg)
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.
![Page 43: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/43.jpg)
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.
![Page 44: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/44.jpg)
SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFING GROUP BY INSTRUCTOR_ID;
INSTRUCTO SUM(SALARY)--------- -----------100000000 5900200000000 5900300000000 4500400000000 4600500000000 1900
ORIGINAL DATA IN TABLE
INSTRUCTOR SALARY100000000 2500100000000 3400200000000 3500200000000 2400300000000 4500400000000 3400400000000 1200500000000 1900
USED WITH FUNCTIONS FOR SUBTOTALING
GROUP BY
![Page 45: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/45.jpg)
INSTRUCTO CALL SALARY----------------- ------- ----------100000000 0001 2500100000000 0002 3400
200000000 0003 3500200000000 0004 2400
300000000 0005 4500
400000000 0006 3400400000000 0007 1200
SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFING GROUP BY CALL_NUMBER;
DOES NOT MAKE SENSETO DISPLAY SALARY ORCALL_NUMBER ON A GROUP BY INSTRUCTOR_IDBECAUSE THEY VARY
DOES MAKE SENSE TO DISPLAYINSTRUCTOR_ID SINCE IT ISTHE SAME ACROSS THE GROUP
GROUP BY Columns
![Page 46: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/46.jpg)
SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFINGWHERE SALARY>2500GROUP BY INSTRUCTOR_ID;
INSTRUCTO SUM(SALARY)----------------- --------------------100000000 6000200000000 6100300000000 4500400000000 3400
INSTRUCTO CALL SALARY--------- ---- ----------100000000 0001 2600100000000 0002 3400
200000000 0003 3500200000000 0004 2600
300000000 0005 4500
400000000 0006 3400400000000 0007 1200
WHERE CAN RESTRICTWHICH ROWS ARE PUT INTO THE GROUP
GROUP BY and WHERE
![Page 47: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/47.jpg)
SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFINGHAVING SUM(SALARY)>4000GROUP BY INSTRUCTOR_ID
INSTRUCTO SUM(SALARY)--------- -----------100000000 5900200000000 5900300000000 4500
INSTRUCTO CALL SALARY--------- ---- ----------100000000 0001 2500100000000 0002 3400
200000000 0003 3500200000000 0004 2400
300000000 0005 4500
400000000 0006 2400400000000 0007 1200
HAVING DETERMINESWHICH GROUPS WILL BEDISPLAYED
GROUP BY and HAVING
![Page 48: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/48.jpg)
SQL statement processing order (adapted from van der Lans, p.100)
An intermediate recordset is developed after each clause.
![Page 49: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/49.jpg)
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
![Page 50: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/50.jpg)
SQL – Advanced Topics
ISM6217 - Advanced Database
![Page 51: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/51.jpg)
What We’re Going to Cover Subqueries (Nested queries)
Example Correlated subquery
Join types Inner/outer
Integrity constraints Triggers Functions
![Page 52: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/52.jpg)
Sample Database Scripts to create and populate the database are available
on the 6217 Web site.
SUPPLIER_NO SUPPLIER_NAME SUPPLIER_STATE SUPPLIER_STATE
SUPPLIERS
PART_NO SUPPLIER_NO PRICE DELIVERY
QUOTATIONS
PART_NO Q_ON_ORDER Q_ON_HAND COMPONENT
INVENTORY
![Page 53: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/53.jpg)
Subqueries A subquery is a query that is used in the
WHERE condition of another query AKA Nested query Can be multiple levels of nesting Can be used with SELECT, INSERT, UPDATE
![Page 54: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/54.jpg)
Example 1: By “Hand”**** LIST PARTS W/ > AVERAGE NUMBER OF Q_ON_HAND**** FIRST QUERY: DETERMINE AVERAGESELECT AVG(Q_ON_HAND)FROM INVENTORY; **** SECOND QUERY: PLUG AVERAGE INTO WHERE CLAUSESELECTPART_NO, Q_ON_HAND, DESCRIPTIONFROM INVENTORYWHERE Q_ON_HAND > 50.92ORDER BY PART_NO;
![Page 55: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/55.jpg)
Example 1: Using Subquery
SELECT PART_NO, Q_ON_HAND, DESCRIPTION
FROM INVENTORYWHERE Q_ON_HAND >
(SELECT AVG(Q_ON_HAND) FROM INVENTORY)
ORDER BY PART_NO;
![Page 56: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/56.jpg)
Example 2: Using Join List all suppliers who can deliver at least one
product in less than 10 days
SELECT DISTINCT (S.SUPPLIER_NO), SUPPLIER_NAME
FROM SUPPLIERS S, QUOTATIONS QWHERE S.SUPPLIER_NO = Q.SUPPLIER_NOAND DELIVERY_TIME < 10ORDER BY S.SUPPLIER_NO;
![Page 57: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/57.jpg)
Example 2: Using Subquery
SELECT SUPPLIER_NO, SUPPLIER_NAMEFROM SUPPLIERSWHERE SUPPLIER_NO IN
(SELECT SUPPLIER_NO FROM QUOTATIONS WHERE DELIVERY_TIME < 10)
ORDER BY SUPPLIER_NAME DESC;
![Page 58: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/58.jpg)
Example 3: With Aggregation List all suppliers who can deliver a product in less
than the average delivery time.
SELECT SUPPLIER_NO, SUPPLIER_NAMEFROM SUPPLIERSWHERE SUPPLIER_NO IN
(SELECT SUPPLIER_NO FROM QUOTATIONS WHERE DELIVERY_TIME <
(SELECT AVG(DELIVERY_TIME) FROM QUOTATIONS)
) ORDER BY SUPPLIER_NAME DESC;
![Page 59: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/59.jpg)
Example 4: ANY LIST SUP_NO, PART, DEL FOR QUOTES WHERE
DEL > ANY SUPPLIED BY #71
SELECT SUPPLIER_NO, PART_NO, DELIVERY_TIMEFROM QUOTATIONSWHERE DELIVERY_TIME > ANY
(SELECT DELIVERY_TIME FROM QUOTATIONS WHERE SUPPLIER_NO = 71)
;
![Page 60: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/60.jpg)
Example 5: ALL LIST SUP_NO, PART, DEL FOR QUOTES WHERE
DEL > ALL SUPPLIED BY #71
SELECT SUPPLIER_NO, PART_NO, DELIVERY_TIMEFROM QUOTATIONSWHERE DELIVERY_TIME > ALL
(SELECT DELIVERY_TIME FROM QUOTATIONS WHERE SUPPLIER_NO = 71)
;
![Page 61: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/61.jpg)
Example 6: ANY Who are alternate suppliers for parts supplied by
#71?
SELECT SUPPLIER_NO, PART_NO, DELIVERY_TIMEFROM QUOTATIONSWHERE PART_NO = ANY
(SELECT PART_NO FROM QUOTATIONS WHERE SUPPLIER_NO = 71)
AND SUPPLIER_NO != 71ORDER BY SUPPLIER_NO;
![Page 62: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/62.jpg)
Example 7: NOT EXISTS List all suppliers who have not provided a quote
SELECT * FROM SUPPLIERSWHERE NOT EXISTS
(SELECT * FROM QUOTATIONS WHERE SUPPLIER_NO =
SUPPLIERS.SUPPLIER_NO)ORDER BY SUPPLIER_NO;
![Page 63: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/63.jpg)
Correlated Subqueries A correlated subquery is a subquery that is
evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. These examples show the general syntax of a correlated subquery:
![Page 64: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/64.jpg)
Example 8: Step-by-Step List all suppliers, parts and prices where quoted
price is less than the average quote for that part.
SELECT AVG(PRICE)FROM QUOTATIONSWHERE PART_NO = 321;
SELECT SUPPLIER_NO, PART_NO, PRICEFROM QUOTATIONS QWHERE PRICE < 4 AND PART_NO = 321; SELECT SUPPLIER_NO, PART_NO, PRICEFROM QUOTATIONS QWHERE PART_NO = 321;
![Page 65: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/65.jpg)
Example 8: Correlated Subquery
SELECT SUPPLIER_NO, PART_NO, PRICE
FROM QUOTATIONS QWHERE PRICE <
(SELECT AVG(PRICE)FROM QUOTATIONSWHERE Q.PART_NO = PART_NO)
ORDER BY PART_NO, SUPPLIER_NO;
![Page 66: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/66.jpg)
Join Types Natural join/inner join
This is what you’re used to. Returns only rows where PK and FK values
match. Does not repeat PK/FK columns
Equi-Join Similar to natural join, but includes both PK
and FK values in record set.
![Page 67: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/67.jpg)
Equi-Join Example
SELECT I.PART_NO, Q.PART_NO, SUPPLIER_NO, PRICEFROM INVENTORY I, QUOTATIONS QWHERE I.PART_NO = Q.PART_NOORDER BY I.PART_NO;
![Page 68: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/68.jpg)
More Join Types Outer join
Includes columns with null FK values Problem: Inner join will not return a row that
does not have a matching value. Sometimes this prevents you from getting the output
you want. Example: List all parts (including description)
and any quotes that exist for each part. We want to include all parts even if there are no quotes for some of them.
![Page 69: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/69.jpg)
Solution: Left Outer JoinSELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO, PRICE
FROM INVENTORY I, QUOTATIONS Q
WHERE I.PART_NO = Q.PART_NO (+)
ORDER BY I.PART_NO
;
This is what makes it an outer join.Include all rows from the table away from the (+)
Includes all rows from the left table.
![Page 70: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/70.jpg)
SQL Server VersionSELECT I.PART_NO, DESCRIPTION,
SUPPLIER_NO, PRICEFROM INVENTORY I, QUOTATIONS QWHERE I.PART_NO *= Q.PART_NOORDER BY I.PART_NO
This is what makes it an outer join.Include all rows from the table closest to the * in *=
Includes all rows from the left table.
![Page 71: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/71.jpg)
Non-Solution: Right Outer JoinSELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO, PRICE
FROM INVENTORY I, QUOTATIONS Q
WHERE I.PART_NO (+)= Q.PART_NO
ORDER BY I.PART_NO;
This query does not include all rows from the INVENTORY table. So, it doesn’t work. We could reverse the order of tables in the WHERE condition and the query would be OK
![Page 72: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/72.jpg)
Right-Outer Join: SQL Server
SELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO, PRICEFROM INVENTORY I, QUOTATIONS QWHERE I.PART_NO *= Q.PART_NOORDER BY I.PART_NO
![Page 73: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/73.jpg)
Null Values Field values in a tuple are sometimes unknown (e.g., a
rating has not been assigned) or inapplicable (e.g., no spouse’s name). SQL provides a special value null for such situations.
The presence of null complicates many issues. E.g.: Special operators needed to check if value is/is not null. Is rating>8 true or false when rating is equal to null? What
about AND, OR and NOT connectives? We need a 3-valued logic (true, false and unknown). Meaning of constructs must be defined carefully. (e.g., WHERE
clause eliminates rows that don’t evaluate to true.) New operators (in particular, outer joins) possible/needed.
![Page 74: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/74.jpg)
Integrity Constraints (Review)
An IC describes conditions that every legal instance of a relation must satisfy. Inserts/deletes/updates that violate IC’s are
disallowed. Can be used to ensure application semantics (e.g.,
sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be < 200)
Types of IC’s: Domain constraints, primary key constraints, foreign key constraints, general constraints. Domain constraints: Field values must be of right
type. Always enforced.
![Page 75: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/75.jpg)
General Constraints
Useful when more general ICs than keys are involved.
Can use queries to express constraint.
Constraints can be named.
CREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( rating >= 1
AND rating <= 10 ) CREATE TABLE Reserves
( sname CHAR(10),bid INTEGER,day DATE,PRIMARY KEY (bid,day),CONSTRAINT noInterlakeResCHECK (`Interlake’ <>
( SELECT B.bnameFROM Boats BWHERE B.bid=bid)))
![Page 76: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/76.jpg)
Constraints Over Multiple Relations
CREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( (SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.bid) FROM Boats B) <
100 )
Awkward and wrong!
If Sailors is empty, the number of Boats tuples can be anything!
ASSERTION is the right solution; not associated with either table.
CREATE ASSERTION smallClubCHECK ( (SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
Number of boatsplus number of sailors is < 100
![Page 77: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/77.jpg)
Triggers
Trigger: procedure that starts automatically if specified changes occur to the DBMS
Three parts: Event (activates the trigger) Condition (tests whether the triggers should
run) Action (what happens if the trigger runs)
![Page 78: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/78.jpg)
Triggers: Example (SQL:1999)CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON SAILORSREFERENCING NEW TABLE NewSailorsFOR EACH STATEMENT
INSERTINTO YoungSailors(sid, name, age, rating)SELECT sid, name, age, ratingFROM NewSailors NWHERE N.age <= 18
![Page 79: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/79.jpg)
Some Useful Functions ABS (n) MOD (m,n)
Returns the remainder of m/n POWER (m,n) ROUND(n[,m]) SIGN(n) SQRT TRUNC(15.79,1)
![Page 80: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/80.jpg)
Character Functions CONCAT(char1, char2)
|| LOWER/UPPER LTRIM(char [,set]) RTRIM(char [,set] SUBSTR(char, m [,n]) LENGTH(char)
![Page 81: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/81.jpg)
Date Functions ADD_MONTHS(d,n) LAST_DAY(d) MONTHS_BETWEEN(d1, d2) ROUND(d[,fmt]) SYSDATE TO_CHAR(d [, fmt [, 'nlsparams'] ]) TO_DATE(char [, fmt [, 'nlsparams'] ])
![Page 82: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/82.jpg)
Data Dictionary Tables DICTIONARY
All tables and views that are available to the user This table contains several hundred rows
Useful Data Dictionary Views Use just like a table More useful (generally) than full tables Use DESCRIBE to see the columns in the view USER_TABLES USER_VIEWS USER_CONSTRAINTS USER_OBJECTS
![Page 83: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/83.jpg)
Visit Me AT
www.hiteshsahni.com
![Page 84: Structured Query Language – The Basics](https://reader036.vdocuments.us/reader036/viewer/2022062422/568141d8550346895dadb142/html5/thumbnails/84.jpg)