3.Modifying data

[mind1949]

Intro

Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. Operations that alter your data like this are collectively known as Data Manipulation Language, or DML. In previous sections, we returned to you the results of the query you've performed. Since modifications like the ones we're making in this section don't return any query results, we instead show you the updated content of the table you're supposed to be working on. You can compare this with the table shown in 'Expected Results' to see how you've done.

Q1: insert some data into table

The club is adding a new facility - a spa. We need to add it into the facilities table. Use the following values: facid: 9, Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.

insert into cd.facilities
    (facid, name, membercost,guestcost, initialoutlay, monthlymaintenance)
    values(9, 'Spa', 20, 30, 100000, 800);

Q2: insert multiple rows of data into a table

In the previous exercise, you learned how to add a facility. Now you're going to add multiple facilities in one command. Use the following values: facid: 9, Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800. facid: 10, Name: 'Squash Court 2', membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80.

insert into cd.facilities
    (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
    values
        (9, 'Spa', 20, 30, 100000, 800),
        (10, 'Squash Court 2', 3.5, 17.5, 5000, 80);   

--or--
insert into cd.facilities
    (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
    SELECT 9, 'Spa', 20, 30, 100000, 800
    UNION ALL
        SELECT 10, 'Squash Court 2', 3.5, 17.5, 5000, 80;

解析:

本质上values是生成了一张表,所以在插入数据时可以用select代替values

Q3: insert calculated data into a table

Let's try adding the spa to the facilities table again. This time, though, we want to automatically generate the value for the next facid, rather than specifying it as a constant. Use the following values for everything else: Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.

insert into cd.facilities
    (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
    values
        ((select max(facid) from cd.facilities)+1, 'Spa', 20, 30, 100000, 800)

--or--
insert into cd.facilities
    (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
    select
        (select max(facid) from cd.facilities)+1, 'Spa', 20, 30, 100000, 800;

Q4: update some existing data

We made a mistake when entering the data for the second tennis court. The initial outlay was 10000 rather than 8000: you need to alter the data to fix the error.

update cd.facilities
    set initialoutlay = 10000
    where name = 'Tennis Court 2';

Q5: update multiple rows an columns

We want to increase the price of the tennis courts for both members and guests. Update the costs to be 6 for members, and 30 for guests.

update cd.facilities
    set membercost = 6,
        guestcost = 30
    where name like 'Tennis Court%';

Q6: update a row based on another

We want to alter the price of the second tennis court so that it costs 10% more than the first one. Try to do this without using constant values for the prices, so that we can reuse the statement if we want to.

update cd.facilities
    set membercost = (select membercost*1.1
                        from cd.facilities
                        where name = 'Tennis Court 1'),
        guestcost = (select guestcost*1.1
                        from cd.facilities
                        where name = 'Tennis Court 1')
    where name = 'Tennis Court 2';

Q7: delete all bookings

As part of a clearout of our database, we want to delete all bookings from the cd.bookings table. How can we accomplish this?

delete from cd.bookings;

--or--
truncate cd.bookings;

Q8: delete a member from cd.members

We want to remove member 37, who has never made a booking, from our database. How can we achieve that?

delete from cd.members where memid = 37;

Q9: delete based on subqueries

In our previous exercises, we deleted a specific member who had never made a booking. How can we make that more general, to delete all members who have never made a booking?

delete from cd.members
    where not memid in (select memid from cd.bookings);

[mind1949]

第三章完成!ﾍ|･∀･|ﾉ*~●