How the system exam works

You get a machine with Oracle. You type a query, run it, show output, explain it. These 5 facts decide pass/fail.

-- the dummy table
DESC DUAL;
SELECT COUNT(*) FROM DUAL;  -- answer is always 1
SELECT 'If you want to know what a man is like…' FROM DUAL;
SELECT SYSDATE FROM DUAL;  -- today's date; also works FROM any table

Create, alter, add & modify data

Everything else stands on this. If you can build a table and change it, you can do Expts 2–6.

1A · Data types you actually use

1B · CREATE — build the table (Expt 2 Q1 / Expt 3 Q1 / Expt 6 Q1 pattern)

CREATE TABLE employee_details (
  id        NUMBER(3),
  emp_name  VARCHAR2(20),
  age       NUMBER(3),
  city      VARCHAR2(20),
  salary    NUMBER(8,2)
);

CREATE TABLE employee_book (
  employee_id      NUMBER(10),
  employee_name    VARCHAR2(20),
  dob              DATE,
  date_of_joining  DATE,
  employee_salary  NUMBER(10)
);

1C · INSERT — add rows

INSERT INTO employee_details VALUES (101, 'Arun', 25, 'Kurnool', 7500);
INSERT INTO employee_book VALUES (101, 'Arun Kumar', TO_DATE('15-05-1990','DD-MM-YYYY'), TO_DATE('10-06-2018','DD-MM-YYYY'), 75000);
-- check what you did
SELECT * FROM employee_details;

1D · ALTER — change the structure after creation

-- add a column
ALTER TABLE employee_details ADD (email VARCHAR2(30));
-- change size / type
ALTER TABLE employee_details MODIFY (city VARCHAR2(30));
-- rename a column (Oracle)
ALTER TABLE employee_details RENAME COLUMN city TO work_city;
-- drop a column
ALTER TABLE employee_details DROP COLUMN email;

1E · UPDATE / DELETE / TRUNCATE / DROP — change data vs destroy

-- Expt 2 Q13 pattern: change salary 8500 → 9000 for id 5
UPDATE employee SET salary = 9000 WHERE id = 5;
-- DANGER: no WHERE = every row changes. Always write WHERE first.
DELETE FROM employee_details WHERE id = 105;   -- removes rows, keeps table
TRUNCATE TABLE employee_details;  -- removes ALL rows fast, keeps table
DROP TABLE employee_details;      -- deletes table itself
DESC employee_details;  -- describe structure anytime

Comparison, BETWEEN, IN, LIKE, NULL

All on table EMPLOYEE(id, name, age, address, salary). Every query is SELECT * FROM employee WHERE …

2A · Comparison — salary filters (Q2)

SELECT * FROM employee WHERE salary > 6000;
-- all six: =  !=  <>  >  <  >=  <=   (!= and <> both mean not-equal)

2B · BETWEEN — a range, inclusive (Q3–Q4)

SELECT * FROM employee WHERE salary BETWEEN 2000 AND 9000;
SELECT * FROM employee WHERE salary NOT BETWEEN 2000 AND 9000;
-- BETWEEN includes both ends. NOT BETWEEN = outside the range.

2C · LIKE — pattern matching (Q5–Q8). The most-asked block.

SELECT * FROM employee WHERE name LIKE 'K%';     -- starts with K (Q5; PDF wrongly shows p%)
SELECT * FROM employee WHERE address LIKE '%i';  -- ends with i
SELECT * FROM employee WHERE name LIKE '%a%';   -- contains a (use %a%, not _a%)
SELECT * FROM employee WHERE address LIKE '_____'; -- exactly 5 chars = 5 underscores
SELECT * FROM employee_details WHERE emp_name NOT LIKE 'B%'; -- Expt 3 Q15

2D · NULL — the only correct syntax (Q9–Q10)

SELECT * FROM employee WHERE salary IS NULL;
SELECT * FROM employee WHERE salary IS NOT NULL;
-- WRONG: WHERE salary = NULL  (never returns rows)

2E · IN — list membership (Q11–Q12)

SELECT * FROM employee WHERE age IN (20, 25);
SELECT * FROM employee WHERE age NOT IN (20, 25);
-- same as: WHERE age = 20 OR age = 25

AND / OR / NOT, aliases, ORDER BY, concatenation, arithmetic

Table EMPLOYEE_DETAILS(id, emp_name, age, city, salary). This experiment is about combining conditions and formatting output.

3A · Logical operators (Q2–Q4)

SELECT * FROM employee_details WHERE age = 25 AND city = 'kurnool';
SELECT * FROM employee_details WHERE age = 25 OR city = 'bangalore';
SELECT * FROM employee_details WHERE age != 25;  -- also: <>  or  NOT (age = 25)

3B · Aliases — rename output columns (Q5–Q6, Q11–Q12)

SELECT id, emp_name, salary, salary * 1.1 AS "expected salary" FROM employee_details;
SELECT id, emp_name, 60 - age AS "retirement years" FROM employee_details;
SELECT id, emp_name, salary, salary * 0.75 AS "salary after cut" FROM employee_details;
-- quotes needed only when alias has a space. AS is optional but use it.

3C · Arithmetic in SELECT (Q5–Q6, Q11–Q14, Expt 6 Q3/Q6)

SELECT 100 * 20 + 40 - 200 FROM DUAL;              -- 1840, normal maths priority
SELECT (((200+350) - (30+30)) * 1.5) / 2 FROM DUAL;  -- Expt 6 Q6 = 367.5
SELECT +3 FROM DUAL;  -- unary plus = 3
SELECT -3 FROM DUAL;  -- unary minus = -3
-- salary maths:
SELECT salary * 1.1 AS "10% hike", salary * 1.05 AS "5% hike", salary * 0.75 AS "25% cut" FROM employee_details;

3D · ORDER BY (Q7–Q8)

SELECT * FROM employee_details ORDER BY salary;          -- ascending = default
SELECT * FROM employee_details ORDER BY salary ASC;
SELECT * FROM employee_details ORDER BY age DESC;      -- descending

3E · Concatenation — the corrected version (Q9)

-- CORRECT Oracle way (use this in exam):
SELECT 'The employee name is ' || emp_name || ' and his age is ' || age || ' from ' || city || ' getting Rs ' || salary FROM employee_details;
-- CONCAT only joins 2 strings, so nest it:
SELECT CONCAT(CONCAT('The employee name is ', emp_name), ' ...') FROM DUAL;

3F · SYSDATE (Q10)

SELECT SYSDATE AS "Today's Date" FROM DUAL;

UNION, INTERSECT, MINUS and UPPER / LOWER / INITCAP

Tables cricket_players / football_players / basketball_players(player_id, player_name, section). Set operators stack result sets vertically.

4A · The three set operators — one mental model

SELECT * FROM cricket_players UNION SELECT * FROM football_players;
SELECT * FROM cricket_players UNION ALL SELECT * FROM football_players;
SELECT * FROM cricket_players UNION SELECT * FROM football_players UNION SELECT * FROM basketball_players;
SELECT * FROM basketball_players INTERSECT SELECT * FROM football_players;
SELECT * FROM football_players MINUS SELECT * FROM cricket_players;

4B · Case functions (Q7–Q15)

SELECT UPPER('all is well') FROM DUAL;                                        -- ALL IS WELL
SELECT UPPER('The way to get started…') FROM DUAL;                  -- ALL CAPS
SELECT UPPER(player_name) FROM football_players;                     -- Q10: whole column
SELECT LOWER('THE ONLY REAL MISTAKE…') FROM DUAL;                  -- all small
SELECT LOWER(player_name) FROM football_players;                     -- Q14
SELECT INITCAP('every man dies, not every man lives') FROM DUAL;  -- Every Man Dies, Not Every Man Lives

INSTR, SUBSTR, LPAD, RPAD, TRIM, REPLACE

Highest marks-per-line experiment. Every function below appeared verbatim in your assignment.

5A · INSTR — find position (Q1–Q4)

Template: INSTR(string, search, start_position, occurrence). Returns 0 (not NULL) when not found. Case-sensitive.

SELECT INSTR('Nothing is impossible', 't', 1, 1) FROM DUAL;   -- Q1: first 't'
SELECT INSTR('Nothing is impossible', 'b', -1, 1) FROM DUAL;  -- Q2: search backwards: use -1 start
SELECT INSTR('Nothing is impossible', 'z', 1, 1) FROM DUAL;   -- Q3: 0 = not found
SELECT INSTR('Nothing is impossible', 'I', 1, 1) FROM DUAL;   -- Q4: capital I only

5B · SUBSTR — cut out text (Q5–Q6)

Template: SUBSTR(string, start, length). Position 1 = first character.

SELECT SUBSTR('A picture is worth a thousand words', 14, 5) FROM DUAL;  -- 'worth' (w is 14th char; PDF's 13,6 gives ' worth' with space)
SELECT SUBSTR('A picture is worth a thousand words', 1000, 6) FROM DUAL; -- NULL: past the end

5C · LPAD / RPAD — pad to a width (Q7–Q8)

Template: LPAD(string, total_length, pad_char). Total includes the original.

SELECT LPAD('Be honest', 13, '#') FROM DUAL;   -- '####Be honest' (9+4=13)
SELECT RPAD('Work Hard', 13, '$') FROM DUAL;   -- 'Work Hard$$$$'

5D · TRIM family (Q9–Q11, Q14)

SELECT LTRIM('money is not everything', 'money ') FROM DUAL;  -- Q9: strips leading set
SELECT RTRIM(LTRIM('money', 'm'), 'y') FROM DUAL;            -- Q10: 'one' (nest them)
SELECT RTRIM('not', 't') FROM DUAL;                            -- Q11: 'no'
SELECT RTRIM('Actions speak louder than words', 's') FROM DUAL; -- Q14: trims trailing s → '…word'
-- standard TRIM you can also quote:
SELECT TRIM(BOTH '#' FROM '##hi##') FROM DUAL;  -- 'hi'

5E · REPLACE (Q12–Q13, Q15)

SELECT REPLACE('Practice makes perfect', 'perfect', 'a man perfect') FROM DUAL;
SELECT REPLACE('When it rains, it pours', 'pours', 'pains') FROM DUAL;
SELECT REPLACE('You cannot have your cake and eat it too', 'not') FROM DUAL; -- 3rd arg omitted = delete 'not' → positive sentence

ROUND, TRUNC, MOD, MONTHS_BETWEEN, ADD_MONTHS, SUM / AVG / MAX / MIN

6A · ROUND vs TRUNC — the sign of the second argument

SELECT ROUND(1233.678, 0) FROM DUAL;   -- 1234 (Q7)
SELECT ROUND(1233.678, 2) FROM DUAL;   -- 1233.68 (Q8)
SELECT ROUND(1299.678, -2) FROM DUAL;  -- 1300 (Q9)
SELECT TRUNC(189.987) FROM DUAL;         -- 189: cuts, never rounds (Q10)
SELECT TRUNC(189.987, 2) FROM DUAL;      -- 189.98 (Q11)
SELECT TRUNC(189.987, -2) FROM DUAL;     -- 100 (Q12)

6B · MOD — remainder (Q13–Q15)

SELECT MOD(19987, 23) FROM DUAL;        -- integer remainder
SELECT MOD(19987.555, 23) FROM DUAL;    -- works with decimals
SELECT MOD(-19987.555, 23) FROM DUAL;   -- sign follows the dividend (negative)

6C · Dates — experience, gaps, shifts (Q16–Q17, Q22–Q23)

-- Q16 (corrected): experience in years
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, date_of_joining) / 12) AS experience_years FROM employee_book;
-- Q17: months between Jun-2018 and May-2023 ≈ 60
SELECT MONTHS_BETWEEN(TO_DATE('01-06-2023','DD-MM-YYYY'), TO_DATE('01-05-2018','DD-MM-YYYY')) FROM DUAL;
-- Q22/Q23: shift a date by ±6 months
SELECT ADD_MONTHS(TO_DATE('06-05-2023','DD-MM-YYYY'), 6) FROM DUAL;   -- +6 → Nov 2023
SELECT ADD_MONTHS(TO_DATE('06-05-2023','DD-MM-YYYY'), -6) FROM DUAL;  -- −6 → Nov 2022

6D · Aggregates — one row answers (Q18–Q21 + COUNT)

SELECT SUM(employee_salary) FROM employee_book;   -- total
SELECT AVG(employee_salary) FROM employee_book;   -- average
SELECT MAX(employee_salary) FROM employee_book;   -- highest
SELECT MIN(employee_salary) FROM employee_book;   -- lowest
SELECT COUNT(*) FROM employee_book;              -- how many rows
-- Q19 corrected: average experience
SELECT AVG(ROUND(MONTHS_BETWEEN(SYSDATE, date_of_joining) / 12)) FROM employee_book;

Your professor's pattern — and the paper she'll likely set

Decoded from Expt 2–6 PDFs (text-vs-code mismatches, Before/After boxes, “identify difference / check error / write inference / justify”). She is a Template Recycler + Edge-Case Tester + Inference Demander. Values change, concepts don't.

Her 6 fingerprints (with proof)

Predicted system paper in HER voice (Aim → Code → Output → Inference)

SELECT * FROM employee WHERE name LIKE 'S%';     -- starts with S
SELECT * FROM employee WHERE address LIKE '_____'; -- exactly 5 = 5 underscores
SELECT * FROM employee WHERE salary IS NULL;  -- never = NULL

SELECT emp_name, salary * 1.15 AS "revised salary" FROM employee_details ORDER BY age DESC;
SELECT 'The employee name is ' || emp_name || ' from ' || city FROM employee_details;
SELECT SYSDATE AS "Today's Date" FROM DUAL;

SELECT player_name FROM cricket_players UNION SELECT player_name FROM football_players;
SELECT player_name FROM cricket_players UNION ALL SELECT player_name FROM football_players;
-- deliberate error she WILL ask for:
SELECT player_id, player_name FROM cricket_players UNION SELECT * FROM basketball_players;

SELECT * FROM cricket_players WHERE player_name = 'ranga';  -- maybe 0 rows
SELECT * FROM cricket_players WHERE player_name = 'RANGA';  -- maybe 0 rows
SELECT UPPER('all is well'), LOWER('TODay'), INITCAP('every man dies') FROM DUAL;
-- fix she wants to hear:
SELECT * FROM cricket_players WHERE UPPER(player_name) = 'RANGA';

SELECT INSTR('Nothing is impossible','t',1,1) FROM DUAL;   -- 3
SELECT INSTR('Nothing is impossible','b',-1,1) FROM DUAL;  -- from end
SELECT INSTR('Nothing is impossible','z',1,1) FROM DUAL;   -- 0
SELECT SUBSTR('A picture is worth a thousand words',14,5) FROM DUAL; -- worth (PDF prints 13,6 = ' worth' with space)
SELECT LPAD('Be honest',13,'#') FROM DUAL;  -- ####Be honest (9+4)
SELECT RTRIM(LTRIM('money','m'),'y') FROM DUAL;  -- one: single command
SELECT REPLACE('When it rains, it pours','pours','pains') FROM DUAL;

SELECT ROUND(1299.678,-2), TRUNC(189.987,2), MOD(-19987.555,23) FROM DUAL;
-- corrected parens (her sheet misses one):
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, date_of_joining)/12) AS exp_yrs FROM employee_book;
SELECT ADD_MONTHS(TO_DATE('06-05-2023','DD-MM-YYYY'),6) FROM DUAL;
SELECT SUM(employee_salary), AVG(employee_salary), MAX(employee_salary), MIN(employee_salary) FROM employee_book;

Viva one-liners she listens for

15 likely system tasks — say the query, then the output

Each takes under a minute once practised. Cover one from each experiment and you cover everything.

CREATE TABLE employee (id NUMBER(10), name VARCHAR2(20), age NUMBER(3), address VARCHAR2(20), salary NUMBER(10,2));
INSERT INTO employee VALUES (1, 'Kiran', 25, 'Kurnool', 8500);
INSERT INTO employee VALUES (2, 'Anu', 22, 'Delhi', NULL);

SELECT * FROM employee WHERE salary BETWEEN 2000 AND 9000;
UPDATE employee SET salary = 9000 WHERE id = 1;

SELECT * FROM employee WHERE name LIKE 'K%';
SELECT * FROM employee WHERE address LIKE '_____';
SELECT * FROM employee WHERE salary IS NULL;
SELECT * FROM employee WHERE age IN (20, 25);

SELECT emp_name, salary * 1.1 AS "expected salary" FROM employee_details ORDER BY salary DESC;
SELECT 'The employee name is ' || emp_name FROM employee_details;

SELECT UPPER('all is well'), LOWER('TODay'), INITCAP('every man dies') FROM DUAL;

SELECT INSTR('Nothing is impossible', 't', 1, 1), SUBSTR('A picture is worth…', 14, 5) FROM DUAL;

SELECT LPAD('Be honest',13,'#'), RPAD('Work Hard',13,'$'), REPLACE('it pours','pours','pains') FROM DUAL;

SELECT ROUND(1299.678,-2), TRUNC(189.987,2), MOD(19987,23) FROM DUAL;

SELECT ROUND(MONTHS_BETWEEN(SYSDATE, date_of_joining)/12) FROM employee_book;
SELECT ADD_MONTHS(TO_DATE('06-05-2023','DD-MM-YYYY'),6) FROM DUAL;