SQL Operations

After entering the SQLite shell, use SQL commands to create tables. CREATE TABLE: creates a new table. For my assignment I will be making a table about athletes so I type athletes after the CREATE TABLE command.

id INTEGER PRIMARY KEY AUTOINCREMENT: Creates a unique, auto-incrementing identifier for each athlete.

name TEXT NOT NULL: Stores the athlete’s name. The NOT NULL constraint ensures this column cannot be empty.

sport TEXT NOT NULL: Stores the sport the athlete plays. Also NOT NULL.

country TEXT NOT NULL: The country the athlete represents. Also NOT NULL.

age INTEGER: The athlete's age.

height REAL: The athlete’s height (e.g., in meters).

weight REAL: The athlete’s weight (e.g., in kilograms).

INSERT INTO: Adds new data into the athletes table.

Columns Specified: (name, sport, country, age, height, weight) ensures data is added only to these columns. The id column not listed is automatically handled by the database.

VALUES: Contains the data for the specified columns in the same order.

SELECT: Retrieves data from the athletes table.

*: Selects all columns in the table.

FROM athletes: Displays all rows and columns in the athletes table.

ALTER TABLE: Modifies the structure of the existing athletes table.

ADD COLUMN team TEXT: Adds a new column named team to store the athlete’s team.

UPDATE: Modifies existing data in the athletes table.

SET team =: Updates the team column to the value of the team they play for.

WHERE name/country =: Ensures only the row where the athlete's name or country matches is updated.

DELETE FROM athletes: Removes data from the athletes table.

WHERE name =: Deletes only the row where the athlete’s name matches.