1 / 50

STRUCTURED QUERY LANGUAGE

STRUCTURED QUERY LANGUAGE. Chandra S. Amaravadi. IN THIS PRESENTATION. Codd’s rules for relational systems Types of SQL DDL and DML Examples. CODD’S RULES AND TYPES OF SQL. CODD’S RULES FOR RDBMSs. Codd has written a paper in which he outlined the rules for

talasi
Télécharger la présentation

STRUCTURED QUERY LANGUAGE

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 Chandra S. Amaravadi

  2. IN THIS PRESENTATION • Codd’s rules for relational systems • Types of SQL • DDL and DML • Examples

  3. CODD’S RULES AND TYPES OF SQL

  4. CODD’S RULES FOR RDBMSs Codd has written a paper in which he outlined the rules for relational systems. These are as follows: • Information representation • Guaranteed access • Dynamic on-line catalog • Comprehensive data sub-language • View updating Note: Codd was a research fellow at IBM in the ’70s

  5. CODD’S RULES FOR RDBMSs.. • High-level insert, update and delete • Physical data independence • Logical data independence • Integrity independence • Distribution independence • Non-subversion

  6. CODD’S RULES FOR RDBMSs • Information representation -- all information should be represented as atomic values in tables. • Guaranteed access -- given a row, column and table name we should be able to access values in the table. • Online catalog -- the system catalog (data dictionary) should be online and accessible by the system. • Comp. data lang. -- there should be a language for data definition and data manipulation. • View updating -- the system must be able to automatically update views based on a base table.

  7. CODD’S RULES FOR RDBMSs • High level insertion, update.. -- insertion, deletion and update should operate on a table. • Physical data independence -- should be able to change internal storage structures without affecting application programs. • Logical data independence -- should be able to change logical scheama without affecting application programs. • Integrity independence -- integrity controls must be independent of appln. prog. • Distribution independence -- the users/appln. programs should not be affected by where the data is physically stored. • Non subversion -- should not be able to bypass integrity rules by using the data sub language.

  8. STRUCTURED QUERY LANGUAGE Structured Query Language originated from SEQEL (early 1980’s). SQL (is/was): • based on relational calculus (uses select, project, join etc.) • standardized in ’82,’95, ‘05 • Embedded SQL a standard in ‘89 • Two major types and three other types • DDL, DML • SQL/T, SQL/I, SQL/AU (DCL) • Embedded SQL

  9. STRUCTURED QUERY LANGUAGE There are five types of SQL as follows: DDL - Creating / modifying tables, views and indexes DML - Retrieving / inserting / updating information SQL/T - Transaction boundaries SQL/I - Integrity constraints SQL/AU. - Authorization (also called DCL)

  10. DATA DEFINITION LANGUAGE

  11. DATA DEFINITION LANGUAGE DDL is the language used to define/modify the database Schema. • Create/Open Database (not discussed)Create Database Employee; • Create/Alter/Drop Table • Create/Drop View • Create/Drop Index

  12. DATA DEFINITION LANGUAGE.. TYPICAL FORMAT Action DatabaseComponent ComponentName… Create Drop Table View Index Cust. Emp. ………… Alter Table Emp.

  13. DATA DEFINITION LANGUAGE.. Command used to create tables CREATE TABLE Table name (attribute attr. type, attribute attr.type..) [CONSTRAINT Constr name TYPE attr]; Command used to change attributes in tables ALTER TABLE Table name ADD attr. attr. type, attr. attr. type; Command used to delete table definitions DROP TABLE Table name ;

  14. CREATE TABLE Creates a table (schema) Dept. d_no d_name d_mgr_ssn d_phone CREATE TABLE DEPT. ( d_no Integer, d_name VarChar(15), d_mgr_SSN Char(9), d_phone Char(12));

  15. ALTER, DROP TABLE Alter table adds/drops attributes; Drop table drops the entire table. Dept. d_no d_name d_mgr_ssn d_phone no_of_emp ALTER TABLE DEPT. ADD no_of_emp Integer; ALTER TABLE DEPT. DROP no_of_emp; DROP TABLE Dept.;

  16. CREATE VIEW Command used to create VIEWS CREATE VIEW Viewname AS SELECT... • A view is the way a user looks at the data. • Views are subsets of the data in the database. • A view could include data from more than one table. • All application programs access data via views. • Views provide logical data independence. • Reports can be created from views (as well as from tables)

  17. CREATE VIEW.. Creates a query; AS is an “alias” or name Emp. e_ssn e_name e_title Create a view of employees who are analysts CREATE VIEW Analysts AS SELECT e_name, e_title FROM Emp. WHERE e_title = “analyst”;

  18. MORE DDL COMMANDS.. Creates a view Called analysts CREATE VIEW ANALYSTS..... DROP VIEW ANALYSTS; Drops the view CREATE INDEX DEPT_INDEX ON DEPT(d_no); Creates an index called Dept_index on attribute d_no DROP INDEX DEPT_INDEX; Drops the index

  19. DATA MANIPULATION LANGUAGE

  20. DATA MANIPULATION LANGUAGE DML is the language used to create/modify and delete data in the database. • INSERT • insert a record • UPDATE • change values • DELETE • delete records • SELECT • choose record

  21. THE INSERT COMMAND.. Inserts a record into a table with name TABLENAME. FORMAT INSERT INTO TABLENAME VALUES (attr1 value, attr2 value….);

  22. THE INSERT COMMAND.. Insert employee record EMP. e_ssn e_name e_title INSERT INTO EMP VALUES (‘978-98-9878’, ‘Sullivan’, ‘developer’);

  23. THE UPDATE COMMAND Update employee title to ‘analyst’ EMP. e_ssn e_name e_title UPDATE EMP SET e_title = ‘analyst’ WHERE e_name = ‘Sullivan’; /* table name */ /* new values */ /* condition */ EMP.

  24. THE DELETE COMMAND Delete Employee record EMP. DELETE FROM EMP. WHERE e_name = ‘Sullivan’; EMP. What if you were to issue, DELETE FROM EMP?

  25. THE SELECT STATEMENT

  26. THE SELECT STATEMENT SELECT <Attribute list> FROM <Relations> [WHERE <Conditions> AND/OR <Conditions>] [GROUP BY <Attribute list>] [HAVING <Conditions>] [ORDER BY <Attribute list> DESC/ASC]; Group by is used to organize data into groups and provide summary information. Having is used for the group condition.

  27. ADDITIONAL NOTES ON SELECT Notes on the select statement • The Select part can include literals (“The number of..”) • Some functions can be included in SELECT part or WHERE part • More than one table and one condition can be specified • Conditions are connected by logical operators -- and/or etc. • When GROUP BY is used, the WHERE clause is not used. • Instead the group condition is specified by HAVING. • ORDER BY is optional and used if sorting is required.

  28. DISCUSSION The output of a SELECT statement is: a) an attribute ? b) a single record ? c) table ?

  29. FUNCTIONS IN SQL Operators to carry out different types of calculations • Logical • Arithmetic • String • Date IN WHICH PART OF THE QUERY ARE FUNCTIONS USED?

  30. LOGICAL OPERATORS Logical operators are generally used to carry out comparison • “=“, “>“, “<“ • “>=“, “<=“ • “<>“ OR “!=“ • (NOT) BETWEEN X1 AND X2 (inclusive) • LIKE • “_” or “%” • IN • (NOT) NULL Dept. (Select….Where D_Name like “%fin%”) Finance Financial Records

  31. LOGICAL OPERATORS.. To select employees between certain income range Select emp#, emp name From emp Where income between 70000 and 90000; To select customers in Macomb, Chicago or Bloomington Cust. Select cust.cust# From cust Where cust.zip in (61455, 60601….)

  32. BUILT-IN ARITHMETIC FUNCTIONS Arithmetic functions are used to carry out math operations • ABS • ROUND • TRUNC • COUNT • SUM • AVG • MAX • MIN Select count(emp_name)… Select max(emp_salary)…..

  33. STRING FUNCTIONS String functions are used to carry out string manipulation • LENGTH(string) • SUBSTR(string, start, no of ch.) • LOWER • UPPER Select substr(prob_descr, 0, 10) ……… Prob_descr = “I am unable to log in…”

  34. DATE FUNCTIONS Date functions are used to carry out date arithmetic • ADD_MONTHS(1/1/13, 5) = 6/1/13 • MONTHS_BETWEEN (sysdate, hiredate) • NEXT_DAY(hiredate, ‘Friday’) • TO_DATE(string, picture) TO_DATE(“12/09/13”, ‘DY th MM, YYYY’) = 9 th DEC, 2013 Next_day(2/20/13, ‘Friday’) = 2/25/13

  35. EMP. e_ssn e_name e_title SIMPLE RETRIEVALS Select employees who are analysts SELECT e_name FROM EMP WHERE e_title = ‘analyst’;

  36. SIMPLE RETRIEVALS.. EMPLOYEE

  37. EMP. e_ssn e_name e_title SIMPLE RETRIEVALS.. Select employees whose name ends with ‘son’ SELECT e_name FROM EMP WHERE e_name like ‘%son’

  38. EMP. e_ssn e_name e_title SIMPLE RETRIEVALS.. Select employees whose name does not begin with ‘a’ SELECT e_name FROM EMP WHERE e_name not like ‘a%’

  39. EMP. e_ssn e_name e_title RETRIEVALS WITH AGGREGATES.. Count the number of developers SELECT COUNT(e_name) FROM EMP WHERE e_title = ‘developer’

  40. RETRIEVALS WITH AGGREGATES.. EMP. e_ssn e_name e_title e_salary SELECT “Average Salary=“, Avg(e_salary) From EMP Where e_title = ‘developer’ or e_title = ‘analyst’ ; What does this query do?

  41. RETRIEVALS WITH EXPRESSIONS.. List the employees and their witholdings (calculated as 8% of salary). EMP. e_ssn e_name e_title e_salary SELECT e_name, e_salary * 0.08 AS Withholdings FROM EMP

  42. EMP. e_ssn e_name e_title RETRIEVALS WITH GROUP BY.. List all job titles and the number of emps in each SELECT e_title, COUNT(e_name) FROM EMP GROUP BY e_title

  43. GROUP BY.. EMPLOYEE

  44. RETRIEVALS WITH HAVING.. List the number of employees in each job category with salary > $50,000 EMP. e_ssn e_name e_title e_salary SELECT “Number of: “, e_title, “=“, COUNT(e_name) FROM EMP GROUP BY e_title HAVING e_salary > 50000

  45. RETRIEVALS WITH HAVING.. EMP.

  46. RETRIEVAL FROM MULTIPLE TABLES • Sharing of information a key concept • Normalization process leads to multiple tables • When data is retrieved from > 1 table, need to • link tables together • This is done by equating FK values with PK values • for each set of tables that need to be linked Emp. emp#, emp name, dept# Dept. dept#, dept name, mgr

  47. Dept. d_named_nod_mgr_ssnd_phone RETRIEVAL FROM MULTIPLE TABLES EMP. e_ssn e_name e_title SELECT Emp.e_name, Dept.d_name FROM EMP, dept. WHERE EMP.e_ssn = dept.d_mgr_ssn

  48. RETRIEVAL FROM MULTIPLE TABLES.. EMPLOYEE DEPARTMENT

  49. DISCUSSION Write SQL queries for the following: 1. Create Emp table with E_SSN, E_Name, E_title as attr. -- assume data types. 2. Add E_salary to the Employee table. 3. Create an index, “Ti_ndx” on E_title. 4. List employees and job titles in order of title. 5. List employees other than developers. 6. Create a view “hi_flier” listing developers with salary > $100K. 7. Count the #. of employees who are managers.

More Related