MORE SQL!

MORE SQL!

PART I – SELECT

Chapter 4 & 5

SQL Commands

⚫ DDL

◦ Create, Drop, Rename, Alter (Truncate)*

⚫ DML

◦ Update, Delete, Insert

◦ Select

⚫ TCL

◦ Commit, Rollback (Savepoint)*

⚫ DCL

The DROP Command

⚫ DROP command

◦ Used to drop named schema elements, such as tables, views, domains, or constraints

⚫ Drop behavior options:

◦ CASCADE and RESTRICT

⚫ Example:

◦ 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

⚫ Example:

ALTER TABLE Company.Employee ADD COLUMN Job VARCHAR(12);

⚫ To drop a column

Outline

⚫ SELECT

◦ Projection

◦ Inner Joins

◦ DISTINCT

◦ Ordering

SQL Retrieval

⚫ SQL allows a table to have two or more tuples that are identical in all their attribute values

◦ Unlike relational model

◦ Multiset or bag behavior

⚫ SELECT statement

◦ Basic statement for retrieving information FROM

table(s)

◦ Projection attributes

◦ WHERE Selection condition

SQL Retrieval

⚫ SQL retrieval operation: SELECT statement

◦ SELECT <attributes>

FROM <tables>

[WHERE <condition>];

◦ <attributes> - Projection attributes

◦ <condition> - Selection condition

SQL Retrieval

⚫ Example SQL Query:

◦ SELECT Bdate, Address FROM Employee

WHERE Fname = ‘John’

ANDLname = ‘Smith’;

⚫ Retrieves every row from the table where the selection condition holds true

◦ Anyone with the name “John Smith” 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

⚫ 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 - Binary

⚫ 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) )

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

Aliases

⚫ 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 – Ternary

⚫ 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:

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, ‘John Smith’ in Research and ‘Bob Jones’ in ‘Accounting’, you would get:

Fname Lname Dname

John Smith Accounting

John Smith Research

Bob Jones Accounting

Bob Jones Research

Attribute Wildcard: *

⚫ Attribute wildcards in the SELECT clause

◦ Retrieve all of the attributes, using the asterisk

● SELECT *

FROM Employee;

● This would return all employee data from the Employee table,

for all attributes/columns

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

Tables as Sets in SQL (cont’d)

⚫ Set operations on queries

◦ UNION (R U S)

◦ INTERSECT (R ∩ S)

◦ EXCEPT (R – S)

⚫ Example:

◦ 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’) UNION

( SELECT DISTINCT(Pnumber)

FROM Project, Works_On, Employee

WHERE Pnumber=Pno AND Essn=Ssn AND Lname=‘Smith’);

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

● SELECTFname, 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:

● SELECTPname

Value Wildcards: LIKE, % and _

⚫ Wildcards in the WHERE clause (cont’d)

◦ ( _ ) replaces a single character

● Find all employees who were born during the 1950s

● SELECTFname, Lname

FROM Employee

WHERE Bdate LIKE ‘_ _ 5 _ _ _ _ _ _ _’;

● If we really need a ‘_’ literal character, we need to escape it:

● SELECTPname

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

● SELECT *

FROM Employee

WHERE Salary >= 30000 AND Salary <= 40000;

⚫ Or using the BETWEEN keyword (range inclusive)

● SELECT *

FROM Employee

WHERE Salary BETWEEN 30000 AND 40000;

SQL Retrieval

⚫ SQL Query Syntax:

SELECT <attribute(s)>

FROM <table(s)>

[ WHERE <condition> ]

[ ORDER BY <attribute list> ]

Ordering

⚫ Ordering

◦ 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

● SELECT *

FROM Employee

ORDER BY Lname DESC, Fname, Super_ssn;

Views in SQL

⚫ A view is a single table defined by other tables

◦ A virtual table

◦ Can be actual tables or other views

⚫ Use the CREATE VIEW command to create a new view in the database

◦ Uses a SELECT statement to build the view

● Any SELECT statement can form the basis of a view

◦ Views are dynamic

● Not copies of the underlying tables

●

Views in SQL

⚫ Example view creation:

CREATE VIEW WORKS_ON_V1

AS SELECT Fname, Lname,Pname, Hours FROM Employee, Project, Works_on WHERE Ssn=Essn AND Pno=Pnumber;

CREATE VIEW DEPT_INFO(Dept_name, Num_emps, Total_sal) AS SELECT Dname, COUNT(*), SUM(Salary)

FROM Department, Employee WHERE Dnumber=Dno

GROUP BY Dname;

Views in SQL

⚫ Specify SQL queries on a view

⚫ View always up-to-date

◦ Responsibility of the DBMS and not the user

◦ Several different approaches to storing the

view

Using Views in SQL

⚫ Create a view called PROGS consisting of the EMPNO, name and salary of all programmers. Include the locations of their departments.

CREATE VIEW Progs (EMPNO, Name, Salary, Homebase)

AS SELECT EMPNO, Lname, Salary, Dlocation FROM Employee EMP, Department DEPT,

Dept_Locations DL

WHERE EMP.Dno = DEPT.Dnumber

AND DL.Dnumber = DEPT.Dnumber

EMP.Job = ‘PROGRAMMER’;

Using Views in SQL

⚫ Using the PROGS view, find the average salary of programmers in Columbus.

SELECT AVG(Salary) FROM Progs

WHERE Homebase = ‘Columbus’;