Databases

1. Operators Operator Description Examples Result + addition 4+3 7 - subtraction 5-3 2 * multiplication 4*2 8 / division (integer division truncates the result) 8/4 2 % modulo (remainder) 10%4 2 ^ exponentiation 2^3 8 For example, 9/4 will give 2 while 9.0/4 will give 2.2500...00. 2. Functions Function Description Examples Result abs(x) absolute value abs(-2) 2 round(x, d) round x to d decimal places round(7.4632, 2) 7.46 ceiling(x) round up to integer ceiling(7.4632) 8 floor(x) round down to integer floor(7.4632) 7

Here, we go through conditional expressions. 1. CASE WHEN CASE WHEN goes through a set of conditions and returns a value if a condition is met. The syntax is as follows: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END Start the statement with CASE and end with END. In between, the statement has conditions and results. If multiple conditions are met, the result of the highest priority is returned. For example, if both condition1 and condition2 are true, only result1 will be returned. If there is no ELSE statement, and all the conditions after WHEN are not met, NULL is returned. ...

Here we go through other basic SQL commands for grouping: 1. Aggregate Functions Aggregate functions aggregate values in multiple rows to one value. COUNT in SQL 1. Basics is one of aggregate functions. Here are commonly used aggregate functions: SUM() AVG() MIN() MAX() COUNT() The examples are as follows: SELECT SUM(column_name1) FROM table_name1 You usually need to specify the column name inside the parentheses. COUNT accepts *. Aggregate functions cannot be used with other columns that are not aggregated. This is because aggregation results in only one value while other columns may have multiple values. Thus, the syntax below is not possible: ...

Here we go through other basic SQL commands for filtering: 1. WHERE WHERE is used to filter the data in the output. It always comes after FROM. The syntax is: SELECT column_name1, column_name2 FROM table_name1 WHERE condition For example, we can filter out all columns where the amount is 10.99. SELECT * FROM payment WHERE amount = 10.99 Furthermore, we can filter out certain columns. SELECT first_name, last_name FROM customer WHERE first_name = 'ADAM' Enclose the texts around '. We can use other operators to filter the data, e.g. >,<, <=, >=, != or <>, ...

1. SQL SQL stands for structured query language. It is a language to interact with databases. Database is an organized collection of structured information, or data, typically stored electronically in a computer system. Database consists of schemas, which are a collection of tables and help structuring the database. Database is managed by Database Management Systems(DBMS). One example of DMBS is PostgreSQL. a) Dialects of SQL All DMBS’s have slightly different dialects even though the differences are small. Among all DMBS’s, PostgreSQL is a good place to start because ...

Here, we go through basic SQL commands: 1. SELECT SELECT is used to select and return data. For a single column, the syntax is as below: SELECT column_name FROM table_name1 For multiple columns, you just list the column names with ,. SELECT column_name1, column_name2 FROM table_name1 To select all columns, use *. SELECT * FROM table_name1 Note that the exact format doesn’t matter, e.g. the two examples are the same: ...