Relational Model
PART I – SELECT
Copyright By PowCoder代写 加微信 powcoder
SQL Commands
Create, Drop, Rename, Alter (Truncate)*
Update, Delete, Insert
Commit, Rollback (Savepoint)*
(Grant, Revoke)*
* Not covered this semester
The DROP Command
DROP command
Used to drop named schema elements, such as tables, views, domains, or constraints
Drop behavior options:
CASCADE and RESTRICT
DROP SCHEMA COMPANY CASCADE;
The ALTER Command
Alter table actions include:
Adding or dropping a column (attribute)
Changing a column definition
Adding or dropping table constraints
ALTER TABLE Company.Employee
ADD COLUMN Job VARCHAR(12);
To drop a column
Choose either CASCADE or RESTRICT
Select, Project, Rename elements
Inner and Outer Joins
Ordering / Grouping
Calculations and Aggregate Functions
DISTINCT – ?
SQL Retrieval
SELECT statement
Projection attributes SELECT Fname, Lname
Same as in Relational Algebra
Basic statement for retrieving information FROM table(s)
WHERE Selection condition
Boolean condition that must be true for any retrieved tuple
SQL Retrieval
SQL retrieval operation: SELECT statement
SELECT
FROM
SQL Retrieval
Example SQL Query:
SELECT Bdate, Address
FROM Employee
WHERE Fname = ‘John’
AND Lname = ‘Smith’;
Retrieves every row from the table where the selection condition holds true
Anyone with the name “ ” will have their birth date and address returned
SQL Retrieval
Example SQL Query:
SELECT Bdate, Address
FROM Employee
WHERE Fname = ‘John’
AND Lname = ‘Smith’;
What does this look like in relational algebra?
π Bdate, Address(σ Fname=‘John’ AND Lname=‘Smith’(EMPLOYEE) )
SQL Missing WHERE?
Missing “WHERE” clauses
Where clauses are not required
When missing, ALL results are returned:
SELECT Fname, Lname
FROM EMPLOYEE;
This would return all employee names in the table
SQL Inner Join – two tables
Example select-project-join SQL Query:
SELECT Fname, Lname
FROM Employee, Department
WHERE Dname = ‘Research’
AND Dnumber = Dno;
Here will retrieve first name and last name of all employees who work in the ‘Research’ department
Join condition – combines multiple tables in the WHERE
Finds the row in the table Department where Dname is ‘Research’
Matches the Dnumber for that row to the Dno in the Employee table
Only retrieves results where these values match
SQL Inner Join
Example select-project-join SQL Query:
SELECT Fname, Lname
FROM Employee, Department
WHERE Dname = ‘Research’
AND Dnumber = Dno;
What does this look like in relational algebra?
Research_Dept ¬ (σ Dname=‘Research’(DEPARTMENT) )
Research_Emps ¬ (EMPLOYEE ⨝Dno=Dnumber (Research_Dept) )
Result ¬ (π Fname, Lname (Research_Emps) )
Cross-Join Behavior
Need to be careful with results
When multiple tables involved, you get back the cross product of your tuples if no join condition is specified (by default!):
SELECT Fname, Lname, Dname
FROM Employee, Department
This actually returns ALL Employees crossed with ALL Department names
So if you have two employees and two departments, ‘ ’ in Research and ‘ ’ in ‘Accounting’, you would get:
Accounting
SQL Join Confusion
Suppose we had chosen different names for our attributes
What if we wanted to use “Dnumber” everywhere for the department number?
This would no longer work:
SELECT Fname, Lname
FROM Employee, Department
WHERE Dname = ‘Research’
AND Dnumber = Dnumber;
In this case, what do we mean by Dnumber=Dnumber?
Ambiguous – we have two tables with the same attribute in them
SQL Join Confusion solved – aliases!
We can specify exactly what we mean
SELECT Fname, Lname
FROM Employee, Department
WHERE Dname = ‘Research’
AND Employee.Dnumber
= Department.Dnumber;
Can also use aliases to make our lives simpler:
SELECT Fname, Lname
FROM Employee AS E, Department AS D
WHERE Dname = ‘Research’
AND E.Dnumber = D.Dnumber;
Aliases can also help us with circular references:
SELECT E.Fname, E.Lname,
S.Fname, S.Lname
FROM Employee AS E, Employee AS S
WHERE E.Super_ssn = S.Ssn;
This gets us back a list of employees and their supervisors
Without aliasing, this query would not work
Best practice is to always use aliases, even when you don’t need them
Usually improves understandability of code:
SELECT EMP.Fname, EMP.Lname
FROM Employee AS EMP, Department AS DEPT
WHERE DEPT.Dname=‘Research’
AND DEPT.Dnumber=EMP.Dno;
SQL Inner Join – Multiple tables
Another example select-project-join SQL Query
SELECT Pname, Lname
FROM Employee, Department, Project
WHERE Dnum = Dnumber
AND Mgr_ssn = Ssn
AND Plocation = ‘Houston’;
Here we find:
Names of projects located in Houston
Last names of the managers of the departments that these projects are associated with
Note that this query joins across 3 tables:
Need PROJECT table to find the location
Relate PROJECT to DEPARTMENT via DNUMBER to get Department Manager’s SSN
Use Dpt. Manager’s SSN to find last name in EMPLOYEE table
Joins – Inner Joins with JOIN
Previously…
SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dno=Dnumber AND Dname=‘Research’;
Use JOIN to dynamically create a table “on the fly”
SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;
Joins – Inner Joins with JOIN
SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;
The FROM clause here is a single joined table
Our join condition is explicit in the FROM clause after the ON keyword
Attributes are all the attributes in the EMPLOYEE table together with the attributes in the DEPARTMENT table
ON condition shows where things match up
In this case, on the “Department Number” attribute
Joins – Outer Joins with OUTER JOIN
Sometimes we want to include results even when an attribute is unknown (i.e. NULL)
We use an Outer Join (as in relational algebra – can use the qualifiers LEFT, RIGHT and FULL)
SELECT E.Lname, S.Lname AS Super
FROM (Employee AS E LEFT OUTER JOIN Employee AS S ON E.Super_ssn=S.ssn);
SELECT E.Lname, S.Lname AS Super
FROM (Employee AS E RIGHT OUTER JOIN Employee AS S ON E.Super_ssn=S.ssn);
SELECT E.Lname, S.Lname AS Super
FROM (Employee AS E FULL OUTER JOIN Employee AS S ON E.Super_ssn=S.ssn);
Tables as Sets in SQL
Duplicate elimination
In a pure relational model, no relation would have duplicate tuples
But SQL does allow duplicate tuples in a relation
So long as there is no conflict in their primary keys
…but if there’s no primary key defined in a table, it can have duplicate entries
Tables as Sets in SQL
Even on tables that have a primary key, sometimes queries will return duplicates
If we’re only getting a limited number of attributes, there could be overlap in those attributes
SELECT Salary
FROM Employee;
Will return ALL salary values, including duplicates
If we want to remove duplicates, we use the DISTINCT keyword
SELECT DISTINCT(Salary)
FROM Employee;
This would return only the unique SALARY entries from the EMPLOYEE table, removing duplicate entries
Tables as Sets in SQL (cont’d)
Set operations on queries
UNION (R U S)
INTERSECT (R ∩ S)
EXCEPT (R – S)
Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.
( SELECT DISTINCT(Pnumber)
FROM Project, Department, Employee
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname=‘Smith’)
( SELECT DISTINCT(Pnumber)
FROM Project, Works_On, Employee
WHERE Pnumber=Pno AND Essn=Ssn AND Lname=‘Smith’);
Attribute Wildcard: *
Attribute wildcards in the SELECT clause
Retrieve all of the attributes, using the asterisk
FROM Employee;
This would return all employee data from the Employee table, for all attributes/columns
Value Wildcards: LIKE, % and _
Wildcards in the WHERE clause
Sometimes we don’t want to match a whole attribute completely
% replaces an arbitrary number of zero or more characters
We can use a wildcard in the WHERE clause with LIKE
SELECT Fname, Lname
FROM Employee
WHERE Lname LIKE ‘Smith%’;
Will match any last name like Smith*, including Smith, Smithton, Smithfield, etc.
If we really need a ‘%’ literal character, we need to escape it:
SELECT Pname
FROM Products
WHERE Discount LIKE ‘%5\%’;
Will match any discount like *5%, including 5%, 25%, 95%, etc.
Value Wildcards: LIKE, % and _
Wildcards in the WHERE clause (cont’d)
( _ ) replaces a single character
Find all employees who were born during the 1950s
SELECT Fname, Lname
FROM Employee
WHERE Bdate LIKE ‘_ _ 5 _ _ _ _ _ _ _’;
If we really need a ‘_’ literal character, we need to escape it:
SELECT Pname
FROM Products
WHERE Pname LIKE ‘%\_clean’;
Will match any product name like *_clean, such as Spring_clean, Fresh_clean, etc.
Arithmetic Operators
Arithmetic in queries
Can use all addition (+), subtraction (–), multiplication (*) and division (/) symbols
SELECT Lname, 1.1*Salary AS Increase
FROM Employee;
This would give us back all of the employee last names with their salary increased by 10%
Note that it doesn’t change what’s in the database – just what is reported by the query!
Ranges: BETWEEN
Ranges can be expressed as operators
FROM Employee
WHERE Salary >= 30000
AND Salary <= 40000;
Or using the BETWEEN keyword (range inclusive)
FROM Employee
WHERE Salary BETWEEN 30000 AND 40000;
Nested Queries
Nested queries
Complete select-from-where blocks within WHERE clause of another query
Comparison operator IN
Compares value v with a set (or multiset) of values V
Evaluates to TRUE if v is one of the elements in V
Select Using “IN”
List the name of employees in departments 25, 47, or 53
SELECT Lname, Fname
FROM Employee
WHERE Dno IN (25, 47, 53);
Nested Queries
Find all the project names for projects being managed by employee last name ‘Wong’:
SELECT Pname
FROM Project
WHERE Pnumber IN
(SELECT Pnumber
FROM Project, Department, Employee
WHERE Dnum=Dnumber AND
Mgr_ssn=Ssn AND Lname=‘Wong’);
SQL Retrieval
SQL Query Syntax:
SELECT
FROM
[ ORDER BY
We can force the query to come back in a particular order
Very useful for reports and for debugging
Use ORDER BY clause
Keyword DESC to see result in a descending order of values
Keyword ASC to specify ascending order explicitly
FROM Employee
ORDER BY Lname DESC, Fname, Super_ssn;
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com