SQL statement usage of OracleDELETE Delete record

The syntax is the same as that of other SQL statements, as shown in the following: DELETEFROMCOURSESWHERE & nbsp; COURSE_DESIGNATERJava110: DELETEFROMCLASSCONVENINGSWHERE & nbsp; CLASSES_NUM_FK & nbsp ;&

The syntax is the same as that of other SQL statements.
DELETE FROM COURSES
WHERE COURSE_DESIGNATER = 'java110'

Complex operations by conditions

DELETE FROM CLASSCONVENINGS
WHERE CLASSES_NUM_FK> 4
AND CLASS_CONVENE_DATE = TO_DATE ('2017-02-15 00:00:00 ',
'Yyyy-MM-DD HH24: MI: ss ')
AND CLASS_LOCATION_FK = 'seattle-Training Room 1'

Next, let's look at a detailed instance.

SQL> -- create demo table
SQL> create table Employee (
2 ID VARCHAR2 (4 BYTE) not null,
3 First_Name VARCHAR2 (10 BYTE ),
4 Last_Name VARCHAR2 (10 BYTE ),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number (8, 2 ),
8 City VARCHAR2 (10 BYTE ),
9 Description VARCHAR2 (15 bytes)
10)
11/

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01 ', 'jason', 'martin ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960725, 'toronto ', 'programmer ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('02 ', 'alison', 'mathews ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19760321, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('03', 'James ', 'Smith', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19781212, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('04 ', 'cela', 'Rice', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19821024, 'vancouver ', 'manager ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('05 ', 'Robert', 'black', to_date ('000000', 'yyyymmdd'), to_date ('20140901', 'yyyymmdd'), 19840115, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('06', 'linda ', 'green', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19870730, 'New York ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('07 ', 'David', 'Larry', to_date ('123', 'yyyymmdd'), to_date ('123456', 'yyyymmdd'), 19901231, 'New York ', 'manager ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('08', 'James ', 'cat', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960917, 'vancouver ', 'tester ')
3/

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> * from Employee
2/

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-----------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-----------------------------------------------------------------------------
08 James Cat 17-SEP-96 15-apr-01232.78 Vancouver Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- delete command with conditions
SQL>
SQL> delete from Employee where Salary> 3000;

4 rows deleted.

SQL>
SQL> select * from Employee;

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-----------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
08 James Cat 17-SEP-96 15-apr-01232.78 Vancouver Tester

4 rows selected.

Deletes the specified content.

SQL> CREATE TABLE project (
2 pro_id NUMBER (4 ),
3 pro_name VARCHAR2 (40 ),
4 budget NUMBER (9, 2 ),
5 CONSTRAINT project_pk primary key (pro_id)
6 );

Table created.

SQL>
SQL>
SQL> INSERT INTO project (pro_id, pro_name, budget) VALUES (1001, 'A', 1912000 );

1 row created.

SQL> INSERT INTO project (pro_id, pro_name, budget) VALUES (1002, 'erp ', 9999999 );

1 row created.

SQL> insert into project (pro_id, pro_name, budget) VALUES (1003, 'SQL', 897000 );

1 row created.

SQL> INSERT INTO project (pro_id, pro_name, budget) VALUES (1004, 'crm ', 294000 );

1 row created.

SQL> INSERT INTO project (pro_id, pro_name, budget) VALUES (1005, 'vpn', 415000 );

1 row created.

SQL>
SQL>
SQL> SET ECHO ON
SQL> DELETE
2 FROM project
3 WHERE pro_id = 1006
4