• ¿Documento equivocado? Cámbialo gratis
  • Escrito por estudiantes que aprobaron
  • Inmediatamente disponible después del pago
  • Leer en línea o como PDF
Vender
¿Dónde estudias?
Tu idioma
Document preview thumbnail
Vista previa 3 fuera de 16 páginas
Notas de lectura

concept of sql

Document preview thumbnail
Vista previa 3 fuera de 16 páginas

it gives a detail of sql

Vista previa del contenido

Outline

 Join Expressions
 Views
 Transactions
 Integrity Constraints
 SQL Data Types and Schemas
 Index Definition in SQL
Chapter 4 : Intermediate SQL  Authorization




Database System Concepts, 7th Ed.


LICET, CSE 4.2 ©Silberschatz, Korth, Sudarshan and GK




Joined Relations Natural Join in SQL

 Join operations take two relations and return as a result another  Natural join matches tuples with the same values for all common
attributes, and retains only one copy of each common column.
relation.
 A join operation is a Cartesian product which requires that tuples in the  List the names of instructors along with the course ID of the courses that
they taught
two relations match (under some condition). It also specifies the
attributes that are present in the result of the join • select name, course_id
from students, takes
 The join operations are typically used as subquery expressions in the
where student.ID = takes.ID;
from clause
 Three types of joins:  Same query in SQL with “natural join” construct
• Natural join • select name, course_id
from student natural join takes;
• Inner join
• Outer join




LICET, CSE 4.3 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.4 ©Silberschatz, Korth, Sudarshan and GK

, Natural Join in SQL (Cont.) Student Relation

 The from clause can have multiple relations combined using natural join:
select A1, A2, … An
from r1 natural join r2 natural join .. natural join rn
where P ;




LICET, CSE 4.5 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.6 ©Silberschatz, Korth, Sudarshan and GK




Takes Relation student natural join takes




LICET, CSE 4.7 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.8 ©Silberschatz, Korth, Sudarshan and GK

, Dangerous in Natural Join Natural Join with Using Clause

 Beware of unrelated attributes with same name which get equated  To avoid the danger of equating attributes erroneously, we can use the
incorrectly “using” construct that allows us to specify exactly which columns should be
 Example -- List the names of students instructors along with the titles of equated.
courses that they have taken  Query example
• Correct version select name, title
select name, title from (student natural join takes) join course using (course_id)
from student natural join takes, course
where takes.course_id = course.course_id;
• Incorrect version
select name, title
from student natural join takes natural join course;
 This query omits all (student name, course title) pairs where the
student takes a course in a department other than the student's
own department.
 The correct version (above), correctly outputs such pairs.




LICET, CSE 4.9 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.10 ©Silberschatz, Korth, Sudarshan and GK




Join Condition Join Condition (Cont.)

 The on condition allows a general predicate over the relations being  The on condition allows a general predicate over the relations being
joined joined.
 This predicate is written like a where clause predicate except for the use  This predicate is written like a where clause predicate except for the use
of the keyword on of the keyword on.
 Query example  Query example
select * select *
from student join takes on student_ID = takes_ID from student join takes on student_ID = takes_ID
• The on condition above specifies that a tuple from student matches a • The on condition above specifies that a tuple from student matches a
tuple from takes if their ID values are equal. tuple from takes if their ID values are equal.
 Equivalent to:  Equivalent to:
select * select *
from student , takes from student , takes
where student_ID = takes_ID where student_ID = takes_ID




LICET, CSE 4.11 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.12 ©Silberschatz, Korth, Sudarshan and GK

Libro relacionado
 image
Abraham Silberschatz, Henry F. Korth, S. Sudarshan ISE Database System Concepts
Editorial: 2019 ISBN: 9781260084504 Edición: Desconocido

Información del documento

Subido en
13 de agosto de 2024
Número de páginas
16
Escrito en
2024/2025
Tipo
Notas de lectura
Profesor(es)
Abraham silberschatz, henry f. korth, s. sudharsha
Contiene
Todas las clases
$3.49

¿Documento equivocado? Cámbialo gratis Dentro de los 14 días posteriores a la compra y antes de descargarlo, puedes elegir otro documento. Puedes gastar el importe de nuevo.
Escrito por estudiantes que aprobaron
Inmediatamente disponible después del pago
Leer en línea o como PDF

Vendido
0
Seguidores
0
Artículos
10
Última venta
-



Por qué los estudiantes eligen Stuvia

Creado por compañeros estudiantes, verificado por reseñas

Calidad en la que puedes confiar: escrito por estudiantes que aprobaron y evaluado por otros que han usado estos resúmenes.

¿No estás satisfecho? Elige otro documento

¡No te preocupes! Puedes elegir directamente otro documento que se ajuste mejor a lo que buscas.

Paga como quieras, empieza a estudiar al instante

Sin suscripción, sin compromisos. Paga como estés acostumbrado con tarjeta de crédito y descarga tu documento PDF inmediatamente.

Student with book image

“Comprado, descargado y aprobado. Así de fácil puede ser.”

Alisha Student

Preguntas frecuentes