1.17k likes | 1.36k Views
Basic SQL. Joe Meehean. What we are going to Learn. How do we access the data stored in a DBMS? What happens when we access data in a DBMS? How do we add or modify data in a DBMS?. Structured Query Language (SQL). Often pronounced “sequel” Standardized interface for interacting with DBMSs
E N D
Basic SQL Joe Meehean
What we are going to Learn • How do we access the data stored in a DBMS? • What happens when we access data in a DBMS? • How do we add or modify data in a DBMS?
Structured Query Language (SQL) • Often pronounced “sequel” • Standardized interface for interacting with DBMSs • as standardized as any CS interface gets • Oracle, DB2, SQL Server, MySQL, PostgreSQL,… • Used to • create/define tables • destroy tables • look up data • insert/modify data • delete data
Structured Query Language (SQL) • Declarative language • states what answer should look like • NOT how to compute it • e.g. “Who are the students with GPAs < 2.0?”
Querying Tables • Looking up information in relational tables • Use the SQL SELECT statement • SELECT <list of columns>FROM table • Result of a SELECT is another table
Extracting Columns • SELECT LastName, MajorFROM Students Students
Extracting Columns • SELECT LastName, MajorFROM Students Students
Extracting Columns • SELECT LastName, MajorFROM Students Result
Extracting Columns • SELECT LastName, MajorFROM Students • Note the duplicate rows in the result Result
Extracting Columns • SELECT DISTINCT LastName, MajorFROM Students • DISTINCT removes duplicate rows Result
Getting All the Columns • SELECT * • returns all columns Students SELECT * FROM Students
Getting All the Columns • SELECT * • returns all columns Result SELECT * FROM Students
Calculated Fields • Create new columns using arithmetic • Can use +, −, *, /, ( ) • Should name new column • Use the AS keyword
Calculated Fields Parts SELECT PartNo, Pdesc, Pqty * Wholsale AS StockCost FROM Parts
Calculated Fields Result SELECT PartNo, Pdesc, Pqty * Wholsale AS StockCost FROM Parts
Extracting Specific Rows • Add the WHERE clause to the SELECT • SELECT <columns,…>FROM <table>WHERE <condition> • WHERE conditions similar to C++ conditions
Extracting Specific Rows Students
Extracting Specific Rows Students SELECT LastName, FirstName FROM Students WHERE GPA >= 3.5
Extracting Specific Rows Students SELECT LastName, FirstName FROM Students WHERE GPA >= 3.5
Extracting Specific Rows Students SELECT LastName, FirstName FROM Students WHERE GPA >= 3.5
Extracting Specific Rows Result SELECT LastName, FirstName FROM Students WHERE GPA >= 3.5
Extracting Specific Rows • SQL supports following comparison operators • = : equals • <, <= : less than, less than or equal to • >, >= : greater than, greater than or equal to • <> : not equal • Combination of conditions • OR: inclusive • AND • NOT
Extracting Specific Rows Parts SELECT PartNo, Pdesc, Pqty FROM Parts WHERE PDesc = ‘5mm wrench’ OR Pdesc = ‘10mm nut’
Extracting Specific Rows Result SELECT PartNo, Pdesc, Pqty FROM Parts WHERE PDesc = ‘5mm wrench’ OR Pdesc = ‘10mm nut’
Extracting Specific Rows • LIKE operator • used for inexact string matching • like regular expressions • * wild card for any series of characters • e.g., ‘CS*’ matches strings prefixed with CS • e.g., ‘*Algebra*’ matches strings that contain Algebra • _ matches a single character • e.g., ‘CS24_’ matches CS241 and CS242, but not CS242A
Extracting Specific Rows Parts SELECT PartNo, Pdesc, Pqty FROM Parts WHERE PDesc LIKE ‘5mm *’
Extracting Specific Rows Result SELECT PartNo, Pdesc, Pqty FROM Parts WHERE PDesc LIKE ‘5mm *’
Extracting Specific Rows • Matching a range • e.g., parts that cost between $0.50 and $1.00 • use the BETWEEN-AND operator Parts SELECT * FROM Parts WHERE Wholesale BETWEEN 0.50 AND 1.00
Extracting Specific Rows • Matching a range • e.g., parts that cost between $0.50 and $1.00 • use the BETWEEN-AND operator Result SELECT * FROM Parts WHERE Wholesale BETWEEN 0.50 AND 1.00
Extracting Specific Rows • Testing for null • IS NULL • IS NOT NULL Offering SELECT CourseNo, SectionNo FROM Offering WHERE CourseNo = ‘CS242’ AND FacultyID IS NOT NULL
Extracting Specific Rows • Testing for null • IS NULL • IS NOT NULL Result SELECT CourseNo, SectionNo FROM Offering WHERE CourseNo = ‘CS242’ AND FacultyID IS NOT NULL
Sorting the Results • Want sorted results • ORDER BY clause • Ascending order by default • Add DESC for descending order • More than 1 column • sorts by 1st column, then 2nd, 3rd, etc…
Sorting the Results Students SELECT LastName, GPA FROM Students ORDER BY GPA
Sorting the Results Results SELECT LastName, GPA FROM Students ORDER BY GPA
Sorting the Results Students SELECT LastName, GPA FROM Students ORDER BY GPA DESC
Sorting the Results Results SELECT LastName, GPA FROM Students ORDER BY GPA DESC
Sorting the Results Students SELECT Major, GPA FROM Students ORDER BY Major, GPA DESC
Sorting the Results Students SELECT Major, GPA FROM Students ORDER BY Major, GPA DESC
Data Aggregation • Summarizing data • e.g., average GPA of all students • called data aggregation • COUNT(*) • computes the total number of rows • COUNT(column) • counts the non-null values in a column • counts duplicates • COUNT( DISTINCT column) • counts the non-null unique values in a column
Data Aggregation • AVG • computes the average of a numeric column • excludes null values • adding DISTINCT computes average of unique column values • SUM • computes the sum of a numeric column • excludes null values • adding DISTINCT computes sum of unique column values
Data Aggregation • MIN • finds the smallest value • works on both numeric and string columns • MAX • finds the largest value • works on both numeric and string columns
Data Aggregation Students SELECT AVG(GPA) AS AvgGPA FROM Students
Data Aggregation Result SELECT AVG(GPA) AS AvgGPA FROM Students
Data Aggregation Students SELECT MAX(GPA) AS MaxGPA FROM Students
Data Aggregation Result SELECT MAX(GPA) AS MaxGPA FROM Students
Data Aggregation Offering SELECT COUNT(*) AS NumOfferings, COUNT(DISTINCT CourseNo) AS NumCourse FROM Offering WHERE Term = ‘Spring’
Data Aggregation Result SELECT COUNT(*) AS NumOfferings, COUNT(DISTINCT CourseNo) AS NumCourses FROM Offering WHERE Term = ‘Spring’
Joining Tables • Combining multiple tables into a single query • Join • most common way to query multiple tables • combines related rows across several tables • rows combined must have common value in column Offering Course
Joining Tables • Combining multiple tables into a single query • Join • most common way to query multiple tables • combines related rows across several tables • rows combined must have common value in column Result