Search Results

Description Window functions are somewhat similar to aggregate functions but do not cause rows to be grouped into a single output row like aggregate functions do. For example, in this code, avg(salary) function is an aggregate function in its form, but OVER cl...

Intro CREATE TABLE sal_emp( name text, pay_by_quarter integer[], schedule text[][]); Arrays can be specifed without its exact size. can be specified with exact size as well, as integer[3][3]. If any dimension is written as slice(i.e [1:2], containing co...

Style Guidelines Python (Python Enhancement Proposal(PEP) 8) uses underscores in its variable names uses CamelCase for class declarations FOO_CONST = 10 class FooBar(object): #.... def foo_bar(): baz_bar = 'some string' Javascript uses CamelCase for its ...

Intro PostgreSQL provides with statement that allows to construct auxiliary statements for use in a query. These statements are often referred to as common table expressions or CTEs. As resursive query is a query that refers to a recursive CTE. The following i...

Frequently Used Data Types Name Aliases Description boolean bool true/false integer int 4-byte integer bigint int8 8-byte integer serial, bigserial serial4, serial8 auto-incrementing 4 or 8-byte integer numeric[(p,s)] decimal[(p,s)] exact numeri...

Create Database CREATE DATABASE test; \c <database name>: connects to the database \?: help \q: quit psql Create Table CREATE TABLE courses( c_no text PRIMARY KEY -- all the values designeated in PRIMARY KEY column are unique; NULL not allowed title text ...

Data Constraints Column/Table Constraints CREATE TABLE products( product_no integer, name text, price numeric CONSTRAINT positive_price CHECK(price > 0), -- column CONSTRAINT definition comes after the data type discounted_price numeric CHECK (price-d...

Insertion test =# INSERT INTO courses(c_no, title, hours) VALUES ('CS301', 'Databases', 30) ('CS305', 'Networks', 60); Retrieval SELECT title AS course_title, hours FROM courses; DISTINCT commands to show without repeating values SELEC...

Intro Subqueries are nested SELECT command in parantheses SELECT name, (SELECT SCORE FROM exams WHERE exams.s_id = students.s_id AND exmas.c_no = 'CS305') FROM students; name |score ------------ Anna | 5 Victor | 4 Nina | <- NULL -- can rep...

Intro An aggregate fucnction computes a single result from multiple input rows. SELECT max(temp_lo) FROM weather; SELECT city FROM weather WHERE temp_lo = max(temp_lo); This query will not work since the aggregate max cannot be used in the WHERE clause. This...

LIKE LIKE expression returns true/false depending on the string matches the supplied pattern. Expression return(T/F) 'abc' LIKE 'abc' T 'abc' LIKE 'a%' T 'abc' LIKE '_b_' T 'abc' LIKE 'c' F Pattern Matching postgreSQL documentation

CREATE TABLE groups( g_no text PRIMARY KEY, monitor integer NOT NULL REFERENCES students(s_id)); ALTER TABLE students ADD g_no text REFERENCES groups(g_no) Let's create a new group called 'A-101' move all the students in this group, and make Anna its mon...

Sample JavaScript Code let data = [3,7,2,8,1,11]; let sum = 0; data.forEach(function(d){ sum += d; }); console.log('Sum = '+ sum); // outputs 'Sum = 33' Python http module execute example python -m http.server <port number>

Python # Using alias for your import import pandas as pd # Importing a part of the library from csv import DictWriter, DictReader # Importing all the variables from module (bad idea) from numpy import * # submodule import import matplotlib.pyplot as plt df = ...

Python # A student_data = [ {'name': 'Bob', 'id':0, 'scores':[68,75,56,81]}, {'name': 'Alice', 'id':1, 'scores':[75,90,64,88]}, {'name': 'Carol', 'id':2, 'scores':[59,74,71,68]}, {'name': 'Dan', 'id':3, 'scores':[64,58,5...

Python Python's data types types name primitive Integers primitive Float primitive Strings primitive Boolean non-prim Arrays non-prim Lists non-prim Tuples non-prim Dictionary non-prim Sets non-prim Files Javascript 7 primative JS d...

Python foo = {'a':3, 'b':2} for x in foo: print(x) a b for x in foo.items(): print(x) ('a', 3) ('b', 2) for key, value in foo.items(): print(key, value) a 3 b 2 Javascript for(let i in ['a','b','c']){ console.log(i) }; output: 0 1 2 forEach() B...

import numpy as np import pandas as pd import matplotlib.pyplot as plt import seaborn as sns import statsmodels as sm

Data Structures Tuples A tuple is a fixed-length, immutable sequence. tup = (4, 5, 6) tup = 4, 5, 6 # changing a list into a tuple tuple([4, 0, 2]) # changing a string into a tuple tuple('string') Tuple itself is immutable, but if an object inside a tuple is...

Python if elif else if a > b: print('a is bigger') elif a < b: print('b is bigger') else: print('a and b are same') python does not have switch but have match in python 3.10 for value in [value1, value2, value3]: match value: case value1: # ...