1 / 75

SQL Workshop

SQL Workshop. Day 1. Day 1 Agenda SQL data selection. Introduction Normalization Tables Unique Key / Primary Key Foreign key SQL Introduction to SQL SELECT, WHERE, ORDER BY Selection from multiple tables Subqueries Practice. Normalization.

eron
Télécharger la présentation

SQL Workshop

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 Workshop Day 1

  2. Day 1 AgendaSQL data selection Introduction Normalization Tables Unique Key / Primary Key Foreign key SQL Introduction to SQL SELECT, WHERE, ORDER BY Selection from multiple tables Subqueries Practice

  3. Normalization • In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics—insertion, update, and deletion anomalies—that could lead to a loss of data integrity • Informally, a relational database table (the computerized representation of a relation) is often described as "normalized" if it is in the Third Normal Form (3NF). • A standard piece of database design guidance is that the designer should create a fully normalized design; selective denormalization can subsequently be performed for performance reasons. However, some modeling disciplines, such as the dimensional modeling approach to data warehouse design, explicitly recommend non-normalized designs, i.e. designs that in large part do not adhere to 3NF

  4. Normalization – an example Table before normalisation Normalized table EMPLOYEE Normalized table CHARGE

  5. Tables

  6. Unique Key • In relational database design, a unique key (UK) can uniquely identify each row in a table. An unique key comprises a single column or a set of columns. • A unique key must uniquely identify all possible rows that exist in a table and not only the currently existing rows. Examples of unique keys are telephone numbers.Names, addresses are not a good candidates for an unique key. • No two distinct rows in a table can have the same value (or combination of values) in those columns if NULL values are not used. • Depending on its design, a table may have arbitrarily many unique keys.

  7. Primary Key • A table can have at most one primary key (PK), but more than one unique key. • A primary key is a combination of columns which uniquely specify a row. It is a special case of unique keys. One difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is enforced. Thus, the values in unique key columns may or may not be NULL. • Another difference is that primary keys must be defined using another syntax. Thus Primary Key column allows no row having NULL while Unique Key column allows null value.

  8. ?Question What are the differences between UK and PK ?

  9. Unique key – an example Bad example

  10. Unique key – an example 2

  11. Foreign Key 1/2 • Aforeign key(FK)is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a set of columns in another (referenced) table. • The columns in the referencing table must be the primary key or other candidate key in the referenced table. • The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. • Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.

  12. Foreign Key 2/2 • A table may have multiple foreign keys, and each foreign key can have a different referenced table. Each foreign key is enforced independently by the database system. • Cascading relationships between tables can be established using foreign keys. • The referencing and referenced table may be the same table, i.e. the foreign key refers back

  13. Foreign key – an example

  14. Introduction to SQL SQL (Structured Query Language) allows the users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others using English-like statements giving the users the ability to describe the data they wish to see. SQL also allows users to define, view and manipulate the data in a database.

  15. SQL Statements Data retrieval SELECT Data manipulation language (DML) INSERT, UPDATE, DELETE, MERGE Data definition language (DDL) CREATE, ALTER, DROP, RENAME, TRUNCATE Transaction control COMMIT, ROLLBACK, SAVEPOINT Data control language GRANT, REVOKE

  16. SQL statements characteristics Are not case sensitive Can be one or more lines Keywords can not be abbreviated or split across lines Clauses are usually places in separated lines Indents are used to enhance readability Arithmetical expressions can be used (+ - * / )

  17. SQL standards Use a single case for all SQL verbs Begin all SQL verbs on a new line Right or left align verbs within the initial SQL verb Separate all words with a single space

  18. SELECT statement SELECT identifies what columns FROM identified which tables SELECT * | {DISTINCT column, expression [alias],… } FROM table; SELECT * FROM employees; SELECT job_id, first_name FROM employees;

  19. SELECTData projection

  20. SELECT statement -duplicated rows By default SELECT returns all rows Duplicates can be eliminated by using the DISTINCT keyword SELECT job_id FROM employees; SELECT DISTINCT job_id FROM employees;

  21. WHERE clause Restrict rows selected by using the WHERE clause The WHERE clause follows FROM clause SELECT * | {distinct column, expression [alias],… } FROM table [WHERE condition(s)]; SELECT * FROM emp; SELECT * FROM emp WHERE deptno = 30;

  22. WHEREdata selection

  23. Comparison conditions =, >, <, >=, <=, <> BETWEEN … AND … IN (set) LIKE IS NULL

  24. Examples SELECTfirst_name FROM employees WHERE substr(first_name, 1, 1) IN (’A’,’W’,’J’); SELECTe1.first_name FROM employees e1 WHERE e1.salary > (SELECT MAX(e2.salary) FROMemployees e2 WHERE e2.job = ’SALESMAN’);

  25. ?Question How many rows will return following query ? SELECT * FROM employees WHERE commission_pct = NULL;

  26. Logical conditions AND OR NOT WHERE salary >= 1000 AND last_name like ‘%MAN%’ WHERE salary > 1000 OR last_name like ‘%MAN%’ WHERE last_name NOT in (‘DOE’,SMITH’)

  27. ORDER BY clause Sort rows with ORDER BY clause ASC ascending (default) Numeric columns with the lowest values first Character columns in alphabetical order Date columns with earlier dates first DESC descending SELECT * | {distinct column, expression [alias],… } FROM table [ORDERBY {column, expr} [ASC|DESC]]; SELECT * FROM employees ORDERBY department_id, salary DESC;

  28. ?Question What will be returned by following query ? SELECTcommission_pct FROMemployees ORDER BYcommission_pct DESC;

  29. ORDER BY clause Sorting null values NULLS FIRST / NULLS LAST (default) Column numbers aliases Expressions in ORDER BY clause ORDER BY commission_pct NULLS FIRST; ORDER BY 2, 1 DESC; ORDER BY DECODE(last_name, ‘DOE', 'A', ‘SMITH', 'B', ‘KING', 'C', 'Z');

  30. Selecting data from multiple tables

  31. ?Question How many rows will return following query ? SELECT * FROM employees, departments;

  32. Selecting data from multiple tablesCartesian Product emp (count = 13), dept (count = 4) Result (13 x 4 = 52 rows ) Cartesian product is generated when WHERE clause is omitted SELECT first_name, department_name FROM employees, departments;

  33. Joining tablesOracle syntax Write JOIN condition in WHERE clause Prefix column name with table name when the same column appears in both tables. SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2 SELECT first_name, department_name FROM employees, departments WHERE employees. department_id =departments. department_id;

  34. Joining tablesWhat is in WHERE clause? SELECT ename, dname FROM employees, departments WHERE employees.department_id=departments.department_id; PK FK

  35. Joining more than two tables SELECT table1.column, table2.column, table3.column FROM table1, table2, table3 WHERE table1.column1 = table2.column2 AND table2.column3 = table3.column4

  36. Outer joinOracle syntax Use outer join to see rows that do not meet the join condition Outer join is (+) operator SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 (+) = table2.column2; SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2 (+); SELECT first_name, department_name FROM employees, departments WHEREemployees.department_id(+)= departments.department_id;

  37. ?Question You want to create a report displaying employee, department names and locations. Which query should you use to create an equi-join. SELECT first_name, department_id, location_id FROM employees,departments; SELECT emp.first_name, dept.department_id, dept.location_id FROM employees e, departments d WHERE e. department_id =d.department_id SELECT e. first_name, d.department_id, d.location_id FROM employees e, departments d WHERE manager_id = manager_id SELECT e.first_name, d.department_id, d. location_id FROM employees e, departments dWHERE e.department_id =d.department_id

  38. Subqueries Subqueries allow the results from one query to be passed directly into another query. A subquery is sometimes called an ‘inner query’, the parent query being called the ‘outer query’. Subqueries can return none, one or more than one row to the parent query in which it has been embedded. Subqueries can be correlated or non correlated.

  39. Subqueriessyntax SELECT column1, … FROM table1 WHERE condition expr (SELECT column2 FROM table 2 WHERE condition); SELECT column1, (SELECT column2 FROM table 2 WHERE condition) FROM table1 WHERE condition;

  40. Subqueriesexamples SELECT first_name, department_id, (SELECT department_name FROM departments WHERE departments.department_id= employees.department_id) FROM employees SELECT first_name, department_id FROM employees WHERE department_idIN (SELECT department_id FROM departments WHERE department_name LIKE '%A%')

  41. SELECT from SELECT SELECT table1.column1, table2.column2, … FROM table1, (SELECT column1, column2 FROM table 2 WHERE condition) vtable2 WHERE table1.column = vtable2.column2

  42. IN / EXISTS condition SELECT department_id FROM departments WHEREdepartment_id NOT IN(SELECT emp.department_id FROM employees emp) SELECT department_id FROM departments WHERENOT EXISTS(SELECTnull FROM employees WHEREemployees.department_id = departments.department_id)

  43. ?Question Evalute the SQL statement: What is the result when the query is executed ? SELECT * FROM departments WHERE department_id = (SELECTt.department_id FROM employees t WHEREt.job_id = ‘IT_PROG’)

  44. Practice SQL_1 Please write statement which lists the name, salary and commision for all the employees who have a manager and earn more than 1500.

  45. Practice SQL_2 You would like to display the name of employee with his salary and name of his supervisor. If the supervisor isn’t specified for the employee, the value should be NULL.Please write this SQL statement.

  46. SQL Performance tuning • What shall be checked before we start optimalization • Understand how our query is executed • Understand if it can run faster • Chose best option (execution plan)

  47. Practice SQL query optimalization Try to optimize the query: SELECT * FROM employees_opt eo WHERE eo.last_name = 'Mavris'; 00 sql for optimalization.sql

  48. Practice SQL query optimalization As the fisrt step of optimalization you shall check if statistics calculated for tables used in the query are in place. TIP: use user_tables view Useful columns of user_tables view: table_name– the name of the table num_rows– number of rows in the table blocks– number of blocks in the table avg_row_len– average row length of the table last_analyzed– the last date of gather statistics for table

  49. Practice SQL query optimalization Answer: SELECT table_name, num_rows, blocks, avg_row_len, to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') FROM user_tables WHERE table_name = 'EMPLOYEES_OPT'; 01 user_tables.sql

  50. Practice SQL query optimalization As the second step of optimalization you shall check if statistics calculated for indexes of tables used in the query are in place TIP use user_indexes view. Useful columns of user_indexes view: table_name– the name of the table index_name – the name of the index num_rows– number of rows in the index distinct_keys – number of distinct values in the index last_analyzed– the last date of gather statistics for index

More Related