Download AITU dbms chapter six and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
Chapter 5
SQL Development. DML Statements.
Data Manipulation Language (DML) โ is used to update and access data.
- SELECT โ Access data from database.
- INSERT INTO โ Insert a new row of data.
- UPDATE โ Change an existing row of data.
- DELETE โ Delete a row of data.
DML INSERT is utilized to insert row(s) into a table.
The basic syntax of INSERT INTO statement:
1 INSERT INTO <TABLE_NAME> (<column1_name>, <column2_name>, <column3_name>, <columnN_name>) 2 VALUES (, , ,);
Example 5 .1 Table "university"
INSERT INTO university (uni_id, name , address, year_created) VALUES ( 1001 , 'AITU', 'st.Mangilik 55, Nur-Sultan', '01-MAY-2019'); INSERT INTO university (uni_id, name , address, year_created) VALUES ( 1002 , 'IITU', '34A st.Zhandosova, Almaty', '01-APR-2009'); INSERT INTO university (uni_id, name , address, year_created) VALUES ( 1003 , 'SDU', 'Kaskelen', '01-MAY-1996'); SELECT * FROM university;
Statement above would create the following records in table "university".
Example 5 .2. The following example inserts multiple rows into existing table.
INSERT INTO university (uni_id, name , address, year_created) VALUES ( 1004 , 'ENU', 'Nur-Sultan', '01-MAY-1990'), ( 1005 , 'UIB', 'Almaty', '01-SEPT-1997'; SELECT * FROM university;
DML UPDATE โ is utilized to modify the present records in a table. The UPDATE statement can be used
with WHERE clause to modify selected row(s); otherwise, all records will be updated.
The basic syntax of UPDATE statement with WHERE clause:
UPDATE <table_name> SET <column1_name> = , <column2_name>=, <columnN_name>= WHERE [condition];
Example 5 .3 The following is an example which would update ACCR_STATUS for UNI_ID โ1002โ.
UPDATE university SET accr_status = 'NO' WHERE uni_id = 1002; SELECT * FROM university ORDER BY uni_id;
DML DELETE - is utilized to delete one or multiple records from existing table.
The basic syntax of DELETE statement with WHERE clause:
DELETE from <table_name> WHERE [conditions];
Example 5 .6 The following example would delete the record where UNI_ID is equal to 1005.
DELETE from university WHERE uni_id = 1005; SELECT * from university ORDER by uni_id;
Example 5 .7 The following is example of deleting all records. You do not have to use WHERE clause.
DELETE from university; SELECT * from university;
Practice works SQL Development. DML Statements. Practice task 1 In this section, you will apply what you have learned from previous chapters without being given any commands. a. Create the "shipping_service" and "package" tables, including all of columns, data types, and constraints. Make sure to create the foreign key constraint. b. Insert the following companies into "shipping_service" adding ship_id of your choice.
- Kazpost
- DHL
- FedEx
- PonyExpress c. Insert the following package details into "package" table adding package_id of your choice.
- delivered by Kazpost description: IT devices date_delivered: 25 - May- 2019
- delivered by DHL description: fragile box date_delivered: 15-June- 2019
- delivered by FedEx description: sport equipment date_delivered: 20-APR- 2018
i. Write a single query to remove the employees who are not working anymore. SELECT queries with WHERE clause
SELECT statement is used to list the content of a table from database.
a. The basic syntax of SELECT statement is:
1 SELECT <column1_name>, <column2_name>, <columnN_name> FROM <table_name>;
b. The following syntax is to retrieve all available fields in the table.
Example 5. 8 The following example is to list id, name, marital_status fields in emp table.
1 SELECT employee_id, name , marital_status FROM emp; 1 SELECT * FROM <table_name>;
Comparison Operators
SYMBOL MEANING
= equal to < less than <= less than or equal to
greater than = greater than or equal to <>/ != not equal to
c. The following basic syntax of SELECT statement with WHERE clause.
SELECT <column1_name>, <column2_name>, <columnN_name> FROM <table_name>
