1 / 92

Structured Query Language (SQL)

Structured Query Language (SQL). SQL. Non-Procedural Structured Query Language.(4GL) Data Sub language. Developed by IBM in the late 1970’s. In 1986, the ANSI made SQL as standard for all RDBMS. SQL does not support any programming language constructs like if…else OR do…while.

ralph
Download Presentation

Structured Query Language (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. Structured Query Language(SQL)

  2. SQL • Non-Procedural Structured Query Language.(4GL) • Data Sub language. • Developed by IBM in the late 1970’s. • In 1986, the ANSI made SQL as standard for all RDBMS. • SQL does not support any programming language constructs like if…else OR do…while. • SQL can be embedded in other programming languages like C, COBOL. Ltitl/Trng/Sql/V1.0

  3. Features of SQL • SQL is a non-procedural language. • SQL is used for all types of Database activities by all ranges of users including, • System administrators. • Database administrators. • Application Programmers • End users. Ltitl/Trng/Sql/V1.0

  4. DBMS LANGUAGES • Data Definition Language (DDL) • Creating & Altering the structure of the database. • Data Manipulation Language (DML) • Insert, Update & Delete. • Data Control Language (DCL) • Controlling access to the database(Grant,Revoke) Ltitl/Trng/Sql/V1.0

  5. Transaction Control… COMMIT, ROLLBACK • Data Retrieval… SELECT Ltitl/Trng/Sql/V1.0

  6. DATA TYPES SUPPORTED IN SQL Ltitl/Trng/Sql/V1.0

  7. Tables used : Ltitl/Trng/Sql/V1.0

  8. Data Retrieval Using SQL*PLUS • SQL*PLUS provides a query capability in the form of SELECT statement. • One can view the current information in the tables by using this statement. Ltitl/Trng/Sql/V1.0

  9. 5 parts of basic SQL query • SELECT – list the columns you want • … (more than one table, use table.field) • FROM – list of tables used • WHERE – selection/join criteria (opt) • GROUP BY – how to summarize (opt) • ORDER BY – sorting order (opt) Ltitl/Trng/Sql/V1.0

  10. SELECT Command • Displaying some OR all the columns from a table. SELECT <col1, col2, … .> OR <*> FROM <table_name> ; Ltitl/Trng/Sql/V1.0

  11. List the Department table. 1. SELECT * FROM Dept ; Ltitl/Trng/Sql/V1.0

  12. A Better Way! 2. List the Department table. List the Department table SELECT Deptcode, Deptname, Deptmanager, Deptbudget FROM Dept ; Ltitl/Trng/Sql/V1.0

  13. Only Some Columns 3. List all department managers with the names of their department SELECT Deptmanager, Deptname FROM Dept ; Ltitl/Trng/Sql/V1.0

  14. WHERE Predicate ( = ) 4. List all employees of the Accounts department. SELECT Empname, Deptcode FROM Emp WHERE Deptcode = ‘Acct’ ; Ltitl/Trng/Sql/V1.0

  15. WHERE Predicate ( < ) 5. List all officers. SELECT Empname, Gradecode FROM Emp WHERE Gradecode < 10 ; Ltitl/Trng/Sql/V1.0

  16. Date Comparison 6. List all young employees. SELECT Empname, Birthdate FROM Emp WHERE Birthdate > ‘01-Jan-70’ ; Ltitl/Trng/Sql/V1.0

  17. Eliminating Redundant Data • DISTINCT is an argument that provides a way for you to eliminate duplicate values. e.g. SELECT DISTINCT deptcode FROM emp ; Ltitl/Trng/Sql/V1.0

  18. Use of Boolean operators 1. SELECT * FROM emp WHERE Basicpay >= 2500 AND Basicpay <= 4500; 2. SELECT * FROM emp WHERE Desigcode = ‘Engg’ OR Basicpay > 3000 ; 3. SELECT empname FROM emp WHERE NOT deptcode = ‘Acct’ ; Ltitl/Trng/Sql/V1.0

  19. In Operator • IN operator can be used to select rows that match one of the values included in the list. 1. SELECT empname FROM emp WHERE deptcode IN(‘Acct’, ‘Pers’) ; 2. SELECT empname FROM emp WHERE deptcode NOT IN(‘Acct’, ‘Pers’) ; Ltitl/Trng/Sql/V1.0

  20. Between construct • Defines a range that values must fall into make the predicate true. List all middle level staff List all middle level staff SELECT Empname, Gradecode FROM Emp WHERE Gradecode BETWEEN 10 AND 15; Ltitl/Trng/Sql/V1.0

  21. Like construct • Used for pattern searching. • Pattern consists of characters to be matched & the wildcard’ characters. • WildCard chars. Matches • _ (under score) Any single character. • % Any sequence of zero or more characters. Ltitl/Trng/Sql/V1.0

  22. Like construct List all employees with UMA in their names SELECT Empname FROM Emp WHERE Upper(Empname) LIKE ‘%UMA%’ ; Ltitl/Trng/Sql/V1.0

  23. Null values List the employees who have not been assigned to any supervisor SELECT Empname, Supcode FROM Emp WHERE Supcode IS NULL ; Ltitl/Trng/Sql/V1.0

  24. Compound Predicate - Interval List the female employees who have just completed 5 years SELECT Empname, Sex, Joindate FROM Emp WHERE Sex = “F” AND Joindate BETWEEN(sysdate - 5*365) AND (sysdate - 6*365) ; Ltitl/Trng/Sql/V1.0

  25. Predicate using OR List employees who are either 50 years or more or have more than 20 years experience. SELECT EmpName FROM Emp WHERE BirthDate < (sysdate - 50*365) OR JoinDate < (sysdate - 20*365) ; Ltitl/Trng/Sql/V1.0

  26. Parentheses in Predicate List foremen who are either 50 years or more or have more than 20 years experience. SELECT EmpName FROM Emp WHERE DesigCode = ‘FRMN’ AND ((BirthDate < (sysdate - 50*365) OR JoinDate < (sysdate - 20*365)); Ltitl/Trng/Sql/V1.0

  27. Expressions in SELECT List List 1% of take-home pay of all employees. SELECT Empcode, (Basic + Allow -Deduct ) * 0.01 FROM Salary WHERE SalMonth = ‘1-Mar-97’; Ltitl/Trng/Sql/V1.0

  28. EMPCODE (BASIC+ALLOW-DEDUCT) * 0.01 -------------- -------------------------------------------- 7129 440 7233 440 7345 143 7369 66 7844 198 7876 44 7900 55 7902 330 7934 77 17 rows selected. Ltitl/Trng/Sql/V1.0

  29. Aliasing List the present age of all the employees. SELECT EmpName, TRUNC(SYSDATE - BirthDate) / 365) AGE FROM Emp ; Ltitl/Trng/Sql/V1.0

  30. Ltitl/Trng/Sql/V1.0

  31. Order By • For getting the resultant rows in a specified order. • Ascending order is default. List all employees ordered by age SELECT EmpName, TRUNC(SYSDATE - BirthDate)/365 AGE FROM Emp ORDER BY BirthDate ; Ltitl/Trng/Sql/V1.0

  32. Ltitl/Trng/Sql/V1.0

  33. Sorting Ascending / Descending List middle level staff according to seniority List middle level staff according to seniority SELECT EmpName, GradeCode, GradeLevel FROM Emp WHERE GradeCode BETWEEN 10 AND 25 ORDER BY GradeCode, GradeLevel DESC ; Ltitl/Trng/Sql/V1.0

  34. Ltitl/Trng/Sql/V1.0

  35. Sorting by Result of Expression List middle level staff according to seniority List all according to age and seniority SELECT EmpName, TRUNC(SYSDATE - BirthDate/365) AGE, GradeCode, GradeLevel FROM Emp ORDER BY 2 DESC, GradeCode, GradeLevel DESC ; Ltitl/Trng/Sql/V1.0

  36. Ltitl/Trng/Sql/V1.0

  37. AGGREGATE Functions • Act on a group or set of rows and return one row of summary information per set. • SUM Computes the total value of the group. • AVG Computes the average value of the group. • MIN Computes the minimum value of the group. • MAX Computes the maximum value of the group • COUNT Counts the no. of NON-NULL values for the specified group. • COUNT(*) Counts the no. of rows including those having NULLvalues for the given condition. Ltitl/Trng/Sql/V1.0

  38. AGGREGATE Functions Count employees reporting to Singh. SELECT COUNT(*) FROM Emp WHERE Supcode = ‘7844’ ; Ltitl/Trng/Sql/V1.0

  39. GROUP BY - Aggregate Functions • The grouping of rows is achieved by using the GROUP BY clause. • Allows you to define a subset of the values in a particular field in terms of another field, and apply an aggregate functions to the subset. • Enables you to combine fields and aggregate functions in a single SELECT statement Ltitl/Trng/Sql/V1.0

  40. GROUP BY - Aggregate Functions List the number of staff reporting to each supervisor. SELECT Supcode, COUNT(*) FROM Emp GROUP BY Supcode ORDER BY Supcode ; Ltitl/Trng/Sql/V1.0

  41. GROUP BY - Sum List the total take-home pay during 96-97 for all employees. SELECT EmpCode, SUM(Basic + Allow - Deduct) PAY FROM Salary WHERE SalMonth BETWEEN ‘1-Apr-96’ AND ‘31-Mar-97’ GROUP BY EmpCode ORDER BY EmpCode ; Ltitl/Trng/Sql/V1.0

  42. Ltitl/Trng/Sql/V1.0

  43. GROUP BY - Max & Min List the maximum & minimum salaries in grades. SELECT GradeCode, MAX(Basic), MIN(Basic) FROM Grade GROUP BY GradeCode ORDER BY GradeCode ; Ltitl/Trng/Sql/V1.0

  44. Ltitl/Trng/Sql/V1.0

  45. HAVING • Defines criteria used to eliminate certain groups from the output, just as the WHERE clause does for individual rows. • HAVING can take only arguments that have a single value per output group. Ltitl/Trng/Sql/V1.0

  46. HAVING List the number of staff reporting to each supervisor having more than 3 people working under them. SELECT SupCode, COUNT(*) FROM Emp GROUP BY SupCode HAVING COUNT(*) > 3 ORDER BY SupCode ; Ltitl/Trng/Sql/V1.0

  47. Where - Group By - Having List the total take-home pay during 96-97 for all employees getting a total take-home-pay < Rs. 20000 SELECT EmpCode, SUM(Basic + Allow - Deduct) PAY FROM Salary WHERE SalMonth BETWEEN ‘1-Apr-96’ AND ‘31-Mar-97’ GROUP BY EmpCode HAVING SUM(Basic + Allow - Deduct) ORDER BY EmpCode ; Ltitl/Trng/Sql/V1.0

  48. Where - Group By - Having List the maximum and minimum basic salary in each grade for grades with start < Rs. 4000 SELECT GradeCode, MAX(Basic), MIN(Basic) FROM Grade GROUP BY GradeCode HAVING MIN(Basic) < 4000 ORDER BY GradeCode ; Ltitl/Trng/Sql/V1.0

  49. Joining Table • The process of forming rows from two or more tables by comparing the contents of related cloumns is called ‘Joining Tables’. • Joins are the foundation of multi-table query processing in SQL. Syntax : SELECT <col1,col2,...> FROM <table_name> WHERE <logical expr.> ; Ltitl/Trng/Sql/V1.0

  50. Joining Table - Types of Joins • Equi-Join • Cartesian Join • Self Join • Outer Join Ltitl/Trng/Sql/V1.0

More Related