Flashcards
Databases Topic 2
Flashcards do conjunto (82)
- 1What is database query languages?Recall from Topic 1 that a data model has 3 primary features:
- Objects
- Operators
- Constraints
Database query language is the operators that are focus of this topic. - 2Features of Database query languageIt should allow user to:
- Create the database and structures
-Manage the data, Retrieve data
This should all happen with:
-Minimal effort
-Relatively simple syntax - 3What is Relational Algebra?Codd defined the relational algebra:
It transforms one or two relations into a new relation (closure property). - 4Relation Specific OperatorsRESTRICT
PROJECT
CARTESIAN PRODUCT X
JOIN *
(Full Outer Join, One-Sided Join)
DIVISION - 5RESTRICT AND PROJECTRestrict and Project are similar in that the result of operations using them is a subset of the original relation.
- 6The RESTRICT Operator-Operates on one relation
- Produces a subset of the tuples (rows) of a relation
-Reffered to in some texts as Select - 7Eg. EMPLOYEE (E#, Name, Age, Salary) 'Restrict to Employees whose age is less than 30'RESTRICT Age < 30 (EMPLOYEE)
- 8The PROJECT Operator- Operates on one relation
- Used to select a subset of attributes (Column) of a relation. - 9Eg. EMPLOYEE (E#, Name, Age, Salary) 'List the names and salaries of all employees'PROJECT Name, Salary (EMPLOYEE)
- 10CARTESIAN PRODUCT Operator- Applies to two relations
- R1 X R2
- Result is a relation with the combined attributes of the two relations and records consisting of all possible combinations of tuples (rows) from the two relations. - 11NATURAL JOIN OperatorR *join condition S
- An operation on two relations, equivalent to a product by a restrict
Eg. EMP* EMP.D# = DEPT.D# DEPT - 12OUTER JOIN Operator- Natural join preserves matching values
- Outer join also preserves non-matchin tuples (rows).
-Any missing values in second relation are set to null.
- Outer joins can be: Full or One-sided. - 13The DIVISION Operator-Match on a subset of values-A relation R with two attributes is divided by a relation S with one attribute
- The result is a relation consisting of the attribute which was not in S - 14"Traditional" Set Operators- A Union B
- A Intersection B
- Difference (or A Minus B) - 15Union Compatibility- Same number of Attributes
- Each corresponding pair of attributes is compatible. (Positional correspondence) - 16UnionR U S
Produces a relation that includes all the tuples in R or S or Both. - 17Difference (or Minus)R - S
Produces a relation that includes all the tuples that are in R but not in S. - 18IntersectionR n S
Produces a relation that includes all the tuples in both R and S. - 19Aggregation and Grouping OperatorsMain aggregate functions are: COUNT, SUM, AVG, MIN and MAX.
- 20Eg. How many students are enrolled in the Semester 2, 2013 offering of ICT218?COUNT StudentNo (RESTRICT UnitCode =‘ICT218’ AND Year = 2013 AND Semester = ‘S2’ (Enrolment))
- 21What is SQL?- SQL is a standard non-procedural language.
- Uses table, row, column in place of relation, tuple and attribute. - 22BASIC SQL SELECTSELECT {list of column expressions}
FROM {list of tables and join operations}
WHERE {list of logical expressions for rows}
GROUP BY {list of grouping columns}
HAVING {list of logical expressions for groups}
ORDER BY {list of sorting specifications} - 23RESTRICT in SQLThe WHERE clause allows us to retrieve only specified rows from a table.
- 24Eg. “List the names of students with a StdGPA >= 3.0”SELECT StdFirstName, StdLastName, StdGPA
FROM Student
WHERE StdGPA >= 3.0; - 25Standard Comparison Operators:= equal to
< less than
> greater than
<= less than or equal to
>= greater than or equal to
<> or != not equal - 26The WHERE clause can also contain:Arithmetic operators (+, -, *, /, **)
Logical operators (AND, NOT, OR)
Range search (BETWEEN/AND)
LIKE – used for inexact matching
ANY and ALL (used with subqueries) - 27Eg. “List the names of students with a GPA >= 3.0 and who are in the Games Tech major”SELECT StdFirstName, StdLastName, StdGPA, StdMajor
FROM Student
WHERE StdGPA >= 3.0
AND StdMajor = ‘Games Tech'; - 28Other logical operators- Is null, is not null
- 29Eg. “List the name of any student that does not have a GPA”SELECT StdFirstName, StdLastName
FROM Student
WHERE StdGPA IS NULL; - 30Eg. “List the names of students who are from Koondoola, Girrawheen, or Balga”SELECT StdFirstName, StdLastName, StdCity
FROM Student
WHERE StdCity IN (‘Koondoola’, ’Girrawheen’,’Balga’); - 31Pattern MatchingMatching against a pattern
- Use LIKE in the WHERE clause - 32Eg. “List the unit code and title of units with Finance in their description”SELECT UnitCode, UnitTitle
FROM Unit
WHERE UnitTitle LIKE "*Finance*"; - 33Eg. Range Searching “List the names and GPA of students with a GPA between 3 and 4”SELECT StdFirstName, StdLastName, StdGPA
FROM Student
WHERE StdGPA BETWEEN 3 AND 4;
Note that BETWEEN/AND uses >= and <= - 34Subqueries- A query that appears in the WHERE or HAVING clause of another query.
- Can be used with comparison operators, in/not in, exists/ not exists, any/all. - 35Eg. “List the students who are currently studying ICT218”SELECT StudentNo, FirstName, LastName
FROM Student
WHERE StudentNo IN
(SELECT StudentNo
FROM Enrolment
WHERE UnitCode = ‘ICT218’
and YearSemester = ‘S2 2014’); - 36Eg. “List the students that are NOT enrolled in any units in S2 2014”SELECT StudentNo, StdFirstName, StdLastName
FROM Student
WHERE StudentNo NOT IN
(SELECT StudentNo
FROM Enrolment
AND YearSemester = ‘S2 2014’); - 37SQL Joins- Using JOIN operator
- Matching Primary and Foreign key values in the WHERE clause - 38Example Join using JOIN operatorSELECT {list of column expressions}
FROM Table1 INNER JOIN Table2
ON Table1.PrimaryKey = Table2. ForeignKey
WHERE {list of logical expressions for rows} - 39Eg. List the Names of students enrolled in ICT218 in Semester 2, 2014SELECT StdFirstName, StdLastName
FROM Student INNER JOIN Enrolment
ON Student.StudentNo = Enrolment.StudentNo
WHERE UnitCode = ‘ICT218’
AND YearSemester = ‘S2 2014”; - 40JOINS based on PK/FK EqualitySELECT
FROM Table1, Table2
WHERE Table1.PrimaryKey=Table2.ForeignKey - 41Eg. List the Names of students enrolled in ICT218 in Semester 2, 2014SELECT StdFirstName, StdLastName
FROM Student, Enrolment
WHERE Student.StudentNo = Enrolment.StudentNo
AND UnitCode = ‘ICT218’
AND YearSemester = ‘S2 2014”; - 42Subqueries and JoinsSubqueries can be used to express JOINS as long as the columns in the SELECT statement are from a SINGLE table.
- 43Eg. “List the titles of units being offered in Semester 2 2014”SELECT UnitTitle FROM Unit
WHERE UnitCode IN
(SELECT UnitCode
FROM Offering
WHERE Offering.UnitCode = Unit.UnitCode
AND YearSemester = ‘S2 2014’; - 44EXISTS and NOT EXISTS example “Retrieve the StaffNumber, the name, school , and salary of staff members who are NOT students.”SELECT StaffNo, LastName, SchoolName
FROM Staff
WHERE NOT EXISTS
( SELECT * FROM Student
WHERE Student.StudentNo= Staff.StaffNo); - 45Aggregate FunctionsCOUNT, MIN, MAX, SUM, AVG
- 46COUNTUsing COUNT(*) returns the number of rows retrieved by the query.
SELECT COUNT(*)
FROM Student; - 47ASThe result column can be renamed AS
SELECT Avg(StdGPA) AS AverageGPA,
FROM Student; - 48Aggregate Functions - WHEREAggregate functions can't be used in the WHERE clause directly:
SELECT StdFirstName, StdLastName
FROM Student
WHERE StdGPA > AVG(StdGPA);
*DOES NOT WORK - 49Instead, you need to use a subquerySELECT StdFirstName, StdLastName
FROM Student
WHERE StdGPA >
(SELECT AVG(StdGPA)
FROM Student); - 50GROUP BYGROUP BY 'separates' the rows of a table into groups that have the same value for a specified attribute.
Eg. Students can be separate into groups depending on their Major. - 51Eg. “List the minimum, maximum and average GPA for each of the majors”SELECT StdMajor, MIN(StdGPA) AS MinimumGPA, MAX(StdGPA) AS MaximumGPA, AVG(StdGPA) AS AverageGPA
FROM Student
GROUP BY StdMajor; - 52GROUP BY - using HAVINGHAVING is used to select GROUPS in the same way that WHERE is used to select ROWS.
- 53Eg. E.g. “List the majors and the average GPA of majors where the GPA is greater than 3.3”SELECT StdMajor, AVG(StdGPA)
FROM Student
GROUP BY StdMajor
HAVING AVG(StdGPA) > 3.3; - 54Set OperatorsUNION, INTERSECTION, MINUS
- As with relational algebra, the two queries MUST be UNION COMPATIBLE.
- Duplicates are eliminated from the result. - 55UNIONWe need to construct two queries.
The result is the UNION of the first and second queries. - 56INTERSECTRetrieves rows that are in A AND B.
- 57MINUS (Difference)Retrieves the rows in A but not B.
- 58Eg. “List the students who have been enrolled in ICT231 but not ICT218”SELECT StudentNo
FROM ClassList
WHERE UnitCode = ICT231
MINUS
SELECT StudentNo
FROM ClassList
WHERE UnitCode = ICT218; - 59DIVISIONThe relational algebra division operator is not directly implemented in SQL.
It can be implemented:
- Using nested NOT EXISTS
- Using COUNT - 60Data Definition Language (DDL)It is used to create and modify the structure of database objects in database.
- 61DDLCREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value. - 62Eg. CREATE TABLECREATE TABLE Customer
(CustNo CHAR(8) CONSTRAINT CustPK PRIMARY KEY ,
CustFirstName VARCHAR2(20) CONSTRAINT FNameNN NOT NULL,
CustLastName VARCHAR2(30) NOT NULL,
CustStreet VARCHAR2(50),
CustCity VARCHAR2(30),
CustZip CHAR(10),
CustBal DECIMAL(12,2) DEFAULT 0); - 63Eg. CREATE TABLE ... ASCREATE TABLE CSStudents AS
SELECT *
FROM Student
WHERE StdMajor = ‘CS’; - 64ALTER TABLEModifies the table structure after it has been created.
- Add/drop columns
- Modify column definitions
- Add/drop constraints - 65Eg. 'Remove not null constraint for CustFirstName'ALTER TABLE Customer
DROP CONSTRAINT FNameNN; - 66DROP TABLERemoves named table and all rows within it.
Eg. DROP TABLE Customer; - 67CONSTRAINTSThere are a number of constraints that can be specified in SQL, including:
- Required
-Unique
-Entity integrity
-Referential integrity - 68Required data constraintCan be implemented in SQL table definition using NOT NULL.
Eg. CustLastName VARCHAR2(30) NOT NULL, - 69UniquenessCan specify a particular column (or combination of columns) will only allow unique values.
Eg. CustEmail VARCHAR2(25) CONSTRAINT UniqueEmail UNIQUE, - 70Entity integrityThe entity integrity constraint says that primary values are unique and cannot be null. These are automatically enforced for a primary key - you don't have to define them separately.
Eg. CONSTRAINT Cust_Artist_PK PRIMARY KEY (CustNo, ArtistID) - 71Referential integritySQL allows you to specify actions in the constraint definition:
Eg. CONSTRAINT ArtistFK FOREIGN KEY (ArtistID)
REFERENCES ARTIST(ArtistID) - 72Referential ActionsNO ACTION: prevent deletion if referential integrity would be violated
SET NULL: set to null
SET DEFAULT: set to a default value defined for that field
CASCADE: cascade the update/delete - 73Eg. Oracle allows you to specify only ‘on delete cascade’ in the constraint definition.CONSTRAINT ArtistFK FOREIGN KEY (ArtistID)
REFERENCES ARTIST(ArtistID)
ON DELETE CASCADE); - 74Data Manipulating Language (DML)It is used to retrieve, store, modify, delete, insert and update data in database.
- 75DMLSELECT – Retrieves data from a table
INSERT – Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table - 76INSERTINSERT adds a single row (record) to the table.
- 77Eg. INSERT INTO TableName [(columnList)] VALUES (dataValueList);INSERT INTO Artist
VALUES (1, ‘Miro’,’Joan’,’Spanish’,1893, 1983); - 78INSERT rows from other tablesINSERT may be used to add several rows as the result of a SELECT from another table(s)
INSERT INTO TableName [(columnList)] SELECT ….FROM ……WHERE …. - 79UPDATEUPDATE is used to modify the attribute values in selected rows.
Eg. Rows are selected using WHERE:
UPDATE Artist
SET DateOfDeath = 1984
WHERE ArtistID = 1; - 80Eg. If no WHERE clause specified all rows are updated.UPDATE Trans
SET AskingPrice = 1.1*AskingPrice; - 81DELETERows defined by a WHERE clause are deleted:
Eg. DELETE FROM Artist
WHERE LastName = ‘Tobey’; - 82Eg. If no WHERE clause if specified all rows are deleted.DELETE FROM Artist;
*Note that you can’t delete columns, only rows – deleting a column is altering the structure of a table.