Databases Topic 2

jaydeeees
Flashcards by jaydeeees, updated more than 1 year ago
jaydeeees
Created by jaydeeees about 5 years ago
85
5

Description

Database Query Languages
Tags

Resource summary

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.
Show full summary Hide full summary

Similar

The Internet
Gee_0599
FLAT FILE VS RELATIONAL DATABASE
rosiejones
Flash Cards Networks
JJ Pro Wrestler
Data & Information
Calum_Weeks
Software testing strategies: Summary
harrymt
CCNA Security 210-260 IINS - Exam 3
Mike M
MICROSOFT WORD 2013 SKILLS FOR WORK
John O'Driscoll
Types and Components of Computer Systems
Jess Peason
Input Devices
Jess Peason
Output Devices
Jess Peason
General ICT Quiz
leahshaw