Scribd
Upload
641 views9 pages

Database Exrcise1

The document contains SQL statements that are inserting rows into the Project table with different project names, numbers, locations, and department numbers. It also contains SELECT statements that are querying the employee table and returning 10 rows of employee data.

Uploaded by

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

Database Exrcise1

The document contains SQL statements that are inserting rows into the Project table with different project names, numbers, locations, and department numbers. It also contains SELECT statements that are querying the employee table and returning 10 rows of employee data.

Uploaded by

Prateek Gupta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 9

new 1: insert into Project values('ProjectB',1945,'Salt Lake City',3)

1 row created.

SQL> /
Enter value for project_name: ProjectC
Enter value for project_number: 6688
Enter value for project_location: Houston
Enter value for department_number: 5
old 1: insert into Project
values('&Project_Name',&Project_Number,'&Project_Location',&Department_Number)
new 1: insert into Project values('ProjectC',6688,'Houston',5)

1 row created.

SQL> /
Enter value for project_name: ProjectD
Enter value for project_number: 2423
Enter value for project_location: Bellaire
Enter value for department_number: 4
old 1: insert into Project
values('&Project_Name',&Project_Number,'&Project_Location',&Department_Number)
new 1: insert into Project values('ProjectD',2423,'Bellaire',4)

1 row created.

SQL> /
Enter value for project_name: ProjectE
Enter value for project_number: 7745
Enter value for project_location: SugarLand
Enter value for department_number: 5
old 1: insert into Project
values('&Project_Name',&Project_Number,'&Project_Location',&Department_Number)
new 1: insert into Project values('ProjectE',7745,'SugarLand',5)

1 row created.

SQL> /
Enter value for project_name: ProjectF
Enter value for project_number: 15666
Enter value for project_location: Salt Lake City
Enter value for department_number: 3
old 1: insert into Project
values('&Project_Name',&Project_Number,'&Project_Location',&Department_Number)
new 1: insert into Project values('ProjectF',15666,'Salt Lake City',3)

1 row created.

SQL> /
Enter value for project_name: ProjectG
Enter value for project_number: 1234
Enter value for project_location: New York
Enter value for department_number: 2
old 1: insert into Project
values('&Project_Name',&Project_Number,'&Project_Location',&Department_Number)
new 1: insert into Project values('ProjectG',1234,'New York',2)

1 row created.
SQL> /
Enter value for project_name: ProjectH
Enter value for project_number: 3467
Enter value for project_location: Stafford
Enter value for department_number: 4
old 1: insert into Project
values('&Project_Name',&Project_Number,'&Project_Location',&Department_Number)
new 1: insert into Project values('ProjectH',3467,'Stafford',4)

1 row created.

SQL> /
Enter value for project_name: ProjectI
Enter value for project_number: 4345
Enter value for project_location: Chicago
Enter value for department_number: 1
old 1: insert into Project
values('&Project_Name',&Project_Number,'&Project_Location',&Department_Number)
new 1: insert into Project values('ProjectI',4345,'Chicago',1)

1 row created.

SQL> /
Enter value for project_name: ProjectJ
Enter value for project_number: 2212
Enter value for project_location: San francisco
Enter value for department_number: 2
old 1: insert into Project
values('&Project_Name',&Project_Number,'&Project_Location',&Department_Number)
new 1: insert into Project values('ProjectJ',2212,'San francisco',2)

1 row created.

SQL> select * from employee;

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------
Doug E Gilbert 554433221 09-JUN-60
11Sv59 E,Salt LAke City,UT M 80000 NULL
3

Joyce PAN 543216789 07-FEB-78


35 S 18 E,Salt Lake City,UT F 70000 NULL
2

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------

Franklin T Wong 333445555 08-DEC-45


638 Voss,Houston,TX M 40000 554433221
5
Jennifer S Wallace 987654321 20-JUN-31
291 Berry Bellaire, TX F 43000 554433221

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------
4

John B Smith 123456789 09-JAN-55


731 Fondren,Houston,TX M 30000 333445555
5

Ramesh K Narayan 666884444 15-SEP-52

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------
975 Fire Oak,Humble,TX M 38000 333445555
5

Joyce A English 453453453 31-JUL-62


5631 RIce,Houston,TX F 25000 333445555
5

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------
James E Borg 888665555 10-NOV-27
450 Stone Houston,TX M 55000 543216789
1

Alicia J Zelaya 999887777 19-JUL-58


3321 Castle Spring,TX F 25000 987654321
4

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------

Ahmad V Jabbar 987987987 29-MAR-59


980 Dallad, Houston,TX M 25000 987654321
4

10 rows selected.
SQL> select * from employee;

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------
Doug E Gilbert 554433221 09-JUN-60
11Sv59 E,Salt LAke City,UT M 80000 NULL
3

Joyce PAN 543216789 07-FEB-78


35 S 18 E,Salt Lake City,UT F 70000 NULL
2

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------

Franklin T Wong 333445555 08-DEC-45


638 Voss,Houston,TX M 40000 554433221
5

Jennifer S Wallace 987654321 20-JUN-31


291 Berry Bellaire, TX F 43000 554433221

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------
4

John B Smith 123456789 09-JAN-55


731 Fondren,Houston,TX M 30000 333445555
5

Ramesh K Narayan 666884444 15-SEP-52

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------
975 Fire Oak,Humble,TX M 38000 333445555
5

Joyce A English 453453453 31-JUL-62


5631 RIce,Houston,TX F 25000 333445555
5
FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY
--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------
James E Borg 888665555 10-NOV-27
450 Stone Houston,TX M 55000 543216789
1

Alicia J Zelaya 999887777 19-JUL-58


3321 Castle Spring,TX F 25000 987654321
4

FIRST_NAME MI LAST_NAME SSN_NUMBE BIRTHDAY


--------------- -- --------------- --------- ---------
ADDRESS S SALARY SUPERVISO
-------------------------------------------------- - ---------- ---------
DEPARTMENT_NUMBER
-----------------

Ahmad V Jabbar 987987987 29-MAR-59


980 Dallad, Houston,TX M 25000 987654321
4

10 rows selected.

SQL> select Employee_name,SSN,Supervisor_SSN form employee;


select Employee_name,SSN,Supervisor_SSN form employee
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select Employee_name SSN Supervisor_SSN form employee;


select Employee_name SSN Supervisor_SSN form employee
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> desc employee


Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_NAME VARCHAR2(15)
MID_NAME CHAR(2)
LAST_NAME VARCHAR2(15)
SSN_NUMBER CHAR(9)
BIRTHDAY DATE
ADDRESS VARCHAR2(50)
SEX CHAR(1)
SALARY NUMBER(7)
SUPERVISOR_SSN CHAR(9)
DEPARTMENT_NUMBER NUMBER(5)

SQL> select Firtst_Name,SSN,Supervisor_SSN form employee;


select Firtst_Name,SSN,Supervisor_SSN form employee
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select First_Name,SSN,Supervisor_SSN form employee;


select First_Name,SSN,Supervisor_SSN form employee
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select First_Name,SSN_Number,Supervisor_SSN form employee;


select First_Name,SSN_Number,Supervisor_SSN form employee
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select First_Name,SSN_Number,Supervisor_SSN from employee;

FIRST_NAME SSN_NUMBE SUPERVISO


--------------- --------- ---------
Doug 554433221 NULL
Joyce 543216789 NULL
Franklin 333445555 554433221
Jennifer 987654321 554433221
John 123456789 333445555
Ramesh 666884444 333445555
Joyce 453453453 333445555
James 888665555 543216789
Alicia 999887777 987654321
Ahmad 987987987 987654321

10 rows selected.

SQL> select First_Name from employee where Birthday='29-MAR-1959';

FIRST_NAME
---------------
Ahmad

SQL> select distinct salary from employee;

SALARY
----------
38000
43000
55000
30000
40000
80000
25000
70000

8 rows selected.

SQL> select Manager_SSN,Manager_Start_Date from Department where


department_name='Finance';
select Manager_SSN,Manager_Start_Date from Department where
department_name='Finance'
*
ERROR at line 1:
ORA-00904: "MANAGER_START_DATE": invalid identifier

SQL> select Manager_SSN,Manage_Start_Date from Department where


department_name='Finance';

MANAGER_S MANAGE_ST
--------- ---------
987654321 01-JAN-85

SQL> update employee set department_number=5 where first_Name=Joyce


2 ;
update employee set department_number=5 where first_Name=Joyce
*
ERROR at line 1:
ORA-00904: "JOYCE": invalid identifier

SQL> update employee set department_number=5 where first_Name='Joyce';

2 rows updated.

SQL> alter table department add Department_Phone number(10);

Table altered.

SQL> insert into department values(&Department_Phone);


Enter value for department_phone: 456789
old 1: insert into department values(&Department_Phone)
new 1: insert into department values(456789)
insert into department values(456789)
*
ERROR at line 1:
ORA-00947: not enough values

SQL> insert into department


(department_phone)values(123456,234567,3456789,46789,567891);
insert into department (department_phone)values(123456,234567,3456789,46789,567891)
*
ERROR at line 1:
ORA-00913: too many values

SQL> insert into department


(&department_phone)values(123456,234567,3456789,46789,567891);
Enter value for department_phone: 123456
old 1: insert into department
(&department_phone)values(123456,234567,3456789,46789,567891)
new 1: insert into department (123456)values(123456,234567,3456789,46789,567891)
insert into department (123456)values(123456,234567,3456789,46789,567891)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> update department set Department_Phone=123456 where Department_number=1;

1 row updated.

SQL> update department set Department_Phone=123457 where Department_number=2;

1 row updated.

SQL> update department set Department_Phone=123458 where Department_number=3;

1 row updated.

SQL> update department set Department_Phone=123459 where Department_number=4;

1 row updated.

SQL> update department set Department_Phone=123460 where Department_number=5;

1 row updated.

SQL> alter table department modify department_Phone number(12);

Table altered.

SQL> alter table department rename column department_number to PhNo;

Table altered.

SQL> rename department to Dept;

Table renamed.

SQL> table dept drop column PhNo;


SP2-0734: unknown command beginning "table dept..." - rest of line ignored.
SQL> alter table dept drop column PhNo;

Table altered.

SQL> create table copyOfDept as select * from Dept;

Table created.

SQL> truncate copyOfDept;


truncate copyOfDept
*
ERROR at line 1:
ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword

SQL> truncate table copyOfDept;

Table truncated.

SQL> drop copyOfdept;


drop copyOfdept
*
ERROR at line 1:
ORA-00950: invalid DROP option
SQL> drop table copyOfdept;

Table dropped.

SQL> commit;

Commit complete.

You might also like