Structured Query Language Flashcards
What is a Derived Column?
A new column that is a manipulation of the existing columns in your database.
What are the three essentials ideas that are aimed at database normalisation?
1 - Are the tables storing logical groupings of the data?
2 - Can I make changes in a single location, rather than in many tables for the same information?
3 - Can I access and manipulate data quickly and efficiently?
What are the Primary Keys and Foreign Keys?
A primary key is a unique column in a particular table. This is the first column in each of our tables. Here, those columns are all called id, but that doesn’t necessarily have to be the name.
A foreign key is a column in one table that is a primary key in a different table.
In a Left Join statement, which table is considered as the Left table
In a query like this, the Left Table is table_a
SELECT *
FROM table_a a
LEFT JOIN table_b b
ON a.id=b.fk
What is the result of the use go a full cutter join?
This will return the inner join result set, as well as any unmatched rows from either of the two tables being joined.
How to filter rows before combining tables?
Using a logic operator in the ON clause: SELECT orders.*, accounts.* FROM orders LEFT JOIN account ON order.account_id = account.id AND account.sales_rep_id = 321500
When using Count(*), what is the only kind of row the won’t show up?
Only rows that have only NULL values.
What is a down side of using DISTINCT?
Particularly in aggregations, DISTINCT can slow you queries down quite a bit.
When using multiple WHEN clause, which of them will sql check if its true first?
The first. See example:
SELECT account_id, occurred_at, total, CASE WHEN total > 500 THEN 'Over 500' WHEN TOTAL > 300 THEN '301 - 500' WHEN total > 200 THEN '201 - 300' ELSE '200 or under' END AS total_group
What is a CTE (Common Table Expression)?
These expressions serve the exact same purpose as subqueries that are defined once and previous to your query. This way, the query that defines your subquery has to be executed only on time.
What is the purpose of the LEFT function?
Pull character of the left side of the string and present them as a separate string
What is another way of using the CAST function?
Using ‘::’
SELECT columnA::date
It is appropriate to use Right, Left and SUBTR along with CAST?
No, because LEFT, RIGHT or SUBSTRING automatically cast data to a string data type
What is the purpose of the function COALESCE?
Returns the first NON-NULL value passed for each row
Explain the purpose of the Window Functions by defining the clauses in this query:
SELECT
depname,
empno,
salary,
avg(salary) OVER (PARTITION BY depname)
FROM empsalary;
A window function call always contains an OVER clause directly following the window function’s name and argument(s). This is what syntactically distinguishes it from a regular function or aggregate function. The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY list within OVER specifies dividing the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.
