SQL(4)---Manipulation

Manipulation

In order to create a Table, we use the code below:

CREATE TABLE celebs ( id INTEGER, name TEXT, age INTEGER );

The words in yellow can be replaced by other variable words and values.

Then, to insert a row into the table, we use the code below:

INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 22);

The value row is the values you would put into the row of that table.

Add columns to a table, we use:

ALTER TABLE celebs ADD COLUMN twitter_handle TEXT; SELECT * FROM celebs;

1. ALTER TABLE is a clause that lets you make the specified changes.
2. celebs is the name of the table that is being changed.
3. ADD COLUMN is a clause that lets you add a new column to a table: 

• twitter_handle is the name of the new column being added
• TEXT is the data type for the new column

4. NULL is a special value in SQL that represents missing or unknown data. Here, the rows that existed before the column was added have NULL values for twitter_handle.

The ALTER TABLE statement adds a new column to a table.

Change existing records, we use:

UPDATE celebs SET twitter_handle = '@taylorswift13' WHERE id = 4; SELECT * FROM celebs;

1. UPDATE is a clause that edits a row in the table. 
2. celebs is the name of the table. 
3. SET is a clause that indicates the column to edit.

The UPDATE statement edits a row in a table.

Delete existing records, we use:

DELETE FROM celebs WHERE twitter_handle IS NULL; SELECT * FROM celebs;

1. DELETE FROM is a clause that lets you delete rows from a table.
2. celebs is the name of the table we want to delete rows from.
3. WHERE is a clause that lets you select which rows you want to delete. Here we want to delete all of the rows where the twitter_handle column IS NULL.

4.IS NULL is a condition in SQL that returns true when the value is NULL and false otherwise.

The DELETE FROM statement deletes one or more rows from a table.

Used to tell the database to reject inserted data that does not adhere to a certain restriction.

CREATE TABLE awards ( id INTEGER PRIMARY KEY, recipient TEXT NOT NULL, award_name TEXT DEFAULT 'Grammy' );

1. PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

2. UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.

3. NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.

4. DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

Constraints that add information about how a column can be used are invoked after specifying the data type for a column.

REVIEW:

SQL is a programming language designed to manipulate and manage data stored in relational databases.

• A relational database is a database that organizes information into one or more tables.
• A table is a collection of data organized into rows and columns.

A statement is a string of characters that the database recognizes as a valid command.

• CREATE TABLE creates a new table.
• INSERT INTO adds a new row to a table.
• SELECT queries data from a table.
• ALTER TABLE changes an existing table.
• UPDATE edits a row in a table.
• DELETE FROM deletes rows from a table.

Constraints add information about how a column can be used.