SQL
How do you change a date format?
to_char(date_column, ''YYYY-DD-MM') http://www.sqlines.com/oracle-to-sql-server/to_date
What is the NOT EQUAL operator?
<> OR !=
What operators can you use with the WHERE clause?
= > < >= <= <> BETWEEN LIKE IN
What does GROUP BY do?
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions (COUNT, MAX, MIN, SUM, AVG). eg. if a particular column has same values in different rows then it will arrange these rows in a group. For example, if the column customer has bunch of 'Jane Doe' entry with different purchase_amount values, then you can group all of those purchase_amount under 'Jane Doe' In ORACLE, whatever columns that you selected that is NOT an aggregate function needs to be in the GROUP BY statement eg. SELECT to_char(submit_date, 'MM-YYYY'), COUNT(TCN) FROM b_submission_history GROUP BY to_char(submit_date, 'MM-YYYY') This basically says group all the count of TCN under the same date
Wha does LIMIT do?
Allows you to limit the number of rows you get back after a query Useful when wanting all the columns but not all the rows. Always go at the end. eg. SELECT * FROM customer LIMIT 5; this will just return the first 5 results.
What does LIKE do?
Allows you to use pattern matching. % matches any sequence while _ matches just one single character. It is case sensitive. To make it case insensitive, use ILIKE. ILIKE does NOT work in Oracle database. eg. you want to find results with the word Health from table AGENCY SELECT agency_name FROM agency WHERE agency_name like '%Health%'
How do you SELECT something after you filter it out the first time?
By using a subquery eg. SELECT purchases FROM (SELECT customer_id, COUNT(payment_id) as purchases FROM payment GROUP BY customer_id) as test;
What's the date format for SQL statement?
Eg. '2-may-19' '12-5-17'
HOW DO YOU LIMIT THE RESULT TO THE TOP 10%?
FETCH FIRST 10 PERCENT ROWS ONLY; You can use either NEXT or FIRST ROWS or ROW
What is the equivalence of LIMIT 5 in Oracle?
FETCH NEXT 5 ROWS ONLY You can use either NEXT or FIRST ROWS or ROW
What do MIN MAX AVG SUM do?
For number values only MIN - return the smallest value in that column MAX - return the largest value in that column AVG - return the average value of all values in that column. SUM - return the sum of all values in that column
How do you read a blob in Oracle?
GetBlobVarchar2(blob_column)
What does NVL( ) do?
In Oracle, this function lets you return an alternative value if an entry is NULL. The alternative data type must match that of the column. So if number, use number, if date, use date. eg. SELECT ProductName, NVL(UnitsOnOrder, 0)) FROM Products; So if UnitsOnOrder is NULL, return 0 instead
What is the alternative for AS for tables when using with FROM?
Instead of writing SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o You can just put a space and write like this SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers c, Orders o
What does the string literal do?
It allows you to create a new column in the result with the same value http://www.geeksengine.com/database/basic-select/literal-character-strings.php eg. SELECT CategoryID, CategoryName, 'Northwind Category' AS Note FROM categories; ^this will create a new "Note" column in the result, all with the value of "Northwind Category"
What does (+) do?
It's Oracle's synonym for OUTER JOIN. Depending on which side you put it on, it indicates a LEFT or RIGHT OUTER JOIN. SELECT * FROM a, b WHERE a.id(+) = b.id; gives the same result as SELECT * FROM b LEFT OUTER JOIN a ON b.id = a.id; and SELECT * FROM a, b WHERE a.id = b.id(+); gives the same result as SELECT * FROM a LEFT JOIN b ON a.id = b.id; Basically whatever side where the (+) sign is the side that is being joined to. And the side without the (+) is basically the left side
What does CASE WHEN ( ) THEN END do?
It's basically an if else statement. The CASE statement goes through conditions and returns a value when the FIRST condition is met. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL. eg. SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN "The quantity is greater than 30" WHEN Quantity = 30 THEN "The quantity is 30" ELSE "The quantity is under 30" END AS QuantityText FROM OrderDetails;
What does HAVING do?
It's like WHERE but applied after you applied GROUP BY. It's filtering after the group by statement. Always go after the GROUP BY statement eg. SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id HAVING SUM(amount) > 200;
What does IN do?
It's one of the operators in the WHERE clause. It specifies multiple possible values for a column eg. SELECT * FROM Customers WHERE City IN ('Paris','London'); This statement will return all results from the Customers table that has the value of EITHER Paris OR London in the City column It's similar to using a bunch of OR statements
What are aggregate functions?
MIN MAX AVG SUM COUNT Need accompany with GROUP BY statement?
What does a FULL OUTER JOIN do?
Produces the set of all records in table A and table B, with matching records from both sides, where available. If there is no match, the missing side will contain null.
What does a FULL OUTER JOIN do?
Return all rows from both tables as long as either table has a value for the matching part. eg. ON A.customerID = ON B.customerID. So as long as a row from either table contains a value for customerID, return it. If the customerID came from table A, then columns from B would be null. If customerID came from B, then columns from A would be null.
What does a RIGHT JOIN do?
Return all rows that table B has has records for, eg. ON employee.empID = location.empID, it would return all records in which table B has a empID for. If table A does not have matches with table B, ie. table B has a certain empID that table A does not, it will return null for columns coming from table A.
What is the function TRUNC(date_column) used for?
Returns a date truncated to a specific unit of measure. If the format parameter is omitted, the TRUNC function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.
What does a LEFT OUTER JOIN do?
Returns only row that table A has values for. Produces a complete set of records from table A, with the matching records (if available) in table B. If there is no match, eg. table A has customer_id 20 but table B does not have that customer_id, the right side will return null in columns coming from table B. This makes sense since those data does not exist in table B. So if table A has 200 records with a non-null customer_id, then the result would turn 200 records
What does an INNER JOIN do?
Returns rows (matches) in table A that have the same corresponding rows in table B. It selects records that have matching values in both tables. Matching values here refer to the ON A.customer_id = B.customer_id. Basically, it only return rows where customer_id is the same for both A and B. Any other columns individual to each table is combined into one row. You need to specify the table for the WHERE and ORDER BY statement if the column that you use is not in both tables. Just writing JOIN by default would apply INNER JOIN to it.
What does ROUND(AVG(amount), 2) do?
Round the average amount to 2 decimals
How do you skip the first 10 rows from the result?
SELECT product_name FROM inventories ORDER BY quantity DESC OFFSET 10 ROWS ;
How do you ORDER BY several columns?
SELECT * FROM Customers ORDER BY Country, CustomerName; selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName
What is the rule of quotes for text vs numeric fields?
SQL requires single quotes around text values SELECT * FROM Customers WHERE Country='Mexico'; However, numeric fields should not be enclosed in quotes SELECT * FROM Customers WHERE CustomerID=1;
How do you get the current time and date in Oracle?
SYSDATE No parameters
What does SELECT do?
Select a columns or several columns from a table eg. SELECT CustomerName, City FROM Customers; The above SQL statement selects the "CustomerName" and "City" columns from the "Customers" table: SELECT can select from other tables beside the one specified after FROM eg. SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
Should be straightforward
WHAT does SELECT DISTINCT column_A do?
Show you all the distinct values of column_A
What does WHERE do?
Specifies matching condition. The WHERE clause is used to extract only those records that fulfill a specified condition. The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc
What does AND do?
The AND operator displays a record if ALL the conditions separated by AND are TRUE. SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';
What does AS do?
The AS command is used to rename a column or table result with an alias. It requires double quotation marks or square brackets if the alias name contains spaces. The AS take effect for whatever columns/tables after the comma SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;
What does COUNT( ) do?
The COUNT() function returns the number of rows that matches a specified criteria. It basically returns the number of matches. It does not take NULL value into account SELECT COUNT(column_name) FROM table_name WHERE condition; eg. The following SQL statement finds the number of products that is 10 in price: SELECT COUNT(*) FROM Products WHERE Price = 10;
What does ORDER BY do?
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. eg. SELECT * FROM Customers ORDER BY Country; selects all customers from the "Customers" table, sorted by the "Country" column: SELECT * FROM Customers ORDER BY Country DESC; selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:
What does TRIM( ) do?
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. eg. TRIM(' SQL Tutorial! ') ---> 'SQL Tutorial!'
How do you combine columns result and concat them?
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country): SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers;
How do you specify all records that has does not have a specific column value?
WHERE NOT Column = 'Value'
What does END AS do?
Whatever returned from CASE, let those values END AS whatever_column_name_you_set
When is a single quote needed?
When the value is a string. Number does not need single quote around it
What does a FULL OUTER JOIN with WHERE do?
When you want values existing in only A and B and not the overlapping area.
What does a LEFT OUTER JOIN with WHERE do?
You use this when you only want values that in table A that is not included in table B eg. SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL This would only return records that have an id value in table A but that id value is null/non-existent in table B. For example, an id of 77 exists in table A but not table B, then the record with the id of 77 would show up in the result. So if table A has 200 records with a non-null id, and table B only match 160 of those records, then the result would turn 160 records
What does extract() do?
extracts subfields such as year or hour from date/time values eg. select extract (month from date '2017-06-15') from dual; ==> 6
How do you capitalize the first letter only?
initcap( full_name )
How do you mass insert with a subquery in Oracle?
insert into table (a, b, c) (SELECT x, y, z from table2) Make sure there's no order by clause in the subquery.
How do you remove all spaces in a column?
my_value := regexp_replace(my_value, '[[:space:]]*','');
How do you convert a string to a CLOB in Oracle?
update TABLE set blog = to_clob ('Hello this is the story of my life. I was born in Vietnam and migrated here as a child and blah blah blah');
How do you convert a string to a BLOB in Oracle?
update TABLE set blog = utl_raw.cast_to_raw ('Hello this is the story of my life. I was born in Vietnam and migrated here as a child and blah blah blah'); OR update TABLE set blog = rawtohex ('Hello I am another way to do it');
