Lecture 4 Querying Database PDF

Lecture 4 Querying Database Database 2 Dr. Mohamed Saied Amer Outline Relational Algebra Data Manipulation Language (DML) Commands. Data Control Language (DCL) Commands. Relational Algebra Overview Relational algebra is the basic set of operations for the relational model These...

Lecture 4 Querying Database Database 2 Dr. Mohamed Saied Amer Outline Relational Algebra Data Manipulation Language (DML) Commands. Data Control Language (DCL) Commands. Relational Algebra Overview Relational algebra is the basic set of operations for the relational model These operations enable a user to specify basic retrieval requests (or queries) The algebra operations thus produce new relations. A sequence of relational algebra operations forms a relational algebra expression. Unary Relational Operations: SELECT The SELECT operation (denoted by  (sigma)) is used to select a subset of the tuples from a relation based on a selection condition. The selection condition acts as a filter Keeps only those tuples that satisfy the qualifying condition Tuples satisfying the condition are selected whereas the other tuples are discarded (filtered out) Unary Relational Operations: SELECT In general, the select operation is denoted by (R) where Examples: Select the EMPLOYEE tuples whose department number is 4: DNO = 4(EMPLOYEE) Select the employee tuples whose salary is greater than $30,000: SALARY > 30,000 (EMPLOYEE) Unary Relational Operations: PROJECT PROJECT Operation is denoted by (pi) This operation keeps certain columns (attributes) from a relation and discards the other columns. PROJECT creates a vertical partitioning The list of specified columns (attributes) is kept in each tuple The other attributes in each tuple are discarded Unary Relational Operations: PROJECT The general form of the project operation is: (R) Example: To list each employee’s first and last name and salary, the following is used:  LNAME, FNAME,SALARY(EMPLOYEE) Single expression versus sequence of relational EmpId Fname Lname Salary DNo operations 1 (Example) Ahmed Ali 10000 1 2 Ali Sameh 20000 5 To retrieve 3 theMahmoud first name, Salem last name, 15000 and3 salary of all employees who work in department number 5, we must apply a select and a project operation. We can writeFname a single Lname relationalSalary algebra expression as follows: Ali Sameh 20000 FNAME, LNAME, SALARY(DNO=5(EMPLOYEE)) Relational Algebra Operations from Set Theory: UNION UNION Operation Binary operation, denoted by  The result of R  S, is a relation that includes all tuples that are either in R or in S or in both R and S Duplicate tuples are eliminated The two operand relations R and S must be “type compatible” (or UNION compatible) R and S must have same number of attributes Each pair of corresponding attributes must be type compatible Relational Algebra Operations from Set Theory: UNION Example: To retrieve the social security numbers of all employees who either work in department 5 (RESULT1 below) or directly supervise an employee who works in department 5 (RESULT2 below) We can use the UNION operation as follows: Figure 8.3 Result of the UNION operation RESULT ← RESULT1 ∪ RESULT2. Relational Algebra Operations from Set Theory: INTERSECTION INTERSECTION is denoted by  The result of the operation R  S, is a relation that includes all tuples that are in both R and S The attribute names in the result will be the same as the attribute names in R The two operand relations R and S must be “type compatible” UNION and INTERSECTION example Intersection Two Tables Union Binary Relational Operations: JOIN JOIN Operation (denoted by ) The sequence of CARTESIAN PRODECT followed by SELECT is used quite commonly to identify and select related tuples from two relations A special operation, called JOIN combines this sequence into a single operation This operation is very important for any relational database with more than a single relation, because it allows us combine related tuples from various relations Binary Relational Operations: JOIN The general form of a join operation on two relations R(A1, A2,..., An) and S(B1, B2,..., Bm) is: R S where R and S can be any relations that result from general relational algebra expressions. Binary Relational Operations: JOIN Example: Suppose that we want to retrieve the name of the manager of each department. To get the manager’s name, we need to combine each DEPARTMENT tuple with the EMPLOYEE tuple whose SSN value matches the MGRSSN value in the department tuple. We do this by using the join operation. DEPT_MGR ←DEPARTMENT MGRSSN=SSN EMPLOYEE Binary Relational Operations: JOIN DML Commands in SQL. There are four main commands in DML which are: INSERT command SELECT command and JOIN command UPDATE command DELETE command INSERT command Adding data to a table by inserting one or more rows into the table. Syntax: INSERT INTO table1 (ID, varchar1, variant1) VALUES (4, 'Fourier', ‘Value2'); SELECT command It is the most important data manipulation command in Structured Query Language. The SELECT command shows the records of the specified table. Syntax: SELECT * FROM table_name; SELECT column1, column2,... FROM table_name; Using WHERE clause: SELECT * FROM table_name WHERE condition on fields  SELECT command with where Where operators: Operator Description SELECT * FROM Customers = Equal WHERE age between 16 AND 45; > Greater than SELECT * FROM Customers < Less than WHERE age > 20; >= Greater than or equal