Introduction to SQL: Learn the Basics and Beyond

Slide Note

Explore the world of Structured Query Language (SQL) with this comprehensive guide. Discover what SQL is, its history, usage, and how to learn it on your own through free online tutorials. Understand the properties of relational databases and get insights into designing a relational database. With SQL being a fundamental tool for managing data and querying databases, mastering it opens up a wide range of opportunities in the data-driven world.

ford Follow

Uploaded on Jul 30, 2024 | 0 Views

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. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript

IR 101: Beginning SQL in IR Kelly Wahl Director of Statistical Analysis and Assessment Coordinator UCLA

Learning Outcomes Know what SQL is Read a basic SQL SELECT statement Find online opportunities to learn the language Recognize examples of SQL syntax and identify what they do Consider how the results from a SELECT statement can be formatted to meet your needs

What is SQL? Structured Query Language (SQL) Often pronounced sequel because the original acronym was SEQUEL (Structured English QUEry Language) Developed by IBM in the early 70 s Changed its name because another company had already trademarked SEQUEL

How And When Do We Use SQL? Its name gives away its purpose: It is a programming language used for managing data and querying databases. SQL has been structured to be clear, to sequence commands, to allow options to be pursued, and to block (i.e., to treat groups of statements as one). Relational Database Management Systems (RDBMS) can use it. Oracle Database, SQL Server, and MySQL are examples of database software that use this language for data access and management.

How Can I Learn SQL on My Own? Free Tutorials Online: http://w3schools.com/sql http://sqlzoo.net http://sqlbolt.com http://sqlteaching.com http://quackit.com/sql/tutorial/ Give Me Your Info Online (But Free): https://community.modeanalytics.com/sql/ https://www.codecademy.com/learn/learn-sql https://academy.vertabelo.com

Properties of Relational Databases Relational databases are for storing and retrieving related information. They can be explained with diagrams showing the way the data are modeled. Records are stored as rows in tables that have columns for each variable being stored per case.

A Relational Database: major_degree_table declared_major_table term_table Major | Degree student_table ID | Term | Major Term | Year ID | Name | Gender term_enrollment_table subj_dept_table Subject | Dept ID | Term | Subject | Catlg_No | Grade dept_org_table ID | Ethnicity Dept | Org student_ethnicity_table

On a SQL Console: A List of Tables Is Provided Clicking around Reveals Columns and Properties

Commands Results

What Makes a SQL Statement Work? Syntax Syntax Syntax columns SELECT name FROM student_table WHERE gender = Male table filter

How to Select Records SELECT name FROM student_table WHERE gender = Male ID | Name | Gender

How to Select Records Name Robert Frank Juan Tongshan Denzel SELECT name FROM student_table WHERE gender = Male

More Complicated Selections WILDCARD (give me all of them) SELECT * FROM term_enrollment_table WHERE (subject = Math or subject = Chem ) and term = 17F and substring(catlg_no,2,1) <= 1 All Math enrollments and all Chemistry enrollments, in Fall 2017, at the undergraduate course level

More Complicated Selections ID | Term | Subject | Catlg_No | Grade 1 17F Math 2 17F Chem 2 17F Chem 3 17F Chem 3 17F Math . . . SELECT * FROM term_enrollment_table WHERE (subject = Math or subject = Chem ) and term = 17F and substring(catlg_no,2,1) <= 1 0010 0133 0141 0055A 0111 A B B+ C A

substring(catlg_no,2,1) <= 1 0032A 0205 Returns: 2 Returns: 0 Selected? YES Selected? NO

Little Aggregated Reports SELECT subject, COUNT(subject) enrollments FROM term_enrollment_table WHERE term = 18S GROUP BY subject ORDER BY enrollments desc A list of the subjects offered in Spring 2018 with count of enrollments, in descending order

Little Aggregated Reports SELECT subject, COUNT(subject) enrollments FROM term_enrollment_table WHERE term = 18S GROUP BY subject ORDER BY enrollments desc Subject | Enrollments Chem Math English Psych . . . 4401 2335 2011 1816

Coding New Columns SELECT CASE WHEN subject in ( Math , Chem ) THEN STEM ELSE Not STEM END STEM_flag, grade FROM term_enrollment_table WHERE term = 18F Grades for Fall 2018 marked as STEM or not STEM

Coding New Columns SELECT CASE WHEN subject in ( Math , Chem ) THEN STEM ELSE Not STEM END STEM_flag, grade FROM term_enrollment_table WHERE term = 18F STEM_Flag | Grade STEM STEM STEM Not STEM Not STEM STEM . . . B A- C A B+ D

CASE WHEN subject in ( Math , Chem ) THEN STEM WHEN subject in ( English , Spanish ) THEN HUMANITIES ELSE Other END discipline_group The code will test until it finds a match, then return the THEN value and go to the next row. Subject Chem Spanish Math Psych English Discipline_Group STEM HUMANITIES STEM OTHER HUMANITIES The new column name appears after the END .

Fancier Output for Reports What if I want something that looks like this: Subject | Enrollment 16F | Enrollment 17F | Enrollment 18F Chem 3801 4814 English 3510 3200 Math 2706 2800 Psych 1507 1750 Spanish 1042 1201 5023 3001 3056 2022 1502

Fancier Output for Reports Or something like this: Term | Subject | A s Awarded | B s Awarded | C s Awarded 17F Chem 1101 17F English 2108 17F Math 520 17F Psych 1202 17F Spanish 726 1121 1500 2203 516 233 1098 220 476 22 103

Fancier Output for Reports I d have to create a new column and aggregate at the same time. A CASE statement, plus a GROUP BY ???

Term Trend of Total Course Enrollment per Subject SELECT subject, SUM(CASE WHEN term = 16F then 1 else 0 end) enrollment_16F, SUM(CASE WHEN term = 17F then 1 else 0 end) enrollment_17F, SUM(CASE WHEN term = 18F then 1 else 0 end) enrollment_18F FROM term_enrollment_table GROUP BY subject ORDER BY subject English Math Psych Spanish Subject | Enrollment_16F | Enrollment_17F | Enrollment_18F Chem 3801 3510 2706 1507 1042 4814 3200 2800 1750 1201 5023 3001 3056 2022 1502

Grade Counts per Subject in a Term SELECT term, subject, SUM(CASE WHEN grade in ( A+ , A , A- ) then 1 else 0 end) As, SUM(CASE WHEN grade in ( B+ , B , B- ) then 1 else 0 end) Bs, SUM(CASE WHEN grade in ( C+ , C , C- ) then 1 else 0 end) Cs FROM term_enrollment_table WHERE term = 17F GROUP BY term, subject ORDER BY term, subject Term | Subject | A s | B s | C s 17F Chem 1101 17F English 2108 17F Math 520 17F Psych 1202 17F Spanish 726 1121 1500 2203 516 233 1098 220 476 22 103

Learning Outcomes Know what SQL is Read a basic SQL SELECT statement Find online opportunities to learn the language Recognize examples of SQL syntax and identify what they do Consider how the results from a SELECT statement can be formatted to meet your needs