Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
149 lines (101 sloc) 5.87 KB
List the commands for adding, updating, and deleting data
CREATE TABLE (table name) ((column name) (datatype));
INSERT INTO (table name) (column names) VALUES (values in column order);
UPDATE (table name) SET (column name = value) WHERE (column name =,<,> value);
DELETE FROM (table name) WHERE (column name =,<,> value);
ALTER TABLE (table name) ADD COLUMN (column name) (datatype) SET DEFAULT (something);
ALTER TABLE (table name) DROP COLUMN (column name);
DROP TABLE (table name);
Explain the structure for each type of command.
*see above*
What are some the data types that can be used in tables? Give a real world example of each.
timestamp - employees clocking into and out of work
boolean - does an employee have administrative access? TRUE of FALSE
money - store inventory's price range
Think through how to create a new table to hold a list of people invited to a wedding. This table needs to have first and last name, whether they sent in their RSVP, the number of guests they are bringing, and the number of meals (1 for adults and 1/2 for children).
Which data type would you use to store each of the following pieces of information?
First and last name.
character varying
Whether they sent in their RSVP.
boolean
Number of guests.
integer
Number of meals.
numeric(2, 1)
Write a command that makes the table to track the wedding.
CREATE TABLE wedding (
name character varying,
rsvp boolean,
guests integer,
meals numeric(2, 1)
);
Using the table we just created, write a command that adds a column to track whether they were sent a thank you card.
ALTER TABLE wedding ADD COLUMN thank-you-card boolean SET DEFAULT false;
You have decided to move the data about the meals to another table, so write a command to remove the column storing the number meals from the wedding table.
ALTER TABLE wedding DROP COLUMN meals;
The guests are going to need a place to sit at the reception, so write a statement that adds a column for table number.
ALTER TABLE wedding ADD COLUMN table-number integer;
The wedding is over and we do not need to keep this information, so write a command that deletes the wedding table from the database.
DROP TABLE wedding;
Write a command to make a new table to hold the books in a library with the columns ISBN, title, author, genre, publishing date, number of copies, and available copies.
CREATE TABLE books (
ISBN varchar(10),
title varchar,
author varchar,
genre varchar,
publishing-date date,
number-of-copies integer,
available-copies integer,
);
Find three books and add their information to the table.
INSERT INTO books (ISBN, title, author, genre, publishing-date, number-of-copies, available-copies)
VALUES
(0-7475-3269-9, 'Harry Potter and the Philosopher's Stone', 'J.K. Rowling', 'Fantasy', 'June 26, 1997', 100, 50),
(978-0307887, 'Ready Player One', 'Ernest Cline', 'Adventure', 'August 16, 2011', 50, 25),
(0544174224, 'The Hobbit: Illustrated Edition', 'J.R.R. Tolkien', 'Fantasy', 'October 1, 2013', 75, 25);
Say that someone has just checked out one of the books. Change the available copies column to 1 less.
UPDATE books SET available-copies=24 WHERE title='Ready Player One';
Now one of the books has been added to the banned books list. Remove it from the table.
DELETE FROM books WHERE title='The Hobbit: Illustrated Edition';
Write a command to make a new table to hold spacecrafts. Information should include id, name, year launched, country of origin, a brief description of the mission, orbiting body, if it is currently operating, and approximate miles from Earth.
CREATE TABLE spacecrafts (
id integer,
name varchar,
launch-year date,
country varchar,
mission-description text,
orbiting-body varchar,
operational boolean,
miles-from-earth bigint
);
Add 3 non-Earth-orbiting satellites to the table.
INSERT INTO spacecrafts (id, name, launch-year, country, mission-description, orbiting-body, operational, miles-from-earth)
VALUES
(4717, 'Morty', 2014, 'USA', 'Find Mega Seeds for Rick', 'Gazorpazorp', TRUE, 68475849375859),
(1977, 'Millenium Falcon', 1977, 'USA', 'Avoid Imperial Entanglements', 'Tatooine', TRUE, 99984757374267),
(1998, 'The Bebop', 1998, 'Japan', 'Hunt Bounties', 'Mars', FALSE, 96846274371629);
Remove one of the satellites from the table since it has just been crashed into the planet.
DELETE FROM spacecrafts WHERE name='Morty';
Edit another satellite because it is no longer operating and change the value to reflect that.
UPDATE spacecrafts SET operational=FALSE WHERE name='Millenium Falcon';
Write a command to make a new table to hold the emails in your inbox. This table should include an id, the subject line, the sender, any additional recipients, the body of the email, the timestamp, whether or not it’s been read, and the id of the email chain it’s in.
CREATE TABLE inbox (
id integer,
subject varchar,
sender varchar,
cc varchar,
body text,
timestamp timestamp,
read boolean,
chain-id integer
);
Add 3 new emails to the inbox.
INSERT INTO inbox (id, subject, sender, cc, body, timestamp, read, chain-id)
VALUES
(1123, 'TPS Reports', 'BillLumbergh@Initech.com', '', 'Yeah, gonna need you to come in Saturday', 2003-04-12 17:05:06, FALSE, 4242),
(7765, 'COPIER!!!', 'SamirNagheenanajar@Initech.com', '', 'Meet me outside in 15. It's time to destroy that unholy machine', 2003-04-12 12:05:06, TRUE, 3455),
(3412, 'Stapler', 'MiltonWaddams@Initech.com', '', 'Come into work late tomorrow', 2003-04-12 10:05:06, TRUE, 6666);
You’ve just deleted one of the emails, so write a command to remove the row from the inbox table.
DELETE FROM inbox WHERE id='1123';
You started reading an email but just heard a crash in another room. Mark the email as unread before investigating, so you can come back to it later.
UPDATE inbox SET read=FALSE WHERE subject='Stapler';
You can’t perform that action at this time.