1 / 121

Basic SQL

Basic SQL. SMSU Computer Services Short Course. Contact Information. Greg Snider – MIS Database Analyst Ext. 6-4397 Email – SGS345B Don’t hesitate to email or call if you have any questions after you start using QM and SQL. Confidentiality.

konala
Download Presentation

Basic 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. Basic SQL SMSU Computer Services Short Course

  2. Contact Information • Greg Snider – MIS Database Analyst • Ext. 6-4397 • Email – SGS345B Don’t hesitate to email or call if you have any questions after you start using QM and SQL.

  3. Confidentiality • “The confidentiality of student information is protected by the Family Education Rights and Privacy Act (FERPA). Do not release confidential information obtained through QM reports to anyone except SMSU faculty or staff who have a need for the information and be sure to properly dispose of reports when you no longer have need for them.”

  4. Contents • Concepts • What is SQL? • Terminology • SQL Language • Its parts • SELECT Statement • Query Manager

  5. What is SQL? • S(trucured) Q(uery) L(anguage) • A standard language for accessing data • Designed to be portable • Used by most database vendors • It’s how you access data stored in a …

  6. Terminology • Table • A set of rows with columns containing data • Think of a table as a large spreadsheet

  7. Terminology Hot and Cold Running Tables • Cold or Query Table is refreshed nightly or as predetermined. • Hot or Live Table can be updated as you write and run your queries. • At this point, all the tables you use in your queries are cold tables.

  8. Terminology • View • Another way of accessing the data in a table • May or may not contain all the columns in a table • Can be a join of two or more tables • Transparent to the user

  9. SQL Language • Language elements • Statements • Functions • Joins • Unions • Subselects

  10. Language Elements • Data types • Constants • Expressions • Predicates

  11. Data Types • Character strings • Datetime • Date • Time • Numeric • Integer • Decimal • Numeric

  12. Character Strings • Fixed length • 1 – 254 positions

  13. Date • Date • Format MM/DD/YYYY • 10 positions • When using a date, it must be enclosed in single quotes, ’09/16/2004’

  14. Numeric • Integer • Small • 32768 - +32767 • Large • -2147483648 - +2147483647 • Decimal • 15 digits max • (Precision, Scale) • Precision – how many digits total • Scale – how many digits to the right of the decimal point • Numeric • 31 digits max

  15. Constants • Integer constants • 456, -789 • Decimal constants • 978.34, 9584.2746 • String (character) constants • ‘ABCE’, ‘Computers for Learning’

  16. Expressions • Operators • || or CONCAT, /, *, +, - • || only for strings • Standard rules apply for arithmetic operations

  17. Date Arithmetic • Admit_date + 2 months + 2 days is valid • Grad_date – Admit_date is valid

  18. Predicates • =, <>, <, >, <=, >= • Between: expression (NOT) BETWEEN 123 and 999 • Null: expression IS (NOT) NULL • Like: expression (NOT) LIKE pattern • Pattern % represents 0 or more characters _ represents only 1 character • Exists discussed when we talk about subselects • In expression IN (value1, value2, value3, …) • AND and OR may be used

  19. Statements • Select

  20. Select • Select clause • From clause • Where clause • Group by clause • Having clause • Order by clause

  21. Select Clause • SELECT columns, expressions • Tells what you want to see

  22. From Clause • FROM datacoll.view-name • Datacoll is the owner of all our views

  23. Where Clause • WHERE search-condition

  24. Group By Clause • GROUP BY column1, column2, …

  25. Having Clause • HAVING search-condition • Each column used in the search must: • Unambiguously identify a grouping column or • Be specified with a column function

  26. Examples • Example 1: Show all rows and columns of the table datacoll.classes • Example 2: Show the job code, maximum salary and minimum salary for each group of rows in EMP with the same job code, but only for groups with more than 1 row and with a maximum salary greater than 50000

  27. Examples • Example 1: • SELECT * FROM DATACOLL.CLASSES • Example 2: • SELECT JOB, MAX(SALARY), MIN(SALARY) FROM EMP GROUP BY JOB HAVING COUNT(*) > 1 AND MAX(SALARY) > 500000

  28. How would you use SQL in your job?

  29. Column Functions • AVG • COUNT • MAX • MIN • SUM • On all column functions, you can use DISTINCT to remove duplicates • On COUNT, DISTINCT also removes null values

  30. Scalar Functions 1 • CHAR(expression) or CHAR(expression,USA) • The first form returns the character representation of a number • The second returns the character represention of a date or time • DATE(expression) • If the expression is a number <= 3652059, the result is the date that is n-1 days after 01/01/0001 • If the expression is a character string with length 7 in yyyyddd format, the result is the date represented by the string • If the expression is any other character string, it must be in valid date format (’01/01/2005’)

  31. Scalar Functions 2 • DAY(expression) • if the expression is a date, the result is the day part of the value • DAYS(expression) • The expression must be a date or a valid string of a date • The result is 1 more that the number of days from 01/01/0001 to the expression

  32. Scalar Functions 3 • DECIMAL(expression, integer, integer) • 2nd and 3rd arguments are for precision and scale • If 3rd is omitted, default is 0 • If 2nd is omitted, precision is 15

  33. Scalar Functions 4 • HOUR(expression) • INTEGER(expression) • MINUTE(expression) • MONTH(expression) • SECOND(expression

  34. Scalar Functions 5 • SUBSTR(string,start,length) • Lentgh may be omitted. If it is, the default is the length of the string – start + 1 • TIME(expression)

  35. Joins 1 • Joins are the combined data from 2 or more tables • Specify more than 1 table in the FROM clause, seperated by a comma • Specify a search condition for the join in the WHERE clause; otherwise, you get all possible combinations of rows for the tables in the FROM clause • In this case, the number of rows return is the product of the number of rows in each table

  36. Joins 2 -- Intersections or Differences • Intersections • Difference

  37. Joins 3 - Intersection • Identify the juniors who have a foreign language major and the classes they are taking this fall Classes Table Classes Student Table

  38. Joins 4 -Intersection Example • Select Name, Course_Code, Course_No, Section_No, Credit_Hours • From Tstudent S, Tclasses C • Where First_major_curr like ‘FL%’ • and Sem = ‘4’ • and Year = ‘93’ • and S.Soc_Sec = C.Soc_Sec • Correlation names • Defined in the FROM clause • Used to designate table names

  39. Joins 4 • Example: • Select name, stu_class, crs_cd, crs_num from datacoll.students s, datacoll.classes c where s.soc_sec = c.soc_sec

  40. Unions 1 • Merging results from 2 or more queries • Identify Sr and JR in FL Student Table Classes Table

  41. Union 2 - Example • Select Name, Adviser_Curr, Course_code, Comb_Grade_Pts, Course_no, Section_no, ’1’ • From Tstudent S, Tclasses C • Where S.Soc_Sec = C.Soc_Sec and Sem = ‘4’ and Year = ‘93’ and First_major_curr like ‘FL%’ and class_curr = ‘SR’ • Union

  42. Union 3 - Example cont • Select Name, Adviser_curr, Course_code, Comb_Grade_Pts, Course_no, Section_no, ‘2’ • From Tstudent S, Tclasses C • Where S. Soc_sec = C.Soc_sec and Sem = ‘4’ and Year = ‘93’ and Class_curr = ‘JR’ and First_major_curr like ‘FL%’ • Order by 7,1

  43. Union 4 - Example 2 • List the names of all students who are either advised by advisor E333 or are juniors. Advised by E333 Final Report Juniors

  44. Union and Union All - 5 • Union All--In the previous example, if students were both Juniors and advised by E333, they would be on the final report two times. • Union -- Sorts and removes duplicates • Union All -- does not eliminate duplicate rows from the report

  45. Unions 6 - Rules • Select -- any number of columns can be selected • Each Select must produce similar results • same number of columns • by position, same general type, ie... • Char--char--dec Dec--char-char --NO • Char--char--dec Char--char--dec --Yes

  46. Unions 6 - Rules continued • You may use any combination of Union and Union All • Efficient to use union all on all but the last UNION statement (Sort only once) • ORDER BY statement must follow all SELECTs and reference only column positions, not names

  47. Combining Union and Union All S o r t I n t e r n a l a r e a Final Report Query 1 Final Report Union All Query 2 Union All Query 3 Union Query 4

  48. Unions 1 • SELECT stmt UNION (ALL) SELECT stmt UNION (ALL) … • UNION without the ALL option causes duplicate rows to be eliminated • UNION ALL causes all rows from all SELECT stmts to be returned • Same number of columns must be returned by all SELECT stmts • The corresponding columns in all SELECT stmts must have the same compatable data types

  49. Subselects select empno, actno, emstdate, emendate from empprojact where empno in (select empno from emp where workdept = ‘E11’ select workdept, max(salary) from emp group by workdept having max(salary) > (select avg(salary) from emp select workdept, max(salary) grom emp q group by workdept having max(salary) < (select avg(salary) from emp where not workdept = q.workdept)

  50. Lunch Break • Class resumes at 1:00

More Related