3145106
| Question | Answer |
| What 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. |
| Features of Database query language | It should allow user to: - Create the database and structures -Manage the data, Retrieve data This should all happen with: -Minimal effort -Relatively simple syntax |
| What is Relational Algebra? | Codd defined the relational algebra: It transforms one or two relations into a new relation (closure property). |
| Relation Specific Operators | RESTRICT PROJECT CARTESIAN PRODUCT X JOIN * (Full Outer Join, One-Sided Join) DIVISION |
| RESTRICT AND PROJECT | Restrict and Project are similar in that the result of operations using them is a subset of the original relation. |
| The RESTRICT Operator | -Operates on one relation - Produces a subset of the tuples (rows) of a relation -Reffered to in some texts as Select |
| Eg. EMPLOYEE (E#, Name, Age, Salary) 'Restrict to Employees whose age is less than 30' | RESTRICT Age < 30 (EMPLOYEE) |
| The PROJECT Operator | - Operates on one relation - Used to select a subset of attributes (Column) of a relation. |
| Eg. EMPLOYEE (E#, Name, Age, Salary) 'List the names and salaries of all employees' | PROJECT Name, Salary (EMPLOYEE) |
| CARTESIAN 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. |
| NATURAL JOIN Operator | R *join condition S - An operation on two relations, equivalent to a product by a restrict Eg. EMP* EMP.D# = DEPT.D# DEPT |
| OUTER 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. |
| The 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 |
| "Traditional" Set Operators | - A Union B - A Intersection B - Difference (or A Minus B) |
| Union Compatibility | - Same number of Attributes - Each corresponding pair of attributes is compatible. (Positional correspondence) |
| Union | R U S Produces a relation that includes all the tuples in R or S or Both. |
| Difference (or Minus) | R - S Produces a relation that includes all the tuples that are in R but not in S. |
| Intersection | R n S Produces a relation that includes all the tuples in both R and S. |
| Aggregation and Grouping Operators | Main aggregate functions are: COUNT, SUM, AVG, MIN and MAX. |
| Eg. 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)) |
| What is SQL? | - SQL is a standard non-procedural language. - Uses table, row, column in place of relation, tuple and attribute. |
| BASIC SQL SELECT | SELECT {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} |
| RESTRICT in SQL | The WHERE clause allows us to retrieve only specified rows from a table. |
| Eg. “List the names of students with a StdGPA >= 3.0” | SELECT StdFirstName, StdLastName, StdGPA FROM Student WHERE StdGPA >= 3.0; |
| Standard Comparison Operators: | = equal to < less than > greater than <= less than or equal to >= greater than or equal to <> or != not equal |
| The 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) |
| Eg. “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'; |
| Other logical operators | - Is null, is not null |
| Eg. “List the name of any student that does not have a GPA” | SELECT StdFirstName, StdLastName FROM Student WHERE StdGPA IS NULL; |
| Eg. “List the names of students who are from Koondoola, Girrawheen, or Balga” | SELECT StdFirstName, StdLastName, StdCity FROM Student WHERE StdCity IN (‘Koondoola’, ’Girrawheen’,’Balga’); |
| Pattern Matching | Matching against a pattern - Use LIKE in the WHERE clause |
| Eg. “List the unit code and title of units with Finance in their description” | SELECT UnitCode, UnitTitle FROM Unit WHERE UnitTitle LIKE "*Finance*"; |
| Eg. 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 <= |
| Subqueries | - 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. |
| Eg. “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’); |
| Eg. “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’); |
| SQL Joins | - Using JOIN operator - Matching Primary and Foreign key values in the WHERE clause |
| Example Join using JOIN operator | SELECT {list of column expressions} FROM Table1 INNER JOIN Table2 ON Table1.PrimaryKey = Table2. ForeignKey WHERE {list of logical expressions for rows} |
| Eg. List the Names of students enrolled in ICT218 in Semester 2, 2014 | SELECT StdFirstName, StdLastName FROM Student INNER JOIN Enrolment ON Student.StudentNo = Enrolment.StudentNo WHERE UnitCode = ‘ICT218’ AND YearSemester = ‘S2 2014”; |
| JOINS based on PK/FK Equality | SELECT <list of column expressions> FROM Table1, Table2 WHERE Table1.PrimaryKey=Table2.ForeignKey |
| Eg. List the Names of students enrolled in ICT218 in Semester 2, 2014 | SELECT StdFirstName, StdLastName FROM Student, Enrolment WHERE Student.StudentNo = Enrolment.StudentNo AND UnitCode = ‘ICT218’ AND YearSemester = ‘S2 2014”; |
| Subqueries and Joins | Subqueries can be used to express JOINS as long as the columns in the SELECT statement are from a SINGLE table. |
| Eg. “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’; |
| EXISTS 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); |
| Aggregate Functions | COUNT, MIN, MAX, SUM, AVG |
| COUNT | Using COUNT(*) returns the number of rows retrieved by the query. SELECT COUNT(*) FROM Student; |
| AS | The result column can be renamed AS SELECT Avg(StdGPA) AS AverageGPA, FROM Student; |
| Aggregate Functions - WHERE | Aggregate functions can't be used in the WHERE clause directly: SELECT StdFirstName, StdLastName FROM Student WHERE StdGPA > AVG(StdGPA); *DOES NOT WORK |
| Instead, you need to use a subquery | SELECT StdFirstName, StdLastName FROM Student WHERE StdGPA > (SELECT AVG(StdGPA) FROM Student); |
| GROUP BY | GROUP 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. |
| Eg. “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; |
| GROUP BY - using HAVING | HAVING is used to select GROUPS in the same way that WHERE is used to select ROWS. |
| Eg. 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; |
| Set Operators | UNION, INTERSECTION, MINUS - As with relational algebra, the two queries MUST be UNION COMPATIBLE. - Duplicates are eliminated from the result. |
| UNION | We need to construct two queries. The result is the UNION of the first and second queries. |
| INTERSECT | Retrieves rows that are in A AND B. |
| MINUS (Difference) | Retrieves the rows in A but not B. |
| Eg. “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; |
| DIVISION | The relational algebra division operator is not directly implemented in SQL. It can be implemented: - Using nested NOT EXISTS - Using COUNT |
| Data Definition Language (DDL) | It is used to create and modify the structure of database objects in database. |
| DDL | CREATE – 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. |
| Eg. CREATE TABLE | CREATE 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); |
| Eg. CREATE TABLE ... AS | CREATE TABLE CSStudents AS SELECT * FROM Student WHERE StdMajor = ‘CS’; |
| ALTER TABLE | Modifies the table structure after it has been created. - Add/drop columns - Modify column definitions - Add/drop constraints |
| Eg. 'Remove not null constraint for CustFirstName' | ALTER TABLE Customer DROP CONSTRAINT FNameNN; |
| DROP TABLE | Removes named table and all rows within it. Eg. DROP TABLE Customer; |
| CONSTRAINTS | There are a number of constraints that can be specified in SQL, including: - Required -Unique -Entity integrity -Referential integrity |
| Required data constraint | Can be implemented in SQL table definition using NOT NULL. Eg. CustLastName VARCHAR2(30) NOT NULL, |
| Uniqueness | Can specify a particular column (or combination of columns) will only allow unique values. Eg. CustEmail VARCHAR2(25) CONSTRAINT UniqueEmail UNIQUE, |
| Entity integrity | The 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) |
| Referential integrity | SQL allows you to specify actions in the constraint definition: Eg. CONSTRAINT ArtistFK FOREIGN KEY (ArtistID) REFERENCES ARTIST(ArtistID) |
| Referential Actions | NO 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 |
| Eg. Oracle allows you to specify only ‘on delete cascade’ in the constraint definition. | CONSTRAINT ArtistFK FOREIGN KEY (ArtistID) REFERENCES ARTIST(ArtistID) ON DELETE CASCADE); |
| Data Manipulating Language (DML) | It is used to retrieve, store, modify, delete, insert and update data in database. |
| DML | SELECT – 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 |
| INSERT | INSERT adds a single row (record) to the table. |
| Eg. INSERT INTO TableName [(columnList)] VALUES (dataValueList); | INSERT INTO Artist VALUES (1, ‘Miro’,’Joan’,’Spanish’,1893, 1983); |
| INSERT rows from other tables | INSERT may be used to add several rows as the result of a SELECT from another table(s) INSERT INTO TableName [(columnList)] SELECT ….FROM ……WHERE …. |
| UPDATE | UPDATE is used to modify the attribute values in selected rows. Eg. Rows are selected using WHERE: UPDATE Artist SET DateOfDeath = 1984 WHERE ArtistID = 1; |
| Eg. If no WHERE clause specified all rows are updated. | UPDATE Trans SET AskingPrice = 1.1*AskingPrice; |
| DELETE | Rows defined by a WHERE clause are deleted: Eg. DELETE FROM Artist WHERE LastName = ‘Tobey’; |
| Eg. 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. |
Want to create your own Flashcards for free with GoConqr? Learn more.