SQL statement usage of OracleDELETE Delete record

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.