1 / 44

Lecture 5 Term 2

Lecture 5 Term 2. 10/1/14. SQL Like. Example. select emp_name,sal from employee WHERE sal LIKE '1_%_%';. BYRNE 100 CASEY 1000 DOHERTY 150. Example. select emp_name,sal from employee WHERE sal LIKE '%0';. HEARNE 800 BYRNE 100 WALSH 3500 HARTE 700 CASEY 1000 MURRAY 360

medea
Download Presentation

Lecture 5 Term 2

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. Lecture 5 Term 2 10/1/14

  2. SQL Like

  3. Example select emp_name,sal from employee WHERE sal LIKE '1_%_%'; BYRNE 100 CASEY 1000 DOHERTY 150

  4. Example select emp_name,sal from employee WHERE sal LIKE '%0'; HEARNE 800 BYRNE 100 WALSH 3500 HARTE 700 CASEY 1000 MURRAY 360 DOHERTY 150 MARTIN 900

  5. Note: Aliases • You can rename a table or a column temporarily by giving another name known as alias. • The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name does not change in the database. • The column aliases are used to rename a table's columns for the purpose of a particular SQL query.

  6. Run Time Variables • Require input from user • One script versus many • Data that is supplied at run time and stored in a variable • The & character (ampersand) is used for variables

  7. SQL Constraints • Constraints are the rules enforced on data columns on table. • These are used to limit the type of data that can go into a table. • This ensures the accuracy and reliability of the data in the database. • Constraints could be column level or table level. • Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

  8. Constraints • NOT NULL Constraint: Ensures that a column cannot have NULL value. • DEFAULT Constraint: Provides a default value for a column when none is specified. • UNIQUE Constraint: Ensures that all values in a column are different. • PRIMARY Key: Uniquely identified each rows/records in a database table. • FOREIGN Key: Uniquely identified a rows/records in any another database table. • CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. • INDEX: Use to create and retrieve data from the database very quickly.

  9. Not Null • For example, the following SQL creates a new table called CUSTOMERS and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULLs: CREATE TABLE CUSTOMER ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, SALARY DECIMAL (18, 2) );

  10. Index (Manual 10-31) • Indexes are special lookup tables that the database search engine can use to speed up data retrieval. • An index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. • For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers. • CREATE INDEX index_name ON table_name;

  11. Continued. • An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements • Indexes can be created or dropped with no effect on the data • Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

  12. SQL Joins • The SQL Joins clause is used to combine records from two or more tables in a database. • A JOIN is a means for combining fields from two tables by using values common to each.

  13. Non equi join • An nonequi (or theta) join is an inner join statement that uses an unequal operation (i.e: <>, >, <, !=, BETWEEN, etc.) to match rows from different tables.

  14. Order and Customer Table http://www.w3resource.com/sql/joins/perform-a-non-equi-join.php

  15. SQL Example SELECT a.ord_num,a.ord_amount,b.cust_name,b.working_area FROM orders a,customer b     WHERE a.ord_amount BETWEEN b.opening_amt AND b.opening_amt;  

  16. Outer Joins The where clause is used in the examples to restrict the joins to the information that we want. For example, only show the info where the department number matches in the two tables. • If the condition is not met then the information will not be displayed. • But what about cases where we want it to be displayed?

  17. Outer Joins (cont) As an example, there is a department 7 (FACILITIES) in the depttable but no employees in the department (from the employee table). • If we wanted a list of all employees in a department we would run select d.dept_no, d.dept_name , e.emp_name from dept d, employee e where e.dept_no=d.dept_no order by dept_no; 1 HR CASEY 2 SALES MARTIN 2 SALES MURRAY 3 SAFETY BYRNE 3 SAFETY HARTE 3 SAFETY DOHERTY 4 FINANCE HEARNE 4 FINANCE WALSH

  18. Outer Join (cont 2) • If we wanted to see department 7 in the output, we would use an outer join You can see an outer join in a where statement when you see (+) • The parenthesis are included. • For example where e.dept_no (+) = d.dept_no • This tells Oracle that there isn’t any corresponding data in the emp table – it is deficient in information. When Oracle sees this it knows that he where clause can’t be met and it displays the “missing” data

  19. Deficient in data select d.dept_no, d.dept_name , e.emp_name from dept d, employee e where e.dept_no (+) = d.dept_no order by dept_no; The (+) goes on the side of the where clause where there isn’t sufficient data 1 HR CASEY 2 SALES MURRAY 2 SALES MARTIN 3 SAFETY BYRNE 3 SAFETY HARTE 3 SAFETY DOHERTY 4 FINANCE WALSH 4 FINANCE HEARNE 7 FACILITIES (NULL)

  20. Left Outer Join • returns all rows from the left table, even if there are no matches in the right table. • This means that if the ON clause matches 0 (zero) records in right table • The join will still return a row in the result, but with NULL in each column from the right table. SELECT table1.column1, table2.column2... FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;

  21. Example CASEY 1 HR MARTIN 2 SALES MURRAY 2 SALES DOHERTY 3 SAFETY HARTE 3 SAFETY BYRNE 3 SAFETY WALSH 4 FINANCE HEARNE 4 FINANCE SELECT e.emp_name,d.dept_no,d.dept_namefrom employee e LEFT OUTER JOIN dept d ON(e.dept_no = d.dept_no);

  22. Right Outer Join • returns all rows from the right table, even if there are no matches in the left table. • This means that if the ON clause matches 0 (zero) records in left table • the join will still return a row in the result, but with NULL in each column from left table. • SELECT table1.column1, table2.column2... • FROM table1 • RIGHT JOIN table2 • ON table1.common_field = table2.common_field;

  23. Example SELECT e.emp_name,d.dept_no,d.dept_name from employee e RIGHT OUTER JOIN dept d ON(e.dept_no = d.dept_no); HEARNE 4 FINANCE BYRNE 3 SAFETY WALSH 4 FINANCE HARTE 3 SAFETY CASEY 1 HR MURRAY 2 SALES DOHERTY 3 SAFETY MARTIN 2 SALES 7 FACILITIES

  24. Full Outer Join • Retrieves all the rows in each table even if there is no match SELECT e.emp_name,d.dept_no,d.dept_name from employee e FULL OUTER JOIN dept d ON(e.dept_no = d.dept_no); CASEY 1 HR MARTIN 2 SALES MURRAY 2 SALES DOHERTY 3 SAFETY HARTE 3 SAFETY BYRNE 3 SAFETY WALSH 4 FINANCE HEARNE 4 FINANCE 7 FACILITIES

  25. Set Operators • UNION returns the result from both sql queries. It returns all distinct rows (no duplicates). • To see all managers and clerks, we would run select emp_name, job from employee where job='MANAGER' union select emp_name, job from employee where job='CLERK'; Emp_name job BYRNE CLERK CASEY MANAGER DOHERTY CLERK MARTIN MANAGER MURRAY CLERK WALSH MANAGER

  26. Other set operators • MINUS, will show all entries in the first query that are not in the second query. • INTERSECT will show all entries that appear in both queries. • The format of both is the same as UNION

  27. Subqueries • A sub-query is a query within a query. • If I wanted to find out the names of everyone who earns more than Byrne, I need two queries. The first query will determine Byrnes salary Select sal from employee Where emp_name='BYRNE'; result 100 • I then need to take this result and feed it into the second query which finds everyone who earns more than this salary. Select emp_name, sal From employee Where sal > 100; Emp_namesal HEARNE 800 WALSH 3500 HARTE 700 CASEY 1000 MURRAY 360 DOHERTY 150 MARTIN 900

  28. Subqueries (cont) • What I have written above as “[Byrne’s salary]” is the sub-query (the first query defined above. • In effect, [Byrne’s salary] is equal to Select sal from employee Where emp_name=’Byrne’; • So we can write the complete query as Select emp_name, sal From employee Where sal > (Select sal from employee Where emp_name='BYRNE');

  29. Returning multiple columns We can have subqueries that return more than one field – but they are still returning only one row. The example finds everyone who has the same job in the same department as BYRNE. select emp_name,job,dept_no from employee where (dept_no, job)= (select dept_no, job from employee where emp_name='BYRNE'); EMP_NAME JOB DEPT_NO BYRNE CLERK 3 DOHERTY CLERK 3

  30. Distributed Databases

  31. Distributed Databases • Logical next steps in geographically dispersed organisations • Objective is to provide location transparency • A set of decentralised DBs located in differed places, developed for the specific information needs of each site

  32. What is a distributed Database? • A database that is stored in more than one location. • Parts or copies of the database are physically stored in one location. • Other parts or copies are stored and maintained in other locations.

  33. Distributed Databases • The organisation may possess a host of databases located at various sites controlled by a computer network • Alternatively, users may download processing tasks from a partitioned central database

  34. Advantages of Distributed Databases • Increased reliability of systems • Availability of data • Local control preserved • Modular growth possible at each site and at new sites • Optimised communication costs • Faster response time

  35. Disadvantages of DDBs • Distributed databases disseminate information in a way that it is difficult to manage, coordinate and control. • DDBs typically have a large user group with access to proprietary data creating security issues. • DDBs requires that data be backed up in multiple locations and sychronised with the central db.

  36. Control in Databases • Transaction control: • Commit transactions • Roll back transactions • Concurrency control: • Simultaneous access • Simultaneous update • Deletion

  37. Control in Distributed Databases • Different portions of the overall database reside at different locations. • These portions are controlled by different processors running sometimes different DBMSs. • Common schema means can involve any portion of the DB residing at any location.

  38. Options for Distributed DBs • Issue of physical design (data structure) • Performance of the DB

  39. Options for Distributed DBs • There are a number of options: • Data replication • Horizontal partitioning • Vertical partitioning • Combinations of the above

  40. Data Replication • Store a separate copy of the full tables at each site. • If a copy is stored at every site: Full replication. • E.g. • Lufthansa Airlines

  41. Central Database HOST CPU REMOTE CPU REMOTE CPU Remote Database Duplicate Remote Database Duplicate

  42. Data Replication Continued…. • Advantages • Reliability • Rapid response rate • Disadvantages • Storage requirements • Complexity and cost of updates

  43. Partitioned Database • The central database may be partitioned so that each remote processor may access the necessary data to serve its local area. • Changes in local files can be justified with the central database on a batch basis.

  44. Central Database HOST CPU REMOTE CPU REMOTE CPU Remote Database Partition B Remote Database Partition A

More Related