SQL Coding Flashcards
What are Common Table Expressions in SQL?
These are used to simplify complex joins and run subqueries - create temporary table object that can be called in another query.
WITH temp as (
SELECT id as id
FROM id
WHERE grade= “A”
AND major= “Science”
)
SELECT *
FROM class
WHERE id in (SELECT id from temp)
How would you handle duplicate data points in an SQL query?
You can avoid duplicates using DISTINCT.
The query below will return unique data points from the CUSTOMERS table.
SELECT DISTINCT Name, ADDRESS FROM CUSTOMERS
ORDER BY Name;
Or delete duplicate rows using rowid with Max or Min command.
The SQL query is deleting rowid where rowid is a MAX, group by employee name and address. It will group the table into Name and ADDRESS and pick the highest row id and discard the others. Every record in the table has a unique rowid that points to a physical location in the disk.
DELETE FROM Employee
WHERE rowid NOT IN (
SELECT MAX(rowid)
FROM Employee
GROUP BY Name, ADDRESS
);
What is SQL?
SQL stands for “structured query language”. It is a language used to query,
analyze, and manipulate data from databases. Today, SQL is one of the most
widely used tools in data.
Get all the columns from a table
SELECT * FROM table
Get specific columns from table in ascending order
SELECT col1, col2
FROM table
ORDER BY col ASC
Get first 5 rows of table
SELECT *
FROM table LIMIT 5
Get unique values in column
SELECT DISTINCT col
FROM table
How do you filter on columns containing numeric data?
SELECT col
FROM table
WHERE condition (e.g. col > 3)
How do you filter on columns containing text?
SELECT col
FROM table
WHERE condition (e.g. col = ‘Paris’ or col LIKE ‘j%’ AND NOT LIKE ‘%t’)
What are the simple aggregation functions in SQL?
SUM(), MIN(), MAX(), COUNT(), AVG() and GROUP BY
e.g.
SELECT col1, AVG(col2)
FROM table
WHERE col1 IN (‘str1’, ‘str2’)
GROUP BY col1
How would you select top N records from a table?
LIMIT
e.g.
SELECT col1
FROM table
ORDER BY col1 DESC
LIMIT n
What are window functions in SQL?
Window functions applies aggregate and ranking functions over a particular window (set of rows).
e.g.
SELECT co1,
window_function(col2)
OVER([PARTITION BY col1] [ORDER BY col3]) AS new_column
FROM table
What is data deduplication?
Data deduplication is a process that eliminates excessive copies of data and significantly decreases storage capacity requirements.
Deduplication can be run as an inline process as the data is being written into the storage system and/or as a background process to eliminate duplicates after the data is written to disk.
