Introduction to SQL Workshop at University of California, Berkeley School of Information
"Explore the basics of SQL through this workshop at University of California, Berkeley School of Information. Learn about database management, relational models, querying, and more with hands-on exercises. Get a comprehensive overview of SQL syntax and its applications in various database systems."
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
SQL Workshop University of California, Berkeley School of Information IS 257: Database Management IS 257 Fall 2015 2015-09-10 SLIDE 1
Announcements IS 257 Fall 2015 2015-09-10 SLIDE 2
Lecture Outline Review The Relational Model Revisited Introduction to SQL How to Connect to MySQL Via phpMyAdmin Via Terminal and MySQL program Installing the Diveshop Database Running queries Via phpMyAdmin Via MySQL on Terminal IS 257 Fall 2015 2015-09-10 SLIDE 3
SQL Structured Query Language Used for both Database Definition, Modification and Querying Basic language is standardized across relational DBMS s. Each system may have proprietary extensions to standard. Relational Calculus combines Restrict, Project and Join operations in a single command. SELECT. IS 257 Fall 2015 2015-09-10 SLIDE 4
SELECT Syntax: SELECT [DISTINCT] attr1, attr2, , attr3 FROM rel1 r1, rel2 r2, rel3 r3 WHERE condition1 {AND | OR} condition2 ORDER BY attr1 [DESC], attr3 [DESC] IS 257 Fall 2015 2015-09-10 SLIDE 5
SELECT Syntax: SELECT a.`Author Name`, b.Title FROM authors a, books b WHERE a.Authorid = b.Authorid ORDER BY a.`Author name` ; authors pubid pubname 1 Harper 2 Addison 3 Oxford 4 Que Authorid Author nam 1 Smith 2 Wynar 3 Jones 4 Duncan 5 Applegate books Book ID Title 1 Introductio 2 The history 3 New stuff a 4 Another titl 5 And yet mo pubid Author id 2 4 3 2 1 1 2 3 4 5 Book ID Subid 1 2 3 4 4 2 1 3 2 3 Subid Subject 1 cataloging 2 history 3 stuff IS 257 Fall 2015 2015-09-10 SLIDE 6
SELECT Conditions = equal to a particular value >= greater than or equal to a particular value > greater than a particular value <= less than or equal to a particular value <> not equal to a particular value LIKE %term% (may be other wild cards in other systems) IN ( opt1 , opt2 , , optn ) BETWEEN val1 AND val2 IS NULL IS 257 Fall 2015 2015-09-10 SLIDE 7
Relational Algebra Restrict using SELECT Syntax: SELECT * WHERE condition1 {AND | OR} condition2; IS 257 Fall 2015 2015-09-10 SLIDE 8
Relational Algebra Projection using SELECT Syntax: SELECT [DISTINCT] attr1, attr2, , attr3 FROM rel1 r1; IS 257 Fall 2015 2015-09-10 SLIDE 9
Relational Algebra Join using SELECT Syntax: SELECT * FROM rel1 r1, rel2 r2 WHERE r1.linkattr = r2.linkattr ; (Natural or Inner) Join A1 B1 C1 A2 B1 C1 A3 B2 C2 A1 B1 A2 B1 A3 B2 B1 C1 B2 C2 B3 C3 IS 257 Fall 2015 2015-09-10 SLIDE 10
DiveShop ER Diagram Customer No DiveCust 1 Destination Name Customer No ShipVia Destination no n ShipVia ShipVia n 1 DiveOrds n Dest 1 1 Destination no 1 Order No Destination Site No n Order No Sites n 1 1 DiveItem n 1/n Site No Item No BioSite ShipWrck n Species No n Site No 1 1 Item No DiveStok Species No BioLife IS 257 Fall 2015 2015-09-10 SLIDE 11
Sorting SELECT BIOLIFE.Common_Name, BIOLIFE.Length_cm FROM BIOLIFE ORDER BY BIOLIFE.Length_cm DESC; IS 257 Fall 2015 2015-09-10 SLIDE 12
Subqueries SELECT SITES.Site_Name, SITES.Destination_no FROM SITES WHERE sites.Destination_no IN (SELECT Destination_no from DEST where Avg_Temp_F >= 78); Can be used as a form of JOIN IS 257 Fall 2015 2015-09-10 SLIDE 13
Aggregate Functions Count Avg SUM MAX MIN Many others are available in different systems IS 257 Fall 2015 2015-09-10 SLIDE 14
Using Aggregate functions SELECT attr1, Sum(attr2) AS name FROM tab1, tab2 ... GROUP BY attr1, attr3 HAVING condition; IS 257 Fall 2015 2015-09-10 SLIDE 15
Using an Aggregate Function Implied Joins SELECT DIVECUST.Name, Sum(Rental_Price*Qty) AS Total FROM DIVECUST, DIVEORDS, DIVEITEM, DIVESTOK WHERE DIVECUST.Customer_No = DIVEORDS.Customer_No AND DIVEORDS.Order_No = DIVEITEM.Order_No AND DIVEITEM.Item_No = DIVESTOK.Item_No GROUP BY DIVECUST.Name HAVING ((DIVECUST.Name) LIKE %Jazdzewski% ); IS 257 Fall 2015 2015-09-10 SLIDE 16
Using an Aggregate Function Explicit Join statements SELECT DIVECUST.Name, Sum(Rental_Price*Qty) AS Total FROM (DIVECUST INNER JOIN DIVEORDS ON DIVECUST.Customer_No = DIVEORDS.Customer_No) INNER JOIN DIVEITEM ON DIVEORDS.Order_No = DIVEITEM.Order_No INNER JOIN DIVESTOK ON DIVEITEM.Item_No = DIVESTOK.Item_No GROUP BY DIVECUST.Name HAVING ((DIVECUST.Name) LIKE %Jazdzewski% ); IS 257 Fall 2015 2015-09-10 SLIDE 17
GROUP BY SELECT DEST.Destination_Name, Count(*) AS Expr1 FROM DEST INNER JOIN DIVEORDS ON DEST.Destination_Name = DIVEORDS.Destination GROUP BY DEST.Destination_Name HAVING ((Count(*))>1); Provides a list of Destinations with the number of orders going to that destination IS 257 Fall 2015 2015-09-10 SLIDE 18
Connecting to phpMyAdmin We will step through Kevin Heard s phpMyAdmin tutorial (there is a link from the Assignment 1 page) Follow along on your laptop Note: phpMyAdmin has changed versions since the tutorial was originally written, but most things should work about the same IS 257 Fall 2015 2015-09-10 SLIDE 19
