Database Testing and SQL for Testers (top SQL interview Qs)

Install SQL Server Express edition, a free option ideal for learning, on 64-bit Windows. Install SQL Server Management Studio to connect and create databases and tables.

Understand what a database is and how a database management system, especially relational DBMS, creates databases and tables and establishes relationships via foreign keys.

Create a database and its tables by understanding data and log files, growth, and filegroups, using either the right-click method or the create database command, then verify creation.

Learn the syntax for creating a table, define columns, and apply constraints in a database. Grasp how a primary key uniquely identifies rows and prevents duplicates.

Explore the basics of SQL and the select statement to retrieve data from a department table, switch databases, and perform insert, delete, and update operations on relational data.

Eliminate duplicates by using the select distinct statement to return only unique rows from the department table, as shown in the caption's example.

Explain how the order by clause sorts results by salary in ascending or descending order, with the default being ascending, and demonstrate with sample query results.

Explore how the BETWEEN operator defines a numeric range in SQL, filtering salaries between 3000 and 4000 and returning the matching rows (three in this example).

Master the like operator to filter strings by pattern in SQL, using pattern matching with % and examples of first names starting with g and j.

Learn how insert statements add values to tables, including full row inserts and inserts into specific columns, with nulls for missing fields.

Learn to use the update statement to modify one or more columns in a table with a where clause to target specific rows.

Learn to use the delete statement to remove data from a table, including conditional deletes with where clauses and full-table deletions, demonstrated with an employee example.

Learn practical SQL Server string functions, including concatenation, left and right extraction, len, lower, upper, trim (including ltrim and rtrim), replace, reverse, and substring with hands-on examples.

Explore common aggregate functions in sql server, including sum, average, max, and count, and see how count differs from count big when calculating salaries.

Learn how group by with aggregate function groups records into unique categories and returns one row per group, summing salaries by position to show each role's total.

By default, aggregate functions do not return a column name. Use aliases to give results like sum(salary) as total_salary and improve readability.

Explore joins and their importance in combining columns from multiple tables to reduce duplicates. Learn how a join retrieves data from across tables, such as employee IP address and salary.

Master inner joins by combining two tables, such as student and address, on matching IDs to retrieve records present in both tables.

Explore right outer joins, emphasizing how the right table's rows are preserved, with unmatched left-table columns returning null in the result set, demonstrated via the students and address tables example.

Explore how full outer joins combine left and right tables, returning all rows with unmatched sides filled with nulls, demonstrated through a practical example.

Learn union and union all to merge results from multiple tables with matching columns; union removes duplicates, while union all preserves every row, illustrated with students and part-time students.

Validate user input by querying database tables with select statements to confirm data from a contact form is stored correctly in the contacts table.

Validate multiple user inputs in database tables by creating several contacts and querying with the in operator to verify all records exist.

Learn how to count rows efficiently in SQL by using select count(1) from employee instead of counting all records, improving performance.

Learn how to extract a portion of a string using SQL's substring function. The lecture demonstrates starting at a given position and specifying length with examples on a sample string.

Learn to write a sql query that returns the number of characters in a string using the lamb function, with India as an example.

Learn to display unique salaries from a table using select distinct salary from the table, enabling testers to identify distinct values in data.

Use the cat index function to return the numeric position of the first occurrence of a character in a string, enabling substring extraction; for example, J returns five.

discover how to get the nth highest salary in sql when duplicates exist, by selecting distinct salaries in descending order and taking the minimum of the top n results.

Explain the difference between a primary key and a unique key: one primary key per table enforces unique values; multiple unique keys can exist on a table to enforce uniqueness.

Discover how aggregate functions perform calculations on a set of values and return a single value. Explore examples like count, sum, averages, mean, and max that operate on multiple rows.

Delete removes rows from a table, optionally with a condition. Drop removes the entire table from the database, and after dropping, the table no longer exists.

Learn to specify conditions on grouped data using the having clause, compute annual salary by name, and apply filters similar to a where clause for grouped results.