SQL QUESTIONS

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 UNION and 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 duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

List and explain the different types of JOIN clauses supported in ANSI-standard SQL.

Hide answer

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 table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

·      RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

·      FULL JOIN (or FULL OUTER JOIN): Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.

·      CROSS JOIN: Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).

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 customer_id <> '123';

Hide answer

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 equals another value, so = null is always assumed to be false.

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     |

+----+----------------+-----------+

What will be the result of the query below?

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races)

Explain your answer and also provide an alternative version of this query that will avoid the issue that it exposes.

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 NOT null)

Note, this is assuming the standard SQL behavior that you get without modifying the default ANSI_NULLS setting.

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 your answer.

View the answer →

What is wrong with this SQL query? Correct it so it executes properly.

SELECT Id, YEAR(BillingDate) AS BillingYear

FROM Invoices

WHERE BillingYear >= 2010;

View the answer →

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?

View the answer →

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.

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

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

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) values (30);

insert into test_a(id) values (40);

insert into test_a(id) values (50);

insert into test_b(id) values (10);

insert into test_b(id) values (30);

insert into test_b(id) values (50);

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.

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.)

Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?

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 list of users who took the a training lesson more than once in the same day, grouped by user and training lesson, each ordered from the most recent lesson date to oldest date.

\What is an execution plan? When would you use it? How would you view the execution plan?

List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.

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.)

How can you select all the even number records from a table? All the odd number records?

What are the NVL and the NVL2 functions in SQL? How do they differ?

What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

What is the difference between the WHERE and HAVING clauses?

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

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;

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

1.     What is the difference between single-row functions and multiple-row functions?

2.     What is the group by clause used for?

What is the difference between char and varchar2?

Write an SQL query to display the text CAPONE as:

C

A

P

O

N

E

Or in other words, an SQL query to transpose text.

Can we insert a row for identity column implicitly?

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";

Table is as follows:

ID	C1	C2	C3
1	Red	Yellow	Blue
2	NULL	Red	Green
3	Yellow	NULL	Violet

Print the rows which have ‘Yellow’ in one of the columns C1, C2, or C3, but without using OR.

Write a query to insert/update Col2’s values to look exactly opposite to Col1’s values.

Col1	Col2
1	0
0	1
0	1
0	1
1	0
0	1
1	0
1	0

How do you get the last id without the max function?

What is the difference between IN and EXISTS?

Suppose in a table, seven records are there.

The column is an identity column.

Now the client wants to insert a record after the identity value 7 with its identity value starting from 10.

Is it possible? If so, how? If not, why not?

How can you use a CTE to return the fifth highest (or Nth highest) salary from a table?

Imagine 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']

How do you get the Nth-highest salary from the Employee table without a subquery or CTE?

Given the following table named A:

  x

------

  2

 -2

  4

 -4

 -3   

  0

  2

Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x.

Given the table mass_table:

weight
5.67
34.567
365.253
34

Write a query that produces the output:

weight	kg	gms
5.67	5	67
34.567	34	567
365.253	365	253
34	34	0

Consider the Employee table below.

Emp_Id	Emp_name	Salary	Manager_Id
10	Anil	50000	18
11	Vikas	75000	16
12	Nisha	40000	18
13	Nidhi	60000	17
14	Priya	80000	18
15	Mohit	45000	18
16	Rajesh	90000	–
17	Raman	55000	16
18	Santosh	65000	17

Write a query to generate below output:

Manager_Id	Manager	Average_Salary_Under_Manager
16	Rajesh	65000
17	Raman	62500
18	Santosh	53750

How do you copy data from one table to another table ?

Find the SQL statement below that is equal to the following: SELECT name FROM customer WHERE state = 'VA';

1.     SELECT name IN customer WHERE state IN ('VA');

2.     SELECT name IN customer WHERE state = 'VA';

3.     SELECT name IN customer WHERE state = 'V';

4.     SELECT name FROM customer WHERE state IN ('VA');

How to find a duplicate record?

1.     duplicate records with one field

2.     duplicate records with more than one field