Certain operations do no return any results, but rather modify the Database.
INSERT, UPDATE, DELETE
Insert
INSERT INTO R VALUES (a,b,c)
For example:
INSERT INTO sale VALUES (11, ‘10/9’, 100, 23, 2241, 30)
In the above case, we need to know the order, we need to get the order correct.
If we don’t want to insert an entire tuple, or if we want to change the order that we insert items:
insert into sale (sale_id, date, cust_id, salpers_id, prod_id, qty)
values (12, str_to_date('2021-10-01', '%Y-%m-%d'), 10, 11, 12, 100);
Where the order of the first parentheses denote the order data will arrive in the second parenthesis.
Insert a subquery
INSERT INTO R <subquery>
For example
INSERT INTO big_sale (select * from sale where qty > 200)
This might not actually work if we don’t have big_sale already existing and matching whatever attributes you will be giving it.
You can use SELECT INTO R to create a new table
SELECT * INTO new_table from (select * from sale where qty > 200)
(But MySQL doesn’t support this, although most DBMS’ do). It’s probably best to create the table with the appropriate CREATE syntax and well defined schema before hand anyways.
Say I have a table mid_mgmt(salpers_id, salpers_name, manager_id) that represents the middle level of management in this company, i.e., salespersons that are manager’s of someone and have a manager themselves. Write an insert statement that fills this table.
INSERT INTO mid_mgmt (
SELECT S2.salpers_id, S2.salpers_name, S2.manager_id
FROM salesperson S1, salesperson S2
WHERE S2.salpers_id = S1.manager_id;)
^^This returns nulls… might work I guess^^ Better version below:
SELECT salpers_id, salpers_name, manager_id
FROM salesperson
WHERE salpers_id in ( SELECT manager_id FROM salesperson) and manager_id is not null;
Deletion
DELETE FROM R WHERE C
Extreme danger – no recovery, no recycle bin, inodes wont help. Deletions are permanent*.
* (log might help you)
TRUNCATE R => DELETE FROM R
DROP TABLE R deletes the whole relation and schema
Update
UPDATE R SET a=1, b=2,…k=n WHERE C
Add 1 dollar to Gomez current balance
UPDATE customer SET cur_bal=cur_bal+1 WHERE cust_name = 'Gomez';
Increase prices that are less than $25 by 7%
update product set price = price * 1.07 where price < 25
Say, Gomez bought a Sweater and paid from his account. Update his account reflecting the cost of the purchase:
update customer set cur_bal = cur_bal - (select price from product where prod_desc = 'Sweater') where cust_name='Gomez';