This is a copy of a conversation between ChatGPT & Anonymous.
Report conversation
Create a STUDENT table with appropriate constraints
(Primary Key, NOT NULL, CHECK).
Q2. Alter the table to add a UNIQUE column and insert 5
records, then display using SELECT.
Q3. Write a query to display students who scored more
than 75 marks and order them in
descending order.
Q4. Drop a column from the table and then drop the entire
table.
Q5. Write a query to find the student who secured fourth
rank in the class.
Q6. Write queries using IN and ANY operators with
subqueries.
Q7. Write queries using ALL and EXISTS operators.
Q8. Write a query using NOT EXISTS with suitable
example.
Q9. Write queries using UNION and INTERSECT operators.
Q10. Write SQL queries to display the details of
employees:
a) Whose salary is greater than 30000 AND department
number is 10.
b) Who belong to department 10 OR department 20.
c) Who are NOT working in department 30.
Q11. Write queries using aggregate functions (COUNT,
SUM, AVG, MAX, MIN).
[Link] queries using GROUP BY and HAVING clause.
Q13. Create a VIEW for students scoring more than 80
marks.
Q14. Drop the created VIEW.
Q15. Write queries using conversion functions (TO_CHAR,
TO_NUMBER, TO_DATE).
Q16. Write queries using string functions (Concatenation,
LPAD, RPAD, LOWER, UPPER).
Q17. Write queries using string functions (INITCAP,
LENGTH, SUBSTR, INSTR, LTRIM, RTRIM).
Q18. Write queries to display current date and time using
SYSDATE and TO_CHAR.
Q19. Write queries using date functions (ADD_MONTHS,
NEXT_DAY, LAST_DAY).
Q20. Write queries using date functions
(MONTHS_BETWEEN, LEAST, GREATEST, TRUNC,
ROUND).
Give answers for all this neatly
I’ll give clear Oracle SQL queries step-by-step so you can easily write them in exams or
lab records.
Q1. Create a STUDENT table with constraints
CREATE TABLE STUDENT (
SID NUMBER(5) PRIMARY KEY,
SNAME VARCHAR2(30) NOT NULL,
MARKS NUMBER(3) CHECK (MARKS BETWEEN 0 AND 100),
DEPTNO NUMBER(5)
);
Explanation
PRIMARY KEY → SID must be unique and not null
NOT NULL → SNAME cannot be empty
CHECK → MARKS must be between 0 and 100
Q2. Add UNIQUE column, insert records and display
Alter table
ALTER TABLE STUDENT
ADD EMAIL VARCHAR2(50) UNIQUE;
Insert records
INSERT INTO STUDENT VALUES (101,'Ravi',85,10,'ravi@[Link]');
INSERT INTO STUDENT VALUES (102,'Raju',72,20,'raju@[Link]');
INSERT INTO STUDENT VALUES (103,'Ramu',90,10,'ramu@[Link]');
INSERT INTO STUDENT VALUES (104,'Sita',65,30,'sita@[Link]');
INSERT INTO STUDENT VALUES (105,'Anu',78,20,'anu@[Link]');
Display table
SELECT * FROM STUDENT;
Q3. Students scoring more than 75 (descending order)
SELECT *
FROM STUDENT
WHERE MARKS > 75
ORDER BY MARKS DESC;
Q4. Drop a column and drop table
Drop column
ALTER TABLE STUDENT
DROP COLUMN EMAIL;
Drop table
DROP TABLE STUDENT;
Q5. Student who secured fourth rank
SELECT *
FROM (
SELECT SNAME, MARKS,
RANK() OVER (ORDER BY MARKS DESC) AS RANK_NO
FROM STUDENT
)
WHERE RANK_NO = 4;
Q6. Queries using IN and ANY
IN operator
SELECT *
FROM STUDENT
WHERE DEPTNO IN (10,20);
ANY operator
SELECT *
FROM STUDENT
WHERE MARKS > ANY
(SELECT MARKS FROM STUDENT WHERE DEPTNO = 20);
Q7. Queries using ALL and EXISTS
ALL operator
SELECT *
FROM STUDENT
WHERE MARKS > ALL
(SELECT MARKS FROM STUDENT WHERE DEPTNO = 20);
EXISTS operator
SELECT SNAME
FROM STUDENT S
WHERE EXISTS
(SELECT *
FROM STUDENT
WHERE MARKS > 80 AND [Link] = SID);
Q8. Query using NOT EXISTS
SELECT SNAME
FROM STUDENT S
WHERE NOT EXISTS
(SELECT *
FROM STUDENT
WHERE MARKS < 40 AND [Link] = SID);
Q9. UNION and INTERSECT
UNION
SELECT SNAME FROM STUDENT WHERE DEPTNO = 10
UNION
SELECT SNAME FROM STUDENT WHERE DEPTNO = 20;
INTERSECT
SELECT SNAME FROM STUDENT WHERE MARKS > 70
INTERSECT
SELECT SNAME FROM STUDENT WHERE DEPTNO = 10;
Q10. Employee queries
Create employee table (example)
CREATE TABLE EMPLOYEE(
EMPID NUMBER,
ENAME VARCHAR2(30),
SALARY NUMBER,
DEPTNO NUMBER
);
a) Salary > 30000 AND department 10
SELECT *
FROM EMPLOYEE
WHERE SALARY > 30000
AND DEPTNO = 10;
b) Department 10 OR 20
SELECT *
FROM EMPLOYEE
WHERE DEPTNO = 10
OR DEPTNO = 20;
c) Not working in department 30
SELECT *
FROM EMPLOYEE
WHERE DEPTNO <> 30;
Q11. Aggregate functions
COUNT
SELECT COUNT(*) FROM STUDENT;
SUM
SELECT SUM(MARKS) FROM STUDENT;
AVG
SELECT AVG(MARKS) FROM STUDENT;
MAX
SELECT MAX(MARKS) FROM STUDENT;
MIN
SELECT MIN(MARKS) FROM STUDENT;
Q12. GROUP BY and HAVING
GROUP BY
SELECT DEPTNO, AVG(MARKS)
FROM STUDENT
GROUP BY DEPTNO;
HAVING
SELECT DEPTNO, AVG(MARKS)
FROM STUDENT
GROUP BY DEPTNO
HAVING AVG(MARKS) > 75;
Q13. Create a VIEW for students scoring more than 80
CREATE VIEW TOP_STUDENTS AS
SELECT *
FROM STUDENT
WHERE MARKS > 80;
Display view
SELECT * FROM TOP_STUDENTS;
Q14. Drop the VIEW
DROP VIEW TOP_STUDENTS;
Q15. Conversion functions
TO_CHAR
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')
FROM DUAL;
TO_NUMBER
SELECT TO_NUMBER('123')
FROM DUAL;
TO_DATE
SELECT TO_DATE('05-MAR-2026','DD-MON-YYYY')
FROM DUAL;
Q16. String functions
Concatenation
SELECT SNAME || ' - Student'
FROM STUDENT;
LPAD
SELECT LPAD(SNAME,10,'*')
FROM STUDENT;
RPAD
SELECT RPAD(SNAME,10,'*')
FROM STUDENT;
LOWER
SELECT LOWER(SNAME)
FROM STUDENT;
UPPER
SELECT UPPER(SNAME)
FROM STUDENT;
Q17. String functions
INITCAP
SELECT INITCAP(SNAME)
FROM STUDENT;
LENGTH
SELECT LENGTH(SNAME)
FROM STUDENT;
SUBSTR
SELECT SUBSTR(SNAME,1,3)
FROM STUDENT;
INSTR
SELECT INSTR('DATABASE','BASE')
FROM DUAL;
LTRIM
SELECT LTRIM(' ORACLE')
FROM DUAL;
RTRIM
SELECT RTRIM('ORACLE ')
FROM DUAL;
Q18. Current date and time
SYSDATE
SELECT SYSDATE
FROM DUAL;
Format date
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
FROM DUAL;
Q19. Date functions
ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE,3)
FROM DUAL;
NEXT_DAY
SELECT NEXT_DAY(SYSDATE,'MONDAY')
FROM DUAL;
LAST_DAY
SELECT LAST_DAY(SYSDATE)
FROM DUAL;
Q20. Date functions
MONTHS_BETWEEN
SELECT MONTHS_BETWEEN
(TO_DATE('01-JAN-2026','DD-MON-YYYY'),
TO_DATE('01-JAN-2025','DD-MON-YYYY'))
FROM DUAL;
LEAST
SELECT LEAST(10,20,5,8)
FROM DUAL;
SQL
GREATEST
SELECT GREATEST(10,20,5,8)
FROM DUAL;
TRUNC
SELECT TRUNC(SYSDATE)
FROM DUAL;
ROUND
SELECT ROUND(SYSDATE)
FROM DUAL;
✅ If you want, I can also convert this into a clean lab record format (Step-1, Step-2,
Output format) or PDF for exam writing.
Ok
Your PDF is ready.
Download the PDF
It contains all Q1–Q20 SQL queries neatly formatted so you can use it for:
📘 Lab record
📝 Exam preparation
💻 Oracle SQL practice
If you want, I can also make a second PDF with outputs/screenshots format (Experiment
→ Query → Output) which many colleges require for DBMS lab records.
Ok
Great 👍
You can download your PDF here:
📄 Download SQL Lab Queries Answers
This PDF contains all answers from Q1 to Q20 neatly arranged so you can directly use
them for:
📘 DBMS Lab Record
📝 Exam preparation
💻 Oracle SQL practice
✅ If you want, I can also prepare:
Lab Record Format (Aim → Query → Output → Result)
Short 2-mark answers for these SQL topics
Important Viva Questions for DBMS Lab (very useful in practical exams) 🚀
Create a STUDENT table with appropriate constraints
(Primary Key, NOT NULL, CHECK).
Q2. Alter the table to add a UNIQUE column and insert 5
records, then display using SELECT.
Q3. Write a query to display students who scored more
than 75 marks and order them in
descending order.
Q4. Drop a column from the table and then drop the entire
table.
Q5. Write a query to find the student who secured fourth
rank in the class.
Q6. Write queries using IN and ANY operators with
subqueries.
Q7. Write queries using ALL and EXISTS operators.
Q8. Write a query using NOT EXISTS with suitable
example.
Q9. Write queries using UNION and INTERSECT operators.
Q10. Write SQL queries to display the details of
employees:
a) Whose salary is greater than 30000 AND department
number is 10.
b) Who belong to department 10 OR department 20.
c) Who are NOT working in department 30.
Q11. Write queries using aggregate functions (COUNT,
SUM, AVG, MAX, MIN).
[Link] queries using GROUP BY and HAVING clause.
Q13. Create a VIEW for students scoring more than 80
marks.
Q14. Drop the created VIEW.
Q15. Write queries using conversion functions (TO_CHAR,
TO_NUMBER, TO_DATE).
Q16. Write queries using string functions (Concatenation,
LPAD, RPAD, LOWER, UPPER).
Q17. Write queries using string functions (INITCAP,
LENGTH, SUBSTR, INSTR, LTRIM, RTRIM).
Q18. Write queries to display current date and time using
SYSDATE and TO_CHAR.
Q19. Write queries using date functions (ADD_MONTHS,
NEXT_DAY, LAST_DAY).
Q20. Write queries using date functions
(MONTHS_BETWEEN, LEAST, GREATEST, TRUNC,
ROUND).
Give me answers with neatly and their outputs also
I’ll show queries + example output tables so you can write them neatly in your DBMS lab
record.
Q1. Create a STUDENT table with constraints
CREATE TABLE STUDENT (
SID NUMBER(5) PRIMARY KEY,
SNAME VARCHAR2(30) NOT NULL,
MARKS NUMBER(3) CHECK (MARKS BETWEEN 0 AND 100),
DEPTNO NUMBER(5)
);
Output
Table created.
Q2. Add UNIQUE column and insert 5 records
Alter Table
ALTER TABLE STUDENT
ADD EMAIL VARCHAR2(40) UNIQUE;
Output
Table altered.
Insert Records
INSERT INTO STUDENT VALUES (101,'Ravi',85,10,'ravi@[Link]');
INSERT INTO STUDENT VALUES (102,'Raju',72,20,'raju@[Link]');
INSERT INTO STUDENT VALUES (103,'Ramu',90,10,'ramu@[Link]');
INSERT INTO STUDENT VALUES (104,'Sita',65,30,'sita@[Link]');
INSERT INTO STUDENT VALUES (105,'Anu',78,20,'anu@[Link]');
Output
5 rows inserted.
Display Table
SELECT * FROM STUDENT;
Output
SID SNAME MARKS DEPTNO EMAIL
101 Ravi 85 10 ravi@[Link]
102 Raju 72 20 raju@[Link]
103 Ramu 90 10 ramu@[Link]
104 Sita 65 30 sita@[Link]
105 Anu 78 20 anu@[Link]
Q3. Students scoring more than 75 (Descending)
SELECT *
FROM STUDENT
WHERE MARKS > 75
ORDER BY MARKS DESC;
Output
SID SNAME MARKS
103 Ramu 90
101 Ravi 85
105 Anu 78
Q4. Drop column and table
ALTER TABLE STUDENT
DROP COLUMN EMAIL;
Output
Table altered.
DROP TABLE STUDENT;
Output
Table dropped.
Q5. Student who secured fourth rank
SELECT *
FROM (
SELECT SNAME, MARKS,
RANK() OVER (ORDER BY MARKS DESC) RANK_NO
FROM STUDENT
)
WHERE RANK_NO = 4;
Output
SNAME MARKS
Sita 65
Q6. IN and ANY operators
IN
SELECT * FROM STUDENT
WHERE DEPTNO IN (10,20);
Output
SID SNAME MARKS DEPTNO
101 Ravi 85 10
102 Raju 72 20
103 Ramu 90 10
105 Anu 78 20
ANY
SELECT * FROM STUDENT
WHERE MARKS > ANY
(SELECT MARKS FROM STUDENT WHERE DEPTNO=20);
Q7. ALL and EXISTS
ALL
SELECT * FROM STUDENT
WHERE MARKS > ALL
(SELECT MARKS FROM STUDENT WHERE DEPTNO=20);
EXISTS
SELECT SNAME
FROM STUDENT S
WHERE EXISTS
(SELECT * FROM STUDENT WHERE MARKS>80 AND [Link]=SID);
Q8. NOT EXISTS
SELECT SNAME
FROM STUDENT S
WHERE NOT EXISTS
(
SELECT *
FROM STUDENT
WHERE MARKS < 40
AND [Link] = SID
);
Output
All students will be displayed if no one scored below 40.
Q9. UNION and INTERSECT
UNION
SELECT SNAME FROM STUDENT WHERE DEPTNO=10
UNION
SELECT SNAME FROM STUDENT WHERE DEPTNO=20;
Output
Ravi
Raju
Ramu
Anu
INTERSECT
SELECT SNAME FROM STUDENT WHERE MARKS>70
INTERSECT
SELECT SNAME FROM STUDENT WHERE DEPTNO=10;
Output
Ravi
Ramu
Q10. Employee queries
Table
CREATE TABLE EMPLOYEE(
EMPID NUMBER,
ENAME VARCHAR2(20),
SALARY NUMBER,
DEPTNO NUMBER
);
Example data
EMPID ENAME SALARY DEPTNO
1 Kiran 35000 10
2 Ramesh 25000 20
3 Anil 40000 10
4 Suresh 28000 30
a) Salary >30000 AND dept 10
SELECT * FROM EMPLOYEE
WHERE SALARY>30000 AND DEPTNO=10;
SQL
Output
EMPID ENAME SALARY DEPTNO
1 Kiran 35000 10
3 Anil 40000 10
b) Dept 10 OR 20
SELECT * FROM EMPLOYEE
WHERE DEPTNO=10 OR DEPTNO=20;
c) NOT dept 30
SELECT * FROM EMPLOYEE
WHERE DEPTNO<>30;
Q11. Aggregate functions
SELECT COUNT(*) FROM STUDENT;
SELECT SUM(MARKS) FROM STUDENT;
SELECT AVG(MARKS) FROM STUDENT;
SELECT MAX(MARKS) FROM STUDENT;
SELECT MIN(MARKS) FROM STUDENT;
Example Output
COUNT = 5
SUM = 390
AVG = 78
MAX = 90
MIN = 65
Q12. GROUP BY and HAVING
SELECT DEPTNO, AVG(MARKS)
FROM STUDENT
GROUP BY DEPTNO;
Output
DEPTNO AVG(MARKS)
10 87.5
20 75
30 65
SELECT DEPTNO, AVG(MARKS)
FROM STUDENT
GROUP BY DEPTNO
HAVING AVG(MARKS)>75;
Q13. Create VIEW
CREATE VIEW TOP_STUDENTS AS
SELECT * FROM STUDENT
WHERE MARKS>80;
Output
View created.
Q14. Drop VIEW
DROP VIEW TOP_STUDENTS;
Output
View dropped.
Q15. Conversion functions
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY') FROM DUAL;
SELECT TO_NUMBER('123') FROM DUAL;
SELECT TO_DATE('05-MAR-2026','DD-MON-YYYY') FROM DUAL;
Output example
05-MAR-2026
123
05-MAR-2026
Q16. String functions
SELECT SNAME||' Student' FROM STUDENT;
SELECT LPAD(SNAME,10,'*') FROM STUDENT;
SELECT RPAD(SNAME,10,'*') FROM STUDENT;
SELECT LOWER(SNAME) FROM STUDENT;
SELECT UPPER(SNAME) FROM STUDENT;
Q17. String functions
SELECT INITCAP(SNAME) FROM STUDENT;
SELECT LENGTH(SNAME) FROM STUDENT;
SELECT SUBSTR(SNAME,1,3) FROM STUDENT;
SELECT INSTR('DATABASE','BASE') FROM DUAL;
SELECT LTRIM(' ORACLE') FROM DUAL;
SELECT RTRIM('ORACLE ') FROM DUAL;
Q18. Current date and time
SELECT SYSDATE FROM DUAL;
Output
05-MAR-2026 13:20:15
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
FROM DUAL;
Q19. Date functions
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'MONDAY') FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
Q20. Date functions
SELECT MONTHS_BETWEEN
(TO_DATE('01-JAN-2026','DD-MON-YYYY'),
TO_DATE('01-JAN-2025','DD-MON-YYYY'))
FROM DUAL;
Output: 12
SQL
SELECT LEAST(10,20,5,8) FROM DUAL;
SELECT GREATEST(10,20,5,8) FROM DUAL;
SELECT TRUNC(SYSDATE) FROM DUAL;
SELECT ROUND(SYSDATE) FROM DUAL;
✅ If you want, I can also give you perfect DBMS Lab Record format (Aim → Query →
Output → Result) for all 20 questions, exactly how teachers expect in practical exams.