1 / 115

SQL Training

SQL Training. Sumali Conlon Associate Professor of MIS University of Mississippi University, MS 38677 sconlon@bus.olemiss.edu Phone: 662-915-5470. Outline. General Relational Database concepts (very briefly) Database Design Theory and Methodology (briefly) SQL

jamar
Download Presentation

SQL Training

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 Training Sumali Conlon Associate Professor of MIS University of Mississippi University, MS 38677 sconlon@bus.olemiss.edu Phone: 662-915-5470 SQL-Training

  2. Outline General Relational Database concepts (very briefly) Database Design Theory and Methodology (briefly) SQL Table, View, Store Procedures, Triggers, DML, DDL, Indexes Insert, Update, Delete Retrieving Data – a lot of these Simple SQL, Joins (inner, outer), Nested SQL, etc. Union, minus, division, functions (max, min, count, sum, etc.) Common T-SQL functions Getdate, Convert, Dateadd, datepart, datedif, left, right Common Table Expressions (CTE) Performance – SQL tuning Practical Information -Saving to CSV, shortcuts, etc. SQL-Training

  3. To start • Start SQL Server Management Studio • Add a new database • Restore the database SQL-Training

  4. SQL-Training

  5. SQL-Training

  6. SQL-Training

  7. Relational Data Models, Relational Constraints, and Relational Algebra SQL-Training • Flat file: A two dimensional array of attributes or data items ProductX 1 Bellaire 5 ProductY 2 Sugarland 5 ProductZ 3 Houston 5 Computerization 10 Stafford 4 Reorganization 20 Houston 1 Newbenefits 30 Stafford 4

  8. SQL-Training

  9. SQL-Training Attribute: A name characteristic or property of an entity = column header Entity: A “thing” in the real world with an independent existence physical existence: person, student, car

  10. Domain - The valid set of atomic value for an attribute in a relation e.g. SSN set of 9 digits GPA: 0<= GPA <= 4.0 Atomic - each value in the domain is indivisible Name (Fname, Minit, Lname) – not atomic Fname -- atomic Minit -- atomic Lname -- atomic SQL-Training

  11. Definition Summary SQL-Training

  12. Candidate key: a super key such that no proper subset of its attributes is itself a super key. So candidate keys must have a minimal identifier.STUIDSSNPrimary key: the candidate key that is chosenOR the candidate key that is used to identify tuples in a relation-- unique, must exist SQL-Training

  13. Concatenated (composite) key: A primary key that is comprised of two or more attributes or data itemsG RADE_REPORT(STUID, COURSE#, GRADE)  SQL-Training

  14. Foreign key: A non-key attribute in one relation that appears as the primary key (or part of the key) in another relationEMPLOYEE(SSN, FNAME, MINIT, DNO)DEPARTMENT(DNUMBER, DNAME, MANAGER) SQL-Training

  15. SQL-Training

  16. Referential Integrity Constraints for COMPANY database SQL-Training

  17. SQL-Training

  18. Relational Algebra Overview • Relational algebra is the basic set of operations for the relational model • These operations enable a user to specify basic retrieval requests (or queries) • The result of an operation is a new relation, which may have been formed from one or more input relations • This property makes the algebra “closed” (all objects in relational algebra are relations) SQL-Training

  19. Data Definition Language or Data Description Language (DDL) • DDL – Define data structure CREATETABLE employees ( id INTEGERPRIMARYKEY, first_nameCHAR(50) NULL, last_nameCHAR(75) NOTNULL, dateofbirthDATENULL); • DROPTABLE employees; • ALTERTABLEemployee ADDemail Varchar(30) • ALTERTABLEemployee DROPCOLUMNemail; SQL-Training

  20. Data Definition Language (DDL) Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands SQL-Training

  21. Data Manipulation Language (DML) Statements (Transact-SQL) • DML - statements are used for managing data within schema objects • SELECT - retrieve data from the a database • INSERT - insert data into a table • UPDATE - updates existing data within a table • DELETE - deletes all records from a table, the space for the records remain • MERGE - UPSERT operation (insert or update) • CALL - call a PL/SQL or Java subprogram • EXPLAIN PLAN - explain access path to data • LOCK TABLE - control concurrency • http://msdn.microsoft.com/en-us/library/ms177591(v=sql.90).aspx SQL-Training

  22. VIEW • a view consists of a stored query accessible as a virtual table composed of the result set of a query create view V_staff_dno5 as select * from staff where dno = 5; Benefits: • Restrict access to specific rows and columns in a table • Join columns from multiple tables and present them as though they are part of a single table • Present aggregate information (such as the results of the COUNT function) SQL-Training

  23. Index •  an index in a database • lets you quickly find specific information in a table or indexed view. • An index contains keys built from one or more columns in the table, or view, and pointers that map to the storage location of the specified data. • How to: Create a Spatial Index (SQL Server Management Studio) • http://msdn.microsoft.com/en-us/library/bb964714.aspx SQL-Training

  24. Relational Algebra Overview • Relational Algebra consists of several groups of operations • Unary Relational Operations • SELECT (symbol:  (sigma)) • PROJECT (symbol:  (pi)) • Relational Algebra Operations From Set Theory • UNION ( ), INTERSECTION ( ), DIFFERENCE (or MINUS, – ) • CARTESIAN PRODUCT ( x ) • Binary Relational Operations • JOIN (several variations of JOIN exist) • DIVISION • Additional Relational Operations • OUTER JOINS, OUTER UNION • AGGREGATE FUNCTIONS (These compute summary of information: for example, SUM, COUNT, AVG, MIN, MAX) SQL-Training

  25. Unary Relational Operations: SELECT • The SELECT operation (denoted by  (sigma)) is used to select a subset of the tuples from a relation based on a selection condition. • The selection condition acts as a filter • Keeps only those tuples that satisfy the qualifying condition • Tuples satisfying the condition are selected whereas the other tuples are discarded (filtered out) • Examples: • Select the EMPLOYEE tuples whose department number is 4: DNO = 4 (EMPLOYEE) • Select the employee tuples whose salary is greater than $30,000: SALARY > 30,000(EMPLOYEE) SQL-Training

  26. Select Works on single table and takes rows that meet a specified condition, copy them into a new table • (Table name) Condition(s) SQL (Structured Query language) SELECT * FROM (table name) WHERE condition 1 AND condition 2 AND condition 3… SQL-Training

  27. Find employees who work for department number 5.  employee DNO = 5 SQL: SELECT * FROM employee WHERE dno = 5; SQL-Training

  28. Project Operates on a single table, produces a vertical subset of the table, extract the values of specified columns eliminate duplicate rows place the value in a new table  (table name) column1, column2, column3, … SQL-Training

  29. SQL: SELECT column1, column2, column3, … FROM (table name) Sql Server – Select with (nolock) NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table. SQL-Training

  30. E.g. Show the names of all employees employeefname, minit, lname SELECT fname, minit, lname FROM employee; SQL-Training

  31. Select & project Show the names of all employees who work for department number 5  ( employee) fname, minit, lnamedno = 5 SELECT fname, minit, lname FROM employee WHERE dno = 5; SQL-Training

  32. fname,minit,lname  DNO = 5 Employee SQL-Training

  33. Examples of applying SELECT and PROJECT operations SQL-Training

  34. Example of Query Tree SQL-Training

  35. Equijoin SQL-Training

  36. Query Tree for Equijoin X ID = STUID Credit_Hours Student SQL-Training

  37. Natural Join |X| Is an equijoin which the repeated column is eliminated Usually join performs over column with the same names SQL-Training

  38. Remove this column SQL-Training

  39. SQL-Training

  40. Query Tree for Natural Join |X| Credit_Hours Student SQL-Training

  41. Semi-join: If R1 and R2 are tables Semijoin of R1 and R2 is natural join of R1 and R2 and then projecting the result into the attributes of A Semijoin is not cumulative SQL-Training

  42. Create tables create table student1 (id char(3) primary key, fnamechar(10), lname char(10)); insert into student1 values(‘101’,’Jim’,’Smith’); insert into student1 values(‘102’,’Tim’,’Brown’); insert into student1 values(‘103’,’Babara’,’Houston’);  ----------------- ---- create table credit_hours (stuid char(3) primary key, hours number(3)); insert into credit_hoursvalues(101,60); insert into credit_hoursvalues(102,85); SQL-Training

  43. Left Semi-Join SQL-Training

  44. Right Semi-Join SQL-Training

  45. Outer Join: Is an extension of a THETA JOIN, an EQUIJOIN, or a NATURAL JOIN An outer join consists of all rows that appear in the usual theta join, plus an additional row for each of the tuples from the original tables that do not participate in the theta join. In those rows that are unmatched original tuples, extend it by assigning null values to the other attributes. SQL-Training

  46. Left outer join unmatched rows from the first (left) table appear in the resulting table • Right outer join unmatched rows from the second (right) table appear in the resulting table SQL-Training

  47. Left Outer Join Right Outer Join SQL-Training

  48. SQL Server Outer Joins • LEFT OUTER JOIN or LEFT JOIN • RIGHT OUTER JOIN or RIGHT JOIN • FULL OUTER JOIN or FULL JOIN SQL-Training

  49. Outer Join -- Oracle Left-outer join select * from student, credit_hours where id = stuid(+); SELECT E.FNAME, E.LNAME, dependent_name FROM EMPLOYEE E, DEPENDENT D WHERE E.SSN = D.ESSN(+); SQL-Training

  50. Join select fname, lname,s_id, essn, pno from staff S with (nolock) join workson W with (nolock) on S.s_id = W.essn where dno = 3; fnamelnamepno Tom Delay 2 Tom Delay 3 Tom Delay 4 Tom Delay 5 Larry Brown 3 Jane English 1 Jane English 2 Joe Hugh 1 Joe Hugh 2 SQL-Training

More Related