DBMS Practical List - 8
Date and Time
Platform: Oracle Express
1. Logon to Oracle using your User Name
2. See list of tables using “Tab”
3. Write a query to create an “Employee” table with following constraints:
EmpCode
EmpName
EmpCity
DOB (date of birth)
DOJ (date of joining)
Salary
4. Display the structure of the table
5. Insert the following data:
EMPCODE EMPNAME EMPCITY DOB DOJ Salary
1 Yogesh Delhi 1/11/75 1/1/05 12000
2 Dinesh Mumbai 22/5/78 Current 6000
Date
3 Sheena Pune 16/6/89 1/6/99 50000
5 Sundar Bangalore 15/9/90 1/1/02 65000
6. Execute the following queries:
1) Select empname,DOB from employee
2) Select empname, DOB, sysdate from employee.
3) Select empname, DOB, DOB + 15 from employee
4) Select empname, DOB,DOB-3 from employee
5) Select empname, DOB, TO_CHAR(dob,’DD MM YYYY’) from employee
6) Select empname, DOB, TO_CHAR(dob,’Day Month Year’) from employee
7) Select empname, DOB, TO_CHAR(dob,’Day MON Year DD MM YY’) from
employee
8) Select empname, DOB, TO_CHAR(dob,’DD DD MM MM YY YYYY YYYY’)from
employee
9) Select empname, DOB, TO_CHAR(dob,’DDsp MMsp YYYYsp’) from employee
10) Select empname, DOB, TO_CHAR(dob,’DDspth MMspth YYYYspth’) from
employee
11) Select empname, DOB, TO_CHAR(dob,’DDsp MMspth YYYYsp’) from employee
12) Select empname, DOB, TO_CHAR(dob,’DD MM YYYY HH MI SS’) from
employee
13) Select empname, DOB, TO_CHAR(dob,’HH MI SS’) from employee
14) Insert into employee values(4,’Amit’,’Meerut’,’1-Jan-67’,’21-Sep-04’,15000)
15) Select empname, DOB, TO_CHAR(DOB,’DD MM YYYY’), TO_CHAR(DOJ,’DD
MM YYYY’) from employee where empcode=4
16) Insert a record with DOB=1674 and DOJ=1734
17) Display this record with full year in DOB and DOJ
18) 7. Write queries for the following:
19) Show all records
20) Show names and salary of all employees
21) Display highest salary
22) Display lowest salary
23) Show the total number of records
24) Display the empname, actual salary and salary with 15% increment
25) Display all records in format: “The Salary of Yogesh is 12000 and he belongs to
Delhi”
26) Display the total number of characters in all employee names
27) Display all employee names in Capital Letters
28) Display all cities in Small Letters
29) Display names in Initial capital letters
30) Display only current date
31) Display only current time
32) Display the current date and time
33) Display only current year
34) Display only current month
35) Display all records in following format: “The date of birth of Yogesh is 1 November
Nineteen Seventy Five”
36) Display the total number of months an employee is working
37) Display the name and age of each employee
38) Display total experience of all employees in years
39) Display the full 4 digit birth year of all employees
40) Enter a record with your detail in the table with your actual DOB
41) Display on which day you were born
42) Display all list of employees who were born after your dob
43) Delete your record
44) Again enter your record but this time enter your birth time also
45) Display names, DOB, Time of Birth, age of all records
