Slide 1 Relation Table name birth gpa grad Anderson 19871022 39 2009 Jones 1990416 24 2012 Hernandez 1989812 31 2011 Chen 199024 32 2011 VARCHAR30 DATE FLOAT ID: 794300
Download The PPT/PDF document "CS 142 Lecture Notes: Relational Databas..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
CS 142 Lecture Notes: Relational Databases
Slide 1
Relation (Table)
namebirthgpagradAnderson1987-10-223.92009Jones1990-4-162.42012Hernandez1989-8-123.12011Chen1990-2-43.22011VARCHAR(30)DATEFLOATINT
Column/Attribute/Field
Row/Tuple/Record
Column Types
Slide2CS 142 Lecture Notes: Relational Databases
Slide 2
Primary Key
idnamebirthgpagrad14Anderson1987-10-223.9200938Jones1990-4-162.4201277Hernandez1989-8-123.12011104Chen1990-2-4
3.22011
INT
VARCHAR(30)DATE
FLOATINT
Unique For Each Row
Slide3CS 142 Lecture Notes: Relational Databases
Slide 3
Basic Table Operations
CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(30), birth DATE, gpa FLOAT, grad INT, PRIMARY KEY(id));INSERT INTO students(name, birth, gpa, grad) VALUES ('Anderson', '1987-10-22', 3.9, 2009);INSERT INTO students(name, birth, gpa, grad) VALUES ('Jones', '1990-4-16', 2.4, 2012);DELETE FROM students WHERE name='Anderson';DROP TABLE students;
Slide4CS 142 Lecture Notes: Relational Databases
Slide 4
Query: Display Entire Table
idnamebirthgpagrad1Anderson1987-10-223.920092Jones1990-4-162.420123Hernandez1989-8-123.120114Chen1990-2-43.2
2011
SELECT * FROM students;
+----+-----------+------------+------+------+| id | name | birth | gpa | grad |+----+-----------+------------+------+------+| 1 | Anderson | 1987-10-22 | 3.9 | 2009 |
| 2 | Jones | 1990-04-16 | 2.4 | 2012 || 3 | Hernandez | 1989-08-12 | 3.1 | 2011 || 4 | Chen | 1990-02-04 | 3.2 | 2011 |+----+-----------+------------+------+------+
Slide5CS 142 Lecture Notes: Relational Databases
Slide 5
Query: Select Columns
idnamebirthgpagrad1Anderson1987-10-223.920092Jones1990-4-162.420123Hernandez1989-8-123.120114Chen1990-2-43.2
2011
SELECT name,
gpa FROM students;+-----------+------+| name | gpa |+-----------+------+
| Anderson | 3.9 || Jones | 2.4 || Hernandez | 3.1 || Chen | 3.2 |+-----------+------+
Slide6CS 142 Lecture Notes: Relational Databases
Slide 6
Query: Filter Rows
idnamebirthgpagrad1Anderson1987-10-223.920092Jones1990-4-162.420123Hernandez1989-8-123.120114Chen1990-2-43.2
2011
SELECT name,
gpa FROM students WHERE gpa > 3.0;+-----------+------+| name | gpa
|+-----------+------+| Anderson | 3.9 || Hernandez | 3.1 || Chen | 3.2 |+-----------+------+
Slide7CS 142 Lecture Notes: Relational Databases
Slide 7
Query: Sort Output
idnamebirthgpagrad1Anderson1987-10-223.920092Jones1990-4-162.420123Hernandez1989-8-123.120114Chen1990-2-43.2
2011
SELECT
gpa, name, grad FROM students WHERE gpa > 3.0 ORDER BY gpa
DESC;+------+-----------+------+| gpa | name | grad |+------+-----------+------+| 3.9 | Anderson | 2009 || 3.2 | Chen | 2011 |
| 3.1 | Hernandez | 2011 |+------+-----------+------+
Slide8CS 142 Lecture Notes: Relational Databases
Slide 8
Update Value(s)
idnamebirthgpagrad1Anderson1987-10-223.920092Jones1990-4-162.420123Hernandez1989-8-123.120114Chen1990-2-4
3.22011
UPDATE students
SET gpa = 2.6, grad = 2013 WHERE id = 2
Slide9CS 142 Lecture Notes: Relational Databases
Slide 9
s.id
s.names.births.gpas.grads.advisor_idp.idp.namep.title1Anderson1987-10-223.9200921Fujimuraassocprof1Anderson1987-10-223.920092
2Bolosky
prof
2Jones
1990-4-16
2.42012
1
1
Fujimura
assocprof
2
Jones
1990-4-16
2.4
2012
1
2
Bolosky
prof
3
Hernandez
1989-8-12
3.1
2011
1
1
Fujimura
assocprof
3
Hernandez
1989-8-12
3.1
2011
1
2
Bolosky
prof
4
Chen
1990-2-4
3.2
2011
1
1
Fujimuraassocprof4Chen1990-2-43.2201112Boloskyprof
idnamebirthgpagradadvisor_id1Anderson1987-10-223.9200922Jones1990-4-162.4201213Hernandez1989-8-123.1201114Chen1990-2-43.220111
Foreign Key
SELECT s.name,
s.gpa FROM students s, advisors p WHERE s.advisor_id = p.id AND p.name = 'Fujimura';
students
advisors
id
name
title
1
Fujimura
assocprof
2
Bolosky
prof
Slide10CS 142 Lecture Notes: Relational Databases
Slide 10
s.id
s.names.births.gpas.grads.advisor_idp.idp.namep.title1Anderson1987-10-223.9200921Fujimuraassocprof1Anderson1987-10-223.920092
2Bolosky
prof
2Jones
1990-4-16
2.42012
1
1
Fujimura
assocprof
2
Jones
1990-4-16
2.4
2012
1
2
Bolosky
prof
3
Hernandez
1989-8-12
3.1
2011
1
1
Fujimura
assocprof
3
Hernandez
1989-8-12
3.1
2011
1
2
Bolosky
prof
4
Chen
1990-2-4
3.2
2011
1
1
Fujimuraassocprof4Chen1990-2-43.2201112Boloskyprof
idnametitle1Fujimuraassocprof2Boloskyprofidnamebirthgpagradadvisor_id1Anderson1987-10-223.9200922Jones1990-4-162.4201213Hernandez1989-8-12
3.12011
1
4Chen1990-2-4
3.22011
1
SELECT s.name,
s.gpa
FROM
students s, advisors p
WHERE
s.advisor_id
= p.id AND p.name = 'Fujimura';
students
advisors
Slide11CS 142 Lecture Notes: Relational Databases
Slide 11
SELECT s.name,
s.gpa FROM students s, advisors p WHERE s.advisor_id = p.id AND p.name = 'Fujimura';+-----------+------+| name | gpa |+-----------+------+| Jones | 2.4 || Hernandez | 3.1 || Chen | 3.2 |+-----------+------+idnamebirthgpagradadvisor_id1Anderson1987-10-223.9200922Jones1990-4-162.420121
3Hernandez
1989-8-12
3.1
2011
14
Chen
1990-2-4
3.2
2011
1
students
advisors
id
name
title
1
Fujimura
assocprof
2
Bolosky
prof
Slide12CS 142 Lecture Notes: Relational Databases
Slide 12
id
namebirthgpagrad1Anderson1987-10-223.920092Jones1990-4-162.420123Hernandez1989-8-123.120114Chen1990-2-4
3.22011
id
numbername
quarter1
CS142
Web stuff
Winter 2009
2
ART101
Finger painting
Fall 2008
3
ART101
Finger painting
Winter 2009
4
PE204
Mud wrestling
Winter 2009
course_id
student_id
1
1
3
1
4
1
1
2
2
2
1
3
2
4
4
4
students
courses
courses_students
SELECT s.name,
c.quarter
FROM students s, courses c,
courses_students cs WHERE c.id = cs.course_id AND s.id = cs.student_id AND c.number = 'ART101';+----------+-------------+| name | quarter |+----------+-------------+| Jones | Fall 2008 || Chen | Fall 2008 || Anderson | Winter 2009 |+----------+-------------+
Slide13CS 140 Lecture Notes: File SystemsSlide 13