RDBMS

Diploma – 3rd Semester, Computer Engineering

Unit 2

SQL In-built Functions

and Joins

Prepared by:

Bharti D. Mulchandani

Lecturer, Computer Engineering Department

R. C. Technical Institute, Ahmedabad

Course Outcome (CO)

Use joins and subqueries to retrieve and manipulate data from multiple tables.

BDM | RDBMS – Unit 2

1

2.1 SQL Operators — The DUAL Table

• DUAL is mainly used to check mathematical expressions, find the system date/time, and perform quick calculations.
• It is a kind of worktable provided by Oracle — a dummy table.
• It is a special one-row, one-column table.
• DUAL is part of the data dictionary; its schema cannot be changed.

DESC dual; → Column: DUMMY VARCHAR2(1)

SELECT * FROM dual; → returns one row: 'X' (D = Dummy, X = dummy character)

SELECT 10 * 5 FROM dual; → 50 SELECT USER FROM dual; → current username

BDM | RDBMS – Unit 2

2

SQL Operators — Overview & Sample Table

• Operators perform operations on data, usually inside an expression or a WHERE clause condition.
• Four categories: 1) Arithmetic 2) Relational (Comparison) 3) Logical 4) Special (IN, ANY, ALL, BETWEEN, EXISTS, LIKE).

Table 'person' — used throughout this unit:

BDM | RDBMS – Unit 2

3

SQL Operators — Arithmetic

• Perform arithmetic operations on numeric operands — including data stored in a table.
• Operators: Addition (+), Subtraction (−), Multiplication (*), Division (/), and parentheses ( ) to control order of operation.

SELECT 5+3, 5-3, 5*3, 5/3 FROM dual; → 8 2 15 1.667

Example — add a bonus of 100 to every person's balance:

SELECT sr, name, balance "CURRENT BAL", balance+100 "NEW BAL" FROM person;

BDM | RDBMS – Unit 2

4

SQL Operators — Relational (Comparison)

• Used to compare one expression with another; the result is TRUE, FALSE, or unknown.
• Operators: = , > , < , >= , <= , != or <> (not equal).

Example — balance greater than 15000:

SELECT * FROM person WHERE balance > 15000;

Example — persons living in Rajkot:

SELECT * FROM person WHERE city = 'rajkot'; → shreeja, parth

BDM | RDBMS – Unit 2

5

SQL Operators — Logical (AND, OR, NOT)

SELECT name,city,balance FROM person WHERE city='ahmedabad' AND balance<5000; → samay, avani

SELECT name,city,balance FROM person WHERE city='ahmedabad' OR balance<5000; → samay, avani, misha, aarav

SELECT * FROM person WHERE NOT city = 'ahmedabad'; → all persons except samay, avani, misha

• Use AND when a row must satisfy every condition; use OR when satisfying any one condition is enough; use NOT to exclude rows matching a condition.

BDM | RDBMS – Unit 2

6

Range Operators — BETWEEN, IN, NOT IN

BETWEEN — checks a range (inclusive)

…ColumnName BETWEEN lower_limit AND upper_limit;

SELECT * FROM person WHERE balance BETWEEN 10000 AND 20000; → bhagya(15000), vihan(10000), tushar(19000)

IN — matches any value in a list (like 'multiple OR')

SELECT * FROM person WHERE city IN ('ahmedabad','rangpur','palanpur'); → 5 rows (vihan, samay, tushar, avani, misha)

NOT IN — opposite of IN

SELECT * FROM person WHERE city NOT IN ('ahmedabad','rangpur','palanpur'); → remaining 5 rows

• Syntax: …ColumnName IN (value1, value2, …, valueN); and …ColumnName NOT IN (value1, value2, …, valueN);

BDM | RDBMS – Unit 2

7

Special Operators — LIKE (Pattern Matching)

• Used for pattern matching within a string — unlike '=' which needs an exact match.
• Two wildcards: % (any number of characters) and _ (exactly one character).

SELECT name FROM person WHERE name LIKE 'a%'; -- starts with 'a' → aarush, avani, aarav

SELECT name FROM person WHERE name LIKE '_a%'; -- 'a' as 2nd letter → samay, aarush, parth, aarav

SELECT name,surname FROM person WHERE name LIKE '_____'; -- exactly 5 characters long

BDM | RDBMS – Unit 2

8

Special Operators — ALL & ANY

ALL — TRUE if the condition holds against every value from the subquery

…column operator ALL (subquery)

SELECT name,surname,balance FROM person

WHERE balance > ALL (SELECT balance FROM person WHERE surname='parikh'); → misha (55000)

ANY — TRUE if the condition holds against at least one value ('at least one match')

…column operator ANY (subquery)

SELECT name,balance FROM person

WHERE balance > ANY (SELECT balance FROM person WHERE surname='parikh'); → bhagya, vihan, tushar, aarush, misha

• Both ALL and ANY must be preceded by a comparison operator (>, <, =, etc.) and are generally used with subqueries.
• 'parikh' balances are {15000, 5000, 22000, 5000} — ALL needs > 22000 (its max), ANY only needs > 5000 (its min).

BDM | RDBMS – Unit 2

9

Special Operators — EXISTS

• Checks whether a subquery returns any rows at all — result is a Boolean (TRUE/FALSE).
• EXISTS doesn't care what the subquery returns, only whether it returns anything.
• Generally used with correlated subqueries.

SELECT column_names FROM table_name WHERE EXISTS (subquery);

SELECT name, city FROM person

WHERE EXISTS (SELECT * FROM person WHERE city='ahmedabad'); → all 10 rows (subquery found a match)

• If the subquery returns at least one row → the outer query runs and returns its own result.
• If the subquery returns nothing → the outer query gives no output at all.

BDM | RDBMS – Unit 2

10

Concatenation ( || ) & IS NULL

Concatenation operator ( || )

• Links two or more columns/expressions together, regardless of their data type.

SELECT name || surname "FULL NAME" FROM person; → bhagyaparikh, vihansagar, shreejaparikh, …

IS NULL — find rows with a missing value

• Retrieves records where a column has no value (NULL) — useful for data analysis.
• Example table 'employees': managers are the rows where manager_id is NULL.

SELECT * FROM employees WHERE manager_id IS NULL;

BDM | RDBMS – Unit 2

11

System Date: SYSDATE & SYSTIMESTAMP

SYSDATE

• A pseudo-column holding the current date and time.
• With the DUAL table, returns the current date in DD-MON-YY format.

SELECT SYSDATE FROM dual;

SYSTIMESTAMP

• Returns the system date including fractional seconds and time zone.

SELECT SYSTIMESTAMP FROM dual;

→ 07-JUN-25 03.02.52.220000 PM +05:30

BDM | RDBMS – Unit 2

12

2.2 SQL In-built Functions — Overview

• Pre-defined functions provided by Oracle — also called Oracle functions.
• Useful for mathematical calculations, string manipulation, sub-strings, and more.
• General format: Function_name (argument1, argument2, …, argumentN)

Aggregate Functions

Group functions — operate on many rows, return one summary value (MAX, MIN, SUM, AVG, COUNT)

Scalar Functions

Single-row functions — operate on one row/value, return one value per row (Numeric, Character, Date, Conversion)

BDM | RDBMS – Unit 2

13

Aggregate Functions — MAX & MIN

MAX() — returns the maximum value of a column

SELECT MAX(balance) FROM person; → 55000

SELECT MAX(name) FROM person; → vihan (works on character/date types too)

MIN() — returns the minimum value of a column

SELECT MIN(balance) FROM person; → 200

SELECT MIN(name) FROM person; → aarav

• Syntax: MAX(ColumnName) and MIN(ColumnName)
• Both are aggregate (group) functions — they process many rows but return a single value.

BDM | RDBMS – Unit 2

14

Aggregate Functions — SUM & AVG

SUM() — total of all values in a column

SUM(ColumnName) OR SUM([DISTINCT] ColumnName)

SELECT SUM(balance), SUM(DISTINCT balance) FROM person; → 133400, 133400

AVG() — average of all values in a column

AVG([DISTINCT] ColumnName)

SELECT AVG(balance), AVG(DISTINCT balance) FROM person; → 13340, 13340

• DISTINCT removes duplicate values before computing the SUM/AVG; without it, all values are used by default.

BDM | RDBMS – Unit 2

15

Aggregate Functions — COUNT(*) & COUNT()

COUNT(*) — total rows in a table, including NULLs

SELECT COUNT(*) FROM person; → 10

COUNT(column) — total non-NULL values in a column

SELECT COUNT(city), COUNT(DISTINCT city) FROM person; → 10, 4

• COUNT(*) counts every row, NULL or not.
• COUNT(column) skips NULLs and can be combined with DISTINCT to count unique values only.
• Here, DISTINCT city = 4 because only ahmedabad, rangpur, rajkot, and palanpur appear — ahmedabad itself repeats across 6 rows.

BDM | RDBMS – Unit 2

16

Scalar (Single-Row) Functions — Numeric

• Scalar functions operate on a single row/value and return one result per record.
• Four families: Numeric, Character, Date, and Conversion functions.

ABS(n) — absolute value (drops the negative sign)

SELECT ABS(-10), ABS(10), ABS(10.5) FROM dual; → 10, 10, 10.5

SQRT(n) — square root (n cannot be negative)

SELECT SQRT(25), SQRT(24) FROM dual; → 5, 4.899

• For any scalar/character/conversion function asked in an exam, always state: (1) It's a scalar/single-row function, and (2) which family it belongs to (numeric/character/date/conversion).

BDM | RDBMS – Unit 2

17

Numeric Functions — POWER, MOD, CEIL, FLOOR

• All four are scalar / single-row numeric functions.
• CEIL and FLOOR are not part of the GTU syllabus but are commonly useful to know.

BDM | RDBMS – Unit 2

18

Numeric Functions — ROUND vs TRUNC

ROUND(m, n) -- rounds m to n decimal places

SELECT ROUND(123.45,2), ROUND(123.45,-1), ROUND(123.45) FROM dual; → 123.45, 120, 123

TRUNC(m, n) -- truncates m to n decimal places (no rounding)

SELECT TRUNC(123.456,2), TRUNC(123.456), TRUNC(123.456,-2) FROM dual; → 123.45, 123, 100

Side-by-side comparison:

BDM | RDBMS – Unit 2

19

Character Functions — LENGTH, LOWER/UPPER, INITCAP

• Character functions accept a string as input and return either a character or numeric result.
• Two types: Case-Manipulative (LOWER, UPPER, INITCAP) and Character-Manipulative (CONCAT, LENGTH, SUBSTR, INSTR, TRIM, REPLACE, TRANSLATE, LPAD, RPAD).

SELECT LENGTH('hello') FROM dual; → 5

SELECT LOWER('BHAGYA'), UPPER('bhagya aarush') FROM dual; → bhagya, BHAGYA AARUSH

SELECT INITCAP('bharti d mulchandani') FROM dual; → Bharti D Mulchandani

• LOWER converts to lower case, UPPER to upper case, INITCAP capitalizes the first letter of every word.

BDM | RDBMS – Unit 2

20

Character Functions — TRIM, LTRIM, RTRIM

TRIM — removes leading/trailing spaces (or given characters) by default

SELECT TRIM(' aarush ') FROM dual; → 'aarush'

LTRIM(string, set) — removes matching characters from the LEFT

SELECT LTRIM('123BHAGYA','123') FROM dual; → BHAGYA

SELECT LTRIM('xyzxyzBHAGYA','xyz') FROM dual; → BHAGYA

RTRIM(string, set) — removes matching characters from the RIGHT

SELECT RTRIM('BHAGYA','AYPB') FROM dual; → BHAG

• Removal stops at the first character that is NOT in the given 'set'.

BDM | RDBMS – Unit 2

21

Character Functions — LPAD, RPAD, SUBSTR, INSTR

SELECT LPAD('aarush',10,'*') FROM dual; → '****aarush'

SELECT RPAD('aarush',10,'*') FROM dual; → 'aarush****'

SELECT SUBSTR('Bharti Mulchandani',3,3) FROM dual; → 'art' (start at pos 3, take 3 chars)

SELECT INSTR('aarush','r') FROM dual; → 3 (position of first 'r')

SELECT INSTR('aarush parikh','X') FROM dual; → 0 (not found)

• LPAD / RPAD (not in GTU syllabus): pad a string on the left/right up to length n using string2 (default blank).
• SUBSTR(string, position, length): extracts part of a string.
• INSTR(string1, string2): returns the position of the first occurrence of string2 in string1, or 0 if not found.

BDM | RDBMS – Unit 2

22

Character Functions — REPLACE & TRANSLATE

REPLACE(string, from_string, to_string) — replaces a whole substring

SELECT REPLACE('abc12ef3','12','XY') FROM dual; → 'abcXYef3'

TRANSLATE(string, from_set, to_set) — replaces character-by-character

SELECT TRANSLATE('abc12ef3','1234','WXYZ') FROM dual; → 'abcWXefY'

• REPLACE works on strings — it swaps an entire matching substring for another.
• TRANSLATE works on individual characters — each character in from_set is mapped one-to-one to the matching character in to_set.

BDM | RDBMS – Unit 2

23

Date Functions — ADD_MONTHS & MONTHS_BETWEEN

• Dates are written as 'DD-MON-YY' in single quotes; SYSDATE can also be used directly.

ADD_MONTHS(date, n) — adds (or subtracts, if n is negative) n months

SELECT ADD_MONTHS(sysdate,1), ADD_MONTHS(sysdate,-1) FROM dual;

MONTHS_BETWEEN(date1, date2) — months between two dates

SELECT MONTHS_BETWEEN(sysdate,'13-NOV-99') FROM dual; → ~293.5 (months since Nov 1999)

Example — calculate everyone's age from the person table:

SELECT name, bdate, FLOOR(MONTHS_BETWEEN(SYSDATE,bdate)/12) "AGE" FROM person;

• Dividing months by 12 and flooring gives whole years of age.

BDM | RDBMS – Unit 2

24

Date Functions — ROUND & TRUNC

ROUND(date, unit) — rounds a date to the nearest day/month/year

SELECT ROUND(sysdate) FROM dual; -- rounds to nearest day

SELECT ROUND(sysdate,'year') FROM dual; -- rounds to nearest year (Jan 1)

TRUNC(date, unit) — truncates a date down to a specific unit

SELECT TRUNC(sysdate,'day') FROM dual; -- back to the start of the week

SELECT TRUNC(sysdate,'month') FROM dual; -- back to the 1st of the month

• Unit is optional for both functions; common units are 'day', 'month', and 'year'.
• ROUND rounds to the nearest unit, while TRUNC always rounds down.

BDM | RDBMS – Unit 2

25

Conversion Functions — TO_NUMBER & TO_CHAR (number)

TO_NUMBER(string) — converts CHAR/VARCHAR2 into a NUMBER

• The string must consist of digits 0–9, a decimal point, and an optional +/- sign.

SELECT TO_NUMBER('123'), TO_NUMBER('123.45') FROM dual; → 123, 123.45

TO_CHAR(n, format) — converts a NUMBER into formatted text

• Format uses '0' (zero-padded digit), '9' (digit), and ',' (thousands separator).

SELECT TO_CHAR(123456,'09,99,999') FROM dual; → '01,23,456'

SELECT TO_CHAR(12345,'99,999') FROM dual; → '12,345'

BDM | RDBMS – Unit 2

26

Conversion Functions — TO_CHAR (date) & TO_DATE

SELECT TO_CHAR(sysdate,'dd-mon-yyyy') FROM dual; → 17-sep-2023

SELECT TO_CHAR(sysdate,'day-mon-yyyy') FROM dual; → sunday-sep-2023

SELECT TO_DATE('13 november 2019','DD MONTH YYYY') FROM dual; → 13-NOV-19

Common format specifiers:

• If the format is omitted, TO_CHAR(date) defaults to 'DD-MON-YY'; TO_DATE always returns that same default format regardless of the input string's layout.

BDM | RDBMS – Unit 2

27

2.3 SQL Clauses — ORDER BY

• SELECT does not guarantee any particular row order — ORDER BY sorts the result.
• Default order is ascending; add DESC for descending order.

SELECT columnNames FROM table_name ORDER BY columnName [DESC];

SELECT name, balance FROM person ORDER BY balance; -- ascending

BDM | RDBMS – Unit 2

29

SQL Clauses — GROUP BY

• Groups rows that share the same value(s) in specified column(s).
• Placed after WHERE and before ORDER BY. Typically paired with an aggregate function (COUNT, MAX, SUM, AVG, etc.), returning one row per group.

SELECT columnNames, AggregateFunction(arg) FROM table_name WHERE condition GROUP BY columnNames;

SELECT surname, SUM(balance) "Total Balance" FROM person GROUP BY surname;

BDM | RDBMS – Unit 2

30

SQL Clauses — HAVING

• Filters the groups produced by GROUP BY — like a WHERE clause, but for groups.
• HAVING requires a GROUP BY clause; only groups meeting the HAVING condition appear in the output.

SELECT columnNames, ... FROM table_name WHERE condition GROUP BY columnNames HAVING condition;

SELECT surname, COUNT(surname) FROM person GROUP BY surname HAVING surname = 'parmar';

• Without HAVING, GROUP BY alone would list all 6 surnames; HAVING narrows the grouped result down to just 'parmar'.

BDM | RDBMS – Unit 2

31

2.4 JOINS in SQL — Overview

• Joins combine records from two or more tables using common columns.
• The result is a new, wider table where each row is a concatenation of matching rows.
• Types: CROSS JOIN, INNER JOIN (equi / non-equi), SELF JOIN, and OUTER JOIN (Left / Right / Full).

Sample tables used throughout this section:

Table: test1

Table: test2

BDM | RDBMS – Unit 2

32

JOINS — CROSS JOIN (Cartesian Product)

• The simplest join — also called a Cartesian product.
• Combines every row of one table with every row of the other, with no filtering condition.

SELECT column1, ..., columnN FROM table1, table2;

SELECT * FROM test1, test2; -- 3 rows × 3 rows = 9 rows total

BDM | RDBMS – Unit 2

33

JOINS — INNER JOIN

• Filters the CROSS JOIN output down to only matching (consistent) rows.
• Returns records that have matching values in both tables — an 'equi-join' when the match uses '='; any other comparison operator makes it a 'non-equi join'.

SELECT column_names FROM table1, table2 WHERE table1.col OP table2.col;

SELECT column_names FROM table1 INNER JOIN table2 ON table1.col = table2.col;

SELECT test1.id, name, city FROM test1, test2 WHERE test1.id = test2.id;

BDM | RDBMS – Unit 2

34

JOINS — OUTER JOIN (Overview)

• OUTER JOIN extends INNER JOIN to prevent losing unmatched rows.
• Notice: an INNER JOIN of test1 and test2 drops 'bhagya' (id=1) because test2 has no id=1 — outer joins fix this.

Left Outer Join

All rows from the LEFT table + matches from the right; unmatched → NULL

Right Outer Join

All rows from the RIGHT table + matches from the left; unmatched → NULL

Full Outer Join

All rows from BOTH tables; unmatched sides → NULL

BDM | RDBMS – Unit 2

35

JOINS — LEFT OUTER JOIN

SELECT * FROM table1, table2 WHERE table1.col = table2.col(+);

SELECT * FROM table1 LEFT JOIN table2 ON table1.col = table2.col;

Example — left outer join of test1 and test2:

SELECT * FROM test1 left join test2 on test1.id = test2.id;

• The (+) marker on the right table's column marks it as the 'outer' side in classic Oracle syntax.

BDM | RDBMS – Unit 2

36

JOINS — RIGHT OUTER JOIN

SELECT * FROM table1, table2 WHERE table1.col(+) = table2.col;

SELECT * FROM table1 RIGHT JOIN table2 ON table1.col = table2.col;

Example — right outer join of test1 and test2:

SELECT * FROM test1 right join test2 on test1.id = test2.id;

• This time the (+) is on the left table's column — the right table (test2) keeps every row.

BDM | RDBMS – Unit 2

37

JOINS — FULL OUTER JOIN & Summary

​

SELECT * FROM t1 FULL JOIN t2 ON t1.col = t2.col;

• or: (Left Outer Join) UNION (Right Outer Join) SELECT * FROM test1 full join test2 on test1.id =test2.id;

BDM | RDBMS – Unit 2

38

JOINS — SELF JOIN

• A table joined with itself — each row is combined with other rows of the same table.
• Two 'copies' of the table are opened in memory, distinguished using two aliases.

SELECT col1,... FROM table1 alias1, table1 alias2 WHERE condition;

Example table 'test_self' — br_no is a person's brother's row no., sis_no is their sister's row no.:

SELECT a.name, b.name "Sister" FROM test_self a, test_self b WHERE a.sis_no = b.no;

→ aarush–avani, aarav–avani, bhagya–avani

BDM | RDBMS – Unit 2

39

2.5 SUB-QUERIES — Introduction

• A subquery is also called an 'inner query' or 'nested query' — simply, a query within another query.
• The statement containing the subquery is the 'parent query', 'outer query' or 'main query'.
• Subqueries can be chained (a subquery inside a subquery).
• A subquery must be enclosed in parentheses.
• ORDER BY cannot be used inside a subquery.

SELECT columnNames FROM tableName1

WHERE value ExpressionOperator (SELECT columnNames FROM tableName2 WHERE condition);

BDM | RDBMS – Unit 2

40

SUB-QUERIES — Example & Chain of Subqueries

Find the city of the person named 'aarush' (using test1, test2 from before):

SELECT city FROM test2 WHERE id IN (SELECT id FROM test1 WHERE name='aarush'); → ahmedabad

Chain of subqueries — three related tables:

Find the id of the faculty teaching subject 3330701:

SELECT id FROM teacher WHERE name = (SELECT name FROM teaches

WHERE subject = (SELECT subject FROM subject WHERE subid = 3330701)); → id = 2 (bdp)

BDM | RDBMS – Unit 2

41

SUB-QUERIES — EXISTS & Correlated Subqueries

EXISTS in a subquery:

SELECT subject FROM teaches t WHERE EXISTS (SELECT name FROM teacher WHERE name=t.name);

Correlated subquery — inner query re-runs for every outer row:

• Find the highest balance for each surname in 'person':

SELECT name, surname, balance FROM person P

WHERE balance IN (SELECT MAX(balance) FROM person WHERE surname = P.surname);

• Alias 'P' lets the inner query reference the outer row's surname — this is what makes it 'correlated'.

BDM | RDBMS – Unit 2

42

2.6 SET Operators in SQL

• Combine the results of two or more SELECT statements into one 'compound query'.

• For UNION / INTERSECT / MINUS: matching data types and column counts are required, duplicates are removed, NULLs are ignored, and results are sorted ascending.

BDM | RDBMS – Unit 2

43

SET Operators — UNION & UNION ALL

test1

test2

SELECT * FROM test1 UNION SELECT * FROM test2; → 5 rows (1,2,3,4,5) — no duplicate '3 shreeja'

SELECT * FROM test1 UNION ALL SELECT * FROM test2; → 6 rows — '3 shreeja' appears twice

• UNION removes duplicate rows automatically; UNION ALL keeps every row from both queries, including duplicates.

BDM | RDBMS – Unit 2

44

SET Operators — INTERSECT & MINUS

INTERSECT — rows common to both queries

SELECT * FROM test1 INTERSECT SELECT * FROM test2; → 3 shreeja

MINUS — rows in the first query only, not the second

SELECT * FROM test1 MINUS SELECT * FROM test2; → 1 bhagya, 2 aarush

Practice example — collegeA, collegeB & hostelABCD:

SELECT * FROM collegeA INTERSECT SELECT * FROM hostelABCD;

-- collegeA students living in the hostel → a1 Aone, a3 Athree, a4 Afour

• Try yourself: which students from BOTH collegeA and collegeB live in the hostel? (Hint: UNION the two colleges first, then INTERSECT with hostelABCD.)

BDM | RDBMS – Unit 2

45

SET Operators — Summary

Set A = {1, 2, 3, 4} Set B = {3, 4, 5}

A UNION B

{1, 2, 3, 4, 5}

A UNION ALL B

{1,2,3,3,4,4,5}

A INTERSECT B

{3, 4}

A MINUS B

{1, 2}

BDM | RDBMS – Unit 2

46

Problem Solving Practice

Using the 'person' table from this unit, write SQL for:

• 1. Display names of persons whose city starts with 'r'.
• 2. Find the total and average balance for each city.
• 3. Show cities having more than one person living there.
• 4. Find the person(s) with the second-highest balance (hint: use a subquery with MAX and NOT IN).
• 5. Display each person's name in uppercase along with the length of their name.
• 6. List persons whose balance is above the overall average balance.

Sample solution for Q3:

SELECT city, COUNT(*) FROM person GROUP BY city HAVING COUNT(*) > 1;

BDM | RDBMS – Unit 2

47

Quick Check: MCQs

1. Which is NOT an SQL arithmetic operator?

a) Modulus b) Addition c) Subtraction d) Unary

2. Which join returns all rows from both tables, with NULLs where there's no match?

a) INNER JOIN b) LEFT OUTER JOIN c) RIGHT OUTER JOIN d) FULL OUTER JOIN

3. Which SQL join type is also called an 'equi-join'?

a) OUTER JOIN b) INNER JOIN c) CROSS JOIN d) SELF JOIN

4. What does a CROSS JOIN return?

a) Only matching rows b) All row combinations c) Only left table rows d) Only unmatched rows

5. Which set operator keeps duplicate rows?

a) UNION b) UNION ALL c) INTERSECT d) MINUS

BDM | RDBMS – Unit 2

48

Thank You

Bharti D. Mulchandani

Lecturer, Computer Engineering Department

R. C. Technical Institute, Ahmedabad