Содержание
- 2. Querying Data From Tables Query operations facilitate data retrieval from one or more tables. The result
- 3. Review of last lecture The operations of Relational algebra: projection selection union difference intersection
- 4. Join The join operation enables querying information from two or more related tables. It is similar
- 5. Join: example 1 CREATE TABLE Groups ( group_id int PRIMARY KEY, group_name varchar(15)); CREATE TABLE Students
- 6. SELECT stud_id, last_name, group_name FROM Students, Groups WHERE Students.group_id = Groups.group_id; Join: example 1
- 7. table.column format The table.column format used in the above selection condition. This syntax is used to
- 8. Join: example 2 CREATE TABLE Account ( id int PRIMARY KEY, balance int); CREATE TABLE Customer
- 9. Join: example 2 Suppose we want to query the name of the Customer who has Balance
- 10. Join: example 2 The SQL query is: SELECT name FROM Customer, Account WHERE Customer.accountid= Account.id AND
- 11. Join: example 3
- 12. CREATE TABLE Courses ( course_id int PRIMARY KEY, name varchar(30)); CREATE TABLE Teachers ( teach_id int
- 13. SELECT Courses.name, Teachers.name FROM Courses, Teachers, Schedule WHERE Courses.course_id = Schedule.course_id AND Teachers.teach_id = Schedule.teach_id; Join:
- 14. JOIN keyword An SQL JOIN clause is used to combine rows from two or more tables.
- 15. INNER JOIN The most common type of join is SQL INNER JOIN (simple join). An SQL
- 16. INNER JOIN: example SELECT Students.stud_id, Students.fname, Groups.group_name FROM Students INNER JOIN Groups ON Students.group_id = Groups.group_id;
- 17. INNER JOIN: example
- 18. LEFT JOIN The LEFT JOIN keyword returns all rows from the left table (tableA), with the
- 19. LEFT JOIN: example The following SQL statement will return all students, and groups they might have:
- 20. RIGHT JOIN The RIGHT JOIN keyword returns all rows from the right table (tableB), with the
- 21. RIGHT JOIN: example The following SQL statement will return all groups, and students they might have:
- 22. FULL OUTER JOIN The FULL OUTER JOIN keyword returns all rows from the left table (tableA)
- 23. FULL JOIN: example The following SQL statement selects all students and all groups: SELECT Students.stud_id, Students.fname,
- 24. CROSS JOIN “All-to-All”. The SQL CROSS JOIN produces a result set which is the number of
- 25. CROSS JOIN: example SELECT * FROM Students CROSS JOIN Groups; or SELECT * FROM Students, Groups;
- 26. CROSS JOIN: example
- 27. The complete JOIN syntax SELECT Attribute(s) FROM TableA {INNER | {LEFT | RIGHT | FULL} OUTER
- 28. JOIN with USING The USING clause is a shorthand that allows you to take advantage of
- 29. JOIN with USING: example SELECT * FROM Students INNER JOIN Groups USING (group_id); The output of
- 30. NATURAL JOIN NATURAL is a shorthand form of USING: it forms a USING list consisting of
- 31. NATURAL JOIN: example SELECT * FROM Students NATURAL INNER JOIN Groups;
- 32. Notation The operations have their own symbols.
- 33. Books Connolly, Thomas M. Database Systems: A Practical Approach to Design, Implementation, and Management / Thomas
- 34. Question The SQL statement that queries or reads data from a table is ________ . SELECT
- 35. Question The result of a SQL SELECT statement is a(n) ________ . Report Form File Table
- 36. Question Which of the following is the correct order of keywords for SQL SELECT statements? a)
- 37. Question In an SQL SELECT statement querying a single table, the asterisk (*) means that: all
- 38. Question Which of the following SQL clauses specifies a search condition? a) WHERE b) SEARCH c)
- 39. Question Which of the following is used to denote the selection operation in relational algebra ?
- 41. Скачать презентацию