Structured Query Language (SQL) for Data Science

Explore installing and configuring mysql, understand basic web architecture, and learn to access remote mysql servers along with licensing and available interfaces.

discover client-server architecture powering the Internet, where browsers send requests to servers via the communication network and receive response pages after querying their databases.

Examine MySQL distributions, comparing 5.7 and 8 features such as roles, multilingual support, and performance improvements, and learn local installation on macOS, Linux, and Windows.

install mysql 5.5 on mac by downloading the archive, running the installer, accepting the license, and starting the server via system preferences or the terminal using the admin password.

Install MySQL server on Linux using apt with admin rights, verify with systemctl status MySQL, and access the MySQL shell via sudo mysql to work with a 5.7 setup.

Learn how to perform a local installation on Windows by accessing the start menu, using the command line, and entering your password to complete setup.

Explore how MySQL licensing works, comparing the open source community edition under GPL 3 with the enterprise edition, and when commercial licenses apply.

Connect to a remote MySQL server for data science projects using the MySQL client, specifying the host, username, and password, and ensure you have permission to access and modify databases.

Explore graphical user interfaces and various MySQL clients, including free, paid, and trial options, and learn to install and configure MySQL using the MySQL shell.

Explore SQL basics for data science, including what SQL is. Learn to create tables, add data, and retrieve results with SELECT, and compare data types and null and not null.

Sql enables creating, maintaining, and retrieving relational databases, and communicates with the database, and runs independently of hardware as the standard language.

Explore the origins of sql from IBM research in 1972, where researchers pursued a standard language for relational databases and later renamed to sql after a 1979 trademark issue.

Connect to MySQL from your terminal using mysql -u root -p, enter the MySQL password, and log in to view the 5.7.2 version with GPL licensing.

Explore data definition language (DDL) and how SQL uses it to define database schemas. Learn how create, drop, and alter commands build, remove, and modify database objects.

Explore data manipulation language (dml) and how it manipulates data in tables, using select, insert, update, and delete to retrieve, add, modify, and remove records.

Learn data control language (DCL) and its access control commands for managing database permissions, including grant and revoke of user privileges, plus a note on data integrity commands.

Create the job portal database with a create database statement, note that the name is case sensitive, verify by showing databases, and use job portal to switch to it.

Learn how to create tables in SQL by defining a table structure with fields like name, designation, and salary, using the create table command and verifying with tables.

Insert data into a table using insert into with column names such as name, designation, and salary and their values, then verify by selecting all or specific columns.

Explore the select statement to retrieve data from tables, choose specific columns like name, and filter records with salary = 40. Learn to slice and dice data by designation.

Explore MySQL datatypes for table design, including integers, varchar, timestamp, and enum, and learn how not null and binary types affect storage.

Explore how null and not null constraints enforce mandatory versus optional fields in a registration form, including insert behavior, alter commands, and the difference between blank and null.

Explore updating and deleting data in tables, inspect table structures with describe, and modify schemas with alter in this module of SQL for data science.

Learn to use the update command to modify a table's column values with conditions; updates affect one column at a time, and without a where clause they affect all rows.

Learn how to use the SQL update command with a two-step example: first update designation, then salary, using a where clause to target specific rows or update all.

Explore deleting data from a table with the delete from syntax, noting that without a where clause all matching rows are removed; future practice follows in the next video.

Demonstrate the delete command by removing rows from the user details table using a condition, and avoid deleting all rows without a clause.

Describe command reveals the structure of a table, while create table shows the schema needed to recreate it. The video contrasts these two commands and promises a practical demo.

Describe and show create commands reveal table structure and create table details, including column types, nullability, defaults, engine, and charset, helping data retrieval and understanding in sql for data science.

Explore the alter table command to add a column, change the name from experience to XP, modify its data type, and drop the XP column in the user details table.

Learn to import data from CSV and Excel into MySQL, export data for Excel and business use, and back up tables and databases in SQL format for restoration.

Learn how to import a csv into a MySQL table using load data local infile, including setting delimiters and ignoring header lines to populate the details table.

Export data from the user_details table in MySQL to a csv file, using fields and lines terminators, and verify six rows with columns separated.

Learn to back up databases and tables with mysqldump, redirecting output to a file; back up all databases or a single table with create table and insert statements.

Learn to restore databases from backups, create a blank database, and recover tables and data after accidental deletion or server migrations using SQL commands.

Introduce data analysis with SQL techniques to count items, compute key statistics via aggregation, identify extremes, slice and group data, and derive actionable insights.

Learn to count rows with count and select, and identify distinct salary values using distinct, reducing results to five salaries; the next video covers averages and standard deviations.

Learn to use aggregation functions in SQL to compute total and average salaries, salary standard deviation, and min and max values, with outputs renamed via aliases.

Identify the maximum and minimum values of a variable, such as salary, using max and min, with table-based examples; next video covers in, not, and between operators.

Learn to slice data in sql using where, and, between, and not equal, and not in, to filter by designation, sum salaries for sales, exclude names, and show ranges 20–50.

Use limit and offset to retrieve specific rows from the user_details table, first showing the top two records, then the next two by skipping the first two.

Sort data by salary to identify the highest earning user in a large dataset using order by and limit. Select the name to reveal the top earner.

Filter data with the like operator and wildcards to find names that start with S, end with a character, or contain characters, using underscores and percent signs.

Explore grouping by and rolling up data to reveal department distributions, then compute total, average, and standard deviation of salaries, apply having filters, and order results to analyze departmental performance.

Explore descriptive analytics of the FIFA 19 players dataset, understand data through a data dictionary, and build tables with SQL while practicing simple and complex queries for data insights.

Practice data eyeballing on a FIFA 19 dataset by manually inspecting rows, columns, and variables like age, nationality, overall rating, club, value, wage, height, and penalties to gain initial insights.

Explore how a data dictionary defines each variable and its meaning, detailing id, name, age, nationality, club, value, wage, penalties, and other dataset attributes.

Frame the right business questions using structured thinking and derive answers from data with sql, exploring a players dataset to analyze nationalities, wages, ratings, clubs, and timelines.

Analyze data to design a players table with appropriate data types and lengths. Create the database and table, then explain indexing tradeoffs between fast queries and insert speed.

Load data into the players table using load data local, specifying the file and delimiter, optionally enclosed by, and ignoring one line, then verify the import by viewing records.

Learn sql for data analysis with simple queries to count players, determine distinct nationalities, calculate total and average wages with standard deviation, and identify top nationalities and top earners.

Master advanced sql queries for data science, including distribution by foot and jersey. Learn to filter by date ranges, group by, order by, and analyze yearly club joins.

Explore how to retrieve data from multiple tables using joins, identify when to use different join types, and practice self joins and subqueries to enhance SQL data analysis.

Discover how SQL joins retrieve data from multiple tables, using a job portal example to connect user details, job details, and applications and reveal who applied to which job.

Explore the main join types for data science, including left join, inner join, right join, and cross join, with notes on usage frequency and practical tips.

Explore the left join concept by comparing left and right tables, joining on a common key, and retrieving all records from the left table with matching right-table data.

Explore a practical left join example that links transactions with user details to retrieve active customers' contact numbers, driven by keys like user_id.

Identify how inner join returns only matching records from two tables and how to chain multiple inner joins using the same syntax, with comparison to left join.

Learn about the cross join, a cartesian join rarely used because it multiplies rows and requires heavy computation, and note it uses no on clause.

Explore the right join and its similarity to the left join. Learn why data scientists favor left joins and use the right join syntax for multi-table queries.

Explore self joins in sql by pairing a table with itself using aliases in the from clause, enabling analyses like finding players with same club or age gaps under 10.

Explore the basics of indexing in SQL and how it speeds data retrieval. Understand the types of relationships and constraints, including primary keys, uniqueness, foreign keys, and auto increment.

Learn how indexing speeds SQL searches by creating an index on one or more columns, including composite indexes, and understand trade-offs with insert and update performance.

Explore how indexing organizes data in databases to speed searches, using the librarian analogy and focusing on insertion costs and ordering by subject or author.

Explore how relationships connect the user details and job tables through the applied table in a dog portal database, showing how user id and job id link applications.

Explore the three types of relationships: one-to-one, one-to-many, and many-to-many, with examples like user profiles and resumes, and highlight primary keys, uniqueness constraints, and foreign keys.

Explore table constraints that control data through primary key, foreign key, and uniqueness rules, and note how auto increment creates serial numbers.

Learn how the concat string function concatenates first and last names to form full names, and joins name and nationality with a pipe separator in the FIFA 19 players dataset.

Convert string cases using upper and lower functions to display nationalities in uppercase or lowercase, shown with a sample query selecting nationality from players.

Trim strings by removing leading and trailing spaces using left trim, right trim, or the trim function on a column or variable; the video demonstrates removing both sides.

Learn to extract substrings with left and substring functions, including taking the first two characters of each player's name from the first 10 players.

Learn how to use date/time functions in SQL to obtain the current date and time with now and generate UNIX timestamps for data science tasks.

Extract date and time components from a field using SQL date/time functions. Retrieve date, time, month, day, and year from datetime values, including timestamps, for join dates and related fields.

Learn how to format date and time as strings using format functions to customize display, including specifying formats with year, month, and day tokens and exploring multiple formatting options.

Explore numeric functions for rounding in SQL data science. Round to the nearest integer, floor, ceiling, truncate decimals, and use absolute value to handle negatives.