Chapter 10 - Query Processing and Optimization Introduction to Query Processing, Translating SQL Queries into Relational Algebra, Translating SQL Queries into Relational Algebra, Using Selectivity and Cost Estimates in Query Optimization, Overview of Query Optimization in Oracle. | CSC271 Database Systems Lecture # 10 Summary: Previous Lecture The relation algebra operations Division Aggregate and grouping operations The relational calculus Tuple relational calculus Domain relational calculus SQL: Data Manipulation Chapter 5 Objectives of SQL Ideally, database language should allow user to: Create the database and relation structures Perform insertion, modification, deletion of data from relations Perform simple and complex queries Must perform these tasks with minimal user effort and command structure/syntax must be easy to learn It must be portable Objectives of SQL SQL is a transform-oriented language with 2 major components: A DDL for defining database structure A DML for retrieving and updating data Until SQL:1999, SQL did not contain flow of control commands. These had to be implemented using a programming or job-control language, or interactively by the decisions of user Objectives of SQL SQL is relatively easy to learn It is non-procedural language, you specify what information you require, rather than how to get it It is essentially free-format Objectives of SQL Consists of standard English words: 1) CREATE TABLE Staff(staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2)); 2) INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300); 3) SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000; Objectives of SQL Can be used by range of users including DBAs, management, application developers, and other types of end users An ISO standard now exists for SQL, making it both the formal and de facto standard language for relational databases (ISO, 1992, 1999a) History of SQL In 1974, D. Chamberlin (IBM San Jose Laboratory) defined language called ‘Structured English Query Language’ (SEQUEL) A revised version, SEQUEL/2, was defined in 1976 but name was subsequently changed to SQL for legal reasons History of SQL Still pronounced ‘see-quel’, though official pronunciation is ‘S-Q-L’ IBM subsequently produced a prototype DBMS . | CSC271 Database Systems Lecture # 10 Summary: Previous Lecture The relation algebra operations Division Aggregate and grouping operations The relational calculus Tuple relational calculus Domain relational calculus SQL: Data Manipulation Chapter 5 Objectives of SQL Ideally, database language should allow user to: Create the database and relation structures Perform insertion, modification, deletion of data from relations Perform simple and complex queries Must perform these tasks with minimal user effort and command structure/syntax must be easy to learn It must be portable Objectives of SQL SQL is a transform-oriented language with 2 major components: A DDL for defining database structure A DML for retrieving and updating data Until SQL:1999, SQL did not contain flow of control commands. These had to be implemented using a programming or job-control language, or interactively by the decisions of user Objectives of SQL SQL is relatively easy to learn It is non-procedural language,
