SQL Notes
-
Optimized Java SQL Insert or Update Statements
private static final String INSERT_UPDATE_TABLE = "INSERT INTO " + TableName.TABLE_NAME + " (" + TableName.LINE_ID + " ," + TableName.PERIOD + " ," + TableName.CALLS + " ," + TableName.ANSWERED + "…
-
Find managers and how many employees they manage
create table employee (emp_id int, emp_name varchar(225), mrg_id int); insert into employee values (1, "A", null); insert into employee values (2, "B", null); insert into employee values (3, "C",…
-
How many columns is too many columns?
I've noticed that a lot of folks here cite tables with 20+ (I've seen as much as 55) columns in one table. Now I don't pretend to be a database design expert, but I've always heard that this is a…
-
UNION vs 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…
-
Types of JOIN clauses 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…
-
NULL comparison in a CASE expression
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…
-
NOT IN with NULL values in a subquery
Given the following tables: sql> SELECT * FROM runners; +----+--------------+ | id | name | +----+--------------+ | 1 | John Doe | | 2 | Jane Doe | | 3 | Alice Jones | | 4 | Bobby Louis | | 5 | Lisa…
-
UPDATE with INNER JOIN and EXISTS
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…
-
Using a SELECT alias in the WHERE clause
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…
-
ReferredBy <> 2 and three-valued logic
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…
-
Primary key vs unique key
Difference between primary key and unique key in SQL 1) Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL , Oracle etc. 2)…
-
Invoice query with customer and referrer names
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…
-
Cartesian product row count
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…
-
Swap male and female values with one UPDATE
Given a table SALARIES, such as the one below, that has m = male and f = female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no…
-
Values in one table but not another without NOT
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),…
-
Add 2 where the value is 0 and 3 where it is 1
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. update…
-
Find the 10th highest salary
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…
-
Using UNION ALL to avoid duplicates
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…
-
Users who took the same training more than once per day
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…
-
Execution plans
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…
-
ACID properties
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…
-
TRUNCATE vs DELETE
What is a key difference between Truncate and Delete? Truncate is used to delete table content and the action can not be rolled back, whereas Delete is used to delete one or more rows in the table…
-
Select the first 100 odd user IDs
Given a table dbo.users where the column user_id is a unique identifier, how can you efficiently select the first 100 odd user_id values from the table? (Assume the table contains well over 100…
-
Select even and odd rows
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…
-
NVL vs NVL2
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)…
-
RANK vs DENSE_RANK
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.,…
-
WHERE vs HAVING
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 BY is used: The WHERE clause is…