Scribd
Upload
12 views6 pages

SQL Queries for Date and Train Management

The document is a lab assignment for a DBMS course that includes SQL queries for various date and time operations using the SYSDATE function and operations on an EMP table and a Train table. It covers displaying system date, current day, and specific employee joining dates, as well as creating a Train table and inserting records into it. Additionally, it includes queries to filter train departures and arrivals based on time conditions.

Uploaded by

aaryaaggarwal17
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
12 views6 pages

SQL Queries for Date and Train Management

The document is a lab assignment for a DBMS course that includes SQL queries for various date and time operations using the SYSDATE function and operations on an EMP table and a Train table. It covers displaying system date, current day, and specific employee joining dates, as well as creating a Train table and inserting records into it. Additionally, it includes queries to filter train departures and arrivals based on time conditions.

Uploaded by

aaryaaggarwal17
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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
[Link] 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

Common questions

Powered by AI

First, create the table using: CREATE TABLE Train (Train_Number INTEGER PRIMARY KEY, Date_Of_Departure DATE, Time_Of_Departure TIMESTAMP, Time_Of_Arrival TIMESTAMP). Then, format timestamps using TO_TIMESTAMP function. For example, to insert a record: 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')).

You can identify trains arriving in the PM by using the TO_CHAR function on the Time_Of_Arrival with the 'AM' check. The query is: SELECT * FROM Train WHERE TO_CHAR(Time_Of_Arrival, 'AM') = 'PM';

To display employees who joined on a Monday, you would use the TO_CHAR function on the Date_Of_Joining to match 'Monday'. The query is: SELECT * FROM Employee WHERE TO_CHAR(Date_Of_Joining, 'Day') = 'Monday';

To find the next occurrence of a specific weekday, such as Friday, you use the NEXT_DAY function. The query is: SELECT NEXT_DAY(SYSDATE, 'FRIDAY') AS Next_Friday FROM DUAL;

To round the current system date to the nearest year, you can use the ROUND function with the 'YEAR' argument. The query is: SELECT ROUND(SYSDATE, 'YEAR') AS Rounded_Year FROM DUAL;

To identify a train departing within the next hour, use the BETWEEN clause with SYSTIMESTAMP and an interval. The query is: SELECT Train_Number FROM Train WHERE Time_Of_Departure BETWEEN SYSTIMESTAMP AND SYSTIMESTAMP + INTERVAL '1' HOUR;

To display the spelled-out current date in SQL using Oracle Database, you can use the TO_CHAR function with the appropriate format. The query is: SELECT TO_CHAR(SYSDATE, 'DDth Month, YYYY') AS Spell_Out_Date FROM DUAL;

To list employees who joined in the last 30 days, compare Date_Of_Joining with SYSDATE minus 30 days. The query is: SELECT * FROM Employee WHERE Date_Of_Joining >= SYSDATE - 30;

Use the TO_CHAR function to extract the AM/PM indicator from SYSDATE. The query is: SELECT TO_CHAR(SYSDATE, 'AM') AS AM_PM FROM DUAL;

To truncate the current system date to the beginning of the month, you can use the TRUNC function. The query is: SELECT TRUNC(SYSDATE, 'MONTH') AS Truncated_Date FROM DUAL;

You might also like