JOURNAL:2
Create following table with appropriate constraints.
Employee(Ename,City)
Emp_Company(Ename,Cname,Salary,Jdate)
Company(Cname,City)
Manager(Ename,Mname)
Emp_Shift(Ename,Shift)
CREATE ALL TABLE AND INSERT RECORD.
-->CREATE TABLE EMPLOYEE
CREATE TABLE EMPLOYEE
(
ENAME VARCHAR(20),
CITY VARCHAR(20)
);
INSERT INTO EMPLOYEE
VALUES(‘AMAN’,’NAVSARI’);
INSERT INTO EMPLOYEE
VALUES(‘SHIVANI’,’CULCUTTA’);
INSERT INTO EMPLOYEE
VALUES(‘MITTAL’,’MANDVI’);
INSERT INTO EMPLOYEE
VALUES(‘PRIYAL’,’SURAT’);
INSERT INTO EMPLOYEE
VALUES(‘VATSAL’,’DELHI’);
INSERT INTO EMPLOYEE
VALUES(‘AAROHI’,’BHARUCH’);
INSERT INTO EMPLOYEE
VALUES(‘JAY’,’SELVAS’);
-->CREATE TABLE EMP_COMPANY
CREATE TABLE EMP_COMPANY
(
ENAME VARCHAR(20),
CNAME VARCHAR(20),
SALARY NUMBER(7),
JDATE DATE
);
INSERT INTO EMP_COMPANY
VALUES(‘AMAN’,’ACC’,1500,’1989-05-02’);
INSERT INTO EMP_COMPANY
VALUES(‘SHIVANI’,’TATA’,2000,’1990-07-11’);
INSERT INTO EMP_COMPANY
VALUES(‘MITTAL’,’CMC’,1700,’1998-01-02’);
INSERT INTO EMP_COMPANY
VALUES(‘PRIYAL’,’CMC’,1700,’1991-06-07’);
INSERT INTO EMP_COMPANY
VALUES(‘VATSAL’,’TATA’,5000,’1988-01-04’);
INSERT INTO EMP_COMPANY
VALUES(‘AAROHI’,’ACC’,3000,’1989-05-25’);
INSERT INTO EMP_COMPANY
VALUES(‘JAY’,’TATA’,8000,’1995-04-29’);
-->CREATE TABLE COMPANY
CREATE TABLE COMPANY
(
ENAME VARCHAR(20),
CITY VARCHAR(20)
);
INSERT INTO COMPANY
VALUES(‘ACC’,’MANDVI’);
INSERT INTO COMPANY
VALUES(‘TATA’,’BHARUCH’);
INSERT INTO COMPANY
VALUES(‘ACC’,’NAVSARI’);
INSERT INTO COMPANY
VALUES(‘CMC’,’BHARUCH’);
INSERT INTO COMPANY
VALUES(‘TATA’,’DELHI’);
-->CREATE TABLE EMP_SHIFT
CREATE TABLE EMP_SHIFT
(
ENAME VARCHAR(20),
SHIFT VARCHAR(20)
);
INSERT INTO EMP_SHIFT
VALUES(‘AROHI’,’A’);
INSERT INTO EMP_SHIFT
VALUES(‘SHIVANI’,’B’);
INSERT INTO EMP_SHIFT
VALUES(‘PRIYAL’,’C’);
INSERT INTO EMP_SHIFT
VALUES(‘JAY’,’B’);
INSERT INTO EMP_SHIFT
VALUES(‘MITTAL’,’C’);
-->CREATE TABLE MANAGER
CREATE TABLE MANAGER
(
ENAME VARCHAR(20),
MNAME VARCHAR(20)
);
INSERT INTO MANAGER
VALUES(‘AROHI’,’JAY’);
INSERT INTO MANAGER
VALUES(‘SHIVANI’,’PRIYAL’);
INSERT INTO MANAGER
VALUES(‘JAY’,’-’);
INSERT INTO MANAGER
VALUES(‘AMAN’,’VATSAL’);
INSERT INTO MANAGER
VALUES(‘JAY’,’-’);
INSERT INTO MANAGER
VALUES(‘AROHI’,’SHIVANI’);
INSERT INTO MANAGER
VALUES(‘AMAN’,’-’);
QUERIES:
1.Display all record of Employee.
2.Retrieve employee name only from employee table
3.Retrieve employee whose comes from MANDVI.
4.Change city with BHARUCH whose comes from MUMBAI.
5.Find employee name whose name’s third letter is ‘A’
6.Find employee name whose name’s contain TS latter and comes from DELHI.
7.Retrieve employee name which is having NI letter
8.Modify name AMAN whose comes from NASARI
9.Display city name only in which city names are not duplicated
10Find employee name and date whose cname is ACC from employee company table.
11.Display employee name which is in descending order
12.Display salary greater than 2000 from emp_company table
13.Display joining date and salary whose salary between 1500 to 3000 from employee
company
14.Display all record whose salary not between 2000 to 3000
15.Describe employee table
16.Remove records whose are coming from CULCATTA
17.Display employee name whose comes from SURAT and SELVAS.
18.Display employee name whose comes from SURAT or SELVAS
19.Change table name with emp instead of Employee
20.Display record between 2-JAN-88 to 13-DEC-91
21.Retrieve data whose joining date is 4-JAN-88
22.Display and add 200 rupees in all salary
23.Add 20% bonus whose customer name is TATA.
24.Display cname with alias name like Customer Name.
25.Display Jdate with alias name like Joing_Date or JOING DATE.
26.Change salary with 2300 rupees whose name is SHIVANI.
27.Change with 12-MAY-90 Joining date whose cname is CMC and employee name PRIYAL.
28.Display employee name, salary and joining date whose salary is greater than 3000
rs and joining date is 2-MAY-89.
29.Create empcmpy table from emp_company with all columname.
30.Display all record whose joining date is greater than 14-DEC-91.
31.Display salary with 5% penalty of AMOL.
32.Remove jdate column from emp_company table.
33.Giving less size of salary column from existing size.
34.Display information whose cname is TATA or CMC
35.Display all information of company table
36.Remove Company table with record
37.Delete all record of Company table
38.Retrieve of all rows of manager
39.Display manager name only from manager table
40.Display JAY’s employee name from manager table
41.Add empid as new column in manager table
42.Remove emplD column from manager table
43.Display all record of employee shift table
44.Do not display duplicate value of shift column
45.Change employee name with PRATIK whose shift is B
46.Copying shift data into another table
47.Remove data whose shift is C from employee shift
48.Insert one record as system date in joining date column employee company table
49.Remove shift column from employee shift table
50.Remove employee shift table
