37 Essential SQL Interview Questions
Taken from Toptal. Links: toptal
37 flashcards · Programming Languages · by lakesare
What does UNION do? What is the difference between UNION and UNION ALL?
UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNIONand UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records. It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having du…
List and explain the different types of JOIN clauses supported in ANSI-standard SQL.
ANSI-standard SQL specifies five types of JOIN clauses as follows: INNER JOIN (a.k.a. “simple join”): Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right tab…
For a table orders having a column defined simply as customer_id VARCHAR(100), consider the following two query results: SELECT count(*) AS total FROM orders; +-------+ | total | +-------+ | 100 | +-------+ SELECT count(*) AS cust_123_total FROM orders WHERE customer_id = '123'; +----------------+ | cust_123_total | +----------------+ | 15 | +----------------+ Given the above query results, what will be the result of the query below? SELECT count(*) AS cust_not_123_total FROM orders WHERE custo…
The obvious answer is 85 (i.e, 100 - 15). However, that is not necessarily correct. Specifically, any records with a customer_id of NULL will not be included in either count (i.e., they won’t be included in cust_123_total, nor will they be included in cust_not_123_total). For example, if exactly one of the 100 customers has a NULL customer_id, the result of the last query will be: +--------- ----------+ | cust_not_123_total | +--------------------+ | 84 | +--------------------+
What will be the result of the query below? Explain your answer and provide a version that behaves correctly. select case when null = null then 'Yup' else 'Nope' end as Result;
This query will actually yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =. Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows: select case when null is null then 'Yup' else 'Nope' end as Result; This is because null represents an unknown value. If you don’t know the value, you can’t know whether it equ…
Given the following tables: sql> SELECT * FROM runners; +----+--------------+ | id | name | +----+--------------+ | 1 | John Doe | | 2 | Jane Doe | | 3 | Alice Jones | | 4 | Bobby Louis | | 5 | Lisa Romero | +----+--------------+ sql> SELECT * FROM races; +----+----------------+-----------+ | id | event | winner_id | +----+----------------+-----------+ | 1 | 100 meter dash | 2 | | 2 | 500 meter dash | 3 | | 3 | cross-country | 2 | | 4 | triathalon | NULL | +----+----------------+-----------+ Wh…
Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table. Knowing this, a query that avoids this issue would be as follows: SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races WHERE winner_id IS N…
Given two tables created and populated as follows: CREATE TABLE dbo.envelope(id int, user_id int); CREATE TABLE dbo.docs(idnum int, pageseq int, doctext varchar(100)); INSERT INTO dbo.envelope VALUES (1,1), (2,2), (3,3); INSERT INTO dbo.docs(idnum,pageseq) VALUES (1,5), (2,6), (null,0); What will the result be from the following query: UPDATE docs SET doctext=pageseq FROM docs INNER JOIN envelope ON envelope.id=docs.idnum WHERE EXISTS ( SELECT 1 FROM dbo.docs WHERE id=envelope.id ); Explain you…
The result of the query will be as follows: idnum pageseq doctext 1 5 5 2 6 6 NULL 0 NULL The EXISTS clause in the above query is a red herring. It will always be true since ID is not a member of dbo.docs. As such, it will refer to the envelope table comparing itself to itself! The idnum value of NULL will not be set since the join of NULL will not return a result when attempting a match with any value of envelope.
What is wrong with this SQL query? Correct it so it executes properly. SELECT Id, YEAR(BillingDate) AS BillingYear FROM Invoices WHERE BillingYear >= 2010;
The expression BillingYear in the WHERE clause is invalid. Even though it is defined as an alias in the SELECT phrase, which appears before the WHERE phrase, the logical processing order of the phrases of the statement is different from the written order. Most programmers are accustomed to code statements being processed generally top-to-bottom or left-to-right, but T-SQL processes phrases in a different order. The correct query should be: SELECT Id, YEAR(BillingDate) AS BillingYear FROM Invoic…
Given these contents of the Customers table: Id Name ReferredBy 1 John Doe NULL 2 Jane Smith NULL 3 Anne Jenkins 2 4 Eric Branford NULL 5 Pat Richards 1 6 Alice Barnes 2 Here is a query written to return the list of customers not referred by Jane Smith: SELECT Name FROM Customers WHERE ReferredBy <> 2; What will be the result of the query? Why? What would be a better way to write it?
Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don’t show up. But certainly those customers weren’t referred by Jane Smith, and certainly NULL is not equal to 2, so why didn’t they show up? SQL Server uses three-valued logic, which can be troublesome for programmers accustomed to the more satisfying two…
Considering the database schema displayed in the SQLServer-style diagram below, write a SQL query to return a list of all the invoices. For each invoice, show the Invoice ID, the billing date, the customer’s name, and the name of the customer who referred that customer (if any). The list should be ordered by billing date.
SELECT i.Id, i.BillingDate, c.Name, r.Name AS ReferredByName FROM Invoices i JOIN Customers c ON i.CustomerId = c.Id LEFT JOIN Customers r ON c.ReferredBy = r.Id ORDER BY i.BillingDate; This question simply tests the candidate’s ability take a plain-English requirement and write a corresponding SQL query. There is nothing tricky in this one, it just covers the basics: Did the candidate remember to use a LEFT JOIN instead of an inner JOIN when joining the customer table for the referring custome…
Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name). If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following SQL query: Select * From Emp, Dept Explain your answer.
The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.
Given a table SALARIES, such as the one below, that has m = male and f = femalevalues. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table. Id Name Sex Salary 1 A m 2500 2 B f 1500 3 C m 5500 4 D f 500
UPDATE SALARIES SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
Given two tables created as follows create table test_a(id numeric); create table test_b(id numeric); insert into test_a(id) values (10), (20), (30), (40), (50); insert into test_b(id) values (10), (30), (50); Write a query to fetch values in table test_a that are and not in test_b without using the NOT keyword. Note, Oracle does not support the above INSERT syntax, so you would need this instead: insert into test_a(id) values (10); insert into test_a(id) values (20); insert into test_a(id) val…
In SQL Server, PostgreSQL, and SQLite, this can be done using the except keyword as follows: select * from test_a except select * from test_b; In Oracle, the minus keyword is used instead. Note that if there are multiple columns, say ID and Name, the column should be explicitly stated in Oracle queries: Select ID from test_a minus select ID from test_b MySQL does not support the except function. However, there is a standard SQL solution that works in all of the above engines, including MySQL: s…
Given a table TBL with a field Nmbr that has rows with the following values: 1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1 Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.
This can be done as follows: update TBL set Nmbr = case when Nmbr = 0 then Nmbr+2 else Nmbr+3 end;
Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer. (Note: You may assume that there are at least 10 records in the Employee table.)
This can be done as follows: SELECT TOP (1) Salary FROM ( SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC ) AS Emp ORDER BY Salary This works as follows: First, the SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC query will select the top 10 salaried employees in the table. However, those salaries will be listed in descending order. That was necessary for the first query to work, but now picking the top 1 from that list will give you the highest salary not…
Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?
You can avoid duplicates using UNION ALL and still run much faster than UNION DISTINCT (which is actually same as UNION) by running a query like this: SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command, while avoiding much of its performance hit.
Given the following tables: SELECT * FROM users; user_id username 1 John Doe 2 Jane Don 3 Alice Jones 4 Lisa Romero SELECT * FROM training_details; user_training_id user_id training_id training_date 1 1 1 "2015-08-02" 2 2 1 "2015-08-03" 3 3 2 "2015-08-02" 4 4 2 "2015-08-04" 5 2 2 "2015-08-03" 6 1 1 "2015-08-02" 7 3 2 "2015-08-04" 8 4 3 "2015-08-03" 9 1 4 "2015-08-03" 10 3 1 "2015-08-02" 11 4 2 "2015-08-04" 12 3 2 "2015-08-02" 13 1 1 "2015-08-02" 14 4 3 "2015-08-03" Write a query to to get the l…
SELECT u.user_id, username, training_id, training_date, count( user_training_id ) AS count FROM users u JOIN training_details t ON t.user_id = u.user_id GROUP BY u.user_id, username, training_id, training_date HAVING count( user_training_id ) > 1 ORDER BY training_date DESC; user_id username training_id training_date count 4 Lisa Romero 2 August, 04 2015 00:00:00 2 4 Lisa Romero 3 August, 03 2015 00:00:00 2 1 John Doe 1 August, 02 2015 00:00:00 3 3 Alice Jones 2 August, 02 2015 00:00:00 2
What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure. In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visu…
List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows: Atomicity. Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. Consistency. The consistenc…
Given a table dbo.users where the column user_id is a unique numeric identifier, how can you efficiently select the first 100 odd user_id values from the table? (Assume the table contains well over 100 records with odd user_id values.)
SELECT TOP 100 user_id FROM dbo.users WHERE user_id % 2 = 1 ORDER BY user_id
How can you select all the even number records from a table? All the odd number records?
To select all the even number records from a table: Select * from table where id % 2 = 0 To select all the odd number records from a table: Select * from table where id % 2 != 0
What are the NVL and the NVL2 functions in SQL? How do they differ?
Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null. With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1. With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.
What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.
The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie). For examp…
What is the difference between the WHERE and HAVING clauses?
When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent. However, when GROUP BYis used: The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made. The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).
Suppose we have a Customer table containing the following data: CustomerID CustomerName 1 Prashant Kaurav 2 Ashish Jha 3 Ankit Varma 4 Vineet Kumar 5 Rahul Kumar Write a single SQL statement to concatenate all the customer names into the following single semicolon-separated string: Prashant Kaurav; Ashish Jha; Ankit Varma; Vineet Kumar; Rahul Kumar
SELECT CustomerName+ '; ' From Customer For XML PATH('') This is close, but will have an undesired trailing ;. One way of fixing that could be: SELECT top 1 LTRIM(STUFF((SELECT '; ' + c1.CustomerName FROM Customer c1 FOR XML PATH ('')), 1, 1,'')) as SSV from Customer c2; In PostgreSQL one can also use this syntax to achieve the fully correct result: SELECT array_to_string(array_agg(CustomerName), '; '::text) FROM Customer
Given a table Employee having columns empName and empId, what will be the result of the SQL query below? select empName from Employee order by 2 desc;
“Order by 2” is only valid when there are at least two columns being used in select statement. However, in this query, even though the Employee table has 2 columns, the query is only selecting 1 column name, so “Order by 2” will cause the statement to throw an error while executing the above sql query.
What will be the output of the below query, given an Employee table having 10 records? BEGIN TRAN TRUNCATE TABLE Employees ROLLBACK SELECT * FROM Employees
This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.
What is the difference between single-row functions and multiple-row functions? What is the group by clause used for?
Single-row functions work with single row at a time. Multiple-row functions work with data of multiple rows at a time. The group by clause combines all those records that have identical values in a particular field or any group of fields.
What is the difference between char and varchar2?
When stored in a database, varchar2 uses only the allocated space. E.g. if you have a varchar2(1999) and put 50 bytes in the table, it will use 52 bytes. But when stored in a database, char always uses the maximum length and is blank-padded. E.g. if you have char(1999) and put 50 bytes in the table, it will consume 2000 bytes.
Can we insert a row for identity column implicitly?
Yes, like so: SET IDENTITY_INSERT TABLE1 ON INSERT INTO TABLE1 (ID,NAME) SELECT ID,NAME FROM TEMPTB1 SET IDENTITY_INSERT OFF
Given this table: Testdb=# Select * FROM "Test"."EMP"; ID ---- 1 2 3 4 5 (5 rows) What will be the output of below snippet? Select SUM(1) FROM "Test"."EMP"; Select SUM(2) FROM "Test"."EMP"; Select SUM(3) FROM "Test"."EMP";
5 10 15
Print the rows which have ‘Yellow’ in one of the columns C1, C2, or C3, but without using OR.
SELECT * FROM table WHERE 'Yellow' IN (C1, C2, C3)
Write a query to insert/update Col2’s values to look exactly opposite to Col1’s values.
update table set col2 = case when col1 = 1 then 0 else 1 end Or if the type is numeric: update table set col2 = 1 - col1
How do you get the last id without the max function?
In MySQL: select id from table order by id desc limit 1
What is the difference between IN and EXISTS?
IN: Works on List result set Doesn’t work on subqueries resulting in Virtual tables with multiple columns Compares every value in the result list Performance is comparatively SLOW for larger resultset of subquery EXISTS: Works on Virtual tables Is used with co-related queries Exits comparison when match is found Performance is comparatively FAST for larger resultset of subquery
Suppose in a table, seven records are there. The column is an identity column. Now the client wants to insert a record aftImagine a single column in a table that is populated with either a single digit (0-9) or a single character (a-z, A-Z). Write a SQL query to print ‘Fizz’ for a numeric value or ‘Buzz’ for alphabetical value for all values in that column. Example: ['d', 'x', 'T', 8, 'a', 9, 6, 2, 'V'] …should output: ['Buzz', 'Buzz', 'Buzz', 'Fizz', 'Buzz','Fizz', 'Fizz', 'Fizz', 'Buzz']er th…
SELECT col, case when upper(col) = lower(col) then 'Fizz' else 'Buzz' end as FizzBuzz from table;
How do you copy data from one table to another table?
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;
Find the SQL statement below that is equal to the following: SELECT name FROM customer WHERE state = 'VA'; SELECT name IN customer WHERE state IN ('VA'); SELECT name IN customer WHERE state = 'VA'; SELECT name IN customer WHERE state = 'V'; SELECT name FROM customer WHERE state IN ('VA');
4. SELECT name FROM customer WHERE state IN ('VA');