MonetDB Complete

Datatypes:
CREATE TABLE emp_reg (
id INT, -- integer
age SMALLINT, -- small integer
salary DECIMAL(5,2), -- fixed precision decimal
initial CHAR(1), -- single character
name VARCHAR(50), -- variable length string
dt DATE, -- calendar date
tm TIME, -- clock time
profile JSON, -- JSON document
ip INET, -- IP address
sid SERIAL, -- auto increment ID
link URL, -- URL link
uid UUID -- universally unique identifier
);

INSERT INTO emp_reg (

id, age, salary, initial, name, dt, tm, profile, ip, link, uid
)
VALUES (
1, -- INT
25, -- SMALLINT
55000.75, -- DECIMAL(5,2)
'A', -- CHAR(1)
'Arjun Patel', -- VARCHAR
DATE '2000-05-15', -- DATE
TIME '09:30:00', -- TIME
'{"skills":["SQL","Python"],"exp":3}', -- JSON
'[Link]', -- INET
'[Link] -- URL
[Link]() -- UUID (auto-generated unique ID)
);

Case Sensitive:
1. SQL Keywords (queries): Keywords are not case-sensitive.
Example: SELECT * FROM emp_reg;
You can also write select * from emp_reg; or SeLeCt * FrOm emp_reg; — all work the same way.
2. Identifiers (table names, column names): By default, table and column names are stored in lowercase.
If you create "Emp_Test" and "Name" (with quotes), then you must always write them exactly with the same
case and quotes: SELECT "Name" FROM "Emp_Test";
3. String and JSON values: String values are case-sensitive.
Example: If you inserted 'Arjun Patel' into the name column, a query where name = 'Arjun Patel' works, but
where name = 'arjun patel' does not match anything.
JSON is also case-sensitive. '{"skills":["SQL"]}' is different from '{"Skills":["SQL"]}'. So you must match
the key name exactly.
Schema Commands

Keyword Description Example

Create a new logical container for

tables, views, sequences, etc. CREATE SCHEMA IF NOT EXISTS
CREATE SCHEMA
Supports IF NOT EXISTS and university;
optional AUTHORIZATION.

Add or remove a human-readable

COMMENT ON SCHEMA university
COMMENT ON SCHEMA comment (use NULL or '' to
IS 'Teaching DB for labs';
remove).

Switch the current schema; new

SET SCHEMA objects without qualifiers are created SET SCHEMA university;
here.

Retrieve the name of the current

CURRENT_SCHEMA schema (handy to verify SET SELECT CURRENT_SCHEMA;
SCHEMA).

Create/read objects in another

CREATE TABLE [Link](id
Fully-qualified names schema by prefixing with
INT);
[Link].

Create a schema owned by a CREATE ROLE fac;

AUTHORIZATION user/role (ownership fixed after CREATE SCHEMA "HR"
creation). AUTHORIZATION fac;

SELECT schema_name FROM

List non-system schemas via SQL
INFORMATION_SCHEMA information_schema.schemata
standard view.
WHERE NOT is_system;

ALTER SCHEMA Rename a schema (only if no ALTER SCHEMA university

RENAME external dependencies). RENAME TO university_v2;

SET SCHEMA sys;

DROP SCHEMA Drop only if the schema is empty
RESTRICT (switch away from it first). DROP SCHEMA university
RESTRICT;

Drop schema and all contained SET SCHEMA sys;

DROP SCHEMA
objects (tables, views, sequences, DROP SCHEMA IF EXISTS
CASCADE
etc.). university CASCADE;
Table commands:

Keyword Description Example

CREATE TABLE students (sid INT

PRIMARY KEY, name VARCHAR(100)
Create a new regular table in NOT NULL);
the current schema; table
CREATE TABLE INSERT INTO students (sid, name)
names are unique within
VALUES (101,'gopi');
schema.
INSERT INTO students (sid, name)
VALUES (102,'krishnan');

Create a table based on a query

CREATE TABLE student_names AS
CREATE TABLE AS result. WITH DATA copies
SELECT name FROM students WITH
(CTAS) rows; WITH NO DATA copies
DATA;
only structure.

Create session-scoped tables

stored under schema tmp. CREATE TEMP TABLE tmp_scores(sid
CREATE TEMP TABLE LOCAL visible only in INT, score INT) ON COMMIT PRESERVE
session; GLOBAL defined in ROWS;
catalog but data isolated.

Define a virtual table CREATE MERGE TABLE marks_all (sid

combining partitions with INT, course VARCHAR(30), mark INT);
CREATE MERGE TABLE
identical structure. Query acts ALTER TABLE marks_all ADD TABLE
like a UNION. marks_2024;

CREATE REMOTE TABLE remote_students

Create an alias to a table/view
(sid INT, name VARCHAR(100)) ON
CREATE REMOTE on another MonetDB server.
'mapi:monetdb://host:50000/otherdb' WITH
TABLE Names and types must match
USER 'usr' ENCRYPTED PASSWORD
exactly.
'pwd';

Create a table without write-

CREATE UNLOGGED ahead logging; faster but non-
CREATE UNLOGGED TABLE foo(x INT);
TABLE durable. Can optionally be
INSERT ONLY.

Remove a table from schema.

RESTRICT requires no DROP TABLE IF EXISTS
DROP TABLE
dependents, CASCADE also university.student_names CASCADE;
drops dependent objects.

Inspect user tables, columns, SELECT * FROM

INFORMATION_SCHEMA
and stats via system catalogs information_schema.tables WHERE
& [Link]
and views. table_schema='university';
Keyword Description Example

Add a new column branch of

ALTER TABLE … ADD ALTER TABLE student ADD COLUMN
type VARCHAR(50) to the
COLUMN branch VARCHAR(50);
student table.

ALTER TABLE … Rename the column name to ALTER TABLE student RENAME
RENAME COLUMN sname for clarity. COLUMN name TO sname;

ALTER TABLE student ADD COLUMN sno

Adds a new column sno of INTEGER;
Add new integer column
type INTEGER to the table ALTER TABLE student ADD COLUMN
sem INTEGER DEFAULT 5;

Creates a temporary column

Step 1 — Add new column ALTER TABLE student ADD COLUMN
sno_new with the target data
with desired type sno_new DECIMAL(5,2);
type DECIMAL(5,2)

Copies and converts values

Step 2 — Copy existing from the old column sno into UPDATE student SET sno_new = CAST(sno
values sno_new using MonetDB’s AS DECIMAL(5,2));
CAST()

Removes the previous integer

Step 3 — Drop the old ALTER TABLE student DROP COLUMN
column once data has been
column sno;
safely copied

Renames sno_new back to sno

Step 4 — Rename new ALTER TABLE student RENAME
to keep the original name with
column COLUMN sno_new TO sno;
the new type

Displays table definition: DESCRIBE student;

column names, data types, and
DESCRIBE
nullability. Useful to check
schema after modifications. \d student
Key constrains:

Feature Example Command Explanation

Simplest form, defines 4

CREATE TABLE student ( sid INT, regno VARCHAR(20),
Basic table columns with types, no
name VARCHAR(100), dob DATE );
constraints.

PRIMARY CREATE TABLE student ( regno VARCHAR(20) regno is unique and non-null;
KEY PRIMARY KEY, name VARCHAR(100), dob DATE ); acts as the identifier.

CREATE TABLE student ( regno VARCHAR(20)

name cannot be left empty
NOT NULL PRIMARY KEY, name VARCHAR(100) NOT NULL, dob
on inserts.
DATE );

CREATE TABLE student ( sid INT PRIMARY KEY, regno

sid is PK, but regno must
UNIQUE VARCHAR(20) UNIQUE, name VARCHAR(100), dob
still be unique.
DATE );

CREATE TABLE student ( sid INT PRIMARY KEY, name Validates CGPA values;
CHECK
VARCHAR(100), cgpa DECIMAL(4,2) CHECK (cgpa anything outside 0–10
constraint
BETWEEN 0 AND 10) ); rejected.

CREATE TABLE student ( sid INT PRIMARY KEY, regno

DEFAULT Inserts without cgpa auto-fill
VARCHAR(20), name VARCHAR(100) NOT NULL, cgpa
value 0.0.
DECIMAL(4,2) DEFAULT 0.0 );

CREATE TABLE department ( did INT PRIMARY KEY,

dname VARCHAR(50) ); Links [Link] to
FOREIGN
CREATE TABLE student ( sid INT PRIMARY KEY, regno [Link], enforcing
KEY
VARCHAR(20) UNIQUE, did INT, FOREIGN KEY (did) referential integrity.
REFERENCES department(did) );

CREATE SEQUENCE sid_seq START WITH 101;

Using CREATE TABLE student ( sid INT PRIMARY KEY Auto-increments sid using an
SEQUENCE DEFAULT NEXT VALUE FOR sid_seq, regno explicit sequence.
VARCHAR(20) UNIQUE, name VARCHAR(100) NOT
NULL, dob DATE );

CREATE TABLE student ( sid SERIAL PRIMARY KEY,

Using Shorthand: SERIAL auto-
regno VARCHAR(20) UNIQUE, name VARCHAR(100)
SERIAL creates and binds a sequence.
NOT NULL, dob DATE );
Sequences

Keyword Description Example Output

Defines an auto-
increment
generator for
CREATE CREATE SEQUENCE sid_seq START
student IDs, operation successful
SEQUENCE WITH 101 INCREMENT BY 1;
starting from 101
and incrementing
by 1.

Creates student CREATE TABLE student ( sid INT

table, uses PRIMARY KEY DEFAULT NEXT
CREATE DEFAULT NEXT VALUE FOR university.sid_seq, regno
TABLE with VALUE FOR VARCHAR(20) NOT NULL, name operation successful
DEFAULT sid_seq so sid VARCHAR(100) NOT NULL, dob
auto-fills from DATE NOT NULL, marks INT, cgpa
sequence. DECIMAL(4,2), address JSON );

INSERT INTO student (regno, name,

Insert values
dob, marks, cgpa, address) VALUES
without sid;
INSERT row ('25CSE001','Arjun Patel',DATE '2005-
MonetDB fills it 1 row inserted
(auto sid) 04-12',86, 8.75,CAST('{"street":"123
from sequence
Elm St","city":"New
automatically.
York","state":"NY"}' AS JSON));

sid=101, regno=25CSE001,
Retrieve inserted name=Arjun Patel,
record, showing dob=2005-04-12, cgpa=8.75,
SELECT data SELECT * FROM student;
sequence-assigned address={"street":"123 Elm
sid (101). St","city":"New
York","state":"NY"}
Consider this table:
regno sname dob cgpa address branch
{"street":"MG
25001 Rohan Mehta 3/15/2005 8.1 CSE
Road","city":"Mumbai","state":"MH"}
Ananya {"street":"Park
25002 7/22/2006 8.9 ECE
Gupta Street","city":"Kolkata","state":"WB"}
Vikram {"street":"Jubilee
25003 11/5/2005 7.2 EEE
Reddy Hills","city":"Hyderabad","state":"TS"}
{"street":"MG
25004 Priya Nair 1/12/2006 9.3 IT
Road","city":"Bengaluru","state":"KA"}
Karthik
25005 9/25/2005 6.85 {"street":"Civil Lines","city":"Delhi","state":"DL"} MECH
Sharma

CREATE SEQUENCE sid_seq START WITH 25001 INCREMENT BY 1;

CREATE TABLE student (regno INT PRIMARY KEY DEFAULT NEXT VALUE FOR university.sid_seq,
sname VARCHAR(100) NOT NULL, dob DATE NOT NULL, cgpa DECIMAL(4,2), address JSON, branch
VARCHAR(50));

DROP TABLE IF EXISTS [Link] CASCADE;

DROP SEQUENCE sid_seq;

INSERT INTO student (sname, dob, cgpa, address, branch) VALUES ('Rohan Mehta', DATE '2005-03-15',
8.10, CAST('{"street":"MG Road","city":"Mumbai","state":"MH"}' AS JSON), 'CSE');

INSERT INTO student (sname, dob, cgpa, address, branch) VALUES ('Ananya Gupta', DATE '2006-07-22',
8.90, CAST('{"street":"Park Street","city":"Kolkata","state":"WB"}' AS JSON), 'ECE');

INSERT INTO student (sname, dob, cgpa, address, branch) VALUES ('Vikram Reddy', DATE '2005-11-05',
7.20, CAST('{"street":"Jubilee Hills","city":"Hyderabad","state":"TS"}' AS JSON), 'EEE'), ('Priya Nair',
DATE '2006-01-12', 9.30, CAST('{"street":"MG Road","city":"Bengaluru","state":"KA"}' AS JSON), 'IT'),
('Karthik Sharma', DATE '2005-09-25', 6.85, CAST('{"street":"Civil Lines","city":"Delhi","state":"DL"}' AS
JSON), 'MECH');

SELECT * FROM student;

Projections

Keyword Description Example

Project all attributes from the

Select all columns SELECT * FROM student;
table.

Select single
Retrieve only student names. SELECT sname FROM student;
column

Select multiple Show student name and

SELECT sname, branch FROM student;
columns branch only.

Rename column SELECT sname AS student_name, cgpa AS grade

Use alias for readability.
(alias) FROM student;

Add a literal value in

Select constants SELECT sname, 'India' AS country FROM student;
projection.

Apply string function in SELECT UPPER(sname) AS name_caps FROM

String functions
projection. student;

Arithmetic in SELECT sname, cgpa*10 AS cgpa_percent FROM

Calculate derived column.
projection student;

Substring Extract year from date of SELECT sname, EXTRACT(YEAR FROM dob) AS
projection birth. birth_year FROM student;

SELECT sname, CAST(address AS STRING) AS

Show city from JSON address full_address FROM student;
JSON as string
(basic cast). SELECT sname, address AS full_address FROM
student;

Selections:

Keyword Description Example

WHERE with Select students from branch SELECT sname, branch FROM student WHERE
equality CSE. branch = 'CSE';

SELECT sname, cgpa FROM student WHERE cgpa >

8.5;
SELECT sname, cgpa FROM student WHERE cgpa >=
WHERE with Find students with CGPA 8.5;
comparison above 8.5. SELECT sname, cgpa FROM student WHERE cgpa <
8.5;
SELECT sname, cgpa FROM student WHERE cgpa <=
8.5;
Keyword Description Example

SELECT sname, cgpa FROM student WHERE cgpa >=

8 AND branch = 'CSE';
WHERE with Get students with CGPA SELECT sname, cgpa FROM student WHERE cgpa >=
BETWEEN between 7.0 and 9.0. 8 OR branch = 'CSE';
SELECT sname, cgpa FROM student WHERE cgpa
BETWEEN 7.0 AND 9.0;

Select students from specific SELECT sname, branch FROM student WHERE
WHERE with IN
branches. branch IN ('CSE','ECE');

WHERE with Find students whose names SELECT sname FROM student WHERE sname LIKE
LIKE start with ‘A’. 'A%';

WHERE with IS Get students without a SELECT sname FROM student WHERE branch IS
NULL branch assigned. NULL;

WHERE with
Students from Bengaluru SELECT sname, address FROM student WHERE
JSON (string
city. CAST(address AS STRING) LIKE '%Bengaluru%';
search)

Students from CSE branch SELECT sname, branch, cgpa FROM student WHERE
WHERE with AND
with CGPA ≥ 8.0. branch='CSE' AND cgpa>=8.0;

SELECT sname, address FROM student WHERE

Students from Delhi OR
WHERE with OR CAST(address AS STRING) LIKE '%Delhi%' OR
Hyderabad.
CAST(address AS STRING) LIKE '%Hyderabad%';

SELECT sname, branch FROM student WHERE NOT

Exclude rows that match a branch = 'CSE';
NOT (negation) condition; inverse of equality
or LIKE. SELECT sname, branch FROM student WHERE
branch != 'CSE';

Exclude names starting with SELECT sname FROM student WHERE sname NOT
NOT with LIKE
'A'. LIKE 'A%';

Exclude multiple values at SELECT sname, branch FROM student WHERE

NOT with IN
once. branch NOT IN ('CSE','ECE');

Displays students sorted by

ORDER BY SELECT regno, sname, cgpa FROM student ORDER
CGPA in ascending order
(Ascending) BY cgpa ASC;
(lowest first)

Displays students sorted by

ORDER BY SELECT regno, sname, cgpa FROM student ORDER
CGPA in descending order
(Descending) BY cgpa DESC;
(highest first)
Keyword Description Example

Restricts output to a specific

SELECT regno, sname, cgpa FROM student ORDER
LIMIT number of rows (top 3
BY cgpa DESC LIMIT 3;
students by CGPA)

Skips the first n rows and

SELECT regno, sname, cgpa FROM student ORDER
OFFSET (Skip) displays the rest (skip top 2
BY cgpa DESC OFFSET 2;
highest CGPAs)

Combines both to display a

LIMIT with SELECT regno, sname, cgpa FROM student ORDER
specific range (3rd and 4th
OFFSET BY cgpa DESC LIMIT 2 OFFSET 2;
students by CGPA)

Aggregate Functions

Keyword Description Example

Counts number of rows in a

COUNT(*) SELECT COUNT(*) AS total_students FROM student;
table.

Counts non-NULL values in

COUNT(col) SELECT COUNT(branch) AS branch_count FROM student;
a column.

Returns the sum of all values

SUM(col) SELECT SUM(cgpa) AS total_cgpa FROM student;
in a column.

Computes average value of a

AVG(col) SELECT AVG(cgpa) AS avg_cgpa FROM student;
column.

Returns minimum value in a

MIN(col) SELECT MIN(cgpa) AS min_cgpa FROM student;
column.

Returns maximum value in a

MAX(col) SELECT MAX(cgpa) AS max_cgpa FROM student;
column.

Aggregates rows grouped by SELECT branch, AVG(cgpa) FROM student GROUP BY

GROUP BY
a column. branch;

Applies condition after SELECT branch, AVG(cgpa) FROM student GROUP BY

HAVING
grouping. branch HAVING AVG(cgpa) > 8.5;

Aggregates unique values SELECT COUNT(DISTINCT branch) AS distinct_branches

DISTINCT
only. FROM student;
UPDATE queries

Keyword Description Example

Change branch of a
Basic update UPDATE student SET branch='AIML' WHERE sid=101;
single student.

Update multiple Modify more than one UPDATE student SET cgpa=9.1, branch='DS' WHERE
columns field at once. sname='Ananya Gupta';

Increase CGPA by 0.5

Update with
for all students in UPDATE student SET cgpa=cgpa+0.5 WHERE branch='CSE';
arithmetic
CSE.

Update using Convert branch names

UPDATE student SET branch=UPPER(branch);
string function to uppercase.

Change city in
UPDATE student SET address=CAST('{"street":"MG
Update JSON address JSON by
Road","city":"Chennai","state":"TN"}' AS JSON) WHERE
(replace whole) replacing whole
sid=104;
object.

Update JSON Replace city text UPDATE student SET address=CAST(REPLACE(CAST(address

(string replace without rewriting full AS STRING), '"city":"Delhi"', '"city":"Noida"') AS JSON)
trick) JSON. WHERE sid=105;

Conditional Set branch to IT for

UPDATE student SET branch='IT' WHERE cgpa < (SELECT
update with students below
AVG(cgpa) FROM student);
subquery average CGPA.

DELETE and DROP

Keyword Description Example

DELETE single row Remove one student by sid. DELETE FROM student WHERE sid=101;

DELETE by Delete all students in MECH DELETE FROM student WHERE

condition branch. branch='MECH';

Delete students with regno <

DELETE using range DELETE FROM student WHERE regno < 25003;
25003.

DELETE using Remove students below average DELETE FROM student WHERE cgpa <
subquery CGPA. (SELECT AVG(cgpa) FROM student);

DELETE all rows Clear table but keep structure. DELETE FROM student;

Faster delete all rows (metadata

TRUNCATE TABLE TRUNCATE TABLE student;
reset).
Keyword Description Example

Permanently remove the table

DROP TABLE DROP TABLE student;
definition and data.

DROP TABLE IF Prevents error if table doesn’t

DROP TABLE IF EXISTS student;
EXISTS exist.

DROP SCHEMA
Delete schema if empty only. DROP SCHEMA university RESTRICT;
RESTRICT

DROP SCHEMA Force delete schema + all

DROP SCHEMA university CASCADE;
CASCADE objects.

Date Time Functions

Keyword Description Example

CURRENT_DATE Returns today’s date. SELECT CURRENT_DATE;

Returns current system

CURRENT_TIME SELECT CURRENT_TIME;
time.

Returns current date

CURRENT_TIMESTAMP SELECT CURRENT_TIMESTAMP;
and time.

Extract part of a date

SELECT EXTRACT(YEAR FROM dob) AS
EXTRACT(field FROM date) (YEAR, MONTH,
birth_year FROM student;
DAY).

Retrieves students
Students born after a specific SELECT * FROM student WHERE dob > DATE
whose DOB is later
date '2005-12-31';
than a given date
Retrieves students
Students born before a specific SELECT * FROM student WHERE dob < DATE
whose DOB is earlier
date '2006-01-01';
than a given date
Extracts students
SELECT * FROM student WHERE
Students born in 2005 whose DOB year is
EXTRACT(YEAR FROM dob) = 2005;
2005
Filters students born SELECT * FROM student WHERE
Students born in a specific
in a given month & EXTRACT(YEAR FROM dob)=2006 AND
month & year (Jan 2006)
year EXTRACT(MONTH FROM dob)=1;
Students sorted by DOB
Oldest to youngest SELECT * FROM student ORDER BY dob ASC;
(ascending)
Students sorted by DOB (most SELECT * FROM student ORDER BY dob
Youngest to oldest
recent first) DESC;
Returns total students SELECT COUNT(*) AS total_students FROM
Count students born in 2006 with DOB year = student WHERE EXTRACT(YEAR FROM dob)
2006 = 2006;
Keyword Description Example

Lists students whose SELECT * FROM student WHERE dob

Students born between two
DOB is within a date BETWEEN DATE '2005-01-01' AND DATE
dates
range '2005-12-31';
SELECT sname, (EXTRACT(YEAR FROM
Approximates age by
Display name and age (years) CURRENT_DATE) - EXTRACT(YEAR FROM
year difference
dob)) AS age_years FROM student;
SELECT * FROM student WHERE
Filters by age > 18
Students older than 18 years (EXTRACT(YEAR FROM CURRENT_DATE) -
(year-diff approx)
EXTRACT(YEAR FROM dob)) > 18;
SELECT regno, sname, dob, (EXTRACT(YEAR
Calculate age (full years) per Shows regno, sname,
FROM CURRENT_DATE) - EXTRACT(YEAR
student dob, computed age
FROM dob)) AS age_years FROM student;
SELECT regno, sname, dob FROM student
Filters students with WHERE (EXTRACT(YEAR FROM
Students older than 19 years
age > 19 (year-diff) CURRENT_DATE) - EXTRACT(YEAR FROM
dob)) > 19;

CAST FUNCTIONS

Keyword Description Example

Convert a numeric column to string SELECT sname, CAST(cgpa AS STRING) AS

CAST to STRING
for display or pattern search. cgpa_text FROM student;

Convert CGPA (decimal) into SELECT sname, CAST(cgpa AS INT) AS

CAST to INT
integer (drops decimals). cgpa_int FROM student;

SELECT regno, CAST(regno AS

CAST to Convert regno (INT) into
DECIMAL(10,2)) AS reg_decimal FROM
DECIMAL DECIMAL(10,2).
student;

Convert a string into DATE SELECT CAST('2005-04-12' AS DATE) AS

CAST to DATE
explicitly. birth_date;

CAST to Turn a date-time string into SELECT CAST('2025-09-22 14:30:00' AS

TIMESTAMP TIMESTAMP. TIMESTAMP);

CAST JSON → Convert JSON to string for SELECT sname, CAST(address AS STRING) AS
STRING searching. addr_text FROM student;

CAST STRING SELECT CAST('{"city":"Chennai"}' AS JSON)

Convert text literal into JSON type.
→ JSON AS addr;

CAST to Convert integer marks element into SELECT sid, CAST(marks[1] AS DOUBLE) AS
DOUBLE DOUBLE precision. first_mark FROM student;
Keyword Description Example

CAST to Convert integer expressions into SELECT regno, CAST((cgpa > 8) AS

BOOLEAN boolean. BOOLEAN) AS high_performer FROM student;

Mathematics Functions

Keyword / Function Description Example

Arithmetic operators; / on
SELECT regno, regno/2 AS half, regno%2 AS
+-*/% integers truncates; % is
rem FROM student;
modulo (remainder).

Bitwise AND/OR/XOR/NOT SELECT regno, regno & 15 AS and15, regno ^ 3

& | ^ ~ << >>
and bit shifts (integers only). AS xor3, regno << 1 AS shl FROM student;

SELECT sname, abs(cgpa-9.0) AS diff FROM

abs(x) Absolute value.
student;

Returns −1, 0, or 1 based on SELECT sname, sign(cgpa-8.0) AS above8

sign(x)
sign. FROM student;

SELECT sname, ceil(cgpa) AS up FROM

ceil(x) / ceiling(x) Rounds up to nearest integer.
student;

Rounds down to nearest SELECT sname, floor(cgpa) AS down FROM

floor(x)
integer. student;

SELECT sname, round(cgpa,1) AS cg FROM

round(x,d) Round to d decimals.
student;

SELECT regno, power(2,2) AS four FROM

power(x,y) x raised to power y.
student LIMIT 1;

sqrt(x) Square root. SELECT sqrt(2.0) AS root2;

cbrt(x) Cube root. SELECT cbrt(27.0) AS cube_root;

exp(x) Exponential e^x. SELECT exp(1) AS e;

Natural logarithm (same

ln(x) / log(x) SELECT ln(2.0) AS ln2, log(2.0) AS log2n;
function).

log10(x) / log2(x) Base-10 and base-2 logs. SELECT log10(100.0), log2(64.0);

Logarithm base b. (Arg order

log(b,x) SELECT log(2,64.0) AS log2_64;
is b,x in modern releases.)

Modulo function (works with

mod(x,y) SELECT mod(5.0,2.1) AS m;
decimals too).
Keyword / Function Description Example

Return higher/lower of two

SELECT greatest(cgpa,8.0), least(cgpa,8.0)
greatest(x,y) / least(x,y) values (same as
FROM student;
sql_max/sql_min).

Random 32-bit integer;

rand() / rand(seed) SELECT sname, rand() AS r FROM student;
optional seed.

Next representable float from

nextafter(x,y) SELECT nextafter(5.99999999999999,12.1);
x toward y.

Bitwise functions (function SELECT bit_and(91,15), bit_or(32,3),

bit_and/or/xor/not
form). bit_xor(17,5), bit_not(1);

left_shift/right_shift Bit shifts (function form). SELECT left_shift(1,4), right_shift(16,2);

degrees(rad) / Convert rad↔deg; pi()

SELECT degrees(pi()/2), radians(180), pi();
radians(deg) constant.

Round to prc decimals;

sys.ms_round(x,prc,trunc) SELECT sys.ms_round(1.2359,2,0);
truncate control.

sys.ms_trunc(x,prc) Truncate to prc decimals. SELECT sys.ms_trunc(1.2359,2);

String Functions

Keyword Description Example

Returns number of characters SELECT sname, LENGTH(sname) AS name_len

LENGTH(str)
in a string. FROM student;

Converts all characters to SELECT UPPER(sname) AS caps_name FROM

UPPER(str)
uppercase. student;

Converts all characters to SELECT LOWER(sname) AS small_name

LOWER(str)
lowercase. FROM student;

Capitalizes first letter of each SELECT INITCAP(LOWER(sname)) AS proper

INITCAP(str)
word. FROM student;

SELECT CONCAT(sname, branch) AS details

CONCAT(str1,str2) Concatenates two strings.
FROM student;

SUBSTRING(str FROM Extracts substring starting at SELECT SUBSTRING(sname FROM 1 FOR 5)

x FOR y) position x with length y. AS short_name FROM student;

Finds index of substring in SELECT POSITION('a' IN sname) AS pos

POSITION(substr IN str)
string. FROM student;
Keyword Description Example

Removes spaces (or specified

TRIM([chars FROM] str) SELECT TRIM(BOTH ' ' FROM ' Delhi ');
chars) from ends.

Replaces substring SELECT REPLACE(sname,'a','@') AS funny

REPLACE(str, old, new)
occurrences. FROM student;

SELECT REPEAT('CS',3) AS code FROM

REPEAT(str,n) Repeats string n times.
student;

Generator Functions

Keyword Description Example

generate_series(start, Generates a series of integers from start SELECT * FROM

stop) to stop inclusive, step = 1. generate_series(1,5);

generate_series(start, stop, SELECT * FROM

Generates integers with a custom step.
step) generate_series(2,10,2);

generate_series + Combine with aggregates for numeric SELECT AVG(g) FROM

aggregates tests. generate_series(1,100) AS g;

View in MonetDB

Feature Purpose / Description Example

Defines a virtual table based on a SELECT

CREATE CREATE VIEW Maxmarks AS SELECT regno,
query. The view does not store data
VIEW name, cgpae FROM student WHERE cgpa > 9;
physically.

SHOW
View display SELECT * FROM Maxmarks;
VIEW

DROP Removes an existing view from the

DROP VIEW Maxmarks;
VIEW database.

CREATE Improves query performance by creating an CREATE INDEX idx_stu_cgpa ON

INDEX index on one or more columns. student(cgpa);

DROP
Deletes an existing index. DROP INDEX idx_stu_cgpa;
INDEX
Indexes in MonetDB

Keyword Description Example

Create a simple index on a CREATE INDEX idx_student_name ON

CREATE INDEX
column. student(sname);

CREATE UNIQUE Ensure values are unique CREATE UNIQUE INDEX idx_regno_unique ON
INDEX (like alternate key). student(regno);

CREATE INDEX on Index on combination of CREATE INDEX idx_branch_cgpa ON

multiple cols columns. student(branch, cgpa);

CREATE INDEX with Create index with fully CREATE INDEX university.idx_city ON
schema name qualified name. student((CAST(address AS STRING)));

DROP INDEX Remove an index by name. DROP INDEX idx_student_name;

DROP INDEX IF Prevent error if index

DROP INDEX IF EXISTS idx_branch_cgpa;
EXISTS missing.

Query system catalog for

List indexes SELECT name, table_id FROM [Link];
existing indexes.

Index helps speed up SELECT sname FROM student WHERE sname

Index + query
filtering queries. LIKE 'A%';

Create index on computed CREATE INDEX idx_year ON

Index on expression
expression. student(EXTRACT(YEAR FROM dob));
Nested Queries (subqueries) in MonetDB

Keyword Description Example

Subquery in Compare with a single SELECT sname, cgpa FROM student WHERE cgpa >
WHERE (scalar) value from a subquery. (SELECT AVG(cgpa) FROM student);

Subquery in Project derived value SELECT sname, (SELECT MAX(cgpa) FROM student) AS
SELECT alongside each row. max_cgpa FROM student;

Subquery in
Use a subquery as a SELECT branch, AVG(cgpa) FROM (SELECT * FROM
FROM (inline
virtual table. student WHERE cgpa>=8) AS top GROUP BY branch;
view)

Match values against set SELECT sname FROM student WHERE branch IN (SELECT
IN with subquery
from subquery. DISTINCT branch FROM student WHERE cgpa>8.5);

SELECT sname FROM student s WHERE EXISTS (SELECT

EXISTS Check if related rows
1 FROM student t WHERE [Link]=[Link] AND
subquery exist.
[Link]>9);

Opposite of EXISTS; SELECT sname FROM student s WHERE NOT EXISTS

NOT EXISTS returns when no match (SELECT 1 FROM student t WHERE [Link]=[Link]
found. AND [Link]>9);

Compare with any value SELECT sname, cgpa FROM student WHERE cgpa > ANY
ANY / SOME
from subquery. (SELECT cgpa FROM student WHERE branch='ECE');

Compare with all values SELECT sname, cgpa FROM student WHERE cgpa > ALL
ALL
from subquery. (SELECT cgpa FROM student WHERE branch='MECH');

Update rows using UPDATE student SET branch='TOPPER' WHERE

Nested update
subquery result. cgpa=(SELECT MAX(cgpa) FROM student);

SELECT [Link], [Link] FROM student s1 WHERE

Correlated Subquery depends on
[Link] >= (SELECT AVG([Link]) FROM student s2
subquery outer row.
WHERE [Link]=[Link]);
Joins in MonetDB:

course_id cname branch regno

1 Database Systems CSE 25001

2 Digital Electronics ECE 25002

3 Electrical Machines EEE 25003

4 Operating Systems IT 25004

5 Thermodynamics MECH 25005

Operation Description Example Query

CREATE TABLE course ( course_id SERIAL, cname

Create course Creates a course table
VARCHAR(100) NOT NULL, branch VARCHAR(50),
table with linked to the student
regno INT, FOREIGN KEY (regno) REFERENCES
foreign key table via regno
student(regno) );

INSERT INTO course (cname, branch, regno)

SELECT 'Database Systems', 'CSE', regno FROM student
WHERE sname = 'Rohan Mehta'
UNION ALL
SELECT 'Digital Electronics', 'ECE', regno FROM student
WHERE sname = 'Ananya Gupta'

Inserts a record for UNION ALL

Insert CSE
Database Systems SELECT 'Electrical Machines', 'EEE', regno FROM student
course
offered to regno 101 WHERE sname = 'Vikram Reddy'
UNION ALL
SELECT 'Operating Systems', 'IT', regno FROM student
WHERE sname = 'Priya Nair'
UNION ALL
SELECT 'Thermodynamics', 'MECH', regno FROM student
WHERE sname = 'Karthik Sharma';

Show all courses in

Show courses SELECT * FROM course ORDER BY course_id;
ascending order

Foreign key violation

INSERT INTO course (cname, branch, regno) VALUES ('Artificial Intelligence', 'AIML', 26001), ('Cloud
Computing', 'CLOUD', 26002);
INSERT INTO course (cname, branch, regno) VALUES ('Artificial Intelligence', 'AIML', 25001), ('Cloud
Computing', 'CLOUD', 25002);

Insert two students:

INSERT INTO student (sname, dob, cgpa, address, branch) VALUES ('Aditi Rao', DATE '2006-09-15', 8.60,
CAST('{"street":"Anna Nagar","city":"Chennai","state":"TN"}' AS JSON), 'AI'), ('Harish Patel', DATE
'2005-05-27', 7.95, CAST('{"street":"Law Garden","city":"Ahmedabad","state":"GJ"}' AS JSON), 'CIVIL');

Insert one row in course:

INSERT INTO course (cname, branch, regno) VALUES ('Quantum Computing', 'PHY', NULL);

Join Queries:

Keyword Description Example

INNER Returns rows where values match SELECT [Link], [Link] FROM student s INNER
JOIN in both tables. JOIN course c ON [Link]=[Link];

LEFT Returns all students, with courses SELECT [Link], [Link] FROM student s LEFT JOIN
JOIN if available; otherwise NULL. course c ON [Link]=[Link];

RIGHT Returns all courses, with matching SELECT [Link], [Link] FROM student s RIGHT
JOIN students if available. JOIN course c ON [Link]=[Link];

FULL
Returns all students and all SELECT [Link], [Link] FROM student s FULL
OUTER
courses, matches where possible. OUTER JOIN course c ON [Link]=[Link];
JOIN

CROSS Returns Cartesian product of SELECT [Link], [Link] FROM student s CROSS
JOIN students × courses. JOIN course c;

NATURAL Joins automatically on columns SELECT sname, cname FROM student NATURAL JOIN
JOIN with same name (sid). course;

INSERT INTO student (sname, dob, cgpa, address,

branch) VALUES ('Divya Menon', DATE '2006-03-20',
8.25, CAST('{"street":"MG
Road","city":"Bengaluru","state":"KA"}' AS JSON),
Student table joined with itself 'CSE'), ('Rahul Iyer', DATE '2006-06-10', 7.90,
SELF
(e.g., pair students in same CAST('{"street":"Guindy","city":"Chennai","state":"TN"}'
JOIN
branch). AS JSON), 'ECE');
SELECT [Link] AS st1, [Link] AS st2, [Link]
FROM student a JOIN student b ON [Link]=[Link]
AND [Link]<[Link];
Keyword Description Example

JOIN with SELECT [Link], [Link] FROM student s JOIN course

Join with non-equality condition.
inequality c ON [Link]<>[Link];

JOIN with Shortcut when both tables have SELECT sname, cname FROM student JOIN course
USING same column name. USING(regno);

CREATE TABLE department ( dept_id SERIAL,

dept_name VARCHAR(100) NOT NULL, branch
VARCHAR(50) UNIQUE);
INSERT INTO department (dept_name, branch) VALUES
('Computer Science and Engineering', 'CSE'), ('Electronics
and Communication Engineering', 'ECE'), ('Electrical and
Electronics Engineering', 'EEE'), ('Information
Multi-table Join more than two tables (extend Technology', 'IT'), ('Mechanical Engineering', 'MECH'),
JOIN if more exist). ('Artificial Intelligence and Machine Learning', 'AIML'),
('Cloud Technology', 'CLOUD'), ('Civil Engineering',
'CIVIL');

SELECT [Link], [Link], d.dept_name FROM student s

JOIN course c ON [Link]=[Link] JOIN department d
ON [Link]=[Link];

Admin Commands in MonetDB

Keyword Description Example Output

CREATE USER alice WITH PASSWORD

Create a new database operation
CREATE USER 'alice123' NAME 'Alice Kumar' SCHEMA
user with password. successful
university;

Create a new role (group operation

CREATE ROLE CREATE ROLE faculty;
of privileges). successful

GRANT (to Give user access to a GRANT SELECT, INSERT ON student TO operation
user) schema or object. alice; successful

GRANT (role to operation

Assign a role to a user. GRANT faculty TO alice;
user) successful

REVOKE Remove specific operation

REVOKE INSERT ON student FROM alice;
(privilege) privileges from a user. successful

Remove a role from a operation

REVOKE (role) REVOKE faculty FROM alice;
user. successful
Keyword Description Example Output

ALTER USER Change a user’s ALTER USER alice WITH PASSWORD operation
(password) password. 'newpass123'; successful

ALTER USER Change default schema operation

ALTER USER alice SET SCHEMA sys;
(schema) of a user. successful

Remove a user account operation

DROP USER DROP USER IF EXISTS alice;
completely. successful

Delete a role from the operation

DROP ROLE DROP ROLE IF EXISTS faculty;
system. successful