SQL Basic - What you need to know! (Basics)
Tags: sql, beginners, programming, womenintech
Published: 2022-05-11 06:18:16

## 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 

```SQL
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`

```SQL
-- 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..`
```SQL
-- 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 …;`

```SQL
-- 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 

```SQL
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. 
```SQL
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.

```SQL
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.
```SQL
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.

	

```SQL
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 .
	

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

ROLLBACK;
```