Study Notes on Database Tables and SQL Commands
Overview of Database Tables
A database typically comprises one or more tables.
Each table is identified by a unique name (e.g., "Customers" or "Orders").
Tables consist of records (rows) that hold data.
Example: Customers Table
Customer ContactNam PostalCo Countr
ID CustomerName e Address City de y
German
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 y
Ana Trujillo Avda. de la México
2 Emparedados y helados Ana Trujillo Constitución 2222 D.F. 05021 Mexico
Antonio Moreno Antonio México
3 Taquería Moreno Mataderos 2312 D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
Christina
5 Berglunds snabbköp Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
Key Points
The above table contains five records and seven columns.
SQL keywords are not case-sensitive; however, it is standard practice to write
them in upper-case.
A semicolon (;) is often required at the end of each SQL statement, especially
when executing multiple statements.
Important SQL Commands
1. SELECT: Extracts data from a database.
Syntax:
sql
SELECT column1, column2 FROM table_name WHERE condition;
2. INSERT INTO: Adds new data to a database.
Syntax:
sql
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Example:
sql
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
3. UPDATE: Modifies existing data in a database.
Syntax:
sql
UPDATE table_name SET column1 = value1 WHERE condition;
Example:
sql
UPDATE Customers SET City = 'Berlin' WHERE CustomerID = 1;
4. DELETE: Removes data from a database.
Syntax:
sql
DELETE FROM table_name WHERE condition;
Example:
sql
DELETE FROM Customers WHERE CustomerID = 5;
5. CREATE DATABASE: Creates a new database.
Syntax:
sql
CREATE DATABASE database_name;
6. ALTER DATABASE: Modifies an existing database.
Syntax:
sql
ALTER DATABASE database_name SET option = value;
7. CREATE TABLE: Creates a new table in the database.
Syntax:
sql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
Example:
sql
CREATE TABLE Employees (
EmpId INT PRIMARY KEY,
LastName VARCHAR(255),
FirstName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255)
);
8. ALTER TABLE: Modifies an existing table.
Syntax:
sql
ALTER TABLE table_name ADD column_name datatype;
9. DROP TABLE: Deletes a table from the database.
Syntax:
sql
DROP TABLE table_name;
10. CREATE INDEX: Creates an index for faster searches.
Syntax:
sql
CREATE INDEX index_name ON table_name (column_name);
11. DROP INDEX: Deletes an index from a table.
Syntax:
sql
DROP INDEX index_name ON table_name;
Summary of SQL Command Usage
Use the SELECT command for retrieving data with filtering options available
through the WHERE clause.
The INSERT INTO command can add single or multiple records to a table.
The UPDATE command allows for targeted modifications based on specified
conditions using the WHERE clause.
The DELETE command can remove specific records or all records if no condition is
provided.
These notes provide a foundational understanding of how to work with tables and
perform basic SQL operations within a database context, particularly using the
.Northwind sample database as an example
