cs2300: file structures and introduction to...
TRANSCRIPT
![Page 1: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/1.jpg)
CS2300: File Structures and
Introduction to Database Systems
Lecture 15: SQL
Doug McGeehan
1
![Page 2: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/2.jpg)
2
Brief Review
• Create tables
– Specify various constraints on table and attributes
• Drop tables
– RESTRICT• Drop table if it is not referenced
– CASCADE• Drop constraints that depend on the table
• Then drop data and remove table
![Page 3: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/3.jpg)
3
Brief Review
• Create tables
– Specify various constraints on table and attributes
• Drop tables
– Note for MySQL / MariaDB:• RESTRICT and CASCADE have no effect
• DROP TABLE eliminates referential integrity constraints
i.e. foreign keys revert back to plain attributes
![Page 4: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/4.jpg)
Creating Your Database
• In MySQL / MariaDB
– Root account
• Allows control over entire database
• Password is set up upon installation
• Permits the creation of new databases
– Unlikely you’ll be able to use MySQL
databases on the CLC Linux Machines
– Install MySQL / MariaDB server on your
own systems4
![Page 5: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/5.jpg)
Creating Your Database
• Creating a Database in MySQL/MariaDB
– Log in with the root account
$ mysql --user root --passwordEnter password:mysql>
5
![Page 6: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/6.jpg)
Creating Your Database
• Creating a Database in MySQL/MariaDB
mysql> CREATE DATABASE CS2300;Query OK, 1 row affected (0.00 sec)
mysql> USE DATABASE CS2300;Database changedmysql>
6
![Page 7: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/7.jpg)
Creating A User
• Adding users is more secure than
accessing database through root user
mysql> GRANT ALL PRIVILEGES -> ON CS2300.* TO->'your_project_name'@->'localhost' IDENTIFIED BY->'some_password';
• Ctrl + D to exit 7
![Page 8: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/8.jpg)
Creating A User
• Log in with your newly created user
$ mysql -u your_project_name\–p[some_password]\CS2300
mysql>
• You are in.
8
![Page 9: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/9.jpg)
Insert, Delete and Update
Data in a Table
![Page 10: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/10.jpg)
Inserting a Row
To insert a row into the Employee table
Employee(id:integer,fname:varchar(20),gender:varchar(1),
deptnumber:integer, salary:integer)
• Approach 1:– Number of values equals airty (degree/column count) of table
– Order and types of attributes match table schema
– Result: No need to specify attribute names on Employee
SQL> Insert Into Employee
Values (122,‘Bob','M',12,110000);
![Page 11: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/11.jpg)
Inserting a Row
To insert a row into the Employee table
Employee(id:integer,fname:varchar(20),gender:varchar(1),
deptnumber:integer, salary:integer)
• Approach 2:– Not all attribute values are defined for insertion
– Result: specify which attributes are being assigned
SQL> Insert Into Employee(id,deptnumber)Values(123,13);
![Page 12: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/12.jpg)
Inserting a Row
• Approach 3:
– Use a SQL query to retrieve data from other tables
– Then insert them into target table
SQL> Insert Into Employee(id,fname)
Select [attr1], [attr2]
From [table_name]
Where [condition];
![Page 13: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/13.jpg)
Inserting a Row
The insertion operation will be rejected if it
violates the constraints specified in the
table definition.
![Page 14: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/14.jpg)
Deleting Rows
• General format:
DELETE FROM Table WHERE Condition;
• Deletes all rows satisfying Condition
from Table
![Page 15: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/15.jpg)
Example
• Remove the employee with id 121 from the Employee
table:
DELETE FROM Employee WHERE id = 121;
• Remove all employees having a salary greater than
100,000:
DELETE FROM Employee WHERE Salary > 100000;
• Remove all records in the Employee tableDELETE FROM Employee;
![Page 16: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/16.jpg)
Deleting Rows
• The Condition can also involve SQL
queries.
• Example:
DELETE FROM Employee
WHERE Deptnumber IN (Select Dnumber
From Department
Where Dname = `Research’);
![Page 17: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/17.jpg)
Updating Rows
• We can update rows in a table
• General format:
UPDATE Table
SET Column1=value1,…,ColumnN=valueN
WHERE Condition
![Page 18: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/18.jpg)
Example
• Now we can reduce salaries instead of firing employees:
UPDATE Employee
SET Salary = 100000
WHERE Salary > 100000;
![Page 19: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/19.jpg)
Updating Rows
• The Condition can also involve SQL
queries.
• Example:
UPDATE Employee
SET Salary = Salary*1.1
WHERE Deptnumber IN (Select Dnumber
From Department
Where Dname = `Research’);
![Page 20: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/20.jpg)
Try This Command
• Show the structure of a table
desc table_name;
![Page 21: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/21.jpg)
2121
Comments?
CREATE TABLE Employee(
ID INTEGER,
Fname VARCHAR(20),
Lname VARCHAR(20),
Gender CHAR(1),
Salary INTEGER NOT NULL,
Dept INTEGER,
PRIMARY KEY(FNAME,LNAME)
);
CREATE TABLE Employee(
ID INTEGER,
Fname VARCHAR(20) PRIMARY KEY,
Lname VARCHAR(20) PRIMARY KEY,
Gender CHAR(1),
Salary INTEGER NOT NULL,
Dept INTEGER,
);
wrong
![Page 22: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/22.jpg)
Some more constraints in
CREATE TABLE
22
![Page 23: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/23.jpg)
23
CHECK Conditions
• A check condition is a Boolean expression:
– E.g.: x>5 and x<10
• Attribute-Based Check
• Tuple-Based Check
![Page 24: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/24.jpg)
24
Attribute-Based Check
• Follow an attribute by a condition that
must hold for that attribute in each tuple
of its relation.
• Condition is checked only when the
associated attribute changes (i.e., an
insert or update occurs).
![Page 25: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/25.jpg)
25
Attribute-Based Check
CREATE TABLE Employee(ID INTEGER primary key,Fname VARCHAR(20),Lname VARCHAR(20),Gender CHAR(1) check(Gender=‘F’ or
Gender=‘M’),Salary INTEGER check(Salary>=0),DeptNumber INTEGER
);
The check occurs only when we insert or update an
Employee tuple, not when we delete a tuple.
![Page 26: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/26.jpg)
26
Tuple-Based Check
• Separate element of table declaration.
• Checked whenever a tuple is inserted or
updated.
![Page 27: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/27.jpg)
27
Example
• Only Joe's Bar can sell beer for more
than $5.
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20),price REAL,PRIMARY KEY (bar,beer),
CHECK (bar = 'Joe''s Bar' OR price <= 5.00));
![Page 28: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/28.jpg)
28
Altering Tables
• Table definitions can be altered after their creation
• Use the reserved words ALTER TABLE• Options:
– Adding columns: ADD COLUMN– Changing columns’ definition: ALTER COLUMN– Dropping columns: DROP COLUMN– Adding or dropping constraints: ADD, DROP
CONSTRAINT– And more…
![Page 29: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/29.jpg)
29
Altering Tables
• Adding a column:
ALTER TABLE Employee ADD COLUMN mname VARCHAR(20);
• Changing columns’ definition:
ALTER TABLE Employee Modify mname varchar(10);
• Dropping columns:
ALTER TABLE Employee DROP COLUMN Mname CASCADE;
• Adding constraints:
ALTER TABLE Employee ADD check(age>0);
![Page 30: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/30.jpg)
30
SQL Queries
![Page 31: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/31.jpg)
31
Basic SQL Query Structure
SELECT Attributes
FROM relations
WHERE condition
SELECT sid,sname
FROM students
WHERE sid=1122
For example
![Page 32: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/32.jpg)
32
Query Components
• A query can contain the following clauses
– Select
– From
– Where
– Group by
– Having
– Order by
• Only Select and From are obligatory
• Order of clauses is always as above
![Page 33: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/33.jpg)
33
Very Basic SQL Query
SELECT [Distinct] Attributes
FROM relations
•Attributes: The attributes which will appear in the
query result (For example: id, name).
•Relations: Relations to perform the query on.
•DISTINCT: Optional keyword to delete duplicates
![Page 34: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/34.jpg)
34
StudentID StudentDept StudentName StudentAge
1123 Math Moshe 25
2245 Computers Mickey 26
55611 Math Menahem 29
Select studentID, studentName
From students
StudentID StudentName
1123 Moshe
2245 Mickey
55611 Menahem
Example
![Page 35: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/35.jpg)
35
Basic SQL Query
SELECT [Distinct] Attributes
FROM relations
WHERE condition
•condition:
– A Boolean condition
e.g. eid>21, or ename=‘Yuval’
– Only tuples satisfying condition
appear in the result
![Page 36: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/36.jpg)
36
StudentID StudentDept StudentName StudentAge
1123 Math Moshe 20
2245 NULL Mickey 18
55611 Math Menahem 21
Select studentID, studentName
From students
Where StudentDept=`Math’
StudentID StudentName
1123 Moshe
55611 Menahem
Example
![Page 37: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/37.jpg)
37
The WHERE Clause
• Numerical and string comparison:
!=,<>,=, <, >, >=, <=, between(val1 AND val2)
• Logical components: AND, OR
• Null verification: IS NULL, IS NOT NULL
• Checking against a list with IN, NOT IN
37
![Page 38: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/38.jpg)
38
Example
SELECT StudentName
FROM Students
WHERE age>=17 AND StudentDept IS NOT NULL ;
StudentID StudentDept StudentName StudentAge
1123 Math Moshe 20
2245 NULL Mickey 18
55611 Math Menahem 21
38
![Page 39: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/39.jpg)
39
Example
SELECT StudentID, StudentName
FROM Students
WHERE StudentID IN (1123,2245) OR
StudentName between(‘Alice’ and ‘Karl’);
StudentID StudentDept StudentName StudentAge
1123 Math Moshe 20
2245 NULL Mickey 18
55611 Math Bob 21
39
![Page 40: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/40.jpg)
40
The LIKE Operator
• A pattern matching operator (regular
expression)
• Basic format: colname LIKE pattern
• Example:
_ is a single character % is 0 or more characters
SELECT StudentName
FROM Students
WHERE StudentName LIKE ‘M%e_’;
40
![Page 41: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/41.jpg)
4141
A Few SELECT Options
• Select all columns:SELECT * FROM Students;
• Applying functions (e.g., Mathematical manipulations)
SELECT 2010-StudentAge
FROM Students;
• Rename relations and selected columns:SELECT StudentName AS Name
FROM Students [AS] S; (here AS is optional)
41
![Page 42: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/42.jpg)
What if more than one relation
are needed for a query?
42
![Page 43: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/43.jpg)
4343
Example
43
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
sid bid day
22 101 10/10/96
58 103 11/12/96
Find the names of the sailors who reserved a boat.
ReserveSailor
Πsname ( sailor.sid=reserve.sid(Sailor x Reserve))
![Page 44: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/44.jpg)
44
Basic SQL Query
• If more than one relation is mentioned, the
operation is on the Cartesian product
between the relations
• Example:
Select sname
From sailor, reserve
Where sailor.sid=reserve.sid
![Page 45: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/45.jpg)
4545
Relation Naming
SELECT S.sname
FROM Sailors [AS] S, Reserves [AS] R
WHERE S.sid = R.sid;
• Naming relations is good style
• It is necessary if the same relation appears
twice in the FROM clause
45
![Page 46: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/46.jpg)
46
SQL and Relational Algebra
SELECT Distinct A1,…,An
FROM R1,…,Rm
WHERE C;
A1,…,An(C(R1 x R2…x Rm))
![Page 47: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/47.jpg)
47
Basic SQL Query
SELECT [Distinct] attributes
FROM relations
WHERE condition;
Important! The evaluation order is:
1. Compute the cross product of the tables in relations.
2. Delete all rows that do not satisfy condition.
3. Delete all columns that do not appear in attributes.
4. If Distinct is specified, eliminate duplicate rows.
![Page 48: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/48.jpg)
48
Sailor Reserve
sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/96
22 Dustin 7 45.0 58 103 11/12/96
31 Lubber 8 55.5 22 101 10/10/96
31 Lubber 8 55.5 58 103 11/12/96
58 Rusty 10 35.0 22 101 10/10/96
58 Rusty 10 35.0 58 103 11/12/96
Stage 1: Sailor x Reserve
![Page 49: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/49.jpg)
49
Sailor Reserve
sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/96
58 Rusty 10 35.0 58 103 11/12/96
Stage 2: “where sailor.sid=reserve.sid”
![Page 50: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/50.jpg)
50
Sname
Dustin
Rusty
Stage 3: “select sname”
Final answer
![Page 51: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/51.jpg)
51
Example Query
SELECT sname AS Sailor_Name
FROM Sailor S, Reserve R
WHERE S.sid = R.sid and
bid = 103;
Sailor(sid, sname, rating, age)
Reserve(sid,bid,day)
Q: Find the names of sailors who reserved boat 103
Don’t forget to include the expression highlighted by red color
![Page 52: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/52.jpg)
52
Sailor Reserve
sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/96
22 Dustin 7 45.0 58 103 11/12/96
31 Lubber 8 55.5 22 101 10/10/96
31 Lubber 8 55.5 58 103 11/12/96
58 Rusty 10 35.0 22 101 10/10/96
58 Rusty 10 35.0 58 103 11/12/96
WHERE S.sid = R.sid and bid = 103;
![Page 53: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/53.jpg)
53
Sailors Reserves
sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/96
22 Dustin 7 45.0 58 103 11/12/96
31 Lubber 8 55.5 22 101 10/10/96
31 Lubber 8 55.5 58 103 11/12/96
58 Rusty 10 35.0 22 101 10/10/96
58 Rusty 10 35.0 58 103 11/12/96
Select sname
![Page 54: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/54.jpg)
Exercise
• Suppliers(sid, sname, address)
• Parts(pid,pname,color)
• Catalog(sid,pid,cost)
Write the SQL query to find the names of
the suppliers who produce red parts.
54
![Page 55: CS2300: File Structures and Introduction to …web.mst.edu/~djmvfb/courses/cs2300/static/media/cs2300...CS2300: File Structures and Introduction to Database Systems Lecture 15: SQL](https://reader033.vdocuments.us/reader033/viewer/2022060322/5f0d5b6b7e708231d439f331/html5/thumbnails/55.jpg)
What’s Next…
• More SQL
• Project Phase 2: due Tuesday Nov 6th
at 11:59pm on Canvas
• Homework 3 assigned
– Due Thursday, Nov 1st
– Hard copy in class, Digital copy on Canvas
• Quiz 3: Thursday, Nov 1st
55