SQL Basic - What you need to know! (Basics)

Main components of SQL’s Syntax:

Data Definition Language (DDL)

A set of statements that allow user to modify data structures and objects e.g. tables

• The CREATE statement

CREATE object_type object_name;

CREATE TABLE object_name(column_name data_type);

CREATE TABLE sales(purchase_no INT);

• The ** ALTER** statement Used when altering existing objects; ADD, REMOVE, RENAME

-- Add a column in a table
ALTER TABLE sales
ADD COLUMN date_of_purchase DATA;

-- delete an entire table   
DROP TABLE customer;

-- Rename a table   
RENAME TABLE sales TO customer;

• The TRUNCATE statement Instead of deleting an entire table through DROP, you can remove its data and continue to have as an abject in the database.

Data Manipulation Language (DML)

Statements that allow to manipulate the data in the tables of a database.

• The SELECT statement SELECT … FROM..

-- deliver all the records in the table.
    SELECT* FROM sales; 
-- Select specific specific data from the database. 
SELECT row FROM sales;      

The INSERT statement
Used insert data into tables INSERT .. INTO .. VALUES …;

-- Choose specific cell to insert values
INSERT INTO sales (purchase_number, date_of_purchase) 
VALUES(1, “12-09-2020”);

-- Insert value
INSERT INTO sales VALUES(1, “12-09-2020”);

• The UPDATE statement UPDATE.. SET.. WHERE Allows you to renew existing data of your table

UPDATE sales
SET date_of_purchase = “16-11_2020”
WHERE purchase_number =1;

• The DELETE statement DELETE… FROM… WHERE Specify what is to be removed in a table.

DELETE FROM sales
WHERE purchase_number =1;

Data Control Language (DCL)

GRANT and REVOKE statements. They allow rights of people using the database and used by administrators.

• The GRANT statement Gives or grants certain permissions to users.

GRANT type_of_permission ON database_name.table_name TO ‘username’@‘localhost’

-- Creates user `ochwada` whose password is "password"
CREATE USER ‘ochwada’@‘localhost’ IDENTIFIED BY ‘password’;

-- Allow the user to use all SELECT Statements on the table 'customer' from the 'sales' database.
GRANT SELECT ON sales.customers To ‘ochwada’@‘localhost’;

-- Use all statements and all tables in the 'sales' database.
GRANT ALL ON sales.* To ‘ochwada’@‘localhost’;

• The REVOKE Clause Used to revoke permissions and privileges of database users.

REVOKE type_of_permission ON database_name.table_name TO ‘username’@‘localhost’

-- Only the use of SELECT statement will be REVOKED for the table customers in the sales database.  
REVOKE SELECT ON sales.customers To ‘ochwada’@‘localhost’;

-- use of all statements in the 'sales' database will be revoked.   
REVOKE ALL ON sales.* To ‘ochwada’@‘localhost’;

Transaction Control Language (TCL)

Not every change you make to a database is saved automatically.

• The COMMIT statement Related to INSERT, DELETE, UPDATE. It will save the changes you’ve made permanently Allow other users to have access to modified version of the database.

UPDATE sales
SET date_of_purchase = “16-11_2020”
WHERE purchase_number =1;

COMMIT;

• The ROLLBACK Clause The clause that will let you make a step back . Allow you to undo any changes you have made but don’t want to be saved permanently .

UPDATE sales
SET date_of_purchase = “16-11_2020”
WHERE purchase_number =1;
COMMIT;

ROLLBACK;