Transcript DatabasesAndSQLx

Databases and SQL
CSCI 201
Principles of Software Development
Jeffrey Miller, Ph.D.
[email protected]
Outline
• Databases
• SQL
• Try It!
USC CSCI 201L
Databases
▪ Database systems store data and provide a means of
accessing, updating, manipulating, and analyzing data
▪ Databases are stored in files on a file system, but they are
arranged in a manner that allows for fast queries and updates
▪ A Database Management System (DBMS) is designed for
programmers, not casual users
›
›
›
›
›
›
›
›
MySQL
PostgreSQL
Oracle
Microsoft SQL Server
IBM DB2
Apache Hadoop
MongoDB
Firebase
USC CSCI 201L
3/16
Relational Databases
▪ Relational database management systems (RDBMS) provide three things
› Structure – the representation of the data
› Integrity – constraints on the data
› Language – means for accessing and manipulating data
OverallGrades
fname
lname
prefix
num
letterGrade
CSCI
103
Sheldon
Cooper
A
CSCI
104
Howard
Wolowitz
A-
CSCI
201
Leonard
Hofstadter
A
CSCI
201
Howard
Wolowitz
B
EE
101
Howard
Wolowitz
B-
Tables are called relations
Columns are called attributes
Rows are called tuples
Connections are called relationships
USC CSCI 201L
4/16
Relational Databases
Non-normalized
OverallGrades
fname
lname
prefix
num
CSCI
103
Sheldon
Cooper
A
CSCI
104
Howard
Wolowitz
A-
CSCI
201
Leonard
Hofstadter
A
CSCI
201
Howard
Wolowitz
B
EE
101
Howard
Wolowitz
B-
Class
classID
prefix
num
1
CSCI
103
2
CSCI
104
3
CSCI
201
4
EE
101
letterGrade
Student
Normalized
Grades
studentID
fname
lname
1
Sheldon
Cooper
2
Leonard
Hofstadter
gradeID
classID
studentID
letterGrade
3
Howard
Wolowitz
1
1
1
A
4
Rajesh
Koothrappali
2
2
3
A-
3
3
2
A
4
3
3
B
5
4
3
B-
USC CSCI 201L
5/16
Primary and Foreign Keys
▪ A primary key is a column (or a combination of multiple columns) in a table that
provides a unique reference to a row in the table
▪ A foreign key is a link between two tables that uniquely identifies a row in
another table
classID
prefix
num
1
CSCI
103
2
CSCI
104
3
CSCI
201
4
EE
101
5
EE
102
studentID
fname
lname
1
Sheldon
Cooper
2
Leonard
Hofstadter
3
Howard
Wolowitz
4
Rajesh
Koothrappali
gradeID
classID
studentID
letterGrade
1
1
1
A
2
2
3
A-
3
3
2
A
4
3
3
B
5
5
3
B-
6/16
Outline
• Databases
• SQL
• Try It!
SQL
▪ The Structured Query Language (SQL) is the primary language
supported by DBMSs for accessing and manipulating data
▪ Some MySQL SQL statements you should know
›
›
›
›
›
›
›
›
›
SHOW
CREATE DATABASE
USE
CREATE TABLE
INSERT
UPDATE
SELECT
DELETE
DROP
▪ If you are not familiar with SQL, there are many good tutorials
online and our textbook has a good chapter reference as well
USC CSCI 201L
8/16
SQL Scripts
▪ After installing a DBMS, you will be able to run SQL
scripts
▪ SQL scripts are files that contain a collection of SQL
statements that can be used for recreating databases and
populating them with initial or testing data
› This is often used in the testing phase of software engineering
to test different scenarios without requiring the QA engineers
to insert all of the data manually
USC CSCI 201L
9/16
Sample Script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DROP DATABASE if exists StudentGrades;
CREATE DATABASE StudentGrades;
USE StudentGrades;
CREATE TABLE Student (
studentID int(11) primary key not null auto_increment,
fname varchar(50) not null,
lname varchar(50) not null
);
INSERT INTO Student (fname, lname) VALUES ('Sheldon', 'Cooper');
INSERT INTO Student (fname, lname) VALUES ('Leonard', 'Hofstadter');
CREATE TABLE Class (
Class
classID int(11) primary key not null auto_increment,
studentID
classID
prefix
num
prefix varchar(5) not null,
1
1
CSCI
103
num int(4) not null
);
2
2
CSCI
104
INSERT INTO Class (prefix, num) VALUES ('CSCI', 103);
INSERT INTO Class (prefix, num) VALUES ('CSCI', 104);
CREATE TABLE Grade (
Grade
gradeID int(11) primary key not null auto_increment,
gradeID
classID
studentID
classID int(11) not null,
1
1
1
studentID int(11) not null,
2
2
1
letterGrade varchar(2) not null,
FOREIGN KEY fk1(classID) REFERENCES class(classID),
3
1
2
FOREIGN KEY fk2(studentID) REFERENCES student(studentID)
4
2
2
);
INSERT INTO Grade (studentID, classID, letterGrade) VALUES (1, 1, 'A');
INSERT INTO Grade (studentID, classID, letterGrade) VALUES (1, 2, 'A');
INSERT INTO Grade (studentID, classID, letterGrade) VALUES (2, 1, 'A');
INSERT INTO Grade (studentID, classID, letterGrade) VALUES (2, 2, ‘B');
Student
fname
lname
Sheldon
Cooper
Leonard
Hofstadter
letterGrade
A
A
A
B
USC CSCI 201L
10/16
SQL Visualization Tools
▪ A visualization tool connects to a database and allows
execution of SQL statements
› The tool then will graphically display the results
▪ Free MySQL Clients
›
›
›
›
Command line client (Windows, Mac, Linux)
MySQL Workbench (Windows, Mac, Linux)
Sequel Pro (Mac)
Toad for MySQL (Windows)
USC CSCI 201L
11/16
Executing SQL
▪ There are a few ways to execute SQL statements, which
typically depend on the DBMS
▪ We often want to be able to access databases from within
programs though
› This can be to insert, update, delete, or query the data
▪ The Java Database Connectivity (JDBC) drivers allow us to
embed SQL in our Java code and execute those statements on
a database programmatically
USC CSCI 201L
12/16
Outline
• Databases
• SQL
• Try it!
USC CSCI 201L
SQL Queries
▪ Write SQL code to do the following
› Print out all of the classes offered
› Print out all of the students without the studentID value
› Print out the name, grade, and class for all students in all of the
classes
› Print out the name and grade for each student in CSCI 103
› Print out the class and grades for Sheldon Cooper
USC CSCI 201L
14/16
SQL Queries Solution
▪ Write SQL code to do the following
›
Print out all of the classes offered
• SELECT * FROM class;
›
Print out all of the students without the studentID value
• SELECT fname, lname FROM student;
›
Print out the name, grade, and class for all students in all of the classes
• SELECT s.fname, s.lname, c.prefix, c.num, g.letterGrade FROM student
s, grade g, class c WHERE c.classID=g.classID AND
s.studentID=g.studentID ORDER BY s.fname, s.lname, c.prefix, c.num;
›
Print out the name and grade for each student in CSCI 103
• SELECT s.fname, s.lname, g.letterGrade FROM student s, grade g, class
c WHERE s.studentID=g.studentID AND g.classID=c.classID AND
c.prefix='CSCI‘ AND c.num=201;
›
Print out the class and grades for Sheldon Cooper
• SELECT c.prefix, c.num, g.letterGrade FROM student s, grade g, class c
WHERE c.classID=g.classID AND s.studentID=g.studentID AND
s.fname='Sheldon' AND s.lname='Cooper';
USC CSCI 201L
15/16
SQL Statements
▪ Write SQL code to do the following
›
›
›
›
›
Insert CSCI 170 into the database
Change Leonard’s grade in EE 101 to a C+
Add Amy Farrah Fowler into the Student table
Give Amy a grade of A for CSCI 103
Give Amy a grade of B+ for CSCI 170
USC CSCI 201L
16/16
SQL Queries Solution
▪ Write SQL code to do the following
›
Insert CSCI 170 into the database
• INSERT INTO class (prefix, num) VALUES (‘CSCI’, 170);
›
Change Leonard’s grade in EE 101 to a C+
• UPDATE grade g, student s, class c SET g.letterGrade='C+' WHERE
s.studentID=g.studentID AND s.fname='Leonard' AND s.lname='Hofstadter'
AND g.classID=c.classID AND c.prefix='EE' AND c.num='101';
›
Add Amy Farrah Fowler into the Student table
• INSERT INTO student (fname, lname) VALUES ('Amy', 'Farrah Fowler');
›
Give Amy a grade of A for CSCI 103
• INSERT INTO grade (classID, studentID, letterGrade) VALUES ((SELECT
classID FROM class WHERE prefix='CSCI' AND num=103), (SELECT studentID
FROM student WHERE fname='Amy' AND lname='Farrah Fowler'), 'A');
›
Give Amy a grade of B+ for CSCI 170
• INSERT INTO grade (classID, studentID, letterGrade) VALUES ((SELECT
classID FROM class WHERE prefix='CSCI' AND num=170), (SELECT studentID
FROM student WHERE fname='Amy' AND lname='Farrah Fowler'), ‘B+');
USC CSCI 201L
17/16