SQL Tutorial For Tester

Learn ddl and dml in sql, including creating databases and tables, defining a schema with columns, and performing data updates and other data manipulation tasks.

Create a database in MySQL with create database database demo; verify by listing databases and confirming the entry exists via a switch statement.

Use an admin tool to list all databases, select the world database, and show its tables city, country, and country language.

Learn how to insert data into a table with a full column list and with selected columns, supply matching values, and observe that omitted columns default to null.

Update data in table using MySQL update statement with set and where clause to modify a row, as shown by running the query and confirming one row affected.

Learn to delete data from a table using a where clause to remove specific records or delete all rows, demonstrated on table one with its schema remaining intact.

Learn how to drop a table using drop table statement in MySQL, and compare it to delete, noting that drop removes the table and its schema, while delete clears data.

Explore SQL constraints such as unique, not null, check, primary key, default, foreign key, and index, with examples like email uniqueness and age checks to ensure data accuracy.

Learn about unique constraints in MySQL and SQL Server, including single and multi-column definitions, not null handling, and how to enforce unicity with primary keys.

Identify how the primary key constraint enforces unique and not null values in SQL, and explore single-column and composite keys, with examples like row number and social security number.

Create a student detail table with integer role number and varchar name, then place the primary key at the end, or use a key of row number and user id.

Learn to use the where clause with select statements to narrow results by continent and population, using operators like equals, not equal to, less than, greater than, between, and like.

Learn to use the distinct statement to fetch unique values from a column with select distinct, such as the continent column from country, returning seven distinct continents.

Learn how union and union all combine data from select statements, enforce equal column counts, matching data types, and column order; union yields unique rows, union all may include duplicates.

Learn how to use the in operator with a where clause to filter results, and apply it in sample queries like selecting continents in Asia or Europe.

Explore sql aliases that assign temporary names to tables or columns for readability. Use as or space syntax, apply aliases in joins, and alias count(*) as total to clarify results.

Learn how to use the max function in SQL to return the maximum numeric value from a column, illustrated by selecting the maximum population from the country table.

Learn how the avg function computes the average by summing a numeric column and dividing by the row count in SQL, using select avg(population) from country to return a decimal.

Learn how to use the sum() function in SQL to total a numeric column. See an example that sums population from the country table with select sum(population) from country.

Learn how to use the count function in SQL to return the total number of rows from a table, such as selecting count(*) from country, with 239 rows shown.

Master using group by with aggregate functions (avg, mean, max, sum, count) to summarize country data by region, apply having for conditional filtering, and alias results for readability.

Explore how SQL joins query data from two or more tables based on relationships, covering inner, outer, and full joins, with table a and table b illustrating the concept.

Learn how inner joins reveal common data from two tables by matching IDs, demonstrated with left and right tables, data insertion, and an inner join query.