Some general SQL rules before we dive in-

1. Keywords are generally not case sensitive in SQL. However, the common practice is to use uppercase for keywords.

2. SQL statements always end with a semi-colon (;) unless otherwise stated.

3. Square brackets [ ] in the syntax indicate optional content.

Adding Comments

Use two hyphens, followed by a space. Eg: -- comment

Use the # symbol. Eg: #comment

Defining and Using a Database

CREATE DATABASE name_of_database;

After we create a database, we have to let the DBMS know that we want to use this database. This is because the DBMS may be managing more than one databases concurrently. We have to let it know that all subsequent code that we write applies to the stated database.

USE name_of_database;

Deleting a Database

DROP DATABASE [IF EXISTS] name_of_database;

Creating a Table

CREATE TABLE table_name ( column_name1 datatype [column constraints], column_name2 datatype [column constraints], … [table constraints], [table constraints] );

Renaming a Table

RENAME TABLE old_name TO new_name;

Deleting a Table

DROP TABLE [IF EXISTS] table_name;

Altering a Table

To change the starting value of the auto increment column

ALTER TABLE table_name

AUTO_INCREMENT = starting_value;

To add a table constraint (including foreign key constraints)

ALTER TABLE table_name

ADD CONSTRAINT [name of constraint] details_of_constraint;

To drop a table constraint (excluding foreign key constraints)

ALTER TABLE table_name

DROP INDEX name_of_constraint;

To drop a foreign key constraint

ALTER TABLE table_name

DROP FOREIGN KEY name_of_foreign_key;

To modify a column

ALTER TABLE table_name

MODIFY COLUMN column_name data_type [constraints];

To drop a column

ALTER TABLE table_name

DROP COLUMN column_name;

To add a column

ALTER TABLE table_name

ADD COLUMN column_name data_type [constraints];

To modify the foreign key constraint

1. Drop the original foreign key

   ALTER TABLE mentorships DROP FOREIGN KEY fk2;

2. Using a new ALTER statement (we are not allowed to drop and add a foreign key with the same name using a single ALTER statement), we add the foreign key back with the modified conditions.

   ALTER TABLE mentorships

   ADD CONSTRAINT fk2 FOREIGN KEY(mentee_id) REFERENCES employees(id) ON DELETE CASCADE ON UPDATE CASCADE, DROP INDEX mm_constraint;

Getting Table Details

DESCRIBE table_name;

Inserting Data into a Table

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

Updating Data

UPDATE employees SET batting_average = '43.21' WHERE id = 3;

Deleting Data

DELETE FROM cricketers WHERE id = 5;