Lab Assignment–4
DBMS UCS 310
Write queries to:
1. Display the system date
SELECT SYSDATE FROM DUAL;
2. Display current day
SELECT TO_CHAR(SYSDATE, 'Day') AS Current_Day FROM DUAL;
3. Display current month and spell out year
SELECT TO_CHAR(SYSDATE, 'Month YYYY') AS Month_Year FROM DUAL;
4. Display spell out current date
SELECT TO_CHAR(SYSDATE, 'DDth Month, YYYY') AS Spell_Out_Date FROM
DUAL;
5. Check whether it is AM or PM right now
SELECT TO_CHAR(SYSDATE, 'AM') AS AM_PM FROM DUAL;
6. Display the date of next Friday
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') AS Next_Friday FROM DUAL;
7. Round the system date on month
SELECT ROUND(SYSDATE, 'MONTH') AS Rounded_Date FROM DUAL;
8. Truncate the system date on month
SELECT TRUNC(SYSDATE, 'MONTH') AS Truncated_Date FROM DUAL;
9. Round the system date on year
SELECT ROUND(SYSDATE, 'YEAR') AS Rounded_Year FROM DUAL;
10. Truncate the system date on year
SELECT TRUNC(SYSDATE, 'YEAR') AS Truncated_Year FROM DUAL;
11. Find the day a er three days
SELECT TO_CHAR(SYSDATE + 3, 'Day') AS Day_A er_Three_Days FROM
DUAL;
Queries Based on EMP table
12. Display day of date of joining column
SELECT ECode, Name, TO_CHAR(Date_Of_Joining, 'Day') AS
Joining_Day FROM Employee;
13. Display those employees who join the company on Monday
SELECT * FROM Employee WHERE TO_CHAR(Date_Of_Joining,
'Day') = 'Monday';
ft
ft
14.Display those employees who join the company this month
SELECT * FROM Employee WHERE TO_CHAR(Date_Of_Joining,
'MM') = TO_CHAR(SYSDATE, 'MM');
15. Display those employees who join the company in last 30 days
SELECT * FROM Employee WHERE Date_Of_Joining >= SYSDATE -
30;
Create a table train having three four columns
16. Train Number, date of Departure, me of departure, me of arrival
CREATE TABLE Train ( Train_Number INTEGER PRIMARY KEY,
Date_Of_Departure DATE, Time_Of_Departure TIMESTAMP,
Time_Of_Arrival TIMESTAMP );
17. Insert ve columns in train table
INSERT INTO Train VALUES (101, TO_DATE('2024-10-16', 'YYYY-MM-DD'),
TO_TIMESTAMP('2024-10-16 10:30:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP('2024-10-16 16:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO Train VALUES (102, TO_DATE('2024-10-17', 'YYYY-MM-DD'),
TO_TIMESTAMP('2024-10-17 09:15:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP('2024-10-17 13:30:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO Train VALUES (103, TO_DATE('2024-10-18', 'YYYY-MM-DD'),
TO_TIMESTAMP('2024-10-18 12:00:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP('2024-10-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));
fi
ti
ti
INSERT INTO Train VALUES (104, TO_DATE('2024-10-18', 'YYYY-MM-DD'),
TO_TIMESTAMP('2024-10-18 18:00:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP('2024-10-18 22:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO Train VALUES (105, TO_DATE('2024-10-19', 'YYYY-MM-DD'),
TO_TIMESTAMP('2024-10-19 05:00:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP('2024-10-19 09:45:00', 'YYYY-MM-DD HH24:MI:SS'));
18. Display all the records
SELECT * FROM Train;
19. Display the me values inserted in the columns
SELECT Train_Number, Time_Of_Departure, Time_Of_Arrival
FROM Train;
20. Display those trains which arrived on PM
SELECT * FROM Train
WHERE TO_CHAR(Time_Of_Arrival, 'AM') = 'PM';
21. Display train number who are going to depart in next on hour.
SELECT Train_Number FROM Train
WHERE Time_Of_Departure BETWEEN SYSTIMESTAMP AND
SYSTIMESTAMP + INTERVAL '1' HOUR;
ti
