Master SQL interviews (Top Interview questions & answers)

Define a database as an organized, structured collection of information or data stored in a computer system, enabling efficient data organization and retrieval.

Differentiate a database from a dbms: a database is a data collection, while a dbms is the software that manipulates, stores, and retrieves data; write select queries and update statements.

Explain how a database management system uses software to manipulate, store, and retrieve data, and how a relational DBMS stores data in tables and defines relationships.

Explore ddl, dml, and dcl in sql, showing ddl definitions with drop, alter, rename; dml data manipulation with insert, update, delete, select; and dcl access control with grant, deny, revoke.

Explore the roles of data definition language, data manipulation language, and data control language, with examples like create, drop, alter, stored procedures, triggers, and grants.

Learn how to create a table in SQL, specify a table name and columns with data types, and define a primary key to uniquely identify records.

Explain the difference between char and varchar2 data types in SQL, contrasting fixed-length storage with char and variable-length storage with varchar2, and illustrate using length measurements.

Learn how to create a new table from an existing one using select into, duplicating both structure and data, with Oracle-specific alternative methods.

Learn how to insert values into a table using insert statements, specify column names to control order, and handle partial column lists or select-based inserts.

Demonstrates inserting into the same table by selecting from it; a primary key prevents data insertion, as five rows become ten in the example.

Inserting a value larger than the varchar column length triggers an error 'value too large for column' in Oracle, and the insert is terminated with no data stored.

Master SQL data retrieval by selecting all columns with select * from a table or specific columns such as first name, salary, position, and last name to improve readability.

Explore how to use the where clause to filter data with exact matches, pattern matching with like, and comparisons such as greater than to retrieve records.

Learn how to delete all data from a table in SQL Server using delete from, with an example deleting 60 rows from the employee table, leaving it empty.

Delete data from a table using a conditional where clause, counting rows before and after removing records such as Becky to show how deletions reduce the total.

Compare delete and truncate in SQL. Delete logs each row in the transaction log, while truncate removes data by deallocating data pages, making it faster.

Learn how to drop tables in SQL Server, including dropping multiple tables in a single statement. After dropping, queries from those tables show an invalid object.

Learn the difference between delete and drop in SQL. Delete removes rows from a table, optionally with a condition, while drop removes the table entirely from the database.

Enforce data integrity by constraints that govern allowed values, ensuring accuracy and reliability; include unique constraints, primary keys, foreign keys, and default handling when values are missing.

Define a primary key that uniquely identifies each row, can be a single or composite key, and must contain unique values, noting there is typically only one primary key.

Create a primary key while creating a table in SQL, and verify the key appears in the newly created table.

Learn how to define a table-level primary key by specifying the columns in a primary key constraint, as shown with the student diary and name columns.

Discover how to define a unique key alongside a primary key when creating a table, using an example where student_id is the primary key and ssn is a unique key.

Learn to create a table level unique key using a unique constraint on selected columns, with the option to use a primary key for the same columns.

Primary key enforces non-null, unique values and allows only one per table. Multiple unique keys can exist on a table and may accept nulls, but both prevent duplicates.

Learn how to define a check constraint at table creation to enforce age values greater than 25, preventing invalid inserts and triggering a check constraint violation error when violated.

Create a table-level check constraint by listing all involved columns and defining a condition. This approach handles multiple criteria, such as age >= 18.

Learn to create a check constraint that restricts values to a range between 15 and 25, enforce it for inserts, and see violations when values fall outside the range.

Master SQL interviews: learn to create a check constraint that enforces a first name starting with A, and validate inserts with practical examples.

Define a foreign key on the student table that references the city code in the city table, enforcing referential integrity by ensuring the child city matches a valid city.

Learn to define a table-level foreign key after listing all columns in the student table, name the constraint (for example, fk_city), and reference city(city_code) to enforce referential integrity.

Explore what a nullable column means, showing that the columns allow null values and can store normal values, demonstrated with a salary column and a select query.

Explore how aggregate functions perform calculations on a set of values and return a single value, with count as an example that returns the raw count of a table.

Learn to count the total rows in a table efficiently by using a simple select count(*) statement, avoiding scanning all records, especially for tables with millions of rows.

Write a SQL statement to count rows that satisfy a given condition using like patterns, such as first names starting with e or g, and interpret the results.

Learn to use the SQL LIKE operator to match string values with starts with, ends with, and contains patterns, filtering rows by the first name.

Discover how to calculate average, minimum, and maximum salaries using sql aggregate functions such as avg, min, and max on the salary data.

Learn how to calculate each employee’s total salary by grouping by employee and summing monthly salaries with a SQL select that returns the employee name and the total salary.

Learn to select data in a table in sorted order, using ascending by default and applying descending to view results, with examples showing how to sort data.

Learn how to apply conditions to grouped data with the group by clause and having, filtering annual salaries by name patterns and salary thresholds.

Discover that where and having clauses can co-exist in a single select statement, demonstrated by an example that groups by a name and applies a salary condition.

Use information_schema views to display a table’s columns, data types, and max length, and leverage the sb_help system stored procedure or the describe command to view schema details.

Discover how to alter an existing table to add a new column using alter table add column, define the column, and observe how existing data remains intact.

Master SQL interviews explains how to change a column's data type by altering the table, specifying table name and column, then executing and verifying the change.

Drop a column from a table using the drop column command, then verify by describing the table schema to confirm the column no longer exists.

Learn how joins combine two or more tables based on related data to retrieve customer and order details, including customer id, customer name, contact name, country, and order date.

Explore different types of joins in SQL, including left join, right join, and full join, and see how they combine data from two sources.

Explain the difference between inner join and outer join in SQL interviews by showing how matching and non-matching rows are returned from two tables, using left and right joins.

Explain the difference between left inner join and left outer join, showing that left joins return all left rows with matching right rows and nulls for nonmatches, using a student–city example.

Explain cross join as the Cartesian product of two tables, producing all row combinations. Use it to generate large data sets by combining two tables, such as students and city.

Learn how a full outer join returns matching rows and nonmatching rows from both tables, with nulls on missing sides, illustrating how full join combines all records.

Self joins show how a table is joined to itself to retrieve the manager name by matching the employee's manager id in the same data, using aliases.

Execute a select statement to display the current date and time using a system function. See how the function returns the system date and time with example values.

Write a SQL statement to display the server and database name using a function and executing a select command.

Write a sql statement to display the user name using a built-in function that reveals the request owner information.

Query information_schema.tables to count distinct table names in the database. It shows the total number of tables as 20.

Learn how to display the second highest salary from the employee table in SQL by excluding the maximum with a nested max query.

Explore how to use the substring function in SQL to extract part of a string by specifying a start position and length, with India as an example.

Master SQL interviews cover computing a string's length with the length function in SQL; see how the string 'India' yields 5 characters.

Learn to use the cat index function to find the numeric position of the first occurrence of a character in a string, with examples like locating 'G' in a name.

Learn how to display unique salaries from a table's salary column using the distinct keyword in a select statement.

Explore two ways to concatenate strings in SQL: using the plus operator and using the built-in CONCAT function, with example queries showing equivalent results.

Question 63 teaches how to create an empty table with the same structure as another table using select into and a false where clause.

Learn how to return the first three characters of a string in SQL using left and substring, with examples from London and extracting middle characters.

Learn how indexes speed data retrieval by creating an index on a table, illustrated with a simple number and names table and the create index syntax.

Explain what a clustered index is, how it sorts table data in order, and how a primary key creates a clustering index. Learn to create one with the cluster keyword.

Explore non-clustered indexes, an index structure separate from table data, including composite indexes, that boost query performance on frequently used columns, with steps to create and verify them.

learn how unique indexes prevent duplicates to protect data integrity, compare them with unique constraints, and practice creating a unique index with sql syntax.

Learn how a SQL view serves as a virtual table built from a query, created with create view view_name as select ..., and queried like a regular table.

learn to write a sql statement that displays user defined views by querying information_schema.tables where table_type = 'VIEW' and excluding system schemas.

Explains what a subquery is and why it's used to simplify complex queries, offering an alternative to joins to list customers who placed orders.

Discover how to remove spaces from the left, right, and both sides of a string in SQL, with examples illustrating left trim, right trim, and full trim.

Compare union and union all by showing how they combine data from two tables, with union removing duplicates and union all preserving them.

Explore how the EXCEPT (or MINUS) operator in SQL returns the difference between two sets, showing rows in the first table not in the second.

Explore the intersect operator inSQL, demonstrated with select examples and table relationships to show how it highlights common results across multiple data sets.

Explore temp tables in SQL Server, their lifespan and growth, and how naming with a hash character signals temporary objects during creation.

Explore how global temporary tables work in SQL, including creation with a double symbol, visibility across all connections, and automatic drop when the last connection ends.

Learn to retrieve the nth highest salary by selecting the top n salaries in descending order and taking their minimum to yield the third, fourth, or fifth highest.

Learn how to compute the nth distinct maximum salary by selecting distinct salaries, ordering them by salary descending, and taking the minimum of the top n distinct values.

Learn how the coalesce function returns the first non-null value in a list and how it replaces nulls in a column or query results.

Define OLTP as online transaction processing system that handles real-time, concurrent financial transactions over the internet with emphasis on speed and atomicity, ensuring transactions either complete or fail.

Explain the difference between OLTP and OLAP systems: OLTP handles many online transactions with atomic, fast processing; OLAP emphasizes data analysis via data warehouses and cubes.

Identify the three relationship types in RDBMS: 1 to 1, 1 to many, and many to many.

Learn how to create temp tables in SQL Server using a leading #, compare temporary and permanent tables, and understand that temp tables are dropped when the connection ends.

Learn to write a case when statement in sql to classify salaries as low or high based on salary < 2500, displaying first name, salary, and salary type.

Delete duplicate rows in the employee salary 2022 table by grouping by name, filtering with having count(*) > 1, and deleting those names.