Introduction to SQL

SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases. It is used to perform tasks such as querying data, updating records, inserting data, and deleting records. Here are some key points about SQL:

1. Data Querying: SQL allows users to query data from a database using the SELECT statement, which can retrieve specific data based on given criteria.

2. Data Manipulation: SQL provides commands for inserting (INSERT), updating (UPDATE), and deleting (DELETE) data within database tables.

3. Data Definition: SQL includes commands for defining the structure of databases and tables, such as CREATE TABLE, ALTER TABLE, and DROP TABLE.

4. Data Control: SQL has features for controlling access to data within a database, including granting and revoking permissions using GRANT and REVOKE.

SQL is widely used in various database systems, including MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Database.

Keep in Mind That...SQL keywords are NOT case sensitive: select is the same as SELECT

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Some of The Most Important SQL Commands

CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table

SELECT - extracts data from a table
UPDATE - updates data in a table
DELETE - deletes data from a table
INSERT INTO - inserts new data into a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

1.Data Definition Commands

Creating a Database

The CREATE DATABASE command is used to create a new database.

CREATE DATABASE database_name;

Altering a Database

The ALTER DATABASE command is used to modify a database. However, the specifics of what can be altered vary between different database management systems (DBMS). Common alterations include changing database settings or modifying properties.

Example: Renaming a Database (PostgreSQL)

ALTER DATABASE old_database_name RENAME TO new_database_name;

Example: Changing the Owner of a Database (PostgreSQL)

ALTER DATABASE database_name OWNER TO new_owner;

Dropping a Database

The DROP DATABASE command is used to delete an existing database. This action is irreversible and will remove all data and structures within the database.

DROP DATABASE database_name;

CREATE TABLE: Used to create a new table.

CREATE TABLE table_name 

( column1 datatype PRIMARY KEY, column2 datatype, column3 datatype );

ALTER TABLE: Used to modify an existing table (e.g., adding or dropping columns)

ALTER TABLE table_name ADD column_name datatype; 

ALTER TABLE table_name DROP COLUMN column_name;

DROP TABLE: Used to delete a table

DROP TABLE table_name;

CREATE INDEX: Used to create an index (search key).

CREATE INDEX index_name ON table_name (column1, column2);

2.Data Manipulation Commands

INSERT INTO: Used to insert new records into a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE: Used to modify existing records in a table.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

DELETE: Used to delete records from a table.

DELETE FROM table_name WHERE condition;

3.Data Querying Commands

SELECT: Used to query data from a database.

SELECT column1, column2 FROM table_name;

WHERE: Used to filter records.

SELECT column1, column2 FROM table_name WHERE condition;

ORDER BY: Used to sort the result set.

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

JOIN: Used to combine rows from two or more tables based on a related column.

SELECT columns FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;

3.Data Control Commands

GRANT: Used to give user access privileges to the database.

GRANT SELECT, INSERT ON table_name TO 'username';

REVOKE: Used to remove user access privileges.

REVOKE SELECT, INSERT ON table_name FROM 'username';

4.Transaction Control Commands

BEGIN TRANSACTION: Used to start a transaction.

BEGIN TRANSACTION;

COMMIT: Used to save the changes made by the transaction.

COMMIT;

ROLLBACK: Used to undo changes made by the transaction.

ROLLBACK;

These commands form the foundation of SQL and are essential for performing various operations in relational databases.