SQL Foundation for data engineer | ETL | Testing | Analytics

Use the select statement to retrieve all data from a table, as demonstrated with the marks table containing student, course, and scores.

Learn how to retrieve specific columns from a table using the select statement, such as names of students or a student course from the marks table.

Explore using distinct to remove duplicates, retrieve a unique set of enrolled students, and obtain distinct student–course pairs when selecting multiple columns.

Learn to filter records using a single criteria with the where clause in SQL, selecting all columns from marks and applying score greater than 50.

Learn how to filter data in sql using the and operator to combine multiple conditions, such as score thresholds and course selections.

Use the SQL not operator to negate results and filter data. See how scores below 50 or less than or equal to 60 are handled, with and/or combinations.

Use the order by keyword to sort data by a column, such as score, in ascending order by default, or specify descending to arrange results from high to low.

Master the insert into syntax to add data into specified columns with values, including handling unknowns as blanks. Verify results by running a select query to view the inserted rows.

Distinguish null values from blanks and test for them using is null and is not null in SQL. Learn how to filter with course and score columns when values missing.

Master the delete statement to remove rows from a table using where conditions, including setting the course field to blank to delete blank records.

Replace blank exam scores with zero in the score column, update the appropriate rows, and verify changes by querying the marks table.

Learn how to retrieve the first rows of a table using the limit clause, with practical examples to fetch the first two or six records.

Learn how to fetch the top two records by score using order by score desc and limit, enabling conditional selection of the highest scores from the marks table.

Learn how to use aggregate statements with mean and max functions to obtain minimum and maximum scores from marks, replacing blanks with zero and aliasing columns.

Explore how the count and average functions measure rows that meet a criterion, including counting all rows versus counting distinct values, with examples using select statements.

count distinct records in a table column by contrasting total rows with unique students, using a distinct subquery with count(*) and the count(distinct student) method to reveal four unique students.

Learn how the avg() function in SQL computes the mean of a numeric column by using select avg(column) from a table to obtain the average.

Learn how the sum() function calculates the total of a numeric column using a marks example that yields 300 and see how the average becomes 50 from dividing by six.

Learn to compute a conditional average by filtering out zero scores, using a query like select average score from marks where score > 0, and see how the average changes.

Explore how the like operator filters data in a where clause by matching patterns with wildcards like % and _, including case-insensitive matching and examples of names starting with Ari.

Learn how to use the in operator in sql to filter by multiple values, replacing long or conditions with a single in clause for numeric and string columns.

Learn how the between operator selects values within a range and includes the endpoints, applicable to numbers, text, or dates in sql queries.

Renaming a column in SQL with AS by aliasing a selected column, such as student score, to a new name and viewing updated results.

Apply the group by statement to group rows by columns and use aggregate functions such as count, max, mean, sum, and average to compute total scores by each student.

Group by and average summarize total marks across exams into an overall performance average; see how exam counts affect averages and why higher totals may not equal the best performer.

Analyze how to use group by and where clauses, apply having for aggregate filtering, and compute averages after filtering scores not equal to zero.

Explore aggregation statements like count and average, and apply group by to summarize courses and exams enrollments with example scores and averages.

Learn how to filter grouped data using the SQL having clause, since where can't be used with group by; apply having to filter aggregates like average scores.

Create tables using a simple syntax by naming the table and defining data types. Add columns with integer and string types to illustrate a basic create table example.

Learn how to insert values into a table using insert into syntax, with an example inserting A into column B and 100 and 50 into other columns in test one.

Drop a table using the drop command, verify removal by attempting to select from the table, and observe the no such table error when the table no longer exists.

Learn how joins combine rows from two tables using a related key, and explore left, right, and full joins with student and course data.

Learn how inner join yields only matching records by joining student and student course tables on a shared key, using on clauses, aliases, and column selection to avoid ambiguity.

Learn how a left join returns all records from the left table and matching rows from the right table, with nulls where there is no match.

Explore full outer join behavior and its distinction from left join, showing how all records from both tables are returned, with duplicates and none where matches are missing.

Learn how the union operator combines result sets from multiple selects with matching column counts, data types, and order. Compare union and union all to see how duplicates are handled.

Create a view in sql, a virtual table built from the results of a select statement, with fields drawn from one or more real tables and queryable like a table.

Learn how to delete a view in sql, mirroring how to delete a table, with a simple example of naming the view and executing the deletion.

Reinforce the basics of execution with these indexes and support your queries and tasks in testing, development, and scripting, making them handy everywhere.